نمونه سوالات SQL

--answer with NOT IN:
select * 
from Student
where Id not in ( select StudentId
                    from Enrollment )
                                
--answer with NOT EXISTS:
select * from Student S
where not exists ( select StudentId
                    from Enrollment E
                    where E.StudentId = S.Id )

--answer with NOT IN:
select * from Student
where Id not in
       (      select StudentId
             from Enrollment
             where Grade is not null   
       )
      
--answer with NOT EXISTS:
select * from Student S
where not exists
       (      select StudentId
             from Enrollment E
             where  E.StudentId = S.Id and Grade is not null
       )

--answer with NOT IN:
select * from Student S
where S.Id not in
       (
             select E.StudentId
             from Enrollment E
             where not (E.Grade between 12 and 18)
       )
--answer with NOT EXISTS:
select * from Student S
where not exists
       (     
             select E.StudentId
             from Enrollment E
             where  E.StudentId = S.Id and not (E.Grade between 12 and 18)
       )

--answer with NOT IN:
select * from Student S
where S.Id not in
       (      select E.StudentId
             from Enrollment E
             where E.Grade < 15
       )

select * from Teacher T
where not exists
       (
             select C.TeacherId
             from Course C
                    inner join Enrollment E on C.Id = E.CourseId
             where C.TeacherId = T.id
       )

select * from Teacher T
where not exists
       (
             select C.TeacherId
             from Course C
                    inner join Enrollment E on C.Id = E.CourseId
             where (C.TeacherId = T.id) and (E.Grade is not Null)
       )

select * from Teacher T
where T.id not in
       (
             select C.TeacherId from Course C
                    inner join Enrollment E on C.Id = E.CourseId
             where E.Grade < 10
       )

--answer without using WITH:
select C.TeacherId TId, avg(E.Grade) AvgG
from Course C
       inner join Enrollment E on C.Id = E.CourseId
group by C.TeacherId
having avg(E.Grade) =
             (
                    select max(AvgG)
                    from
                           (
                                  select C.TeacherId TId, avg(E.Grade) AvgG
                                 from Course C
                                        inner join Enrollment E on C.Id = E.CourseId
                                 group by C.TeacherId
                           ) AvgGrade
             )

--answer using WITH
with AvgGrade as
(
       select C.TeacherId TId, avg(E.Grade) AvgG
       from Course C
             inner join Enrollment E on C.Id = E.CourseId
       group by C.TeacherId
)

select A1.TId, A1.AvgG
from AvgGrade A1
where A1.AvgG = (select max(A2.AvgG) from AvgGrade A2)

with AvgGrade as
(
       select C.TermId, C.Id CId, avg(E.Grade) AvgG
       from Course C
             inner join Enrollment E on C.Id = E.CourseId
       group by C.TermId, C.Id
)

select A1.TermId, A1.AvgG
from AvgGrade A1
where A1.CId = 1
       and A1.AvgG =
             (
                    select max(A2.AvgG) from AvgGrade A2
                    where A2.TermId = A1.TermId
             )

                            select *
from Enrollment E
where E.Grade = (select max(Grade) from Enrollment)

with T as
(
       select S.Name StudentName, E.Grade
       from Enrollment E
             inner join Student S on E.StudentId = S.Id
       where S.Name like 'a%'
)

select StudentName, Grade
from T
where Grade = (select max(Grade) from T)

select C.TeacherId
from Enrollment E
       inner join Course C on E.CourseId = C.Id
       inner join Student S on E.StudentId = S.Id
group by C.TeacherId
having count(distinct S.Name) = 1

with AvgGrade as
(
       select C.TermId, C.Id CId, avg(E.Grade) AvgG
       from Course C
             inner join Enrollment E on C.Id = E.CourseId
       group by C.TermId, C.Id
)

select A1.TermId, A1.AvgG
from AvgGrade A1
where A1.CId = 1
       and A1.AvgG =
             (
                    select max(A2.AvgG) from AvgGrade A2
                    where A2.TermId = A1.TermId
             )

چون هر درس فقط یک استاد دارد می‌توان گروه بندی را فقط براساس کد درس انجام داد. بصورت زیر:

select T.Id
from Teacher T
where exists
       (
             select C.TeacherId
             from Enrollment E
                    inner join Course C on E.CourseId = C.Id
                    inner join Student S on E.StudentId = S.Id
             where C.TeacherId = T.Id
             group by C.Id
             having count(distinct S.Name) = 1
       )

select T.Id
from Teacher T
where Id in
       (
             select C.TeacherId
             from Enrollment E
                    inner join Course C on E.CourseId = C.Id
                    inner join Student S on E.StudentId = S.Id
             group by C.TeacherId, C.TermId
             having count(distinct S.Name) = 1
       )

select T.Id
from Teacher T
where Id not in
       (
             select C.TeacherId
             from Enrollment E
                    inner join Course C on E.CourseId = C.Id
                    inner join Student S on E.StudentId = S.Id
             group by C.TeacherId, C.TermId
             having count(distinct S.Name) > 1
       )

with S_maxT as
(
select E.StudentId SId, max(C.TermId) maxT
from Enrollment E
       inner join Course C on E.CourseId = C.Id
group by E.StudentId
)

select ST.SId, ST.maxT
from S_maxT ST
where  (
             select avg(E2.Grade)
             from Enrollment E2
                    inner join Course C2 on E2.CourseId = C2.Id
             where E2.StudentId = ST.SId and C2.TermId = ST.maxT
       ) > 15

select S.Id
from Student S
where S.Id in      
       (
             select E1.StudentId SId
             from Enrollment E1
             where E1.Grade =
                           (
                                 select max(E.Grade)
                                 from Enrollment E2
                                 where E1.CourseId = E2.CourseId
                           )
       )

select C.TeacherId, count(C.Id)
from Course C
group by TeacherId
having count(C.Id) > 3

select C.TeacherId, count(distinct C.TermId)
from Course C
group by TeacherId
having count(distinct C.TermId) > 3

select C.TeacherId, count(C.Id), count(distinct C.TermId)
from Course C
group by TeacherId
having count(C.Id) = count(distinct C.TermId)

-- Not Correct
select C.TermId
from Course C
       inner join Enrollment E on C.Id = E.CourseId
group by C.TermId
having count(E.StudentId) = sum(C.Capacity)

برای اینکار باید ظرفیت ترم را به کمک یک کوئری دیگر بدست آورد:

-- Correct
select C.TermId
from Course C
       inner join Enrollment E on C.Id = E.CourseId
group by C.TermId
having count(E.StudentId) =
       (
             select sum(C2.Capacity)
             from Course C2
             group by C2.TermId 
       )

select Id
from Teacher
where Id in
       (
             select C.teacherId
             from Course C
                    inner join Term on C.TermId = Term.Id
             where Term.EndDate > '1392/02/05'
       )