Database Systems - Lec 6, 7: SQL (Structured Query Language) - Nguyen Thanh Tung

Nội dung text: Database Systems - Lec 6, 7: SQL (Structured Query Language) - Nguyen Thanh Tung

1. Outline ▪ The COMPANY Database ▪ SQL developments: an overview ▪ SQL • DDL: Create, Alter, Drop • DML: select, insert, update, delete • DCL: commit, rollback, grant, revoke ▪ Reading Suggestion: • [1]: Chapters 8, 9 • [3]: All • 2
2. SQL developments: an overview ▪ In 1986, ANSI and ISO published an initial standard for SQL: SQL-86 or SQL1 ▪ In 1992, first major revision to ISO standard occurred, referred to as SQL2 or SQL-92 ▪ In 1999, SQL-99 (SQL3) was released with support for object-oriented data management ▪ In late 2003, SQL-2003 was released ▪ Now: SQL-2006 was published 4
3. Outline ▪ The COMPANY Database ▪ SQL developments: an overview ▪ SQL • DDL: Create, Alter, Drop • DML: select, insert, update, delete • DCL: commit, rollback, grant, revoke ▪ Reading Suggestion: • [1]: Chapters 8, 9 • [3]: All • Dr. Dang Tran Khanh, Faculty of CSE, HCMUT (dtkhanh@hcmut.edu.vn) 6
4. DDL: Create, Alter, Drop CREATE TABLE ▪ CREATE TABLE Company.TableName or ▪ CREATE TABLE TableName 8
5. DDL: Create, Alter, Drop CREATE TABLE ▪ DataType • Numeric: INT or INTEGER, FLOAT or REAL, DOUBLE PRECISION, • Character string: fixed length CHAR(n), varying length VARCHAR(n) • Bit string: BIT(n), e.g. B’1001’ • Boolean: true, false or NULL • Date, Time: DATE ‘YYYY-MM-DD’, TIME ‘HH:MM:SS’ • TIMESTAMP: date + time + ▪ CREATE DOMAIN DomainName AS DataType [CHECK conditions]; 10
6. Defining the COMPANY DB schema (1) , 12
7. DDL: Create, Alter, Drop CREATE TABLE ▪ Default values • DEFAULT can be specified for an attribute • If no default clause is specified, the default value is NULL for attributes that do not have the NOT NULL constraint →If NOT NULL option is specified on attribute A and no value is specified as inserting a tupe r( A ) ?? • CHECK clause: DNUMBER INT NOT NULL CHECK (DNUMBER>0 AND DNUMBER 0 AND D_NUM<21); 14
8. DDL: Create, Alter, Drop CREATE TABLE ▪ Primary key and referential integrity constraints • If a PK has a single attribute: DNUMBER INT PRIMARY KEY; • Referential integrity: FOREIGN KEY (list_of_attr) • When are referential integrity constraints violated ?? Default action ?? • The schema designer can add a referential triggered action clause to any FK constraint: ON DELETE ON UPDATE : SET NULL, CASCADE, SET DEFAULT 16
9. DDL: Create, Alter, Drop CREATE TABLE ▪ Giving names to constraints • This is optional • The name is unique within a particular DB schema • Used to identify a particular constraint in case it must be dropped later and replaced with another one 18
10. DDL: Create, Alter, Drop CREATE TABLE ▪ Specifying constraints on tuples using CHECK • Affected on each tuple individually as being inserted or modified (tuple-based constraints) • Dept. create date must be earlier than the manager’s start date: CHECK (DEPT_CREATE_DATE < MGRSTARTDATE); • More general constraints: CREATE ASSERTION 20
11. DDL: Create, Alter, Drop DROP Command ▪ Drop a table: DROP TABLE Dependent CASCADE; (RESTRICT) • RESTRICT option: dropped on if it is not referenced in any constraints or views • CASCADE option: all such constraints and views that reference the table are dropped automatically from the schema along with the table itself ▪ Similarly, we can drop constraints & domains 22
12. DDL: Create, Alter, Drop ALTER Command ▪ Drop a column: similarly to drop a table, CASCADE or RESTRICT option must be specified • CASCADE option: all constraints and views referencing the column are dropped along with the column • RESTRICT option: successful only if no constraints and views are referencing the column E.g., ALTER TABLE Company.Employee DROP Address CASCADE; ▪ Much more details: see [1, 2] & the Web 24
13. DML: Select, Insert, Update, Delete SELECT ▪ Basic form of the SQL SELECT statement is called a mapping or a SELECT-FROM-WHERE block SELECT FROM WHERE • is a list of attribute names whose values are to be retrieved by the query • is a list of the relation names required to process the query • is a conditional (Boolean) expression that identifies the tuples to be retrieved by the query 26
14. DML: Select, Insert, Update, Delete SELECT ▪ SELECT Specifies which columns are to appear in output ▪ FROM Specifies table(s) to be used ▪ WHERE Filters rows ▪ GROUP BY Forms groups of rows with same column value ▪ HAVING Filters groups subject to some condition ▪ ORDER BY Specifies the order of the output 28
15. DML: Select, Insert, Update, Delete SELECT ▪ Basic SQL queries correspond to using the SELECT, PROJECT, and JOIN operations of the relational algebra ▪ Query 0: Retrieve the birthdate and address of the employee whose name is 'John B. Smith'. Q0: SELECT BDATE, ADDRESS FROM EMPLOYEE WHERE FNAME='John' AND MINIT='B’ AND LNAME='Smith’ • Similar to a SELECT-PROJECT pair of relational algebra operations; the SELECT-clause specifies the projection attributes and the WHERE-clause specifies the selection condition • However, the result of the query may contain duplicate tuples 30
16. The COMPANY Database 32
17. DML: Select, Insert, Update, Delete SELECT Q2:SELECT PNUMBER, DNUM, LNAME, BDATE,ADDRESS FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE DNUM=DNUMBER AND MGRSSN=SSN AND PLOCATION='Stafford' ▪ There are 2 join conditions: • The join condition DNUM=DNUMBER relates a project to its controlling department • The join condition MGRSSN=SSN relates the controlling department to the employee who manages that department 34
18. Outline ▪ The COMPANY Database ▪ SQL developments: an overview ▪ SQL • DDL: Create, Alter, Drop • DML: select, insert, update, delete • DCL: commit, rollback, grant, revoke ▪ Reading Suggestion: • [1]: Chapters 8, 9 • [3]: All • 36
19. Aliases ▪ Some queries need to refer to the same relation twice: aliases are given to the relation name ▪ Query 3: For each employee, retrieve the employee's name, and the name of his or her immediate supervisor. Q3: SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME FROM EMPLOYEE E S WHERE E.SUPERSSN=S.SSN • The alternate relation names E and S are called aliases or tuple variables for the EMPLOYEE relation • We can think of E and S as two different copies of EMPLOYEE; E represents employees in role of supervisees and S represents employees in role of supervisors 38
20. Unspecified WHERE-clause ▪ A missing WHERE-clause indicates no condition; hence, all tuples of the relations in the FROM-clause are selected ▪ This is equivalent to the condition WHERE TRUE ▪ Query 5: Retrieve the SSN values for all employees Q5: SELECT SSN FROM EMPLOYEE 40
21. Use of ASTERISK (*) ▪ An asterisk (*) stands for all the attributes ▪ Examples: Q7: SELECT * FROM EMPLOYEE WHERE DNO=5 Q8: SELECT * FROM EMPLOYEE, DEPARTMENT WHERE DNAME='Research' AND DNO=DNUMBER 42
22. Set Operations ▪ Set union (UNION), set difference (EXCEPT) and set intersection (INTERSECT) operations ▪ The resulting relations of these set operations are sets of tuples: duplicate tuples are eliminated from the result ▪ The set operations apply only to union compatible relations ▪ UNION ALL, EXCEPT ALL, INTERSECT ALL ?? 44
23. Substring pattern matching and arithmetic operators ▪ Two reserved characters: % and _ Q11: SELECT * FROM Employee WHERE Address LIKE ‘%HCMC%’ Q12: SELECT * FROM Employee WHERE BDate LIKE ‘_ _8_ _ _ _ _ _ _’ 46
24. Outline ▪ The COMPANY Database ▪ SQL developments: an overview ▪ SQL • DDL: Create, Alter, Drop • DML: select, insert, update, delete • DCL: commit, rollback, grant, revoke ▪ Reading Suggestion: • [1]: Chapters 8, 9 • [3]: All • 48
25. Nested Queries ▪ A complete SELECT query, called a nested query , can be specified within the WHERE-clause of another query, called the outer query ▪ Query 14: Retrieve the name and address of all employees who work for the 'Research' department Q14:SELECT FNAME, LNAME, ADDRESS FROM EMPLOYEE WHERE DNO IN (SELECT DNUMBER FROM DEPARTMENT WHERE DNAME='Research' ) 50
26. The COMPANY Database 52
27. Nested Query Exercises ▪ Query 16: Retrieve the SSNs of all employees who work the same (project, hours) combination on some project that employee John Smith (SSN=123456789) works on (using a nested query) Q16: SELECT DISTINCT ESSN FROM Works_on WHERE (PNO, HOURS) IN (SELECT PNO, HOURS FROM Works_on WHERE ESSN=‘123456789’) 54
28. The EXISTS Function ▪ EXISTS is used to check if the result of a correlated nested query is empty (contains no tuples) ▪ Query 15: Retrieve the name of each employee who has a dependent with the same first name as the employee Q15B: SELECT E.FNAME, E.LNAME FROM EMPLOYEE WHERE EXISTS (SELECT * FROM DEPENDENT WHERE SSN=ESSN AND FNAME=DEPENDENT_NAME) 56
29. Enumerated Sets ▪ It is also possible to use an explicit (enumerated) set of values in the WHERE-clause rather than a nested query ▪ Query 19: Retrieve the SSNs of all employees who work on project numbers 1, 2, or 3. Q19: SELECT DISTINCT ESSN FROM WORKS_ON WHERE PNO IN (1, 2, 3) 58
30. Joined Relations Feature in SQL2 ▪ Examples: SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME FROM EMPLOYEE E S WHERE E.SUPERSSN=S.SSN can be written as: SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME FROM (EMPLOYEE E LEFT OUTER JOIN EMPLOYEE S ON E.SUPERSSN=S.SSN) ▪ Any differences ?? 60
31. Joined Relations Feature in SQL2 ▪ Query 2: For every project located in 'Stafford', list the project number, the controlling department number, and the department manager's last name, address, and birthdate ▪ Q2 could be written as follows; this illustrates multiple joins in the joined tables SELECT PNUMBER, DNUM, LNAME, BDATE, ADDRESS FROM ((PROJECT JOIN DEPARTMENT ON DNUM= DNUMBER) JOIN EMPLOYEE ON MGRSSN=SSN)) WHERE PLOCATION='Stafford’ 62
32. AGGREGATE FUNCTIONS ▪ Queries 21 and 22: Retrieve the total number of employees in the company (Q17), and the number of employees in the 'Research' department (Q18) Q21:SELECT COUNT (*) FROM EMPLOYEE Q22:SELECT COUNT (*) FROM EMPLOYEE, DEPARTMENT WHERE DNO=DNUMBER AND DNAME='Research’ ▪ Note: NULL values are discarded wrt. aggregate functions as applied to a particular column 64
33. GROUPING ▪ Query 23: For each department, retrieve the department number, the number of employees in the department, and their average salary Q23: SELECT DNO, COUNT (*), AVG (SALARY) FROM EMPLOYEE GROUP BY DNO • In Q23, the EMPLOYEE tuples are divided into groups each group having the same value for the grouping attribute DNO • The COUNT and AVG functions are applied to each such group of tuples separately • The SELECT-clause includes only the grouping attribute and the functions to be applied on each group of tuples • A join condition can be used in conjunction with grouping 66
34. GROUPING: THE HAVING-CLAUSE ▪ Sometimes we want to retrieve the values of these functions for only those groups that satisfy certain conditions ▪ The HAVING-clause is used for specifying a selection condition on groups (rather than on individual tuples) 68
35. ORDER BY ▪ The ORDER BY clause is used to sort the tuples in a query result based on the values of some attribute(s) ▪ Query 25: Retrieve a list of employees and the projects each works in, ordered by the employee's department, and within each department ordered alphabetically by employee last name Q25: SELECT DNAME, LNAME, FNAME, PNAME FROM DEPARTMENT, EMPLOYEE, WORKS_ON, PROJECT WHERE DNUMBER=DNO AND SSN=ESSN AND PNO=PNUMBER ORDER BY DNAME, LNAME [DESC|ASC] 70
36. DML: Select, Insert, Update, Delete SELECT ▪ SELECT Specifies which columns are to appear in output ▪ FROM Specifies table(s) to be used ▪ WHERE Filters rows ▪ GROUP BY Forms groups of rows with same column value ▪ HAVING Filters groups subject to some condition ▪ ORDER BY Specifies the order of the output 72
37. Outline ▪ The COMPANY Database ▪ SQL developments: an overview ▪ SQL • DDL: Create, Alter, Drop • DML: select, insert, update, delete • DCL: commit, rollback, grant, revoke ▪ Reading Suggestion: • [1]: Chapters 8, 9 • [3]: All • 74
38. DML: Select, Insert, Update, Delete Insert ▪ Example: U1: INSERT INTO EMPLOYEE VALUES ('Richard','K','Marini', '653298653', '30-DEC-52', '98 Oak Forest,Katy,TX', 'M', 37000,'987654321', 4) ▪ An alternate form of INSERT specifies explicitly the attribute names that correspond to the values in the new tuple, attributes with NULL values can be left out ▪ Example: Insert a tuple for a new EMPLOYEE for whom we only know the FNAME, LNAME, and SSN attributes. U2: INSERT INTO EMPLOYEE (FNAME, LNAME, SSN) VALUES ('Richard', 'Marini', '653298653') 76
39. DML: Select, Insert, Update, Delete Insert ▪ Example: Suppose we want to create a temporary table that has the name, number of employees, and total salaries for each department. A table DEPTS_INFO is created by U3, and is loaded with the summary information retrieved from the database by the query in U3A U3:CREATE TABLE DEPTS_INFO (DEPT_NAME VARCHAR(10), NO_OF_EMPS INTEGER, TOTAL_SAL INTEGER); U3A:INSERT INTO DEPTS_INFO (DEPT_NAME, NO_OF_EMPS, TOTAL_SAL) SELECT DNAME, COUNT (*), SUM (SALARY) FROM DEPARTMENT, EMPLOYEE WHERE DNUMBER=DNO GROUP BY DNAME; 78
40. DML: Select, Insert, Update, Delete Delete ▪ Examples: U4A: DELETE FROM EMPLOYEE WHERE LNAME='Brown’ U4B: DELETE FROM EMPLOYEE WHERE SSN='123456789’ U4C: DELETE FROM EMPLOYEE WHERE DNO IN (SELECT DNUMBER FROM DEPARTMENT WHERE DNAME='Research') U4D: DELETE FROM EMPLOYEE 80
41. DML: Select, Insert, Update, Delete Update ▪ Example: Change the location and controlling department number of project number 10 to 'Bellaire' and 5, respectively. U5: UPDATE PROJECT SET PLOCATION = 'Bellaire', DNUM = 5 WHERE PNUMBER=10 82
42. Advanced DDL: Assertions & Triggers ▪ ASSERTIONs to express constraints that do not fit in the basic SQL categories ▪ Mechanism: CREATE ASSERTION • components include: a constraint name, followed by CHECK, followed by a condition 84
43. Advanced DDL: Assertions & Triggers ▪ Triggers: to specify the type of action to be taken as certain events occur & as certain conditions are satisfied ▪ Details of triggers: chapter 24 [1], [3], and Oracle’s website 86
44. VIEWs ▪ SQL command: CREATE VIEW • a view (table) name • a possible list of attribute names • a query to specify the view contents ▪ Specify a different WORKS_ON table (view) CREATE VIEW WORKS_ON_NEW AS SELECT FNAME, LNAME, PNAME, HOURS FROM EMPLOYEE, PROJECT, WORKS_ON WHERE SSN=ESSN AND PNO=PNUMBER GROUP BY PNAME; 88
45. DCL: Commit, Rollback, Grant, Revoke ▪ Chapter 17: Transaction Processing ▪ Chapter 23: DB security 90
46. Q&A 92