A.(1,2) of R B.(3,4) of R C.(2,4,6) of S D.All of the above E.None of the above 90A B 1 2 3 4 5 6 B C D 2 4 6 4 6 8 4 7 9 R(A,B) S(B,C,D)
Back to SQL So far……We have seen simple Select From Where examples We have seen union intersect and except (Minus) examples We have nested queries We have seen aggregate functions being used in SQL Queries, as well as group by and having clauses We have seen nulls and how and what we mean by outer joins But…How do we modify our relations? What are assertions and triggers? 91
92Database Manipulation Insertion redux Can insert a single tuple using: INSERT INTO Student VALUES(53688, ‘Smith’, ‘222 W.15thave’, 333-4444, MATH) or INSERT INTO Student (sid, name, address, phone, major) VALUES(53688, ‘Smith’, ‘222 W.15thave’, 333-4444, MATH)Add a tuple to student with null address and phone: INSERT INTO Student (sid, name, address, phone, major) VALUES(33388, ‘Chan’, null, null, CPSC)
93Database Manipulation Insertion redux (cont) Can add values selected from another table Enroll student 51135593 into every class taught by faculty 90873519 INSERT INTO Enrolled SELECT 51135593, name FROM Class WHERE fid = 90873519 The select-from-where statement is fully evaluated before any of its results are inserted So, queries like INSERT INTO table1 SELECT * FROM table1 are ok.
94Database Manipulation Deletion Note that only whole tuples are deleted. Can delete all tuples satisfying some condition (e.g., name = Smith): DELETE FROM Student WHERE name = ‘Smith’ Delete all students whose age is above the average student age: DELETE FROM Student WHERE age > (SELECT avg(age) FROM Student)
95Database Manipulation Updates Increase the age of all students by 2 (should not be more than 100) Need to write two updates: UPDATE Student SET age = 100 WHERE age >= 98 UPDATE Student SET age = age + 2 WHERE age < 98 Is the order important?
96Integrity Constraints (Review) An IC describes conditions that every legal instance of a relation must satisfy. Inserts/deletes/updates that violate IC’s are disallowed. Can ensure application semantics (e.g., sidis a key), or prevent inconsistencies (e.g., snamehas to be a string, agemust be < 200) Types of IC’s: domain constraints, primary key constraints, foreign key constraints, general constraints
97General Constraints: Check Created with a CHECK clause. Constraints can be named Can use subqueries to express constraint CREATE TABLE Student ( snum INTEGER, sname CHAR(32), majorCHAR(32), standing CHAR(2)ageREAL, PRIMARY KEY (snum), CHECK( age >= 10 ANDage < 100 ); CREATE TABLE Enrolled ( snum INTEGER, cname CHAR(32), PRIMARY KEY (snum, cname), CONSTRAINT noR15 CHECK(`R15’ <> ( SELECTc.room FROMclass c WHEREc.name=cname))); Check constraints are checked when tuples are inserted or modified
98Constraints over Multiple Relations: Remember this one? We couldn’t express “every employee works in a department and every department has some employee in it”? Neither foreign-key nor not-null constraints in Works_In can do that.