Hello. Can you help me please with some queries at SQL, please? Here are the que
ID: 3756661 • Letter: H
Question
Hello. Can you help me please with some queries at SQL, please? Here are the queries and the diagram. Thank you very much. I appreciate.
1.Using a subquery, list which service categories have never been assigned or worked on.
2.Using a subquery, which unit(s) does Alyssa Kelly own?
3.Whose condo fee is more than the average condo fee?
4.Using an EXISTS, which service categories have had more than 5 hours of work spent total?
SERVICE REQUEST SERVICE CATEGORY CATEGORY_NUM SERVICELID CONDO D CATEGORY NUM STATUS EST HOURS SPENT HOURS NEXT SERVICE DATE CONDO UNIT CONDOLD UNIT NUM SOR FT BDRMS BATHS CONDO FEE OWNER NUM OWNER OWNER NUM LAST NAME FIRST NAME ADDRESS CITY STATE POSTAL CODE LOCATION ADDRESS CITY STATE POSTAL CODEExplanation / Answer
1. Using a subquery, list which service categories have never been assigned or worked on.
To Perform this query we need to join the two tables SERVICE_REQUEST and SERVICE_CATEGORY to get category description else we can perform directly on service_request
**QUERY 1
Select CATEGORY_DESCRIPTION from SERVICE_CATEGORY join SERVICE_REQUEST ON SERVICE_CATEGORY. CATEGORY_NUM = SERVICE_REQUEST. CATEGORY_NUM
Where
SERVICE_REQUEST.STATUS not like (“Assigned” or “worked_on”)
2. Using a subquery, which unit(s) does Alyssa Kelly own?
To Perform this query we need to join the two tables CONDO_UNIT and OWNER.
**QUERY 2
Select UNIT_NUM from CONDO_UNIT join OWNER on UNIT_NUM.OUNER_NUM=OWNER.OWNER_NUM
Where
OWNER.LAST_NAME like ”Alyssa” and OWNER.FIRST_NAME like “Kelly”;
3. Whose condo fee is more than the average condo fee?
**QUERY 3
Select OWNER_NUM from CONDO_UNIT where CONDO_FEE > (select AVG(CONDO_FEE) from CONDO_UNIT)
4. Using an EXISTS, which service categories have had more than 5 hours of work spent total?
**QUERY 4
Select CATEGORY_NUM from SERVICE_REQUEST where EXISTS
(select * from SERVICE_REQUEST where SPENT_HOURS > 5)
A little modifications may be needed as per your requirement as i provided as per my understandability.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.