7.24a

SELECT Order#, Ship_date
FROM SHIPMENT
WHERE Warehouse# = ‘W2’;

7.24b

SELECT Order#, Warehouse#
FROM ORDER o, CUSTOMER c, SHIPMENT s
WHERE o.Cust# = c.Cust# AND o.Order# = s.Order# AND Cname = ‘Jose Lopez’;

7.24c

SELECT Cname, COUNT(Order#), AVG(Ord_Amnt)
FROM ORDER o, CUSTOMER c
WHERE o.Cust# = c.Cust#
GROUP BY c.Cname;

7.24d

SELECT Order#, Warehouse#
FROM ORDER o, SHIPMENT s
WHERE o.Order# = s.Order# AND( Ship_date – Odate) > 30;

7.24e

SELECT o.Order#
FROM ORDER o
WHERE NOT EXISTS
(      (SELECT w.Warehouse#
         FROM WAREHOUSE w
         WHERE w.City = ‘New York’)
         EXCEPT
        (SELECT s.Warehouse#
          FROM SHIPMENT s
          WHERE s.Order# = o.Order#) );

7.25a

SELECT Trip_ID, SSN, From_City, To_City, Departure_Date, Return_Date
FROM TRIP t, EXPENSE e
WHERE t.Trip_ID = e.Trip_ID AND Amount > 2000;

7.25b

SELECT DISTINCT SSN
FROM TRIP
WHERE To_City = ‘Honolulu’;
 
 
 

7.25c

SELECT SUM(Amount)
FROM TRIP t, EXPENSE e
WHERE t.Trip_ID = e.Trip_ID AND SSN = ‘234-56-7890’;

7.26a

SELECT COUNT(Course#)
FROM STUDENT s, ENROLL e
WHERE s.SSN = e.SSN AND Name = ‘John Smith’ AND Quarter = ‘W99’;

7.26b

SELECT x.Course#, y.Book_ISBN, z.Book_Title
FROM COURSE x, BOOK_ADOPTION y, TEXT z
WHERE  y.Book_ISBN = z.Book_ISBN AND  x.Course# = y.Course# AND
               x.Course# IN
(SELECT c.Course#
FROM COURSE c, BOOK_ADOPTION b
WHERE c.Course# = b.Course# AND c.Dept = 'CS'
GROUP BY c.Course#
HAVING COUNT(*) >2);
 

7.26c

(SELECT DISTINCT Dept
FROM COURSE)
EXCEPT
(SELECT Dept
FROM Course c, Book_Adoption b, Text t
WHERE c.Course# = b.Course# AND b.Book_ISBN = t.Book_ISBN
AND t.publisher <> 'BC Publishing');

7.28a

SELECT x.Serial_No, x.Manufacturer, y.Sale_price
FROM CAR x, SALES y, SALESPERSON z
WHERE x.Serial_No = y.Serial_No AND y.Salesperson_id = z.Salesperson_id AND Name = 'Jane Doe';

7.28 b.

SELECT x.Serial_No, x.Model
FROM CAR x
WHERE x.Serial_No NOT IN
(SELECT o.Serial_No
 FROM OPTIONS o);