[Examination] Hệ thống quản lý sinh viên - SQL Server

-- Tao CSDL: BT1800
create database BT1800
go

-- Kich Hoat CSDL
use BT1800
go

-- Tao tables
create table Class (
	ClassId int not null,
	ClassCode nvarchar(50)
)
go

create table Student (
	StudentId int not null,
	StudentName nvarchar(50),
	BirthDate date,
	ClassId int -- 1 sinh vien chi dc hoc 1 lop
)
go

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

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

-- Index & Alter
create nonclustered index NCI_Student_StudentName on Student (StudentName)
go

alter table Result
alter column Mark float
go

-- Constraint
alter table Class
add constraint PK_Class primary key (ClassId)
go

alter table Student
add constraint PK_Student primary key (StudentId)
go

alter table Subject
add constraint PK_Subject primary key (SubjectId)
go

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

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

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

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

-- Check
alter table Subject
add constraint CK_Subject_SessionCount check (SessionCount > 0)
go

-- Insert Data
insert into Class (ClassId, ClassCode)
values
(1, 'A'),
(2, 'B'),
(3, 'C'),
(4, 'D'),
(5, 'E')
go

insert into Student (StudentId, StudentName, BirthDate, ClassId)
values
(1, 'SV A', '1993-08-05', 1),
(2, 'SV B', '1992-06-10', 1),
(3, 'SV C', '1992-09-07', 2),
(4, 'SV D', '1993-10-10', 2),
(5, 'SV E', '1992-06-06', 3)
go

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

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)
go

-- Query
select StudentId, StudentName, BirthDate
from Student
where BirthDate between '1992-10-10' and '1993-10-10'
go

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
go

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
order by TotalMark desc
go

-- View
create view view_StudentSubjectMark
as
select Student.StudentId, Student.StudentName, Subject.SubjectName, Result.Mark
from Student, Subject, Result
where Student.StudentId = Result.StudentId
	and Subject.SubjectId = Result.SubjectId
go

select top(3) * from view_StudentSubjectMark
order by Mark desc
go

-- Proc
create proc up_IncreaseMark
	@SubjectId int
as
begin
	update Result set Mark = Mark + 1 where SubjectId = @SubjectId
end

select * from Result

exec up_IncreaseMark 3

-- Trigger
create trigger TH_Result_Insert on Result
for insert
as
begin
	if (select count(*) from inserted where mark < 0) > 0
	begin
		print N'Can"t insert mark < 0'
		rollback transaction
	end
end

insert into Result(StudentId, SubjectId, Mark)
values
(1, 3, -2)
go

-- Trigger (UPDATE)
create trigger TG_Sujbect_Update on Subject
for update
as
begin
	if update (SubjectName)
	begin
		print N'You don"t update this column'
		rollback transaction
	end
end

update Subject set SubjectName = 'Lap Trinh C' where SubjectId = 1
update Subject set SubjectName = 'Lap Trinh C'