1 / 39

Advanced SQL (part 2) and SQL in practice

Advanced SQL (part 2) and SQL in practice. CS263 Lecture 8 . Ensuring transaction integrity. User-defined transactions can improve system performance because transaction will be processed as sets rather than individually

kaya
Download Presentation

Advanced SQL (part 2) and SQL in practice

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Advanced SQL (part 2) and SQL in practice • CS263 Lecture 8

  2. Ensuring transaction integrity • User-defined transactions can improve system performance because transaction will be processed as sets rather than individually • Some SQL systems have concurrency controls that handle the updating of a shared database by concurrent users • These can journalise database changes, so that a database can be recovered after abnormal termination in the middle of a transaction • Such controls are transparent to the user, no special programming is needed to ensure proper control of concurrent access to data

  3. Data dictionary facilities • Data dictionary = system tables that store metadata • Users usually can view some of these tables, using SELECT statements that can generate reports about system usage, user privileges etc, • Users are restricted from updating them, sine the DBMS maintains them and depends on them for its processing • In Oracle there are over 100 data dictionary views • Tables that anyone can access begin with USER or ALL • Tables that only the Database Administrator can use begin with DBA

  4. Data dictionary facilities • Examples: • DBA_TABLES – descriptions of all tables in database • DBA_CONSTRAINTS – description of constraints • DBA_USERS – information about the users of the system • DBA_TAB_PRIVS – descriptions of grants on objects in the database

  5. SQL-99 enhancements/extensions • Enhancements to SQL have been built into many implementations (including Oracle) • User-defined data types (UDT) subclasses of standard types or an object type • Analytical functions (for OLAP/Data visualisation) - many mathematical/statistical and related functions • Persistent Stored Modules (SQL/PSM) - capability to create and drop code modules. Persistent means that a module of code will be stored until dropped, making it available for execution across user sessions.

  6. SQL-99 enhancements/extensions • New statements: • CASE, IF, LOOP, FOR, WHILE, REPEAT etc • New statements and PSM introduce procedurality into SQL (statements are processed sequentially) whereas base SQL is a non-procedural language and no statement execution sequence is implied • SQL-99 Standard not widely adopted yet • Oracle has propriety version called PL/SQL

  7. Routines and triggers • These are stored in the database and controlled by the DBMS • This promotes stronger data integrity and consistency of use within the database • Since they are stored once, code maintenance is simplified • Both consist of blocks of procedural code • Trigger code is stored in the database and runs automatically whenever the triggering event (such as an UPDATE) occurs • Routines do not run automatically, they have to be called in to operate

  8. Triggers • Since triggers are stored and executed in the database, they execute against all applications that access the database • Triggers can also cascade, causing other triggers to fire • They can be used to ensure referential integrity, enforce business rules, create audit trails etc. • Constraints can be thought of as a special case of triggers, as they are applied automatically as a result of data modification commands (though they are not as flexible as triggers) • Triggers have 3 parts, the event, the condition and the action • The following trigger will automatically insert the order number whenever a new order is added

  9. Triggers • BIR stands for Before Insert Row, also requires that a sequence ID_SEQUENCE has been previously defined • CREATE TRIGGER ORDER_ID_BIR • BEFORE INSERT ON ORDER_T • FOR EACH ROW • BEGIN • SELECT ID_SEQUENCE.NEXTVAL • INTO: NEW.ORDER_ID • FROM DUAL; • END ORDER_ID_BIR;

  10. Triggers • Triggers may occur either before or after the statement that aroused the trigger is executed • They may occur on INSERT, UPDATE or DELETE commands • They may fire once for each time a row is affected, or they may fire once per statement • Care should be taken when using them, since they fire automatically the user will be unaware of them • One trigger can cause another to fire, can easily end up with an endless loop of triggers

  11. Routines • Routines are Program modules that execute on demand • Functions – routines that return values and take input parameters • Procedures – routines that do not return values and can take input or output parameters

  12. Routines - example procedure • CREATE OR REPLACE PROCEDURE PRODUCT_LINE_SALE • AS BEGIN • UPDATE PRODUCT_T • SET SALE_PRICE = 0.90*STANDARD_PRICE • WHERE STANDARD_PRICE >= 400; • UPDATE PRODUCT_T • SET SALE_PRICE = 0.85*STANDARD_PRICE • WHERE STANDARD_PRICE < 400; • END (To run this procedure we would use: EXEC PRODUCT_LINE_SALE)

  13. Triggers contrasted with routines Procedures are called explicitly Triggers are event-driven

  14. Oracle PL/SQL trigger syntax SQL-99 Create routine syntax

  15. Embedded and dynamic SQL • Embedded SQL - including hard-coded SQL statements in a program written in another language such as C or Java = more efficient processing than interactive SQL • Dynamic SQL - ability for an application program to generate SQL code on the fly, as the application is running - central to many internet applications (discussed in a later lecture)

  16. SQL in practice • Following material illustrates moving from ER diagram to SQL code • Following Fig. Shows a simple ER diagram • Other slides show how to populate and query tables using SQL code

  17. Identify entities and attributes Emp Job EmpNo Name Hire Date Comm Salary Dept Location Name DeptNo

  18. Identify relationships Integrity Rules – derived from ER Diagram: Each employee may be managed by one other employee Each employee may manage one or more other employees Each employee must work in a single department Each department may be assigned one or more employees Managed by Manage Emp Dept Works in Assigned

  19. Create a relational schema Emp (EmpNo, Name, Job, Sal, Comm, HireDate, Mgr, DeptNo) Dept (DeptNo, Name, Location) Dept Emp EmpNoNumber(4) Name Varchar2(10) Job Varchar2(9) Sal Number(7,2) Comm Number(7,2) HireDate Date Mgr Number(4) DeptNo Number(2) DeptNoNumber(2) Name Varchar2(14) Location Varchar2(13)

  20. Create relational tables create tableDept (deptno number(2), name varchar2(14), location varchar2(13), constraint DeptPK primary key (deptno)); create tableEmp (empno number(4), name varchar2(10), job varchar2(9), sal number(7,2), comm number(7,2), hiredate date, mgr number(4), deptno number(2), constraint EmpPK primary key (empno), constraint EmpFK1 foreign key (mgr) references Emp, constraint EmpFK2 foreign key (deptno) references Dept); To create a relation in SQL the following ‘Create Table’ command is required: create table R (A1 D1, A2 D2, … An Dn, integrity constraint1, integrity constarint2) Where: R = Relation (table) name A = Attribute name D = Attribute domain

  21. Populate relational tables insert into Dept(deptno, name, location) values (10, ‘Accounting’, ‘New York’) The insert order matters in terms of referential integrity constraints! insert into Dept (deptno, name, location) values (30, ‘Sales’, ‘Chicago) insert into Emp (empno, name, job, sal, comm, hiredate, mgr, deptno) values (7839, ‘King’, ‘President’, 5000, NULL, ‘17-Nov-81’, NULL, 10) insert into Emp(empno, name, job, sal, comm, hiredate, mgr, deptno) values (7698, ‘Blake’, ‘Manager’, 1600, NULL, ’01-May-81’, 7839, 30) To create a tuple in SQL the following ‘Insert’ command is required: insertinto R (attribute1, attribute2, … attributen ) values (value1, value2, … valuen)

  22. Query relational tables 7839 King 10 7698 Blake 30 To query a relation in SQL the following ‘Select’ command is required: SELECT [ALL | DISTINCT] attribute1, attribute2, … attributen FROMrelation1, relation2, … relationn [WHEREcondition-expression] [GROUP BYattribute1, attribute2, … attributen] [HAVINGcondition-expression] [ORDER BYattribute1, attribute2, … attributen] Simple Example: list all Employees and the departments they work in select empno, name, deptno from Emp;

  23. Query relational tables Simple Example: list all Employees that work in department 30 select empno, name from Emp where deptno = 30; 7698 Blake Simple Example: list all Employees that work in either department 10 or 30 7839 King 7698 Blake select empno, name from Emp where deptno = 10 or deptno = 30;

  24. Query relational tables - Join Example: list Employee and Department names of all employees that work in either department 10 or 30 select emp.name, dept.name from Emp, Dept where (emp.deptno = 10 or emp.deptno = 30); King Accounting King Sales Blake Accounting Blake Sales select emp.name, dept.name from Emp, Dept where (emp.deptno = dept.deptno) and (emp.deptno = 10 or emp.deptno = 30) King Accounting Blake Sales

  25. Query relational tables – Order by select emp.name, dept.name from Emp, Dept where (emp.deptno = dept.deptno) and (emp.deptno = 10 or emp.deptno = 30) order byemp.nameasc; select emp.name, dept.name from Emp, Dept where (emp.deptno = dept.deptno) and (emp.deptno = 10 or emp.deptno = 30) order bydept.namedesc; Blake Sales King Accounting Blake Sales King Accounting Remember in relations neither tuples nor attributes have any intrinsic order! select name from Dept order byname; Accounting Sales

  26. Example relations EMPNO NAME JOB MGR HIREDATE SAL COMM DEPTNO 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10 DEPTNO NAME LOCATION 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON Emp Dept

  27. Query relational tables - Outer Join Accounting Clark Accounting King Accounting Miller Operations Research Adams Research Ford Research Jones Research Scott Research Smith Sales Allen Sales Blake Sales James Sales Martin Sales Turner Sales Ward select dept.name, emp.name from Emp, Dept where (emp.deptno (+) = dept.deptno) order bydept.name, emp.name; Example: list all departments and the names of staff that work in them.

  28. Query relational tables – Group by Example: List employee’s departments giving a count of employees in each. 10 10 10 20 20 20 20 20 30 30 30 30 30 30 select deptno from Emp order bydeptno; select deptno from Emp group bydeptno; 10 20 30 Aggregate Function! select deptno, count(*) from Emp group bydeptno; 10 3 20 5 30 6 Select queries can contain functions and calculations as well as attribute names in the select condition!

  29. Query relational tables – Group by 20 5 30 6 Example: List employee’s departments giving a count of employees in each, provided that there are over four employees in the department. The having clause is used in Group Selections in the same way that the where clause is used in standard tuple selections. select deptno, count(*) from Emp group by deptno having count(*) > 4; select deptno, sum(sal), sum(sal)/count(*) from Emp group bydeptno; 10 8750 2916.6667 20 10875 2175 30 9400 1566.6667 As we are dealing with groups of tuples, rather than individual tuples, there are only certain types of data that can be selected:1)attributes named in the group by clause;2)group aggregate functions;3)expressions involving combinations of 1) and 2).

  30. Query relational tables – Group by Combined Example: List, in reverse order of average department salary, the total salary and average salary for each department that has employees, and where the average salary of each department is over £2000 select deptno, sum(sal), sum(sal)/count(*) from Emp group by deptno having sum(sal)/count(*) > 2000 order by sum(sal)/count(*) desc; 10 8750 2916.6667 20 10875 2175 The result of an SQL Select is, as shown, a new (unnamed) relation. The attributes of this new relation are named on the basis of the select statement. DEPTNO SUM(SAL) SUM(SAL)/COUNT(*) DEPTNO Total Salary Avg Salary 10 8750 2916.6667 20 10875 2175 10 8750 2916.6667 20 10875 2175 ALIAS:- select deptno, sum(sal)“Total Salary”,sum(sal)/count(*)“Avg Salary”

  31. Query relational tables – Inner Selects As the result of an SQL Select on a relation(s) is itself a relation, it follows that, as with Relational Algebra, the result of one select can be used as the input into another SQL Select! However, rather than create, and name, a separate relation to contain the output of the first select and then use this relation in the second select, it is possible to ‘pipe’ the output of the first select directly into the second select! Example: List employee’s who earn more than the average company salary. select name, sal from Emp where sal > (select AVG(sal) from Emp) order by name; NAME SAL BLAKE 2850 CLARK 2450 FORD 3000 JONES 2975 KING 5000 SCOTT 3000 2 1 2073.2143, is substituted for this select statement

  32. Query relational tables – Inner Selects Remember: Inner Selects (also called sub-selects or sub-queries) are full-bodied SQL Select statements: Therefore, they can, when required to do so, return more than a single value (one tuple, one attribute) relation. Example: List employees who earn the maximum salary in each of the departments. selectname, deptno, sal fromEmp where (deptno, sal)in(select deptno, max(sal) from emp group by deptno) order by deptno; NAME DEPT SAL KING 10 5000 SCOTT 20 3000 FORD 20 3000 BLAKE 30 2850 DEPT MAX(SAL) 10 5000 20 3000 30 2850 This relation is substituted for the inner select statement

  33. Query relational tables – Inner Selects Note: It is sometimes necessary to make reference to an attribute value from the outer select, within the Where Clause of the inner select. This can be achieved by using a Relation Alias! Example: List employee’s who earn more than the average salary in the department they work in. Relation Alias NAME DEPT SAL KING 10 5000 JONES 20 2975 SCOTT 20 3000 FORD 20 3000 ALLEN 30 1600 BLAKE 30 2850 selectname, deptno, sal fromEmp E1 where sal > (select AVG(sal) from Emp where deptno = E1.deptno group by deptno) order by deptno; The deptno attribute value of the current tuple will be substituted here! If there are 14 employee tuples there will be 14 separate substitutions (even though there are only three departments)!

  34. SQL set operations – Union DepB DepA staffno dob SL10 14-02-64 SA51 21-11-82 DS40 01-01-40 CC15 11-03-66 staffno dob CC15 11-03-66 SA51 21-11-82 staffno dob SL10 14-02-64 SA51 21-11-82 DS40 01-01-40  Example: list all staff that work in either of two departments (each dept. has a separate database), showing their staff number, and date of birth. selectstaffno, dob fromDepA UNION selectstaffno, dob fromDepB;

  35. SQL set operations – Intersection staffno dob SA51 21-11-82 Example: list all staff that work in both departments (each dept. has a separate database), showing their staff number, and date of birth. DepB DepA staffno dob CC15 11-03-66 SA51 21-11-82 staffno dob SL10 14-02-64 SA51 21-11-82 DS40 01-01-40  selectstaffno, dob fromDepA INTERSECT selectstaffno, dob fromDepB;

  36. SQL set operations – difference staffno dob SL10 14-02-64 DS40 01-01-40 Example: list all staff that only work in department A (each dept. has a separate database), showing their staff number, and date of birth. DepB DepA staffno dob CC15 11-03-66 SA51 21-11-82 staffno dob SL10 14-02-64 SA51 21-11-82 DS40 01-01-40 selectstaffno, dob fromDepA MINUS selectstaffno, dob fromDepB;

  37. SQL - Group Insert statement Example: copy details of all employees that work in department 10 from the Emp relation into the DepA relation. corresponding attributes have to be of the same type insert into DepA (staffno, name, job, hiredate) select empno, name, job, hiredate from Emp where deptno = 10; Each tuple to be inserted has to be unique! To create a set of tuples in SQL the following ‘Insert’ command can be used: insert into R (attribute1, attribute2, … attributen ) select (attribute1, attribute2, … attributen) from relation1, relation2, … relationn [where condition-expression] [group by attribute1, attribute2, … attributen ] [having condition-expression] [order by attribute1, attribute2, … attributen ]

  38. SQL - Delete statement Example: remove details of all employees that work in department 10 from the Emp relation. If the where clause is omitted then all tuples in the relation will be removed! Delete from Emp wheredeptno = 10; To delete a set of tuples in SQL the following ‘Delete’ command is used: delete from R [where condition-expression]

  39. SQL - Update statement Example: increase the salary of all employees that work in department 10 by 10%. update Emp setsal = sal *1.1 wheredeptno = 10; If the where clause is omitted then all tuples in the relation will be altered! To alter a set of tuples in SQL the following ‘Update’ command is used: update R set attribute1 = datavalue1, attribute2 = datavalue2, ... attributen = datavaluen [where condition-expression]

More Related