Sub Queries
MCQ - 1
MCQ - 2
SQL query - 1
select qty from sp where sno in (select sno from supplier where sname in ('anjali' , 'rachit'));
select sname, city from supplier where sno in (select sno from sp where pno in (1,5));
SQL query - 3
select colour from product where pno in (select pno from sp where sno in (1,3,6));
SQL query - 4
SELECT colour FROM product WHERE pno IN
(SELECT pno FROM sp WHERE sno IN
(SELECT sno FROM supplier WHERE sname NOT IN('rachit', 'kashish')));
SQL query - 5
SELECT sname, age FROM sailors WHERE age IN (SELECT MAX(age) FROM sailors );
MCQ - 3
MCQ - 4
SQL query - 6
select sname from supplier where sno =
(select sno from sp where pno = (select pno from product where pname = 'pencil') and qty > 10);
SQL query - 7
select empname from employee where salary > (select salary from employee where empname = 'Monica Geller');
SQL query - 8
select empname, salary, deptcode, job from employee where job = (select job from employee where empname = 'Phoebe Buffay');
Sub query - 9
select empname, deptcode from employee where EXISTS (select salary from employee where salary > 4000);
SQL query - 10
select bname, color from boats where bid in (select bid from reserves where sid in
(select sid from sailors where age in
(select min(age) from sailors)));
SQL query - 11
select sid, daytook from reserves where sid in (select sid from sailors where rating in
(select max(rating) from sailors));
MCQ - 5
MCQ - 6
MCQ - 7
SQL query - 12
select pname, colour, 20*weight as w from product where colour='red' and weight <10;
SQL query - 13
select empcode ,empname,salary,12*salary as A_sal from employee where 12*salary > 30000;
MCQ - 8
Ans : D
SQL query - 14
select empcode, empname, salary from employee e where salary >
(select AVG(salary) from employee where e.empname like '%e%' );
SQL query - 15
SELECT s.sid, s.sname
FROM sailors s
JOIN reserves r ON s.sid = r.sid
JOIN boats b ON r.bid = b.bid
GROUP BY s.sid, s.sname
HAVING COUNT(DISTINCT r.bid) >= 2
order by s.sname desc;
SQL query - 16
SELECT b.color, r.sid, r.bid
FROM sailors s
join reserves r on s.sid=r.sid
JOIN boats b ON r.bid = b.bid
WHERE s.rating = (
SELECT MAX(rating)
FROM sailors
WHERE rating < (SELECT MAX(rating) FROM sailors)
)
order by r.bid desc;
SQL query - 17
select s.sid, b.bid, s.sname from sailors s join reserves r on r.sid=s.sid
join boats b on b.bid=r.bid
where s.rating > (select AVG(rating) from sailors where r.sid=s.sid);
SQL query - 18
select s.sid, b.bid, s.sname, b.color from sailors s join reserves r on r.sid=s.sid
join boats b on b.bid=r.bid
where s.rating < (select AVG(rating) from sailors where r.sid=s.sid);
MCQ - 9
