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)
