SQL Test : Sample Questions from Amazon Test (2/2)

 SQL questions are about monorail company. Database consists of tables for a monorail company.

 Below listed table definition with column name and one row of sample data.

1.      Table employee contains information of all the employee in the organization (including drivers of monorail).

 

| eid  | ename   | salary |

| 555  | Raheem  | 235    |

 

2. Table monorail Contains the information aboubt each monorail.

 

| rid   | rname      | running_range |

| 1234  | Speedster  | 454           |

 

3. Table route_info contains the description of route on which monorails used to travel.

 

 

| route_no   | origin      | distance | cost |

| 4456       | Gwalior     | Delhi    | 543  |

 

4. Table assignment contains the data about which monorail is driven by which employee. This can be a many-to-many relation.

 

| eid   | rid      |

| 101   | 4454     |

 

NOTE: Make all necessary assumptions.

Table structure repeated below for quick reference:

 

employee(eid,ename,salary)

monorail(rid,rname,running_range)

route_info(route_no,origin,destination,distance,cost)

assignment(eid,rid)

Table structures:

 

create table employee

(

eid int,

ename varchar2(50),

salary int

)

;

 

create table monorail

(

rid int,

rname varchar2(50),

running_range int

);

 

 

create table route_info

(

route_no int,

origin varchar2(50),

destination varchar2(50),

distance int,

cost int

);

 

 

create table assignment

(

eid int,

rid int

);

 

Problem 1: Underpaid Employee :

 

Find the names of employees (“ename”) whose salary is less than the price (“cost”) of cheapest route from ‘Bangalore’ to ‘Delhi’ (No need to consider route with connections).

Problem 2: one stop Route:

 

Find one stop from ‘Bangalore’ to ‘Delhi’. The route will need to make one stop transfer and don’t need to consider time and cost. Query should return two columns : first section route_no and second section route_no.

Problem 3: How many Monorails Does Each Drive:

 

Provide all employee names (“ename”) along with status on whether they are driving monorail or not (Y/N) and also the number of monorails driving (0 if they are not driving monorail).

Note: The format of output should be like below

Raheem N 0

Nusrat Y 3

……….

 

Problem 4: Cheapest route to destination:

 

List all the route destination along with their route_no of the cheapest route to that destination from any origin. Output should have three column: destination, route_no and cost of the route.

 

Problem 5: Top Paid Drivers:

 

List all the monorails along with the top 2 paid (salary ranked 1st or 2nd) drivers of each monorail. The output should have four columns: the name of the monorail (rname), the salary ranking in the drivers of same monorail, the name of the driver (ename), the driver’s salary (salary).

Solution 1: Underpaid Employee :

 

select ename from employee where salary < (select min(cost) from route_info where origin='Bangalore' and destination='Delhi');

Solution 2: one stop Route:

 

select a.route_no as first_section_route,b.route_no as sec_section_route

from route_info a,route_info b where a.origin='Bangalore'

and b.destination='Delhi' and a.destination=b.origin

Solution 3: How many Monorails Does Each Drive:

 

select emp.ename, case when xx.cnt is NULL then 'N' else 'Y' end, nvl(xx.cnt,0)

from employee emp left outer join

(

select eid,ename,nvl(count(1),0) as cnt from

(select e.eid,e.ename,a.rid from employee e inner join assignment a

on e.eid=a.eid) x

group by x.eid,x.ename

) xx

on emp.eid=xx.eid

 

Solution 4: Cheapest route to destination:

 

Select r.destination, r.route_no,r.cost from route_info r, (select destination,min(cost) as mr_cost from route_info group by destination) mr

where r.cost = mr_cost and r.destination=mr.destination

 

Solution 5: Top Paid Drivers:

 

select m.rname,x.salary_rank, x.ename, x.salary from

(

select a.rid, a.eid,e.ename,e.salary, rank() over (partition by a.rid order by e.salary desc) as salary_rank from assignment a inner join employee e on a.eid=e.eid

 ) X , monorail m

 where x.rid=m.rid and salary_rank<=2

 

Click on below link for Part 1.

 

For SQL Test : Sample Questions from Amazon Test (1/2)