Cracking Coding Interview In SQL

Apartments
AptID	int
UnitNumber	Varchar(10)
BuildingID	int

Buildings
BuildingID	int
ComplexID	int
BuildingName	Varchar(100)
Address	Varchar(500)

Requests
RequestID	int
Status	Varchar(100)
AptID	int
Description	Varchar(500)

Complexes
ComplexeID	int
ComplexeName	Varchar(100)

AptTenants
TenantID	int
AptID	int

Tenants
TenantID	int
TenantName	Varchar(100)

Q.1 Write a SQL query to get a list of tenants who are renting more than one apartment.

Solution :- To implement this we can use HAVING and GROUP BY clauses and then INNER JOIN with tenants.

NOTE:-Whenever you write a GROUP BY clause ( in real life). Ensure that anything in the select clause is either a group function or contained within the GROUP BY clause.

Query:

The query given below is the solution to the above question

SELECT TenantName 
FROM Tenants 
INNER JOIN 
(SELECT TenantlD FROM AptTenants GROUP BY TenantlD HAVING COUNT(*) > 1) C 
ON Tenants.TenantID = C.TenantlD

Q.2 Write a SQL query to get a list of all buildings and the number of open requests (Requests in which status equals to 'Open').

Solution :- This problem uses a direct join of requests and apartments to obtain the list building IDs and the number of open requests. Once we have this list, we again join it to the buildings table.

Query:

The query given below is the solution to the above question

SELECT BuildingNne, ISNULL(Count, 0) as 'Count'
FROM Buildings 
LEFT JOIN 
(SELECT Apartments.BuildingID, count(*) as 'Count' 
FROM Requests INNER JOIN Apartments 
ON Requests.AptID = Apartments.AptlD 
WHERE Requests.Status = 'Open' 
GROUP BY Apartments.BuildingID) ReqCounts 
ON ReqCounts.BuildingID = Buildings.BuildingID 

Q.3 Building #11 is undergoing a major renovation.Implement a query to close all requests from apartments in this building.

Solution :- UPDATE queries, such as SELECT queries, can have WHERE clauses.To implement this query, we get a list of all apartment IDs within # 11 and a list of update requests from those apartment.

Query:

The query given below is the solution to the above question

UPDATE Requests 
SET Status 'Closed' 
WHERE AptlD IN (SELECT AptID FROM Apartments WHERE BuildingID = 1 

---

Visit :

---

Discussion