Notes

--create database burak
use burak
create table musteri(
m_no int primary key identity(1,1),
ad varchar(30),
soyad varchar(40),
tel int,
email varchar(70),
adres varchar(40),
sehir varchar(55)
)
go
use burak
go
create table siparis_detay(
d_no int primary key identity(1,1),
s_no int,
u_no int,
miktar int,
)
use burak
insert into musteri values
('Kemal','Kaya',11111,'[email protected]','Bornova','İzmir'),
('Mehmet','Çetin',22222,'[email protected]','Egekent','İzmir'),
('Ceyda','Duran',33333,'[email protected]','Pendik','İstanbul'),
('Serdar','Çolak',44444,'[email protected]','Ulus','Ankara'),
('Derya','Sönmez',55555,'[email protected]','Bakırköy','İstanbul'),
('Ayşe','Ölmez',66666,'[email protected]','Avcılar','İstanbul'),
('İbrahim','Duran',77777,'[email protected]','Konak','İzmir'),
('Selma','Yavuz',88888,'[email protected]','Alsancak','İzmir'),
('Burak','Tan',99999,'[email protected]','Çankaya','Ankara'),
('Kenan','Yıldız',1010101,'[email protected]','Yenimahalle','Bursa'),
('Leyla','Turan',1101101,'[email protected]','Bandırma','Balıkesir'),
('Güneş','Burcu',121212,'[email protected]','Marmaris','Muğla'),
('Petek','Başak',131313,'[email protected]','Marmaris','Muğla')

use burak
insert into siparis_detay values
(1,2,1),
(1,20,1),
(2,9,1),
(3,11,4),
(4,16,5),
(4,29,1),
(5,19,1),
(6,12,1),
(6,18,2),
(7,7,1)

use burak
create table kategori_tablosu(
k_no int primary key identity(1,1),
ad varchar(30)
)

use burak
insert into kategori_tablosu values
('Bilgisayar'),
('Laptop'),
('Televizyon'),
('Cep Telefonu'),
('Kitap'),
('Cd')

use burak
create table siparis(
s_no int primary key identity(1,1),
m_no int,
tarih date,
t_fiyat int
)

use burak
insert into siparis values
(3,'2010/1/1',924),
(7,'2010/1/3',2200),
(7,'2010/3/1',3000),
(1,'2010/4/1',2740),
(12,'2010/1/4',350),
(5,'2010/1/4',1800),
(8,'2010/5/1',1400)

use burak
create table urun(
u_no int primary key identity(1,1),
ad varchar(40),
k_no int,
fiyat int,
stok int
)

use burak
insert into urun values
('Casper pc',1,800,12),
('IBM pc',1,900,14),
('HP pc',1,1200,0),
('Toshiba pc',1,1600,9),
('Exper pc',1,1250,10),
('Sony laptop',2,2100,5),
('Hp laptop',2,1400,7),
('Toshiba laptop',2,1600,0),
('Vestel plazma tv',3,2200,4),
('Samsung tv',3,800,13),
('Philips tv',3,750,5),
('Sony tv',3,900,16),
('Samsung plazma tv',3,3400,0),
('Lg plazma tv',3,5600,5),
('Nokia series',4,500,14),
('Samsung series',4,600,6),
('Sony Ericson series',4,450,22),
('Siemens series',4,350,13)


3-)select musteri.*from musteri inner join siparis on musteri.m_no=siparis.m_no
4-)select musteri.*from musteri inner join siparis on musteri.m_no=siparis.m_no where musteri.sehir!='İzmir'
5-)select musteri.m_no,musteri.ad,musteri.soyad,siparis.s_no,siparis.tarih from musteri inner join siparis on musteri.m_no=siparis.m_no
6-)
7-)select * from musteri where soyad='duran'
8-)select * from siparis where t_fiyat>=2000
9-)select * from siparis where tarih between'2010/01/01'and '2010/01/03'
10-)select *from musteri where adres is not null
11-)select COUNT(u_no) as Toplam_Kayıt from urun
12-)select AVG(DISTINCT fiyat) from urun
13-)select SUM(fiyat) as TOPLAM from urun where stok>10
14-)select AVG(urun.fiyat) from urun inner join kategori_tablosu on urun.k_no=kategori_tablosu.k_no where kategori_tablosu.k_no=4
21-)use burak
ALTER TABLE siparis_detay ADD tarih date
22-)UPDATE siparis_detay
SET tarih='1/1/2010'