PLEASE HELP OUT WITH QUESTIONS NUMBER #3, #4, #6, #7, #8 I AM STRUGGLING WITH TH

Question

PLEASE HELP OUT WITH QUESTIONS NUMBER #3, #4, #6, #7, #8 I AM STRUGGLING WITH THESE QUESTIONS. ALSO CAN YOU WRITE THESE IN SQL THEY ARE QUERIE PROBLEMS AND MAKE THEM SHORT AND FUNCTIONAL FOR THE QUESTIONS.. PLEASE AND THANKYOU.

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 forcign keys, before you write any querics 2. In a separate .sql 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 cach 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. (Bc surc to include companics 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

--3
SELECT d.ID,(d.FNAME ||' '||d.LNAME) AS NAME
FROM Driver d INNER JOIN RIDE r ON d.ID=r.DRIVERID
INNER JOIN CUSTOMER c ON r.CUSTOMER=c.ID
INNER JOIN ACCOUNT a ON c.ACCOUNTID=a.ID
WHERE a.NAME='Deloitte and Touche'
GROUP BY d.ID HAVING COUNT(r.CUSTOMER)>=1;

--4
SELECT (c.FNAME ||' '||c.LNAME) AS NAME, SUM(r.COST) AS TOTALCOST
FROM RIDE r
RIGHT OUTER JOIN CUSTOMER c ON r.CUSTOMER=c.ID
GROUP BY c.FNAME,c.LNAME
ORDER BY c.LNAME;

--5
SELECT DRIVERID FROM RIDE
GROUP BY DRIVERID HAVING COUNT(DRIVERID) >=3;

--6
SELECT a.NAME,COUNT(r.RIDEID)
RIDE r
RIGHT OUTER JOIN CUSTOMER c ON r.CUSTOMER=c.ID
RIGHT OUTER JOIN ACCOUNT a ON c.ACCOUNTID=a.ID
GROUP BY a.NAME
ORDER BY COUNT(r.RIDEID) DESC;

--7
SELECT MIN(r.DISTANCE)
FROM Driver d INNER JOIN RIDE r ON d.ID=r.DRIVERID
INNER JOIN CUSTOMER c ON r.CUSTOMER=c.ID
WHERE d.MAKE='Jeep'
GROUP BY r.CUSTOMER;

--8
SELECT (c.FNAME ||' '||c.LNAME) AS NAME ,d.PHONE
FROM Driver d INNER JOIN RIDE r ON d.ID=r.DRIVERID
INNER JOIN CUSTOMER c ON r.CUSTOMER=c.ID
INNER JOIN ACCOUNT a ON c.ACCOUNTID=a.ID
WHERE a.NAME='JP Morgan Chase' AND d.FNAME='Bret' AND d.LNAME='McKenzie';

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

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