2009-nov-16

Class Notes - November 16, 2009

Questions

How to return distinct tuples regardless of order

e.g if I do

Problem:

Find pairs of students (A,B) where A is taking a subset of courses of B.

select C1.Customer, C2.CustomerID
from Customers C1, Customers C2
where C1.Customer <> C2.CustomerID

Find pairs

Select A.StudentID, B.StudentID
From Students A, Students B
Where A.StudentID <> B.StudentID
and not exists
( select * from Student_Schedules SS
  where SS.StudentID=A.StudentID 
  and not exists
  ( 
    select * from Student_Schedules SS2 
    where SS2.StudentID = B.StudentID
    and SS.ClassID = SS2.ClassID
  )
)

;

There does not exists a course for A that violates the subset constraint.
not exists - connect outer with inner query (correlate)

looking for violators: course student A is in that student B is not.

Same Result using IN

Select A.StudentID, B.StudentID
From Students A, Students B
Where A.StudentID <> B.StudentID
and not exists
( select * from Student_Schedules SS
  where SS.StudentID=A.StudentID 
  and not in
  ( 
    select SS2.ClassID from Student_Schedules SS2 
    where SS2.StudentID = B.StudentID
    and SS.ClassID = SS2.ClassID
  )
)
;

Same Set of Courses

Select A.StudentID, B.StudentID
From Students A, Students B
Where A.StudentID <> B.StudentID
and not exists
( select * from Student_Schedules SS
  where SS.StudentID=A.StudentID and SS.ClassID 
  not in
  ( 
    select SS2.ClassID from Student_Schedules SS2 
    where SS2.StudentID = B.StudentID
  )
)
and not exists
( select * from Student_Schedules SS3
  where SS3.StudentID=B.StudentID and SS3.ClassID 
  not in
  ( 
    select SS4.ClassID from Student_Schedules SS4
    where SS4.StudentID = A.StudentID
  )
)
;

Longest duration

select c.duration from classes c
where c.duration >= all (select c2.duration from classes c2)

Five Basic Math Functions

• Count
• Sum
• Avg
• Max
• Min

Examples

Avg and Best

select avg(SS.grade) as avg_enrolled_grade,
max(SS.grade) as best_enrolled_grade
from Student_Schedules SS
where SS.ClassStatus = 1;

select S.StudentID from student S,
Student_Schedules SS
where S.StudentID = SS.StudentID
and SS.ClassStatus =1 and SS.Grade >= (
select avg() from
)

Group By

select SS.StudentID, avg(SS.Grade) as
avgEnrolledGrade from Student_Schedules SS
where SS.ClassStatus = 1
group by SS.StudentID

My tags:

 

Popular tags: