SQL Test - INDE 498A NAME: ___Solution_Set_____
Spring 2002
(All questions worth 8 points except for question 6 which is worth 12 points)
My best answers are in blue.
Please use the following tables from a THEME_PARK database to answer the questions on this test:
Please Note that the database is available for SELECT commands at
http://sambucus.cev.washington.edu/498A/themepark.html.
Ride | Ride_Schedule
Ride_ID Name Age Height| Ride_FK Operator_FK Shift Date
0 Ferris_Wheel 8 1.0 | 0 0 1 06-05-2002
1 Roller_Coaster 10 1.1 | 0 2 2 06-05-2002
2 Scrambler 9 1.1 | 1 1 1 06-05-2002
3 Superman 12 1.2 | 1 0 2 06-05-2002
4 Tea_Cups 4 0.6 | 3 3 1 06-05-2002
5 Water_Slide 8 0.8 | 3 1 2 06-05-2002
| 4 2 1 06-05-2002
| 4 4 2 06-05-2002
Operator | Manager
Operator_ID Name Yr_Exp | Mngr_ID Name Yr_Exp
0 Jim 2 | 0 Chachi 12
1 Bill 2 | 1 Lili 8
2 Sue 6 |
3 Jane 1 |
4 Pat 4 |
1. Put the letter of the task (work) on the right in front of the person on the left who usually performs it in a very large organization (put one letter in front of each of the six tasks):
_A_ Asks questions about entities and relationships A. Database Modeler
during a database development project
_D_ Answers those questions about entities and relationships B. Database Administrator
_B_ Offers expertise about computer storage devices
_B_ Designs a database backup strategy C. Data Analyst
_C_ Understands the use of logic in asking for information
from the database tables D. Organizational Management
_D_ Reviews the reports prepared specifically for high level use
2. For the four tables in the THEME_PARK database, identify whether each is an entity or relationship
(circle either Entity or Relationship for each table):
Ride Entity Relationship
Ride_Schedule Entity Relationship
Operator Entity Relationship
Manager Entity Relationship
3. To request that a Relational Database Management System prepare a result set showing a simple list of all Rides scheduled for operation today (June 5, 2002) the best SQL command would be (circle the one best answer):
A. SELECT ALL Ride.Name from Ride, Ride_Schedule WHERE Ride_ID=Ride_FK
B. SELECT DISTINCT Ride.Name from Ride, Ride_Schedule WHERE Ride_ID=Ride_FK
C. SELECT ALL Ride.Name from Ride_Schedule
D. SELECT DISTINCT Ride.Name from Ride_Schedule
E. SELECT Ride.Name from Ride
How many rows would be returned in the result set (NOT including the header row)? _4_
4. You want to see the Rides listed in alphabetical order. Which clause would you add to your SQL query to produce the ordering you desire (circle the one best answer):
A. ORDER BY Ride.Name
B. ORDER BY ALPHA
C. ALPHA ON Ride.Name
D. ALPHA ASC
5. A colleague of yours in your organization processes the following valid SQL command:
ALTER TABLE Operator ADD COLUMN Boss text
You want to fill the new column such that Chachi manages anyone with 3 or less years of experience. Complete the
SQL commands that will fill the column out correctly according to this desire (fill in all the blanks):
UPDATE _Operator_ SET _Boss='Chachi' WHERE _Yr_Exp<4
UPDATE _Operator_ SET _Boss='Lili' WHERE _Yr_Exp>3
6. Show the result sets for the following commands (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:
SELECT Operator.Name FROM Ride_Schedule, Operator WHERE Shift=1 and Operator_FK=Operator_ID
Name
----
Jim
Bill
Jane
Sue
SELECT AVG(Age) AS Avg_Age FROM Ride WHERE EXISTS (SELECT * FROM Ride WHERE Ride_ID=1 OR Ride_ID=3 OR Ride_ID=5)
Avg_Age
-------
10
SELECT Ride.Name FROM Ride WHERE Age<6 AND HEIGHT<1.0
Name
--------
Tea_Cups
SELECT COUNT(Height) AS Shorts FROM Ride,Ride_Schedule,Operator,Manager WHERE Manager.Name='Chachi'
AND Manager.Name=Boss AND Operator_ID = Operator_FK AND Ride_ID = Ride_FK
(note this assumes you have processed the command from question 5 properly)
Shorts
------
5
(Note: On this one, I will only subtract 1 point if you said 3 because you read my intent and not the SQL
join. I will give 1 point extra credit to those who said 5 because you get rows for both shifts).
7. You are investigating a Java servlet that runs with the THEME_PARK database. You see the following piece of code:
...
ResultSet rs = st.executeQuery("SELECT * FROM Ride_Schedule, Operator WHERE Name='Bill' AND " +
"Operator_ID=Operator_FK");
int count=0;
while (rs.next()) {
count=count+1;
}
...
What will the count variable's value be after the while statement completes? _2_
8. You want to produce the following SQL query engine result set:
Ride_Name Operator
--------------- ---------------
Roller_Coaster Jim
Superman Bill
Complete the following SQL command that will produce the desired result set:
SELECT _Ride.Name AS Ride_Name_, _Operator.Name AS Operator_ FROM _Ride_, _Ride_Schedule_, _Operator_
WHERE Shift =_2_ AND Age > _9_ AND _Operator_ID_ = _Operator_FK_ AND _Ride_ID_ = _Ride_FK_
9. A brand new Ride named Freefall will be introduced tomorrow at the Theme Park. Which SQL command will properly set a record up in the Ride Table:
A. INSERT INTO Ride VALUES (6, 'Freefall', 12, 1.2)
B. UPDATE Ride INSERT (6, 'Freefall', 12, 1.2)
C. UPDATE Ride NEW VALUES (6, 'Freefall', 12, 1.2)
D. INSERT (6, 'Freefall', 12, 1.2) INTO Ride
E. INSERT VALUES (6, 'Freefall', 12, 1.2) INTO Ride
Would you use an executeQuery() or executeUpdate() SQL command servlet statement method to process the command above?
(circle either: executeQuery() or executeUpdate() )
10. You want your two most experienced (based on number of years experience) employees to operate the
new Freefall on tomorrow's Ride_Schedule entries (06-06-2002). Write 2 (two!) proper SQL statements
to make the Ride_Schedule table agree with your intentions:
INSERT INTO Ride_Schedule VALUES(6,2,1,'06-06-2002')
INSERT INTO Ride_Schedule VALUES(6,4,1,'06-06-2002')
11. You decide that adding the Boss column to the Operator table is sloppy and prefer instead to create a Manages table
that connects the Operator and Manager via Mngr_FK and Operator_FK attributes instead.
Would the Manages table be an Entity table or Relationship table (circle one)? Entity Relationship
If you filled the Manages table in properly so Chachi manages all employees with 3 or less years of experience and Lili manages the rest (circle one of the numbers for each of the four questions):
How many times would the number 0 appear in the Mngr_FK field of the Manages table? 0 1 2 3 4
How many times would the number 1 appear in the Mngr_FK field of the Manages table? 0 1 2 3 4
How many times would the number 0 appear in the Operator_FK field of the Manages table? 0 1 2 3 4
How many times would the number 1 appear in the Operator_FK field of the Manages table? 0 1 2 3 4
12. You want to create an SQL query engine result set with a single row (tuple) that reports the Shift
with the larger total number of years experience (sum of Yr_Exp attribute for all Operators on
the shift):
Which Shift number would you expect to appear in the result set (circle 1 or 2)? 1 2
Which of the following SQL fragments (pieces) could appear in the query you would write to obtain the above result set?
(circle all that would appear)
A. MAX(SUM(Yr_Exp))
B. GROUP BY Shift
C. LIMIT 1
D. SUM (Yr_Exp)
E. WHERE Shift >
For extra credit (make sure you have time to answer this), write a valid SQL query to generate the result: