Assignment 1 Solutions

Back to Assignments

You may want to cut and paste each solution and try to run it at the above URL.

Assignment locations & due dates: aggregate,Special data, joins, subqueries, exists, union ,miscellaneous

Simple Queries (Due 1^st week )

1. Get all the data of all jobs (J table).

select *

from j ;

2. Get all the data on all jobs in London.

select *

from j ;

where city='London' ;

3. Get supplier numbers for suppliers (S table) who supply (SPJ table) job J1, in supplier number order.

select distinct sno

from spj

where jno='J1' order by sno ;

4. Get all shipments (SPJ table) where the quantity is in the range 300 to 750 inclusive.

select *

from spj

where qty>=300

and qty<=750 ;

5. Get a list of all part-color, part-city combinations, with duplicate (color, city) eliminated.

select distinct color, city

from p ;

top Aggregate Functions (Due 3^rd week)

6. Get the total number of jobs supplied by supplier S1.

select count (distinct jno)

from spj

where sno='S1' --- did not work in SQLLearn

7. Get the total quantity of part P1 supplied by supplier S1.

select sum (qty)

from spj

where sno='S1' and pno='P1' ;

8. For each part being supplied to a job, get the part number, the job number and the corresponding total quantity.

select jno,pno, sum (qty)

from spj

group by jno, pno

9. Get part numbers for parts supplied to some job in the average quantity of more than 320.

select distinct pno

from spj

group by jno, pno

having avg(qty)>320 ;

top Special Data (Due 3^rd week)

10. Get all shipments where the quantity is non-null.

select *

from spj

where qty is not null ;

--This will also work, but only in MS Access and SQLLearn:

select *

from spj

where qty = null;

11. Get project numbers and cities where the city has an "o" as the second letter of its name.

select jno, city

from j

where city like '_o%' --Does not work in SqlLearn

12. Get supplier names which start with the letters ‘Sm’.

Select sname

From S

Where sname like ‘Sm%”

13. Get supplier names that have a letter ‘e’ somewhere in their name.

Select sname

From S

Where sname like ‘%e%”

14. Get all shipments made in 1999.

Select *

From spj

Where shipDate >= ‘01/10/99’

And shipDate <=’12/31/99’;

OR

Select *

From spj

Where shipDate between ‘01/10/99’ and ’12/31/99’;

15. Get the last five shipments.

You can use the following solution in MS Access and SQLLearn but not in Oracle. In Oracle and other vendors you need to use a hybrid of SQL and a programming language to get the answer.

Select top 5 *

From SPJ

Order by shipDate desc;

16. Get the first five shipments.

You can use the following solution in MS Access and SQLLearn but not in Oracle

Select top 5 *

From SPJ

Order by shipDate asc;

17. Get the sum of all shipments in 1999.

Select SUM(qty)

From SPJ

Where shipDate between ‘01/10/99’ and ’12/31/99’;

Upgrade Operations (Due 5^th week)

18. Change the color of all red parts to orange.

Note this will not work if color column data type is set to less than 6 characters (letters).

update p

set color = 'Orange'

where color = 'Red' ;

19. Insert a new supplier (S10) into table S. The name and city are Smith and New York, respectively; he status is yet known.

insert

into s

values( 'S10', 'Smith', null, 'New York' ) ;

20. Get all the red parts rows in P and insert the result into a table named RedParts. Assume the table exists.

Insert into RedParts

Select *

From P

Where color =’Red’;

top Joins (Due 2^nd week)

1. Get all supplier-number, part-number, job-number triples such that the indicated supplier, part, job are in the same city.

select sno, pno, jno

from s, p, j

where s.city = p.city

and p.city = j.city ;

2. Get all supplier-number, part-number, job-number triples such that the indicated supplier, part, job are not in the same city.

select sno, pno, jno

from s, p, j

where not (s.city = p.city and p.city = j.city) ;

3. Get all supplier-number, part-number, job-number triples such that no two of the indicated supplier, part, job are in the same city.

select sno, pno, jno

from s, p, j

where (s.city<>p.city and s.city<>j.city and p.city<>j.city) ;

4. Get part number for parts supplied (SPJ table) by a supplier in London.

select distinct pno

from spj

where sno in (select sno from S where city='London') ;

Or by using join:

Select distinct pno

From S,SPJ

Where S.sno = SPJ.sno

And S.city = ‘London’

5. Get part numbers for parts supplied by a supplier in London to a job in Paris.

select distinct spj.pno

from spj , s, j

where spj.sno = s.sno

and spj.jno = j.jno

and s.city = 'London'

and j.city = 'Paris';

6. Get all pairs of city names such that a supplier in the first city supplies a job in the second city.

select distinct s.city, j.city

from s, spj, j

where s.sno = spj.sno

and j.jno = spj.jno ;

7. Get part numbers for parts supplied to any job by a supplier in the same city as the job.

select spj.pno

from spj, s, j

where s.sno=spj.sno

and j.jno=spj.jno

and s.city=j.city ;

8. Get job numbers for jobs supplied by at least one supplier not in the same city.

select distinct j.jno

from spj, s, j

where s.city<>j.city

and s.sno=spj.sno

and j.jno= spj.jno ;

9. Get all pairs of part numbers such that the same supplier supplies both the indicated parts.

select distinct x.pno, y.pno

from spj x, spj y

where x.pno<y.pno and x.sno=y.sno ;

top Subqueries (Due 3^rd week)

10. Get job names for jobs supplied by supplier S1.

select jname

from j

where jno in

(select jno from spj where sno='S1') ;

11. Get colors for parts supplied by supplier S1.

select distinct color

from p

where pno in

(select pno from spj where sno='S1') ;

12. Get part numbers for parts supplied to any job in London.

select distinct pno

from spj

where jno in

(select jno from j where city='London' ) ;

13. Get job numbers for jobs using at least one part available from supplier S1.

select distinct jno

from spj

where pno in

(select pno from spj where sno='S1') ;

14. Get supplier numbers for suppliers supplying at least one part supplied by at least one supplier who supplies at least one red part.

select distinct sno

from spj

where pno in

(select distinct pno

from spj

where sno in

(select distinct spj.sno from spj,p where p.color='Red' and spj.pno=p.pno))

15. Get supplier numbers for suppliers with status lower than that of supplier S1.

select sno

from s

where status<(select status from s where sno='S1') ;

16. Get job numbers for jobs whose city is first in the alphabetic list of such cities.

select jno

from j

where city=(select min(city) from j) ;

17. Get job numbers for jobs supplied with part P1 in an average quantity greater than the greatest quantity in which any part is supplied to project J1.

select jno

from spj

where pno='P1'

group by jno

having avg(qty)>(select max(qty) from spj where jno='J1') ;

18. Get supplier numbers for suppliers supplying some job with part P1 in a quantity greater than the average shipment quantity of part P1 for that project.

select sno

from spj X

where X.pno='P1'

and qty>(select avg(qty) from spj Y where Y.pno='P1' and X.jno=Y.jno ) ;

top EXISTS (Due 4^th week)

19. Get part numbers fro parts supplied to any job in London.

select distinct pno

from spj

where exists (select * from j where j.jno=spj.jno and j.city='London') ;

20.Get job numbers for jobs using at least one part available from suppliers S1 i.e, we know that S1 shipped that part.

select distinct x.jno

from spj x

where exists

(select *

from spj y

where y.pno=x.pno and y.sno='S1') ;

Get job numbers for jobs not supplied with any red part by any London supplier.

select jno

from j

where not exists

(select *

from spj

where spj.jno=j.jno

and spj.pno in

(select pno

from p

where p.color='Red')

and spj.sno in

(select sno from s

where s.city='London')) ;

Two more examples of NOT EXISTS not in the assighnment

a. Get job numbers for jobs supplied entirely by supplier S1.

select distinct jno

from spj x

where not exists

(select * from spj y where y.jno=x.jno and y.sno<>'S1') ;

b. Get part numbers for parts supplied to all jobs in London.

select distinct pno

from spj x

where not exists

(select *

from j

where j.city='London'

and not exists

(select * from spj y

where x.pno=y.pno

and y.jno=j.jno) ) ;

top UNION (Due 4^th week)

21. Construct a list of all cities in which at least one supplier, part, or job is located.

select city from s

UNION

select city from

UNION

select city from j ;

22. Show the results of the following SELECT:

SELECT P.COLOR

FROM P

UNION

SELECT P.COLOR

FROM P;

top Miscellaneous (Due 5^th week)

23. How many suppliers in Athens that supply red parts?

select count(spj.sno)

from s, p, spj

where s.city = 'Athens'

and p.color = 'Red'

and s.sno = spj.sno

and p.pno = spl.pno ;

24. Delete all suppliers that never supplied a red part.

delete

from s

where sno not in

(select spj.sno

from s, spj

where s.color = 'Red'

and s.sno = spj.sno) ;

top Back to Assignments