Assignment 1 Sample Solution
CIS 451/551 Fall 2007


Against stores7 database

1.  List all customers who live on a "Street", as opposed to an Avenue, Court, Drive, etc. Thus, the address1 field should end with the substring "Street", or any reasonable abbreviation of that word.

SELECT fname, lname, address1
FROM customer
WHERE address1 LIKE '%Street' OR  address1 LIKE '%St.'
      OR address1 LIKE '%Str'OR address1 LIKE '%St'


2.  Give the names of the cities to which an order was sent in the second half (after the 15th) of the month of May 1998. Use the functions month(attribute), day(attribute), etc.

SELECT c.city, o.ship_date
FROM customer c, orders o
WHERE c.customer_num=o.customer_num AND
        month(ship_date)=5 AND year(ship_date)=1998
        AND day(ship_date)>=15

3.  Give the catalog description of everything ordered by "George Watson". (Note the two attribute foreign key.)

SELECT cat.cat_descr
FROM customer cus, orders o, items i, catalog cat
WHERE cus.customer_num=o.customer_num AND o.order_num=i.order_num
      AND i.stock_num=cat.stock_num AND i.manu_code=cat.manu_code
      AND cus.fname='George' AND cus.lname='Watson';


4.  For each state, give the state name and the name (first and last) of each customer living in that state. If no one lives in that state, it should still be listed but with a blank (null) next to the state name. (Hint: outer join.)

SELECT sname, fname, lname
FROM state LEFT JOIN customer ON code=state
ORDER BY 1, 3, 2


Against company database

5.   Retrieve the names of all employees in department 5 who work at least 10 hours per week on the 'ProductX' project.

SELECT fname, lname
FROM employee, works_on, project
WHERE ssn=essn AND pno=pnumber AND hours>=10 
      AND pname='ProductX' AND dno=5


6.  Show all pairs of siblings (with the older one listed first).

SELECT d1.dependent_name, d2.dependent_name
FROM dependent d1, dependent d2
WHERE d1.essn=d2.essn AND d1.bdate<d2.bdate
      AND d1.relationship IN ('SON', 'DAUGHTER') 
      AND d2.relationship IN ('SON', 'DAUGHTER') 


7.  How many projects are run by the Administration department? 

SELECT count(*) numProjects
FROM department INNER JOIN project ON dnumber=dnum
WHERE dname='Administration'