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

-- Tao CSDL
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 & Table Alter
create nonclustered index NCI_Student_StudentName on Student (StudentName)
go

alter table Result
alter column Mark float
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

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, 'SA', '1993-08-05', 1),
(2, 'SB', '1993-08-05', 1),
(3, 'SC', '1993-08-05', 2),
(4, 'SD', '1993-08-05', 2),
(5, 'SE', '1993-08-05', 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 Operation
select StudentId 'Ma Sinh Vien', StudentName 'Ten Sinh Vien', BirthDate 'Ngay Sinh'
from Student
where BirthDate between '1992-10-10' and '1993-10-10'
go

select Class.ClassId 'Ma Lop', Class.ClassCode 'Ten Lop', count(Student.StudentId) 'Si So Lop'
from Class left join Student on Class.ClassId = Student.ClassId
group by Class.ClassId, Class.ClassCode
go

select Student.StudentId 'Ma Sinh Vien', Student.StudentName 'Ten Sinh Vien', sum(Result.Mark) 'Tong Diem'
from Student left join Result on Student.StudentId = Result.StudentId
group by Student.StudentId, Student.StudentName
having sum(Result.Mark) > 10
go

-- View
---- C1
create view view_StudentSubjectMark
as
select top(3) Student.StudentId, Student.StudentName, Subject.SubjectName, Result.Mark
from Student, Subject, Result
where Student.StudentId = Result.StudentId
	and Subject.SubjectId = Result.SubjectId
order by Result.Mark desc
go

select * from view_StudentSubjectMark

drop view view_StudentSubjectMark

---- C2
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

---- C3
create view view_StudentSubjectMark
as
select Student.StudentId, Student.StudentName, Subject.SubjectName, Result.Mark
from Student left join Result on Student.StudentId = Result.StudentId
	left join Subject on Subject.SubjectId = Result.SubjectId
go

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

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

select * from Result

exec up_IncreaseMark 2

-- 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 < 0'
		rollback transaction
	end
end


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

-- Trigger
create trigger TG_Subject_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 = 'AAA' where SubjectId = 1