HELP WITH #3, #4, #6, #7, #8 QUESTIONS? week: Sections 6.6 and 3.1-3.3 of Ullman

Question

HELP WITH #3, #4, #6, #7, #8 QUESTIONS?

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

SELECT D.ID
,D.FNAME
FROM DRIVER D
,CUSTOMER C
,RIDE R
,ACCOUNT A
WHERE
D.ID = R.DRIVERID
C.ID = R.CUSTOMER
C.ACCOUNTID = A.ID
AND A.ID = 'Deloitte and Touche'
---------------------------------------

SELECT CONCAT (FNAME," ",LNAME) AS FULLNAME,SUM(COST) FROM CUSTOMER C
LEFT JOIN RIDER ON C.ID = R.CUSTOMER
GROUP BY R.CUSTOMER ORDER BY C.LNAME DESC

-----------------------------------------------------------

SELECT A.NAME,SUM(R.COST) AS COST FROM ACCOUNT A
LEFT JOIN CUSTOMER C ON A.ID = C.ACCOUNTID
LEFT JOIN RIDE R ON R.CUSTOMER = C.ID
GROUP BY A.ID ORDER BY COST DESC

-----------------------------------------------------------

SELECT DISTANCE FROM RIDE R
, DRIVER D
WHERE R.DRIVERID = D.ID
AND D.MAKE = 'Jeep'
ORDER BY DISTANCE ASC LIMIT 1
-----------------------------------------------------------

SELECT CONCAT(FNAME," ",LNAME) AS NAME, PHONE FROM CUSTOMER C
, ACCOUNT A
, DRIVER D
WHERE CONCAT(D.FNAME," ",LNAME) = 'Bret McKenzie'
AND C.ACCOUNTID = A.ID
AND A.NAME = 'JP Morgan Chase'

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

---

---

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