16 Şubat 2016 Salı

northwind join işlemleri

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

Hiç yorum yok:

Yorum Gönder