week: Sections 6.6 and 3.1-3.3 of Ullman/Widom Your task in this assignment is t

Question

week: Sections 6.6 and 3.1-3.3 of Ullman/Widom Your task in this assignment is to write a set of SQL queries on a set of tables I supply. 1. First, download the script file Rides.sql from the course web site and run it in SQLDeveloper to construct a database instance containing four tables: DRIVER, CUSTOMER, ACCOUNT and RIDE. These tables represent rides taken by corporate customers using a ride sharing service. Each ride matches a customer with a driver, and each customer charges their rides to the corporate account of the company they work for Inspect the tables and their schemas in SQLDeveloper so that you understand the structure of the database. I recommend that you sketch the schema of the database, including all primary keys and foreign keys, before you write any queries 2. In a separate .sal file (do not modify Rides.sql), write a script that contains the following eight SQL queries (in this order): 1. List the full names of all customers that charge their rides to the 'NBC Universal account. Order the names alphabetically by the first name. 2. Display the average cost for all rides given by the driver 'Tina Weymouth' 3. Give the IDs and full names of all drivers who have given at least one ride to a customer who charges their rides to 'Deloitte and Touche'. 4. For each customer, display the customer's full name, and the total cost of that customer's rides. Order the output alphabetically the customer's last name. (Be sure to include customers who have not taken any rides yet.) 5. Give the IDs of all drivers who have given at least three rides 6. For each company, give the name of the company and the total number of rides that have been charged to that company's account. Order the companies from the one with the highest total number of rides to the one with the lowest. (Be sure to include companies that have not been charged for any rides yet.) 7. Find the length of the shortest ride any customer has taken in a Jeep 8. Give the names and phone numbers of all customers who charge their rides to 'JP Morgan Chase' and have taken a ride with the driver 'Bret McKenzie'

Explanation / Answer

1. Select FNAME,LNAME FROM CUSTOMER INNER JOIN RIDE ON CUSTOMER.ID = RIDE.CUSTOMER INNER JOIN ACCOUNT ON CUSTOMER.ACCOUNTID = ACCOUNT.ID WHERE ACCOUNT.NAME = 'NBC Universal';

2. SELECT AVG(COST) FROM RIDE INNER JOIN DRIVER ON RIDE.DRIVERID = DRIVER.ID WHERE DRIVER.FNAME = 'Tina' AND DRIVER.LNAME = 'Weymouth';

3. SELECT ID,FNAME,LNAME FROM DRIVER INNER JOIN RIDE ON RIVER.ID = RIDE.DRIVER INNER JOIN CUSTOMER ON CUSTOMER.ID = RIDE.CUSTOMER INNER JOIN ACCOUNT ON ACCOUNT.ID = CUSTOMER.ACCOUNTID WHERE ACCOUNT.NAME = 'Deloitte and Touche';

4. SELECT FNAME,LNAME SUM(COST) FROM CUSTOMER LEFT INNER JOIN RIDE ON CUSTOMER.ID = RIDE.CUSTOMER GROUP BY CUSTOMER.ID ORDER BY LNAME;

5. SELECT ID FROM DRIVER INNER JOIN RIDE ON DRIVER.ID = RIDE.DRIVERID GROUP BY ID HAVING COUNT(RIDEID) >= 3;

6. SELECT NAME,COUNT(RIDEID) FROM ACCOUNT LEFT INNER JOIN CUSTOMER ON ACCOUNT.ID = CUSTOMER.ACCOUNTID INNER JOIN RIDE ON CUSTOMER.ID = RIDE.CUSTOMER GROUP BY NAME ORDER BY COUNT(RIDEID) DESC;

7. SELECT MIN(DISTANCE) FROM RIDE INNER JOIN CUSTOMER ON CUSTOMER.ID = RIDE.CUSTOMER INNER JOIN DRIVER ON DRIVER.ID = RIDE.DRIVERID WHERE MAKE = 'Jeep';

8. SELECT FNAME,LNAME,PHONE FROM RIDE INNER JOIN CUSTOMER ON CUSTOMER.ID = RIDE.CUSTOMER ?WHERE NAME = 'JP Morgan Chase' AND DRIVER.FNAME = 'Bret' AND DRIVER.LNAME = 'Mckenzie';

Do ask if any doubt. Please upvote.

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.