[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 datetime,
	ClassId int
)
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
create nonclustered index NCI_Student_StudentName on Student (StudentName)
go

-- Constraint
---- Primary Key
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, 'Sinh Vien A', '1993-08-05', 1),
(2, 'Sinh Vien B', '1992-06-10', 1),
(3, 'Sinh Vien C', '1992-09-07', 2),
(4, 'Sinh Vien D', '1993-10-10', 2),
(5, 'Sinh Vien 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 'MaSinhVien', StudentName 'TenSinhVien', BirthDate 'NgaySinh'
from Student
where BirthDate between '1992-10-10' and '1993-10-10'
go

select Class.ClassId, Class.ClassCode, count(Student.StudentId) 'SiSo'
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) 'TongDiem'
from Student left join Result on Student.StudentId = Result.StudentId
group by Student.StudentId, Student.StudentName
having sum(Result.Mark) > 10
order by 'TongDiem' 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

exec up_IncreaseMark 2
go

-- Trigger
create trigger TG_Result_insert on Result
for insert
as
begin
	if (select count(*) from inserted where Mark < 0) > 0
	begin
		print N'Can not insert mark less than 0'
		rollback transaction
	end
end

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

-- Trigger update
create trigger TG_Subject_update on Subject
for update --update event -> khi chay update table set ... -> trigger se dc bat len
as
begin
	if update(SubjectName) --function: Kiem tra xem column SubjectName -> Co bi thay doi du lieu trong cau lenh update ko
	begin
		print N'You don"t update this column'
		rollback transaction
	end
end

update Subject set SubjectName = 'ABC' where SubjectId = 2
go