CSC443

1. CSC443 Integrity Set operations List operations

2. Review • Commands: • Create • Drop • Alter • Insert • Select

3. Syllabus • New syllabus in course info • Reading this week - 4.1-3; 8.6-8; finishing chap 3 • Test: mostly on executing sql statements

4. Integrity - Primary key • Primary key uniqueness was serious • SQL> insert into odetailsvalues(1020, 10506, 1);  2insert into odetails*ERROR at line 1:ORA-00001: unique constraint (S06_011.SYS_C0016803) violated

5. Integrity - Foreign key • Foreign key existence was serious SQL> insert into customers values (1113,'James','123 Park Ave',66666,'333-333-33 33'); insert into customers values (1113,'James','123 Park Ave',66666,'333-333-3333') * ERROR at line 1: ORA-02291: integrity constraint (PEPPERK.SYS_C0016470) violated - parent key not found

6. Integrity - Check or Not Null qoh int check(qoh >= 0), -> set to -3 SQL> insert into parts values (3352,'bolt', -3,55.2,1); insert into parts values (3352,'bolt', -3,55.2,1) * ERROR at line 1: ORA-02290: check constraint (PEPPERK.SYS_C0016465) violated

7. Large Object Types • Clob = characters • Blob = binary Book_review clob(10KB) Image blob(20MB)

8. Assertions and Domains • Domain - a new data type with a pre-defined check • Assertion - statement that the database always validates is true • DOES NOT EXIST IN SQL

9. Triggers • Procedure to keep tables in balance: • First, created a reorder table: SQL> create table reorder 2 (pno number(5) primary key, 3 qty number(38));

10. Triggers - cont • Trigger example: create trigger reorder_rule after update on parts referencing new as nrow for each row when (nrow.qoh < 200) begin insert into reorder values (:nrow.pno, 300); end reorder_rule;

11. Triggers - last one • Result: SQL> update parts set qoh = 50 where qoh = 60; 1 row updated. SQL> select * from reorder; PNO QTY ---------- ---------- 10509 300

12. Commit and Rollback • Transactions - Need all together in order to be in sync. • Example: SQL> commit; Commit complete. SQL> update parts set qoh = 30 where qoh = 100; 1 row updated. SQL> select * from parts where qoh = 100; no rows selected

14. Commit & Rollback Cont SQL> rollback; Rollback complete. SQL> select * from parts where qoh = 100; PNO PNAME QOH PRICE OLEVEL ---------- ------------------------------ ---------- ---------- ---------- 10900 Dr. Zhivago 100 24.99 30

15. Back into Query Swing • List the first name of each student with a grade of less than 75 in any course. • List the average grade of each student assuming every component equal weight • Change the query above to exclude compname “programs” and to only show students with avg over 80.

16. Answers • Select distinct fname from students s, scores g where s.sid = g.sid and points<75; • Select avg(points), sid from scores group by sid; • Select avg(points) as pavg, sid from scores where compname != 'programs' group by sid having avg(points) > 80;

17. Set Operations • Union • Intersect • Except • Optional: all

18. Union • Example: Show the max points from components mixed as another row in scores • select * from (select * from scores union all (select 'MAX',term, lineno, compname, maxpoints from components)) order by lineno, compname, sid;

19. Intersect • Find all sids with scores: select sid from students intersect select sid from scores; • Find all sids with no scores select sid from students where sid not in (select sid from scores intersect select  sid from students);

20. Except - Minus • Select all students with no grades • Select distinct sid from students minus (select sid from scores);

21. SubQueries and Lists Using a sub-query to get one value Select distinct sid from scores where points > (select avg(points) from scores);

22. List Comparison - in • Compare to values in a list - in or not in • List student first names of sid 1111 and 2222 Select distinct fname from students where SID in (1111, 2222);

23. List Comparison - in • Can use a subquery to create the list: • List student first names who are enrolled in term f96 Select distinct fname from enrolls where term = ‘f96’); You try: List first names of students in course 1030, 1031 and 1035

24. List Comparison - any, all, some > ,< , <>,=, >=, <= • Select scores greater than all in the list: Select distinct sid from scores where points >= all (90, 340, 70) Substitute a query listing all points

25. List Comparison - any, all, some • Select scores greater than all in the list: Select distinct sid from scores where points >= all (select points from scores); 1111 • You try: List scores less than all in the list

26. List Comparison - any, all, some Select distinct sid from scores where points <= all (select points from scores); 2222 Try one more: List all scores which are greater than the average score

27. Sub-Query using outer • Sub query acts for every row in the main (outer) query • Use the fields from the outer query in the inner query • List of courses in which all individual components grades are higher than B: Select term, lineno, cno from courses where b <all (select points from scores where scores.lineno = courses.lineno and scores.term = courses.term);

28. Exists • If subquery has any row in result, then exists is TRUE • If no row results, then FALSE Select all courses in which 1111 is enrolled: select distinct cno from courses where exists (select 'a' from enrolls where courses.lineno = enrolls.lineno and sid = ‘1111’); You try: list all the fields from the student table where any score is greater than 90. (use exists)

29. Exists • select * from students where exists (select ‘a’ from scores where students.sid = scores.sid and points > 90);

30. Important Stuff • Database checks integrity • Subqueries help make lists for where conditions • In, All, Some, Any • Exists