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
• SQLgt insert into odetailsvalues(1020, 10506, 1)
   2insert into odetailsERROR at line
  1ORA-00001 unique constraint
  (S06_011.SYS_C0016803) violated

5
Integrity - Foreign key

• Foreign key existence was serious
• SQLgt 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 gt 0), -gt set to -3
• SQLgt 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
• SQLgt 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 lt 200)
• begin
• insert into reorder values (nrow.pno, 300)
• end reorder_rule

11
Triggers - last one

• Result
• SQLgt update parts set qoh 50 where qoh 60
• 1 row updated.
• SQLgt select from reorder
• PNO QTY
• ---------- ----------
• 10509 300

12
Commit and Rollback

• Transactions - Need all together in order to be
  in sync.
• Example
• SQLgt commit
• Commit complete.
• SQLgt update parts set qoh 30 where qoh 100
• 1 row updated.
• SQLgt select from parts where qoh 100
• no rows selected

13
(No Transcript)
14
Commit Rollback Cont

• SQLgt rollback
• Rollback complete.
• SQLgt 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 pointslt75
• 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) gt 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 gt
  (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

• gt ,lt , ltgt,, gt, lt
• Select scores greater than all in the list
• Select distinct sid from scores where points gt
  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 gt
  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 lt
  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
  ltall (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 gt 90)

30
Important Stuff

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