Thiết kế database - bãi đỗ xe - Lập trình SQL Server

Nôi dung kiến thức:
	- foreign key
	- constraint
	- check
	- select
		- Nhieu bang (tables)
		- group by (min, max, count, sum, avg)
			having

-- Tao CSDL
create database BT1795
go

-- Kich hoat CSDL: BT1795
use BT1795
go

-- Tao tables
drop table BaiDoXe
drop table ThongTinGui
drop table ChuSoHuu

create table BaiDoXe (
	baidoxe_id int primary key identity(1,1),
	name nvarchar(50),
	address nvarchar(200)
)
go

create table ThongTinGui (
	id int primary key identity(1,1),
	vehicleName nvarchar(20),
	baidoxe_id int,
	owner_id int
)
go

create table ChuSoHuu (
	owner_id int primary key identity(1,1),
	fullname nvarchar(50),
	cmtnd nvarchar(20),
	address nvarchar(200)
)
go

-- Them du lieu
insert into BaiDoXe(name, address)
values
('Ba Do Xe 01', 'Ha Noi'),
('Ba Do Xe 02', 'Ha Nam'),
('Ba Do Xe 03', 'Ha Noi'),
('Ba Do Xe 04', 'Nam Dinh'),
('Ba Do Xe 05', 'Ha Noi')
go

insert into ChuSoHuu (fullname, cmtnd, address)
values
('TRAN VAN A', '123456', 'Ha Noi'),
('TRAN VAN B', '234234', 'Ha Noi'),
('TRAN VAN C', '456677', 'Nam Dinh'),
('TRAN VAN D', '123543', 'Ha Noi'),
('TRAN VAN E', '789798', 'Ha Nam')
go

insert into ThongTinGui (vehicleName, baidoxe_id, owner_id)
values
('Honda 123', 1, 1),
('Honda 234', 2, 2),
('Honda 456', 2, 3),
('Honda 234', 3, 2),
('Honda asdf', 1, 2),
('Honda okaa', 2, 1)
go

select * from BaiDoXe
select * from ChuSoHuu
select * from ThongTinGui

-- Tim hieu foreign key
insert into ThongTinGui (vehicleName, baidoxe_id, owner_id)
values
('TEST', 10, 7) -- Nhan xet: baidoxe_id = 10 -> khong ton tai -> sai du lieu & owner_id = 7 -> khong tim thay
go -- Du lieu sai -> khong chinh xac
-- Bai toan dat ra: co cach nao -> chan ko cho chen du lieu sai vao bang ThongTinGui khong???
-- Nhan xet: baidoxe_id (ThongTinGui) -> du lieu ton tai trong bang BaiDoXe, owner_id (ThongTinGui) -> du lieu ton tai trong bang ChuSoHuu
-- baidoxe_id -> foreign key -> references BaiDoXe (baidoxe_id)
drop table ThongTinGui

-- Cach 1:
create table ThongTinGui (
	id int primary key identity(1,1),
	vehicleName nvarchar(20),
	baidoxe_id int references BaiDoXe (baidoxe_id), -- cach 1: viet nhu nay
	owner_id int,
	-- foreign key (owner_id) references ChuSoHuu (owner_id) -- Cach 2: viet nhu nay (giong nhu cach 1)
	constraint fk_owner_id foreign key (owner_id) references ChuSoHuu (owner_id) -- Cach 3: khac biet fk_owner_id
)
go

insert into ThongTinGui (vehicleName, baidoxe_id, owner_id)
values
('Honda 123', 1, 1),
('Honda 234', 2, 2),
('Honda 456', 2, 3),
('Honda 234', 3, 2),
('Honda asdf', 1, 2),
('Honda okaa', 2, 1)
go

insert into ThongTinGui (vehicleName, baidoxe_id, owner_id)
values
('TEST', 10, 7)
go

-- Huy constraint -> foreign key
alter table ThongTinGui
drop constraint FK__ThongTinG__baido__4CA06362 -- Sinh tu dong -> ko co dinh voi tung lap vien (tung may tinh)
go

alter table ThongTinGui
drop constraint fk_owner_id
go

-- Add lai foreign key sau khi bang da tao ra
alter table ThongTinGui
add constraint fk_baidoxe_id foreign key (baidoxe_id) references BaiDoXe (baidoxe_id) -- Cach 4: Add foreign key
go

alter table ThongTinGui
add constraint fk_owner_id foreign key (owner_id) references ChuSoHuu (owner_id) -- Cach 4: Add foreign key
go

-- Xoa du lieu error
delete from ThongTinGui
where id = 10

delete from ThongTinGui
where id > 1

-- Hiển thị thông tin người gửi xe (số cmtnd, tên, tên bãi đỗ xe, ten xe)
-- Phan tich: cmtnd (ChuSoHuu), fullname (ChuSoHuu), ten bai so xe: name (BaiDoXe), ten xe (ThongTinGui)
select ChuSoHuu.fullname, ChuSoHuu.cmtnd, BaiDoXe.name, ThongTinGui.vehicleName
from ChuSoHuu, BaiDoXe, ThongTinGui
where BaiDoXe.baidoxe_id = ThongTinGui.baidoxe_id
	and ThongTinGui.owner_id = ChuSoHuu.owner_id

---- TRAN VAN A, 123456, Ba Do Xe 01, Honda 123
-- Hiển thị số lần gửi xe của tất cả các Chủ Sở Hữu >> Tên bãi đỗ xe, ten xe, Chủ Sở Hữu, Số CMTND, Số Lần Gửi
select * from BaiDoXe
select * from ChuSoHuu
select * from ThongTinGui

select ChuSoHuu.fullname, ChuSoHuu.cmtnd, BaiDoXe.name, ThongTinGui.vehicleName, count(*) SoLanGuiXe
from ChuSoHuu, BaiDoXe, ThongTinGui
where BaiDoXe.baidoxe_id = ThongTinGui.baidoxe_id
	and ThongTinGui.owner_id = ChuSoHuu.owner_id
group by ChuSoHuu.fullname, ChuSoHuu.cmtnd, BaiDoXe.name, ThongTinGui.vehicleName

-- Chi hien thi nhung xe co so lan gui >= 2
-- Lay ket qua tu -> count, sum, min, max, avg -> lam dieu kien -> su dung having
select ChuSoHuu.fullname, ChuSoHuu.cmtnd, BaiDoXe.name, ThongTinGui.vehicleName, count(*) SoLanGuiXe
from ChuSoHuu, BaiDoXe, ThongTinGui
where BaiDoXe.baidoxe_id = ThongTinGui.baidoxe_id
	and ThongTinGui.owner_id = ChuSoHuu.owner_id
group by ChuSoHuu.fullname, ChuSoHuu.cmtnd, BaiDoXe.name, ThongTinGui.vehicleName
having count(*) >= 2

-- Quan diem sinh vien
create table Student (
	student_id int primary key identity (1,1),
	fullname nvarchar(50)
)
go

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

create table Marks (
	id int primary key identity (1,1),
	student_id int references Student (student_id),
	subject_id int references Subject (subject_id),
	mark float
)
go

insert into Student (fullname)
values
('TRAN VAN A'),
('TRAN VAN B'),
('TRAN VAN C')
go

insert into Subject (subject_name)
values
('LAP TRINH C'),
('HTML/CSS/JS')
go

insert into Marks (student_id, subject_id, mark)
values
(1, 1, 7),
(1, 2, 8),
(2, 1, 6),
(2, 2, 10),
(3, 1, 5),
(3, 2, 2)
go

select * from Student
select * from Subject
select * from Marks

-- Xem thong tin: ten, mon hoc, diem
select Student.fullname, Subject.subject_name, Marks.mark
from Student, Subject, Marks
where Student.student_id = Marks.student_id
	and Subject.subject_id = Marks.subject_id

-- Dem so diem thi cua tung sinh vien: ten sinh vien + so diem thi (count)
select Student.fullname, count(*) SoMonThi
from Student, Marks
where Student.student_id = Marks.student_id
group by Student.fullname

select Student.fullname, count(Marks.id) SoMonThi
from Student, Marks
where Student.student_id = Marks.student_id
group by Student.fullname

-- Tinh tong diem cua tung sinh vien
select Student.fullname, sum(Marks.mark) TongDiem
from Student, Marks
where Student.student_id = Marks.student_id
group by Student.fullname

-- Tim diem max
select Student.fullname, max(Marks.mark) DiemMax
from Student, Marks
where Student.student_id = Marks.student_id
group by Student.fullname

-- Tim diem Min
select Student.fullname, min(Marks.mark) DiemMax
from Student, Marks
where Student.student_id = Marks.student_id
group by Student.fullname

-- Tinh diem trunh binh
select Student.fullname, avg(Marks.mark) DiemTrungBinh
from Student, Marks
where Student.student_id = Marks.student_id
group by Student.fullname

-- Check
delete from Marks

insert into Marks (student_id, subject_id, mark) -- marks: 0 -> 10
values
(1, 1, -6)
go

drop table Marks

create table Marks (
	id int primary key identity (1,1),
	student_id int references Student (student_id),
	subject_id int references Subject (subject_id),
	mark float,
	check (mark >= 0 and mark <= 10) -- check (mark between 0 and 10)
	-- constraint check_mark check (mark >= 0 and mark <= 10)
)
go

-- Cach khac:
create table Marks (
	id int primary key identity (1,1),
	student_id int references Student (student_id),
	subject_id int references Subject (subject_id),
	mark float
)
go

alter table Marks
add constraint check_mark check (mark >= 0 and mark <= 10)
go