Frequently Used database interview questions

Answer:

-- 1
Select * from students where Sid in
(Select r_sid from sresult where r_cid = (select CID from course where cname = 'tax basis '))
-- 2
Select * from students where Sid in
(Select r_sid from sresult where r_cid = 2)
-- 3
Select * from students where sid not in
(Select r_sid from sresult where r_cid = 5)
-- 4
Select * From DBO. Students where Sid in
(
Select r_sid from sresult group by r_sid having count (*) =
(Select count (*) from course)
)
-- 5
Select count (distinct r_sid) from sresult
-- Select count (*) from
-- (Select r_sid from sresult
-- Group by r_sid)
-- 6

Select * From DBO. Students where Sid in
(
Select r_sid from sresult group by r_sid having count (*)> 5
)

 

Answer:

-- 1
Select * from students where st_id in
(Select r_stid from results where r_tid not in
(Select t_id from DBO. Teachers where t_name = 'lilim '))
-- 2
Select a. st_name, B. fenshu from students,
(Select r_stid, AVG (r_fenshi) as fenshu from results where r_fenshi <60 group by r_stid having count (*)> = 2) B
Where a. st_id = B. r_stid
-- 3

Select * from students where st_id in
(
Select r_stid from results where r_tid in
(Select t_id from teachers where t_lesson = 'mate' or t_lesson = 'English ')
Group by r_stid having count (*) = 2
)
-- 4
Select r_stid from results
Where r_fenshi>
(Select r_fenshi from DBO. Results where r_tid = 't001' and r_stid = 'st002 ')
And r_tid = 't001'

-- 5
-- Select * From DBO. Students
-- Select * From DBO. Teachers
Select * from (select * From results where r_tid = 't001') A, (select * From results where r_tid = 't002') B
Where
A. r_stid = B. r_stid
And a. r_fenshi> B. r_fenshi

-- Select * From results where (r_tid = 't001' or r_tid = 't002') and r_stid in ('st001', 'st002', 'st002') order by r_stid
-- Select * From results where r_tid = 't002 'order by r_stid