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

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

یک بانک اطلاعاتی ثبت نام دانش آموزان را در نظر بگیرید:

  • دانش آموز: کد دانش آموز، نام دانش آموز
    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 نشان می‌دهد که هر دانش آموز در چه درس‌هایی ثبت نام کرده و نمره نهایی آن دانش آموز در آن درس چند شده است.

سوالات:

  1. دانش آموزانی که در هیچ درسی ثبت نام نکرده‌اند.
  2. دانش آموزانی که هیچ نمره‌ای ندارند.
  3. دانش آموزانی که تمام نمرات آن‌ها بین 12 و 18 بوده است.
  4. دانش آموزانی که همیشه نمره 15 یا بالای 15 گرفته‌اند.
  5. استادانی که هیچ دانش آموزی ندارند.
  6. استادانی که به هیچ دانش آموزی هیچ نمره‌ای نداده‌اند.
  7. استادانی که دانش آموز ردی نداشته‌اند. (نمره ردی: نمره زیر 10)
  8. استادانی که بیش‌ترین میانگین نمرات را داشته‌اند.
  9. ترم‌هایی که درس Id=1 بیش‌ترین میانگین نمره را داشته است.
  10. لیست بالاترین نمرات.
  11. لیست بالاترین نمرات برای دانش آموزانی که اسم آن‌ها با حرف ‘a’ شروع می‌شود.
  12. استادانی که همه دانش آموزان آن‌ها همنام بوده‌اند. (با Exists یا گروه بندی)
  13. استادانی که حداقل در یک درس همه دانش آموزان آن‌ها همنام بوده‌اند.
  14. استادانی که حداقل در یک ترم همه دانش آموزان آن‌ها همنام بوده‌اند.
  15. استادانی که در هر ترمی همه دانش آموزان آن‌ها همنام بوده‌اند. (مثلا ترم1 همه علی، ترم2 همه رضا و …)
  16. دانش آموزانی که معدل آخرین ترم آن‌ها بالاتر از 15 هست.
  17. دانش آموزانی که حداقل یک بار بیش‌ترین نمره درس را گرفته‌اند. (بدون تکرار)
  18. اساتیدی که بیش از 3 درس تدریس داشته‌اند.
  19. اساتیدی که در بیش از 3 ترم تدریس کرده‌اند.
  20. اساتیدی که تعداد ترم‌هایشان با تعداد درس‌هایشان برابرست.
  21. ترم‌هایی که تعداد دانش آموزان آن‌ها با ظرفیت آن‌ها (مجموع ظرفیت درس‌های آن ترم) برابرست.
  22. اساتیدی که بعد از تاریخ ‘1392/02/05’ درس تمام نشده دارند.

 

 

 

جواب‌ها:

  1. دانش آموزانی که در هیچ درسی ثبت نام نکرده‌اند.توضیح:دانش آموزانی که در هیچ درسی ثبت نام نکرده‌اند یعنی برای آن‌ها هیچ رکوردی در جدول
    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 )
    
  2. دانش آموزانی که هیچ نمره‌ای ندارند.توضیح:ممکن است دانش آموزی در جدول 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
           )
    
  3. دانش آموزانی که تمام نمرات آن‌ها بین 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)
           )
    
  4. دانش آموزانی که همیشه نمره 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
           )
    
  5. استادانی که هیچ دانش آموزی ندارند.
    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
           )
    
  6. استادانی که به هیچ دانش آموزی هیچ نمره‌ای نداده‌‍اند.(در اینجا هیچ دانش آموزی کلمه اضافی است و تاثیری ندارد چون استادی که به هیچ دانش آموزی نمره‌ای نداده یعنی اصلا نمره‌ای نداده)
    معادل: استادانی که هیچ نمره‌ای ندارند.
    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)
           )
    
  7. استادانی که دانش آموز ردی نداشته‌اند. (نمره ردی: نمره زیر 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
           )
    
  8. استادانی که بیش‌ترین میانگین نمرات را داشته‌اند.توضیح:در اینجا منظور این نیست که لیست استادان را بر اساس میانگین نمراتشان از بیش‌ترین تا کمترین مرتب کنیم و
    همه را نشان دهیم بلکه منظور لیست استادانی هست که دقیقا بیش‌ترین میانگین نمرات را داشته‌اند یعنی میانگین نمراتشان
    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)
    
  9. ترم‌هایی که درس 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
                 )
    
  10. لیست بالاترین نمرات.
                                select *
    from Enrollment E
    where E.Grade = (select max(Grade) from Enrollment)
    
  11. لیست بالاترین نمرات برای دانش آموزانی که اسم آن‌ها با حرف ‘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)
    
  12. استادانی که همه دانش آموزان آن‌ها همنام بوده‌اند. (با 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
    
  13. استادانی که حداقل در یک درس همه دانش آموزان آن‌ها همنام بوده‌اند.
    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
           )
    
  14. استادانی که حداقل در یک ترم همه دانش آموزان آن‌ها همنام بوده‌اند.
    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
           )
    
  15. استادانی که در هر ترمی همه دانش آموزان آن‌ها همنام بوده‌اند. (مثلا ترم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
           )
    
  16. دانش آموزانی که معدل آخرین ترم آن‌ها بالاتر از 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
    
  17. دانش آموزانی که حداقل یک بار بیش‌ترین نمره درس را گرفته‌اند. (بدون تکرار)
    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
                               )
           )
    
  18. اساتیدی که بیش از 3 درس تدریس داشته‌اند.
    select C.TeacherId, count(C.Id)
    from Course C
    group by TeacherId
    having count(C.Id) > 3
    
  19. اساتیدی که در بیش از 3 ترم تدریس کرده‌اند.توضیح: به رکوردهای تکراری توجه داشته باشید:
    select C.TeacherId, count(distinct C.TermId)
    from Course C
    group by TeacherId
    having count(distinct C.TermId) > 3
    
  20. اساتیدی که تعداد ترم هایشان با تعداد درس‌هایشان برابرست.اساتیدی که در هیچ ترمی بیش از یک درس ارائه نکرده‌اند.توضیح: به رکوردهای تکراری ترم برای یک استاد (در صورتیکه آن استاد در یک ترم بیش از یک درس ارائه کرده باشد) توجه داشته باشید:
    select C.TeacherId, count(C.Id), count(distinct C.TermId)
    from Course C
    group by TeacherId
    having count(C.Id) = count(distinct C.TermId)
    
  21. ترم‌هایی که تعداد دانش آموزان آن‌ها با ظرفیت آن‌ها (مجموع ظرفیت درس‌های آن ترم) برابرست.توضیح:توجه کنید که در 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 
           )
    
  22. اساتیدی که بعد از تاریخ ‘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'
           )
    
یک پاسخ بنویسید

نشانی ایمیل شما منتشر نخواهد شد.فیلد های مورد نیاز علامت گذاری شده اند *

4 + 19 =