CREATE DATABASE SqlOgreniyorum
CREATE TABLE Calisanlar(
id INT NOT NULL,
ad VARCHAR (50) NOT NULL,
soyad VARCHAR (50) NOT NULL,
email VARCHAR(50) NOT NULL,
tc_no NCHAR(11) NOT NULL,
maas MONEY NOT NULL,
departman_id INT NOT NULL
)
CREATE TABLE Departman
(
id INT NOT NULL,
departman_ad VARCHAR(50) NOT NULL
)
create procedure spCalisanEkle(
@ID int,
@Adi varchar(50),
@Soyadi varchar(50),
@DepartmanID int,
@Email varchar(50),
@TcNo varchar(50),
@Maas money) as begin
insert Calisanlar (id,ad,soyad,departman_id,email,tc_no,maas)values
(@ID,@Adi,@Soyadi,@DepartmanID,@Email,@TcNo,@Maas)
end
exec spCalisanEkle 10,'x','Y',1,'x@as','1111111',1000
create procedure spDepartmanEkle(
@ID int,
@Adi varchar(50))as begin
declare @kayitvarmi int
select @kayitvarmi = COUNT(*) from Departman WHERE id = @ID
declare @sonuc int if @kayitvarmi > 0 begin set @sonuc=0 end else begin
insert Departman(id,departman_ad) values (@ID,@Adi) set @sonuc=1 end return @sonuc end
declare @kayityapildimi int
exec @kayityapildimi = spDepartmanEkle 1, "Yordam Departman"
if @kayityapildimi = 0
begin
print 'Kayit islemi basarisiz'
end else begin
print 'Kayit islemi basarili'
end
create procedure CalisanZamGuncelle(@Zam decimal,@id int,@yenimaas decimal output)
as begin
update Calisanlar set maas = maas+@Zam where id = @id
Select @yenimaas = maas from Calisanlar where id = @id
end
declare @yenitutar money
exec CalisanZamGuncelle 100,1,@yenitutar out print @yenitutar
use master
alter table Calisanlar
alter column Email varchar(50) null
create trigger trgMailEkle on Calisanlar after insert as begin
declare @ad varchar(50)
declare @cid int
select @ad = ad,@cid=id from inserted
update Calisanlar set Email = CONCAT(substring(@ad,1,2),'@as')
where id = @cid
end
insert Calisanlar (id,ad,soyad,tc_no,maas,departman_id)