Thiết kế hệ quản trị CSDL - Quản lý sinh viên (create, alter, update, insert, select) - Lập trình SQL Server

-- Tao CSDL
create database StudentManagementSystem
go

-- Kich hoat CSDL
use StudentManagementSystem
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

-- Thay doi kieu du lieu Mark (Result) tu int -> float
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

-- Insert du lieu
insert into Class (ClassId, ClassCode)
values
(1, 'C1106KV'),
(2, 'C1108GV'),
(3, 'C1108IV'),
(4, 'C1108HV'),
(5, 'C1109GV')
go

insert into Student (StudentId, StudentName, birthdate, ClassId)
values
(1, N'Phạm Tuấn Anh', '1993-08-05', 1),
(2, N'Phạm Văn Huy', '1992-06-10', 1),
(3, N'Nguyễn Hoàng Minh', '1992-09-07', 2),
(4, N'Trần Tuấn Tú', '1993-10-10', 2),
(5, N'Đỗ Anh Tài', '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

-- Test data
select * from Class
select * from Student
select * from Subject
select * from Result

-- Hien thi StudentId (Student), StudentName (Student), DateOfBirtd (Student)
select StudentId N'Mã Sinh Viên', StudentName N'Tên Sinh Viên', BirthDate N'Ngày Sinh' from Student
go

-- Hiển thị thông tin dữ liệu: ClassId (Class), ClassName (Class), StudentId (Student), StudentName (Student)
select Class.ClassId, Class.ClassCode, Student.StudentId, Student.StudentName
from Class, Student
where Class.ClassId = Student.ClassId
go

-- Đếm số sinh viên trong 1 lớp học (ClassId, ClassName, Sĩ Số Học Sinh)
---- 1 C1106KV 2
---- 2 C1108GV 2
---- 3 C1108IV 1
select Class.ClassId, Class.ClassCode, count(Student.StudentId) N'Sĩ Số Học Sinh'
from Class, Student
where Class.ClassId = Student.ClassId
group by Class.ClassId, Class.ClassCode
go

select Class.ClassId, Class.ClassCode, count(Student.StudentId) N'Sĩ Số Học Sinh'
from Class, Student
where Class.ClassId = Student.ClassId
group by Class.ClassId, Class.ClassCode
order by N'Sĩ Số Học Sinh' asc
go

select Class.ClassId, Class.ClassCode, count(Student.StudentId) N'Sĩ Số Học Sinh'
from Class, Student
where Class.ClassId = Student.ClassId
group by Class.ClassId, Class.ClassCode
order by N'Sĩ Số Học Sinh' desc
go

-- group by: count, sum, avg, max, min
select * from student
select * from result

---- Test hien thi: StudentId, StudentName, Mark
select Student.StudentId, Student.StudentName, Result.Mark
from Student, Result
where Student.StudentId = Result.StudentId
go

select Student.StudentId, Student.StudentName, sum(Result.Mark) as N'Tổng Điểm'
from Student, Result
where Student.StudentId = Result.StudentId
group by Student.StudentId, Student.StudentName
having sum(Result.Mark) > 10
go

select Student.StudentId, Student.StudentName, avg(Result.Mark) as N'Điểm Trunh Bình'
from Student, Result
where Student.StudentId = Result.StudentId
group by Student.StudentId, Student.StudentName
go

select Student.StudentId, Student.StudentName, max(Result.Mark) as N'Điểm MAX'
from Student, Result
where Student.StudentId = Result.StudentId
group by Student.StudentId, Student.StudentName
go

select Student.StudentId, Student.StudentName, min(Result.Mark) as N'Điểm MIN'
from Student, Result
where Student.StudentId = Result.StudentId
group by Student.StudentId, Student.StudentName
go

---- Test hien thi: StudentId (Student), StudentName (Student), SubjectName (Subject), Mark (Result)
select Student.StudentId, Student.StudentName, Subject.SubjectName, Result.Mark
from Student, Result, Subject
where Student.StudentId = Result.StudentId
	and Subject.SubjectId = Result.SubjectId
go

-- Tim hieu Join: inner join -> left table: Class, right table: Student
select Class.ClassId, Class.ClassCode, Student.StudentId, Student.StudentName
from Class, Student
where Class.ClassId = Student.ClassId
go

select Class.ClassId, Class.ClassCode, Student.StudentId, Student.StudentName
from Class inner join Student on Class.ClassId = Student.ClassId
go

select Class.ClassId, Class.ClassCode, Student.StudentId, Student.StudentName
from Class join Student on Class.ClassId = Student.ClassId
go

-- Tim hieu left join
select Class.ClassId, Class.ClassCode, Student.StudentId, Student.StudentName
from Class left join Student on Class.ClassId = Student.ClassId
go

-- Tim hieu right join
select Class.ClassId, Class.ClassCode, Student.StudentId, Student.StudentName
from Class right join Student on Class.ClassId = Student.ClassId
go

insert into Student (StudentId, StudentName, birthdate)
values
(6, N'Phạm Tuấn AnhAAA', '1993-08-05')
go

select Class.ClassId, Class.ClassCode, Student.StudentId, Student.StudentName
from Class full outer join Student on Class.ClassId = Student.ClassId
go

select Class.ClassId, Class.ClassCode, count(Student.StudentId) N'Sĩ Số Học Sinh'
from Class left join Student on Class.ClassId = Student.ClassId
group by Class.ClassId, Class.ClassCode
go