CSE 480: Database Systems

1. CSE 480: Database Systems • Lecture 11: SQL

2. SQL Query SELECT <attribute list>FROM <table list>WHERE <condition that must be satisfied by a tuple> In MySQL, FROM and WHERE clauses are optional Example:

3. SELECT Clause SELECT <attribute list> Attribute list may include * (wildcard) Keywords such as AS and DISTINCT Arithmetic expression String functions Boolean expression

4. Wildcard(*) in SELECT-Clause Retrieve the values for all columns of the selected tuples

5. AS in SELECT-Clause • Rename the columns in query result • Example: Find the names of employees who earn more than their supervisors • SELECT E.FNAME AS EMP_FNAME, E.LNAME AS EMP_LNAMEFROM EMPLOYEE E, EMPLOYEE SWHERE E.SUPERSSN=S.SSN AND E.SALARY > S.SALARY; Output:

6. DISTINCT in SELECT-Clause Eliminate duplicate tuples in query result

7. Arithmetic Expression in SELECT-Clause Query: Show the effect of giving employees a 10% raise

8. String functions in SELECT-Clause

9. String functions in SELECT-Clause

10. String functions in SELECT-Clause For more string functions, go to http://dev.mysql.com/doc/refman/4.1/en/string-functions.html

11. Boolean Expression in SELECT-Clause

12. FROM Clause SELECT <attribute list> FROM <table list> Table list may include Names of 1 or more tables Subquery for joined tables

13. Joined Relations in FROM-Clause Query 1: Retrieve the name and address of all employees who work for the 'Research' department • SELECT Fname, Lname, AddressFROM EMPLOYEE, DEPARTMENT WHERE Dname='Research' AND Dno=Dnumber is equivalent to: • SELECT Fname, Lname, AddressFROM (EMPLOYEE JOIN DEPARTMENT ON Dno=Dnumber)WHERE Dname='Research'

14. Joined Relations in FROM-Clause • Many types of Join • regular JOIN • NATURAL JOIN • CROSS JOIN • LEFT OUTER JOIN • RIGHT OUTER JOIN, • etc

15. JOIN SELECT * FROM R JOIN S on R.Id = S. Id R S Id Name 111 John 222 Mary 333 Bill 444 Joe Id Value 111 B 111 A 222 A foreach row r in table R foreach row s in table S if r.Id = s.Id then output the merged row of r and s Id Name Id Value 111 John 111 B 111 John 111 A 222 Mary 222 A

16. JOIN – Example Student Transcript Id Name Addr Status 111 John ….. ….. 222 Mary ….. ….. 333 Bill ….. ….. 444 Joe ….. ….. StudId CrsCode Sem Grade 111 CSE305 S00 B 111 CSE306 S99 A 222 CSE304 F99 A SELECT * FROM Student JOIN Transcript on Id = StudId Id Name Addr Status StudId CrsCode Sem Grade 111 John ….. ….. 111 CSE305 S00 B 111 John ….. ….. 111 CSE306 S99 A 222 Mary ….. ….. 222 CSE305 F99 A Produces columns (attributes) with identical values, which is redundant

17. Natural Join • Join condition equates all attributes with the same name • Duplicate columns are automatically eliminated from result SELECT * FROM R NATURAL JOIN S R S Id Name 111 John 222 Mary 333 Bill 444 Joe Id Value 111 B 111 A 222 A foreach row r in table R foreach row s in table S Examine their common attributes If their values are the same, then merge the rows while removing their duplicate columns Id Name Value 111 John B 111 John A 222 Mary A

18. SELECT * FROM Student NATURAL JOIN Transcript2 Id Name Addr Status CrsCode Sem Grade 111 John ….. ….. CSE305 S00 B 111 John ….. ….. CSE306 S99 A 222 Mary ….. ….. CSE305 F99 A Natural Join Join attribute must have the same name (Id) Student Transcript2 Id Name Addr Status 111 John ….. ….. 222 Mary ….. ….. 333 Bill ….. ….. 444 Joe ….. ….. Id CrsCode Sem Grade 111 CSE305 S00 B 111 CSE306 S99 A 222 CSE304 F99 A Duplicate attribute (Id) was removed

19. Cross Join SELECT * FROM R CROSS JOIN S R S Id Name 111 John 222 Mary 333 Bill 444 Joe Id Value 111 B 111 A 222 A foreach row r in table R foreach row s in table S output the merged row r and s Id Name Id Value 111 John 111 B 111 John 111 A • John 222 A • … … … … • 444 Joe 222 A Output has 12 rows

20. Left Outer Join SELECT * FROM R LEFT OUTER JOIN S ON R.Id = S.Id R S Id Name 111 John 222 Mary 333 Bill 444 Joe Id Value 111 B 111 A 222 A foreach row r in table R foreach row s in table S. if r.Id = S.Id then output the merged row r and s if row r is not merged with any rows in S output row r with NULL values for s Id Name Id Value 111 John 111 B 111 John 111 A • 222 Mary 222 A • 333 Bill NULL NULL • 444 Joe NULL NULL Output has 5 rows

21. Left Outer Join - Example Retrieve the name of all employees and their supervisors SELECT E.Fname, E.Lname, S.Fname, S.LnameFROM (EMPLOYEE E LEFT OUTER JOIN EMPLOYEE S ON E.SUPERSSN=S.SSN)

22. Right Outer Join SELECT * FROM R RIGHT OUTER JOIN S ON R.Id = S.Id R S Id Name 111 John 222 Mary 333 Bill 444 Joe Id Value 111 B 111 A 555 A foreach row s in table S foreach row r in table R if r.Id = S.Id then output the merged row r and s if row s is not merged with any rows in R output row s with NULL values for r Id Name Id Value 111 John 111 B 111 John 111 A • NULL NULL 555 A Output has 3 rows

23. WHERE <condition> Selection condition is a Boolean expression Simple selection condition: <attribute> operator <constant> <attribute> operator <attribute> <attribute> operator <set> or <attribute> operator <relation> Complex conditions: <condition> AND <condition> <condition> OR <condition> NOT <condition> WHERE-Clause

24. Boolean Expression in WHERE-Clause • <attribute> operator <constant> • <attribute> operator <attribute> • Operator: =, >, <, >=, <=, <> (not equal to) • Applicable to integers, floats, strings, dates, etc. (except for NULL) SELECT *FROM EMPLOYEEWHERE SSN > SUPERSSN AND SALARY > 49999.99 AND MINIT='B' AND LNAME='SMITH' AND BDATE >= '1980-01-01';

25. Substring Comparison in WHERE-Clause Find employees who live in “Houston, TX”. Use the LIKE operator to compare partial strings Two reserved characters are used: % matches an arbitrary number of characters _ matches a single arbitrary character

26. Substring Comparison • Query: Retrieve all employees whose address is in Houston, Texas. SELECT FNAME, LNAME FROM EMPLOYEE WHERE ADDRESS LIKE '%Houston, TX%‘;

27. Substring Comparison • Query: Retrieve all employees who were born during the 1950s. Q26: SELECT FNAME, LNAME FROM EMPLOYEE WHERE BDATE LIKE '195_ - _ _ - _ _‘;

28. Arithmetic Expression in WHERE-Clause Query: Retrieve the names of employees who earn more than half the salary of their supervisors SELECT E.FName, E.LnameFROM EMPLOYEE AS E, EMPLOYEE AS SWHERE E.Salary > S.Salary/2 AND E.SuperSSN = S.SSN;

29. Arithmetic Expression in WHERE-Clause Between comparison operator Query: Retrieve the first and last names of employees in department 5 whose salary is between $30,000 and $40,000. SELECT Fname, LnameFROM EMPLOYEEWHERE (Salary BETWEEN 30000 AND 40000) AND Dno = 5

30. UNSPECIFIED WHERE-clause If there is only one relation in the FROM-clause and there is no join condition, this implies all tuples of the relation are selected SELECT SSN FROM EMPLOYEE; If more than one relation is specified in the FROM-clause and there is no join condition, then the CARTESIAN PRODUCT (Cross Join) of tuples is selected SELECT SSN, DNAME FROM EMPLOYEE, DEPARTMENT;

31. NULLS IN SQL QUERIES Cannot use equality (=) comparison to check for null values Query: Retrieve the names of employees who do not have supervisors Query: Retrieve the names of employees who have supervisors • SELECT FNAME, LNAMEFROM EMPLOYEEWHERE SUPERSSN IS NULL; • SELECT FNAME, LNAMEFROM EMPLOYEEWHERE SUPERSSN IS NOT NULL;

32. SET OPERATIONS SQL has directly incorporated some set operations UNION INTERSECT MINUS or EXCEPT The resulting relations of these set operations are sets of tuples; duplicate tuples are eliminatedfrom the result The set operations are applicable to union compatible relations the two SQL relations must have the same attributes and the attributes must appear in the same order Note: MySQL supports only UNION; Oracle supports all 3 set operations

33. Example in Oracle Duplicate rows are eliminated Not available in MySQL

34. Example Query: List the names of projects that involve an employee whose last name is 'Smith' as a worker or as a manager of the department that controls the project

35. Example Query: List the names of projects that involve an employee whose last name is 'Smith' as a worker or as a manager of the department that controls the project (SELECT PNAME FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE DNUM=DNUMBER AND MGRSSN=SSN AND LNAME='Smith') UNION (SELECT PNAME FROM PROJECT, WORKS_ON, EMPLOYEE WHERE PNUMBER=PNO AND ESSN=SSN AND NAME='Smith');

36. Example Query: List the first name and last name of employees who do not work on any project

37. Example Query: List the first name and last name of employees who do not work on any project (SELECT Fname, Lname FROM EMPLOYEE) MINUS (SELECT Fname, Lname FROM WORKS_ON, EMPLOYEE WHERE ESSN=SSN); Caution: Not applicable in MySQL (see slide 40 on how to write this query in MySQL)

38. IN Operator v IN W The comparison operator IN compares a value v with a set of values W, and evaluates to TRUE if v is one of the elements in W. This is SET membership test. Examples: 3 in {1, 2, 3} TRUE 0 in {1, 2, 3} FALSE

39. IN Operator Query: Retrieve the social security numbers of all employees who work on project number 1, 2, or 3 SELECT DISTINCT ESSN FROM WORKS_ON WHERE (PNO = 1) OR (PNO = 2) OR (PNO = 3); Using IN Operator: SELECT DISTINCT ESSN FROM WORKS_ON WHERE PNO IN (1, 2, 3);

40. Example Query: List the first name and last name of employees who do not work on any project SELECT Fname, Lname FROM EMPLOYEE WHERE SSN NOT IN (SELECT ESSN FROM WORKS_ON);

41. Exercise • Find the names of supervisors who are also managers • Find the names of employees who are not supervisors • Find the names of supervisors who supervise exactly one employee • Try to write it in two ways: • Using IN operator • Using SET operations (UNION, INTERSECT, MINUS)