نمونه سوال sql پایگاه داده

در قسمت اول آموزش دستورات SQL دستورات پایه و مهم SQL را بررسی کردیم. در این نوشته با بررسی چند مثال دستورات آموزش داده شده را بهتر بررسی میکنیم. همانطور که در جلسه قبل آموزش گفتیم، از دیتابیس university که شامل جداول زیر است استفاده میکنیم:
جدول student (دانشجو)، clg(دانشکده یا بخش)، course (درس)، prof (استاد) و sec(گروه) است.

Student (s# , sname , city , clg#)
Clg(clg#, clgname , prof# , city)
Course(crs# , cname , unit , clg#)
Prof(prof# , pname , degree , office)
Sec(s# , crs#, prof# , term , score)

در جدول دانشجو اطلاعاتی نظیر شماره دانشجو ، نام دانشجو، شهر دانشجو و کد دانشکده وجود دارد.
در جدول دانشکده اطلاعاتی نظیر شماره دانشکده، نام دانشکده، کد رییس دانشکده و شهر مکان دانشکده وجود دارد.
در جدول course اطلاعاتی نظیر کد درس، نام درس، تعداد واحد درس، کد دانشکده ای که این درس را ارائه کرده است وجود دارد.
در جدول prof اطلاعاتی نظیر کد استاد، نام استاد، مدرک استاد و اتاق کار استاد وجود دارد.
در جدول sec که مهم‌ترین جدول دیتابیس است مشخص می‌شود که چه دانشجویی در چه درسی و با چه استادی در چه ترمی چه نمره ای گرفته است!

مثال 1- پرس‌وجویی بنویسید که نام اساتیدی که درس پایگاه داده را تدریس کرده‌اند را در خروجی نمایش دهد

select pname 
from prof inner join (select  *
			from sec inner join course
			on sec.crs# = course.crs# 
				and cname = 'پایگاه داده') as table1
on prof.prof# = table1.prof#

در مثال 10 آموزش دستورات SQL این مثال توضیح داده شده است.

مثال 2- پرس‌وجویی بنویسید که تعداد دروس دانشکده کامپیوتر را چاپ کند.

select count(crs#)
from course inner join clg
on course.clg# = clg.clg# and clgname = 'computer'

برای نوشتن این پرس‌وجو به دو جدول course و clg نیاز است که با استفاده از شرط #clg این دو جدول را پیوند میزنیم. همچنین شرط clgname=’computer’ را در شرط پیوند قید می‌کنیم.

مثال 3- پرس‌وجویی بنویسید که تعداد دروس 3 واحدی دانشکده کامپیوتر را نمایش دهد.

select count(crs#)
from course inner join clg
on course.clg# = clg.clg# 
	and clgname = 'computer' and unit = 3

شبیه به مثال 2 با این تفاوت که شرط unit=3 اضافه شده است

مثال 4- پرس‌وجویی بنویسید که لیستی از دانشجویانی که بیش از 100 واحد پاس کرده‌اند را در خروجی نمایش دهد

select sec.s# , sum(unit)
from student inner join(select * 
			from sec inner join course
			on sec.crs# = course.crs# 
			   and score>10) as table1
on student.s# = table1.s#
group by sec.s#
having sum(unit)>100
			

در این مثال به سه جدول student و sec و course نیاز است. در خط 2 تا 5 دو جدول sec و course را پیوند میزنیم و نام جدول حاصل table1 قرار میدهیم. و در ادامه دو جدول table1 و sec را پیوند می‌زنیم. جدول حاصل جدولی خواهد بود که در کنار هر سطر از جدول Sec اطلاعات درس (که تعداد واحد نیز شامل می‌شود) و اطلاعات دانشجو قرار دارد. حال بر اساس #s گروه بندی میکنیم تا دروسی که هر دانشجو پاس کرده است در یک گروه قرار گیرد. در نهایت با استفاده از شرط having دانشجویانی که بیش از 100 واحد پاس کرده اند در خروجی نمایش می‌دهد.

نکات:
1- همانطور که در جلسه قبل گفتیم هر صفتی که در group by ظاهر می‌شود در دستور select هم باید ظاهر شود
2- از آنجایی که در این مثال و در پیوند سه جدول دو صفت #s داریم (یکی برای جدول student و دیگری sec) حتما موقع استفاده از صفت #s باید دقیقا مشخص کنیم که با #s کدام جدول میخواهیم کار کنیم. به همین دلیل است که در خط 7 از #sec.s استفاده شده است.

مثال 5- پرس‌وجویی بنویسید که مجموع واحدهای گذرانده دانشجویانی با نام رضایی را در خروجی نمایش دهد

select sec.s# , sum(unit)
from student inner join(select * 
			from sec inner join course
			on sec.crs# = course.crs# 
			   and score>10) as table1
on student.s# = table1.s# and sname = 'رضایی'
group by sec.s#

مثال 6- پرس‌وجویی بنویسید که مشخصات اساتیدی که در ترم اول 97 بیش از 20 واحد تدریس کرده اند را نمایش دهد.

select *
from prof
where prof# in (select prof#
		from sec inner join course
		on sec.crs# = course.crs#
		group by prof#
		having count(unit) > 20) 

مثال 7 – مشخصات اساتیدی که هیچ درس یک واحدی تدریس نکرده اند

select *
from prof
where prof# not in (select prof#
			from course inner join sec
			on course.crs# = sec.crs# and unit = 1)
			

در این مثال ابتدا اساتیدی که درس یک واحدی ارائه کرده‌اند را پیدا می‍کنیم و از کل اساتید کم می‌کنیم.

مثال 8- رییس دانشکده‌هایی که هیچ دانشجوی افتاده ای ندارند.

select pname.*
from prof inner join clg
on prof.prof# = clg.prof# 
      and prof# not in (select prof#
			from sec
			where score <10)

از پیوند دو جدول prof و clg بر اساس #prof جدولی بدست می‌آید که مشخصات رییس دانشکده ها را نمایش می‌دهد. برای اینکه پرس‌وجوی خواسته شده در مثال را پیاده کنیم، اساتیدی که دانشجوی افتاده دارند را از کل رییس دانشگده ها کم می کنیم. به همین دلیل در خط 4 تا 6 زیر پرس و جویی نوشته شده است که اساتیدی که دانشجوی افتاده دارند را بر میگرداند.

مثال 9- مشخصات دانشکده هایی که بیشتر از 100 دانشجو دارند.

select *
from clg
where clg# in ( select clg#
		from student
		group by clg#
		having count(s#) > 100

مثال 10- مشخصات دانشکده هایی که هیچ درس یک واحدی ارائه نکرده اند.

select *
from clg
where clg# not in (select clg#
			from course inner join sec
			on course.crs# = sec.crs# and unit = 1)
			

مثال 11- مشخصات دانشجویانی که همه دروس دانشکده برق را گذرانده اند.

select *
from student
where s# in (select s#
		from sec inner join course
		on sec.crs# = course.crs# and score>10
		and clg# in (select clg# from clg where clgname='برق')
		group by s#
		having count(sec.crs#) = (select count(sec.crs#)
						from course inner join sec
						on sec.crs# = course.crs# 
		)				and clg# in (select clg# from clg where clgname='برق'))

مثال 12- مشخصات دانشجویانی که همه دروس دانشکده خود را گذرانده اند.

select *
from student
where s# in (select s#
		from sec inner join course
		on sec.crs# = course.crs# and score>10
		and clg# in
		(select clg# from clg where clgname='کامپیوتر' and clg#=student.clg# )
		group by s#
		having count(sec.crs#) = (select count(sec.crs#)
						from course inner join sec
						on sec.crs# = course.crs# 
		)				and clg# in 
						(select clg# from clg where clgname='برق' clg#=student.clg# )
					)