Due: see class web page

Before you start on this project, you must read

• 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# = 1 in this assignment (use SQL-proj# = 2 in the next SQL assignment).

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

   /home/cs377001/bin/chsql 1 1

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

   /home/cs377001/bin/chsql 1 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 part numbers of all red parts that is made in London

   Sample output:

   +------+ | pnum | +------+ | p1 | | p4 | | p6 | +------+

   Answer: click here

2. Get project numbers of projects that uses a red part that is manufactured in the same city as the project

   Sample output:

   +------+ | jnum | +------+ | j1 | | j4 | | j17 | | j18 | | j19 | | j14 | +------+

   Answer: click here

3. Get supplier numbers of suppliers who do not supply to any project in city that the supplier is based.

   Sample output:

   +------+ | snum | +------+ | s7 | | s9 | | s11 | +------+

   Answer: click here

4. Find snum of suppliers who supply a part that is lighter than every part supplied by supplier Blake

   Sample output:

   +------+ | snum | +------+ | s1 | | s2 | | s5 | | s6 | | s7 | | s8 | | s9 | | s10 | | s11 | | s12 | | s13 | +------+

   Answer: click here

5. Find jnum of projects that do not use any `Nut's nor any 'Bolt's.

   Sample output:

   +------+ | jnum | +------+ | j6 | | j14 | +------+

   Answer: click here

6. For every supplier, list his supplier number snum and the part number pnum of the heaviest part that he ships

   Sample output:

   +------+------+ | snum | pnum | +------+------+ | s1 | p2 | | s2 | p3 | | s3 | p3 | | s4 | p6 | | s5 | p6 | | s6 | p6 | | s7 | p3 | | s8 | p2 | | s8 | p3 | | s9 | p2 | | s10 | p1 | | s11 | p8 | | s12 | p8 | | s13 | p6 | +------+------+

   Answer: click here

7. Find snum for suppliers that do not supply any part named 'Nut'

   Sample output:

   +------+ | snum | +------+ | s13 | | s3 | | s4 | | s9 | +------+

   Answer: click here

8. Find snum of supplier that only supplies/ships to projects in the city that the supplier lives

   Sample output:

   +------+ | snum | +------+ | s10 | | s12 | +------+

   
   Answer: click here

9. Get snum of suppliers that ship all (= every one) parts that are used in project "j6".

   Suppose project j6 uses parts p1, p2 and p3. Then find suppliers that have shipments for all these parts in the spj relation. The supplier does not need to ship these parts to project j6.

   Sample output:

   +------+ | snum | +------+ | s12 | | s13 | | s2 | | s3 | | s4 | | s5 | | s6 | | s7 | | s8 | +------+

   
   Answer: click here

10. Get jnum of projects that have shipments from all (= every one) suppliers that live in the city where the project is located.

    E.g.: if project X is in Atlanta and the suppliers S1 and S2 live in Atlanta, than project X must have shipments from supplier S1 and S2 in the spj relation

    Sample output:

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

    
    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 sql1-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    sql1-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 sql1 project, you must execute the following command before the deadline of the homework:

  /home/cs377001/req-ext sql1

  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.