Write the following queries in SQL (no top, rank, limit, or windows allowed) Cus
ID: 3551714 • Letter: W
Question
Write the following queries in SQL (no top, rank, limit, or windows allowed)
Customer (cid, name, address)
Car (vin, make, color, number-doors, daily-price)
Reservation (cid, vin, date, location, number-days)
Customer contains the customer id (the key), name and address of each customer who rents a car. Car contains information about vehicles: the vin (key), the make, color, number of doors (either 2 or 4), and the daily price to rent the car. Reservations registers each reservation, who reserved the car, which car was reserved, the location where the car was rented, the date the reservation starts, and the number of days it lasts. Each customer may make several reservations and each car can be reserved several times. Note that we assume no two reservations of the same car or by the same customer on the same day. Assume date has both month and year, as in March 2009.
Write the following queries in SQL (no top, rank, limit, or windows allowed)
1. List the names of customers who have reserved a green car during September 2013.
2. Give the number of reservations each month during all of 2010.
3. Find the number of reservations involving Ford cars in a location other than Detroit.
4. Find out customers who made more than 12 reservations in 2010.
5. List the longest reservations (the ones with the most number-days) in the whole database.
6. List the reservations with a duration (number-days) larger than the average duration.
7. List the car makes that have the most days in total reservations in 2010.
8. Find the cutstomers who have reserved only 4-door cars.
Explanation / Answer
-- 3 tables in oracle
create table Customer (cid number, name varchar2(250), address varchar2(500));
create table Car (vin number, make varchar2(25), color varchar2(25), number_doors number, daily_price number);
create table Reservation (cid number, vin varchar2(25), reserv_date date, location varchar2(50) , number_days number);
-- List the names of customers who have reserved a green car during September 2013.
SELECT c.name
FROM customer c, Reservation r, car cr
WHERE c.cid = r.cid
AND R.VIN = CR.VIN
AND reserv_date BETWEEN to_date('09/01/2013', 'mm/dd/yyyy') AND to_date('09/30/2013', 'mm/dd/yyyy')
AND UPPER(cr.color) = 'GREEN';
-- Give the number of reservations each month during all of 2010.
SELECT to_char(R.RESERV_DATE, 'mm') months, count(*)
FROM reservation r
WHERE to_char(R.RESERV_DATE, 'yyyy') = '2010'
GROUP BY to_char(R.RESERV_DATE, 'mm');
-- Find the number of reservations involving Ford cars in a location other than Detroit.
SELECT R.LOCATION, count(*)
FROM reservation r, car c
WHERE R.VIN = C.VIN
AND UPPER(C.MAKE) = 'FORD'
AND UPPER(R.LOCATION) <> 'DETROIT'
GROUP BY R.LOCATION;
-- Find out customers who made more than 12 reservations in 2010.
SELECT C.NAME, count(*)
FROM customer c, reservation r
WHERE C.CID = R.CID
AND to_char(R.RESERV_DATE, 'yyyy') = '2010'
GROUP BY C.NAME
HAVING count(*) > 12;
-- List the longest reservations (the ones with the most number-days) in the whole database.
SELECT * FROM reservation r1 WHERE R1.NUMBER_DAYS = (
SElECT MAX(R.NUMBER_DAYS)
FROM reservation r);
-- List the reservations with a duration (number-days) larger than the average duration.
SELECT * FROM reservation r1 WHERE R1.NUMBER_DAYS > (
SElECT AVG(R.NUMBER_DAYS)
FROM reservation r);
-- List the car makes that have the most days in total reservations in 2010.
SELECT C.MAKE
FROM car c
GROUP BY C.MAKE
HAVING COUNT(*) = (SELECT max(c1.cnt)
FROM (
SElECT C.MAKE, SUM(R.VIN) cnt
FROM reservation r, car c
WHERE R.VIN = C.VIN
AND to_char(R.RESERV_DATE, 'yyyy') = '2010'
GROUP BY C.MAKE ) c1);
-- Find the cutstomers who have reserved only 4-door cars.
SELECT c.name
FROM customer c, Reservation r, car cr
WHERE c.cid = r.cid
AND R.VIN = CR.VIN
AND CR.NUMBER_DOORS = 4;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.