The following are the queries to be completed for the last phase of the project.

Question

The following are the queries to be completed for the last phase of the project.

Please first run the SQL codes in the Phase 2 solutions file to create and populate tables. (You may need to first drop your existing tables before you could run these codes).

QUERIES

List the customer name and phone number for every customer that owns a BMW, Honda or Toyota.

List the part number and part description for every part that has less than 10 in stock.

List the customer name and vehicle serial number for those customers who have had more than one repair order.

List the repair order number, customer first name, last name, vehicle make, and date of all repair orders from November 2005. Sort the results by date, and then by customer last name.

List the value of the parts inventory currently in stock. For each part, list the part number, unit cost, quantity in stock and the total value of the quantity in stock (quantity in stock X unit cost).

Deliverables:

A Notepad file (.txt or .sql) containing your SQL codes

A Word file (.doc or docx) containing screen shots of query results from Oracle.

Explanation / Answer

If you have any doubts, please give me comment...

-- List the customer name and phone number for every customer that owns a BMW, Honda or Toyota

SELECT CustFName, CustLName, CustPhone

FROM Customer C, Vehicle V

WHERE C.CustNo = V.CustNo AND (V.Make = 'Hoda' || V.Make='BMW' || V.Make='Toyota');

-- List the part number and part description for every part that has less than 10 in stock.

SELECT PartNo, PartDesc

FROM Part

WHERE PartStk<10;

-- List the customer name and vehicle serial number for those customers who have had more than one repair order.

SELECT CustFName, CustLName, R.VIN,

FROM Customer C, Vehicle V, RepairOrder R

WHERE C.CustNo = V.CustNo AND V.VIN = R.VIN

GROUP BY CustFName, CustLName, R.VIN

HAVING COUNT(*)>1;

-- List the repair order number, customer first name, last name, vehicle make, and date of all repair orders from November 2005. Sort the results by date, and then by customer last name.

SELECT RepOrdNo, CustFName, CustLName, V.Make, RepOrdDate

FROM Customer C, Vehicle V, RepairOrder R

WHERE C.CustNo = V.CustNo AND V.VIN = R.VIN AND RepOrdDate>'11-01-2005'

ORDER BY RepOrdDate, CustLName;

-- List the value of the parts inventory currently in stock. For each part, list the part number, unit cost, quantity in stock and the total value of the quantity in stock (quantity in stock X unit cost).

SELECT partNo, PartPPU, PartStk, (PartStk * PartPPU) AS total

FROM Part;

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

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