Tạo database & bảng (tables)
-- 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')
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)
insert into Subject(SubjectId, SubjectName, SessionCount)
values
(1 ,'C Programming', 22),
(2 ,'Web Design', 18),
(3 ,'Database Management', 23)
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)
-- 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
