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 

);



check constraint
([avg]>=(0) AND [avg]<=(20))



alter table [Z]
add constraint ck1 check([avg]>=0    AND [avg]<=20)



alter table [Z]
add [index] as (([avg] /10)*2) 

===================================================================================

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)


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

==============================================================================
==============================================================================

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

);

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

==============================================================================
==============================================================================

INSERT INTO S
VALUES (N'قطعه سازان',null, 2);

INSERT INTO S (sname,scity,[status])
VALUES (N'همیار مکانیک', null, 1);

INSERT INTO S (sname,scity,[status])
VALUES (N'نیکو صنعت', N'سمنان', 2);

INSERT INTO P ([type],color)
VALUES (N'مس',N'نارنجی');

INSERT INTO P
VALUES (N'آهن',N'قرمز', null);

INSERT INTO P
VALUES (N'آلومینیوم',N'سبز', null);
go 3

DELETE FROM P WHERE p# >3;


INSERT INTO SP
VALUES (2,1,7,getdate());

INSERT INTO SP (s#, p#, qty)
VALUES (1,1,6);

INSERT INTO SP (s#, p#, qty)
VALUES (1,2,10);

==============================================================================
==============================================================================

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

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

SELECT TOP (3) s# as supplier#, p#,qty as [Max-quantity]
  FROM SP
  ORDER BY qty desc;

SELECT  s#
      ,sname+ N' '+  scity as zz
      ,[status]
  FROM S

SELECT [s#]
      ,[p#]
      ,[qty]
      ,qty *20 as zz
  FROM [productSupplier].[dbo].[SP]

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 s 
cross join sp

select * 
from s,sp

SELECT *
FROM s
INNER JOIN sp
ON s.s# = sp.s#;

SELECT *
FROM s,sp
where s.s# = sp.s#;

SELECT *
FROM prof
INNER JOIN reg
ON prof.p# = reg.p#;


SELECT p.type
FROM ((p
INNER JOIN sp ON p.p#= sp.p#)
INNER JOIN s ON s.s#=sp.s# and scity=N'تهران');

SELECT p.type
FROM p where p# in (select p# FROM sp where s# in (select s# FROM s where scity=N'تهران'))


==============================================================================
The HAVING clause was added to SQL because 
the WHERE keyword cannot be used with aggregate functions.
==============================================================================


select *
from SP
where qty> (SELECT MIN(qty) FROM SP)


SELECT COUNT(s#),scity
FROM S
where [status] > 1
GROUP BY scity
 HAVING COUNT(s#) > 1

==============================================================================
============================== CASE ==========================================
==============================================================================
SELECT s#,[avg],
CASE
    WHEN   20>= [avg] and [avg]> 17 THEN 'A'
    WHEN   17>= [avg] and [avg]> 14 THEN 'B'
	WHEN   14>= [avg] and [avg]> 12 THEN 'C'
	WHEN   12>= [avg] and [avg]> 10 THEN 'D'
ELSE 'The avg is under 10'
END AS avgText
FROM STUD;

==============================================================================
============================== Stored Procedure ==============================
A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again.
You can also pass parameters to a stored procedure, so that the stored procedure can act based on the parameter value(s) that is passed.
==============================================================================
CREATE PROCEDURE SelectAllstudents
AS
SELECT * FROM STUD
GO;

EXEC SelectAllstudents;

================================
CREATE PROCEDURE Selectstudents @City nvarchar(20)
AS
SELECT * FROM STUD WHERE City = @City


EXEC Selectstudents @City = 'd';
================================
CREATE PROCEDURE Selectstudents0 @City nvarchar(20), @avg float
AS
SELECT * FROM STUD WHERE City = @City AND [avg]>@avg


EXEC Selectstudents0 @City = 'd', @avg=10;


==============================================================================
============================== view ==========================================
==============================================================================

create view View3table
as
select S.s#,S.sname,SP.qty,P.p#,P.color
from S inner join SP
on(S.s#=SP.s#) inner join P on(SP.p#=P.p#)

select *
from View3table

select *
from View3table 
where qty<5
==============================================================================
============================== variable, if else, while==========================================
==============================================================================

declare @x int
set @x=100
print @x
=======================
declare @x int
set @x=13
if @x%2=0
	print 'EVEN'
Else
	print 'ODD'
=======================
declare @x int
set @x=1
while @x<=100
begin
	print @x
	set @x=@x+1
end
=======================
declare @x int,@i int
set @x=29
set @i=2
while @i<@x
begin
	if @x%@i=0
	begin
		print 'Its not Prime'
		print @i
		return
	end
	set @i=@i+1
end
print 'Its Prime'

=======================

declare @x int
set @x=1
while @x<=3
begin
	insert into SP (s#,p#,qty,[date])
	select @x,p#,CAST(rand()*10 as int),GETDATE()
	from P
	set @x=@x+1
end
=======================