use northwind
go
select * from dersler
select * from ogrenciler
select * from dbo.OgrenciDers
create view OgrenciKarne
as
select O.Adi , O.Soyadi ,D.DersAdi ,OD.Notu
from OgrenciDers OD inner join ogrenciler O
on OD.OgrenciID = O.OgrID
inner join Dersler D
on OD.DersID = D.DersID
select * from OgrenciKarne
create view DersNotAlmayanlar
as
select O.Adi , O.Soyadi ,D.DersAdi ,OD.Notu
from OgrenciDers OD right join ogrenciler O
on OD.OgrenciID = O.OgrID
left join Dersler D
on OD.DersID = D.DersID
where D.DersAdi is null or OD.Notu is null
select * from DersNotAlmayanlar
alter view DersNotAlmayanlar
as
select O.Adi as ‘Öğrenci Adı’, O.Soyadi as ‘Öğrenci Soyadi’,D.DersAdi ,OD.Notu
from OgrenciDers OD right join ogrenciler O
on OD.OgrenciID = O.OgrID
left join Dersler D
on OD.DersID = D.DersID
where D.DersAdi is null or OD.Notu is null
alter view DersNotAlmayanlar(OgrenciAdi,OgrenciSoyadi,DersinAdi,DersNotu)
as
select O.Adi , O.Soyadi ,D.DersAdi ,OD.Notu
from OgrenciDers OD right join ogrenciler O
on OD.OgrenciID = O.OgrID
left join Dersler D
on OD.DersID = D.DersID
where D.DersAdi is null or OD.Notu is null
sp_help DersNotAlmayanlar
sp_helptext DersNotAlmayanlar
select * from INFORMATION_SCHEMA.TABLES
where table_name=’DersNotAlmayanlar’
select * from INFORMATION_SCHEMA.VIEWS
where table_name=’DersNotAlmayanlar’
alter view DersNotAlmayanlar(OgrenciAdi,OgrenciSoyadi,DersinAdi,DersNotu)
with encryption
as
select O.Adi , O.Soyadi ,D.DersAdi ,OD.Notu
from OgrenciDers OD right join ogrenciler O
on OD.OgrenciID = O.OgrID
left join Dersler D
on OD.DersID = D.DersID
where D.DersAdi is null or OD.Notu is null
alter view OgrenciDurum
with schemabinding
as
select O.Adi , O.Soyadi ,D.DersAdi ,OD.Notu , OD.Durum
from dbo.OgrenciDers OD inner join dbo.ogrenciler O
on OD.OgrenciID = O.OgrID
inner join dbo.Dersler D
on OD.DersID = D.DersID
select * from OgrenciDurum
sp_helptext OgrenciDurum
update OgrenciDers
set Durum = (case when Notu >=50 then ‘Geçti’ else ‘kaldi’ end)
northwind join işlemleri
