Thiết kế CSDL quản lý sinh viên - nâng cao - phân 2 - lập trình sql server

-- Tao CSDL: BT1764
create database BT1764
go

-- Kich Hoat CSDL
use BT1764
go

-- Tao Tables
create table student (
	rollno nvarchar(12) primary key, -- Trong du an thuc te -> khong su dung rollno lam primary key -> id: int primary key identity(1,1)
	fullname nvarchar(50),
	age int,
	address nvarchar(200),
	email nvarchar(150),
	phone_number nvarchar(20),
	gender nvarchar(16)
)
go

create table subject (
	subject_no int primary key identity(1,1),
	subject_name nvarchar(50)
)
go

create table result (
	rollno nvarchar(12) not null references student (rollno),
	subject_no int not null references subject (subject_no),
	mark float default 0,
	primary key (rollno, subject_no)
)
go

create table class (
	class_no int primary key identity(1,1),
	class_name nvarchar(50)
)
go

create table group_class (
	class_no int not null references class (class_no),
	rollno nvarchar(12) not null references student (rollno),
	primary key (class_no, rollno)
)
go

create table room (
	room_no int primary key identity(1,1),
	room_name nvarchar(50),
	table_num int default 0,
	desk_num int default 0,
	address nvarchar(50)
)
go

create table book (
	id int primary key identity(1,1),
	class_no int references class (class_no),
	room_no int references room (room_no),
	subject_no int references subject (subject_no),
	start_date datetime,
	end_date datetime
)
go

-- insert data
insert into room (room_name, table_num, desk_num)
values
('A1', 20, 20),
('A2', 20, 20),
('A3', 20, 20),
('A4', 20, 20),
('A5', 20, 20)
go

insert into subject (subject_name)
values
('Lap Trinh C'),
('HTML/CSS/JS'),
('SQL Server'),
('PHP/Laravel'),
('eProject')
go

insert into class (class_name)
values
('C2108G3'),
('C2108G2'),
('C2108G1')
go

insert into student (rollno, fullname, address, phone_number, age, email, gender)
values
('R001', 'TRAN VAN A', 'Ha Noi', '12312312', 20, 'a@gmail.com', 'Nam'),
('R002', 'TRAN VAN B', 'Nam Dinh', '12312312', 20, 'b@gmail.com', 'Nam'),
('R003', 'TRAN VAN C', 'Ha Noi', '12312312', 20, 'c@gmail.com', 'Nam'),
('R004', 'TRAN VAN D', 'Nam Dinh', '12312312', 20, 'd@gmail.com', 'Nam'),
('R005', 'TRAN VAN E', 'Ha Noi', '12312312', 20, 'e@gmail.com', 'Nam'),
('R006', 'TRAN VAN F', 'Ha Noi', '12312312', 20, 'f@gmail.com', 'Nam')
go

insert into group_class (rollno, class_no)
values
('R001', 1),
('R002', 1),
('R003', 1),
('R004', 2),
('R005', 2),
('R006', 3)
go

insert into result(rollno, subject_no, mark)
values
('R001', 1, 10),
('R001', 2, 8),
('R002', 1, 7),
('R003', 3, 6),
('R004', 2, 9)
go

insert into book(class_no, room_no, subject_no, start_date, end_date)
values
(1, 1, 1, '2022-03-20', '2022-04-22'),
(2, 2, 1, '2022-03-25', '2022-04-28'),
(3, 3, 1, '2022-03-22', '2022-04-26'),
(1, 2, 2, '2022-04-28', '2022-05-30'),
(2, 1, 2, '2022-04-30', '2022-05-02')
go

-- Query
-- 1) Hiển thị sinh viên có quê ở Nam Định
select * from student where address = 'Nam Dinh'
go

-- 2) Hiển thị lớp học có chứa chữ 'A8'
select student.rollno, student.fullname, student.phone_number, class.class_name
from student, class, group_class
where class.class_no = group_class.class_no
	and student.rollno = group_class.rollno
go

select student.rollno, student.fullname, student.phone_number, class.class_name
from student, class, group_class
where class.class_no = group_class.class_no
	and student.rollno = group_class.rollno
	and class.class_name like '%G3%'
go

-- 3) Hiển thị thông tin sinh viên (RollNo, tên, ten moc hoc, và điểm thi) của sinh viên có tên là 'TRAN VAN A'
select student.rollno, student.fullname, subject.subject_name, result.mark
from student, subject, result
where student.rollno = result.rollno
	and subject.subject_no = result.subject_no
	and student.fullname = 'TRAN VAN A'
go

-- Hiển thị thông tin điểm thi (RollNo, tên, ten mon hoc, điểm thi) của tất cả sinh viên
select student.rollno, student.fullname, subject.subject_name, result.mark
from student, subject, result
where student.rollno = result.rollno
	and subject.subject_no = result.subject_no
go

select * from result

insert into result (rollno, subject_no)
values
('R003', 4)
go