Due: see class web page

If you have not read this yet for SQL project 1, you should read it now:

• intro-sql.html: General intro to using SQL

You must read the information/description on the SPJ project database in the URL below to do the project.

• The SPJ database: click here

Specifically: pay attention to what the meaning of each relation is.

How to do SQL projects:

• You can do the project by executing your SQL queries using this webpage: click here to run your SQL queries using your browser

  Type the query into the Query box and click send to execute the query. Use the back button on your browser to return to the query web page.

  ---

• Or you can do the project using the cs377-sql GUI application (= the program I use in class) by running the following command on a CS lab machine:

  /home/cs377001/bin/cs377-sql

  To access the SPJ database, you must enter:

  spjDB

  in the database field and click the select button.

1. Assignment

Formulate the queries in Section 3 in the SQL language

Notes:

I provide the output that a correct query will print out. Note that incorrect queries can print out the same answer (so seeing the same output is no guarantee that your query is correct). I will test your queries with a different database content.

Note: You should be aware that sometimes, an incorrect query can produce the same outputs as the right query. Incorrect queries that happens to produce the same answers as the solution query will not receive full credits. In fact, we use a different database content to grade the SQL projects, so do NOT rely solely on visual output to check for correctness. You should understand SQL and the techniques discussed in class.

2. SQL Queries Files that you need to turn in

Extremely important:

You must turn in plain text files !!! Use the following editors to create your file: On Windows PC: notepad On Mac: sublime or use plain text mode with TextEdit ( How to Use Plain Text Mode in TextEdit) On Linux: gedit Do not use these editors: Microsoft Word Any editor that saves in ".rtf" (rich text format)

You should use one file to hold one SQL query.

Follow the following guidelines for each file:

If you have comments in your SQL query, you must use C style comments, i.e., put a comment between: /* This is a comment line... This line also... */ (// style comment will cause errors !!!) The honor code must be inside a comment Do NOT end your query with ";" Do not put any text in the your answer other than the SQL query without using the comment brackets /* ... */ : Text other than the SQL query (select command) in the answer must be enclosed in comment: /* .... */ (Points will be deducted for non-compliant;because it will not execute correctly) You can check if your submitted query will execute correctly by: Cut and paste the content of your file into the mySQL tool and execute the query

---

---

*** Checking for output of your queries ***

To help you verify whether your queries is wrong, I provided a program that you can run to obtain the output of the queries:

If your output is not the same, then your solution is (for sure) not correct. However: incorrect queries can produce the same output. I will be grading your queries using a different database; so even if your query prints the same output, it is not a guarantee that your query is correct.

To usage of the command is as follows (you need to provide 2 parameters):

/home/cs377001/bin/chsql SQL-proj# Question_Number

Use SQL-proj# = 2 in this assignment.

To check what SQL query for problem 1 would return, run:

   /home/cs377001/bin/chsql 2 1

To check what SQL query for problem 2 would return, run:

   /home/cs377001/bin/chsql 2 2

And so on.

3. SQL Queries

Save each query of this project in a separate file !!!           Because you will need to turn each file in separately See below for turn in instructions. Save your query files inside your cs377 project directory Because this cs377 project directory protected from access by other students

Express the following queries in SQL:

1. Get name of suppliers who have at least 3 shipments to the project 'j4' and the number of shipments the supplier is shipping to project 'j4'

   Sample output:

   +-------+-------------+ | sname | count(jnum) | +-------+-------------+ | Adams | 6 | | Klein | 4 | | Smith | 3 | +-------+-------------+

   Answer: click here

2. Find sname of suppliers who ships to every city where there is a project taking place.

   Suppose the cities where projects take place are: { London, Paris, Athens}, then the supplier must have a shipment to all these cities.

   Sample output:

   +-------+ | sname | +-------+ | Brown | +-------+

   Answer: click here

3. Get project number of projects that have shipments for all the parts with the highest weight

   Suppose the parts with the hightest weight are {p6, p8}, then the project must have shipments for both parts p6 and p8.

   Sample output:

   +------+ | jnum | +------+ | j2 | | j16 | +------+

   Answer: click here

4. Find number of parts that have the second highest weight of all parts

   Sample output:

   +------+ | pnum | +------+ | p2 | | p3 | +------+

   Answer: click here

5. For each supplier, show his supplier number and the project number of the projects that receive the highest number of shipments from this supplier and the number of shipments to these projects.

   Suppose s1 has 3 shipments to j1, 5 shipments to j2 and 5 shipments to j3, you must print s1, j2, 5 and s1, j3, 5. Do this for every supplier in the SPJ database.

   Sample output:

   +------+------+----------+ | snum | jnum | count(*) | +------+------+----------+ | s1 | j4 | 3 | | s10 | j11 | 1 | | s11 | j11 | 1 | | s11 | j12 | 1 | | s11 | j13 | 1 | | s11 | j14 | 1 | | s11 | j15 | 1 | | s11 | j5 | 1 | | s11 | j8 | 1 | | s11 | j9 | 1 | | s12 | j2 | 4 | | s13 | j15 | 3 | | s13 | j5 | 3 | | s13 | j7 | 3 | | s2 | j2 | 2 | | s3 | j1 | 1 | | s3 | j2 | 1 | | s4 | j2 | 2 | | s4 | j3 | 2 | | s5 | j4 | 6 | | s6 | j1 | 3 | | s6 | j3 | 3 | | s6 | j5 | 3 | | s6 | j8 | 3 | | s7 | j4 | 4 | | s8 | j1 | 4 | | s9 | j12 | 2 | | s9 | j8 | 2 | +------+------+----------+

   Answer: click here

6. Find sname of suppliers who have >= 2 shipments to all projects in the city that they live in.

   Suppose supplier s1 lives in Atlanta and projects j1 and j2 are in Atlanta. Supplier s1 is output by the query if and only if s1 has >= 2 shipments to project j1 and >= 2 shipments to project j2.

   Sample output:

   +--------+ | sname | +--------+ | Newton | | Lofton | +--------+

   Answer: click here

7. Find jnum of projects that have shipments with the highest total number of 'Screw' parts and show the number of 'Screw' parts being shipped to these projects.

   Suppose parts p1 and p2 are 'Screw' parts and suppose the spj relation contains the following shipments of 'Screw' parts:

   +------+------+------+------+ | jnum | pnum | snum | qty | +------+------+------+------+ | j1 | p1 | s1 | 200 | j1 has a total of 400 'Screw' parts | j1 | p1 | s2 | 100 | | j1 | p2 | s1 | 100 | | j2 | p1 | s1 | 100 | j2 has a total of 200 'Screw' parts | j2 | p2 | s2 | 100 | | j3 | p2 | s1 | 400 | j3 has a total of 400 'Screw' parts +------+------+------+------+

   Then the output of this query is (j1, 400) and (j3, 400)

   Sample output:

   +------+----------+ | jnum | sum(qty) | +------+----------+ | j2 | 1900 | | j4 | 1900 | +------+----------+

   Answer: click here

8. Find sname of suppliers who ship the part p3 to every projects in the city that the supplier lives.

   Suppose supplier s1 lives in Atlanta and projects j1 and j2 are in Atlanta. The s1 must have shipments of part p3 to j1 and j2.

   Sample output:

   +--------+ | sname | +--------+ | Newton | | Lofton | +--------+

   
   Answer: click here

9. Find the jnum of projects that have 5 or more shipments from suppliers that live in a different city than the project city (= "remote suppliers")

   Suppose project j1 is in Atlanta and has 7 shipments from suppliers who are not living in Atlanta, then j1 qualifies.

   Sample output:

   +------+ | jnum | +------+ | j1 | | j2 | | j4 | | j5 | | j8 | +------+

   
   Answer: click here

10. Get jnum for projects that has more shipments from "local suppliers" (= suppliers that live in the city where the project takes place) than "remote suppliers" (= non-local suppliers)

    Suppose project j1 takes places in Atlanta; j1 has 4 shipments from suppliers are in Atlanta and j1 has 3 shipments from suppliers that are not in Atlanta; then project j1 qualifies.

    Sample output:

    +------+ | jnum | +------+ | j2 | | j3 | | j5 | | j7 | | j15 | +------+

    
    Answer: click here

Turn in

Put each query in a separate file and turn each file separately in using the command:

/home/cs377001/turnin filename sql2-XX

Use for XX the query number in the handout. (XX ranges from 1, 2, 3, 4, 5, ...., 20).

For example, if your file ans5.sql contains the solution to query 5, use the command:

              /home/cs377001/turnin    ans5.sql    sql2-5

to turn in your solution. Do NOT use a leading 0 !

Each query must be stored as a separate file and turned in separately.

Also, make sure you work in your cs377 project directory to protect your files from illegal access.

Extension request

• To request an extension for the sql2 project, you must execute the following command before the deadline of the homework:

  /home/cs377001/req-ext sql2

  You request will be successful if you have not exceeded the maximum number of "free" (no-questions-asked) requests allowed

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.