Laba_4_DB

--1)
SELECT COUNT(Student_Id) as 'students'
FROM Exam_mark
WHERE Subj_Id = 2

--2)
SELECT COUNT(DISTINCT Subj_Id) as 'Количество дисциплин'
FROM Exam_mark;

--3)
SELECT Student_Id, MIN(Mark) as 'Мин оценка'
FROM Exam_mark
GROUP BY Student_Id;

--4)
SELECT Student_Id, MAX(Mark) as 'Макс оценка'
FROM Exam_mark
GROUP BY Student_Id;

--5)
SELECT Subj_Id, MIN(Mark) as 'Мин оценка'
FROM Exam_mark
GROUP BY Subj_Id;

--6)
SELECT Exam_Date, COUNT(Student_Id) as 'Кол-во студентов'
FROM Exam_mark
GROUP BY Exam_Date;

--7)
SELECT Subj_Id, AVG(Mark) as 'Средний балл'
FROM Exam_mark
GROUP BY Subj_Id

--8)
SELECT Student_Id, AVG(Mark) as 'Средний балл'
FROM Exam_mark
GROUP BY Student_Id

--9)
SELECT COUNT(DISTINCT Subj_Id) as 'Кол-во дисциплин'
FROM Exam_mark
WHERE Mark>4

------------------------------------------------------------------
--1)*
SELECT Student_id, Surname, Stipendia,(Stipendia*120)/100 AS 'Стипендия +20%'
FROM Student
ORDER BY Stipendia, Surname

--2)
SELECT TOP (2) Surname
FROM Student WHERE Surname like 'П%'
ORDER BY Surname

--3)
SELECT Student_id, max(Mark) as 'Макс. оценка' , min(Mark) as 'Мин. оценка'
FROM Exam_mark
GROUP BY Student_Id

--4)*
SELECT Subj_Name, Hour
FROM Subject
ORDER BY Subj_Name DESC, hour asc

--5)
SELECT Exam_Date, Sum(Mark) as 'Сумма баллов'
FROM Exam_mark
GROUP BY Exam_Date
ORDER BY Sum(Mark) DESC

--6)*
SELECT AVG(Mark) as 'Средняя оценка',MIN(Mark) as 'Минимальная оценка',MAX(Mark) as 'Максимальная оценка', Exam_Date
FROM Exam_mark
GROUP BY Exam_Date
ORDER BY 1 DESC, 2 desc, 3 desc