Exercises: using SPJ database.

 Simple Queries  

  1. Get all the data of all jobs (J table).
  2. Get all the data on all jobs in London.
  3. Get supplier numbers for suppliers (S table) who supply (SPJ table) job J1, in supplier number order.
  4. Get all shipments (SPJ table) where the quantity is in the range 300 to 750 inclusive.
  5. Get a list of all part-color, part-city combinations, with duplicate (color, city) eliminated.

Aggregate Functions

  1. Get the total number of jobs supplied by supplier S1.
  2. Get the total quantity of part P1 supplied by supplier S1.
  3. For each part being supplied to a job, get the part number, the job number and the corresponding total quantity.
  4. Get part numbers for parts supplied to some job in the average quantity of more than 320.

Special Data

  1. Get all shipments where the quantity is non-null.
  2. Get project numbers and cities where the city has an "o" as the second letter of its name.

12.  Get suppliers names which start the letters 'Cl'.

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

14.  Get all the shipments made in 1999.

15.  Get the last five shipments.

16.  Get the first five shipments.

17.  Get the sum of all the shipments in 1999.

     Upgrade Operations

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

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

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

Part II

Joins

  1. Get all supplier-number, part-number, job-number triples such that the indicated supplier, part, job are in the same city.
  2. Get all supplier-number, part-number, job-number triples such that the indicated supplier, part, job are not in the same 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.
  4. Get part number for parts supplied (SPJ table) by a supplier in London.
  5. Get part numbers for parts supplied by a supplier in London to a job in Paris.
  6. Get all pairs of city names such that a supplier in the first city supplies a job in the second city.
  7. Get part numbers for parts supplied to any job by a supplier in the same city as the job.
  8. Get job numbers for jobs supplied by at least one supplier not in the same city.
  9. Get all pairs of part numbers such that the same supplier supplies both the indicated parts

Subqueries

  1.  Get job names for jobs supplied by supplier S1.
  2.  Get colors for parts supplied by supplier S1.
  3.  Get part numbers for parts supplied to any job in London.
  4.  Get job numbers for jobs using at least one part available from supplier S1.
  5.  Get supplier numbers for suppliers supplying at least one part supplied by at least one supplier who supplies at least one red part.
  6.  Get supplier numbers for suppliers with status lower than that of supplier S1.
  7.  Get job numbers for jobs whose city is first in the alphabetic list of such cities.
  8.  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.
  9.  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.

EXISTS

  1. Get part numbers for parts supplied to any job in London.
  2.  Get job numbers for jobs using at least one part available from supplier S1.

UNION

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

      22. Show the results of the following SELECT:

SELECT P.CPLOR

FROM P

UNION

SELECT P.COLOR

Miscellaneous

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

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