Untitled

CREATE TABLE PERSONEL(
Perno int identity(1,1) PRIMARY KEY,
Isim varchar(15)
CONSTRAINT ck_Isim
CHECK(LEN(Isim) BETWEEN 3 AND 15),
Soyad varchar(15)
CONSTRAINT ck_Soyad
CHECK(LEN(Soyad) BETWEEN 2 AND 15),
Dept varchar(3)
CONSTRAINT ck_Dept
CHECK(Dept in ('MUH','SAT','SER','NAK','ARG')),
Ucret money
CONSTRAINT ck_Ucret
CHECK(Ucret BETWEEN 0 AND 10000),
Baslamatarih date
CONSTRAINT ck_Baslamatarih
CHECK(Baslamatarih BETWEEN '01.01.2000' AND getdate()),
Unvan varchar(6)
CONSTRAINT ck_Unvan
CHECK(Unvan in ('MUDUR','SEF','MEMUR','ELEMAN'))
)

ALTER TABLE PERSONEL
ALTER COLUMN Isim varchar(20)

ALTER TABLE PERSONEL 
ADD Memleket varchar(15)
ALTER TABLE PERSONEL
ADD ogrenim_durumu varchar(15)
CONSTRAINT ck_ogrenim_durumu
CHECK(ogrenim_durumu in ('Ilkogretim','Lise','Onlisans','Lisans','Lisansustu'))

ALTER TABLE PERSONEL
ADD Boy int
CONSTRAINT ck_Boy
CHECK(Boy>0)

ALTER TABLE PERSONEL
DROP ck_Boy
ALTER TABLE PERSONEL
DROP COLUMN Boy

INSERT INTO PERSONEL VALUES ('Berke','Yilmaz','MUH','3500','01.02.2001','MUDUR','Duzce','Lisans')
INSERT INTO PERSONEL VALUES ('Semih','Dincer','SAT','2540','01.03.2003','SEF','Sakarya','Onlisans')
INSERT INTO PERSONEL VALUES ('Didem','Bekmezci','SER','2850','01.03.2004','MEMUR','Izmir','Lise')

INSERT INTO PERSONEL VALUES('Mine','Ozturk','ARG','2900','01.03.2007','ELEMAN','Bolu','Lisansustu'),
('Utku','Sendir','NAK','2350','01.05.2009','ELEMAN','Kars','Ilkogretim')

INSERT INTO PERSONEL(Isim) VALUES ('Remzi')
INSERT INTO PERSONEL(Isim,Soyad) VALUES ('Gurkan','Su')
INSERT INTO PERSONEL (Isim,Soyad,Dept) VALUES ('Pinar','Gurler','MUH')
INSERT INTO PERSONEL (Isim,Soyad,Dept,Ucret) VALUES ('Timur','Tilli','NAK','2450')

UPDATE PERSONEL
SET Ucret = Ucret*1.15

UPDATE PERSONEL
SET Ucret = Ucret*1.10
WHERE DATEDIFF(YEAR,Baslamatarih,getdate()) >=5

UPDATE PERSONEL
SET Dept = 'SER', Unvan = 'MUDUR'
WHERE Perno = ( SELECT TOP 1 Perno FROM PERSONEL
WHERE Dept = 'SAT'
ORDER BY Baslamatarih ASC)

DELETE FROM PERSONEL
WHERE Perno = ( SELECT TOP 1 Perno FROM PERSONEL
WHERE Dept = 'SER'
ORDER BY Baslamatarih ASC)

UPDATE PERSONEL
SET Dept = 'NAK', Unvan = 'ELEMAN'
WHERE Dept is NULL

SELECT * FROM PERSONEL
WHERE Isim like '__'

SELECT * FROM PERSONEL
WHERE Soyad like '_____' AND Soyad like '%e%'

SELECT COUNT(*) AS CalisanSayisi FROM PERSONEL
WHERE Dept = 'SAT'

SELECT Dept AS Bolum,COUNT(*) AS CalisanSayisi FROM PERSONEL
GROUP BY Dept

SELECT AVG(Ucret) AS OrtalamaUcret FROM PERSONEL
WHERE Dept ='MUH'

SELECT Dept AS Bolum, AVG(Ucret) AS OrtalamaUcret FROM PERSONEL
GROUP BY Dept

SELECT TOP 1 Isim
FROM PERSONEL
WHERE Dept = 'ARG'
ORDER BY Baslamatarih ASC;

SELECT Unvan, COUNT(*) AS CalisanSayisi FROM PERSONEL
GROUP BY Unvan

SELECT Unvan, AVG(Ucret) AS OrtalamaUcret FROM PERSONEL
GROUP BY Unvan

SELECT * FROM PERSONEL
WHERE Dept = 'MUH' AND UCRET > ( SELECT UCRET FROM PERSONEL WHERE Isim = 'Recep' AND Dept = 'MUH' )

SELECT Isim, Dept, Unvan FROM PERSONEL
WHERE Dept = 'SAT' AND Baslamatarih < (SELECT Baslamatarih FROM PERSONEL WHERE Isim = 'Eda' AND Dept = 'SAT')

SELECT Isim FROM PERSONEL
WHERE Dept = 'ARG' AND Baslamatarih > (Select Baslamatarih FROM PERSONEL WHERE Isim = 'Ahmet' AND Dept = 'ARG')

SELECT DISTINCT Dept FROM PERSONEL

SELECT DISTINCT Unvan FROM PERSONEL

SELECT (SELECT AVG(Ucret) FROM PERSONEL WHERE Dept = 'MUH') - (SELECT AVG(Ucret) FROM PERSONEL WHERE Dept = 'ARG') AS UcretOrtalamasiFarki

SELECT Isim FROM PERSONEL
WHERE Ucret = (SELECT MAX(Ucret) FROM PERSONEL)

SELECT TOP 3 Isim,Ucret From Personel
ORDER BY Ucret DESC

SELECT Isim FROM PERSONEL
WHERE Ucret = (SELECT MIN(Ucret) FROM PERSONEL)

SELECT TOP 3 Isim, Ucret FROM PERSONEL
ORDER BY Ucret ASC