RELATIONAL DATA RETRIEVAL: SQL - Fundamentals of Database Management Systems - page 95

Australia must be determined first in the subquery. Then that result must be used
in the main query, where it must be compared only to the salaries of Australian
pianists.

SELECT MUSNAME
FROM MUSICIAN, ORCHESTRA
WHERE MUSICIAN.ORCHNAME=ORCHESTRA.ORCHNAME
AND INSTRUMENT='Piano'
AND COUNTRY='Australia'
AND ANNSALARY=
(SELECT MAX(ANNSALARY)
FROM MUSICIAN, ORCHESTRA
WHERE MUSICIAN.ORCHNAME=ORCHESTRA.ORCHNAME
AND INSTRUMENT='Piano'
AND COUNTRY='Australia');

EXAMPLE: LUCKY RENT-A-CAR

Figure 4.4 is the Lucky Rent-A-Car relational database. Here is a list of queries for
Lucky Rent-A-Car.

1.

''List the manufacturers whose names begin with the letter ''C'' or the letter
''D'' and that are located in Japan.''

SELECT MANUFNAME
FROM MANUFACTURER

MANUFACTURER table

MANUFNAME COUNTRY SALESREPNAME SALESREPPHONE

CAR table

CARNUM MODEL YEAR CLASS MANUFNAM E

MAINTENANCE table

REP AIRNUM CARNUM DATE PROCEDURE MILEAGE REPAIRTIME

CUSTOMER table

CUSTNUM CUSTNAME CUSTADDR CUSTPHONE

RENTAL table

FIGURE4.4
Lucky Rent-A-Car relational database

CARNUM CUSTNUM RENTALDATE RETURNDATE COST