Draw data models for a restaurant menu, tree, and photograph.
1a.
1b.
1c.
Do the following queries based on the OFFICE table using SQL.
| OFFICE | ||||||
| ROOMID | LENGTH | WIDTH | HEIGHT | RENTPRICE | NUMWINDOW | OCCUPANT |
2a.
Who occupies the office with the roomid = 1234?
SELECT OCCUPANT FROM OFFICE WHERE ROOMID = 1234
2b.
What is the average rental price for an office?
SELECT AVG(RENTPRICE) FROM OFFICE
2c.
What is the average rental price for offices with two windows?
SELECT AVG(RENTPRICE) FROM OFFICE WHERE NUMWINDOW = 2
2d.
How many offices are occupied by Miller, Inc.?
SELECT COUNT(ROOMID) FROM OFFICE WHERE OCCUPANT = 'Miller, Inc.'
2e.
How large (in square feet) is the office with roomid 2345?
SELECT LENGTH*WIDTH FROM OFFICE WHERE ROOMID = 2345
2f.
What is the average price per square foot for an office with one window?
SELECT AVG(PRICE/(LENGTH*WIDTH)) FROM OFFICE
WHERE NUMWINDOW = 1
Find the error(s) in the following SQL to answer the query:
Select # from SHR where shrcode = (BS or PT
Answer:
1. the # should be a *
2. the = should be in
3. there needs to be single quotes around BS and PT
4. there should be a comma instead of the word OR
4.
Find the error(s) in the following SQL to answer the query:
Select Count(*) from shrqty where shrcode >15000
Answer :
1. count should be sum
2. * should be shrqty
3. shrqty should be shr
4. shrcode should be shrqty
5. > should be <
5. Find the error(s) in the following SQL to answer the query:
Modify shr
shrprice=shrprice*.1
where shrprice =5
Answer:
1. Modify should be Update
2. On the second line the key word SET is missing
3. .1 should be 1.1
4. = should be <
| This page is part of the promotional and support
material for Data Management (open edition) by Richard T. Watson For questions and comments please contact the author |