Announcements

1. Announcements • Read 6.7 – 6.10 for Wednesday • Homework 6, due today

2. SQL Database Manipulation Language Lecture 16

3. Example – Using Functions • Find the total number of students enrolled in ART103A COUNT returns the number of values in the column SUM returns the sum of the values in the column AVG returns the mean of the values in the column MAX returns the largest value in the column MIN returns the smallest value in the column

4. Example – Using Functions • Find the number of departments that have Faculty in them. • SELECT COUNT(DISTINCT department) FROM Faculty; • Find the average number of credits student have.

5. Examples – Using Functions • Find the student with the largest number of credits.

6. Find the ID of the student(s) with the highest grade in any course SELECT stuId FROM Enroll WHERE grade = (SELECT MIN(grade) From Enroll); Find names and IDs of students who have less than the average number of credits Examples – Using Functions

7. Example – Using an Expression and a String Constant • Assuming each course is three credits list, for each student, the number of courses he or she has completed • SELECT stuId, ‘Number of courses =‘, credits/3 FROM Student;

8. Example – Use of GROUP BY • For each course, show the number of students enrolled • SELECT classNumber, COUNT(*) FROM Enroll GROUP BY classNumber; GROUP BY allows us to put together all records with a single value in the specified field

9. Example – Use of HAVING • Find all courses in which fewer than three students are enrolled • SELECT classNumber FROM Enroll GROUP BY classNumber HAVING COUNT(*) < 3 HAVING is used to determine which groups have a quality, just as WHERE is used with tuples to determine which records have some quality.

10. Example – Use of LIKE • Get details of all MTH courses • SELECT * FROM Class WHERE classNumber LIKE ‘MTH%’; % The percent character stands for any sequence of characters of any length >= 0 _ The underscore character stands for any single character.

11. Example – Use of NULL • Find the stuId and classNumber of all students whose grades in that course are missing • SELECT stuId, classNumber FROM Enroll WHERE grade IS NULL;

12. Example – Inserting multiple records • Create and fill a new table that shows each course and the number of students enrolled in it • CREATE TABLE Enrollment ( classNumber CHAR(7) NOT NULL, students SMALLINT);

13. Example – Updating with a Query • Change the room to B220 for all courses taught by Tanaka • UPDATE Class SET room = ‘B220’ WHERE facId = (SELECT facId FROM Faculty WHERE name = ‘Tanaka’);

14. Example – Delete with a subquery • Erase all enrollment records for Owen McCarthy • DELETE FROM Enroll WHERE stuId = (SELECT stuId FROM Studet WHERE lastName = ‘McCarthy’ AND firstName = ‘Owen’);

15. Active Databases-Constraints • DBMS monitors database to prevent illegal states, using constraints and triggers • Constraints • can be specified when table is created, or later • IMMEDIATE MODE: constraint checked when each INSERT, DELETE, UPDATE is performed • DEFERRED MODE: postpones constraint checking to end of transaction – write SET CONSTRAINT name DEFERRED • Can use DISABLE CONSTRAINT name, and later ENABLE CONSTRAINT name

16. Triggers • More flexible than constraints • Must have three parts: • event, some change made to the database • condition, a logical predicate (can be empty) • action, a procedure done when the event occurs and the condition is true, also called firing the trigger • Can be fired before or after insert, update, delete • Trigger can access values it needs as :OLD. and :NEW. • prefix :OLD refers to values in a tuple deleted or to the values replaced in an update • prefix :NEW refers to the values in a tuple just inserted or to the new values in an update. • Can specify whether trigger fires just once for each triggering statement, or for each row that is changed by the statement

17. Trigger Syntax CREATE OR REPLACE TRIGGER trigger_name [BEFORE/AFTER] [INSERT/UPDATE/DELETE] ON table_name [FOR EACH ROW] [WHEN condition] BEGIN trigger body END; • Can disable triggers using ALTER TRIGGER name DISABLE; • Later write ALTER TRIGGER name ENABLE; • Can drop triggers using DROP TRIGGER name;

18. Trigger for Student Enrolling in a Class CREATE TRIGGER ADDENROLL AFTER INSERT ON RevEnroll FOR EACH ROW BEGIN UPDATE RevClass SET currentEnroll = currentEnroll + 1 WHERE RevClass.classNumber = :NEW.classNumber; END;

19. Trigger for Student Dropping a Class CREATE TRIGGER DROPENROLL AFTER DELETE ON RevEnroll FOR EACH ROW BEGIN UPDATE RevClass SET currentEnroll = currentEnroll – 1 WHERE RevClass.classNumber = :OLD.classNumber; END;

20. Trigger for Student Changing Classes CREATE TRIGGER SWITCHENROLL AFTER UPDATE OF classNumber ON RevEnroll FOR EACH ROW BEGIN UPDATE RevClass SET currentEnroll = currentEnroll + 1 WHERE RevClass.classNumber = :NEW.classNumber; UPDATE RevClass SET currentEnroll = currentEnroll – 1 WHERE RevClass.classNumber = :OLD.classNumber; END;

21. Trigger for Checking for Over-enrollment Before Enrolling Student CREATE TRIGGER ENROLL_REQUEST BEFORE INSERT OR UPDATE OF classNumber ON RevEnroll FOR EACH ROW DECLARE numStu number; maxStu number; BEGIN set maxEnroll into maxStu from RevClass where RevClass.classNumber = :NEW.classNumber; set currentEnroll + 1 into numStu from RevClass where RevClass.classNumber = :NEW.classNumber; if numStu > maxStu RequestClosedCoursePermission(:NEW.stuId, :NEW.classNumber, RevClass.currentEnroll, RevClass.maxEnroll); end if; END;

22. Example Trigger • Prevent students from enrolling in two classes that meet at the same time • CREATE TRIGGER NOTTWOENROLL

23. Ending Transactions • COMMIT makes permanent changes in the current transaction • ROLLBACK undoes changes made by the current transaction