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

create table ChuSoHuu (
	id int primary key identity(1,1),
	fullname nvarchar(50),
	cccd nvarchar(30),
	address nvarchar(200)
)
go

create table ThongTinGui (
	id int primary key identity(1,1),
	name nvarchar(50),
	carno nvarchar(20),
	baidoxe_id int references BaiDoXe(id),
	chusohuu_id int
)
go

alter table ThongTinGui
add constraint fk_thongtingui_chusohuu foreign key (chusohuu_id) references ChuSoHuu (id)
go

-- Insert du lieu
insert into BaiDoXe (name, address)
values
('Bai Do Xe 1', 'A'),
('Bai Do Xe 2', 'B'),
('Bai Do Xe 3', 'C'),
('Bai Do Xe 4', 'D'),
('Bai Do Xe 5', 'E')
go

insert into ChuSoHuu (fullname, cccd, address)
values
('Tran Van A', '123', 'Ha Noi'),
('Tran Van B', '434', 'Ha Noi'),
('Tran Van C', '554', 'Ha Noi'),
('Tran Van D', '234', 'Ha Noi'),
('Tran Van E', '546', 'Ha Noi')
go

insert into ThongTinGui (baidoxe_id, chusohuu_id, carno, name)
values
(2, 1, 'R001', 'C01'),
(2, 2, 'R002', 'C02'),
(2, 2, 'R002', 'C02'),
(3, 3, 'R003', 'C03'),
(3, 3, 'R003', 'C03')
go

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

-- Query
-- Hiển thị thông tin người gửi xe (số cmtnd, tên, tên bãi đỗ xe, biển số xe)
-- Phan tich: cccd (ChuSoHuu), ten (ChuSoHuu), ten bai do xe (BaiDoXe), bien so xe (ThongTinGui)

delete from ThongTinGui where id > 3
delete from ChuSoHuu where id > 2
delete from BaiDoXe where id > 3

-- Xem thong: cccd (ChuSoHuu), ten (ChuSoHuu), bien so xe (ThongTinGui)
-- CCCD, Ten, Bien So Xe: Chinh xac
-- 123, Tran Van A, R001
-- 434, Tran Van B, R002

-- Xuat hien du lieu nhu sau:
-- CCCD, Ten, Bien So Xe: Chinh xac
-- 123, Tran Van A, R001
-- 434, Tran Van B, R002
-- 123, Tran Van A, R002
-- 434, Tran Van B, R001

-- Hien thi thong tin cccd, ten trong bang ChuSoHuu
select cccd, fullname from ChuSoHuu

-- Hien thi bien so xe trong bang ThongTinGui
select carno from ThongTinGui

-- Hien thi CCCD, ten chu so huu trong bang ChuSoHuu, bien so xe trong bang ThongTinGui
select ChuSoHuu.cccd, ChuSoHuu.fullname, ThongTinGui.carno
from ChuSoHuu, ThongTinGui
where ChuSoHuu.id = ThongTinGui.chusohuu_id

select ChuSoHuu.cccd, ChuSoHuu.fullname, ThongTinGui.carno, BaiDoXe.name
from ChuSoHuu, ThongTinGui, BaiDoXe
where ChuSoHuu.id = ThongTinGui.chusohuu_id
	and ThongTinGui.baidoxe_id = BaiDoXe.id
go

---- Select
select ChuSoHuu.cccd, ChuSoHuu.fullname, ThongTinGui.carno, BaiDoXe.name
from ChuSoHuu, ThongTinGui, BaiDoXe
where ChuSoHuu.id = ThongTinGui.chusohuu_id
	and ThongTinGui.baidoxe_id = BaiDoXe.id
	and BaiDoXe.id = 2
go

select ChuSoHuu.cccd, ChuSoHuu.fullname, ThongTinGui.carno, BaiDoXe.name
from ChuSoHuu, ThongTinGui, BaiDoXe
where ChuSoHuu.id = ThongTinGui.chusohuu_id
	and ThongTinGui.baidoxe_id = BaiDoXe.id
	and (BaiDoXe.id = 2
	or BaiDoXe.id = 3)
go

select ChuSoHuu.cccd, ChuSoHuu.fullname, ThongTinGui.carno, BaiDoXe.name
from ChuSoHuu, ThongTinGui, BaiDoXe
where ChuSoHuu.id = ThongTinGui.chusohuu_id
	and ThongTinGui.baidoxe_id = BaiDoXe.id
	and BaiDoXe.id in (2,3)
go

-- id: chau tu 2 -> 4
select ChuSoHuu.cccd, ChuSoHuu.fullname, ThongTinGui.carno, BaiDoXe.name
from ChuSoHuu, ThongTinGui, BaiDoXe
where ChuSoHuu.id = ThongTinGui.chusohuu_id
	and ThongTinGui.baidoxe_id = BaiDoXe.id
	and (BaiDoXe.id >= 2
	or BaiDoXe.id <= 4)
go

select ChuSoHuu.cccd, ChuSoHuu.fullname, ThongTinGui.carno, BaiDoXe.name
from ChuSoHuu, ThongTinGui, BaiDoXe
where ChuSoHuu.id = ThongTinGui.chusohuu_id
	and ThongTinGui.baidoxe_id = BaiDoXe.id
	and BaiDoXe.id between 2 and 4
go

-- Thong ke du lieu
select ChuSoHuu.cccd, ChuSoHuu.fullname, ThongTinGui.carno, BaiDoXe.name
from ChuSoHuu, ThongTinGui, BaiDoXe
where ChuSoHuu.id = ThongTinGui.chusohuu_id
	and ThongTinGui.baidoxe_id = BaiDoXe.id
go

-- Show du lieu
-- Bai Do Xe 1, 3
-- Bai Do Xe 2, 2
select BaiDoXe.name, count(BaiDoXe.name) as 'So Lan Gui'
from ThongTinGui, BaiDoXe
where ThongTinGui.baidoxe_id = BaiDoXe.id
group by BaiDoXe.name
go

select ThongTinGui.carno, BaiDoXe.name, count(BaiDoXe.id) as 'So Lan Gui'
from ThongTinGui, BaiDoXe
where ThongTinGui.baidoxe_id = BaiDoXe.id
group by ThongTinGui.carno, BaiDoXe.name
go

-- count, max, min, avg, sum
select BaiDoXe.name, count(BaiDoXe.id) as 'So Lan Gui'
from ThongTinGui, BaiDoXe
where ThongTinGui.baidoxe_id = BaiDoXe.id
group by BaiDoXe.name
having count(BaiDoXe.name) >= 3
go