1 / 30

CSC443

CSC443. Integrity Set operations List operations. Review. Commands: Create Drop Alter Insert Select. Syllabus. New syllabus in course info Reading this week - 4.1-3; 8.6-8; finishing chap 3 Test: mostly on executing sql statements. Integrity - Primary key.

robertaford
Download Presentation

CSC443

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

  13. 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

  14. 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.

  15. 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;

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

  17. 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;

  18. 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);

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

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

  21. 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);

  22. 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

  23. 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

  24. 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

  25. 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

  26. 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);

  27. 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)

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

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

More Related