| 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 |
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
*** 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