CREATE DATABASE students_1;

USE students_1;

CREATE TABLE students(
student_id INT IDENTITY(1,1) PRIMARY KEY,
name VARCHAR(50),
age INT,
gender VARCHAR(10)
);

CREATE TABLE Courses(
course_id INT IDENTITY(1,1) PRIMARY KEY,
course_name VARCHAR(50)
);

CREATE TABLE Gradess(
grade_id INT IDENTITY(1,1) PRIMARY KEY,
student_id INT FOREIGN KEY REFERENCES students(student_id) ON UPDATE CASCADE ON DELETE CASCADE,
course_id INT FOREIGN KEY REFERENCES Courses(course_id) ON UPDATE CASCADE ON DELETE CASCADE,
grade_value INT
);
----------------------
USE students_1;

INSERT INTO students (name, age, gender)
VALUES
('Alice', 18, 'Female'),
('Bob', 20, 'Male'),
('Charlie', 19, 'Male'),
('Diana', 21, 'Female'),
('Ethan', 18, 'Male'),
('Fiona', 20, 'Female'),
('George', 22, 'Male'),
('Hannah', 19, 'Female'),
('Ian', 23, 'Male'),
('Julia', 21, 'Female');

INSERT INTO Courses (course_name)
VALUES
('Mathematics'),
('Physics'),
('Chemistry'),
('Biology'),
('Computer Science'),
('History'),
('Literature'),
('Economics'),
('Psychology'),
('Sociology');

INSERT INTO Gradess (student_id, course_id, grade_value)
VALUES
(1, 1, 85),
(2, 2, 92),
(3, 3, 78),
(4, 4, 95),
(5, 5, 88),
(6, 6, 90),
(7, 7, 77),
(8, 8, 84),
(9, 9, 91),
(10, 10, 86);
--------------------------
1)--Вывести средний балл студентов мужского и женского пола.
USE students_1
SELECT students.gender AS 'Пол', AVG(Gradess.grade_value) AS 'Средний балл'
FROM students
INNER JOIN Gradess
ON students.student_id = Gradess.student_id
GROUP BY students.gender;

2)--Вывести количество студентов на каждом курсе.
USE students_1
SELECT Courses.course_name AS 'Курс', COUNT(Gradess.student_id) AS student_count
FROM Courses
LEFT JOIN Gradess ON Courses.course_id = Gradess.course_id
GROUP BY Courses.course_name
ORDER BY student_count DESC;

3)--Вывести сумму оценок студента с ID=1 на каждом курсе.
USE students_1
SELECT Courses.course_name, SUM(Gradess.grade_value) AS total_grade
FROM Courses
JOIN Gradess ON Courses.course_id = Gradess.course_id
WHERE Gradess.student_id = 1
GROUP BY Courses.course_name;

4)--Вывести количество студентов, у которых средний балл выше 4.
--делал с помощью AI
USE students_1
SELECT COUNT(DISTINCT student_id) AS 'кол-во студентов'
FROM (SELECT student_id, AVG(grade_value) AS average_grade
FROM Gradess
GROUP BY student_id
HAVING AVG(grade_value) > 4
) AS student_averages;

5)--Вывести средний возраст студентов на каждом курсе.
USE students_1
SELECT Courses.course_name AS 'Курс', AVG(students.age) AS 'Средний возраст'
FROM Courses
LEFT JOIN Gradess ON Courses.course_id = Gradess.course_id
LEFT JOIN students ON Gradess.student_id = students.student_id
GROUP BY Courses.course_name;

6)--Вывести количество студентов на каждом курсе, у которых средний балл выше 3.
--делал с помощью AI
USE students_1
SELECT Courses.course_name AS 'Курс', COUNT(DISTINCT student_averages.student_id) AS 'Кол-во учеников'
FROM  Courses
LEFT JOIN Gradess ON Courses.course_id = Gradess.course_id
LEFT JOIN (SELECT student_id, AVG(grade_value) AS average_grade
FROM Gradess
GROUP BY student_id
HAVING
AVG(grade_value) > 3
) AS student_averages ON Gradess.student_id = student_averages.student_id
GROUP BY Courses.course_name;


7)--Вывести средний возраст студентов мужского и женского пола.
USE students_1
SELECT students.gender AS 'Пол', AVG(students.age) AS 'Средний возраст'
FROM students
GROUP BY students.gender;

8)--Вывести количество студентов с максимальным возрастом.
USE students_1
SELECT COUNT(*) AS 'Кол-во студентов'
FROM students
WHERE age = (SELECT MAX(age) FROM students);