in the below ERD. why I am getting errors. Please check the bold comments and the ERD
Question:
in the below ERD. why I am getting errors.
Please check the bold comments and the ERD attached.
Thank you in advance for your help
- List boats that need engine repair. List boat owner name. Sort by boat id
Ans.
SELECT BoatId, firstname || ',' || lastname "OwnerName"
FROM Boats
JOIN clients USING (clientID)
JOIN repairorders USING (repairorder#)
WHERE description = 'Engine repair'
ORDER BY Boatid;
-- it says "description invalid identifier"
2. Identify boats and their owners. Owner and boats should be in mixed case. Order by boated.
Ans.
SELECT boatid, INITCAP (firstname) ||','||INITCAP (lastname) "Boat owner"
FROM repairorders,clients
WHERE clients.clientid = boatid
ORDER BY boatid
" No data found. I know that I have data"
3. Provide a list of employees. Include employee id, name, address, and phone number. Names should be in mixed case and phone numbers formatted with hyphens. Sort by employee id.
Ans
SELECT employeeid "Employee ID", INITCAP (empFName)||','|| INITCAP (empLName) " Employee Name ", EmpAddress, SUBSTR(EmpPhone,1,3) ||'-'|| SUBSTR(EmpPhone,4,3)||'-'||SUBSTR(EmpPhone,7,4)||','|| "PHONE"
FROM employees
ORDER BY employeeId;
4. List repair order that includes Navigation, Electrical repair. Include boatid, repairorder#, and repaircode. Sort by repairorder# and repaircode.
Ans.
SELECT BoatId, RepairorderId, Repaircode
FROM RepairOrders
WHERE description IN ('Navigation', 'Electrical repair')
ORDER BY Repairorderid, RepairCode;
5. Provide a list of all repair orders. Include RepairOrderDate, RepairBeginDate, RepairEndDate. Dates should be in MM/DD/YYYY format. If an order is still open specify *** Currently*** in the repairenddate filed.
Ans.
SELECT RepairOrder#, TO_CHAR(RepairOrderDate,'MM/DD/YYYY')"Repair Order date",TO_CHAR(RepairBeginDate,'MM/DD,YYYY')"Repair begin", NVL2(TO_CHAR(RepairEndDate,'MM/DD/YYYY'),***CURRENTLY***) "Repair ended"
FROM RepairOrders
ORDER BY RepairOrder#;
6. Identify orders totaling over $500. Include boatID.
Ans.
SELECT DISTINCT BoatId
FROM Repairorders
WHERE( Cost) > 500;
7. What is the price for each service and what is the average price for all services?
Ans.
SELECT RepairCode, Description, cost
(SELECT AVG(cost) AS averageprice
FROM RepairTypes;)
*****Description in repairtypes lists the services offered.****
Income Tax Fundamentals 2013
ISBN: 9781285586618
31st Edition
Authors: Gerald E. Whittenburg, Martha Altus Buller, Steven L Gill