یک بانک اطلاعاتی ثبت نام دانش آموزان را در نظر بگیرید:
- دانش آموز: کد دانش آموز، نام دانش آموز
Student(Id int not null, Name varchar(50) not null)
- استاد : کد استاد، نام استاد
Teacher(Id int not null, Name varchar(50) not null)
- ترم : کد ترم، تاریخ شروع، تاریخ پایان
Term(Id int not null, BeginDate varchar(10) not null, EndDate varchar(10) not null)
- درس : کد درس، نام درس، کد ترم، کد استاد درس، ظرفیت درس
Course(Id int not null, Name varchar(50) not null, TermId int not null, TeacherId int not null, Capacity int not null)\
- ثبت نام :کد ثبت نام، کد درس، کد دانش آموز، نمره دانش آموز
Enrollment (Id int not null, CourseId int not null, StudentId int not null, Grade float null)
در جداول بالا، زیر کلیدهای اصلی خط کشیده شده است.
کلیدهای خارجی با فلش نشان داده شدهاند.
جدول Course نشان میدهد که هر درس در کدام ترم و توسط کدام استاد ارائه شده است و ظرفیت آن درس چند نفر هست.
جدول Enrollment نشان میدهد که هر دانش آموز در چه درسهایی ثبت نام کرده و نمره نهایی آن دانش آموز در آن درس چند شده است.
سوالات:
- دانش آموزانی که در هیچ درسی ثبت نام نکردهاند.
- دانش آموزانی که هیچ نمرهای ندارند.
- دانش آموزانی که تمام نمرات آنها بین 12 و 18 بوده است.
- دانش آموزانی که همیشه نمره 15 یا بالای 15 گرفتهاند.
- استادانی که هیچ دانش آموزی ندارند.
- استادانی که به هیچ دانش آموزی هیچ نمرهای ندادهاند.
- استادانی که دانش آموز ردی نداشتهاند. (نمره ردی: نمره زیر 10)
- استادانی که بیشترین میانگین نمرات را داشتهاند.
- ترمهایی که درس Id=1 بیشترین میانگین نمره را داشته است.
- لیست بالاترین نمرات.
- لیست بالاترین نمرات برای دانش آموزانی که اسم آنها با حرف ‘a’ شروع میشود.
- استادانی که همه دانش آموزان آنها همنام بودهاند. (با Exists یا گروه بندی)
- استادانی که حداقل در یک درس همه دانش آموزان آنها همنام بودهاند.
- استادانی که حداقل در یک ترم همه دانش آموزان آنها همنام بودهاند.
- استادانی که در هر ترمی همه دانش آموزان آنها همنام بودهاند. (مثلا ترم1 همه علی، ترم2 همه رضا و …)
- دانش آموزانی که معدل آخرین ترم آنها بالاتر از 15 هست.
- دانش آموزانی که حداقل یک بار بیشترین نمره درس را گرفتهاند. (بدون تکرار)
- اساتیدی که بیش از 3 درس تدریس داشتهاند.
- اساتیدی که در بیش از 3 ترم تدریس کردهاند.
- اساتیدی که تعداد ترمهایشان با تعداد درسهایشان برابرست.
- ترمهایی که تعداد دانش آموزان آنها با ظرفیت آنها (مجموع ظرفیت درسهای آن ترم) برابرست.
- اساتیدی که بعد از تاریخ ‘1392/02/05’ درس تمام نشده دارند.
جوابها:
- دانش آموزانی که در هیچ درسی ثبت نام نکردهاند.توضیح:دانش آموزانی که در هیچ درسی ثبت نام نکردهاند یعنی برای آنها هیچ رکوردی در جدول
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' )