sql2

create database  sistem1
go
use sistem1
go

create table [dbo].[marka](
marka_no int not null PRIMARY KEY IDENTITY(1,1),
marka_isim nvarchar(50) not null)

create table [dbo].[urun](
urun_no int not null PRIMARY KEY IDENTITY (1,1),
marka_no int,
FOREIGN KEY (marka_no) REFERENCES [dbo].[marka](marka_no),
urun_isim nvarchar(20) not null,urun_fiyat int not null)

create table [dbo].[model](
model_no int not null PRIMARY KEY IDENTITY (1,1), 
marka_no int,
FOREIGN KEY (marka_no) REFERENCES [dbo].[marka](marka_no),
model_isim nvarchar(50) not null)

create procedure SP_Marka1(@marka_isim nvarchar(50))
as
SET NOCOUNT ON;
insert into [dbo].[marka]([marka_isim]) values(@marka_isim)
go
EXEC SP_Marka1 "abc"
go


create procedure SP_Delete(@marka_isim nvarchar(50))
as
SET NOCOUNT ON;
DELETE [dbo].[marka]
     FROM  [dbo].[marka]
     WHERE  
     marka_isim = @marka_isim
go

EXEC SP_Delete
     @marka_isim  =  "abc"    
go

create procedure SP_Update(@urun_no int, @urun_fiyat float)
as
SET NOCOUNT ON;
 UPDATE [dbo].[urun]
      SET 
             urun_fiyat    = @urun_fiyat
      FROM   [dbo].[urun]
      WHERE  
      urun_no    = @urun_no   
go

EXEC SP_Update
     @urun_no =    4   , 
     @urun_fiyat  = 30
	 go




	 create procedure tttt(@marka_no int)
as
SET NOCOUNT ON;
DELETE [dbo].[marka]
     FROM  [dbo].[marka]
     WHERE  
     model_no = @model_no
	 go
	 DELETE [dbo].[model]
     FROM  [dbo].[model]
     WHERE  
     model_no = @model_no
go


EXEC tttt
     @model_no  = 2  
go



SELECT 
*
 FROM  (
SELECT 
	 model_isim as ad
	,Count(*) as SAYI
FROM [dbo].[model] GROUP BY model_isim) K 
PIVOT(SUM(SAYI) FOR ad IN ([corsa],[astra])) AS PVT




 create procedure bbbb(@marka_no int)
as
SET NOCOUNT ON;
DELETE [dbo].[model]
     FROM  [dbo].[model]
     WHERE  
     marka_no = @marka_no
	 go
	 DELETE [dbo].[marka]
     FROM  [dbo].[marka]
     WHERE  
     marka_no = @marka_no
go


EXEC bbbb
     @marka_no  = 2  
go