Homework 3

Question 1

select count(*) 

from Entertainers E1, Entertainers E2,
Engagements Eng1, Engagements Eng2,
Customers C1, Customers C2

where E1.EntertainerID <> E2.EntertainerID
and   E1.EntertainerID = Eng1.EntertainerID
and   E2.EntertainerID = Eng2.EntertainerID
and   Eng1.CustomerID = C1.CustomerID
and   Eng2.CustomerID = C2.CustomerID
and (select count(*) from Entertainers where C1.CustomerID = C2.CustomerID) > 1
;

Pairs of Entertainers

select count(*) 
from Entertainers E1, Entertainers E2
where E1.EntertainerID <> E2.EntertainerID
;

Question 2 - Customers with the same musical tastes

select A.CustomerID, B.CustomerID
from Customers A, Customers B
where A.CustomerID <> B.CustomerID
and not exists
(
  select * from Musical_Preferences MP
   where MP.CustomerID = A.CustomerID
     and MP.StyleID not in
  (
    select MP2.StyleID 
      from Musical_Preferences MP2
     where MP2.CustomerID = B.CustomerID
  )
)
and not exists
(
  select * from Musical_Preferences MP3
   where MP3.CustomerID = B.CustomerID
     and MP3.StyleID not in
  ( 
    select MP4.StyleID
      from Musical_Preferences MP4
     where MP4.CustomerID = A.CustomerID
  )
)
;

Question #4 = Pairs of Agents with overlapping Customer State

select A.AgentID, B.AgentID
from Agents A, Agents B
where A.AgentID <> B.AgentID
and exists
(
  select * from Engagements Eng, Customers C
  where Eng.AgentID = A.AgentID
    and Eng.CustomerID = C.CustomerID
    and C.CustState IN
  (
    select C2.CustState from Engagements Eng2, Customers C2
     where Eng2.AgentID = B.AgentID
       and Eng2.CustomerID = C2.CustomerID
  )
)
;

Music Preferences

List of All Styles that at least one Customer Admires

select distinct MP.StyleID 
from Customers C, Musical_Preferences MP
where C.CustomerID = MP.CustomerID
order by MP.StyleID
;

Customer

select * from Customers C
;

select count(*) from 
Customers C1, Customers C2
where C1.CustomerID <> C2.CustomerID
and not exists
( (select distinct MP1.StyleID from Musical_Preferences MP1
   where C1.CustomerID = MP1.CustomerID)
intersect
  (select distinct MP2.StyleID from Musical_Preferences MP2
   where C2.CustomerID = MP2.CustomerID)
)
;

something along the lines of customers without intersecting styles

select count(*) from 
Customers C1, Customers C2
where C1.CustomerID <> C2.CustomerID
and not exists
( select distinct MP1.StyleID from Musical_Preferences MP1
    join Musical_Preferences MP2
      on MP1.StyleID = MP2.StyleID
   where C1.CustomerID = MP1.CustomerID
     and C2.CustomerID = MP2.CustomerID
)
;

Closer

select count(*) from 
Customers C1, Customers C2,
Musical_Preferences MP1, Musical_Preferences MP2
where C1.CustomerID <> C2.CustomerID
  and C1.CustomerID = MP1.CustomerID
  and C2.CustomerID = MP2.CustomerID
  and not exists
( select distinct MP1.StyleID from Musical_Preferences MP1
    join Musical_Preferences MP2
      on MP1.StyleID = MP2.StyleID
   where C1.CustomerID = MP1.CustomerID
     and C2.CustomerID = MP2.CustomerID
     and MP1.CustomerID <> MP2.CustomerID
)
;

Concrete

Customer 10001 Prefs

select * from Musical_Preferences MP
where CustomerID = 10001
;

Customer 10008 Prefs

select * from Musical_Preferences MP
where CustomerID = 10008
;

Intersect the Two customers preferences

select C1P.CustomerID, C2P.CustomerID, C1P.StyleID
from (select * from Musical_Preferences MP1
where MP1.CustomerID = 10014) as C1P
join (select * from Musical_Preferences MP2
where MP2.CustomerID = 10015) as C2P
on C1P.StyleID = C2P.StyleID
;

EXISTS - Always (get all customers)

select * from Customers C
where exists (
select C1P.CustomerID, C2P.CustomerID, C1P.StyleID
from (select * from Musical_Preferences MP1
where MP1.CustomerID = 10001) as C1P
join (select * from Musical_Preferences MP2
where MP2.CustomerID = 10008) as C2P
on C1P.StyleID = C2P.StyleID)
;

Exists Generalized

select C1.CustomerID, C2.CustomerID 
from Customers C1, Customers C2
where exists 
(
select C1P.CustomerID, C2P.CustomerID, C1P.StyleID
from (select * from Musical_Preferences MP1
where MP1.CustomerID = C1.CustomerID) as C1P
join (select * from Musical_Preferences MP2
where MP2.CustomerID = C2.CustomerID) as C2P
on C1P.StyleID = C2P.StyleID
)

and C1.CustomerID <> C2.CustomerID
;

select * from Customers C
where exists 
(
select * from Musical_Preferences MP 
where MP.CustomerID = C.CustomerID
and exists (select * from Customers CN where CN.CustomerID = C.CustomerID)
)
;

#3 - Pairs of Customers who have no Styles in Common

NOT EXISTS and INNER JOIN

select C1.CustomerID, C2.CustomerID 
from Customers C1, Customers C2
where not exists 
(
select * from Musical_Preferences MP1
  join Musical_Preferences MP2
    on MP1.StyleID = MP2.StyleID
 where MP1.CustomerID = C1.CustomerID
   and MP2.CustomerID = C2.CustomerID
)
and C1.CustomerID <> C2.CustomerID
;

NOT EXISTS and LEFT JOIN IS NOT NULL

select C1.CustomerID, C2.CustomerID 
from Customers C1, Customers C2
where C1.CustomerID <> C2.CustomerID
  and not exists 
(
select * from Musical_Preferences MP1
left join Musical_Preferences MP2
  on MP1.StyleID = MP2.StyleID
where MP1.CustomerID = C1.CustomerID
  and MP2.CustomerID = C2.CustomerID
  and MP2.StyleID IS NOT NULL
)
;

Similar to #3 - customer who have at least one preference in common.

EXISTS and INNER

select C1.CustomerID, C2.CustomerID 
from Customers C1, Customers C2
where exists 
(
select * from Musical_Preferences MP1
  join Musical_Preferences MP2
    on MP1.StyleID = MP2.StyleID
 where MP1.CustomerID = C1.CustomerID
   and MP2.CustomerID = C2.CustomerID
)
and C1.CustomerID <> C2.CustomerID
;

EXIST and LEFT JOIN IS NULL

select C1.CustomerID, C2.CustomerID 
from Customers C1, Customers C2
where C1.CustomerID <> C2.CustomerID
  and exists 
(
select * from Musical_Preferences MP1
left join Musical_Preferences MP2
  on MP1.StyleID = MP2.StyleID
where MP1.CustomerID = C1.CustomerID
  and MP2.CustomerID = C2.CustomerID
  and MP2.StyleID IS NOT NULL
)
;

#2 - Same Musical Preferences

select C1.CustomerID, C2.CustomerID 
from Customers C1, Customers C2
where C1.CustomerID <> C2.CustomerID
  and not exists 
(
select * from Musical_Preferences MP1
left join Musical_Preferences MP2
  on MP1.StyleID = MP2.StyleID
where MP1.CustomerID = C1.CustomerID
  and MP2.CustomerID = C2.CustomerID
  and MP2.StyleID IS NULL
)
;

Concrete example for the Subquery

select * from Musical_Preferences MP1
left join Musical_Preferences MP2
  on MP1.StyleID = MP2.StyleID
where MP1.CustomerID = 10001
  and MP2.CustomerID = 10010
  and MP2.StyleID IS NULL

Intersection of Musical Interests

select * from Musical_Preferences MP1
  full outer join Musical_Preferences MP2
    on MP1.StyleID = MP2.StyleID
 where MP1.CustomerID = 10001
   and MP2.CustomerID = 10008

Showing changes from previous revision. ~~Removed~~ | Added

Revision by hunter, 2009-11-20