SQL

1. SQL • SQL (Structured Query Language) is used to define, query, and modify relational databases • Every relational database system understands SQL • SQL is standard: the same SQL statements can be executed without modifications on many database systems

2. History of SQL • Developed by IBM as SEQUEL in early 70s (Structured English Query Language) • Renamed SQL (Structured Query Language) • SQL-86 (ANSI, ISO) • SQL-89 • SQL-92 (SQL2) • SQL:1999 (SQL3) Here: SQL 2

3. The simple Select statement • The Select statement in SQL combines many relational algebra operations. Syntax:SELECT <attribute_list> FROM <table_list> WHERE <condition> • SELECT clause • specifies the attributes that go in the results table. • FROM clause • specifies the source tables that the database will access in order to execute the query. • WHERE clause • specifies the selection conditions, including the join condition

4. Roles of Select • The basic Select statement includes many of the relational algebra operators combined • ProjectCustomeronlastName, firstName (where is empty) SELECT lastName, firstName FROM Customer • ProjectCustomeronlastName, firstName without duplicates SELECT DISTINCT lastName, firstName FROM Customer

5. Roles of Select … • Retrieve the employees whose last name is ‘Doe’ SELECT * FROM Customer WHERE lastName = 'Doe‘ • select from Customer the accountId, last name, and first name of the employees whose last name is ‘Doe’ and live in Illinois SELECTaccountid, lastname, firstname FROM Customer WHERE lastname = 'Doe‘ and state = ‘IL’

6. Roles of Select … • join Employee and TimeCard based on equality of ssn SELECT * FROM Employee, TimeCard WHERE Employee.ssn = TimeCard.ssn SELECT * FROM Employee JOIN TimeCardON Employee.ssn = TimeCard.ssn Remark. The attribute names are preceded by the table names to remove confusion

7. Outer Join • Outer joins add rows that have no match • Left join adds all rows from left input table that do not match any row of right table. Similarly of right outer join. No full outer join is provided bySQL-2 Ex: SELECT * FROM Employee LEFT OUTER JOIN TimeCard ON Employee.ssn = TimeCard.ssn

8. Examples • List the SSNs of all managers • List the names of all projects • List the names of all employees working for the research department. • List the names of all departments managed by somebody with a salary less than 30.000. • List employees with names of dependents • List the salaries of all employees working in Houston.

9. Pattern Matching • Suppose we want to list all the movies containing the word Pinochio in their title! SELECT * FROM Movie WHERE title LIKE '%Pinochio%‘ • Suppose we want to list all the movies whose title does not start with ‘The’ SELECT * FROM Movie WHERE title NOT LIKE 'The %‘ • Suppose we want to list all employees whose ssn contains 44 in the middle SELECT * FROM Employee WHERE ssn LIKE '___-44-____‘

10. Ordering the output • Ordering results in SQL:suppose we want to output the employees sorted by lastname then firstname SELECT * FROM Customer ORDER BY lastName, firstName

11. Ordering … • Suppose we want to output the accountid of the customers in descending order SELECT * FROMCustomer ORDER BY accountIdDESC Similarly, we can use ASC for ascending

12. Renaming • If necessary we can rename relations or attributes SELECT FNAME AS First_Name FROM Customer

13. Expressions SELECT lastName, firstName, Employee.ssn, date, (endTime-startTime) AS timeworkedFROM Employee, TimeCard WHERE Employee.ssn = TimeCard.ssn • Creates a table with 5 attributes • 5th attribute of an output row is calculated from the endTime and startTime attributes of the input row

14. Aggregate functions • Aggregates functions include: count(), avg(), minimum(), maximum, … Example 1: SELECT count(*) FROM Rental WHERE accountId = 101 Example 2: SELECT count(DISTINCT lastName) FROM Customer

15. Grouping in aggregate functions • Suppose we want to count for every video how many times it was previously rented and the average cost of all the rentals SELECT videoId, avg(cost) ASaverageCost, count(*) as numRentalsFROM PreviousRental GROUP BY videoId

16. Grouping … • Suppose we want to display all the movies that were rented at least twice before SELECT title, genre, count(*) as numRentals, FROM Movie, Video, PreviousRental WHERE Movie.movieId = Video.movieId AND Video.videoId = PreviousRental.videoid GROUP BY Movie.movieId, title, genre HAVING count(*)>1 • A group with count(*) [number of rows in group] <=1 does not produce an output row

17. Examples • Find for the research department the minimum salary and the maximum salary of its employees • For each department with at least 10 employees, list the department name and its number of employees

18. Solution SELECT max(salary) as Highest_Salary, min(salary) as Lowest_Salary FROM employee, department WHERE dno = dnumber AND dname=‘Research SELECT Dname, count(*) as Number_Of_Employees FROM employee, department WHERE dno = dnumber GROUP BY dno, Dname HAVING Count(*) > 9

19. The IN Operator Conditions can contain IN for “element of” SELECT pname FROM project WHERE pnumber IN (1,2,4,5) SELECT pname FROM project WHERE pnumber NOT IN (1,2) SELECT pname FROM project WHERE plocation IN (‘Houston’, ‘Stafford’)

20. Nested select statements List the names of the employees with no dependents SELECT lastname, fname FROM employee WHERE ssn NOT IN (SELECT essn FROM dependent)

21. The ALL operator ALL: the condition should hold for all rows Ex: List the names of employees whose salary is higher than all employees in department number 5 SELECT fname, lname FROM employee WHERE salary > ALL (SELECT salary FROM employee WHERE dno = 5)

22. More examples on nested queries with IN • List the Names of all supervisors. • List the Names of all employees that have a dependent spouse. • List the SSNs of employees that on some project work the same time as `John Smith’

23. Renaming tables Remark. If attributes have the same name we can rename the tables to clarify the scope of each attribute Ex: List the names of all supervisors

24. Nesting Queries with ALL: Examples • List the names of employees that make the maximum salary • List the names of employees that make a salary that is different from everybody else’s salary

25. Existence Tests that a set is nonempty SELECT fname, lname FROM employee WHERE EXISTS (SELECT * FROM dependent WHERE ssn = essn); SELECT fname, lname FROM employee WHERE NOT EXISTS (SELECT * FROM dependent WHERE ssn = essn);

26. Set Intersection using EXISTS Example: Employees with dependents SELECT fname, lname FROM employee WHERE EXISTS (SELECT * FROM dependent WHERE ssn = essn); • Mangers that are also supervisors

27. Set Difference using NOT EXISTS Example: Employees without dependents SELECT fname, lname FROM employee WHERE NOT EXISTS (SELECT * FROM dependent WHERE ssn = essn); • Employees which are not managers • Projects on which nobody works 20 hours or more

28. Unique Existence Tests that a set contains one element SELECT fname, lname FROM employee WHERE UNIQUE (SELECT * FROM dependent WHERE ssn = essn);

29. Existence Examples • List the names of managers that have a dependent • List the names of employees that work on all department 4 projects

30. Set Operations: Union • Union operation: retrieve all videos that have been rented at least once (SELECT * FROM Rental) UNION (SELECT * FROM PreviousRental)

31. Set Operations: Intersection Retrieve videos that are currently rented and were previously rented (SELECT videoId FROM Rental) INTERSECT(SELECT videoId FROM PreviousRental)

32. Set Operations: Difference • Retrieve videotapes that are currently rented for the first time (SELECT videoId FROM Rental) EXCEPT (SELECT videoId FROM PreviousRental)

33. Creating tables • A createtablestatement specifies a table name and a list of attributes and constraints for a new table • Example with no constraints CREATE TABLE Customer (accountId int, lastName varchar(32), firstName varchar(32), street varchar(100), city varchar(32), state char(2), zipcode varchar(9), balance real)

34. Creating tables … Add a primary key constraint CREATE TABLE Customer (accountId int primary key, lastName varchar(32), firstName varchar(32), street varchar(100), city varchar(32), state char(2), zipcode varchar(9), balance real)

35. Creating tables … • Add primary key constraint as separate clause CREATE TABLE Store (storeId int, street varchar(100), city varchar(32), state char(2), zipcode varchar(9), primary key storeId, manager int references Employee) • Add foreign key constraints and specify other keys and multiple-attribute key CREATE TABLE Rental (accountId int, videoId varchar(10) unique, dateRented datetime, dateDue datetime, cost real, primary key (accountId, videoId), foreign key (accountId) references Customer(accountId) foreign key (videoId) references Video(videoId))

36. Inserting into tables Either specify all values: INSERT INTO Customer VALUES (555, 'Yu', 'Jia','540 Magnolia Hall', 'Tallahassee', 'FL', '32306', 0.00) Or the value that need to be inserted, the others will be set to NULL INSERT INTO Customer (firstName, lastName, accountId) VALUES ('Jia', 'Yu', 555)

37. Inserting by SELECT • We can insert from select statement INSERT INTO <table> <select statement> Remark. Fields of SELECT must match fields INSERT in order and type Ex: Suppose we have created a table called Comedy_Movies with the same attributes as Movie except for genre which is omitted. To populate the table: INSERT INTO Comedy_Movies (movieId, title, length, rating) SELECT movieId, title, length, rating FROM Movie WHERE genre = ‘comedy’

38. Updating a table • General form of update statement UPDATE <table> SET <attribute>=<value> ...WHERE <selection condition> Ex: Update to mark every timecard as paid UPDATE TimeCard SET paid = true WHERE paid = false

39. Updating tables … Ex: Update to give every employee a 10% raise of the average salary UPDATE Employee SET salary =salary +0.1 (select avg(salary) from Employee)

40. Delete operation • Each delete statement deletes from one table according to some selection clause(deletion propagates in case of referential constraints) Ex: Delete every row from table DELETE FROM Employee Ex: Delete all employees that live in Illinois DELETE FROM Employee WHERE state = ‘IL’

41. Examples … Ex: Delete all employees with no dependents DELETE FROM Employee WHERENOT EXISTS (SELECT * FROM Dependent WHERE essn = ssn)

42. Altering tables and attributes • An ALTER TABLE statement changes the definition of a table Modify and drop table constraints and drop an attribute ALTER TABLE Video MODIFY (storeId NOT NULL)DROPconstraint (primarykey videoId) DROP (movieId)

43. Dropping a table • Drop a table: completely destroys table and all contents DROP TABLE Employee What happens when table contains keys and foreign keys? Cascade characteristic tells server to delete all rows of other tables that have foreign keys to the dropped table DROP TABLE video cascade Restrict characteristic tells server to block the delete operation if additional rows would have to be deleted DROP TABLE movie restrict

44. Examples • List everybody who makes more than $30000. • List names of everybody working for the research department. • List employees with a dependent. • List employees that have a daughter. • List employees without dependents. • List employees working on a project in Houston. • List all supervisors. • List names of all managers. • List names of managers with at least one dependent

45. Examples … • For every project located in ‘Chicago’, list the project number, the controlling department number, the department manager’s last name, address, and birthdate. • Make a list of project numbers for projects involving an employee whose first name is `Pinochio’ either as a worker on the project, or as a manager of the department that controls the project. • Find the names of all employees who are directly supervised by `Isaac Newton’ • For each department, retrieve the department name and average salary of its employees. • Retrieve the average salary of all female employees • For each project, list the project name and the total number of hours spent on the project.