STRUCTURED QUERY LANGUAGE SECTION 6

1. STRUCTURED QUERY LANGUAGESECTION 6 The Core of SQL (1992)

2. Introduction • Most important relational data manipulation language • ANSI • Used by many commercial databases

3. SEQUEL • 1992 ANSI standard • SQL3

4. An interactive query language • Or embedded in other languages • SQL is not a programming language

5. Querying a Single Table • SQL is a transform-oriented language • Accepts as input • The result?

6. Relations used for SQL examples • JUNIOR (Snum, Name, Major) • HONOUR-STUDENT (Number, Name, Interest) • STUDENT (SID, Name, Major, GradeLevel, Age) • CLASS (Name, Time, Room) • ENROLLMENT ( StudentNumber, ClassName, PositionNumber) • FACULTY (FID, Name, Department) Is there a problem with the ENROLLMENT table and the primary key?

7. Sample Data

9. Projections Using SQL • The projection • STUDENT [SID, NAME, MAJOR] • Specified in SQL SELECT SID, Name, Major FROM STUDENT

10. The result • The word SELECT

11. Another Example SELECT Major FROM STUDENT SELECT DISTINCT Major FROM STUDENT

12. Selections Using SQL • The Relational Algebra selection operator is also performed • Specified in SQL SELECT SID, Name, Major, GradeLevel, Age FROM STUDENT WHERE Major = ‘Math’

13. SELECT - FROM - WHERE SELECT * FROM STUDENT WHERE Major = ‘Math’

14. Combination of Selection and Projection SELECT Name, Age FROM STUDENT WHERE Major = ‘Math’

15. Conditions in a WHERE clause SELECT Name, Age FROM STUDENT WHERE Major = ‘Math’ AND Age > 21

16. Conditions in a WHERE clause can refer to a set of values SELECT Name FROM STUDENT WHERE Major IN (‘Math’, ‘ACCOUNTING’)

17. The following expression SELECT Name FROM STUDENT WHERE Major NOT IN (‘Math’, ‘ACCOUNTING’)

18. WHERE clause and ranges of values SELECT Name, Major FROM STUDENT WHERE Age BETWEEN 19 AND 30 • Equivalent to: SELECT Name, Major FROM STUDENT WHERE Age > 19 AND Age < 30

19. WHERE clause and the LIKE keyword SELECT Name, GradeLevel FROM STUDENT WHERE GradeLevel LIKE ‘_R’

20. Finding last names ending with S SELECT Name FROM STUDENT WHERE Name LIKE ‘%S’

21. IS NULL keyword SELECT Name FROM STUDENT WHERE GradeLevel IS NULL • What will this return?

22. Sorting • Rows of a result can be sorted SELECT Name, Major, Age FROM STUDENT WHERE Major = ‘Accounting’ ORDER BY Name

23. Sorting more than one column SELECT Name, Major, Age FROM STUDENT WHERE GradeLevel IN (‘FR’, ‘SO’, ‘SN’) ORDER BY Major ASC, Age DESC

24. SQL Built-In Functions • Five functions • COUNT, SUM, AVG, MAX, and MIN SELECT COUNT (*) FROM STUDENT

25. Consider the following SELECT COUNT (Major) FROM STUDENT vs. SELECT COUNT (DISTINCT Major) FROM STUDENT

26. Built-In Functions and Grouping • To increase built-in function utility • Formed by collecting rows that have the same value of a specified column • GROUP BY

27. An example SELECT Major, COUNT (*) FROM STUDENT GROUP BY Major

28. Grouping subsets SELECT Major, COUNT (*) FROM STUDENT GROUP BY Major HAVING COUNT (*) > 2

29. Greater generality using WHERE SELECT Major, AVG (Age) FROM STUDENT WHERE GradeLevel = ‘SN’ GROUP BY Major HAVING COUNT (*) > 1

30. Querying Multiple Tables • Study now extended to two or more tables • Will use the data from the following tables • STUDENT (SID, Name, Major, GradeLevel, Age) • CLASS (Name, Time, Room) • ENROLLMENT ( StudentNumber, ClassName, PositionNumber)

31. Retrieval Using Subquery SELECT Name FROM STUDENT WHERE SID IN (100,200) • Want to know the names of the students enrolled in class BD445 SELECT StudentNumber FROM ENROLLMENT WHERE ClassName = ‘BD445’

32. Use it as a subquery SELECT Name FROM STUDENT WHERE SID IN (SELECT StudentNumber FROM ENROLLMENT WHERE ClassName = ‘BD445’)

33. Subqueries can consist of three or more tables SELECT ENROLLMENT.StudentNumber FROM ENROLLMENT WHERE ENROLLMENT.ClassName IN (SELECT CLASS.Name FROM CLASS WHERE ClassTime = ‘MWF3’)

34. To get the names of the students SELECT STUDENT.Name FROM STUDENT WHERE STUDENT.SID IN (SELECT ENROLLMENT.StudentNumber FROM ENROLLMENT WHERE ENROLLMENT.ClassName IN (SELECT CLASS.Name FROM CLASS WHERE ClassTime = ‘MWF3’))

35. Joining With SQL • Want to produce SID, StudentName, and ClassName for every student • What two tables must be joined? SELECT STUDENT.SID, STUDENT.Name, ENROLLMENT.ClassName FROM STUDENT, ENROLLMENT WHERE STUDENT.SID = ENROLLMENT.StudentNumber

36. What operations make up a join? • FROM equates to • WHERE expresses

37. The WHERE clause and qualifiers SELECT STUDENT.SID, ENROLLMENT.ClassName FROM STUDENT, ENROLLMENT WHERE STUDENT.SID = ENROLLMENT.StudentNumber AND STUDENT.Name = ‘RYE’ AND ENROLLMENT.PositionNumber = 1

38. Data from more than two tables SELECT STUDENT.SID, CLASS.Name, CLASS.Time ENROLLMENT.ClassName FROM STUDENT, ENROLLMENT, CLASS WHERE STUDENT.SID = ENROLLMENT.StudentNumber AND ENROLLMENT.ClassName = CLASS.Name AND STUDENT.Name = ‘BAKER’

39. Comparison of SQL Subquery and Join • A join is an alternative to expressing many subqueries • For example, “Classes taken by undergrads?” SELECT DISTINCT ENROLLMENT.ClassName FROM ENROLLMENT, STUDENT WHERE ENROLLMENT.StudentNumber = STUDENT.SID AND STUDENT.Grade.Level NOT = ‘GR’

40. As a subquery SELECT DISTINCT Class.Name FROM ENROLLMENT WHERE StudentNumber IN (SELECT SID FROM STUDENT WHERE GradeLevel NOT = ‘GR’)

41. If we want to know: “Class names and grade levels of undergrads?” SELECT DISTINCT ENROLLMENT.ClassName, SUDENT.GradeLevel FROM ENROLLMENT, STUDENT WHERE ENROLLMENT.StudentNumber = STUDENT.SID AND STUDENT.GradeLevel NOT = ‘GR’ • Why cannot a subquery be used?

42. Thus in the previous subquery example: SELECT DISTINCT Class.Name FROM ENROLLMENT WHERE StudentNumber IN (SELECT SID FROM STUDENT WHERE GradeLevel NOT = ‘GR’) • This would not work: SELECT DISTINCT ENROLLMENT.ClassName, STUDENT.GradeLevel FROM ENROLLMENT WHERE StudentNumber IN (SELECT SID FROM STUDENT WHERE GradeLevel NOT = ‘GR’)

43. EXISTS and NOT EXISTS • Logical operators SELECT DISTINCT StudentNumber FROM ENROLLMENT A WHERE EXISTS (SELECT * FROM ENROLLMENT B WHERE A.StudentNumber = B.StudentNumber AND A.ClassName NOT = B.ClassName)

44. Both query and subquery refer to the same table • Two uses assigned a different name • What is the meaning of the subquery?

45. General procedure: • First row in A is compared with first row in B • First Row in A compared with second row in B

46. SELECT STUDENT.Name FROM STUENT WHERE NOT EXISTS (SELECT * FROM ENROLLMENT WHERE NOT EXISTS (SELECT * FROM CLASS WHERE CLASS.Name = ENROLLMENT.ClassName AND ENROLLMENT.StudentNumber = STUDENT.SID)) • NOT EXISTS operator • Query has three parts

47. Changing Data Inserting Data • Inserting a single row INSERT INTO ENROLLMENT VALUES (400, ‘BD445’, 44)

48. INSERT INTO ENROLLMENT (StudentNumber, ClassName) VALUES (400, ‘BD445’) • Some data in a row not known • Mass copy rows INSERT INTO JUNIOR VALUES (SELECT SID, Name, Major FROM STUDENT WHERE GradeLevel = ‘JR’)

49. Can be deleted one at a time DELETE STUDENT WHERE STUDENT.SID = 100 Deleting Data

50. DELETE ENROLLMENT WHERE ENROLLMENT.StudentNumber IN (SELECT STUDENT.SID FROM STUDENT WHERE STUDENT.Major = ‘Accounting’) DELETE STUDENT WHERE STUDENT.Major = ‘Accounting’ • Groups of rows can be deleted