Write the SQL queries that accomplish the following tasks in the HAFH Realty Company Property Management Database:

The complete question is:

Write the SQL queries that accomplish the following tasks in the HAFH Realty Company Property Management Database:

a. Display the SMemberID and SMemberName for all staff members.

b. Display the CCID, CCName, and CCIndustry for all corporate clients.

c. Display the BuildingID, BNoOfFloors, and the manager’s MFName and MLName for all buildings.

d. Display the MFName, MLName, MSalary, MBDate, and number of buildings that the manager manages for all managers with a salary less than $55,000.

e. Display the BuildingID and AptNo, for all apartments leased by the corporate client WindyCT.

f. Display the InsID and InsName for all inspectors whose next inspection is scheduled after 1-JAN-2014. Do not display the same information more than once.

g. Display the SMemberID and SMemberName of staff members cleaning apartments rented by corporate clients whose corporate location is Chicago. Do not display the same information more than once.

h. Display the CCName of the client and the CCName of the client who referred it, for every client referred by a client in the music industry.

i. Display the BuildingID, AptNo, and ANoOfBedrooms for all apartments that are not leased.

Also a schema of the HAFH database is attached.

Using SQL's SELECT, FROM, WHERE syntax, find below the queries for each question.

a.

SELECT SMemberID , SMemberName  

FROM staffmember

b.

SELECT CCID, CCName, CCIndustry

FROM corpclient

c.

SELECT b.BuildingID, b.BNoOfFloors, m.MFName, m.MLName

FROM building b, manager m

WHERE b.ManagerID = m.ManagerID

d.  

SELECT m.MFName, m.MLName, m.MSalary, m.MBDate, count(*) as buildings

FROM building b, manager m

WHERE m.MSalary<55000

AND b.ManagerID = m.ManagerID

GROUP BY m.ManagerID

e.

SELECT b.BuildingID, a.AptNo

FROM building b, apartment a, corpclient c

WHERE c.CCName = "WindyCT"

AND c.CCID = a.CCID

AND a.BuildingID = b.BuildingID

f.

SELECT DISTINCT i.InsID, i.InsName  

FROM inspector i, inspecting x

WHERE i.InsID = x.InsID

AND x.DateNext > "2014-01-01"

g.

SELECT DISTINCT s.SMemberID, s.SMemberName  

FROM staffmember s, cleaning c, apartment a, corpclient cc

WHERE s.SmemberID = c.SmemberID

AND c.AptNo = a.AptNo

AND a.CCID = cc.CCID

AND cc.CCLocation = "Chicago"

h.

SELECT cc1.CCName, cc2.CCName  

FROM corpclient cc1, corpclient cc2

WHERE cc1.CCIDReferencedBy = cc2.CCID  

AND cc2.CCIndustry = "Music"

i.

SELECT a.BuildingID, a.AptNo, a.ANoOfBedrooms

FROM apartment a

WHERE a.CCID NOT IN (SELECT c.CCID FROM corpclient c)

answer:

ab-cd+c=a

step-by-step explanation: