-- Test
select * from BaiDoXe
select * from ChuSoHuu
select * from ThongTinGui

-- Xem thong tin -> khach hang thuoc tinh nao?
select address from ChuSoHuu
go

select distinct address from ChuSoHuu
go

-- Xem thong tin khac hang
select top(3) * from ChuSoHuu
order by id asc
go

select top(3) * from ChuSoHuu
order by id desc
go

select id, fullname from ChuSoHuu
where id < 6
union
select id, fullname from ChuSoHuu
where id > 7

-- template table
select * from (select id, fullname from ChuSoHuu
where id < 6
union
select id, fullname from ChuSoHuu
where id > 7) as A
where A.fullname like 'B%'
go

select * from (select id, fullname from ChuSoHuu
where id < 6
union
select id, fullname from ChuSoHuu
where id > 7) as A
where A.fullname not like 'B%'
go

----- join
select * from ChuSoHuu
select * from ThongTinGui

select ChuSoHuu.id, ChuSoHuu.fullname, ThongTinGui.name 'Car Name', ThongTinGui.carno
from ChuSoHuu, ThongTinGui
where ChuSoHuu.id = ThongTinGui.chusohuu_id
go

select ChuSoHuu.id, ChuSoHuu.fullname, ThongTinGui.name 'Car Name', ThongTinGui.carno
from ChuSoHuu inner join ThongTinGui on ChuSoHuu.id = ThongTinGui.chusohuu_id
go

select ChuSoHuu.id, ChuSoHuu.fullname, ThongTinGui.name 'Car Name', ThongTinGui.carno
from ChuSoHuu join ThongTinGui on ChuSoHuu.id = ThongTinGui.chusohuu_id
go

-- left join
select ChuSoHuu.id, ChuSoHuu.fullname, ThongTinGui.name 'Car Name', ThongTinGui.carno
from ChuSoHuu left join ThongTinGui on ChuSoHuu.id = ThongTinGui.chusohuu_id
go

select ChuSoHuu.id, ChuSoHuu.fullname, ThongTinGui.name 'Car Name', ThongTinGui.carno
from ThongTinGui left join ChuSoHuu on ChuSoHuu.id = ThongTinGui.chusohuu_id
go

-- right join
select ChuSoHuu.id, ChuSoHuu.fullname, ThongTinGui.name 'Car Name', ThongTinGui.carno
from ThongTinGui right join ChuSoHuu on ChuSoHuu.id = ThongTinGui.chusohuu_id
go

-- full outer join
select ChuSoHuu.id, ChuSoHuu.fullname, ThongTinGui.name 'Car Name', ThongTinGui.carno
from ThongTinGui full outer join ChuSoHuu on ChuSoHuu.id = ThongTinGui.chusohuu_id
go

-- Dem so lan gui xe
select ChuSoHuu.id, ChuSoHuu.fullname, count(ThongTinGui.id) 'So Lan Gui'
from ChuSoHuu, ThongTinGui
where ChuSoHuu.id = ThongTinGui.chusohuu_id
group by ChuSoHuu.id, ChuSoHuu.fullname
go


-- left
select ChuSoHuu.id, ChuSoHuu.fullname, count(ThongTinGui.id) 'So Lan Gui'
from ChuSoHuu left join ThongTinGui on ChuSoHuu.id = ThongTinGui.chusohuu_id
group by ChuSoHuu.id, ChuSoHuu.fullname
order by 'So Lan Gui' asc
go

select ChuSoHuu.id, ChuSoHuu.fullname, count(ThongTinGui.id) 'So Lan Gui'
from ChuSoHuu left join ThongTinGui on ChuSoHuu.id = ThongTinGui.chusohuu_id
group by ChuSoHuu.id, ChuSoHuu.fullname
having count(ThongTinGui.id) = 0
order by 'So Lan Gui' asc
go