mssql_basic_queries.sql

-- ERSEL KIZMAZ - ersel.kizmaz@gmail.com

--				////////// Microsoft SQL Queries \\\\\\\\\\

/* 
SORU 1: HR isimli database yaratma ve 'HR.xlsx' dosyası icerisindeki tabloları sql sorguları ile olusturma 
*/

CREATE DATABASE HR;
USE HR;

-- position tablosunu olusturma
CREATE TABLE POSITION (
	ID SMALLINT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
	POSITION NVARCHAR(40) NOT NULL
	);

-- department tablosunu olusturma
CREATE TABLE DEPARTMENT (
	ID SMALLINT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
	DEPARTMENT NVARCHAR(40) NOT NULL
	);

-- person tablosunu olusturma
CREATE TABLE PERSON (
	ID INT IDENTITY(1, 1) NOT NULL,
	CODE INT NOT NULL,
	TCNUMBER CHAR(11) NOT NULL,
	NAME_ NVARCHAR(40) NOT NULL,
	SURNAME NVARCHAR(40) NOT NULL,
	GENDER CHAR(1),
	BIRTHDATE DATE NOT NULL,
	INDATE DATE NOT NULL,
	OUTDATE DATE,
	DEPARTMENTID SMALLINT NOT NULL,
	POSITIONID SMALLINT NOT NULL,
	PARENTPOSITIONID SMALLINT NOT NULL,
	MANAGERID SMALLINT,
	TELNR VARCHAR(30) NOT NULL,
	SALARY SMALLMONEY NOT NULL,

	CONSTRAINT pk_person PRIMARY KEY (ID),
	CONSTRAINT fk_person_position
		FOREIGN KEY (POSITIONID)
			REFERENCES POSITION (ID),
	CONSTRAINT fk_person_department
		FOREIGN KEY (DEPARTMENTID)
			REFERENCES DEPARTMENT (ID),
	);

/* 
SORU 2: Excel dosyasındaki kayıtlı verileri kopyala-yapıstır yöntemi ile veritabanına ekleme 
*/

-- veriler kopyala-yapıstır yöntemi ile eklendikten sonra konrol islemleri,
SELECT * FROM POSITION;
SELECT * FROM DEPARTMENT;
SELECT TOP 10 * FROM PERSON; -- ilk 10 veriyi inceleme

SELECT COUNT(*) FROM PERSON; -- toplam 1780 veri
SELECT * FROM PERSON WHERE ID = 22; -- excel dosyasındaki veri ile uyusuyor

/*
SORU 3: Şirketimizde halen çalışmaya devam eden çalışanların listesini getiren sorguyu yazınız.
		NOT: İşten çıkış tarihi boş olanlar çalışmaya devam eden çalışanlardır.
*/

SELECT * FROM PERSON WHERE OUTDATE IS NULL;

/* 
SORU 4: Şirketimizde departman bazlı halen çalışmaya devam eden KADIN ve ERKEK sayılarını getiren sorguyu yazınız. 
*/

SELECT d.DEPARTMENT,
	CASE 
		WHEN  p.GENDER = 'E' THEN 'Erkek'
		WHEN  p.GENDER = 'K' THEN 'Kadın'
	END GENDER, 
	COUNT(p.DEPARTMENTID) AS PERSONCOUNT
FROM DEPARTMENT AS d
INNER JOIN PERSON AS p ON p.DEPARTMENTID = d.ID
WHERE p.OUTDATE IS NULL
GROUP BY p.GENDER, d.DEPARTMENT
ORDER BY 1, 2;

/*
SORU 5: Şirketimizde departman bazlı halen çalışmaya devam eden 
KADIN ve ERKEK sayılarını ayrı columnlar halinde getiren sorguyu yazınız.
*/

SELECT d.DEPARTMENT,
	(SELECT COUNT(*) FROM PERSON AS p 
	 WHERE p.DEPARTMENTID = d.ID AND p.GENDER = 'E' AND p.OUTDATE IS NULL) AS MALE_PERSONCOUNT,
	(SELECT COUNT(*) FROM PERSON AS p 
	 WHERE p.DEPARTMENTID = d.ID AND p.GENDER = 'K' AND p.OUTDATE IS NULL) AS FEMALE_PERSONCOUNT
FROM DEPARTMENT AS d
ORDER BY d.DEPARTMENT;

/*
SORU 6: Şirketimizin Planlama departmanına yeni bir şef ataması yapıldı ve maaşını belirlemek istiyoruz.
Planlama departmanı için minimum,maximum ve ortalama şef maaşını getiren sorguyu yazınız.
NOT: İşten çıkmış olan personel maaşları da dahildir.
*/

SELECT po.POSITION, 
	MIN(p.SALARY) AS MIN_SALARY, 
	MAX(p.SALARY) AS MAX_SALARY,
	AVG(p.SALARY) AS AVG_SALARY
FROM POSITION AS po
INNER JOIN PERSON AS p ON p.POSITIONID = po.ID
WHERE po.POSITION = 'PLANLAMA ŞEFİ'
GROUP BY po.POSITION;

/*
SORU 7: Her bir pozisyonda mevcut halde çalışanlar olarak 
kaç kişi ve ortalama maaşlarının ne kadar olduğunu listelettirmek istiyoruz. 
Bu sonucu getiren sorguyu yazınız.
*/

SELECT po.POSITION, COUNT(po.ID) AS PERSONCOUNT, AVG(p.SALARY) AS AVG_SALARY
FROM POSITION AS po
INNER JOIN PERSON AS p ON p.POSITIONID = po.ID
WHERE p.OUTDATE IS NULL
GROUP BY po.POSITION
ORDER BY po.POSITION ASC;

/*
SORU 8: Yıllara göre işe alınan personel sayısını
kadın ve erkek bazında listelettiren sorguyu yazınız.
*/

SELECT YEAR(INDATE) AS YEAR_,
	(SELECT COUNT(*) FROM PERSON WHERE GENDER = 'E' AND YEAR(PERSON.INDATE) = YEAR(p.INDATE)) AS MALE_PERSON,
	(SELECT COUNT(*) FROM PERSON WHERE GENDER = 'K' AND YEAR(PERSON.INDATE) = YEAR(p.INDATE)) AS FEMALE_PERSON
FROM PERSON AS p
GROUP BY YEAR(INDATE)
ORDER BY 1;

/*
SORU 9: Her bir personelimizin ne kadar zamandır 
çalıştığı bilgisini ay olarak getiren sorguyu yazınız.
*/

-- sorgunun yazıldıgı tarihe göre islem yapıldı! (2022-09-05)
SELECT
	CONCAT(NAME_, ' ', SURNAME) AS PERSON,
	INDATE, OUTDATE,
	DATEDIFF(MONTH, INDATE, GETDATE()) AS WORKINGTIME
FROM PERSON; -- concat fonksiyonu string birlestirme icin kullanıldı

/*
SORU 10: Şirketimiz 5. yılında üstünde herkesin isminin ve soyisminin baş harflerinin 
bulunduğu bir ajanda bastırıp çalışanlarına hediye edecektir. Bunun için hangi harf 
kombinasyonundan en az ne kadar sayıda bastırılacağı sorusunun cevabını getiren sorguyu yazınız.
NOT: İki isimli olanların birinci isminin baş harfi kullanılacaktır.
*/

SELECT tmp.SHORTNAME, COUNT(*) AS PERSONCOUNT 
FROM (
	SELECT CONCAT(LEFT(NAME_, 1), '.', LEFT(SURNAME, 1), '.') AS SHORTNAME
	FROM PERSON) AS tmp
GROUP BY tmp.SHORTNAME
ORDER BY 2 DESC;

/*
SORU 11: Maaş ortalaması 5.500 TL’den fazla olan departmanları listeleyecek sorguyu yazınız.
*/

SELECT d.DEPARTMENT, AVG(p.SALARY) AS AVG_SALARY
FROM DEPARTMENT AS d
INNER JOIN PERSON AS p ON p.DEPARTMENTID = d.ID
GROUP BY d.DEPARTMENT
HAVING AVG(p.SALARY) > 5500
ORDER BY d.DEPARTMENT;

/*
SORU 12: Departmanların ortalama kıdemini ay olarak hesaplayacak sorguyu yazınız.
*/
-- sorgunun yazıldıgı tarihe göre islem yapıldı! (2022-09-05)
SELECT d.DEPARTMENT, AVG(DATEDIFF(MONTH, p.INDATE, GETDATE())) AS AVG_WORKINGTIME
FROM DEPARTMENT AS d
INNER JOIN PERSON AS p ON p.DEPARTMENTID = d.ID
GROUP BY d.DEPARTMENT;

/*
SORU 13: Her personelin adını, pozisyonunu bağlı olduğu 
birim yöneticisinin adını ve pozisyonunu getiren sorguyu yazınız.
*/

SELECT TOP 20
	CONCAT(p.NAME_, ' ', p.SURNAME) AS PERSON,
	po.POSITION,
	CONCAT(p1.NAME_, ' ', p1.SURNAME) AS MANAGER,
	po1.POSITION AS MANAGER_POSITION
FROM PERSON AS p
INNER JOIN PERSON AS p1 ON p1.ID = p.MANAGERID
INNER JOIN POSITION AS po ON po.ID = p.POSITIONID
INNER JOIN POSITION AS po1 ON po1.ID = p1.POSITIONID;