سوالات SQL

دانش آموزانی که در هیچ درسی ثبت نام نکرده اند.
توضیح:
دانش آموزانی که در هیچ درسی ثبت نام نکرده اند یعنی برای آنها هیچ رکوردی در جدول Enrollment ثبت نشده است:

--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 )
                        

دانش آموزانی که هیچ نمره ای ندارند.
توضیح:
ممکن است دانش آموزی در جدول Enrollment باشد ولی هیچ نمره ای نداشته باشد. در اینحالت جواب مسئله بصورت زیرست:

--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
       )
                        

دانش آموزانی که تمام نمرات آنها بین 12 و 18 بوده است.
توضیح:
اگر بخواهیم شرط را بصورت (نمره بین 12 و 18 باشد) کنترل کنیم آنوقت باید همه نمرات یک دانش آموز را چک کنیم تا بین 12 و 18 باشد و این کار سخت است. بجای آن لیست کسانیکه حداقل یک نمره بالای 18 یا کمتر از 12 دارند را پیدا می کنیم و بقیه که در این لیست نیستند جواب مسئله اند. بنابراین این سوال معادلست با دانش آموزانی که حتی یک نمره هم خارج این بازه ندارند (بیشتر از 18 یا کمتر از 12):

--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)
       )

دانش آموزانی که همیشه نمره 15 یا بالای 15 گرفته اند. همیشه= همه نمراتشان نه بازه تاریخی
دانش آموزانی که هیچ وقت نمره زیر 15 نگرفته اند. هیچ وقت= هیچ نمره ای. نه بازه تاریخی

--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)
       )

استادانی که دانش آموز ردی نداشته اند. (نمره ردی: نمره زیر 10) = هیچ نمره ای زیر 10 نداشته اند.
استادانی که در هیچ درسی دانش آموز ردی نداشته اند. (همان سوال قبلی است.) شامل کلمات کلیدی اضافی است.
استادانی که در هیچ ترمی دانش آموز ردی نداشته اند. (همان سوال قبلی است.) شامل کلمات کلیدی اضافی است. هیچ ترمی تاثیری ندارد.

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
       )

استادانی که بیشترین میانگین نمرات را داشته اند.
توضیح:
در اینجا منظور این نیست که لیست استادان را بر اساس میانگین نمراتشان از بیشترین تا کمترین مرتب کنیم و همه را نشان دهیم بلکه منظور لیست استادانی هست که دقیقا بیشترین میانگین نمرات را داشته اند یعنی میانگین نمراتشان Max هست و دقت شود که ممکنست میانگین نمرات چند استاد برابر Max شود..

--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)

ترم هایی که درس Id=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 *
from Enrollment E
where E.Grade = (select max(Grade) from Enrollment)

لیست بالاترین نمرات برای دانش آموزانی که اسم آنها با حرف 'a' شروع می شود.

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)

استادانی که همه دانش آموزان آنها همنام بوده اند. (با 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
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
       )

استادانی که در هر ترمی همه دانش آموزان آنها همنام بوده اند. (مثلا ترم1 همه علی، ترم2 همه رضا و ...)

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
       )

دانش آموزانی که معدل آخرین ترم آنها بالاتر از 15 هست.

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
                           )
       )

اساتیدی که بیش از 3 درس تدریس داشته اند.

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

اساتیدی که در بیش از 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)

ترم هایی که تعداد دانش آموزان آنها با ظرفیت آنها (مجموع ظرفیت درس های آن ترم) برابرست.
توضیح:
توجه کنید که در join دو جدول، ظرفیت درس تکرار می شود. بنابراین sum(C.Capacity) در کوئری زیر درست حساب نمی شود. حتی استفاده از sum(distinct C.Capacity) درست نیست زیرا اگر تکرارترم مربوط به یک درس باشد نباید ظرفیت آن دوباره جمع شود ولی اگر تکرار ترم مربوط به درس های مختلف باشد باید ظرفیت آن جمع شود.

-- 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 
       )

اساتیدی که بعد از تاریخ '1392/02/05' درس تمام نشده دارند.
توضیحاتی درباره تاریخ های شمسی با فرمت رشته ای:
تاریخ ها شمسی رشته ای را می توان مستقیما بصورت رشته ای با هم مقایسه کرد بشرط آنکه تعداد ارقام و فرمت نوشتن آنها یکسان بوده و ترتیب سال-ماه-روز آنها بصورت y-m-d باشد. مثلا همه بصورت 'yyyy/mm/dd' نوشته شوند یا همه بصورت 'yyyymmdd'

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'
       )