Lab #3 (Total score: 5 x 21 + 11 x 3 = 138) - UH
Lab #3 (Total score: 5 x 21 + 22 = 127)
Due Date: 10/21 (Tu.)
Run the “bestRental_Lab3.sql” script file. Note that some tables’ structure for the BestRental database has been changed. Write SQL programs to complete the following exercises.
► You need to make sure whether the result is right or not. Having response from Oracle does not necessarily mean that your programs are correct.
Queries:
1. List the client’s name and telephone number for all clients who have rented any car during the year 2002.
2. List the vehicle number, make, and model for all vehicles that have fault reports.
3. For every rental, list the rental number, the client number, and name of the client who rented the car. If a car has been returned, display “Returned”, otherwise, “Not Returned” in the same column for each record.
4. List the clients’ number and name for all clients who have checked out cars in California.
5. List the client number, name, client telephone number, and the mileage driven for all clients who rented a car and returned in two days. Sort the data by the mileage driven in ascending order. In addition, if a client telephone number is not given, display “NO TELEPHONE.”
6. Find the client number, name, city, and state for all clients who have checked out “TOYOTA”.
7. List the outlet number, city, and state for every outlet that has a manager who lives in the city where he/she manages. Use IN operator.
8. Use the EXISTS operator to solve the question 7.
9. Which city has more than one outlet?
10. List the manager’s name (first and last) for all managers who manage a outlet in California.
11. List the client number and client name for all clients who have rented the least expensive car (e.g., lowest rental rate), along with the rented vehicle’s make and model.
12. For each outlet, list the outlet number, city, state, and the number of employees.
13. List the client’s number and name (first and last) for all clients who have rented more than one car. Sort the result by the client name.
14. For every outlet, list all available (currently not checked out) vehicles’ number, maker, and model, along with the outlet number and city.
15. List the name, city, state of client, and the hire rate of the vehicle for each client who has rented a vehicle whose the hire rate is higher than the average hire rate of all cars.
16. List the city and state of the outlet that pays the largest amount for employees’ salary (the largest amount of salary means the sum of all the employees’ salary in that city).
17. List the client number, city, and state for all clients who have rented more than one car.
18. List the vehicle license number, make, model, and comments from fault report for all vehicles. If a vehicle does not have a fault report, list “NO FAULT” for the comments.
19. List all the city names (unique list) from two tables, client and outlet.
20. Every rental agreement requires signature of the manager of an outlet. List the name of manager who signed the rental agreement for a client, “Austin Powers”.
21. Create a table, called VEHICLE_STATS, which contains a vehicle maker, model, and the total number of rentals. Populate the VEHICLE_STATS table without using INSERT command. Show the populated data in the VEHICLE_STATS table.
Turn in your answers and the results returned from Oracle in the following format:
Question#1
Your SQL program
The result returned from Oracle
Question#2
Your SQL program
The result returned from Oracle
…
Creating and using Views: [22]
a. Create a view named MYCLIENT. MYCLIENT contains the information on client’s name, city, state, zipcode, and telephone number. [3]
b. Display all the records stored in the view MYCLIENT using SELECT command. [1]
c. Write a SQL command to retrieve the client name and city for every client who lives in California using the view MYCLIENT. [2]
d. Create a view named FAVORITECOLOR that consists of make, model, number of doors for every vehicle that has silver color. [3]
e. Write a SQL command to retrieve the make, model of all cars that have two doors with silver color using FAVORITECOLOR. [2]
f. For the query in e, what is the actual SQL query that DBMS will execute? [6]
g. Insert your own favorite car information into the VEHICLE table. Can you see your record in the view FAVORITECOLOR? [1]
h. Delete the record you inserted in g from the VEHICLE table. Can you see the record deleted from the view, FAVORITECOLOR? [1]
i. Insert your favorite car information into the view FAVORITECOLOR. Can you insert it? If you can't, explain why not? Can you delete a record from the view, FAVORITECOLOR? If you can’t, how can you delete the record? [4]
j. Drop the view FAVORITECOLOR. [1]
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.