The
University of Texas
CS
327e Spring
2003 Quiz 1
Date: 2003-02-28 Duration: 60 minutes
Total: 100 points. Each
multiple-choice question is weighted equally, and is worth 10 points.
Use a pencil to fill in the appropriate circle for
the correct answer.
NAME:
______________________________________ LAST 4 DIGITS SSN: ________________
1)
Assume the following CREATE TABLE statement for the
SHIPMENT table is
CREATE TABLE SHIPMENT
(SID char (8) not null,
JID char (8) not null,
PID char (8) not null,
Qty decimal (9,2) null)
Assuming this table contains 500 rows, some are
shown in the schema on the last page.
In that case,
SELECT SUM (Qty)/COUNT (Qty), SUM (Qty)/COUNT (SID),
AVG (Qty) FROM SHIPMENT
Will ALWAYS return the same value in all 3 columns.
a)
TRUE b) FALSE
2)
The following statement(s) are true for VIEWS:
a)
You can make all data available to users without
having to worry about the corruption of data in the underlying base tables.
b)
Provide the ability to restrict users to only the
specified portions of the data, which increases privacy as well as data
security.
c)
Views are dynamically updated.
d)
All of the above.
e)
None of the above.
3)
For the data shown in the last page, the following 2
statements will always result in the same answer:
i) SELECT SID FROM SHIPMENT
WHERE QTY = (SELECT MAX (QTY) FROM SHIPMENT);
ii) SELECT SID FROM SHIPMENT
WHERE QTY = MAX (QTY);
a)
TRUE b)
FALSE
4)
To delete all rows from a table T1, you will use the
following command:
a)
DROP TABLE T1
b)
DELETE FROM T1
c)
DELETE FROM T1 WHERE COUNT (ROWS) = 0
d)
PLEASE DELETE ALL ROWS FROM T1
e)
DELETE INTO T1
5)
Sometimes, lower (than 3rd) normalization
forms occur in specialized databases known as data warehouses.
a)
TRUE b) FALSE
6) a) (10 pts) Write the SELECT statement for the
following English statement:
Give the name of supplier(s), along with their total
shipments, who have made at least 3 shipments.
SELECT SUPPLIER.NAME, COUNT (*) AS TOTAL_SHIPMENTS
FROM SUPPLIER, SHIPMENT
WHERE SUPPLIER.SID = SHIPMENT.SID
HAVING COUNT (*) > = 3
b)
(10 pts) Draw the resultant table.
NAME TOTAL_SHIPMENTS
Adams 10
Jones 8
7) a) (20 pts) Write the SQL statement(s) for the
following English statement:
Give the city name(s) where the supplier(s), who has
(have) shipped the least number of parts, resides.
HINT: You can create a VIEW, and use it to determine
your final SELECT.
NOTE: There are many solutions to this problem. This
is 1 way to do it.
CREATE VIEW SUPPLIER_NUMPARTS
(SUPPLIER,
NUMPARTS)
AS
SELECT SHIPMENT.SID, SUM (SHIPMENT.QTY)
FROM SHIPMENT
GROUP BY SHIPMENT.SID
SELECT SUPPLIER.CITY
FROM SUPPLIER, SUPPLIER_NUMPARTS
WHERE
SUPPLIER.SID = SUPPLIER_NUMPARTS.SUPPLIER
AND
SUPPLIER_NUMPARTS.NUMPARTS IN
(
SELECT MIN (NUMPARTS)
FROM SUPPLIER_NUMPARTS
)
b)
(10 pts) Draw the resultant table.
CITY