Nội dung kiến thức:
	Tạo table:
		Các cách tạo ra primary key
		primary key
			1 column
			2 columns trở lên thì sao???
		constraint
		foreign key
			- Ý nghĩa là gì?
			- Các cách tạo ra foreign key
		check -> Điều kiện kiểm tra
	update
	delete
	select
		- Truy cơ bản: 1 bảng
		- Truy vấn có điều kiện -> where
		- Liên kết nhiều bảng -> ???
========================================================

drop table customer
drop table items
drop table orders
drop table orders_detail

-- PHAN 1: TAO TABLES THEO YEU CAU BAI TOAN -> DOC LAP
create table customer (
	-- cach 1: Tao primary key
	cust_no int primary key identity(1,1),
	cust_name nvarchar(50),
	phone_no nvarchar(20)
)
go

create table items (
	item_no nvarchar(20),
	description ntext,
	price money,
	-- Cach 2: Tao primary key
	-- primary key (item_no)
	constraint pk_item_no primary key (item_no)
)
go

alter table items
add constraint check_price check (price >= 0)
go

alter table items
add constraint pk_item_no primary key (item_no)
go

create table orders (
	ord_no int primary key identity(1,1),
	ord_date date,
	cust_no int
)
go

-- Column se lam truong khoa chinh
---- ord_no: khong the lam truong khoa chinh dc -> Nhu da phan tich
---- item_no: khong the lam truong khoa chinh dc -> Nhu da phan tich
drop table orders_detail

create table orders_detail (
	ord_no int not null references orders (ord_no),
	item_no nvarchar(20) not null references items (item_no),
	qty int
)
go

alter table orders_detail
add constraint pk_order_no_item_no primary key (ord_no, item_no)
go

delete from orders_detail

-- THEM DU LIEU
insert into customer (cust_name, phone_no)
values
(N'TRAN VAN A', N'123123123'),
(N'TRAN VAN B', N'323453455')
go

insert into items (item_no, description, price)
values
('SP001', 'san pham 1', 100),
('SP002', 'san pham 2', 200),
('SP003', 'san pham 3', 300)
go

insert into items (item_no, description, price)
values
('SP004', 'san pham 4', -100)
go

insert into items (item_no, description, price)
values
('SP004', 'san pham 4', 1000)
go

insert into orders (cust_no, ord_date)
values
(1, '2022-02-12'),
(2, '2023-02-12'),
(2, '2023-05-12')
go

select * from customer
select * from items
select * from orders
select * from orders_detail

insert into orders_detail (item_no, ord_no, qty)
values
('SP001', 1, 20),
('SP002', 1, 20),
('SP002', 3, 20),
('SP004', 2, 20)
go

insert into orders_detail (item_no, ord_no, qty)
values
('SP001', 1, 20)
go

insert into orders (ord_date, cust_no)
values
('2023-03-12', 5)
go

delete from orders

-- Thiet ke foreign key cust_no (orders) -> Lien ket voi column cust_no trong bang customer
alter table orders
add constraint fk_orders_cust_no foreign key (cust_no) references customer (cust_no)
go

-- PHAN 2: xoa du lieu
delete from orders
go

delete from items where item_no = 'SP003'
go

delete from items where item_no = 'SP002' AND item_no = 'SP001'
go

delete from items where item_no = 'SP002' OR item_no = 'SP001'
go

delete from items where item_no in ('SP001', 'SP002')
go

-- PHAN 3: Update
select * from customer
go

---- Sua lai SDT cua 'TRAN VAN A' Thanh '0967025996'
update customer set phone_no = '0967025996' where cust_name = 'TRAN VAN A'
go

update customer set phone_no = '0967025996', cust_name = 'TRAN VAN AAA' where cust_no = 1
go

-- PHAN 4: TIM KIEU SELECT
select * from customer
go

select * from customer where phone_no = '0967025996'
go

---- HIEN THI KHACH HANG -> CO TEN CHUA CUM 'VAN B'
select * from customer where cust_name like '%VAN B%'
go

select * from customer where cust_name like '%VAN B'
go

select * from customer where cust_name like 'VAN B%'
go

select * from items
go

---- TIM SAN PHAM CO GIA > 150
select * from items where price > 150
go

select * from items where price <= 150
go

---- TIM SAN PHAN CO GIA: 150 TOI 500
select * from items where price >= 150 and price <= 500
go

select * from items where price between 150 and 500
go

-- XEM CO NHUNG SAN PHAM NAO DA DC BAN RA
select * from orders_detail
go

select item_no from orders_detail
go

select item_no from orders_detail
go

select distinct item_no from orders_detail
go

select * from customer
select * from items
select * from orders
select * from orders_detail

---- Thon tin don hang: ma don hang (orders), ten khach hang (customer), sdt (customer), ngay dat don hang (orders)
select orders.ord_no, customer.cust_name, customer.phone_no, orders.ord_date
from customer, orders
where orders.cust_no = customer.cust_no
go