Nội dung kiến thức:
	Tạo database
	Tables
		- Tạo bảng: OK
		- Thêm 1 column mới như nào
		- Sửa kiểu dữ liệu của column như thế nào
		- Xóa column đi
		- Constraint trong  tables
			- primary key: OK
			- unique
			- check
			- not null
			- foreign key là gì -> sử dụng như nào???
	Thao tác dữ liệu
		- update
		- delete
		- select dữ liệu -> đơn giản
=========================================================================
Quản lý sản phẩm
	Danh mục: category
		id: int -> primary key -> identity(1,1)
		name: nvarchar(200)
	Sản phẩm: product
		id: int -> primary key -> identity(1,1)
		title: nvarchar(150)
		thumbnail: nvarchr(500) -> nhập đường dẫn hình ảnh vào đây
		price: float
		description: ntext
		created_at datetime
=========================================================================

-- Tao database
create database C2206L_NEW
go

-- Kich hoat database
use C2206L_NEW
go

-- Tao tables
create table category (
	id int primary key identity(1,1),
	name nvarchar(200) not null
)
go

create table product (
	id int primary key identity(1,1),
	title nvarchar(150) not null
)
go

alter table product
add thumbnail nvarchar(500)
go

alter table product
add price float
go

alter table product
add description ntext
go

alter table product
add created_at datetime
go

alter table product
add test nvarchar(200)
go

-- Xoa column
alter table product
drop column test
go

-- thay du lieu column
alter table product
alter column test int
go

-- Test
select * from product

insert into product(title, thumbnail, price, description, created_at)
values
('San pham 1', 'https://link.jpg', 10000, 'Mo ta', '2022-10-20 08:30:00')
go

insert into product(title, thumbnail, price, description, created_at)
values
('San pham 2', 'https://link.jpg', -10000, 'Mo ta', '2022-10-20 08:30:00')
go

-- Trong thuc te: price >= 0
drop table product
go

-- Check dieu kien price >= 0
alter table product
add constraint ck_price check (price >= 0)
go

-- Bang sinh vien
create table student (
	id int primary key identity(1,1),
	rollno nvarchar(20) not null unique,
	email nvarchar(150) unique,
	fullname nvarchar(50)
)
go

insert into student (rollno, email, fullname)
values
('R001', 'a@gmail.com', 'Tran Van A'),
('R002', 'b@gmail.com', 'Tran Van B'),
('R003', 'c@gmail.com', 'Tran Van C')
go

select * from student

insert into student (rollno, email, fullname)
values
('R001', 'a@gmail.com', 'Tran Van A')
go

drop table student

--- select
select * from student

select id, rollno, fullname from student

select id as 'ID', rollno 'Ma Sinh Vien', fullname as 'Ho & Ten' from student
go

select * from student

-- Them SP
insert into product(title, thumbnail, price, description, created_at)
values
('San pham 2', 'https://link.jpg', 10000, 'Mo ta', '2022-10-20 08:30:00'),
('San pham 3', 'https://link.jpg', 2000, 'Mo ta', '2021-10-20 08:30:00'),
('San pham 4', 'https://link.jpg', 5000, 'Mo ta', '2020-10-20 08:30:00'),
('San pham 5', 'https://link.jpg', 1000, 'Mo ta', '2019-10-20 08:30:00'),
('San pham 6', 'https://link.jpg', 7000, 'Mo ta', '2022-10-20 08:30:00')
go

select * from product
go

select * from product
order by price asc
go

select * from product
order by price desc
go

-- Lay san pham co price > 5000
select * from product
where price > 5000
order by price asc
go

-- Lay san pham co price > 5000 & price < 8000
select * from product
where price > 5000 and price < 8000
order by price asc
go

select * from product
where price > 5000 or price < 2000
order by price asc
go

select * from product
where created_at < '2021-01-01'

select * from product
where title = 'San pham 5'

select * from product
where title like '%5%'

select * from product
where title like '%5'

select * from product
where title like '5%'
-- delete
select * from product

delete from product
where id > 5

-- sua du lieu
update product set price = 20000
where id = 4

-- Phan tich
select * from category
select * from product

insert into category (name)
values
('Danh muc A'),
('Danh muc B')
go

update product set category_id = 1

update product set category_id = 2 where id > 4

alter table product
add category_id int
go

insert into product(title, thumbnail, price, description, created_at, category_id)
values
('San pham 5', 'https://link.jpg', 10000, 'Mo ta', '2022-10-20 08:30:00', 3)
go

delete from product where id = 8

alter table product
add constraint fk_product_category_id foreign key (category_id) references category (id)
go