CS 348 - Homework 3 - Due Thursday Sep 25

Answer the questions listed below based on our parts and supplier database. These are SQL problems. Queries should execute properly on the server. Save a file named hw3.sql to your T: drive folder. Each query should be commented with the problem number and with the problem statement below repeated. Single line comments in an SQL file start with a # character. Also, make sure you turn in a printout of the file.

1. List all of the supplier numbers and the part numbers supplied by the supplier.

   select sno,pno from sp order by sno;
   # The "order by" clause is not strictly necessary.
   

2. Get supplier numbers for suppliers who supply part 1. Answer should have one attribute, (sno).

   select sno from sp where pno = 1;
   

3. Get supplier names and status for all suppliers who have a status between 15 and 25 inclusive. Answer should have two attributes (sname,status)

    select sname,status from supplier where
      supplier.status >= 15 and
      supplier.status <= 20;
   

4. Get all part numbers and the supplier names for parts supplied by a supplier in London. Answer should have two attributes (pno,sname).

      select distinct sp.pno,sname from
        supplier inner join sp using (sno)
        where supplier.city = 'London';
    

5. Find the average status for all the suppliers. Answer is scalar (a table with one row and one column).

    select avg(status) from supplier;
    

6. Get part numbers for parts not supplied by any supplier in London. Answer should have one attribute (pno).

      select distinct parts.pno from parts
        where parts.pno not in
        (
          select distinct parts.pno from
            supplier inner join sp using (sno)
            inner join parts using(pno)
            where supplier.city='London'
        );
    

7. Get city names for cities in which at least two suppliers are located. ANswer has one attribute (city).

      select city from supplier
        group by city
        having count(city) >= 2;
    

8. Get all pairs of part numbers and a supplier name such that the supplier supplies both of the parts. Don't include trivial pairs of part numbers. For example don't list the tuple Smith 1 1. That's obvious. For ease, you can list symmetric tuples, for example, Smith 1 2 and Smith 2 1. Your answer schema should have three attributes (sname,pno,pno).

    select distinct sname,pno1,pno2 from 
       supplier,  
       (
         select sp1.sno, sp1.pno as pno1, sp2.pno as pno2
           from sp as sp1,
              sp as sp2
           where sp1.sno=sp2.sno and
                 sp1.pno != sp2.pno
       ) as temp
     where supplier.sno=temp.sno
     order by sname,pno1;
    

9. Get the total number of different parts supplied by supplier one. Answer is scalar.

      select count(pno) from sp where sp.sno=1;
    

10. Get supplier numbers for suppliers with a status lower than that of supplier 1. Answer has one attribute (sno).

      select sno from supplier 
        where status < (select status from supplier
    	                where sno=1)
     

11. Get supplier numbers for suppliers whose city is first in the lexicographic order of cities. Answer has one attribute (sno).

       select sno from supplier 
       where
          city = (select min(city) from supplier);
     

12. Get part numbers for parts supplied by all suppliers in London. Answer has one attribute (pno).

       select distinct pno from 
         supplier inner join sp using (sno)
         where city = 'London';
     

13. Get supplier number, supplier name, and part number such that the supplier does not supply the part. Answer has three attributes (sno,sname,pno).

     select supplier.sname, supplier.sno, parts.pno
       from supplier,parts
       where not exists
         (select * from sp
            where sp.pno = parts.pno and
                  sp.sno = supplier.sno)
       order by sname,pno;