2021-04-13 13:06:14

-- 1) Database
---- Tao database
create database StudentManagementSystem

---- Kich hoat database
use StudentManagementSystem

-- 2) Tables
---- Table Class
create table Class (
	ClassId int not null,
	ClassCode nvarchar(50)
)

create table Student (
	StudentId int not null,
	StudentName nvarchar(50),
	birthdate datetime,
	ClassId int
)

create table Subject (
	SubjectId int not null,
	SubjectName nvarchar(50),
	SessionCount int
)

create table Result (
	StudentId int not null,
	SubjectId int not null,
	Mark int
)

-- 3) Index Creation and Table Alternation
create nonclustered index NCI_Student_StudentName on Student (StudentName)

alter table Result
alter column Mark float

-- 4) Constraint
---- Primary Key
alter table Class
add constraint PK_Class primary key (ClassId)

alter table Student
add constraint PK_Student primary key (StudentId)

alter table Subject
add constraint PK_Subject primary key (SubjectId)

alter table Result
add constraint PK_Result primary key (StudentId, SubjectId)

---- Foreign Key
alter table Student
add constraint FK_Student_Class foreign key(ClassId) references Class(ClassId)

alter table Result
add constraint FK_Result_Student foreign key(StudentId) references Student(StudentId)

alter table Result
add constraint FK_Result_Subject foreign key(SubjectId) references Subject(SubjectId)

---- CHECK
alter table Subject
add constraint CK_Subject_SessionCount check (SessionCount >= 0)

-- 5) Insert Data
insert into Class(ClassId, ClassCode)
values
(1 ,'C1106KV'),
(2 ,'C1108GV'),
(3 ,'C1108IV'),
(4 ,'C1108HV'),
(5 ,'C1109GV')
select * from Class

insert into Student(StudentId, StudentName, BirthDate, ClassId)
values
(1 ,'Pham Tuan Anh', '1993-08-05', 1),
(2 ,'Phan Van Huy', '1992-06-10', 1),
(3 ,'Nguyen Hoang Minh', '1992-09-07', 2),
(4 ,'Tran Tuan Tu', '1993-10-10', 2),
(5 ,'Do Anh Tai', '1992-06-06', 3)
select * from Student

insert into Subject(SubjectId, SubjectName, SessionCount)
values
(1 ,'C Programming', 22),
(2 ,'Web Design', 18),
(3 ,'Database Management', 23)
select * from Subject

insert into Result(StudentId, SubjectId, Mark)
values
(1, 1, 8),
(1, 2, 7),
(2, 3, 5),
(3, 2, 6),
(4, 3, 9),
(5, 2, 8)
select * from Result

-- 6) Query
---- Search students whose BirthDate is between from ‘1992-10-10’ and ‘1993-10-10’. Display info
------ of StudentId, StudentName, DateOfBirth.
select StudentId, StudentName, BirthDate 'DateOfBirth'
from Student
where BirthDate between '1992-10-10' and '1993-10-10'

---- Count students in each class with information of ClassId, ClassCode, TotalStudent
select Class.ClassId, Class.ClassCode, count(Student.StudentId) 'TotalStudent'
from Class left join Student on Class.ClassId = Student.ClassId
group by Class.ClassId, Class.ClassCode

---- Sum up mark of all subjects for each student, then display only students whose sum of mark is
------ greater than 10. Display info of: StudentId, StudentName, TotalMark
select Student.StudentId, Student.StudentName, sum(Result.Mark) 'TotalMark'
from Student left join Result on Student.StudentId = Result.StudentId
group by Student.StudentId, Student.StudentName
having sum(Result.Mark) > 10

-- 7) Views
---- Create a view named view_StudentSubjectMark which should show result of each student by
------ each subject, with information of: StudentId, StudentName, SubjectName, Mark.
create view view_StudentSubjectMark
as
select top(3) Student.StudentId, Student.StudentName, Subject.SubjectName, Result.Mark
from Student left join Result on Student.StudentId = Result.StudentId
	left join Subject on Result.SubjectId = Subject.SubjectId
order by Result.Mark desc

select * from view_StudentSubjectMark

-- 8) Proc
---- Create a procedure named up_IncreaseMark, with input parameters:
create proc up_IncreaseMark
	@subjectId int
as
begin
	update Result set Mark = Mark + 1 where SubjectId = @subjectId
end

select * from Result
exec up_IncreaseMark 2
select * from Result