SQL : Query Language

1. SQL : Query Language Part II CS3431

2. Sorting: ORDER BY clause SELECT * FROM Student WHERE sNumber >= 1 ORDER BY sNumber, sName  (sNumber, sName) ( (sNumber >= 1) (Student)) CS3431

3. Subqueries Subquery returns a relation Nest subqueries into WHERE clause Nest subqueries into FROM clause CS3431

4. Subqueries SELECT * FROM Student WHERE professor = (SELECT pName FROM Professor WHERE pNumber=1) Note: The inner subquery returns a relation, but SQL runtime ensures that subquery returns a relation with one column and with one row, otherwise it is a run-time error. CS3431

5. Subqueries - Example Student SELECT * FROM Student WHERE professor = (SELECT pName FROM Professor WHERE pNumber=1) Professor CS3431

6. Subqueries • We can use IN, EXISTS, NOT IN, and NOT EXISTS • ALL, ANY can be used with comparisons SELECT * FROM Student WHERE (sNumber, professor) IN (SELECT pNumber, pName FROM Professor) CS3431

7. Subqueries - Example Student Professor SELECT * FROM Student WHERE (sNumber, professor) IN (SELECT pNumber, pName FROM Professor) CS3431

8. Subqueries: EXISTS Professor SELECT * FROM Student WHERE EXISTS (SELECT pName FROM Professor WHERE Student.professor=pName) Student CS3431

9. Subqueries with negation Professor SELECT * FROM Student WHERE (sNumber, professor) NOT IN (SELECT pNumber, pName FROM Professor) Student CS3431

10. Subqueries with negation Student Professor SELECT * FROM Student WHERE NOT EXISTS (SELECT pName FROM Professor WHERE Student.professor=pName) CS3431

11. Subqueries: ALL, ANY CS3431

12. Subqueries: ALL - Example Student Professor SELECT * FROM Student WHERE sNumber > ALL (SELECT pNumber FROM Professor) CS3431

13. Subqueries: ANY - Example Student Professor SELECT * FROM Student WHERE sNumber = ANY (SELECT pNumber FROM Professor) CS3431

14. Subqueries: NOT ALL - Example Student Professor SELECT * FROM Student WHERE NOT sNumber > ALL (SELECT pNumber FROM Professor) CS3431

15. Subqueries: NOT ANY - Example Student Professor SELECT * FROM Student WHERE NOT sNumber = ANY (SELECT pNumber FROM Professor) CS3431

16. Subqueries: Correlation CS3431

17. Subqueries in FROM clause Professor SELECT sName, pName FROM Student, (SELECT * FROM Professor WHERE pNumber=1) WHERE professor=pName; Student CS3431

18. Duplicate Elimination SELECT DISTINCT address FROM Student WHERE sNumber >= 1; SELECT DISTINCT * FROM Student; • (Student) • ( (address) ( (sNumber >= 1) (Student))) Student CS3431

19. Aggregation + GroupBy CS3431

20. Aggregation Functions SELECT COUNT (*) FROM Student; SELECT COUNT (sNumber) FROM Student; SELECT MIN (sNumber) FROM Student; SELECT MAX (sNumber) FROM Student; SELECT SUM (sNumber) FROM Student; SELECT AVG (sNumber) FROM Student; We can have distinct such as: SELECT COUNT (DISTINCT sNumber) FROM Student CS3431

21. Grouping SELECT COUNT (sName) as cnum FROM Student GROUP BY address; • (COUNT (sName) as snum) ( (address, COUNT (sName)) (Student)) Student CS3431

22. Grouping SELECT address, COUNT (sNumber) FROM Student WHERE sNumber > 1 GROUP BY address HAVING COUNT (sNumber) > 1; Student CS3431

23. Aggregation and NULLs • NULLs are ignored in any aggregation; except COUNT (*) • However if the set of attributes to be grouped on has null values, then grouping is done on the null values as well. CS3431

24. SQL Queries - Summary SELECT [DISTINCT] a1, a2, …, an FROM R1, R2, …, Rm [WHERE C1] [GROUP BY g1, g2, …, gl [HAVING C2]] [ORDER BY o1, o2, …, oj] CS3431