SQL: Structured Query Language

1. SQL: Structured Query Language Instructor: Mohamed Eltabakh meltabakh@cs.wpi.edu

2. SQL Language • Data Definition Language (DDL) • Create tables, specifying the columns and their types of columns, the primary keys, etc. • Drop tables, add/drop columns, add/drop constraints – primary key, unique, etc. • Data Manipulation Language (DML) • Update, Insert, Delete tuples • Query the data These are already covered Our focus today

3. Reminder About DDL • Create “Students” relation • Create “Courses” relation CREATE TABLE Students (sid: CHAR(20) Primary Key, name: CHAR(20) Not NULL, login: CHAR(10), age: INTEGER, gpa: REAL); CREATE TABLE Courses (cid: Varchar(20) Primary Key, name: string, maxCredits : integer, graduateFlag: boolean); • Create “Enrolled” relation CREATE TABLE Enrolled (sid: CHAR(20) Foreign Key References (Students.sid), cid: Varchar(20), enrollDate: date, grade: CHAR(2), Constraints fk_cidForeign Key cid References (Courses.cid)); Alter Table Enrolled Add Constraints fk_cidForeign Key cid References Courses(cid));

4. Reminder About:Insert, Update, Delete • This is performed using Data Manipulation Language of SQL (DML) • Insertion • Insert into Students values (“1111”, …); • Deletion • Delete from Students; • Delete from Students Where sid = “1111”; • Update • Update Students Set GPA = GPA + 0.4; • Update Students Set GPA = GPA + 0.4 Where sid = “1111”;

5. SQL Query LanguageSELECT Statement

6. SELECT-FROM-WHERE SELECT <list of columns> FROM <relation name> WHERE<conditions>; π relation name σ

7. SELECT-FROM-WHERE * Means “project all attributes” SELECT * FROM Student WHEREsName=“Greg”AND address=“320FL”; Student  (sName=“Greg” AND address=“320FL”) (Student)

8. SELECT-FROM-WHERE SELECT sNumber FROM Student WHEREsName=“Greg” AND address=“320FL”; Student πsNumber((sName=“Greg” AND address=“320FL”) (Student))

9. Select-From Query • Only SELECTand FROMclauses are mandatory • The WHEREclause is optional • If not exist, then all records will be returned (there are no selection predicates) SELECT <list of columns> FROM <relation name>;

10. Select-From Query SELECTsNumber, sName FROMStudent; Student  (sNumber, sName) (Student)

11. Extended Projection The select clause can have expressions and constants SELECT <list of columns or expressions> FROM <relation name> WHERE<conditions>; Can also rename the fields or expressions using “AS”

12. Extended Projection SELECT‘Name:’ || sName AS info, 0 AS gpa FROM Student WHEREaddress=“320FL”; Student  (info  ‘Name:’||sName, gpa  0 ) ( (address=“320FL”) (Student))

13. Mapping between SQL and Relational Algebra  L ( C (R)) SELECT L FROM R WHERE C

14. Renaming Relations and Tuple Variables SELECTS1.sNumber AS num FROMStudent S1 WHERES1.sNumber >= 1; Tuple variable Student  (num S1.sNumber) ( (S1.sNumber >= 1) (S1(Student)))

15. Where Clause • The comparison operator depends on the data type • For Numbers: • <, >, <=, >=, =, <> • What about Strings?? SELECTS1.sNumber AS num FROMStudent S1 WHERES1.sNumber >= 1;

16. String Operators • Comparison Operators based on lexicographic ordering: =, <, >, <>, >=, <= • Concatenation operator: || • Pattern match: sLIKE p • p denotes a pattern • Can use wild characters in p such as _, % • _ matches exactly any single character • % matches zero or more characters SELECT‘Name:’ || sName FROM Student WHEREaddress=“320FL”;

17. String Matching Example SELECTs1.sNumber AS num FROMStudent S1 WHEREs1.sName LIKE ‘Da%’ OrS1.professor LIKE ‘M_’;

18. Set Operators in SQL • Set Semantics • Union, Intersect, Except • Bag Semantics • Union All, Intersect All, Except All • The two relations R and S must have the same column names and types (Union Compatible)

19. Set Operations in SQL: Example Operators : UNION, INTERSECT, and EXCEPT (SELECT sName FROM Student) EXCEPT (SELECT sName FROM Student WHERE address=‘320FL’) SELECTsName FROMStudent WHERE address <> ‘320FL’;

20. Set Operations in SQL: Example

21. Cartesian Product in SQL • In Relation Algebra: R x S • In SQL, add R and S to FROM clause • No WHERE condition that links R and S SELECT* FROMStudent, Professor; SELECTsName, pNumber FROMStudent, Professor;

22. Cross Product - Example Student Professor SELECT* FROMStudent, Professor;

23. Theta Join in SQL • In Relation Algebra: R ⋈C S • In SQL, add R and S to FROM clause • WHERE condition that links R and S with the join condition C SELECT* FROMStudent, Professor WHEREStudent.pNum = Professor.Number; Join condition

24. Theta Join Example Student Professor SELECTsNumber, sName, pName FROMStudent, Professor WHEREprofNum = pNumber;  sNumber,sName,pName(Student ⋈(profNum=pNumber) Professor)

25. Natural Join Reminder: Join columns must have same names in both relations (R ⋈ S) SELECT * FROM Student , Professor WHERE Student.pnumber = Professor.pnumber ; Student ⋈ Professor SELECT * FROM Student NATURAL JOIN Professor; Explicitly add the equality join condition

26. Natural Join - Example Professor Student SELECT * FROM Student , Professor WHERE Student.pNumber = Professor.pNumber ; Student ⋈ Professor

27. Example Queries SELECT * FROMloan WHEREamount > 1200 ; SELECTloan_number FROMloan WHEREamount > 1200 ;

28. Example Queries SELECTcustomer_name FROMdepositor Union SELECTcustomer_name FROMborrower;

29. Example Queries DBMS is smart enough !!! (Select first, then joins) SELECT customer_name FROMborrower B, loan L WHERE B.loan_number = L.loan_number ANDL.branch_name = “Perryridge”;

30. Sorting: ORDER BY clause • New optionalclause that you can add to the SELECT statement called “ORDER BY” • Allows sorting the returned records according to one or more fields SELECT* FROMStudent WHEREsNumber >= 1 ORDER BY pNumber, sName; Default is ascending order SELECT* FROMStudent WHEREsNumber >= 1 ORDER BY pNumberASC, sNameDESC;

31. Sorting: ORDER BY clause Student SELECT* FROMStudent WHEREsNumber >= 1 ORDER BY pNumber, sName DESC;  (pNumber, sName DESC) ( (sNumber >= 1) (Student))

32. Duplicate Elimination in SQL • New optionalkeyword “DISTINCT” • Added in the SELECT clause SELECTDISTINCT… FROM… … Eliminate any duplicates from the answer

33. Duplicate Elimination: Example Student SELECTDISTINCTsName, address FROMStudent; • (sName,address(Student)) • ( (address) ( (sNumber > 1) (Student))) SELECTDISTINCTaddress FROMStudent WHEREsNumber > 1;

34. Always Remember…. • Only SELECT and FROM clauses are mandatory • All the others are optional • You can mix and match the optional ones • But if you add a clause, then keep it in its order SELECTDISTINCTaddress FROMStudent WHEREsNumber > 1; SELECTaddress FROMStudent ORDER BY sNumber; SELECTaddress FROMStudent WHEREsNumber > 1 ORDER BY sNumber; SELECTaddress FROMStudent ORDER BY sNumber WHEREsNumber > 1; X

35. Aggregation + GroupBy

36. Possible Aggregations in SQL 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;

37. Grouping & Aggregation in SQL • New optionalclause called “GROUP BY” • If the SELECT statement has “WHERE” • Then WHERE conditions are evaluated first, then records are grouped SELECTpNumber, COUNT(sName) FROMStudent GROUP BY pNumber; Form one group for each pNumber, and then count inside each group

38. GROUP BY: Example I Student cnt count(*) (Student) pNumber,cntcount(*) ( (sNumber > 1) (Student)) SELECTpNumber, count(*) AS CNT FROMStudent WHEREsNumber > 1 GROUP BYpNumber; SELECTcount(*) AS CNT FROMStudent;

39. GROUP BY: Example II Student pNumber,address, CNT  count(sName), SUM  sum(sNumber) ( (sNumber > 1) (Student)) SELECTpNumber,address, count(sName) AS CNT, sum(sNumber) ASSUM FROMStudent WHEREsNumber > 1 GROUP BYpNumber, address;

40. Restrictions of GROUP BY • If you group by A1, A2, …An, then any other column projected in SELECT clause must be inside an aggregation function SELECTpNumber, address, count(sName) AS CNT, sum(sNumber) ASSUM FROMStudent WHEREsNumber > 1 GROUP BYpNumber, address; SELECTpNumber, address, sName, sum(sNumber) ASSUM FROMStudent WHEREsNumber > 1 GROUP BYpNumber, address; X SELECTpNumber, count(sName) AS CNT, sum(sNumber) ASSUM FROMStudent WHEREsNumber > 1 GROUP BYpNumber, address;

41. HAVING Clause: Putting Condition on Groups • How to add conditions on each group? • Select only the groups where the COUNT > 5 • These conditions are after you build the groups (not before) • Remember: WHERE conditions are executed before the groups are formed • New optionalclause called “HAVING”, added after the GROUP BY clause SELECTpNumber, COUNT(sName) FROMStudent GROUP BY pNumber HAVING SUM(sNumber) > 2; Can reference aggregation inside HAVING

42. HAVING Clause: Example Student  (SUM> 3) (pNumber,address, CNT  count(sName), SUM  sum(sNumber) ( (sNumber > 1) (Student))) SELECTpNumber,address, count(sName) AS CNT, sum(sNumber) ASSUM FROMStudent WHEREsNumber > 1 GROUP BYpNumber, address HAVING SUM > 3;

43. SELECT Statement Clauses SELECT<projection list> FROM<relation names> WHERE <conditions> GROUP BY <grouping columns> HAVING <grouping conditions> ORDER BY <order columns>; • Optional clauses if added must be in the order above • Order of execution • FROM • WHERE • GROUP BY • HAVING • ORDER BY • SELECT optional