 |
1 | |
Reference: 10-1
CUSTOMER (primary key = CID)
 <![CDATA[<a onClick="window.open('/olcweb/cgi/pluginpop.cgi?it=jpg::::/sites/dl/free/0072942207/90831/10_1A.jpg','popWin', 'width=NaN,height=NaN,resizable,scrollbars');" href="#"><img valign="absmiddle" height="16" width="16" border="0" src="/olcweb/styles/shared/linkicons/image.gif"> (22.0K)</a>]]>
RENTALS (primary key = RTN)
 <![CDATA[<a onClick="window.open('/olcweb/cgi/pluginpop.cgi?it=jpg::::/sites/dl/free/0072942207/90831/10_1B.jpg','popWin', 'width=NaN,height=NaN,resizable,scrollbars');" href="#"><img valign="absmiddle" height="16" width="16" border="0" src="/olcweb/styles/shared/linkicons/image.gif"> (26.0K)</a>]]>
In the table RENTALS, RTN provides the rental number (it is the primary key), CID refers to the CID in CUSTOMER, PICKUP is the city where the car was (or will be) picked up and Return is the city where the car was (or will be) returned, VIN is a foreign key that references the table CAR. The rental company has two branches, one in San Diego (SD) and one in Los Angeles (LA). Cars are picked up and returned by the customers at these two locations.
RENTCOST
 <![CDATA[<a onClick="window.open('/olcweb/cgi/pluginpop.cgi?it=jpg::::/sites/dl/free/0072942207/90831/10_1C.jpg','popWin', 'width=NaN,height=NaN,resizable,scrollbars');" href="#"><img valign="absmiddle" height="16" width="16" border="0" src="/olcweb/styles/shared/linkicons/image.gif"> (10.0K)</a>]]>
In RENTCOST, COST shows the base cost of renting a given MAKE for one day.
CAR
 <![CDATA[<a onClick="window.open('/olcweb/cgi/pluginpop.cgi?it=jpg::::/sites/dl/free/0072942207/90831/10_1D.jpg','popWin', 'width=NaN,height=NaN,resizable,scrollbars');" href="#"><img valign="absmiddle" height="16" width="16" border="0" src="/olcweb/styles/shared/linkicons/image.gif"> (23.0K)</a>]]>
The table CAR (primary key = VIN) provides information about each car, in particular if it is currently rented, and where its usual storage location is (attribute AT).
CREATE VIEW CUST_SD AS
SELECT * FROM CUSTOMER
WHERE Cregion = 'SD'
CREATE VIEW CUST_40D AS
SELECT CNAME, RENTALS.MAKE, RENTCOST.COST
FROM CUSTOMER, RENTALS, RENTCOST
WHERE CUSTOMER.CID = RENTALS.CID
AND RENTALS.MAKE = RENTCOST.MAKE
AND RENTCOST < 40
CREATE VIEW Make_View (Make, NumRentals, Cregion, Storageat) AS
SELECT RENTALS.MAKE, Count (RTN), Cregion, AT
FROM CUSTOMER, RENTALS, CAR
WHERE CUSTOMER.CID = RENTALS.CID
AND RENTALS.VIN = CAR.VIN
GROUP BY RENTALS.MAKE, Cregion, At
SELECT * FROM CUST_SD
WHERE AGE < 25
The number of rows displayed by this SELECT statement is: |
| | A) | 0 |
| | B) | 1 |
| | C) | 2 |
| | D) | 3 |
|
|
2 | |
The view Make-View provides the following information: |
| | A) | The number of rentals per make |
| | B) | The number of rentals per make and storage place of the car |
| | C) | The number of rentals per make, customer region, and storage place of the car |
| | D) | Each car rented, how often, and where stored |
|
|
3 | |
The maximum value in the column Storageat of the view Make_View is: |
| | A) | 1 |
| | B) | 2 |
| | C) | 3 |
| | D) | 4 |
|
|
4 | |
Which of the following queries is the best candidate for execution using the modification method rather than the materialization method? |
| | A) | SELECT Make, COUNT(*) FROM Make_View
GROUP BY Make |
| | B) | SELECT * FROM CUST_40D WHERE COST = 40 |
| | C) | SELECT CUST_SD.CNAME, RENTALS.DATE_OUT
FROM CUST_SD, RENTALS
WHERE CUST_SD.CID = RENTALS.CID
AND CUST_SD.Cregion = RENTALS.RETURN |
| | D) | They are all equal candidates |
|
|
5 | |
There is a request to execute the two following SQL2 commands in the order shown:
CREATE VIEW CUST1 AS
SELECT CNAME, AGE FROM CUSTOMER
UPDATE CUST_SD
SET AGE = AGE +1
Select the most appropriate statement: |
| | A) | After execution of these queries, SELECT * from CUST_SD will show a value of 41 for BLACK |
| | B) | After execution of these queries, SELECT * from CUSTOMER will show a value of 41 for BLACK |
| | C) | CUST_SD is not updatable because some of the attributes of the base table are missing from it |
| | D) | CUST_SD is not updatable because it does not contain the primary key of the base table |
|
|
6 | |
For each Cregion, you want to show each customer (CNAME), and for each customer, each rental ordered by Make, showing PICKUP, RETURN and DATE-OUT. Which of the following would be part of a detail line in a hierarchical report? |
| | A) | MAKE |
| | B) | DATE_OUT |
| | C) | Cregion |
| | D) | CNAME |
|
|
7 | |
Reference: 10-2
CUSTOMER (primary key = CID)
CNAME is NOT NULLIMAGE HERE
RENTALS (primary key = RTN)
CID is a foreign key referencing CUSTOMER
CID is NOT NULL
MAKE is NOT NULL
MAKE is a foreign key referencing RENTCOST
IMAGE HERE
RENTCOST
MAKE is the primary key
IMAGE HERE
(Access) View1:
SELECT RTN, MAKE, PICKUP, RENTALS.CID,
CUSTOMER.CID, CNAME, AGE, Cregion
FROM CUSTOMER INNER JOIN RENTALS
ON CUSTOMER.CID = RENTALS.CID
(Access) View2:
SELECT RTN, MAKE, PICKUP, RENTALS.CID,
CNAME, AGE, Cregion
FROM CUSTOMER INNER JOIN RENTALS
ON CUSTOMER.CID = RENTALS.CID
(Access) View3:
SELECT RTN, RENTALS.MAKE, PICKUP, RENTALS.CID,
CNAME, AGE, Cregion, COST
FROM (CUSTOMER INNER JOIN RENTALS
ON CUSTOMER.CID = RENTALS.CID.
INNER JOIN RENTCOST
ON RENTALS.MAKE = RENTCOST.MAKE
(Access) INSERT INTO View1
(RTN, MAKE, PICKUP, RENTALS.CID, CUSTOMER.CID, CNAME)
VALUES (11, 'GM', 'SD', 70, 70, 'JORDAN' )
Which of the following is not true? |
| | A) | This command inserts 1 row into RENTALS |
| | B) | This command inserts 1 row into CUSTOMER |
| | C) | After execution of this command, SELECT MAKE FROM View1 WHERE CNAME='JORDAN' will show 1 row |
| | D) | There is no row inserted into the CUSTOMER table |
|
|
8 | |
(Access) UPDATE View2
SET MAKE = 'NISSAN', Cregion = 'LA'
WHERE CNAME = 'BLACK'
Which of the following is not true? |
| | A) | This command updates 3 rows in RENTALS |
| | B) | This command updates 1 row in CUSTOMER |
| | C) | After execution of this command, SELECT MAKE FROM View2 will show 5 rows where MAKE has 'NISSAN' for value |
| | D) | The CUSTOMER table is not updated |
|
|
9 | |
(Access) Which of the following statements is true? |
| | A) | A row may be inserted into RENTALS, using an INSERT command on View3 |
| | B) | A row may be inserted into CUSTOMER, using an INSERT command on View3 |
| | C) | A row may be inserted into RENTCOST, using an INSERT command on View3 |
| | D) | All of the above |
|
|
10 | |
You want to create a form to process the rentals of a given customer. Which of the following is not true? |
| | A) | CNAME should appear in the main form |
| | B) | CID should appear in the subform |
| | C) | CID should appear in the main form |
| | D) | Cregion should appear in the main form |
|