Structured Query Language

1. Structured Query Language Agenda: - SQL commands for DB definition - SQL DML commands - VIEWS and security control using SQL

2. CREATE TABLE command - Table name - Description of the attributes: -- Attribute name -- Data type -- Constraints on the attribute values, including: --- NULL or NOT NULL --- Domain of attribute --- Referential constraints on the attribute - Primary key attributes - Foreign key attributes

3. Table name Data type Domain constraint Attribute name Default PK composed of two attributes CREATE TABLE, example 1 CREATE TABLE Book ( isbn VARCHAR(15) NOT NULL, title VARCHAR(200) NOT NULL, catalog_no VARCHAR(15) NOT NULL, copy_no INT NOT NULL DEFAULT 1, keywords CHAR(100) NULL, purchase_date DATE NULL, PRIMARY KEY CLUSTERED(catalog_no, copy_no))

4. Referential constraints CREATE TABLE, example 2 CREATE TABLE Borrows ( catalog_num VARCHAR(15) NOT NULL, copy_num INT NOT NULL, issue_date DATE NOT NULL, person_id CHAR(8) NOT NULL, PRIMARY KEY CLUSTERED(catalog_num, copy_num, person_id, issue_date), CONSTRAINT fk_borrows_book FOREIGN KEY(catalog_num, copy_num) REFERENCES Books(catalog_no, copy_no), CONSTRAINT fk_borrows_person FOREIGN KEY(person_id) REFERENCES Person( id) )

5. Domain constraint CREATE TABLE, example 3 Key (but not PK) CREATE TABLE Person ( lname VARCHAR(35) NOT NULL, fnames VARCHAR(50) NOT NULL, email VARCHAR(60) NOT NULL UNIQUE CHECK ( email LIKE ‘%@%’), id CHAR(8) NOT NULL, phone CHAR(12) NULL, PRIMARY KEY (id) )

6. DROP TABLE command DROP TABLE Person; Problem: Borrows( person_id) REFERENCES Person( id) DROP TABLE Person CASCADE; Step 1. Referential constraints are deleted Step 2. All data in the table is deleted Step 3. The definition of the table is deleted from the DB.

7. ALTER TABLE command - Add a new column in a table - Delete a column from a table - Add/Delete a constraint specified on a table Initial design Example 1 ALTER TABLE Person ADD fines FLOAT; ALTER-ed design

8. ALTER TABLE command… Example 2: add a new attribute ALTER TABLE Book ADD category VARCHAR(10) NOT NULL DEFAULT “normal” CHECK (category in (“normal”, “reserve”, “media”)); Example 3: drop a constraint ALTER TABLE Borrows DROP CONSTRIANT fk_borrows_person; Example 4: add a constraint ALTER TABLE Borrows ADD CONSTRAINT fk_borrows_person FOREIGN KEY(person_id) REFERENCES Person( id); Q: What happens to data entered in a table BEFORE adding a constraint?

9. INSERT INTO command, populating a DB Example 1: INSERT INTO Person VALUES ( ‘Bush’, ‘George W.’, ‘dubya@white.house’, ‘09112001’, NULL, 0); Example 2: INSERT INTO Book VALUES ( ‘0321122267’, ‘Fundamentals of Database Systems’, ‘QA76.9.D3’, 1, ‘Databases’, ‘2004-09-25’);

10. DELETE FROM command - to delete one or more rows of data from a table Example 1: DELETE FROM Person WHERE id= ‘09112001’; Example 2: DELETE FROM Person WHERE lname=’Bush’; Example 3: DELETE FROM Borrows WHERE 1

11. DELETE FROM command … Example 4: DELETE FROM Borrows WHERE person_id IN (“09112001”, “55554444”, “12345678”); Example 5: DELETE FROM Borrows WHERE person_id IN ( SELECT id FROM Person WHERE lname= ‘Bush’);

12. UPDATE command - Modify the value of one or more cells in a table Function provided by SQL Example 1: UPDATE Borrows SET issue_date=CURRENT_DATE( ) WHERE person_id=’09112001’; SQL allows the use of arithmetic expressions Example 2: UPDATE Person SET fines= fines*2.0 WHERE id=’09112001’;

13. SELECT command - Output required information from one or more tables For the following examples (unless stated otherwise): - Use the EMPLOYEE-DEPARTMENT-PROJECTS database - Assume the initial data in the tables as provided earlier

14. SELECT command (1) Example 1: Report the birth date and address of employee named "John Smith" SELECT BDate, Address FROM EMPLOYEE WHERE Fname = ‘John’ AND Lname = ‘Smith’;

15. SELECT command (1a) Example 1a: Report the SSN of Employees who spend more than 15 hours on some project. SELECT ESSN FROM WORKS_ON WHERE Hours > 15; SELECT DISTINCT ESSN FROM WORKS_ON WHERE Hours > 15;

16. SELECT command (2) Example 2: Report the Name and address of employees working in the “Research” department. SELECT Fname, Lname, Address FROM EMPLOYEE, DEPARTMENT WHERE Dname = ‘Research’ AND Dnumber = Dno

17. SELECT command (3) Example 3: For each project located in Stafford, list the project number, the controlling department, and the department manager's last name and address. SELECT Pnumber, Dnum, Lname, Address FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE Dnum = Dnumber AND MgrSSN = SSN AND Plocation = ‘Stafford’;

18. SELECT command (4): Alias and Dot-notation Example 4:For each employee, give the last name, and the last name of his/her supervisor. SELECT E.Lname, S.Lname FROM EMPLOYEE AS E, EMPLOYEE AS S WHERE E.SuperSSN = S.SSN

19. SELECT command (5, 6): no WHERE Example 5: Print SSN of all employees. SELECT SSN FROM EMPLOYEE SELECT SSN, Dname FROM EMPLOYEE, DEPARTMENT How many rows in output ?

20. SELECT command (7): wildcard Example 7:Show the EMPLOYEE table SELECT * FROM EMPLOYEE Example 7a:Report Department information including locations SELECT DEPT_LOCATION.*, DEPARTMENT.Dname FROM DEPT_LOCATION, DEPARTMENT WHERE DEPT_LOCATION.Dnumber = DEPARTMENT.Dnumber

21. SELECT command (8): UNION Example 8: List all projects which either use employee "Wong", or are controlled by a department managed by "Wong". (SELECT Pname FROM PROJECT, WORKS_ON, EMPLOYEE WHERE Pnumber = PNo AND ESSN = SSN AND LName = 'Wong' ) UNION (SELECT Pname FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE DNum = Dnumber AND SSN = MgrSSN AND LName = 'Wong');

22. uncorrelated SELECT command (9): nested queries Example 9:Report the name and address of all employees working in the 'Research' department. SELECT Fname, Lname, Address FROM EMPLOYEE WHERE Dno IN ( SELECT Dnumber FROM DEPARTMENT WHERE Dname = 'Research' )

23. correlated SELECT command (10): nested queries Example 10: Get the names of all employees who have a dependent with the same first name. SELECT E.Fname, E.Lname FROM EMPLOYEE AS E WHERE E.SSN IN ( SELECT ESSN FROM DEPENDENT WHERE ESSN = E.SSN AND E.Fname = DependentName )

24. SELECT command (11): EXISTS operator Example 11: Get names of employees who work for at least one project. SELECT Fname, Lname FROM EMPLOYEE WHERE EXISTS ( SELECT * FROM WORKS_ON WHERE SSN = ESSN )

25. SELECT command (12): NOT EXISTS Example 12: Find names of employees who do not work for even one project. SELECT Fname, Lname FROM EMPLOYEE WHERE NOT EXISTS ( SELECT * FROM WORKS_ON WHERE SSN = ESSN )

26. matches zero or more chars matching operator SELECT command (13): matching CHAR(n) types Example 13: Find names of all Employees who live on Fonden street. SELECT Lname FROM EMPLOYEE WHERE Address LIKE ‘%Fonden%’; NOTES: (1) ‘_’ matches exactly one char (2) RLIKE operator allows REG_EXP

27. SELECT command (14): aggregates Example 14: Get the minimum, maximum, average and total salaries for employees of the Research department. SELECT sum(Salary), max( Salary), min( Salary), avg( Salary) FROM EMPLOYEE, DEPARTMENT WHERE Dno = Dnumber AND Dname = 'Research' SELECT sum(Salary) AS Tot, max( Salary) AS Max, min( Salary) AS Min, avg( Salary) AS Mean FROM EMPLOYEE, DEPARTMENT WHERE Dno = Dnumber AND Dname = 'Research'

28. SELECT command (15): GROUP BY Example 15: For departments other than Headquarters, get the Dno, the No. of employees in that department, and their average salary. SELECT Dno, count(*) AS HeadCount, avg(Salary) AS MeanSalary FROM EMPLOYEE, DEPARTMENT WHERE Dno = Dnumber AND Dname <> 'Headquarters' GROUP BY Dno;

29. SELECT command (16): GROUP BY .. HAVING .. Example 16: For ‘Large’ departments other than Headquarters, get the Dno, the No. of employees in that department, and their average salary. SELECT Dno, count(*) AS HeadCount, avg(Salary) AS MeanSalary FROM EMPLOYEE, DEPARTMENT WHERE Dno = Dnumber AND Dname <> 'Headquarters' GROUP BY Dno HAVING HeadCount > 3;

30. SELECT command (17): Mathematical operators Example 17: Display the result of a 10% increase in Salary of employees whose Last name starts with "B". SELECT Lname, 1.1 * Salary AS IncreasedSalary FROM EMPLOYEE WHERE Lname LIKE 'B%'

31. SELECT command (18): sorting Example 18: Report names and salaries of employees, in descending order by salary SELECT Lname, Salary FROM EMPLOYEE ORDER BY Salary DESC

32. VIEWS VIEW: A virtual table derived from a set of existing tables. Main uses of a view: - Security (selective display of information to different users) - Ease-of-use -- Explicit display of derived attributes -- Explicit display of related information from different tables -- Intermediate table can be used to simplify SQL query

33. CREATE VIEW command (1) Example 1: Create a view showing the names of employees, which project they work on, and how many hours they spend on each project. CREATE VIEW EMP_WORKS_ON AS SELECT Fname, Lname, Pname, Hours FROM EMPLOYEE, PROJECT, WORKS_ON WHERE SSN = ESSN AND Pno = Pnumber; SELECT * FROM EMP_WORKS_ON

34. VIEWS: mechanism of storage/updates 1. Onlydefinition of viewis stored in memory 2. Data for a view is only generated when the query is processed 3. Update a view attribute data in the underlying table is updated

35. VIEWS: mechanism of storage/updates.. Example 2: What happens to employee hours if they work one-shift overtime? UPDATE EMP_WORKS_ON SET Hours = Hours * 1.5 SELECT * FROM WORKS_ON

36. VIEWS: mechanism of storage/updates… Example 3: John Smith, currently working on ‘ProductX’ project, is reassigned to ‘ProductY’ project. Example 3 (incorrect): UPDATE EMP_WORKS_ON SET Pname = 'ProductY' WHERE Lname = 'Smith' AND Pname = 'ProductX' Problem ? Example 3 (correct): UPDATE WORKS_ON SET Pno = (SELECT Pnumber FROM PROJECTS WHERE Pname = 'ProductY') WHERE ESSN = ( SELECT SSN FROM EMPLOYEE WHERE Lname = 'Smith') AND Pno = ( SELECT Pnumber FROM PROJECT WHERE Pname = 'ProductX');

37. VIEWS: using derived attributes CREATE VIEW DEPT_INFOAS SELECT DName, count(*) AS NumEmps, sum( Salary) AS TotalSalary FROM DEPARTMENT, EMPLOYEE WHERE DNumber = DNo GROUP BY DName; NOTE: - you cannot UPDATE a computed attribute

38. VIEWS, GRANT: security control Example 5: Allow user U1 to see/modify all Employee data except Salaries. CREATE VIEW EMP_PERSONNEL AS SELECT Fname, Minit, Lname, SSN, BDate, Address, Sex, SuperSSN, Dno FROM EMPLOYEE; 1 GRANT SELECT, UPDATE ON EMP_PERSONNEL to U1; OR 2 GRANT SELECT, UPDATE ON EMP_PERSONNEL TO U1 WITH GRANT OPTION; U1 > GRANT SELECT ON EMP_PERSONNEL TO U2;

39. GRANT, REVOKE commands Disallow U2 from seeing anything in EMP_PERSONNEL: REVOKE SELECT ON EMP_PERSONNEL FROM U2; GRANT , REVOKE can refer to individual attributes Allow U3 to change Salary in EMPLOYEE table: GRANT UPDATE ON EMPLOYEE( Salary) TO U3;

40. Concluding remarks GRANT on SELECT  use VIEW or TABLE GRANT on INSERT, DELETE, UPDATE  use TABLE Next topic: Indexes