DATABASES-1 PRACTICE [IP-12fAB1G] [2016.feb]
Practice: Wednesday 10:15-11:45 ELTE South B. 2-315 PC4 (comp.lab) Lecture: [here: link] Tuesday 10:15-11:45 South Building 0-221 (class) Practice Course Material/weeks: |#1.Feb.17. |#2. Feb.24. |#3.Mar.02. |#4. Mar.09. | #5.Mar.16. | --- from Mar.23 to Mar.29. Easter Holiday, no classes --- |#6. Mar.30. | |#7. Apr.06. Midterm Test | |#8. Apr.13. | #9. Apr.20. | #10. Apr.27 | #11. May.04. | |#12. May.11. Final Test | | *** Practice RE-TAKE TEST *** | (1) 24 May (Tuesday) 12:15-13:45 Room: 2-315 lab. PC4 | | (2) 27 May (Friday) 10:15-11:45 Room: 2-315 lab. PC4 | | (3) 31 May (Tuesday) 12:15-13:45 Room: 2-315 lab. PC4 | Info: Oracle sqldeveloper Oracle Technology Network: otn.oracle.com > Windows Installation Notes -->> Download(windows32/64.zip) > RPM for Linux Installation Notes -->> Download(linux.rpm) > Connections (1) ARAMIS Hostname: aramis.inf.elte.hu Port: 1521 Service name: eszakigrid97 (2) TOMX Hostname: tomx.inf.elte.hu Port: 1522 Service name: ora11g Course Material (Practice) #1.) 17.Feb.2016. SQL SELECT [+create HRsyn] >> Les01select, Les02where From Relational Algebra to SQL SELECT: >> Exercises1/Fruits [+create table] [+//solution//] class work: 1-7, homework: 8-9 (to be cont.) #2.) 24.Feb.2016. SQL SELECT [+create HRsyn] >> Les03functions, DocSQL: Func's (this week: only Character and Numeric Functions) From Relational Algebra to SQL SELECT >> Exercises1/Fruits [+create table] [+//solution//] class work: 8-11 (to be cont.) #3.) 02.Mar.2016. SQL SELECT [+create HRsyn] >> Les03functions, DocSQL: Func's (more Character, Datetime, Conversion, NULL-Related Functions) >> Orace11gR2 Doc.Library Example Tables: HR Schemas, createHRsynonyms > Sample Schemas - Schema Diagram: HR > SQL Lang.Ref. >> 5 Functions >> see Examples: -- Single-Row Functions Character Functions e.g. SUBSTR, RPAD, ..., INSTR, ... Datetime Functions e.g. MONTHS_BETWEEN, ... Conversion Functions e.g. TO_DATE, ... Null-related Functions e.g. NVL, ... PRACTICES >> OracleExercises Simple Queries: 1.1-1.20. #4.) 09.Mar.2016. Extended Relational Algebra >> Lect_03 (Chapter 5.1-5.2) SET OPERATORS [+create HRsyn] >> Les07set Using the Set Operators GROUPING [+create HRsyn] >> Les04groupby From SQL queries into Ext.Rel.Algebra >> DocSQL: Func's >> Aggregate Func's: COUNT, MAX, MIN, SUM, ... PRACTICES >> Exercises3/DeptEmp queries, set op, group by: 1-13. [+create tables] -- in SQL SELECT statement and in Extended Relational Algebra, too #5.) 16.Mar.2016. SQL SELECT [+create HRsyn] JOINS, OUTER JOINS: >> Les05from Displaying Data from Multiple Table SUBQUERIES >> Les06subq Using Subqueries (to be cont. from 6-31 'The with clause') PRACTICES >> Exercises3/DeptEmp subqueries, joins: 14-21. [+create tables] #6.) 30.Mar.2016. PAPER EXERCISES: Queries in Relational Algebra -> Datalog, and SQL >> Exercises1/Fruits [+create table] [+//solution (part:rel.alg)//] ORACLE EXERCISES SQL DML >> Les08dml Manipulating Data [+ createHRtables] PRACTICES >> Exercises3/DeptEmp delete, insert, update: 22-30. [+create tables] >> more OracleExercises Queries: 3.1-3.20. #7.) 06.Apr.2016. Book: Database Systems: The Complete Book (2nd ed) by Garcia-Molina, Jeff Ullman and Jennifer Widom Table of Contents [+Help: Temp (Apr.19-21, for 3 days)] >> MidtermTest_Exercises_06Apr2016.pdf >> MidtermTest_Results_06Apr2016.pdf
#8.) 13.Apr.2016. SQL DDL statements: Lect_07_SQL_DDL.pdf Oracle: SQL1_Les09.pdf and SQL1_Les10.pdf Recursive Queries: Lect_05_Recursion.pdf [+create_table_Flights] >> Les06subq The with clause (from 6-31) >> 2/7.lesson Hierarchical Queries >> Oracle SQL Lang.Ref.11.2.pdf -- Hierarchical Queries 9-3 and Example 9-5 -- Recursive Queries 19-36 Subquery Factoring Example #9.) 20.Apr.2016. --- PL/SQL Part 1/3 -- PL/SQL Programming Concepts, Review: Oracle_PLSQL.pdf Using Oracle PL/SQL: Ullman/Stanford PL/SQL Lang.Ref.11.2: HTML PDF (ELTE) | HTML PDF (Oracle) | 4. Control Statements Examples -- Put this line before the PL/SQL block's: SET SERVEROUTPUT ON >> 2-24 Variable declaration, Assignment statement >> 2-25 SELECT INTO statement retreives one or more columns from a singe row (Correct this example of PL/SQL blokk: begin ... end; / First exercises: 'Hello, Word!' with Substituting Variables '&Name' >> 4-5 IF THEN ELSIF statements >> 4-6 CASE statements, plus try this: grade := 'B' -> '&B' >> 4-10 Basic LOOP statements with EXIT >> 4-14 FOR LOOP statements >> 4-27 WHILE LOOP statements #10.) 27.Apr.2016. --- PL/SQL Part 2/3 -- PL/SQL Programming Concepts, Review: Oracle_PLSQL.pdf Using Oracle PL/SQL: Ullman/Stanford PL/SQL Lang.Ref.11.2: HTML PDF (ELTE) | HTML PDF (Oracle) | -- Put this line before the PL/SQL block's: SET SERVEROUTPUT ON Using DML and Transactional Statements in PL/SQL to Manipulate Data Examples of 6. Static SQL/Cursors DML statements in PL/SQL -- Put this line before the PL/SQL block's: SET SERVEROUTPUT ON >> 6-1 SQL DML statements in PL/SQL >> 6-4 Implicit cursor attributes Queries in PL/SQL, Cursors, Explicit cursors -- Put this line before the PL/SQL block's: SET SERVEROUTPUT ON >> 6-7 Explicit cursors >> from 6-11 to 6-14 Explicit cursor attributes >> 6-17 Passing parameters to explicit cursors >> 6-43 FOR UPDATE cursor in CURRENT OF clause of UPDATE statement Using PSM Functions and Procedures in PL/SQL -- Put this line before the PL/SQL block's: SET SERVEROUTPUT ON Examples of 8.PL/SQL Subprograms (or before e.g.4-1 Procedures in IF-THEN) Exceptions and Error Handlings in PL/SQL -- Put this line before the PL/SQL block's: SET SERVEROUTPUT ON Examples of 11.PL/SQL Error Handling --> table-11-2 Predifined Exceptions e.g. 11-3 calculates a price-to-earnings ratio for a company. If the company has zero earnings, the division operation raises the predefined exception ZERO_DIVIDE, the execution of the block is interrupted, and control transfers to the exception handlers. #11.) 04.May.2016. --- PL/SQL Part 3/3 -- More Excercises in the EMP and DEPT tables [+create tables] (1) Display the 1., 3., and 5. highest-paid employee names and salaries. Help: %ROWCOUNT Attribute: How Many Rows Were Fetched? %ROWCOUNT returns: Zero after the named cursor is opened but before the first fetch. Otherwise, the number of rows fetched. See 6-14 uses %ROWCOUNT to determine when the fifth row is fetched. (2) Increase the salaries of the officials ( CLERK ) 20% of their own. Help: The SELECT statement with the FOR UPDATE clause (SELECT FOR UPDATE) selects the rows of the result set and locks them. SELECT FOR UPDATE enables you to base an update on the existing values in the rows, because it ensures that no other user can change those values before you update them. When SELECT FOR UPDATE is associated with an explicit cursor, the cursor is called a FOR UPDATE cursor. Only a FOR UPDATE cursor can appear in the CURRENT OF clause of an UPDATE or DELETE statement. See 6-43 FOR UPDATE cursor appears in the CURRENT OF clause of an UPDATE statement.
(3) Display the salary of 'King' with handling exceptions NO_DATA_FOUND, TOO_MANY_ROWS Help: See 11-5 and table-11-2 Predifined Exceptions (4) Recursive Queries in PL/SQL [+create_table_Flights] and Flights_Excercises Next Week: Final Test : ONLY ORACLE EXERCISES (no paper excercises) Topic of all the semester: SQL SELECT, DML and DDL statements, PL/SQL #12.) 11.May.2016. >> Exercises (A) and (B) MidtermTest_Exercises_06Apr2016.pdf >> Tables for Final Test (C) and (D) script create_table_Battles.txt >> Sheet: Attendance_Sheet_Database1_Pract.pdf >> Results: Midterm_Test_Results_06Apr2016.pdf >> Results: Grades_and_Final_Test_Results_11May2016.pdf (A) Midterm Test: paper Rel.Alg. 54 points (9 exercises, 9x6p) (B) Midterm Test: computer SQL 36 ponts (6 exercises, 6x6p) -- corrected by (C) ------------- sum 90 points (C) Final Test: computer SQL 36 points (6 exercises, 6x6p) -- correction for (B) (D) Final Test: comp. PL/SQL 24 ponts (2 exercises, 2x12p) ------------- sum 60 points *** Practice RE-TAKE TEST *** (1) 24 May (Tuesday) 12:15-13:45 Room: 2-315 lab. PC4 (2) 27 May (Friday) 10:15-11:45 Room: 2-315 lab. PC4 (3) 31 May (Tuesday) 12:15-13:45 Room: 2-315 lab. PC4 >>> Back to the main page >>> Hajas Csilla © ELTE IK Információs Rendszerek Tanszék
