Exercises in SQL

1
Exercises in SQL

• Tang Nan
• 2006 03 06

2
SQL Quiz

• 1. What does SQL stand for?
• Strong Question Language
• Structured Question Language
• Structured Query Language

3
SQL Quiz

• 2. Which SQL statement is used to extract data
  from a database?
• SELECT
• GET
• OPEN
• EXTRACT

4
SQL Quiz

• 3. Which SQL statement is used to update data in
  a database?
• SAVE AS
• UPDATE
• SAVE
• MODIFY

5
SQL Quiz

• 4. Which SQL statement is used to delete data
  from a database?
• COLLAPSE
• REMOVE
• DELETE

6
SQL Quiz

• 5. Which SQL statement is used to insert new data
  in a database?
• ADD NEW
• INSERT NEW
• INSERT INTO
• ADD RECORD

7
SQL Quiz

• 6. With SQL, how do you select a column named
  "FirstName" from a table named "Persons"?
• EXTRACT FirstName FROM Persons
• SELECT Persons.FirstName
• SELECT FirstName FROM Persons

8
SQL Quiz

• 7. With SQL, how do you select all the columns
  from a table named "Persons"?
• SELECT Persons
• SELECT FROM Persons
• SELECT all FROM Persons
• SELECT .Persons

9
SQL Quiz

• 8. With SQL, how do you select all the records
  from a table named "Persons" where the value of
  the column "FirstName" is "Peter"?
• SELECT all FROM Persons WHERE FirstName'Peter
• SELECT all FROM Persons WHERE FirstName LIKE
  'Peter
• SELECT FROM Persons WHERE FirstName'Peter
• SELECT FROM Persons WHERE FirstName LIKE 'Peter'

10
SQL Quiz

• 9. With SQL, how do you select all the records
  from a table named "Persons" where the value of
  the column "FirstName" starts with an "a"?
• SELECT FROM Persons WHERE FirstName LIKE 'a
• SELECT FROM Persons WHERE FirstName'a
• SELECT FROM Persons WHERE FirstName LIKE 'a
• SELECT FROM Persons WHERE FirstName'a'

11
SQL Quiz

• 10. The OR operator displays a record if ANY
  conditions listed are true. The AND operator
  displays a record if ALL of the conditions listed
  are true
• False
• True    

12
SQL Quiz

• 11. With SQL, how do you select all the records
  from a table named "Persons" where the
  "FirstName" is "Peter" and the "LastName" is
  "Jackson"?
• SELECT FROM Persons WHERE FirstName'Peter' AND
  LastName'Jackson
• SELECT FROM Persons WHERE FirstName LIKE
  'Peter' AND LastName LIKE 'Jackson
• SELECT FirstName'Peter', LastName'Jackson' FROM
  Persons

13
SQL Quiz

• 12. With SQL, how do you select all the records
  from a table named "Persons" where the "LastName"
  is alphabetically between (and including)
  "Hansen" and "Pettersen"?
• SELECT LastNamegt'Hansen' AND LastNamelt'Pettersen'
  FROM Persons
• SELECT FROM Persons WHERE LastNamegt'Hansen' AND
  LastNamelt'Pettersen
• SELECT FROM Persons WHERE LastName BETWEEN
  'Hansen' AND 'Pettersen'

14
SQL Quiz

• 13. Which SQL statement is used to return only
  different values?
• SELECT DIFFERENT
• SELECT DISTINCT
• SELECT UNIQUE

15
SQL Quiz

• 14 Which SQL keyword is used to sort the
  result-set?
• ORDER BY
• ORDER
• SORT
• SORT BY

16
SQL Quiz

• 15. With SQL, how can you return all the records
  from a table named "Persons" sorted descending by
  "FirstName"?
• SELECT FROM Persons SORT 'FirstName' DESC
• SELECT FROM Persons SORT BY 'FirstName' DESC
• SELECT FROM Persons ORDER FirstName DESC
• SELECT FROM Persons ORDER BY FirstName DESC

17
SQL Quiz

• 16. With SQL, how can you insert a new record
  into the "Persons" table?
• INSERT VALUES ('Jimmy', 'Jackson') INTO Persons
• INSERT INTO Persons VALUES ('Jimmy', 'Jackson')
• INSERT ('Jimmy', 'Jackson') INTO Persons

18
SQL Quiz

• 17. With SQL, how can you insert "Olsen" as the
  "LastName" in the "Persons" table?
• INSERT ('Olsen') INTO Persons (LastName)
• INSERT INTO Persons ('Olsen') INTO LastName
• INSERT INTO Persons (LastName) VALUES ('Olsen')  

19
SQL Quiz

• 18. How can you change "Hansen" into "Nilsen" in
  the "LastName" column in the Persons table?
• UPDATE Persons SET LastName'Nilsen' WHERE
  LastName'Hansen
• MODIFY Persons SET LastName'Nilsen' WHERE
  LastName'Hansen
• UPDATE Persons SET LastName'Hansen' INTO
  LastName'Nilsen
• MODIFY Persons SET LastName'Hansen' INTO
  LastName'Nilsen

20
SQL Quiz

• 19. With SQL, how can you delete the records
  where the "FirstName" is "Peter" in the Persons
  Table?
• DELETE FirstName'Peter' FROM Persons
• DELETE FROM Persons WHERE FirstName 'Peter
• DELETE ROW FirstName'Peter' FROM Persons

21
SQL Quiz

• 20. With SQL, how can you return the number of
  records in the "Persons" table?
• SELECT COLUMNS() FROM Persons
• SELECT COLUMNS() FROM Persons
• SELECT COUNT() FROM Persons
• SELECT COUNT() FROM Persons

22
Question A
List the project names PROJ_NM for projects with
more than 5 people and using parts costing more
than 100 (not the total number of parts used!).
Do not show information for any of their
components.
Answer SELECT PART.PROJ_NM FROM PART,
PROJECT WHERE PART.PROJ_NM PROJECT.PROJ_NM AND
PROJECT.PEOPLE gt 5 AND PART.PRICE gt 100
23
Question B
List the part names and the project names for
parts made in LOCATION TJ and used in projects
with less than 30 people.
Answer SELECT PART_NM, P.PROJ_NM FROM PART P,
PROJECT R WHERE P.PROJ_NM R.PROJ_NM AND
P.LOCATION TJ AND R.PEOPLE lt 30
24
Question C
List the project names PROJ_NM that use parts
whose average cost AVG_COST is greater than the
average cost of all parts, printing out PROJ_NM
and AVG_COST.
Answer SELECT PROJ_NM, AVG(PRICE) AS
AVG_COST FROM PART GROUP BY PROJ_NM HAVING
AVG(PRICE) gt (SELECT AVG(PRICE) FROM PART)
25
Question D
What is the result of the following SQL commands
targeted at the Part and Project database bellow?
Is it legal SQL? If so, show the specific
output and indicate the query in plain
English. SELECT M.PART_NM, N.PART_NM FROM PART
AS M, PART AS N WHERE M.PRICE N.PRICE AND
M.PART_NM / N.PART_NM AND M.LOCATION /
SF AND N.LOCATION / SF
Answer List the names of pairs of parts that
have the same PRICE and are not made in SF.
26
Question E
List the names of pairs of parts that have the
same PRICE and are not made in SF.
Answer SELECT M.PART_NM, N.PART_NM FROM PART AS
M, PART AS N WHERE M.PRICE N.PRICE AND
M.PART_NM / N.PART_NM AND M.LOCATION /
SF AND N.LOCATION / SF
27
Join

• R S s c (R ? S)

• Join condition C is of the form
• ltcond_1gt AND ltcond_2gt AND AND ltcond_kgt
• Each cond_i is of the form A op B, where
• A is an attribute of R, B is an attribute of S
• op is a comparison operator , lt, gt, ?, ?, or ?.
• Different types
• Theta-join
• Equi-join
• Natural join

28
Theta-Join
R S
R.AgtS.C
S(C,D)
R(A,B)
R ? S

• Result

29
Natural-Join

• Relations R and S. Let L be the union of their
  attributes.
• Let A1,,Ak be their common attributes.

30
Natural-Join
Contact(name, addr)
Emp (name, dept)
Emp Contact all employee names, depts, and
addresses.
Emp ? Contact
Result
31
Outer Joins

• Motivation join can lose information
• E.g. natural join of R and S loses info about
  Tom
• and Mary, since they do not join with other
  tuples.
• Called dangling tuples.

S
R

• Outer join natural join, but use NULL values to
  fill in dangling tuples.
• Three types left, right, or full

32
Left Outer Join
R
S
R ? S
Left outer join R S
Pad null value for left dangling tuples.
33
Right Outer Join
R
S
R ? S
Right outer join R S
Pad null value for right dangling tuples.
34
The Evils of Redundancy

• Redundancy is at the root of several problems
  associated with relational schemas
• redundant storage, insert/delete/update anomalies
• Integrity constraints, in particular functional
  dependencies, can be used to identify schemas
  with such problems and to suggest refinements.
• Main refinement technique decomposition
  (replacing ABCD with, say, AB and BCD, or ACD and
  ABD).
• Decomposition should be used judiciously
• Is there reason to decompose a relation?
• What problems (if any) does the decomposition
  cause?