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

Nội dung kiến thức:
- Chữa bài tập - Kiến thức core căn bản
- Kiến thức mới:
	- Foreign key
	- Constraint
	- Default
	- Check
	- Unique
	- Select
		- Kết nối nhiều bảng
		- Group By ... Having
		- Union

-- Tao CSDL
create database BT1795
go

-- Kich hoat CSDL
use BT1795
go

-- Tao tables
create table BaiDoXe (
	baidoxe_id int primary key identity(1,1),
	baidoxe_name nvarchar(50) not null,
	address nvarchar(200)
)
go

create table ThongTinGui (
	thongtingui_id int primary key identity(1,1),
	vehicle_name nvarchar(50),
	vehicle_no 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

-- Insert data
insert into BaiDoXe(baidoxe_name, address)
values
('BDX 01', 'Ha Noi'),
('BDX 02', 'Ha Nam'),
('BDX 03', 'Nam Dinh')
go

insert into ChuSoHuu (fullname, cmtnd, address)
values
('Nguyen A', '12312312', 'Ha Noi'),
('Nguyen B', '34534534', 'Ha Noi'),
('Nguyen C', '654654654', 'Ha Noi'),
('Nguyen D', '456456456', 'Ha Noi'),
('Nguyen E', '546476867', 'Ha Noi')
go

insert into ThongTinGui (baidoxe_id, owner_id, vehicle_name, vehicle_no)
values
(1, 1, 'A', 'No01'),
(1, 2, 'B', 'No02'),
(2, 1, 'A', 'No01'),
(2, 1, 'A', 'No01'),
(2, 2, 'B', 'No02'),
(1, 2, 'B', 'No02')
go

-- Check du lieu
select * from BaiDoXe
select * from ChuSoHuu
select * from ThongTinGui

-- Foreign key la gi
insert into ThongTinGui (baidoxe_id, owner_id, vehicle_name, vehicle_no)
values
(10, 11, 'H', 'No03')
--Nhan xet: du lieu baidoxe_id = 10 -> ko tim thay trong bang BaiDoXe
--Nhan xet: dieu lieu owner_id = 11 -> khong tim thay trong bang ChuSoHuu
--TH: them du lieu nhu -> du lieu sai -> ko chinh xac -> redundant
--Trong thiet ke CSDL: Nghiem cam TH nay xay ra
--Bai toan dat ra: co cach nao system CSDL (SQL Server/MySQL/Oracle) -> tu kiem tra va ko cho insert du lieu sai nay khong
--Mong muon: baidoxe_id -> them vao -> Du lieu nay ton tai trong bang BaiDoXe
--Tuong tu vs owner_id cung vay
go

-- Tom tat

drop table ThongTinGui

-- Cach 1:
create table ThongTinGui (
	thongtingui_id int primary key identity(1,1),
	vehicle_name nvarchar(50),
	vehicle_no nvarchar(20),
	baidoxe_id int references BaiDoXe (baidoxe_id), --Foreign key -> Truong khoa ngoai lien ket vs BaiDoXe (baidoxe_id)
	owner_id int references ChuSoHuu (owner_id)--Foreign key -> Truong khoa ngoai lien ket vs ChuSoHuu (owner_id)
)
go

-- Cach 2:
create table ThongTinGui (
	thongtingui_id int primary key identity(1,1),
	vehicle_name nvarchar(50),
	vehicle_no nvarchar(20),
	baidoxe_id int references BaiDoXe (baidoxe_id), --Foreign key -> Truong khoa ngoai lien ket vs BaiDoXe (baidoxe_id)
	owner_id int,
	foreign key (owner_id) references ChuSoHuu (owner_id)
)
go

-- Cach 3:
create table ThongTinGui (
	thongtingui_id int primary key identity(1,1),
	vehicle_name nvarchar(50),
	vehicle_no nvarchar(20),
	baidoxe_id int, --Foreign key -> Truong khoa ngoai lien ket vs BaiDoXe (baidoxe_id)
	owner_id int,
	foreign key (owner_id) references ChuSoHuu (owner_id),
	constraint fk_baidoxe_id foreign key (baidoxe_id) references BaiDoXe (baidoxe_id)
)
go

drop table ThongTinGui

--Cach 4:
create table ThongTinGui (
	thongtingui_id int primary key identity(1,1),
	vehicle_name nvarchar(50),
	vehicle_no nvarchar(20),
	baidoxe_id int,
	owner_id int
)
go

alter table ThongTinGui
add constraint fk_baidoxe_id foreign key(baidoxe_id) references BaiDoXe(baidoxe_id)
go

alter table ThongTinGui
add constraint fk_owner_id foreign key(owner_id) references ChuSoHuu(owner_id)
go

alter table ThongTinGui
drop constraint FK__ThongTinG__owner__52593CB8

alter table ThongTinGui
drop constraint fk_baidoxe_id

-- Default
select * from BaiDoXe
select * from ChuSoHuu
select * from ThongTinGui

insert into BaiDoXe (baidoxe_name)
values
('BDX 04') --Khi insert du lieu nhu nay -> address: 'Ha Noi'
go

-- C1:
create table BaiDoXe (
	baidoxe_id int primary key identity(1,1),
	baidoxe_name nvarchar(50) not null,
	address nvarchar(200) default 'Ha Noi'
)
go

-- C2:
alter table BaiDoXe
add constraint default_address default 'Ha Noi' for address
go

select * from BaiDoXe

insert into BaiDoXe (baidoxe_name)
values
('BDX 05') --Khi insert du lieu nhu nay -> address: 'Ha Noi'
go

-- Bo sung them column
alter table ChuSoHuu
add salary int default 0
go

select * from ChuSoHuu

update ChuSoHuu set salary = 100

insert into ChuSoHuu (fullname, cmtnd, address, salary)
values
('F', '2323434', 'Nam Dinh', -2000)
go

-- C1:
create table ChuSoHuu (
	owner_id int primary key identity(1,1),
	fullname nvarchar(50),
	cmtnd nvarchar(20),
	address nvarchar(200),
	salary int,
	check (salary > 0)
)
go

delete from ChuSoHuu where owner_id = 6

-- C2:
alter table ChuSoHuu
add constraint check_salary check (salary > 0)
go

select * from ChuSoHuu

-- Add them column:email cho ChuSoHuu
-- Email: Gia tri cua no duy nhat trong bang -> ko trung vs thanh vien khac & email co the de null
alter table ChuSoHuu
add email nvarchar(150)
go

select * from ChuSoHuu

drop table ChuSoHuu

alter table ChuSoHuu
alter column email nvarchar(150) not null
go

alter table ChuSoHuu
add constraint unique_email unique (email)
go

insert into ChuSoHuu (fullname, cmtnd, address, salary, email)
values
('Nguyen A', '12312312', 'Ha Noi', 100, 'a@gmail.com')
go

insert into ChuSoHuu (fullname, cmtnd, address, salary, email)
values
('Nguyen B', '34543543', 'Ha Noi', 200, 'a@gmail.com') --Bao failed
go

-- primary key & unique -> Khac nhau cho nao???
-- Tai sao su dung unique ma ko su dung primary key

-- Select data
---- Hiển thị thông tin người gửi xe (số cmtnd (ChuSoHuu), tên (ChuSoHuu)
----------------- tên bãi đỗ xe (BaiDoXe), biển số xe (ThongTinGui))
insert into ThongTinGui (baidoxe_id, owner_id, vehicle_name, vehicle_no)
values
(1, 1, 'A', 'No01')
go

select ChuSoHuu.cmtnd, ChuSoHuu.fullname, BaiDoXe.baidoxe_name, ThongTinGui.vehicle_no
from ChuSoHuu, BaiDoXe, ThongTinGui
where ThongTinGui.baidoxe_id = BaiDoXe.baidoxe_id
	and ThongTinGui.owner_id = ChuSoHuu.owner_id
go
--12312312 | Nguyen A | BDX 01 | No01

-- Them data

insert into ChuSoHuu (fullname, cmtnd, address, salary, email)
values
('Nguyen B', '34534534', 'Ha Noi', 100, 'b@gmail.com'),
('Nguyen C', '654654654', 'Ha Noi', 100, 'c@gmail.com'),
('Nguyen D', '456456456', 'Ha Noi', 100, 'd@gmail.com'),
('Nguyen E', '546476867', 'Ha Noi', 100, 'e@gmail.com')
go

insert into ThongTinGui (baidoxe_id, owner_id, vehicle_name, vehicle_no)
values
(1, 1, 'A', 'No01'),
(1, 3, 'B', 'No02'),
(2, 1, 'A', 'No01'),
(2, 1, 'A', 'No01'),
(2, 3, 'B', 'No02'),
(1, 3, 'B', 'No02')
go

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

delete from ThongTinGui

-- 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, biển số xe, Chủ Sở Hữu, Số CMTND, Số Lần Gửi
select ChuSoHuu.cmtnd, ChuSoHuu.fullname, BaiDoXe.baidoxe_name, ThongTinGui.vehicle_no, count(*) 'So Lan Gui'
from ChuSoHuu, BaiDoXe, ThongTinGui
where ThongTinGui.baidoxe_id = BaiDoXe.baidoxe_id
	and ThongTinGui.owner_id = ChuSoHuu.owner_id
group by ChuSoHuu.cmtnd, ChuSoHuu.fullname, BaiDoXe.baidoxe_name, ThongTinGui.vehicle_no
go

-- Cung cau hoi tren -> hien thi so lan gui xe >= 2
select ChuSoHuu.cmtnd, ChuSoHuu.fullname, BaiDoXe.baidoxe_name, ThongTinGui.vehicle_no, count(*) 'So Lan Gui'
from ChuSoHuu, BaiDoXe, ThongTinGui
where ThongTinGui.baidoxe_id = BaiDoXe.baidoxe_id
	and ThongTinGui.owner_id = ChuSoHuu.owner_id
group by ChuSoHuu.cmtnd, ChuSoHuu.fullname, BaiDoXe.baidoxe_name, ThongTinGui.vehicle_no
having count(*) >= 2
go

-- count, sum, max, min, avg
---- Quan ly diem thi:
create table Student (
	id int primary key identity(1,1),
	fullname nvarchar(50)
)
go

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

create table Marks (
	student_id int not null,
	subject_id int not null,
	mark float,
	primary key (student_id, subject_id)
)
go

--Marks
--subject_id | student_id | mark
--1				1			8
--1				2			9
--2				1			10
--1				1			6 -> Sai

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

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

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

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

-- Ten SV, Mon Hoc, Diem
select Student.fullname, Subject.subject_name, Marks.mark
from Student, Subject, Marks
where Student.id = Marks.student_id
	and Subject.id = Marks.subject_id
go

-- Tim diem thi max cua tung sinh vien
select Student.fullname, max(Marks.mark) 'Max'
from Student, Subject, Marks
where Student.id = Marks.student_id
	and Subject.id = Marks.subject_id
group by Student.fullname
go

-- Tim diem thi max cua tung sinh vien
select Student.fullname, min(Marks.mark) 'Min'
from Student, Subject, Marks
where Student.id = Marks.student_id
	and Subject.id = Marks.subject_id
group by Student.fullname
go

-- Tim diem thi max cua tung sinh vien
select Student.fullname, avg(Marks.mark) 'Diem Trung Binh'
from Student, Subject, Marks
where Student.id = Marks.student_id
	and Subject.id = Marks.subject_id
group by Student.fullname
go