HW2 Solutions
-------------
6.34
(a) List the names of all employees in department 5 who work more
    than 10 hours per week on the ProductX project.

project[fname,lname](
  select[dno=5](employee)
  join
  project[pnumber](select[pname='ProductX'](projects))
  join
  rename[ssn,pnumber,hours](select[hours>10](works_on))
);

(b) List the names of employees who have a dependent with the same
    first name as themselves.

project[fname,lname](
  employee
  join
  rename[ssn,fname](project[essn,dependent_name](dependent))
);

(c) List the names of employees who are directly supervised by Franklin
    Wong.

project[fname,lname](
  employee
  join
  rename[superssn](project[ssn](
    select[fname='Franklin' and lname='Wong'](employee)))
);

(d) List the names of employees who work on every project.

project[fname,lname](
  employee
  join
  (project[ssn](employee)
   minus
   project[ssn](
     (project[ssn](employee) times project[pnumber](projects))
     minus
     rename[ssn,pnumber](project[essn,pno](works_on))
   )
  )
);

(e) List the names of employees who do not work on any project.

project[fname,lname](
  employee
  join
  (project[ssn](employee)
   minus
   rename[ssn](project[essn](works_on))
  )
);

(f) List the names and addresses of employees who work on at least
    one project located in Houston but whose department has no location
    in Houston.

project[fname,lname,address](
  employee
  join
  (project[ssn](
     employee
     join
     rename[dno](
       project[dnumber](department)
       minus
       project[dnumber](select[dlocation='Houston'](dept_locations))
     )
   )
   intersect
   project[ssn](
     select[plocation='Houston'](projects)
     join
     rename[ssn,pnumber,hours](works_on)
   )
  )
);

(g) List the names of department managers who have no dependents.

project[fname,lname](
  employee
  join
  (rename[ssn](project[mgrssn](department))
   minus
   rename[ssn](project[essn](dependent))
  )
);

6.35
(a) Retrieve the names of parts that cost less than $20.00

project[pname](select[price<20](parts));

(b) Retrieve the names and cities of employees who have taken orders
    for parts costing more than $20.00.

project[ename,city](
  select[price>20](parts)
  join
  odetails
  join
  orders
  join
  employees
  join
  zipcodes
);

(c) Retrieve the pairs of customer number values of customers who live
    in the same Zip Code.

project[cno1,cno2](
  select[cno1<cno2](
    rename[cno1,cname1,street1,zip,phone1](customers)
    join
    rename[cno2,cname2,street2,zip,phone2](customers)
  )
);

(d) Retrieve the names of customers who have ordered parts from employees
    living in Wichita.

project[cname](
  customers
  join
  project[cno](select[city='Wichita'](zipcodes) join employees join orders)
);

(e) Retrieve the names of customers who have ordered ALL parts costing less
    than $20.00. (Note ALL in this query - different from problem in book).

project[cname](
  customers
  join
  (project[cno](customers)
   minus
   project[cno](
     (project[cno](customers)
      times
      project[pno](select[price<20](parts))
     )
     minus
     project[cno,pno](orders join odetails)
   )
  )
);

(f) Retrieve the names of customers who have not placed an order.

project[cname](
  customers
  join
  (project[cno](customers) minus project[cno](orders))
);

(g) Retrieve the names of customers who have placed exactly two orders.

project[cname](
  customers
  join
  (
    project[cno](
      select[ono1<>ono2](
        rename[ono1,cno](project[ono,cno](orders))
        join
        rename[ono2,cno](project[ono,cno](orders))
      )
    )
    minus
    project[cno](
      select[ono1<>ono2 and ono2<>ono3 and ono1<>ono3](
        rename[ono1,cno](project[ono,cno](orders))
        join
        rename[ono2,cno](project[ono,cno](orders))
        join
        rename[ono3,cno](project[ono,cno](orders))
      )
    )
  )
);