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

#SQLQuery1.sql

-- Tao CSDL
create database StudentManagementSystem
go

-- Kich hoat CSDL
use StudentManagementSystem
go

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

---- Cach 1: Tao primary key
create table Class (
	ClassId int primary key,
	ClassCode nvarchar(50)
)
go

---- Cach 2: Tao primary key
create table Class (
	ClassId int,
	ClassCode nvarchar(50),
	primary key (ClassId)
)
go

drop table Class
go

---- Cach 3: Tao primary key
create table Class (
	ClassId int,
	ClassCode nvarchar(50),
	constraint pk_class_id primary key (ClassId)
)
go

---- Cach 4: Tao primary key
create table Class (
	ClassId int, --Cach nay: ko the tao dc primary key ben ngoai
	ClassCode nvarchar(50)
)
go

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

alter table Class
add constraint pk_class_id primary key (ClassId)
go

create table Student (
	StudentId int not null,
	StudentName nvarchar(50),
	BirthDate date,
	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

alter table Result
alter column Mark float
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 Subject
add constraint ck_subject_sessioncount check (SessionCount > 0)
go

-- Insert Data
select * from Class
select * from Student
select * from Subject
select * from Result
go


insert into Class (ClassId, ClassCode)
values
(1, 'C2307L'),
(2, 'C2308L')
go

insert into Student (StudentId, StudentName, BirthDate, ClassId)
values
(1, 'Sinh Vien A', '1988-02-12', 1),
(2, 'Sinh Vien B', '1989-05-12', 2),
(3, 'Sinh Vien C', '1988-08-12', 2),
(4, 'Sinh Vien D', '1990-09-12', 2)
go

insert into Student (StudentId, StudentName, BirthDate, ClassId)
values
(5, 'Sinh Vien E', '1989-08-12', 3) -- Du lieu bi sai: thiet ke CSDL bi error
go

-- Foreign key
create table Student (
	StudentId int not null,
	StudentName nvarchar(50),
	BirthDate date,
	ClassId int references Class (ClassId)
)
go

create table Student (
	StudentId int not null,
	StudentName nvarchar(50),
	BirthDate date,
	ClassId int,
	foreign key (ClassId) references Class (ClassId)
)
go

create table Student (
	StudentId int not null,
	StudentName nvarchar(50),
	BirthDate date,
	ClassId int,
	constraint fk_student_class foreign key (ClassId) references Class (ClassId)
)
go

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

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

-- Foreign Key
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

-- Du lieu chuan
delete from Result
delete from Subject
delete from Student
delete from Class

-- Insert
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'Phan Văn Huy', '1993-08-05', 1),
(3, N'Nguyễn Hoàng Minh', '1993-08-05', 2),
(4, N'Trần Tuấn Tú', '1993-08-05', 2),
(5, N'Đỗ Anh Tai', '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

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

select StudentId N'Mã Sinh Viên', StudentName N'Tên Sinh Viên', BirthDate N'Ngày Sinh' from Student
where BirthDate between '1980-10-10' and '1993-10-10'
go

-- Hien thi du lieu theo format sau
---- StudentId (Student), StudentName (Student), ClassCode (Class)
---- 1, Pham Tuan Anh, C1106KV
---- 2, Phan Van Huy, C1106KV
---- 3, Nguyen Hoang Minh, C1108GV
select Student.StudentId, Student.StudentName, Class.ClassCode
from Student, Class
where Student.ClassId = Class.ClassId
go

-- Hien thi du lieu theo format sau
---- ClassId (Class), ClassCode (Class), StudentId (Student)
select Class.ClassId, Class.ClassCode, Student.StudentId
from Student, Class
where Student.ClassId = Class.ClassId
go

-- Dem so sinh vien trong lop hoc: count, sum, max, min, avg
select Class.ClassId, Class.ClassCode, count(Student.StudentId) 'Si So Lop Hoc'
from Student, Class
where Student.ClassId = Class.ClassId
group by Class.ClassId, Class.ClassCode
go

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

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

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

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