Amazon Interview Report
- 0of 0 votes
AnswersSQL Questions & Answers ,and followup questions
- krish June 09, 2017 in United States
given schema
Tables:
Residents:
resident_ID (num)
Apartment Number
Name
...etc
Vehicles:
vehicle_ID (num)
(FK): Resident_ID (num)
License Plate
Vehicle Size ("small", "large", "motorcycle"...)
Get all the residents who has vehicle size large
SELECT *
FROM amazon_residents res
WHERE EXISTS
( SELECT 1
FROM amazon_vehicles veh
WHERE veh.residentid = res.residentid
AND veh.type = 'large' );| Report Duplicate | Flag | PURGE
Amazon SDE-2 - -1of 1 vote
AnswersEnhancements to schema by adding another table lot
- krish June 09, 2017 in United States
SELECT *
FROM Amazon_lot p,
amazon_vehicles v
WHERE p.lotid= 1
AND p.lotid = v.lotid
AND v.vehicleid = 1;
-- was struck for a moment, Interviewer helped me with hint
SELECT count(1)
FROM amazon_residents r,
Amazon_lot l,
amazon_vehicles v
WHERE r.residentid = l.residentid
AND l.lotid = v.lotid
AND r.residentid = v.residentid
AND (v.vehicleid = 1
OR v.lotid = 2);| Report Duplicate | Flag | PURGE
Amazon SDE-2