06.11.2010 cumartesi

MATEMATİKSEL İŞLEMLER

use eticaretDB
go

select * from urunlerTbl
select markaID from urunlerTbl
select DISTINCT markaID from urunlerTbl

select * from markalarTbl
select COUNT(*) from markalarTbl
select SUM(fiyati) from urunlerTbl
select AVG(fiyati) from urunlerTbl
select * from urunlerTbl where fiyati=(select MAX(fiyati) from urunlerTbl)
select * from urunlerTbl where fiyati=(select MIN(fiyati) from urunlerTbl)

select markaID,SUM(fiyati) from urunlerTbl GROUP BY markaID
select markaID,SUM(fiyati) from urunlerTbl GROUP BY markaID HAVING SUM(fiyati)>250

select binary_checksum('Emrah')
select checksum('Emrah')

select abs(98)
select abs(-98)

select cos(90)
select sin(90)
select tan(90)
select cot(90)

select exp(4.5)
select log(4.7)
select log10(100)

select pi()
select power(5,3)

select round(45.64,1)
select sqrt(16)
select square(4)

**************************************************************

use eticaretDB
go

select * from urunlerTbl where fiyati IN (220,100,270)
select * from urunlerTbl where fiyati NOT IN (100,270)

select * from urunlerTbl where isim LIKE '%Y%'
select * from urunlerTbl where isim NOT LIKE '%Y%'

select * from urunlerTbl where stokSayisi BETWEEN 50 AND 60
select * from urunlerTbl where stokSayisi NOT BETWEEN 50 AND 60

select DAY(kayitTarihi) from urunlerTbl where isim='ERTYU'
select MONTH(kayitTarihi) from urunlerTbl where isim='ERTYU'
select YEAR(kayitTarihi) from urunlerTbl where isim='ERTYU'

select LEFT(isim,2) from urunlerTbl
select RIGHT(isim,2) from urunlerTbl
select LEFT('Ali Emrah',5)
select RIGHT('Ali Emrah',7)

select (' Emrah')
select LTRIM(' Emrah')
select ('Emrah ')
select RTRIM('Emrah ')

select isim,LEN(isim) from urunlerTbl
select LEN(' Ali Emrah')
select LEN(LTRIM(' Ali Emrah'))

select SUBSTRING('Emrah',3,3)
select SUBSTRING(isim,2,2) from urunlerTbl
select SUBSTRING(isim,2,3) from urunlerTbl

select UPPER(isim) from urunlerTbl
select LOWER(isim) from urunlerTbl

select ascii('A')
select ascii('B')
select ascii('C')
select ascii('a')
select ascii('b')
select ascii('c')

select CHAR(65),CHAR(66),CHAR(67)
select CHAR(97),CHAR(98),CHAR(99)
select nChar(65)


select isim as 'Normal Hali',REVERSE(isim) as 'Ters Cevrilmis Hali' from urunlerTbl
select REVERSE('Ali Emrah PEKESEN')

select ad,soyad from musterilerTbl
select ad+soyad from musterilerTbl
select ad+' '+soyad from musterilerTbl
select ad+SPACE(2)+soyad from musterilerTbl

select str(5.45)
select str(5.56)

select isim from urunlerTbl
select REPLACE(isim,'X','Z') from urunlerTbl
select REPLACE('Ali Emrah','a','b')

*********************************************************
BİRLEŞTİRME METODLARI

use eticaretDB
go

select * from markalarTbl,urunlerTbl

select * from urunlerTbl

select * from markalarTbl,urunlerTbl where markalarTbl.id=urunlerTbl.markaID

select * from markalarTbl inner join urunlerTbl on markalarTbl.id=urunlerTbl.markaID
select * from markalarTbl left join urunlerTbl on markalarTbl.id=urunlerTbl.markaID
select * from markalarTbl right join urunlerTbl on markalarTbl.id=urunlerTbl.markaID


select isim from markalarTbl union select isim from urunlerTbl
select isim from markalarTbl union all select isim from urunlerTbl

***********************************************************

use eticaretDB
go

insert into urunlerTbl
(markaID,uretimYili,isim,stokSayisi,fiyati)
values
(2,2011,'XXXYY',67,150)
go

select m.isim as 'Marka Adi',
u.isim as 'Modeli',
u.fiyati as 'Fiyat'
from urunlerTbl u,markalarTbl m where u.markaID=m.id

****************************************************

use forumDB
go

select * from userTbl where soyadi like 'P%'


select adi+' '+soyadi,email from userTbl

select adi+' '+soyadi as 'Ad Soyad',email as 'E Mail Adresi' from userTbl where age between 20 and 40 and adi like '%y%'


select * from userTbl where age between 20 and 40 and adi like '%y%'

select * from userTbl order by email asc --desc -- asc