CS 377/554 - Database Systems
SQL Project 2 (Serie A)

Due: See class webpage

1. Assignment


The SQL Queries Files to store your answers



2. SQL Queries

Express the following queries in SQL:
  1. Get snum and sname for suppliers that supply to > 2 distinct projects in "Rome" and to < 4 distinct projects in "Paris".
    NOTE: you can count the number of unique occurences of an attribute value by using: count(distinct attrname).

    Answer: click here


  2. Get snum and sname of suppliers who supply at least 3 parts to project 'j4'.
    Answer: click here


  3. Find snum and sname of suppliers who supply the same project with > 3 different parts.

    Hint: find the correct set of grouping attributes (plural) !
    Answer: click here


  4. Find snum and sname of suppliers who supply to at least one project in every city other than "Athens" and "Atlanta" and do not supply to any project in "Athens" and "Atlanta".
    Note: the supplier selected cannot supply to projects in Athens and Atlanta.
    Answer: click here


  5. Find snum and sname of suppliers who have 3 or more shipments to all projects in the city they live in.

    NOTE: a shipment is a tuple in spj. You must count each tuple as a separate shipment, e.g.:

      (s1, p1, j1, 10)
      (s1, p1, j1, 20)
    
    are 2 separate shipments

    NOTE: the supplier must have >= 3 shipment to each project. I.e.: if there are 2 projects (j1, j2) in his city, he must have 3 shipments to j1 and 3 shipments to j2.

    Note: you must also make sure that there is a project in the city of the supplier.

    Answer: click here


  6. Find snum and sname of suppliers who supply to at least one project in every city other than "Athens" and "Atlanta".
    Note: the supplier selected may supply to projects in Athens and Atlanta.
    Answer: click here


  7. For each supplier, find snum, sname, and the pnum and pname of parts that the supplier supplies, but only for parts that the supplier supplies to > 3 projects.

    Answer: click here


  8. Find snum and sname of suppliers who supplies to project "j4" and to a project in "Paris"
    Answer: click here


  9. For each project, list the jnum, jname and the total weight of parts from "Smith" being shipped to the project.

    The weight of a shipment is equal to qty × weight of the part.

    Answer: click here


  10. Find jnum and jname of projects that use any one of the heaviest part(s).
    Answer: click here


  11. Find jnum and jname of projects that use any one of the second heaviest part(s).

    Hints:

    Answer: click here


  12. For each supplier, find snum, sname, and the pnum and pname of the heaviest part(s) that the supplier supplies to projects in Paris.

    NOTE: He must supplies that heaviest part TO a project in Paris

    Answer: click here


  13. Find snum and sname of suppliers for which the color of one of the lightest (i.e., minimum weight) part they supply is "Blue".

    Answer: click here


  14. Find snum and sname of suppliers who supply the same part to every projects in the city they live. (For example, if supplier S lives in London, and projects X and Y are in London, then S must supply the same part P to X an Y. Further clarification: if the supplier s1 lives in Paris and j1 and j2 are all the projects in Paris, then s1 is qualified if there is a part p1 that s1 supplies to both j1 and j2. Only 1 part supplied to all projects is necessary to qualify. A supplier does not qualify however if he supplies p1 to j1 but a different p2 to j2.)

    Answer: click here


  15. Find snum and sname of suppliers who have at least 3 shipments going to projects in every city.

    Answer: click here


  16. Find jnum and jname of project(s) that uses the most number of "Blue" parts.
    Note: use the qty (quantity) attribute in spj to find number of parts used in project.

    Answer: click here


  17. Find snum and snames of suppliers that can supply every part (pnum) that are used by projects in "Atlanta".

    Note: use the spj relation to find out the parts that a supplier can supply.

    Answer: click here


  18. Find jnum and jname of projects that uses all but one parts that are available from supplier "s1".
    For example: if "s1" supplies parts "p1", "p2" and "p3", then find projects that uses {"p1", "p2"} or {"p1", "p3"} or {"p2", "p3"}. You must exclude projects that uses all parts available from "s1".

    Answer: click here


  19. Find jnum and jnames of all projects that uses all parts that are heavier than 15.

    Answer: click here


  20. Get snum and sname of suppliers who supply to project `j4' with at least three parts that are also available from supplier 's6' (s6 can supply the parts to any project, not necessarily to j4).

    Answer: click here





Turn in





Extension request




Statement of Policy on Computer Assignments

Students will be graded partially on the basis of their programming assignments. These programming assignments are to be treated as examinations, and are expected to be your individual work. While discussions with other students in the course may be permitted or encouraged by your instructor, you should write your program yourself. The mathlab representatives are available to explain error messages, discuss briefly technical details with which you may not be familiar, and give short suggestions as to how you might detect logic errors. The reps should not, however be asked to write part or all of your program. Your instructor (and any teaching assistants assigned to the course) will be glad to help you to the extent that he or she feels reasonable.

Submissions based on other students solutions in prior offerings of the course specifically violate these guidelines, as do submissions prepared with the help of an outside "tutor".

You should take precautions to protect the confidentiality of your work: preserve the secrecy of your password, do not make files or directories sharable, pick up your printouts promptly and dispose of printouts where they will not tempt other students. All work should be done either in the class directory of your ITD account (preferred) or in your "priv" directory (only if you do not have a class directory).

All submissions should include a comment statement near the top of the program of the form:

THIS CODE IS MY OWN WORK, IT WAS WRITTEN WITHOUT CONSULTING A TUTOR OR CODE WRITTEN BY OTHER STUDENTS - your name

Cases of apparent plagiarism or collusion will be referred to the Honor Council.