Untitled
unknown
plain_text
5 months ago
3.8 kB
4
Indexable
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
Editor is loading...
Leave a Comment