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 BT1793
create database BT1793
go

-- Kich hoat CSDL
use BT1793
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 truong du lieu
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

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, 'SV A', '1993-08-05', 1),
(2, 'SV B', '1992-06-10', 1),
(3, 'SV C', '1992-09-07', 2),
(4, 'SV E', '1993-10-10', 2),
(5, 'SV F', '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 'Ma Sinh Vien', StudentName as 'Ten Sinh Vien', BirthDate 'Ngay Sinh'
from Student
where BirthDate between '1992-10-10' and '1993-10-10'
go

select Class.ClassId, Class.ClassCode, count(Student.StudentId) 'Si So Lop'
from Class, Student
where Class.ClassId = Student.ClassId
group by Class.ClassId, Class.ClassCode
go

select Class.ClassId, Class.ClassCode, count(*) 'Si So Lop'
from Class, Student
where Class.ClassId = Student.ClassId
group by Class.ClassId, Class.ClassCode
go

-- Co the trien khai theo cach khac nhu sau -> dua ra ket qua giao cua 2 bang
-- Class: left table, Student: right table
select Class.ClassId, Class.ClassCode, count(Student.StudentId) 'Si So Lop'
from Class join Student on Class.ClassId = Student.ClassId
group by Class.ClassId, Class.ClassCode
go

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

-- TH: Hien thi tat ca thong tin cua Class
select Class.ClassId, Class.ClassCode, count(Student.StudentId) 'Si So Lop'
from Class left join Student on Class.ClassId = Student.ClassId
group by Class.ClassId, Class.ClassCode
go

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

-- Tong Diem
select Student.StudentId, Student.StudentName, 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
order by 'Tong Diem' desc
go

-- Nang cao: Ten lop, ten sinh vien, ten mon hoc, diem thi
select Class.ClassCode, Student.StudentName, Subject.SubjectName, Result.Mark
from Class, Student, Subject, Result
where Class.ClassId = Student.ClassId
	and Student.StudentId = Result.StudentId
	and Subject.SubjectId = Result.SubjectId
go

-- Nang cao: Ten lop, ten sinh vien, ten mon hoc, diem thi -> Sinh vien ten: SV A
select Class.ClassCode, Student.StudentName, Subject.SubjectName, Result.Mark
from Class, Student, Subject, Result
where Class.ClassId = Student.ClassId
	and Student.StudentId = Result.StudentId
	and Subject.SubjectId = Result.SubjectId
	and Student.StudentName = 'SV A'
go

-- Nang cao: Ten lop, ten sinh vien, ten mon hoc, diem thi -> Mon hoc bat dau bang chu Web
select Class.ClassCode, Student.StudentName, Subject.SubjectName, Result.Mark
from Class, Student, Subject, Result
where Class.ClassId = Student.ClassId
	and Student.StudentId = Result.StudentId
	and Subject.SubjectId = Result.SubjectId
	and Subject.SubjectName like 'Web%'
go

-- Nang cao: Ten lop, ten sinh vien, ten mon hoc, diem thi -> Mon hoc ket thuc bang chu Design
select Class.ClassCode, Student.StudentName, Subject.SubjectName, Result.Mark
from Class, Student, Subject, Result
where Class.ClassId = Student.ClassId
	and Student.StudentId = Result.StudentId
	and Subject.SubjectId = Result.SubjectId
	and Subject.SubjectName like '%Design'
go

-- Nang cao: Ten lop, ten sinh vien, ten mon hoc, diem thi -> Mon hoc chua ky tu m
select Class.ClassCode, Student.StudentName, Subject.SubjectName, Result.Mark
from Class, Student, Subject, Result
where Class.ClassId = Student.ClassId
	and Student.StudentId = Result.StudentId
	and Subject.SubjectId = Result.SubjectId
	and Subject.SubjectName like '%m%'
go