Nội dung kiến thức:
- Thiết kế diagram trong database:
- Tables: Thiết kế nhiều bảng
	- Constraint trong thiết kế bảng: unique, check, foreign key
	- Select
		- Distinct
		- Nối nhiều bảng lại (join)
		- Group By (count, sum, avg, min, max) .. having
		- Order By
		- Where nâng cao (like, in, not like, not in)
=======================================================================
Mini Project: Quản lý bãi đỗ xe (1795)

1) Thiết kế diagram cho database

-- Tao CSDL: BT1795
create database BT1795
go

-- Kich hoat CSDL: BT1795
use BT1795
go

-- Thiet ke Tables trong database
create table BaiDoXe (
	MaBDX int primary key identity(1,1),
	Name nvarchar(50) not null,
	Address nvarchar(200)
)
go

create table ThongTinGui (
	ID int primary key identity(1,1),
	TenXe nvarchar(50),
	BienSoXe nvarchar(20),
	MaBDX int,
	OwnerID int
)
go

create table Owner (
	ID int primary key identity(1,1),
	Name nvarchar(50),
	CMTND nvarchar(20),
	Address nvarchar(200)
)
go

-- Insert du lieu
insert into BaiDoXe (Name, Address)
values
('BDX 01', 'Cau Giay, Ha Noi'),
('BDX 02', 'Ba Dinh, Ha Noi')
go

insert into Owner (Name, CMTND, Address)
values
('A', '123456', 'Ha Noi'),
('B', '234567', 'Ha Noi')

insert into ThongTinGui (TenXe, BienSoXe, MaBDX, OwnerID)
values
('Xe 01', 'BX01', 1, 1),
('Xe 02', 'BX02', 1, 2),
('Xe 01', 'BX01', 2, 1),
('Xe 03', 'BX03', 2, 2)
go

-- TEST
select * from BaiDoXe
select * from Owner
select * from ThongTinGui

-- Tim hieu ve foreign key
---- Nhan xet: tu du lieu trong bang ThongTinGui -> check duoc tat ca cac thong tin
insert into ThongTinGui (TenXe, BienSoXe, MaBDX, OwnerID)
values
('Xe 01', 'BX01', 5, 1)
---- Thiet ke -> Bang ThongTinGui -> 1 ban ghi co MaBDX: 5 -> khong tra cuu duoc thong tin cua MaBDX nay???
---- Ban ghi nay la ban ghi error
---- Bai toan dat ra: lam sao de khi chen 1 ban ghi error -> ko cho phep chen du lieu vao -> error
---- Foreign key
delete from ThongTinGui where ID = 5
delete from ThongTinGui where ID = 7

---- Tao foreign key trong bang ThongTinGui
--C1: Tao luc tao bang ThongTinGui
create table ThongTinGui (
	ID int primary key identity(1,1),
	TenXe nvarchar(50),
	BienSoXe nvarchar(20),
	MaBDX int references BaiDoXe (MaBDX),
	OwnerID int
)
go

--C2: Tao sau khi bang ThongTinGui -> da tao thanh cong truoc do
alter table ThongTinGui
add constraint fk_mabdx foreign key (MaBDX) references BaiDoXe (MaBDX)
go

alter table ThongTinGui
add foreign key (MaBDX) references BaiDoXe (MaBDX)
go

-- Sau nay -> xoa dc foreign key di
alter table ThongTinGui
drop constraint fk_mabdx

alter table ThongTinGui
drop constraint FK__ThongTinG__MaBDX__3C69FB99

---- Sau khi tao xong : check lai cau lenh tren
insert into ThongTinGui (TenXe, BienSoXe, MaBDX, OwnerID)
values
('Xe 01', 'BX01', 5, 1)

insert into ThongTinGui (TenXe, BienSoXe, MaBDX, OwnerID)
values
('Xe 01', 'BX01', 2, 1)

insert into ThongTinGui (TenXe, BienSoXe, MaBDX, OwnerID)
values
('Xe 05', 'BX05', 1, 2)

update ThongTinGui set TenXe = 'Xe 09' where ID = 9

---- Sua foreign key OwnerID trong bang ThongTinGui
-- C1
create table ThongTinGui (
	ID int primary key identity(1,1),
	TenXe nvarchar(50),
	BienSoXe nvarchar(20),
	MaBDX int references BaiDoXe (MaBDX), -- Co the su dung cach nay
	OwnerID int references Owner (ID) -- Co the su dung cach nay
)
go

-- C2
create table ThongTinGui (
	ID int primary key identity(1,1),
	TenXe nvarchar(50),
	BienSoXe nvarchar(20),
	MaBDX int references BaiDoXe (MaBDX),
	OwnerID int,
	foreign key (OwnerID) references Owner (ID)
)
go

-- C3 -> Nen su dung cach nay
alter table ThongTinGui
add constraint fk_owner_id foreign key (OwnerID) references Owner (ID)

-- C4
alter table ThongTinGui
add foreign key (OwnerID) references Owner (ID)

-- Phan 2: unique
select * from BaiDoXe
select * from Owner
select * from ThongTinGui

insert into Owner (Name, CMTND, Address)
values
('C', '123456', 'Ha Noi')
---- Xoa ban ghi error
delete from Owner where ID = 3

---- CMTND -> unique
---- C1:
create table Owner (
	ID int primary key identity(1,1),
	Name nvarchar(50),
	CMTND nvarchar(20) unique,
	Address nvarchar(200)
)
go

---- C2:
alter table Owner
add constraint unique_cmtnd unique (CMTND)

---- Test lai data unique
insert into Owner (Name, CMTND, Address)
values
('C', '123456', 'Ha Noi')

insert into Owner (Name, CMTND, Address)
values
('C', '123457', 'Ha Noi')

alter table Owner
add Age int

insert into Owner (Name, CMTND, Address, Age)
values
('C', '123458', 'Ha Noi', -10)

insert into Owner (Name, CMTND, Address, Age)
values
('E', '123468', 'Ha Noi', 200)

select * from Owner
---- Thiet ke: age >= 0 && age <= 100
delete from Owner where ID >= 6

alter table Owner
add constraint check_age check (Age between 0 and 100)

insert into Owner (Name, CMTND, Address, Age)
values
('C', '123458', 'Ha Noi', 10)

-- Phan 3: Select -> Nhieu tables
select * from ThongTinGui

select TenXe from ThongTinGui

select distinct TenXe from ThongTinGui

select * from ThongTinGui

-- Tim kiem khong chinh xac -> Tim ban ghi ma TenXe co chua 02
select * from ThongTinGui
where TenXe like '%02%'

insert into ThongTinGui (TenXe, BienSoXe, MaBDX, OwnerID)
values
('Xe 02 OKOK', 'BX01', 2, 1)

select * from ThongTinGui
where TenXe not like '%02%'

-- Tim kiem BienSoXe co gia tri BX01, BX02
select * from ThongTinGui
where BienSoXe = 'BX01' OR BienSoXe = 'BX02'

select * from ThongTinGui
where BienSoXe in ('BX01', 'BX02')

select * from ThongTinGui
where BienSoXe not in ('BX01', 'BX02')

select * from ThongTinGui
order by OwnerID asc

select * from ThongTinGui
order by OwnerID desc

-- Tiep
select * from ThongTinGui

---- Dem so lan gui xe cua tung nguoi dung
select OwnerID, count(OwnerID) as Total
from ThongTinGui
group by OwnerID

select OwnerID
from ThongTinGui

select BienSoXe, OwnerID, count(OwnerID) as Total from ThongTinGui
group by BienSoXe, OwnerID

select BienSoXe, OwnerID, sum(OwnerID) as Sum from ThongTinGui
group by BienSoXe, OwnerID

select BienSoXe, OwnerID, max(OwnerID) as Max from ThongTinGui
group by BienSoXe, OwnerID

select BienSoXe, OwnerID, min(OwnerID) as Min from ThongTinGui
group by BienSoXe, OwnerID

select BienSoXe, OwnerID, avg(OwnerID) as AVG from ThongTinGui
group by BienSoXe, OwnerID

select BienSoXe, OwnerID from ThongTinGui

select BienSoXe, OwnerID, count(OwnerID) as Total from ThongTinGui
group by BienSoXe, OwnerID

-- Dieu kien : Total >= 2 -> ket qua cua count, sum, min, max, avg -> su dung having kiem tra dieu kien
select BienSoXe, OwnerID, count(OwnerID) as Total from ThongTinGui
group by BienSoXe, OwnerID
having count(OwnerID) > 2

-- TEST
select * from BaiDoXe
select * from Owner
select * from ThongTinGui

delete from ThongTinGui where ID > 2
delete from BaiDoXe where MaBDX > 2
delete from Owner where ID > 2

---- Hien thi du lieu thong tin gui xe theo yeu cau sau
------ OwnerID, Name (Owner Name), MaBDX, Name (BDX Name), Ten Xe, Bien So Xe
------ Nhan xet: ThongTinGui, BaiDoXe, Owner
select Owner.ID OwnerID, Owner.Name 'Owner Name', BaiDoXe.MaBDX, BaiDoXe.Name 'BDX Name', ThongTinGui.TenXe, ThongTinGui.BienSoXe
from ThongTinGui, BaiDoXe, Owner
where ThongTinGui.MaBDX = BaiDoXe.MaBDX and ThongTinGui.OwnerID = Owner.ID