SQL STRİNG İFADELER,TABLO OLUŞTURMA VE TARİH FONKSİYONLARI

--STRING İFADELER

select SUBSTRING(LastName,3,2) from Employees

select LastName,SUBSTRING(LastName,3,4) from Employees

select SUBSTRING(LastName,3,4)+SUBSTRING(FirstName,1,3) from Employees

select LEFT(LastName,2) from Employees

Select LastName,RIGHT(LastName,3) from Employees

Select LastName,UPPER(Right(Lastname,3)) from Employees

select LastName,LOWER(Right(LastName,3)) from Employees

select Upper(LastName) from Employees

Select UPPER(Left(FirstName,2)+Left(LastName,3)) from Employees

select REVERSE('tarik')

select LEN(FirstName) from Employees where LEN(FirstName)>5

select charindex('ğ','tağrik')

select CHARINDEX('m',firstname),firstname from Employees

--Tablo Oluşturma

--uygulama-calisanlar-sağtık-edit top 200 ver girşi

create database uygulama

create table calisanlar

(

calisanid int primary key identity(1,1),

calisanAdi varchar(40),

calisaSoyad varchar(40),

maasi money default 1000,

baslamatarihi date check(baslamatarihi between '01.01.2010' and getdate()),

calistigi varchar(30) default 'satis'

)

create table kategoriler

(

KategoriID int Primary key identity(1,1),KategoriAdi varchar(30) not null default 'Bilgisayar' unique

)

create table urunler

(

Urunid int primary key identity(1,1),

Urunadi varchar(50),

Kategoriid int references kategoriler(KategoriId),

AlisFiyati money check(AlisFiyati>0),

Birimi char(5) default 'Adet',

StokMiktar int check (stokMiktar between 0 and 1000)

)

create table musteriler1

(

musteriid int primary key identity(1,1),

firmaadi varchar(100),

YetkiliAdi varchar(50) not null,

YetkiliSoyadi varchar(50) not null,

Adres varchar(100),

ceptel char(11)check(len(ceptel)=11 and ceptel like '05[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]') unique,eposta varchar(70)

unique check(eposta like '%@%')

)

--Tarih Fonsiyonları

select getdate() --Sistemin o anki Tarihi

select DATEPART(Month,'03.19.2012')

select DATEPART(DAY,'03.19.2012')

select DATEPART(HOUR,'03.19.2012')

select DATEPART(MINUTE,'03.19.2012')

select DATEPART(MINUTE,GETDATE()) AS dakika,DATEPART(HOUR,GETDATE()) as saniye

select*from Employees

select DATEPART(month,birthdate) as ay,FirstName from Employees where DATEPART(month,birthdate)=1

select DATEDIFF(year,'05.03.1995',getdate())

select DATEDIFF(month,'05.03.1995',getdate())

select DATEDIFF(hour,'05.03.1995',getdate())

select DATEDIFF(second,'05.03.1995',getdate())