SqlQueries.sql

--#HAMZA GÜNDÜZ# #gunduzhamza64@gmail.com#

--1. SORU
--POSITION TABLOSU
CREATE TABLE POSITION
(
ID INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
POSITION VARCHAR(200) NOT NULL
)
-- DEPARTMENT TABLOSU
CREATE TABLE DEPARTMENT
(
ID INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
DEPARTMENT VARCHAR(200)
)
-- PERSON TABLOSU
CREATE TABLE PERSON
(
ID INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
CODE VARCHAR(4),
TCNUMBER VARCHAR(11),
NAME_ NVARCHAR(100),
SURNAME_ NVARCHAR(100),
GENDER VARCHAR(1),
BIRTHDATE DATE,
INDATE DATE,
OUTDATE DATE,
DEPARTMENTID INT FOREIGN KEY REFERENCES DEPARTMENT(ID),
POSITIONID INT FOREIGN KEY REFERENCES POSITION(ID),
PARENTPOSITIONID INT FOREIGN KEY REFERENCES POSITION(ID),
MANAGERID INT FOREIGN KEY REFERENCES PERSON(ID),
TELNR VARCHAR(15),
SALARY TINYINT
)

--2. SORU
--Tabloyu kopyala yapýþtýr iþlemleri ile ekledikten sonra birkaç sql sorgusuyla excel karþýlaþtýrmasý yaptým. 

Select * from PERSON
select * from DEPARTMENT
Select * from POSITION
select * from person where ID=50 or ID=150
Select COUNT(ID) from PERSON
Select COUNT(DEPARTMENT) from DEPARTMENT
Select count(POSITION) from POSITION

--karþýlaþtýrmalarýn hepsi doðru sonucu verdi. Bundan sonraki iþlemlere sýra geldi.

--3. SORU
SELECT * FROM PERSON WHERE OUTDATE IS NULL

--4. SORU (Bu soru join ve subquery ile birlikte yapýlmýþtýr)
Select d.DEPARTMENT,
case 
	when p.GENDER='E' then 'ERKEK'   --CASE YAPISINI KULLANARAK HARFLER YERÝNE KADIN ERKEK KELÝMELERÝNÝ...
	when p.GENDER='K' then 'KADIN'	 --KULLANARAK YENÝ BÝR SÜTUN OLUÞTURDUM.
	end AS GENDER,
count(p.ID) PERSONCOUNT
from DEPARTMENT d inner join PERSON p on 
d.ID=p.DEPARTMENTID
WHERE OUTDATE IS NULL
group by p.GENDER,d.DEPARTMENT --DEPARTMANDAKÝ CÝNSÝYET SAYILARINA GÖRE SORGU ÝSTENDÝÐÝ ÝÇÝN DEPARTMAN VE CÝNSÝYETÝ GRUPLAÞTIRDIM

--Subquery ile 
Select 
(Select DEPARTMENT from DEPARTMENT d where d.ID=p.DEPARTMENTID) as DEPARTMENT,
case 
	when p.GENDER='E' then 'ERKEK'
	when p.GENDER='K' then 'KADIN'
end GENDER,
count(p.DEPARTMENTID) PERSONCOUNT
from person p
WHERE OUTDATE IS NULL
group by p.GENDER,p.DEPARTMENTID
order by department

--5. SORU
Select DEPARTMENT,
(Select count(*) from person where d.ID=DEPARTMENTID and GENDER='E' and OUTDATE is null) MALE_PERSONCOUNT,
(Select count(*) from person where d.ID=DEPARTMENTID and GENDER='K' and OUTDATE is null) FEMALE_PERSONCOUNT
from DEPARTMENT d
order by DEPARTMENT


--6. SORU (Bu soru join ve subquery ile birlikte yapýlmýþtýr)
Select
(SELECT POSITION FROM POSITION WHERE P.POSITIONID=ID) POSITION,
MIN(SALARY) MINSALARY,
MAX(SALARY) MAXSALARY,
CEILING(AVG(SALARY)) AVGSALARY
from person P WHERE POSITIONID=(SELECT ID FROM POSITION WHERE POSITION='PLANLAMA ÞEFÝ')
GROUP BY P.POSITIONID

--JOIN ÝLE
SELECT 
PST.POSITION,
MIN(SALARY) MINSALARY,
MAX(SALARY) MAXSALARY,
CEILING(AVG(SALARY)) AVGSALARY
FROM POSITION PST INNER JOIN PERSON P ON PST.ID=P.POSITIONID
WHERE PST.POSITION='PLANLAMA ÞEFÝ'
GROUP BY PST.POSITION

--7. SORU (Bu soru join ve subquery ile birlikte yapýlmýþtýr)

SELECT 
(SELECT POSITION FROM POSITION WHERE P.POSITIONID=ID) AS POSITION,
COUNT(POSITIONID) PERSONCOUNT,
CEILING(AVG(SALARY)) AVG_SALARY
FROM PERSON P
WHERE OUTDATE IS NULL
GROUP BY POSITIONID
ORDER BY POSITION 

--JOIN ÝLE 
SELECT PST.POSITION,
COUNT(PST.POSITION) PERSONCOUNT,
CEILING(AVG(SALARY)) AVD_SALARY
FROM PERSON P INNER JOIN POSITION PST
ON P.POSITIONID=PST.ID
WHERE P.OUTDATE IS NULL
GROUP BY PST.POSITION
ORDER BY PST.POSITION

--8. SORU
SELECT TMP.YEAR_,
(SELECT COUNT(GENDER) FROM PERSON WHERE GENDER='E' AND YEAR(INDATE)=YEAR_) MALE_PERSON, 
(SELECT COUNT(GENDER) FROM PERSON WHERE GENDER='K' AND YEAR(INDATE)=YEAR_) FEMALE_PERSON
FROM
(SELECT
CASE WHEN YEAR(INDATE)='2015' THEN 2015
WHEN YEAR(INDATE)='2016' THEN 2016
WHEN YEAR(INDATE)='2017' THEN 2017		--CASE YAPISINI KULLARAK GÝRÝÞ TARÝHLERÝNÝN SADECE YILINI GÖSTEREN BÝR SÜTUN EKLEDÝM.
WHEN YEAR(INDATE)='2018' THEN 2018
WHEN YEAR(INDATE)='2019' THEN 2019
END YEAR_,GENDER
FROM PERSON) TMP
GROUP BY YEAR_		--YILLARA GÖRE HESAP YAPILMASI GEREKTÝÐÝ ÝÇÝN YENÝ EKLEDÝÐÝM SÜTUNU GRUPLAÞTIRDIM.
ORDER BY YEAR_

--9. SORU
SELECT
TMP.PERSON,CAST(TMP.INDATE AS DATE) INDATE,
CAST(TMP.OUTDATE AS DATE) OUTDATE,
DATEDIFF(MONTH,TMP.INDATE,TMP.OUTD) AS WORKINGTIME --DATEDIFF FONSÝYONUNU KULLANARAK HESAPLAMAYI YAPTIM
FROM
(Select NAME_ +' ' + SURNAME as PERSON,
INDATE, OUTDATE,
CASE 
WHEN OUTDATE IS NULL THEN GETDATE()		--CASE YAPISINI KULLANARAK ÇIKIÞ TARÝHÝ NULL OLANLARI GETDATE() ÝLE DEÐÝÞTÝRDÝM 
WHEN OUTDATE IS NOT NULL THEN OUTDATE	--YENÝ OLUÞAN SÜTÜNDA ÇIKIÞ TARÝHÝNDE NULL ÝFADE KALMADI.
END OUTD
from PERSON) AS TMP

--10. SORU
SELECT 
SUBSTRING(NAME_,1,1)+'.'+SUBSTRING(SURNAME,1,1) AS SHORTNAME,
COUNT(SUBSTRING(NAME_,1,1)+'.'+SUBSTRING(SURNAME,1,1)) PERSONCOUNT
FROM PERSON
GROUP BY SUBSTRING(NAME_,1,1)+'.'+SUBSTRING(SURNAME,1,1)
ORDER BY COUNT(SUBSTRING(NAME_,1,1)+'.'+SUBSTRING(SURNAME,1,1)) DESC

--11. SORU (Bu soru join ve subquery ile birlikte yapýlmýþtýr)
SELECT 
(SELECT DEPARTMENT FROM DEPARTMENT WHERE ID=P.DEPARTMENTID) DEPARTMENT,
ROUND(AVG(SALARY),0) AVGSALARY FROM PERSON P
GROUP BY DEPARTMENTID
HAVING AVG(SALARY)>5500
ORDER BY DEPARTMENT

--JOIN ÝLE
SELECT 
DPT.DEPARTMENT,ROUND(AVG(SALARY),0) AS AVGSALARY
FROM DEPARTMENT DPT INNER JOIN PERSON P
ON P.DEPARTMENTID=DPT.ID
GROUP BY DPT.DEPARTMENT
HAVING AVG(SALARY)>5500

--12. SORU
SELECT 
TMP.DEPARTMENT, 
AVG(DATEDIFF(MONTH,TMP.INDATE,TMP.OUTDTH)) AVG_WORKINGTIME 
FROM
(Select 
(SELECT DEPARTMENT FROM DEPARTMENT WHERE P.DEPARTMENTID=ID) DEPARTMENT,
INDATE,OUTDATE,
CASE
	WHEN OUTDATE IS NULL THEN GETDATE()			--9. SORUYA BENZER OLDUÐU ÝÇÝN 9. SORUDA YAPILAN CASE ÝÞLEMÝNÝN AYNISI YAPILDI
	WHEN OUTDATE IS NOT NULL THEN OUTDATE
	END OUTDTH
from PERSON P) TMP
GROUP BY TMP.DEPARTMENT


--13. SORU
SELECT
NAME_+' '+SURNAME AS PERSON,
(SELECT POSITION FROM POSITION WHERE ID=P.POSITIONID) AS POSITION,
(SELECT NAME_+' '+SURNAME FROM PERSON WHERE ID=P.MANAGERID) as MANAGER,
(Select POSITION from POSITION where ID=P.PARENTPOSITIONID) AS MANAGERPOSITION
FROM PERSON P
where MANAGERID is not null