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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
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)