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

# GROUP BY SUPPLIER.NAME

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

## London