-- Ngo Tuan Anh --
CREATE DATABASE SE1431_HE141655

USE SE1431_HE141655

CREATE TABLE Departments(
    DeptID VARCHAR(4) NOT NULL PRIMARY KEY,
    Name NVARCHAR(50),
    NoOfStudents INT
);

CREATE TABLE Students(
    StudentID VARCHAR(4),
    LastName NVARCHAR(30),
    FirstName NVARCHAR(30),
    Sex VARCHAR(1),
    DateOfBirth DATE,
    PlaceOfBirth NVARCHAR(30),
    DeptID VARCHAR(4),
    Scholarship FLOAT,
    AverageScore NUMERIC(4,2),
    CONSTRAINT Check_Sex CHECK(Sex IN ('M','F'))
);

CREATE TABLE Courses(
    CourseID VARCHAR(4),
    Name NVARCHAR(35),
    Credits TINYINT
);

CREATE TABLE Results(
    StudentID VARCHAR(4),
    CourseID VARCHAR(4),
    Year INT,
    Semester INT,
    Mark FLOAT(1),
    Grade VARCHAR(6)
);

INSERT INTO Departments (DeptID, Name)
VALUES
('IS','Information Systems'),
('NC','Network and Communication'),
('SE','Software Engineering'),
('CE','Computer Engineering'),
('CS','Computer Science');

INSERT INTO Students (StudentID, LastName, FirstName, Sex, DateOfBirth, PlaceOfBirth, DeptID, Scholarship)
VALUES
('S001',N'Lê',N'Kim Lan','F','1990-02-23',N'Hà Nội','IS','130000'),
('S002',N'Trần',N'Minh Chánh','M','1992-12-24',N'Bình Định','NC','150000'),
('S003',N'Lê',N'An Tuyết','F','1991-02-12',N'Hải Phòng','IS','170000'),
('S004',N'Trần',N'Anh Tuấn','M','1993-12-20',N'TpHCM','NC','80000'),
('S005',N'Trần',N'Thị Mai','F','1991-08-12',N'TpHCM','SE','0'),
('S006',N'Lê',N'Thị Thu Thủy','F','1991-01-02',N'An Giang','IS','0'),
('S007',N'Nguyễn',N'Kim Thư','F','1990-02-02',N'Hà Nội','SE','180000'),
('S008',N'Lê',N'Văn Long','M','1992-12-08',N'TpHCM','IS','190000');

INSERT INTO Courses( CourseID, Name, Credits)
VALUES
('DS01','Database Systems','3'),
('AI01','Artificial Intelligence','3'),
('CN01','Computer Network','3'),
('CG01','Computer Graphics ','4'),
('DSA1','Data Structures and Algorithms','4');

INSERT INTO Results(StudentID, CourseID, Year, Semester, Mark)
VALUES
('S001','DS01','2017','1','3'),
('S001','DS01','2017','2','6'),
('S001','AI01','2017','1','4.5'),
('S001','AI01','2017','2','6'),
('S001','CN01','2017','3','5'),
('S002','DS01','2016','1','4.5'),
('S002','DS01','2017','1','7'),
('S002','CN01','2016','3','10'),
('S002','DSA1','2016','3','9'),
('S003','DS01','2017','1','2'),
('S003','DS01','2017','3','5'),
('S003','CN01','2017','2','2.5'),
('S003','CN01','2017','3','4'),
('S004','DS01','2017','3','4.5'),
('S004','DSA1','2018','1','10'),
('S005','DS01','2017','2','7'),
('S005','CN01','2017','2','2.5'),
('S005','CN01','2018','1','5'),
('S006','AI01','2018','1','6'),
('S006','CN01','2018','2','10');

-- Q2 --
UPDATE Departments
SET NoOfStudents=(SELECT COUNT(1) FROM Students s WHERE s.DeptID=Departments.DeptID);

-- Q3 --
CREATE TABLE avgscore(
	studentID VARCHAR(4) PRIMARY KEY,
	avgs NUMERIC(4,2)
)
INSERT INTO avgscore
SELECT a.StudentID, avg(MaxMarks)
FROM
	(SELECT StudentID, CourseID, max(Mark) MaxMarks
     FROM Results
     GROUP BY StudentID, CourseID) AS a GROUP BY a.StudentID

UPDATE Students SET AverageScore = avgscore.avgs FROM Students, avgscore WHERE Students.StudentID = avgscore.studentID;

-- Q4 --
UPDATE Result
SET Grade = 'Pass'
Where Mark >= '5' AND Mark <= '10';
UPDATE Result
SET Grade = 'Failed'
WHERE Mark >= '0' AND Mark < '5';

-- Q5 --
SELECT StudentID, Fullname = (FirstName + ' ' + LastName), DateOfBirth, PlaceOfBirth, DeptID, Schoolarship
FROM Students
WHERE Schoolarship > '160000'
ORDER BY Schoolarship DESC;

-- Q6 --
SELECT Departments.DeptID, DepartmentName = (Name), FirstName, LastName
FROM Departments
FULL OUTER JOIN Students
ON Departments.DeptID = Students.DeptID;

-- Q7 --
SELECT Students.StudentID, LastName, FirstName, COUNT(Courses.CourseID) AS NumberOfCourse
FROM Students, Courses, Results
WHERE Students.StudentID = Results.StudentID
	AND Courses.CourseID = Results.CourseID
GROUP BY Students.StudentID, LastName,FirstName
ORDER BY NumberOfCourse

-- Q8 --
SELECT Departments.DeptID, Departments.Name,
		SUM(CASE WHEN Sex = 'F' THEN 1 ELSE 0 END) AS NumberOfFemaleStudents,
		SUM(CASE WHEN Sex = 'M' THEN 1 ELSE 0 END) AS NumberOfMaleStudents
FROM Departments, Students
WHERE Departments.DeptID = Students.DeptID
GROUP BY Departments.DeptID, Departments.Name

-- Q9 --
SELECT DISTINCT Students.* FROM Students, Results
WHERE Students.StudentID = Results.StudentID
	AND Results.CourseID = 'DS01'
	AND Students.DeptID <> 'IS'
GROUP BY Students.StudentID, Students.LastName, Students.Firstname, Students.Sex, Students.DateOfBirth, Students.PlaceOfBirth
HAVING Mark > any (SELECT Mark FROM Results, Students
				WHERE Students.StudentID = Results.StudentID
				AND Results.CourseID = 'DS01'
				AND Students.DeptID LIKE 'IS')