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

Noi dung kien thuc moi
	- primary key: tren nhieu columns
	- foreign key
	- select:
		- Nhieu bang
		- group by: sum, count, max, min, avg

primary key (column_1)
primary key (column_1, column_2)
primary key (column_1, column_2, column_3, ...)

-- Tao CSDL
create database BT1763
go

-- Kich hoat CSDL
use BT1763
go

-- Tao tables
create table student (
	rollno nvarchar(12) primary key,
	fullname nvarchar(50),
	age int,
	address nvarchar(200),
	email nvarchar(150),
	phone_number nvarchar(20),
	gender nvarchar(12)
)
go

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

insert into student(rollno, fullname, phone_number, gender, email, age, address)
values
('R001', 'Tran Van A', '234234', 'Nam', 'a@gmail.com', 22, 'Ha Noi'),
('R002', 'Tran Van B', '234234', 'Nam', 'b@gmail.com', 25, 'Ha Noi'),
('R003', 'Tran Van C', '234234', 'Nam', 'c@gmail.com', 21, 'Ha Noi'),
('R004', 'Tran Van D', '234234', 'Nam', 'e@gmail.com', 26, 'Ha Noi'),
('R005', 'Tran Van E', '234234', 'Nam', 'e@gmail.com', 29, 'Ha Noi')
go

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

select * from student
select * from subject


create table result (
	rollno nvarchar(12) not null,
	subject_id int not null,
	mark float
)

select * from student
select * from subject
select * from result

insert into result (rollno, subject_id, mark)
values
('R010', 5, 8),
('R001', 1, 9)
go

drop table result

-- rollno trong bang result -> foreign key -> Lien ket voi truong khoa chinh rollno trong bang student
---- C1:
create table result (
	rollno nvarchar(12) not null references student (rollno),
	subject_id int not null,
	mark float,
	constraint fk_result_subject foreign key (subject_id) references subject (subject_id)
)

insert into result (rollno, subject_id, mark)
values
('R001', 1, 9)
go

insert into result (rollno, subject_id, mark)
values
('R002', 2, 8)
go

drop table result

create table result (
	rollno nvarchar(12) not null,
	subject_id int not null,
	mark float
)
go

alter table result
add constraint fk_result_subject foreign key (subject_id) references subject (subject_id)
go

alter table result
add constraint fk_result_student foreign key (rollno) references student(rollno)
go

insert into result (rollno, subject_id, mark)
values
('R001', 1, 9)
go

insert into result (rollno, subject_id, mark)
values
('R002', 20, 8)
go

-- Huy foreign key
alter table result
drop constraint fk_result_student
go

-- primary key: tren 2 columns
select * from student
select * from subject
select * from result

insert into result (rollno, subject_id, mark)
values
('R001', 1, 9)
go

insert into result (rollno, subject_id, mark)
values
('R001', 1, 6)
go

insert into result (rollno, subject_id, mark)
values
('R001', 2, 6)
go

delete from result

-- Add primary key: rollno, subject_id
alter table result
add constraint pk_rollno_student_id primary key (rollno, subject_id)
go

-- Tao them bang
create table class (
	class_id int primary key identity(1,1),
	class_name nvarchar(50)
)
go

create table room (
	room_id int primary key identity(1,1),
	room_name nvarchar(50),
	seats_count int default 0,
	table_count int default 0,
	address nvarchar(200)
)
go

insert into room (room_name, seats_count, table_count, address)
values
('R01', 6, 10, 'A'),
('R02', 2, 8, 'A'),
('R03', 8, 7, 'A'),
('R04', 1, 2, 'A'),
('R05', 3, 12, 'A')
go

-- Hiển thị phòng học có số bàn > 5 và số ghế > 5
select * from room
where seats_count > 5 and table_count > 5
go

--    - Hiển thị phòng học có số bàn > 5 và < 20 + số ghế > 5 và < 20
select * from room
where seats_count > 5 and seats_count < 20 and table_count > 5 and table_count < 20
go

select * from room
where seats_count between 6 and 19
	and table_count between 6 and 19
go

-- Hien thi thong tin du lieu theo format sau
---- rollno (student), fullname (student), phone_number (student), subject_name (subject), mark (result)
select student.rollno, student.fullname, student.phone_number, subject.subject_name, result.mark
from student, subject, result
where student.rollno = result.rollno
	and subject.subject_id = result.subject_id
go

-- Them data -> TEST: group by .. having (sum, count, max, min, avg)
insert into result (rollno, subject_id, mark)
values
('R001', 3, 8),
('R002', 1, 2),
('R002', 2, 9),
('R002', 3, 6),
('R003', 1, 5),
('R003', 3, 8)
go

select * from student
select * from subject
select * from result

select student.rollno, student.fullname, student.phone_number, subject.subject_name, result.mark
from student, subject, result
where student.rollno = result.rollno
	and subject.subject_id = result.subject_id
go

-- Dem xem moi sinh vien da co bao nhieu diem thi
select student.rollno, student.fullname, student.phone_number, count (result.subject_id) 'So Mon Co Diem Thi'
from student, subject, result
where student.rollno = result.rollno
	and subject.subject_id = result.subject_id
group by student.rollno, student.fullname, student.phone_number
go

select student.rollno, student.fullname, student.phone_number, max(result.mark) 'Max'
from student, subject, result
where student.rollno = result.rollno
	and subject.subject_id = result.subject_id
group by student.rollno, student.fullname, student.phone_number
go

select student.rollno, student.fullname, student.phone_number, min(result.mark) 'Min'
from student, subject, result
where student.rollno = result.rollno
	and subject.subject_id = result.subject_id
group by student.rollno, student.fullname, student.phone_number
go

select student.rollno, student.fullname, student.phone_number, round(avg(result.mark), 2) 'Avg'
from student, subject, result
where student.rollno = result.rollno
	and subject.subject_id = result.subject_id
group by student.rollno, student.fullname, student.phone_number
go

select student.rollno, student.fullname, student.phone_number, sum(result.mark) 'Tong Diem'
from student, subject, result
where student.rollno = result.rollno
	and subject.subject_id = result.subject_id
group by student.rollno, student.fullname, student.phone_number
go

-- Them dieu kien la ket qua cua sum, count, max, min, avg
---- Hien thi thong tin sinh vien co tong diem > 20
select student.rollno, student.fullname, student.phone_number, sum(result.mark) 'Tong Diem'
from student, subject, result
where student.rollno = result.rollno
	and subject.subject_id = result.subject_id
group by student.rollno, student.fullname, student.phone_number
having sum(result.mark) > 20
go

-- order by
select student.rollno, student.fullname, student.phone_number, sum(result.mark) 'Tong Diem'
from student, subject, result
where student.rollno = result.rollno
	and subject.subject_id = result.subject_id
group by student.rollno, student.fullname, student.phone_number
order by 'Tong Diem' asc
go

select student.rollno, student.fullname, student.phone_number, sum(result.mark) 'Tong Diem'
from student, subject, result
where student.rollno = result.rollno
	and subject.subject_id = result.subject_id
group by student.rollno, student.fullname, student.phone_number
order by 'Tong Diem' desc
go

select top(2) student.rollno, student.fullname, student.phone_number, sum(result.mark) 'Tong Diem'
from student, subject, result
where student.rollno = result.rollno
	and subject.subject_id = result.subject_id
group by student.rollno, student.fullname, student.phone_number
order by 'Tong Diem' desc
go

select top(2) student.rollno, student.fullname, student.phone_number, sum(result.mark) 'Tong Diem'
from student, subject, result
where student.rollno = result.rollno
	and subject.subject_id = result.subject_id
group by student.rollno, student.fullname, student.phone_number
order by 'Tong Diem' asc
go

select * from subject
where subject_name like '%C%'

select * from subject
where subject_name like '%C'

select * from subject
where subject_name like 'C%'
------------------------------------------------