use Lab1 -- SELECT AVG Fund MAX Fund MIN Fund AS Average_Fund FROM Dep

use [Lab1.3]

-- 1)
SELECT AVG(Fund) + MAX(Fund) / MIN(Fund) AS Average_Fund
FROM Department

-- 2)
SELECT	COUNT(DISTINCT Department.DepPK) AS [Deprtments Count],
		COUNT(Teacher.TchPK) AS [Teachers Count],
		AVG(Teacher.Salary + Teacher.Commision) AS [Average Salary]
FROM Faculty, Department, Teacher 
WHERE	Faculty.[Name] = 'Informatics' AND
		Faculty.FacPK = Department.FacFK AND
		Department.DepPK = Teacher.DepFK
		

-- 3)
SELECT DISTINCT Room.Building
FROM Department, Teacher T1, Teacher T2, Faculty, Lecture, Room
WHERE	T1.[Name] = 'Ada' AND
		T1.TchPK = Faculty.DekanFK AND
		Faculty.FacPK= Department.FacFK AND
		T2.DepFK = Department.DepPK AND
		T2.TchPK = Lecture.TchFK AND
		Lecture.RoomFK = Room.RoomPK

-- 4)
SELECT	D2.[Name] AS [Кафедра],
		D2.HeadFK AS [Заведующий],
		COUNT(DISTINCT T2.TchPK) AS [К-во преподавателей],
		COUNT(DISTINCT Sgroup.GrpPK) AS [Кол-во групп]
FROM Faculty, Department D1, Department D2, Teacher T1, Teacher T2, Sgroup
WHERE	T1.[Name] = 'Ada' AND
		T1.DepFK = D1.DepPK AND
		D1.FacFK = Faculty.FacPK AND
		D2.FacFK = Faculty.FacPK AND
		T2.DepFK = D2.DepPK AND
		Sgroup.DepFK = D2.DepPK
GROUP BY D2.[Name], D2.HeadFK


-- 5)
SELECT	SUBSTRING(Teacher.[Name], 1, 1) AS [Б],
		COUNT(Teacher.TchPK) AS [Кол-в преподавателей],
		SUM(Salary + Commision) AS [Суммарная зарплата]
FROM Department, Teacher
WHERE	Department.[Name] = 'CAD' AND
		Teacher.DepFK = Department.DepPK AND
		Teacher.Hiredate BETWEEN '20000101' AND '20071231'
GROUP BY SUBSTRING(Teacher.[Name], 1, 1)