Cracking Coding Interview In SQL

SQL coding interviews can be difficult. Do not get caught. These are some of the most common SQL interview query for amazon, google, and microsoft. Learn these and you will be more prepared for your SQL interview.



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


* You must be logged in to add comment.