CS

 338  –  Fall  2013  

Solutions  to  Assignment  #1  
 

TPC-­‐H  

1-­‐ SELECT  c_name  

FROM  customer  
WHERE  c_acctbal  >  9000  AND  c_mktsegment  =  'HOUSEHOLD';  
 
2-­‐ SELECT  DISTINCT  p_partkey,  p_name  
FROM  part,  partsupp,  supplier,  nation,  region  
WHERE  p_partkey  =  ps_partkey  AND  ps_suppkey  =  s_suppkey  AND  s_nationkey  =  n_nationkey  
AND  n_regionkey  =  r_regionkey  AND  r_name  =  'MIDDLE  EAST'  
ORDER  BY  p_partkey;  
 
3-­‐ SELECT  COUNT(DISTINCT  ps_partkey)  
FROM  partsupp,  supplier,  nation,  region  
WHERE  ps_suppkey  =  s_suppkey  AND  s_nationkey  =  n_nationkey  AND  n_regionkey  =  
r_regionkey  AND  r_name  =  'EUROPE'  
 
4-­‐ (SELECT  p_partkey,  p_partname  FROM  part)  
EXCEPT  
(SELECT  p_partkey,  p_partname  FROM  part,  partsupp,  supplier,  nation,  region  WHERE  
ps_suppkey  =  s_suppkey  AND  s_nationkey  =  n_nationkey  AND  n_regionkey  =  r_regionkey  AND  
r_name  =  'EUROPE')  
 
5-­‐  
WITH  custkeys  AS  (  
(SELECT  c_custkey  FROM  customer)  
EXCEPT  
(SELECT  c_custkey    
FROM  lineitem,  orders,  customer,  supplier,  nation  nc,  nation  ns  
WHERE  l_orderkey  =  o_orderkey  AND  o_custkey  =  c_custkey  AND  c_nationkey  =  nc.n_nationkey  
AND  nc.n_regionkey  <>  ns.n_regionkey  AND  l_suppkey  =  s_suppkey  AND  s_nationkey  =  
ns.n_nationkey)  
)  
 
SELECT  DISTINCT  c_name,  c_phone,  r_name  
FROM  custkeys,  customer,  nation,  region  
WHERE  custkeys.c_custkey  =  customer.c_custkey  AND  c_nationkey  =  n_nationkey  AND  
n_regionkey  =  r_regionkey  
  
6-­‐ SELECT  MAX(l_extendedprice)  
FROM  lineitem  
WHERE  l_discount  >  l_tax  
 
7-­‐ WITH  timelyorders  AS  (  
(SELECT  DISTINCT  o_orderkey  AS  orderkey  
FROM  orders)  
EXCEPT  
(SELECT  DISTINCT  l_orderkey  AS  orderkey  
FROM  lineitem  
WHERE  DATEDIFF(day,  l_shipdate,  l_receiptdate)  >  14)  
)  

SELECT  COUNT(orderkey)  FROM  timelyorders;  

 
8-­‐ WITH  requiredcustomers  AS  (  
(SELECT  c_custkey  FROM  customer)  
EXCEPT  
(SELECT  DISTINCT  o_custkey  
FROM  lineitem,  orders,  supplier,  nation  
WHERE  l_orderkey  =  o_orderkey  AND  l_suppkey  =  s_suppkey  AND  s_nationkey  =  n_nationkey  
AND  n_name  =  'UNITED  STATES')  
)  
 
SELECT  COUNT(*)  FROM  requiredcustomers;  
 
9-­‐ SELECT  n_name  
FROM  customer,  nation  
WHERE  c_acctbal  =  (SELECT  MAX(c_acctbal)  FROM  customer)  AND  c_nationkey  =  n_nationkey;  
 
10-­‐ SELECT  DISTINCT  n_name  
FROM  lineitem,  orders,  customer,  supplier,  nation  
WHERE  l_orderkey  =  o_orderkey  AND  o_custkey  =  c_custkey  AND  c_nationkey  =  n_nationkey  
AND  l_suppkey  =  s_suppkey  
AND  s_nationkey  =  c_nationkey  
AND  DATEDIFF(day,  l_shipdate,  l_receiptdate)  >  29  
 
 

   
CHINOOK  

11-­‐ (SELECT  artistId,  name  FROM  artist)  

EXCEPT  
(SELECT  artist.artistId,  artist.name  FROM  artist,  album  WHERE  artist.artistId  =  album.artistId)  
 
12-­‐ (SELECT  artistId,  name  FROM  artist)  
EXCEPT  
(SELECT  artist.artistId,  artist.name  
FROM  artist,  album,  track,  genre  
WHERE  artist.artistId  =  album.artistId  AND  album.albumId  =  track.albumId  AND  track.genreId  =  
genre.genreId  AND  genre.name  =  'Latin')  
 
13-­‐ SELECT  trackId,  track.name  
FROM  track  ,  mediaType  
WHERE  milliseconds  =  
(SELECT  MAX(milliseconds)  FROM  track,  mediaTypeId  AND  track.mediaTypeId    =  
mediaType.mediaTypeId  AND  mediaType.name  LIKE  '%video%')  
AND  track.mediaTypeId    =  mediaType.mediaTypeId  AND  mediaType.name  LIKE  '%video%';  
 
14-­‐ SELECT  firstname,  lastname  
FROM  customer  
WHERE  city  =  (SELECT  city  FROM  employee  WHERE  reportsTo  IS  NULL);  
 
15-­‐ WITH  managersOfSuppBraz  AS  (  
SELECT  DISTINCT  reportsTo  AS  manId  
FROM  employee,  customer  
WHERE  employeeId  =  supportRepId  AND  customer.country  =  'Brazil')  
 
SELECT  employeeId,  firstname,  lastname  
FROM  employee  e,  managersOfSuppBraz  m  
WHERE  e.employeeId  =  m.manId  
 
16-­‐ SELECT  COUNT(t.trackId),  SUM(l.unitprice)  
FROM  invoiceLine  l,  invoice  i,  customer  c,  track  t,  mediaType  m  
WHERE  l.invoiceId  =  i.invoiceId  AND  i.customerId  =  c.customerId  AND  c.country  =  'Germany'  
AND  l.trackId  =  t.trackId  AND  t.mediaTypeId  =  m.mediaTypeId  AND  m.name    LIKE  '%audio%'  
 
17-­‐ (SELECT  playlistId,  name  FROM  playlist)  
EXCEPT  
(SELECT  p.playlistId,  p.name  
FROM  playlist  p,  playlistTrack  pt,  track  t,  genre  g  
 WHERE  p.playListId  =  pt.playListId  AND  pt.trackId  =  t.trackId  AND  t.genreId  =  g.genreId  AND  
g.name  =  'Latin')  
 
18-­‐ SELECT  SUM(bytes),  SUM(unitprice)  
FROM  playlist  p,  playlistTrack  pt,  track  t    
WHERE  p.name  =  'Grunge'  AND  p.playListId  =  pt.playListId  AND  pt.trackId  =  t.trackId  
 
19-­‐ (SELECT  playlistId,  name  FROM  playlist)  
EXCEPT  
(SELECT  p.playlistId,  p.name  
FROM  playlist  p,  playlistTrack  pt,  track  t,  album  b,  artist  a  
WHERE  p.playListId  =  pt.playListId  AND  pt.trackId  =  t.trackId  AND  t.albumId  =  b.albumId  AND  
b.artistId  =  a.artistId  AND  (a.name  =  'Black  Sabbath'    OR  a.name  =  'Chico  Buarque'))  
 
20-­‐ SELECT  c.name,  c.country  
FROM  customer  c,  employee  e  
WHERE  c.supportRepId  =  e.employeeId  AND  DATEDIFF(year,  birthdate,  hiredate)    <  35  

RELATIONAL  ALGEBRA  

 
  

Note:  Full  list  of  RA-­‐expressible  queries  

1,  2,  4,  5,  10  

11,  12,  14,  15,  17,  19,  20