Set CSC443
Integrity 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
Primary key uniqueness was serious SQL> insert into odetails values(1020, 10506, 1); 2 insert into odetails * ERROR at line 1: ORA-00001: unique constraint (S06_011.SYS_C0016803) violated
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
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
Large Object Types
Clob = characters Blob = binary Book_review clob(10KB) Image blob(20MB)
Assertions and Domains
Domain - a new data type with a predefined check Assertion - statement that the database always validates is true DOES NOT EXIST IN SQL
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));
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;
Triggers - last one
Result: SQL> update parts set qoh = 50 where qoh = 60; 1 row updated. SQL> select * from reorder; PNO QTY ---------- ---------10509 300
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
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
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.
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' by group sid having avg(points) > 80;
Set Operations
Union Intersect Except
Optional: all
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;
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);
Except - Minus
Select all students with no grades Select distinct sid from students minus (select sid from scores);
SubQueries and Lists
Using a sub-query to get one value Select distinct sid from scores where points > (select avg(points) from scores);
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);
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
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
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
List Comparison - any, all, so...