ISYS 2263 Chapter 10 Database
Assignment
Task 1: For every property, list the management office number, address, monthly rent,
owner number, owner's first name, and owner's last name. - ✔️✔️SELECT
O.OFFICE_NUM, P.ADDRESS, P.MONTHLY_RENT, OW.OWNER_NUM,
OW.FIRST_NAME, OW.LAST_NAME
FROM OFFICE O
JOIN PROPERTY P ON O.OFFICE_NUM = P.OFFICE_NUM
JOIN OWNER OW ON P.OWNER_NUM = OW.OWNER_NUM
GROUP BY P.PROPERTY_ID
Task 1: For every property, list the management office number, address, monthly rent,
owner number, owner's first name, and owner's last name - ✔️✔️SELECT
PROPERTY.OFFICE_NUM, PROPERTY.ADDRESS, PROPERTY.MONTHLY_RENT,
OWNER.OWNER_NUM, OWNER.FIRST_NAME, OWNER.LAST_NAME
FROM PROPERTY, OWNER
WHERE PROPERTY.OWNER_NUM = OWNER.OWNER_NUM
Task 2: For every completed or open service request, list the property ID, description,
and status. - ✔️✔️SELECT PROPERTY_ID, DESCRIPTION, STATUS FROM
SERVICE_REQUEST
Task 3: For every service request for janitorial work, list the property ID, management
office number, address, estimated hours, spent hours, owner number, and owner's last
name. - ✔️✔️SELECT P.PROPERTY_ID, P.OFFICE_NUM, P.ADDRESS,
S.EST_HOURS, S.SPENT_HOURS, O.OWNER_NUM, O.LAST_NAME
FROM PROPERTY P
JOIN SERVICE_REQUEST S ON P.PROPERTY_ID = S.PROPERTY_ID
JOIN OWNER O ON P.OWNER_NUM = O.OWNER_NUM;
Task 3: For every service request for janitorial work, list the property ID, management
office number, address, estimated hours, spent hours, owner number, and owner's last
name - ✔️✔️SELECT PROPERTY.PROPERTY_ID, PROPERTY.OFFICE_NUM,
, PROPERTY.ADDRESS, SERVICE_REQUEST.EST_HOURS,
SERVICE_REQUEST.SPENT_HOURS,
OWNER.OWNER_NUM, OWNER.LAST_NAME
FROM PROPERTY, SERVICE_REQUEST, OWNER WHERE
PROPERTY.PROPERTY_ID = SERVICE_REQUEST.PROPERTY_ID
AND PROPERTY.OWNER_NUM = OWNER.OWNER_NUM
Task 4: List the first and last names of all owners who own a two-bedroom property.
Use the IN operator in your query. - ✔️✔️SELECT FIRST_NAME, LAST_NAME
FROM OWNER
WHERE OWNER_NUM IN (SELECT OWNER_NUM FROM PROPERTY WHERE
BDRMS=2);
Task 4: List the first and last names of all owners who own a two-bedroom property.
Use the IN operator in your query - ✔️✔️SELECT OWNER.FIRST_NAME,
OWNER.LAST_NAME
FROM OWNER
WHERE OWNER.OWNER_NUM IN (SELECT OWNER_NUM FROM PROPERTY
WHERE BDRMS = 2)
Task 5: Repeat Task 4, but this time use the EXISTS operator in your query. -
✔️✔️SELECT FIRST_NAME, LAST_NAME
FROM OWNER o
WHERE EXISTS
(SELECT 1
FROM PROPERTY
WHERE OWNER_NUM=o.
OWNER_NUM AND BDRMS = 2);
Task 5: Repeat Task 4, but this time use the EXISTS operator in your query -
✔️✔️SELECT OWNER.FIRST_NAME, OWNER.LAST_NAME
FROM OWNER
