CS 405G: Introduction to Database Systems

1. CS 405G: Introduction to Database Systems SQL I Instructor: Jinze Liu

2. Sailors DDL CREATE TABLE Sailors (sid INTEGER, sname CHAR(20), rating INTEGER, age REAL, PRIMARY KEY sid) CREATE TABLE Boats (bid INTEGER, bname CHAR (20), color CHAR(10) PRIMARY KEY bid) CREATE TABLE Reserves (sid INTEGER, bid INTEGER, day DATE, PRIMARY KEY (sid, bid, day), FOREIGN KEY sid REFERENCES Sailors, FOREIGN KEY bid REFERENCES Boats) NOT NULL, Boats NOT NULL, NOT NULL, Reserves NOT NULL, NOT NULL,

3. sid name login age gpa 53666 Jones jones@cs 18 3.4 53688 Smith smith@ee 18 3.2 53650 Smith smith@math 19 3.8 SQL DML • DML includes 4 main statements: SELECT (query), INSERT, UPDATE and DELETE • e.g: To find the names of all 19 year old students: We’ll spend a lot of time on this one PROJECT SELECT S.name FROM Students S WHERE S.age=19 SELECT

4. Querying Multiple Relations SELECT S.name, E.cid FROM Students S, Enrolled E WHERE S.sid=E.sid AND E.grade=‘B' PROJECT • Can specify a join over two tables as follows: SELECT JOIN S.name E.cid Jones History105 result =

5. Basic SQL Query DISTINCT: optional keyword indicating answer should not contain duplicates. In SQL, default is that duplicates are not eliminated! (Result is called a “multiset”) target-list : A list of attributes of tables in relation-list SELECT [DISTINCT] target-list FROMrelation-list WHERE qualification qualification : Comparisons combined using AND, OR and NOT. Comparisons are Attr op const or Attr1 op Attr2, where op is one of ,,,, etc. relation-list : A list of relation names, possibly with a range-variable after each name

6. Query Semantics • Semantics of an SQL query are defined in terms of the following conceptual evaluation strategy: • FROM clause: compute cross-product of all tables • WHERE clause: Check conditions, discard tuples that fail. (called “selection”). 3. SELECT clause: Delete unwanted fields. (called “projection”). 4. If DISTINCT specified, eliminate duplicate rows. • Probably the least efficient way to compute a query! • An optimizer will find more efficient strategies to get the same answer.

7. Query Semantics Example SELECT sname FROM Sailors, Reserves WHERE Sailors.sid=Reserves.sid AND bid=103 Boats X Sailors Reserves

8. Step 1: Compute the cross product Sailors Reserves ... SailorsXReserves

9. Step 1: How big? Sailors Reserves Question: If |S| is cardinality of Sailors, and |R| is cardinality of Reserves, What is the cardinality of Sailors X Reserves? Answer: |S| * |R| |Sailors X Reserves| = 3X2 = 6

10. Step 2: Check conditions in where clause SELECT sname FROM Sailors, Reserves WHERE Sailors.sid=Reserves.sid AND bid=103 SailorsXReserves

11. Step 3: Delete unwanted fields SELECT sname FROM Sailors, Reserves WHERE Sailors.sid=Reserves.sid AND bid=103 SailorsXReserves

12. Range Variables • Used for short hand • Needed when ambiguity could arise e.g two tables with the same column name: SELECT sname FROM Sailors, Reserves WHERE Sailors.sid=Reserves.sid AND Reserves.bid=103 SELECT sname FROM Sailors S, Reserves R WHERE S.sid=R.sid AND R.bid=103 Question: do range variables remind you of anything? • Variables in relational calculus

13. Sometimes you need a range variable e.g a Self-join: SELECT R1.bid, R1.date FROM Reserves R1, Reserves R2 WHERE R1.bid = R2.bid and R1.date = R2.date and R1.sid != R2.sid Reserves Reserves R1 R2 R1 R2 R2 R2 R1

14. Sometimes you need a range variable SELECT R1.bid, R1.day FROM Reserves R1, Reserves R2 WHERE R1.bid = R2.bid and R1.day = R2.day and R1.sid != R2.sid What are we computing? Boats reserved on the same day by different sailors

15. SELECT Clause Expressions • Can use “*” if you want all columns: SELECT * FROM Sailors x WHERE x.age > 20 • Can use arithmetic expressions (add other operations we’ll discuss later) SELECT S.age, S.age-5 AS age1, 2*S.age AS age2 FROM Sailors S WHERE S.sname = ‘Dustin’ • Can use AS to provide column names SELECT S1.sname AS name1, S2.sname AS name2 FROM Sailors S1, Sailors S2 WHERE 2*S1.rating = S2.rating - 1

16. WHERE Clause Expressions • Can also have expressions in WHERE clause: SELECT S1.sname AS name1, S2.sname AS name2 FROM Sailors S1, Sailors S2 WHERE 2*S1.rating = S2.rating - 1 `_’ stands for any one character and `%’ stands for 0 or more arbitrary characters. • “LIKE” is used for string matching. SELECT S.age, S.age-5 AS age1, 2*S.age AS age2 FROM Sailors S WHERE S.sname LIKE ‘B_l%o’

17. Set versus bag semantics • Set • No duplicates • Relational model and algebra use set semantics • Bag • Duplicates allowed • Number of duplicates is significant • SQL uses bag semantics by default

18. Set versus bag example πSIDEnroll Enroll SELECT SIDFROM Enroll;

19. A case for bag semantics • Efficiency • Saves time of eliminating duplicates • Which one is more useful? • πGPAStudent • SELECT GPA FROM Student; • The first query just returns all possible GPA’s • The second query returns the actual GPA distribution • Besides, SQL provides the option of set semantics with DISTINCT keyword

20. SELECT DISTINCT Sailors Reserves Find sailors that have reserved at least one boat SELECT DISTINCT S.sid FROM Sailors S, Reserves R WHERE S.sid=R.sid

21. SELECT DISTINCT SELECT S.sid FROM Sailors S, Reserves R WHERE S.sid=R.sid • How about:

22. Sailors SELECT DISTINCT SELECT S.sname FROM Sailors S, Reserves R WHERE S.sid=R.sid How about: Reserves vs: SELECT DISTINCT S.sname FROM Sailors S, Reserves R WHERE S.sid=R.sid Do we find all sailors that reserved at least one boat?

23. ANDs, ORs, UNIONs and INTERSECTs Find sids of sailors who’ve reserved a red or a green boat Boats SELECT R.sid FROM Boats B,Reserves R WHERE(B.color=‘red’ OR B.color=‘green’) AND R.bid=B.bid Sailors Reserves X

24. ANDs and ORs Find sids of sailors who’ve reserved a red and a green boat Boats SELECT R.sid FROM Boats B,Reserves R WHERE(B.color=‘red’ AND B.color=‘green’) AND R.bid=B.bid X Sailors Reserves

25. Use INTERSECT instead of AND Exercise: try to rewrite this query using a self join instead of INTERSECT! SELECT R.sid FROM Boats B,Reserves R WHERE B.color = ‘red’ AND R.bid=B.bid INTERSECT SELECT R.sid FROM Boats B,Reserves R WHERE B.color = ‘green’ AND R.bid=B.bid Boats Reserves  =

26. Could also use UNION for the OR query SELECT R.sid FROM Boats B, Reserves R WHERE B.color = ‘red’ AND R.bid=B.bid UNION SELECT R.sidFROM Boats B, Reserves R WHERE B.color = ‘green’ AND R.bid=B.bid Boats Reserves = 

27. Sailors EXCEPT: Set Difference Find sids of sailors who have not reserved a boat SELECT S.sid FROM Sailors S EXCEPT SELECT S.sid FROM Sailors S, Reserves R WHERE S.sid=R.sid Reserves First find the set of sailors who have reserved a boat… and then compare it with the rest of the sailors