The Databases Course

Exercise No. 5

More SQL - Practical

Due on December 23th by midnight

 

1. Express the following queries in SQL:

1. For which parts is the number of different suppliers that supply the part larger than the number of different projects which use that part?
2. For each supplier from London and each part, what is the total weight of that part supplied by that supplier, in the case that the total weight is more than twice the average weight?
3. What is the average number of parts supplied to projects? Notice that the average calculated here is different from that in exercise 3 question 8, because here if a part is supplied to two projects, or twice to a project, we want to count it twice for the average.
4. What are the numbers of suppliers who supply p1, p2, p3 and p5 to all projects? Notice that this demonstrates how division works in SQL. Hint: Remember how we defined division using the other algebra operators - the 'minus' keyword is probably is quickest solution here as well.

If you succeed to express either a,b or c (not d) without using 'group by', then you'll get two bonus points (that's 10% of the maximum possible grade!). If you can prove that it is impossible to express either of these queries without 'group by' you'll also get two bonus points. However, no more than two points will be given (as for two different queries or proofs).

 

2. Write an SQL file (list of queries) that does the following, in the requested order:

• Create a new table named 'friends' with attributes for first name, last name, birthday, height and current occupation. Decide on your own about reasonable column names and data types.
• Insert ten records containing information about people you know (well, we won't check id's). At least one person should have a current occupation of 'Student'.
• Display the table on the standard output, ordered by height in descending order (highest people first).
• Update the height of all the people whose height is above the average height to be exactly two meters.
• Delete all records of students (people whose occupation is 'Student').
• Display the table again, ordered by last name (ascending) and then by first name (also ascending).
• Destroy the table.

3. Assume S is a table with attributes A1, ..., An, T is a table with attributes B1, ..., Bm, and c and k are constants. Consider the SQL query:

select distinct a1, ..., an from S where a2 = k
and a1 in (select b1 from T where b2 = c and b3 = S.a3);

1. Rewrite the above query using 'exists' instead of 'in'.

2. Rewrite the above query without using subqueries.

3. Assume that the original query above did not contain the 'distinct' keyword. Are your answer from parts 3a and 3b still correct? If your answers used the keyword 'distinct', will they be correct if this keyword is removed? In all of the above cases, if the answer is yes, explain why, and if the answer is no, give example databases where the queries compared give different answers.

4. Consider the above query where the keyword 'in' is replaced by 'not in':

select distinct a1, ..., an from S where a2 = k
and a1 not in (select b1 from T where b2 = c and b3 = S.a3);

Rewrite the above query using 'exists' / 'not exists' instead of 'not in'.

5. Is it possible to rewrite the above query (from question 3d) without using a subquery? If yes, give the query. If no, prove that it is not possible. To prove that it is not possible, it is acceptable to show that there is no equivalent query of the form:

select [distinct] column-name {, column-name, ...}
from table-name [alias] {, table-name [alias], ...}
where predicate

Here predicate is one of:

• a comparison (using =, >, <, >=, <=) between two column names, or between a column name and a constant.
• if p1 and p2 are predicates, then (p1 and p2) , (p1 or p2) , (not p1) are also predicates.

 

You should submit:

• The answer to question 3, on paper.
• Printouts of all the SQL queries and outputs from questions 1 and 2.
• Submit electronically (run ~db/bin/submit to submit ex2.tar) the following files:
  README - containing only your login on the first line, and your full name and relevant exercise documentation afterwards.
  queries1.sql, queries2.sql - containing the queries of questions 1 and 2 accordingly.
  outputs1.txt, outputs2.txt - containing the outputs of questions 1 and 2 accordingly.