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

Nội dung buổi học:
	- Select:
		- distinct
		- join:
			- inner join
			- left join
			- right join
			- full outer join

	- view | proc
	- Phat trien du an that: (Frontend, Backend, CSDL)
		- Style 1: Viet query -> de trong code
		- Style 2: Viet query -> CSDL -> View | Proc -> su dung de goi trong backend

		view -> chi chua 1 lenh select -> hieu nhu table tam thoi -> dc sinh ra tu cau select

	- proc (store) -> Hien no nhu function (chuc nang) | method (phuong thuc) -> C/JS/...
		- Ten proc
		- Tham so chuyen vao (ko tham so, 1 tham so, n tham so)
		- Du lieu tra ve -> (int, nvarchar, text, ...)
		- Trong proc -> Luu rat nhieu cau quy van (0 dong query, 1 lenh, n lenh)
			- insert
			- update
			- delete
			- select
			- create ...

-- Tao CSDL
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

-- Alter
alter table Result
alter column Mark float
go

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

-- Check
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 D', '1993-10-10', 2),
(5, 'SV E', '1992-06-06', 3)
go

insert into Subject (SubjectId, SubjectName, SessionCount)
values
(1, 'C Progamming', 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, StudentName, BirthDate
from Student
where BirthDate between '1992-10-10' and '1993-10-10'
go

-- Count
select Class.ClassId, Class.ClassCode, Student.StudentId
from Class, Student
where Class.ClassId = Student.ClassId
go

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

-- Tinh Tong Diem
select Student.StudentId, Student.StudentName, sum(Result.Mark) 'Tong Diem'
from Student, Result
where Student.StudentId = Result.StudentId
group by Student.StudentId, Student.StudentName
having sum(Result.Mark) > 10
go

-- Joins
select Class.ClassId, Class.ClassCode, count(Student.StudentId) 'SiSo'
from Class, Student
where Class.ClassId = Student.ClassId
group by Class.ClassId, Class.ClassCode
go

select Class.ClassId, Class.ClassCode, Student.StudentId
from Class, Student -- Class: Table Left, Student: Table Right
where Class.ClassId = Student.ClassId
go

select * from Student
select * from Class

-- Tuong tu cau lenh sau: inner join
select Class.ClassId, Class.ClassCode, Student.StudentId
from Class inner join Student on Class.ClassId = Student.ClassId
go

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

-- Left Join
select Class.ClassId, Class.ClassCode, Student.StudentId
from Class left join Student on Class.ClassId = Student.ClassId
go

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

insert into Student (StudentId, StudentName, BirthDate)
values
(6, 'SV F', '1993-08-05')
go

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

-- Dem so sinh vien trong tun lop hoc
select Class.ClassId, Class.ClassCode, count(Student.StudentId) 'Si So'
from Class left join Student on Class.ClassId = Student.ClassId
group by Class.ClassId, Class.ClassCode
go

-- Xem danh sach lop hoc da co sinh vien
select distinct Class.ClassId, Class.ClassCode
from Class inner join Student on Class.ClassId = Student.ClassId
go

-- View | Proc
create view view_class_list_exists_student
as
select distinct Class.ClassId, Class.ClassCode
from Class inner join Student on Class.ClassId = Student.ClassId
go

create view view_class_list_exists_student
as
select distinct top(2) Class.ClassId, Class.ClassCode
from Class inner join Student on Class.ClassId = Student.ClassId
order by Class.ClassId desc
go

select * from view_class_list_exists_student

alter view view_class_list_exists_student
as
select distinct Class.ClassId, Class.ClassCode
from Class inner join Student on Class.ClassId = Student.ClassId
go

drop view view_class_list_exists_student
go

-- Proc - Khong tham so
create proc proc_insert_data
as
begin
	-- Bat dau code
	insert into Subject (SubjectId, SubjectName, SessionCount)
	values
	(6, 'A', 11),
	(7, 'B', 12)

	select * from Student
	-- Ket thuc code
end

exec proc_insert_data

drop proc proc_insert_data
go

select StudentId, StudentName, BirthDate
from Student
where BirthDate between '1992-10-10' and '1993-10-10'

-- Proc co tham so dau vao
create proc proc_find_student
	@startDate datetime,
	@endDate datetime
as
begin
	select StudentId, StudentName, BirthDate
	from Student
	where BirthDate between @startDate and @endDate
end

exec proc_find_student '1992-10-10', '1993-10-10'
exec proc_find_student '1995-10-10', '1997-10-10'