2004 SQL Test Answers
Please use the following tables from the ART database to answer the questions on this test:
Artist | Show
Artist_ID Name Genre1 Genre2 Region | Artist_FK Gallery_FK Date
--- ------- --- --- --- | --- --- ----------
0 Phoebe 0 3 NE | 0 0 06-04-2004
1 Jacques 4 3 SW | 0 2 06-04-2004
2 Lin 1 null NW | 1 1 06-05-2004
3 Spirit 2 null SE | 1 0 06-05-2004
4 Ted 0 1 NE | 3 3 06-05-2004
| 4 4 06-06-2004
| 0 1 06-07-2004
| 1 4 06-07-2004
Gallery | Genre
Gallery_ID Name Open Close Region Endowment | Genre_ID Genre
--- --------- -- -- -- ---- | --- -----------
0 Red Shack 14 22 SW 3.2 | 0 Painting
1 Clovers 8 19 NE 0.7 | 1 Sculpture
2 17th Ave 9 19 NW 9.6 | 2 Mixed Media
3 Indigo 10 20 SW 1.2 | 3 Digital Media
4 Fresh Look 10 21 NE 0.2 | 4 Photography
Note that the Open and Close times for the Gallery are in 0-23 format (0 is midnight, 12 is noon, 23 is 11pm)
and Endowment figures are in millions of dollars. All questions on the exam are cumulative. Please update the
tables above as you answer relevant questions.
1. Put the letter of the most typical person on the right in front of the task performed
on the left when done in a large organization (put one letter in front of each of the
six tasks):
_D_ Understands the use of logic in asking for information from the database tables
_B_ Asks questions about entities and relationships during a database development project
_A_ Answers those questions about entities and relationships A. Organizational Manager
_C_ Offers expertise about computer storage devices B. Data Modeler
_C_ Designs a database backup strategy C. Database Administrator
_A_ Reviews reports prepared specifically for high-level use D. Data Analyst
2. For the four tables in the ART database, identify whether each is an entity, relationship
or lookup (circle one word for each table):
Artist: Entity Relationship Lookup
Gallery: Entity Relationship Lookup
Genre: Entity Relationship Lookup
Show: Entity Relationship Lookup
3. In order to SELECT the total endowment for gallery regions with more than 5 million
dollars, you SUM(Endowment) to get totals and GROUP BY Region. What clause would
you add to just list those sums > 5.0 (circle the letter before the one best answer
and put a number in the blank after the question that follows):
a. WHERE SUM(Endowment) > 5.0
b. WHERE SUM(Region) > 5.0
c. HAVING SUM(Endowment) > 5.0
d. HAVING SUM(Region) > 5.0
e. WHERE SUM(Region) HAVING SUM(Endowment) > 5.0
How many rows would be returned in the result set (NOT including the header row)? _1_
4. You want to run a SELECT command that lists all artists by name. What clause would
you include in the query to do the alphabetizing (circle the letter before the best
answer):
a. ASC ORDER BY Artist.Name
b. LIST ASC BY Artist.Name
c. ORDER BY Artist.Name
d. SORT BY Artist.Name
e. WHERE Artist.Name ASC
Show the result sets for the commands in questions 5 through 10 (prepare the header row and the result detail row(s))
as an SQL processing engine would produce). Tell me why a query would return nothing if you think that is the case:
5. SELECT * FROM Artist WHERE Region='NE'
artist_id | name | genre1 | genre2 | region
-----------+--------+--------+--------+---------
0 | Phoebe | 0 | 3 | NE
4 | Ted | 0 | 1 | NE
(2 rows)
6. SELECT Artist.Name, Gallery.Name FROM Artist, Gallery, Show WHERE Gallery.Gallery_ID=Show.Gallery_FK
AND Artist.Artist_ID=Show.Artist_FK7
name | name
---------+------------
Phoebe | Red Shack
Phoebe | 17th Ave
Jacques | Red Shack
Jacques | Clovers
Spirit | Indigo
Ted | Fresh Look
(6 rows)
7. SELECT Artist.Region, COUNT(Artist.Region) FROM Artist, Genre WHERE Genre='Painting'
AND Artist.Genre1=Genre.Genre_ID GROUP BY Artist.Region
region | count
---------+-------
NE | 2
(1 row)
8. SELECT Gallery.Name, (Gallery.Close-Gallery.Open) AS OpenHours, Show.Date FROM Gallery, Show WHERE Gallery.Gallery_ID=Show.Gallery_FK AND (Gallery.Close-Gallery.Open) > 10
name | openhours | date
------------+-----------+------------
Clovers | 11 | 2004-06-05
Fresh Look | 11 | 2004-06-06
(2 rows)
9. SELECT DISTINCT G.Region FROM Gallery G, Show S WHERE G.Region > 'PP' AND G.Gallery_ID=S.Gallery_FK
AND S.Date='06-05-2004'
region
--------
SW
(1 row)
10. SELECT A.Name FROM Artist A WHERE NOT EXISTS
(SELECT * FROM Show S WHERE A.Artist_ID = S.Artist_FK)
name
--------
Lin
(1 row)
11. A brand new Gallery named Fireside will open tomorrow in the NE corner of the city.
Which SQL command will properly set a record up in the Gallery Table (circle the
letter before the one correct answer):
a. CREATE Gallery NEW VALUES (5, 'Fireside', 12, 18, 'NE', 1.2)
b. CREATE Gallery RECORD (5, 'Fireside', 12, 18, 'NE', 1.2)
c. INSERT INTO Gallery VALUES (5, 'Fireside', 12, 18, 'NE', 1.2)
d. INSERT (5, 'Fireside', 12, 18, 'NE', 1.2) INTO Gallery
e. UPDATE Gallery INSERT (5, 'Fireside', 12, 18, 'NE', 1.2)
12. You want to add two new shows to the Shows table to set up the show schedule for June 7, 2006.
Write two proper SQL statements to record the shows in the Show table if the shows are digital
media shows in the NE region of the city (write two proper SQL commands):
INSERT INTO Show VALUES (0, 1, '06-07-2004');
INSERT INTO Show VALUES (1, 4, '06-07-2004')
13. Match the SQL standard data type on the right to the attribute name on the left (put one letter
from the right in front of each attribute name):
_C_ Artist.Name a. integer
_C_ Artist.Region b. real
_B_ Gallery.Endowment c. text
_A_ Artist.Genre1 d. variant
_A_ Artist.Genre2 e. whole
14. a. You want to get a result set from an SQL query that shows you the names of
all galleries that are presenting artists who only work with one genre
(specialists). Fill in the blanks to generate the proper SQL command that will
generate that result set:
SELECT Gallery.Name FROM Gallery, Show, Artist WHERE Artist.Genre2 IS NULL
AND Gallery.Gallery_ID=Show.Gallery_FK AND Artist.Artist_ID=Show.Artist_FK;
b. What result set would you expect to see from running this query against the database?
name
--------
Indigo
(1 row)
15. a. You want to get a result set from an SQL query that shows you the average
opening time for shows in each region. The result set should be ordered by
latest average opening time to the earliest. Fill in the blanks to generate the
proper SQL command that will generate that result set:
SELECT G.Region, AVG(Open) FROM Gallery G, Show S WHERE G.Gallery_ID=S.Gallery_FK
GROUP BY G.Region ORDER BY AVG(Open) DESC
b. What result set would you expect to see from running this query against the database?
region | avg
--------+---------------
SW | 10.0000000000
NE | 9.0000000000
NW | 9.0000000000
(3 rows)
16. SQL contains a DELETE FROM command for deleting records from existing tables. The DELETE FROM
statement can be used in conjunction with a subquery to determine which records to delete (same
goes for archiving where deleted records are added to an archived table for safe storage).
The WHERE clause works identically for a DELETE FROM statement as it does for a SELECT statement.
If you wanted to delete all shows for the Red Shack gallery, what SQL command do you think you
could use to do so (complete the following SQL command and answer the follow-up question with
a number):
DELETE FROM Show WHERE Gallery_FK=0;
Note that deletion is all or nothing per existing record. How many records do you think your
command here would delete? _2_
Output when run against the database:
art=# DELETE FROM Show WHERE Gallery_FK=0;
DELETE 2
17. SQL contains a DROP TABLE command to delete a table from an SQL-compliant database. This command
is very dangerous as it deletes all the content in the table before deleting the table itself. I
often run the DROP TABLE command when I make mistakes during database creation. If I wanted to
change all the Genre Lookup assignments to be different than they currently are, do you think I
would use the DROP TABLE command (circle Yes or No and then give a one word answer to the follow-up
question)?
Yes No
If you answered Yes, what SQL command would I then use to reassign Genre types to
different numeric values? _INSERT INTO_
If you answered No, what SQL command might I want to use to reassign the Genre
types to different numeric values? _UPDATE_
For extra credit (make sure you have time to answer this), write a valid SQL query to generate the result:
name primary secondary date
---- ------- --------- ----------
Ted Painting Sculpture 06-06-2004
Lots of Valid Answers Here. Best to come after grading exams.