SQL

1. SQL Database Management Systems, 3rd ed.,Ramakrishnan and Gehrke, Chapter 5

2. Using MySQL Reference manual: http://dev.mysql.com/doc/refman/5.0/en/index.html To use MySQL, log onto Pluto and invoke MySQL: mysql -u <your userid> -p You will be prompted for your password.

3. Using MySQL (continued) To create new database: create database <name of database>; To see names of your databases: show databases; To use one of your databases: use <name of database>;

4. Using MySQL (continued) To see names of all tables in current database: show tables; To review definition of particular table: describe <name of table>;

5. Using MySQL (continued) To get information from manual about a command: help <name of command>; For example: help create table; To exit MySQL: quit;

6. MySQL Storage Engines Act as handlers for different table types: transaction-safe non-transaction-safe

7. MyISAM Provides high-speed storage and retrieval. Non-transaction-safe - each operation treated as an atomic operation; okay for single-user databases

8. InnoDB ACID compliance (atomicity, consistency, isolation, durability) Transaction-safe - can group multiple operations into one atomic action with all-or-nothing semantics

9. Data Definition To create relation: Student (ID, Name, Login, Age, gpa) use the SQL command: CREATE TABLE Student ( ID CHAR(9), Name CHAR(30), Login CHAR(20), Age INTEGER, gpa DECIMAL(4,2), UNIQUE (Login), PRIMARY KEY (ID)) ENGINE=MyISAM;

10. Data Definition (continued) Following syntax will work if PK and all other CKs are single attributes: CREATE TABLE Student ( ID CHAR(9) PRIMARY KEY, Name CHAR(30), Login CHAR(20) UNIQUE, Age INTEGER, gpa DECIMAL(4,2)) ENGINE=MyISAM;

11. Data Definition (continued) What information is represented by these relations? Students (ID, Name, Login, Age, gpa) Courses (CourseNum, CourseName, CreditHours) FinalGrades (ID, CourseNum, Grade)

12. Data Definition (continued) For the FinalGrades relation: CREATE TABLE FinalGrades ( ID CHAR(9), CourseNum CHAR(20), Grade CHAR(2), PRIMARY KEY (ID, CourseNum), FOREIGN KEY (ID) REFERENCES Students (ID), FOREIGN KEY (CourseNum) REFERENCES Courses (CourseNum)) ENGINE=InnoDB;

13. More About MyISAM vs. InnoDB Suppose you try to insert or update tuple that causes primary key, unique key, or foreign key violation. What will happen with MyISAM? What will happen with InnoDB?

14. CHECK Clause CREATE TABLE Students ( ID CHAR(9), Name CHAR(30), Login CHAR(20), Age INTEGER, gpa DECIMAL(4,2), UNIQUE (Login), PRIMARY KEY (ID), CHECK (Age >= 16 AND Age <= 80)); Parsed but not implemented in current version of MySQL.

15. In-class exercises: Exercise 5.8 on page 179: 1, 2(b), 2(j) Exercise 5.10.1 on page 180

16. CREATE DOMAIN Define new domain with constraints: CREATE DOMAIN AgeRange INTEGER CHECK (VALUE >= 16 AND Value <= 80); Can use in table definition: CREATE TABLE Students ( ID CHAR(9), Name CHAR(30), Login CHAR(20), Age AgeRange, gpa DECIMAL(4,2), UNIQUE (Login), PRIMARY KEY (SSN)); Not supported in MySQL.

17. CREATE DOMAIN (continued) Defining default value: CREATE DOMAIN AgeRange INTEGER DEFAULT 18 CHECK (VALUE >= 16 AND VALUE <= 80);

18. CREATE TYPE To avoid nonsensical comparisions such as comparing sailor ID with boat ID: CREATE TYPE SailorIDType as INTEGER; Not supported in MySQL.

19. Constraints Over Multiple Tables Suppose the number of sailors plus the number of boats should be less than 100. This will not work: CREATE TABLE Sailors ( sid INTEGER, sname CHAR(10), rating INTEGER, age REAL, PRIMARY KEY (sid), CHECK ((SELECT COUNT (S.sid) FROM Sailors S) + (SELECT COUNT(B.bid) FROM BOATS B) < 100));

20. Assertion CREATE ASSERTION SmallClub CHECK ((SELECT COUNT (S.sid) FROM Sailors S) + (SELECT COUNT (B.bid) FROM Boats B) < 100); Not supported by MySQL.

21. In-class Exercise: Exercise 5.10.2 on page 180

22. Enforcing Integrity Constraints What happens if we: • Try to insert tuple with same PK as another tuple in table? • Try to insert tuple with null PK? • Try to insert tuple with value for field that’s not in domain (type) for that field? • Try to modify tuple’s PK so that new PK value is same as another in table? Database Management Systems, 3rd ed., Ramakrishnan and Gehrke, Section 3.3

23. Enforcing Integrity Constraints (continued) What about referential integrity constraints? Suppose we have tables for: Students (sid, name, login, age, gpa) Enrolled (sid, cid, grade) sid is a FK that references Students. How can insertions into Enrolled cause a problem? How can deletions from Students cause a problem? How can modifications in either table cause a problem?

24. Enforcing Integrity Constraints (continued) Options for handling deletion problems with Students: • Delete all Enrolled tuples that refer to deleted Students tuple. • Disallow deletion of Students tuple if an Enrolled tuple refers to it. • Set student ID field to some existing “default” student for every Enrolled tuple that refers to the deleted Students tuple. • For every Enrolled tuple that refers to it, set student ID field to null. (In this case, not an option because student ID is part of PK for Enrolled and cannot be null.)

25. How SQL Handles the Options CREATE TABLE Enrolled ( sid CHAR(20), cid CHAR(20), grade CHAR(10), PRIMARY KEY (sid, cid), FOREIGN KEY (sid) REFERENCES Students ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE=InnoDB; Instead, what if we’d said “ON UPDATE CASCADE”?

26. DROP TABLE DROP TABLE Students; DROP TABLE Students RESTRICT; DROP TABLE Students CASCADE;

27. ALTER TABLE • Add or delete columns • Create or destroy indexes • Change column type • Rename columns • Change table type

28. Examples of ALTER TABLE ALTER TABLE Students ADD COLUMN maiden-name CHAR(10); (What happens to existing tuples?) ALTER TABLE Students RENAME TO Undergrads; (Privileges must be reassigned.) ALTER TABLE Students ENGINE = InnoDB;

29. Examples of ALTER TABLE ALTER TABLE Students DROP Phone, DROP Lot; (Deletes columns called Phone and Lot.) ALTER TABLE Students CHANGE SSN ID CHAR(9); (Changes name of SSN column to ID; possibly also changed type.) ALTER TABLE Students CHANGE ID ID INTEGER; (Changes type of the ID column.

30. SQL Query SELECT [DISTINCT] select-list FROM from-list WHERE qualification;

31. Query Evaluation Conceptually: • Compute cross-product of tables in from-list. • Delete rows that fail qualification. • Delete columns not in select-list. • If DISTINCT, delete duplicate rows.

32. Query Examples (Q15) Find names and ages of all sailors. SELECT DISTINCT S.sname, S.age FROM Sailors S; SELECT DISTINCT Sailors.sname, Sailors.age FROM Sailors; SELECT DISTINCT sname, age FROM Sailors;

33. Query Examples (continued) (Q11) Find sailors with rating above 7. SELECT S.sid, S.sname, S.rating, S.age FROM Sailors AS S WHERE S.rating > 7; SELECT * FROM Sailors WHERE rating > 7;

34. Query Examples (continued) (Q1) Find names of sailors who have reserved boat number 103. SELECT S.sname FROM Sailors S Reserves R WHERE S.sid = R.sid AND R.bid = 103;

35. Query Examples (continued) (Q16) Find sids of sailors who have reserved a red boat. SELECT R.sid FROM Boats B, Reserves R WHERE B.bid = R.bid AND B.color = ‘red’; (Q2) Find names of sailors who have reserved a red boat. SELECT S.sname FROM Sailors S, Reserves R, Boats B WHERE S.sid = R.sid AND R.bid = B.bid AND B.color = ‘red’;

36. Query Examples (continued) (Q17) Compute increments for ratings of persons who have sailed two different boats on same day. SELECT S.sname, S.rating+1 AS rating FROM Sailors S, Reserves R1, Reserves R2 WHERE S.sid = R1.sid AND S.sid = R2.sid AND R1.day = R2.day AND R1.bid <> R2.bid;

37. Query Examples (continued) String comparisons: Can use comparison operators (=, <, >, etc.) to compare strings. Can also use LIKE operator as well as wild-card symbols. % 0 or more arbitrary characters _ exactly 1 character For example, ‘_AB%’ denotes a pattern matching any string containing 3 or more characters, with the second and third characters being AB.

38. Query Examples (continued) (Q18) Find ages of sailors whose last name begins and ends with B and has at least 3 characters. SELECT S.age FROM Sailors S WHERE S.sname LIKE ‘B_%B’;

39. Query Examples (continued) (Q5) Find names of sailors who have reserved a red or a green boat. SELECT S.sname FROM Sailors S, Reserves R, Boats B WHERE S.sid = R.sid AND R.bid = B.bid AND (B.color = ‘red’ OR B.color = ‘green’);

40. Query Examples (continued) (Q5) Find names of sailors who have reserved a red or a green boat. Alternate solution: SELECT S.sname FROM Sailors S, Reserves R, Boats B WHERE S.sid = R.sid AND R.bid = B.bid AND B.color = ‘red’ UNION SELECT S2.name FROM Sailors S2, Boats B2, Reserves R2 WHERE S2.sid = R2.sid AND R2.bid = B2.bid AND B2.color = ‘green’;

41. Query Examples (continued) (Q6) Find names of sailors who have reserved both a red and a green boat. Incorrect solution: SELECT S.sname FROM Sailors S, Reserves R, Boats B WHERE S.sid = R.sid AND R.bid = B.bid AND (B.color = ‘red’ AND B.color = ‘green’); Why won’t this work?

42. Query Examples (continued) (Q6) Find names of sailors who have reserved both a red and a green boat. SELECT S.sname FROM Sailors S, Reserves R1, Boats B1, Reserves R2, Boats B2 WHERE S.sid = R1.sid AND R1.bid = B1.bid AND S.sid = R2.sid AND R2.bid = B2.bid AND B1.color = ‘red’ AND B2.color = ‘green’;

43. Query Examples (continued) (Q6) Find names of sailors who have reserved both a red and a green boat. Alternate solution with small bug (to be solved later): SELECT S.sname FROM Sailors S, Reserves R, Boats B WHERE S.sid = R.sid AND R.bid = B.bid AND B.color = ‘red’ INTERSECT SELECT S2.sname FROM Sailors S2, Boats B2, Reserves R2 WHERE S2.sid = R2.sid AND R2.bid = B2.bid AND B2.color = ‘green’; INTERSECT is not supported in MySQL.

44. Query Examples (continued) (Q19) Find sids of sailors who have reserved red boats but not green boats. SELECT R.sid FROM Reserves R, Boats B WHERE R.bid = B.bid AND B.color = ‘red’ EXCEPT SELECT R2.sid FROM Boats B2, Reserves R2 WHERE R2.bid = B2.bid AND B2.color = ‘green’; This solution assumes there are no reservations for nonexisting sailors - referential integrity. EXCEPT and MINUS are not supported in MySQL.

45. Query Examples (continued) (Q20) Find sids of sailors who have rating of 10 or reserved boat 104. SELECT S.sid FROM Sailors S WHERE S.rating = 10 UNION SELECT R.sid FROM Reserves R WHERE R.bid = 104; MySQL supports UNION.

46. Query Examples (continued) (Q19) Find sids of all sailors who have reserved red boats but not green boats. SELECT R1.sid FROM Reserves R1, Boats B1 WHERE R1.bid=B1.bid AND B1.color=‘red’ AND R1.sid NOT IN (SELECT R2.sid FROM Reserves R2, Boats B2 WHERE R2.bid=B2.bid AND B2.color=‘green’);

47. Query Examples (continued) (Q2) Find names of sailors who have reserved a red boat. SELECT S.sname FROM Sailors S WHERE S.sid IN ( SELECT R.sid FROM Reserves R WHERE R.bid IN ( SELECT B.bid FROM Boats B WHERE B.color = ‘red’)); How can you modify this query to find the names of those sailors who have s reserved a red boat?

48. Query Examples (continued) (Q1) Find names of sailors who have reserved a red boat. SELECT S.sname FROM Sailors S WHERE EXISTS ( SELECT * FROM Reserves R WHERE R.bid = 103 AND R.sid = S.sid);

49. Query Examples (continued) (Q22) Find sailors whose rating is better than some sailor called Horatio. SELECT S.sid FROM Sailors S WHERE S.rating > ANY ( SELECT S2.rating FROM Sailors S2 WHERE S2.sname = ‘Horatio’);

50. Query Examples (continued) (Q23) Find sailors whose rating is better than every sailor called Horatio. SELECT S.sid FROM Sailors S WHERE S.rating > ALL ( SELECT S2.rating FROM Sailors S2 WHERE S2.sname = ‘Horatio’); What if there is no sailor named Horatio? How can we modify this query to get the sailors with the highest rating?