/*create database productSupplier
create table [P]
(  p# int not null primary key,
type nvarchar(20)  null,
color nvarchar(20)  null,
pcity nvarchar(20)  null
);

create table [S]
(  s# int not null primary key,
sname nvarchar(20) not null,
scity nvarchar(20)  null,
status int not null
);

create table [SP]
(s# int not null references S(s#),
p# int not null,
qty int null,
primary key(s#,p#),
foreign key (p#) references P(p#)

);


alter table [SP]
add [date] datetime not null default (getdate())


drop table p

create table [P]
(  p# int not null primary key,
type nvarchar(20)  null,
color nvarchar(20)  null,
pcity nvarchar(20)  null
);

alter table [SP]
add constraint sp_p foreign key (p#) references  P(p#)


create table [Z]
(  z# int not null identity (5,10) primary key,
type nvarchar(20)  null,
[avg] int unique 

);*/


--کد با مقادیر پیش فرض و identity
/*create database ProductSupplier
create table [P]
(  p# int not null IDENTITY(1,1) primary key,
type nvarchar(20)  null,
color nvarchar(20)  null,
pcity nvarchar(20)  null default 'Isfahan'
);
create table [S]
(  s# int not null IDENTITY(1,1) primary key,
sname nvarchar(20) not null,
scity nvarchar(20)  null,
status int null,
CHECK (status>=1)
);
create table [SP]
(s# int not null references S(s#),
p# int not null references P(p#),
qty int null,
[date] datetime null default (getdate()),
primary key(s#,p#) 

);*/
------------------------------------------------------------

/*alter table [Z]
add constraint ck9 check (type in ('r','s','d'))

insert into Z (type) values (N'مس')

insert into Z (type,[avg]) values (N'مس', 14)

insert into Z values (N'مس', 14)

insert into Z values (N'مس', null , getdate())

insert into Z values (N'مس', 14) , (N'مس', 16)*/

----------------------------------------------------
/*create database Education
create table CLG
(	 clg# int not null IDENTITY(1,1) primary key,
	clgname nvarchar(20)  null,
	city nvarchar(20)  null default 'Semnan',
	p# int null );

create table [STUD]
( 	 s# int not null IDENTITY(1,1) primary key,
	sname nvarchar(20)  null,
	city nvarchar(20)  null,
	[avg] float null,
	clg# int not null references CLG(clg#),
	CHECK ([avg]>=0 AND [avg]<=20)   );

create table [PROF]
( 	 p# int not null IDENTITY(1,1) primary key,
	pname nvarchar(20)  null,
	grade nvarchar(20)  null,
	clg# int not null references CLG(clg#),
	CHECK (grade IN (N'مربی', N'استادیار', N'دانشیار', N'استاد کامل'))    );

create table [CRS]
( 	 c# int not null IDENTITY(1,1) primary key,
	cname nvarchar(20)  null,
	unit int  null,
	clg# int not null references CLG(clg#),
	CHECK (unit>=1 AND unit<=4)    );

create table [REG]
( 	term int not null,
	score float null,
	s# int not null references STUD(s#),
	c# int not null references CRS(c#),
	p# int null references PROF(p#),
	primary key(s#,c#,term),	
	CHECK (score>=0 AND score<=20));
alter table [CLG]
add constraint clg_prof foreign key (p#) references  PROF(p#)*/


------------------------------------------------------------------
/*SELECT TOP (3) *
  FROM SP
  ORDER BY qty desc;

SELECT TOP (3) s#
  FROM stud
  ORDER BY [avg] desc;

SELECT * 
  FROM SP
  where qty BETWEEN 20 and 30

SELECT * 
  FROM SP
  where qty in (12,22,30)
SELECT * 
  FROM SP
  where qty is null

SELECT *
  FROM s
  where scity like N'%ن'
SELECT *
  FROM s
  where scity like N'____ن'

SELECT *
  FROM s
  where sname like N'%ز%'


--------------------------------------------


/*select  * from p
cross join s*/


--------------------------------------------
--left outer join


--مشخصات تمام دانشکده ها و در صورت وجود (در صورت null نبودن p# در جدول CLG)، مشخصات رییس دانشکده
/*SELECT *     
FROM clg  
LEFT OUTER JOIN prof ON prof.p# = clg.p#*/

/*SELECT *     
FROM clg  
inner JOIN prof ON prof.p# = clg.p#*/





-- مشخصات تمام دروس و در صورت وجود  (null نبودن clg# در جدول CRS)، مشخصات دانشکده ی آنها
/*SELECT  *          
FROM  crs                   
LEFT OUTER JOIN   
    clg ON clg.clg# = crs.clg#  */





--برای تمام دانشجویان و در صورت وجود مشخصات دانشکده آنها
--با left join 
/*SELECT  *          
FROM stud                   
LEFT OUTER JOIN clg ON clg.clg# = stud.clg# */

---------------------------------------------------------
--join

--شماره تهیه کنندگانی که قطعه ای از جنس مس تهیه کرده اند
/*select s#
from sp
inner join p on sp.p#=p.p#
where [type]=N'مس'

select s# from sp
where p# in(select p# from p where [type]=N'مس')

select s# from sp
where exists(select p# from p
			where [type]=N'مس')*/

--نام دانشجویانی که با استادی با مرتبه دانشیار درس داشته اند
/*select sname from stud
inner join reg on stud.s#=reg.s#
inner join prof on reg.p#=prof.p#
where grade=N'دانشیار'

select sname from stud
where s# in( select s# from reg
             where p# in (select p# from prof
						  where grade=N'دانشیار'))*/

--نام روسای دانشکده هایی که درسی سه واحدی تدریس کرده اند
/*select  distinct pname from prof
inner join clg on clg.p#=prof.p#
inner join reg on reg.p#=clg.p#
inner join crs on crs.c#=reg.c#
where unit='3'
select  distinct pname from prof
where p# in(select p# from clg
            where p# in(select p# from reg
			            where c# in (select c# from crs
						             where unit='3')))*/

-------------------------------------------------------------
--نام هایی که یا نام استاد است یا نام دانشجو
/*select pname from prof
union 
select sname from stud*/

/*select pname from prof
union all
select sname from stud*/

/*select pname from prof
intersect
select sname from stud*/

--نام های که نام استاد است ولی نام دانشجو نیست
/*select pname from prof
except
select sname from stud*/

--بیشترین نمره کسب شده توسط دانشجو
--select max(score) from reg

--مجموع تعداد واحد اخذ شده توسط دانشجو s1
/*select sum(unit)
from crs
inner join reg on reg.c#=crs.c#
where s#='1'*/

--محاسبه معدل کل دانشجوی s1
/*select sum(score*unit)/sum(unit)as [avg]
from crs
inner join reg on reg.c#=crs.c#
where s#='1'*/

--تعداد دانشجویان هر شهر
/*SELECT COUNT(s#), city
FROM stud
GROUP BY city*/

--مشخصات هر درس و تعداد دانشجویانی که آن را اخذ کرده اند
/*SELECT crs.cname,crs.unit, crs.clg#, reg.c#, COUNT(distinct s#) AS [quantity of student]  
FROM reg  
inner JOIN crs ON reg.c# = crs.c#  
GROUP BY crs.cname,crs.unit, crs.clg#, reg.c#*/

/*SELECT  reg.c#, COUNT(distinct s#) AS [quantity of student]  
FROM reg  
inner JOIN crs ON reg.c# = crs.c#  
GROUP BY  reg.c#*/


/*SELECT  crs.c#, COUNT(distinct s#) AS [quantity of student]  
FROM crs 
left outer join reg ON reg.c# = crs.c#  
GROUP BY  crs.c#*/

--بیشترین نمره اخذ شده در هر درس
/*select crs.cname,reg.c#,max(score) from crs
inner join reg on reg.c#=crs.c#
group by crs.cname, reg.c#*/

--شماره دروسی که بیش از یک دانشجو آنها را اخذ کرده است
/*select c#,count(distinct s#) as [count] from reg
group by c#
having count(distinct s#) >1*/

--تعدا دروس ارائه شده در هر ترم بعداز ترم 401
/*select term,count(distinct c#) as[number of c#] 
from reg 
where term>401
group by term*/

--نام دانشجویانی که حداکثر نمره آنها بیشتر از 15 باشد
/*select stud.sname, max(score)as[max of score]
from reg
inner join stud on reg.s#=stud.s#
group by stud.sname, reg.s#
having max(score)>15*/

-- محاسبه معدل هر دانشجو
/*select  sum(score*unit)/sum(unit) as[avg]
from reg
inner join crs on reg.c#=crs.c#
group by  reg.s# */

--شماره دانشجویانی که معدل آنها اشتباه محاسبه شده است
/*select s# from reg
inner join crs on reg.c#=crs.c#
group by s#
having sum(score* unit)/sum(unit) not in (select [avg] from stud)*/
--روش دوم(ملاک و روش درست
/*select reg.s# from reg
inner join crs on reg.c#=crs.c#
inner join stud on reg.s#=stud.s#
group by reg.s#,stud.[avg]
having sum(score* unit)/sum(unit) != stud.[avg]*/




--نام تهیه کنندگانی که قطعه ای از جنس مس تهیه کرده اند
/*select sname
from s
inner join sp on s.s#=sp.s#
inner join p on sp.p#=p.p#
where [type]=N'مس'*/


/*select sname
from s 
where s# in (select s# from sp
             where p# in (select p# from p
			              where [type]=N'مس'))*/




--نام تهیه کنندگانی که قطعه سیاه رنگ تهیه کرده اند.
/*select sname 
from s
where s# in (select s#
             from sp
			 where p# in(select p#  
			             from p
				      where color=N'سیاه'))*/





/*select sname 
from s
inner join sp on s.s#=sp.s#
inner join p on sp.p#=p.p#
where color=N'سیاه'*/




/*select sname
from s
where exists (select s#
              from sp
	       where exists (select p#
			     from p
                             where color=N'سیاه'))*/




--نام دانشکده هایی که رئیس آنها دانشجویی از دانشکده علوم پایه داشته اند
/*SELECT  c.clgname  
FROM clg AS c  
INNER JOIN reg ON reg.p# = c.p#  
INNER JOIN stud ON stud.s# = reg.s#  
WHERE c.clgname = N'علوم پایه'*/

/*select c.clgname FROM clg AS c
where p# in (select p# from reg
             where s# in (select s# from stud 
			              where clg# in(select clg# from clg
						              where c.clgname = N'علوم پایه')))*/




--نام دانشکده هایی که روسای آن ها درسی از دانشکده عمران تدریس کرده اند

/*SELECT  c.clgname  
FROM clg AS c  
INNER JOIN reg ON reg.p# = c.p#  
INNER JOIN crs ON crs.c# = reg.c#  
inner join clg on clg.clg#=crs.clg#
WHERE clg.clgname = N'عمران'*/

/*select c.clgname FROM clg AS c
where p# in (select p# from reg
             where c# in (select c# from crs 
			              where clg# in(select clg# from clg
						                where clgname = N'عمران')))*/







--نام دانشجویانی که درس تدریس شده توسط استاد قربانی را برداشته اند

/*select distinct sname
from stud
inner join reg on reg.s#=stud.s#
inner join reg as r on r.c#=reg.c#
inner join prof on r.p#=prof.p#
where pname=N'قربانی'

select sname from stud
where s# in (select s# from reg
             where c# in (select c# from reg as r
			              where p# in(select p# from prof
						              where pname=N'قربانی')))*/





--نام روسای دانشکده هایی که دانشجو متولد سمنان با ایشان درس دارند.
/*SELECT DISTINCT prof.pname  
FROM prof  
INNER JOIN clg ON clg.p# = prof.p#  
INNER JOIN reg ON reg.p# = clg.p#  
INNER JOIN stud ON reg.s# = stud.s#  
WHERE stud.city = N'سمنان'*/

/*select pname from prof
where p# in (select p# from clg
             where p# in (select p# from reg
			              where s# in(select s# from stud
						              where city=N'سمنان')))*/






--نام روسای دانشکده هایی که درسی سه واحدی تدریس کرده اند
/*SELECT DISTINCT prof.pname  
FROM prof  
INNER JOIN clg ON clg.p# = prof.p#  
INNER JOIN reg ON reg.p# = clg.p#  
INNER JOIN crs ON reg.c# = crs.c#  
WHERE crs.unit= '3' */

/*select pname from prof
where p# in (select p# from clg
             where p# in (select p# from reg
			              where c# in(select c# from crs
						              where unit='3')))*/




--نام دانشجویانی که با استادی با مرتبه دانشیار درس داشته اند
/*SELECT sname 
FROM stud
INNER JOIN reg ON stud.s# = reg.s#
INNER JOIN prof ON reg.p#= prof.p# 
WHERE grade = N'دانشیار'*/

/*select sname
from stud
where s# in(select s# 
            from reg
			where c# in (select c#
			             from crs
						 where p# in (select p#
						              from prof
									  where pname=N'قربانی')))*/



--نام دروسی که دانشجوی متولد سمنان آنها را اخذ کرده است
/*select cname FROM crs
INNER JOIN reg ON crs.c# = reg.c#
INNER JOIN stud ON reg.s# = stud.s# 
WHERE city = N'سمنان'*/

/*select cname
from crs
where c# in (select c# 
             from reg
			 where s# in(select s#
			             from stud
						 where city=N'سمنان'))*/



--نام اساتیدی که درسی 3 واحدی تدریس کرده اند.
/*SELECT distinct pname 
FROM prof
INNER JOIN reg ON prof.p# = reg.p#
INNER JOIN crs ON reg.c# = crs.c# 
WHERE unit ='3'*/




/*select pname 
from prof
where p# in (select p#
             from reg
			 where c# in (select c# 
                          from crs
						  where unit='3'))*/
----------------------------------------------------
/*SELECT cname FROM crs
UNION all
SELECT sname FROM stud*/





/*SELECT pname FROM prof 
UNION all
SELECT sname FROM stud*/
---------------------




/*SELECT clg# FROM crs  
INTERSECT  
SELECT clg# FROM stud*/



---------------------



/*SELECT pname FROM prof 
except 
SELECT sname FROM stud*/

---------------------



/*SELECT pname FROM prof 
INTERSECT 
SELECT sname FROM stud*/

---------------------------
--تفاضل
--شماره درس هایی که استادی از دانشکده فیزیک آنها را تدریس نکرده
--with not in
/*select c# from crs
where c# not in(select reg.c#  from reg
                inner join prof on reg.p#=prof.p#
				inner join clg on prof.clg#=clg.clg#
				where clg.clgname=N'فیزیک')

--with except
select c#
from crs 
except
select reg.c#  
from reg
inner join prof on reg.p#=prof.p#
inner join clg on prof.clg#=clg.clg#
where clg.clgname=N'فیزیک'*/

--شماره دانشجویانی که بااستادی از دانشکده فیزیک آنها را تدریس نکرده







--شماره درس هایی که استادی با مرتبه دانشیار آن ها را تدریس نکرده اند
/*select c#
from crs
where c# not in(select c#
                from reg
				inner join prof on reg.p#=prof.p#
				where prof.grade=N'دانشیار')

select c# from crs
except
select c# from reg
inner join prof on reg.p#=prof.p#
where prof.grade=N'دانشیار'*/

--شماره دانشجویانی که با استادی با مرتبه دانشیار درس نداشته اند 
/*select s#
from stud
where s# not in(select s#
                from reg
				inner join prof on reg.p#=prof.p#
				where prof.grade=N'دانشیار')*/



--شماره اساتیدی که درسی از ریاضی ندریس نکرده اند
--پاسخ نادرست
/*select p# from reg
where c# not in (select c# from crs
                 inner join clg on crs.clg#=clg.clg#
				 where clg.clgname=N'ریاضی')*/

--پاسخ درست
/*select p# from reg
where reg.p# not in (select reg.p# from reg
                 inner join crs on reg.c#=crs.c#
                 inner join clg on crs.clg#=clg.clg#
				 where clg.clgname=N'ریاضی')*/


---------------------------------------------------------------------
--تقسیم

--شماره درس هایی که تمام اساتیدی با مرتبه دانشیار آنها را تدریس کرده اند
/*select c# from reg
inner join prof on reg.p#=prof.p#
where prof.grade=N'دانشیار'
group by c#
having count(distinct prof.p#)=(select count(*) from prof
                                where prof.grade=N'دانشیار')*/


--شماره درس هایی که تمام دانشجویان متولد مشهد آن ها را برداشته اند 
/*select reg.c# from reg
inner join stud on reg.s#=stud.s#
where stud.city=N'مشهد'
group by reg.c#
having count(distinct stud.s#)=(select count(*) from stud
                                where city=N'مشهد')*/


--شماره دانشجویانی که تمام دروس 2 واحدی را گرفته اند
/*select s# from reg
inner join crs on reg.c#=crs.c#
where unit='2'
group by reg.s#
having count( distinct reg.c#)=( select count(*) from crs
                                where unit='2')*/


--شماره دانشجویانی که با تمام اساتید مرتبه دانشیار درس داشته اند
/*select reg.s# from reg
inner join prof on reg.p#=prof.p#
where prof.grade=N'دانشیار'
group by reg.s#
having count(distinct prof.p#)=(select count(*) from prof
                                where grade=N'دانشیار')*/


--شماره اساتیدی که تمام دروس 2 واحدی را تدریس کرده اند
/*select reg.p# from reg
inner join crs on reg.c#=crs.c#
where crs.unit='2'
group by p#
having count(distinct reg.c#)=(select count(*) from crs
                               where unit='2')*/



--شماره اساتیدی که تمام دانشجویان با معدل الف با آنها درس داشته اند 
/*select reg.p# from reg
inner join stud on reg.s#=stud.s#
where stud.[avg]>17
group by reg.p#
having count(distinct stud.s#)=(select count(*) from stud
                                where [avg]>17)*/




------------------------------------
--تقسیم

--1)
--شماره درسهایی که تمام اساتید با مرتبه دانشیار آن ها را تدریس کرده اند
/*SELECT c#
FROM reg
JOIN prof ON reg.p# = prof.p#
WHERE prof.grade = N'دانشیار'
GROUP BY c#
HAVING COUNT(DISTINCT prof.p#) = (SELECT COUNT(*) FROM prof
                                  WHERE grade = N'دانشیار')*/



--شماره درس هایی که تمام اساتيد دانشکده برق، آنها را تدریس کرده اند
/*SELECT c# 
FROM reg  
JOIN prof  ON reg.p# = prof.p#  
JOIN clg  ON prof.clg# = clg.clg#
WHERE clg.clgname =N'برق'  
GROUP BY c#  
HAVING COUNT(DISTINCT prof.p# ) = (SELECT COUNT(*) FROM prof 
                                   join clg on prof.p#=clg.p#
                                   WHERE  clgname = N'برق')*/
-----------------------------------------------------------
--2)****
--شماره درسهایی که تمام دانشجویان متولد سمنان آنها را برداشته اند
/*SELECT reg.c# 
FROM reg
JOIN stud ON reg.s# = stud.s#
WHERE stud.city = N'سمنان'
GROUP BY reg.c#
HAVING COUNT(DISTINCT stud.s#) = (SELECT COUNT(*) FROM stud
                                  WHERE city = N'سمنان')*/



--شماره درس هایی که تمام دانشجویان دانشکده عمران، آنها را اخذ کرده اند
/*SELECT reg.c#
FROM reg
JOIN stud ON reg.s# = stud.s#
JOIN clg ON stud.clg# = clg.clg#
WHERE clg.clgname = N'عمران'
GROUP BY reg.c#
HAVING COUNT(DISTINCT stud.s#) = (SELECT COUNT(*) FROM stud
                                  JOIN clg ON stud.clg# = clg.clg#
                                  WHERE clg.clgname = N'عمران')*/

-------------------------------------------------------------------
--3)
--شماره دانشجویانی که تمام دروس 2 واحدی را اخذ کرده اند
/*select s# from reg
join crs on reg.c#=crs.c#
where unit=2
group by reg.s# 
having count(distinct reg.c#)=( select count(*) from crs
                                where unit=2)*/


--شماره دانشجویانی که تمام دروس دانشکده برق را برداشته اند
/*SELECT s# 
FROM reg  
JOIN crs ON reg.c# = crs.c#  
JOIN clg  ON crs.clg# = clg.clg#
WHERE clg.clgname =N'برق'  
GROUP BY s#  
HAVING COUNT(DISTINCT reg.p# ) = (SELECT COUNT(*) FROM prof 
                                   join clg on prof.p#=clg.p#
                                   WHERE  clgname = N'برق')*/

----------------------------------------------------------------------
--4)
--شماره دانشجویانی که تمام اساتید با مرتبه دانشیار آن ها را تدریس کرده اند
/*SELECT s#
FROM reg
JOIN prof ON reg.p# = prof.p#
WHERE prof.grade = N'دانشیار'
GROUP BY s#
HAVING COUNT(DISTINCT prof.p#) = (SELECT COUNT(*) FROM prof
                                  WHERE grade = N'دانشیار')*/



--شماره دانشجویانی که با تمام اساتید فیزیک درس داشته اند
/*SELECT reg.s#
FROM reg
JOIN prof ON reg.p# = prof.p#
JOIN clg ON prof.clg# = clg.clg#
WHERE clg.clgname = N'برق'
GROUP BY reg.s#
HAVING COUNT(DISTINCT clg.clg#) = (SELECT COUNT(DISTINCT clg.clg#)  FROM clg
                                  WHERE clg.clgname = N'برق')*/


------------------------------------------------------------------------
--5)
--شماره اساتیدی که تمام دروس 2 واحدی را تدریس کرده اند
/*select reg.p# from reg
join crs on reg.c#=crs.c#
where unit=2
group by reg.p# 
having count(distinct reg.c#)=( select count(*) from crs
                                where unit=2)*/




--شماره اساتیدی که تمام دروس دانشکده عمران را تدریس کرده اند
/*SELECT reg.p# 
FROM reg 
JOIN crs  ON reg.c# = crs.c#
JOIN clg  ON crs.clg# =clg.clg#
WHERE clg.clgname =N'عمران'  
GROUP BY reg.p# 
HAVING COUNT(DISTINCT crs.c#) = (SELECT COUNT(*)FROM stud  
                                 WHERE clg# = (SELECT clg# FROM clg
								               WHERE clgname =N'عمران'))*/



------------------------------------------------------------------------
--6)
--شماره اساتیدی که تمام دانشجویان با معدل بالای 17، با آنها درس داشته اند 
/*SELECT reg.p#  
FROM reg
JOIN stud ON reg.s# = stud.s#  
WHERE stud.[avg] > 17  
GROUP BY reg.p#   
HAVING COUNT(DISTINCT stud.s#) = (SELECT COUNT(*) FROM stud WHERE [avg] > 17)*/



--شماره اساتیدی که تمام دانشجویان دانشکده عمران با آن درس داشته اند 
/*SELECT reg.p# 
FROM reg   
JOIN stud  ON reg.s# = stud.s#
JOIN clg  ON stud.clg# =clg.clg#
WHERE clg.clgname =N'عمران'  
GROUP BY reg.p# 
HAVING COUNT(DISTINCT stud.s#) = (SELECT COUNT(*)FROM stud  
                                 WHERE clg# = (SELECT clg# FROM clg
								               WHERE clgname =N'عمران'))*/


------------------------------------------------------------------------------

--شماره اساتیدی که تمام دانشجویان متولد سمنان، با ايشان درس داشته اند
/*SELECT prof.p# 
FROM prof   
JOIN reg  ON prof.p# = reg.p#
JOIN stud  ON reg.s# = stud.s#  
WHERE stud.city = 'سمنان'  
GROUP BY prof.p# 
HAVING COUNT(DISTINCT stud.s#) = (SELECT COUNT(*)FROM stud  
                                  WHERE city = 'سمنان')*/




--شماره درس هایی که تمام دانشجویان دانشکده عمران، آنها را اخذ کرده اند
/*SELECT reg.c#
FROM reg
JOIN stud ON reg.s# = stud.s#
JOIN clg ON stud.clg# = clg.clg#
WHERE clg.clgname = N'عمران'
GROUP BY reg.c#
HAVING COUNT(DISTINCT stud.s#) = (SELECT COUNT(*) FROM stud
                                  JOIN clg ON stud.clg# = clg.clg#
                                  WHERE clg.clgname = N'عمران')*/



-------------------------------------------------------------------
--تفاضل

--1)
--شماره درس هایی که استادی از دانشکده برق آن ها را تدریس نکرده

--with not in
/*SELECT c#
FROM crs
WHERE c# NOT IN (
    SELECT reg.c#
    FROM reg 
    JOIN prof  ON reg.p# = prof.p#
	JOIN clg ON prof.clg# = clg.clg#
    WHERE clg.clgname = N'عمران')*/


--with exept
/*SELECT c#
FROM crs

EXCEPT

SELECT DISTINCT reg.c#
FROM reg
JOIN prof ON reg.p# = prof.p#
JOIN clg ON prof.clg# = clg.clg#
    WHERE clg.clgname = N'عمران'*/


---------------------------------------------------

--2)
--شماره دروسی که دانشجویی از عمران آنها را اخذ نکرده 
/*SELECT c#
FROM crs
WHERE c# NOT IN (
    SELECT reg.c#
    FROM reg
    JOIN stud ON reg.s# = stud.s#
    JOIN clg ON stud.clg# = clg.clg#
    WHERE clg.clgname = N'عمران')*/



--with exept
/*SELECT c#
FROM crs

EXCEPT

SELECT DISTINCT reg.c#
FROM reg
JOIN stud ON reg.s# = stud.s#
JOIN clg ON stud.clg# = clg.clg#
    WHERE clg.clgname = N'عمران' */

--------------------------------------------------------
--3)
--شماره دانشجویانی که درس 2 واحدی نگرفته اند
/*SELECT s#
FROM stud
WHERE s# NOT IN (
    SELECT DISTINCT reg.s#
    FROM reg
    JOIN crs ON reg.c# = crs.c#
    WHERE crs.unit = '2')*/


--with exept
/*SELECT s#
FROM stud

EXCEPT

SELECT DISTINCT reg.s#
FROM reg
JOIN crs ON reg.c# = crs.c#
WHERE crs.unit = '2'*/


-----------------------------------------------------
--4)
--شماره دانشجویانی که با استاد قربانی درس نداشته اند
/*SELECT s#
FROM stud
WHERE s# NOT IN (
    SELECT DISTINCT reg.s#
    FROM reg
    JOIN prof ON reg.p# = prof.p#
    WHERE prof.pname = N'قربانی')*/



/*SELECT s#
FROM stud

EXCEPT

SELECT DISTINCT reg.s#
FROM reg
JOIN prof ON reg.p# = prof.p#
WHERE prof.pname = N'قربانی'*/

-----------------------------------------------
--5)
--شماره اساتیدی که درس 2 واحدی نگرفته اند
/*SELECT p#
FROM prof
WHERE p# NOT IN (
    SELECT DISTINCT reg.p#
    FROM reg
    JOIN crs ON reg.c# =crs.c#
    WHERE crs.unit = '2')*/



/*SELECT p#
FROM prof

EXCEPT

SELECT DISTINCT reg.p#
FROM reg
JOIN crs ON reg.c# =crs.c#
WHERE crs.unit = '2'*/

-----------------------------------------------
--6)
--شماره اساتیدی که دانشجوی متولد سمنان با آن ها درس نداشته اند
/*SELECT p#
FROM prof
WHERE p# NOT IN (
    SELECT DISTINCT reg.p#
    FROM reg 
    JOIN stud ON reg.s# = stud.s#
    WHERE stud.city = N'سمنان')*/


/*SELECT p#
FROM prof

EXCEPT

SELECT DISTINCT reg.p#
FROM reg 
JOIN stud ON reg.s# = stud.s#
WHERE stud.city = N'سمنان'*/