Nội dung kiến thức:
- Tạo database
- Active CSDL để thao tác
- Tables: Bảng độc lập (chưa có liên kết bảng)
	- Tạo bảng: Tìm hiểu kỹ hơn về thiết các column trong bảng & primary key & identity
	- Thêm/sửa/xóa 1 column mới vào trong bảng
	- Thêm dữ liệu
	- Hiển thị dữ liệu: Cơ bản
	- Cập nhật dữ liệu: chuẩn chung
	- Xóa dữ liệu: Chuẩn chung

=========================================================================================
Quản lý sản phẩm:
- Thiết kế bảng products
	- id: int -> primary key -> tự tăng (chèn dữ liệu -> ko cần chền column này -> tự động thêm vào)
	- title: nvarchar(250) -> chèn vào -> ko đc đễ rỗng -> not null
	- thumbnail: nvarchar(500) -> link hình ảnh của sản phẩm
	- description: text -> cần nhắc thật kỹ mới dung
	- views: int -> thiết lập giá trị mặc định là 0 -> ko chèn vào -> giá trị này sẽ tự nhận là 0
	- created_at -> datetime (ngày, tháng, năm - giờ, phút, giây) -> Thời gian tạo bản ghi
	- updated_at -> datetime -> Lưu thời gian thay đổi bản ghi
	- category_id -> int
- Thiết kế bảng categories
	- category_id -> primary key -> tự tăng
	- name: tên danh mục sản phẩm

-- Thiet ke CSDL: webbanhang
create database webbanhang
go

-- Kich hoat database: webbanhang
use webbanhang
go

-- Thiet ke bang categories
create table categories (
	category_id int primary key identity(1, 1),
	name nvarchar(150)
)
go

select * from categories
go

insert into categories(name)
values
('Thoi Trang Nam'),
('Thoi Trang Nu'),
('Thoi Trang Kids')
go

-- Tao bang products
create table products (
	id int primary key identity(1,1),
	title nvarchar(250) not null,
	thumbnail nvarchar(500),
	description text
)
go

select * from products
go

-- Bo sung columns moi vao trong tables
alter table products
add views int default 0
go

alter table products
add created_at datetime
go

alter table products
add updated_at datetime
go

alter table products
add category_id int
go

-- Xoa column + thay doi column -> tao 1 column error
alter table products
add x int
go

---- x:int -> sai kieu du lieu -> sua lai thanh x:nvarchar(100)
alter table products
alter column x nvarchar(100)
go

---- x:nvarchar(100) -> add sai -> xoa di
alter table products
drop column x
go

-- Tao 1 table fake -> tao 1 bang dat ten la fake (a1, abc, okok)
create table fake (
	id int primary key identity(1,1),
	name nvarchar(100)
)
go

---- Xoa 1 bang khoi database -> Khi su dung can can than -> xoa 1 bang di -> thi dung lenh nao
drop table fake
go

-- Them du lieu
select * from categories
select * from products

insert into products (title, thumbnail, description, views, created_at, updated_at, category_id)
values
('San pham 1', 'link 01', 'mo ta 1', 100, '2021-03-16 13:06:30', '2021-03-16 13:30:30', 1)
go

insert into products (title, thumbnail, description, created_at, updated_at, category_id)
values
('San pham 2', 'link 02', 'mo ta 2', '2021-06-16 13:06:30', '2021-07-16 13:30:30', 1)
go

insert into products (title, thumbnail, description, views, created_at, updated_at, category_id)
values
('San pham 3', 'link 03', 'mo ta 3', 10, '2021-01-16 13:06:30', '2021-03-16 13:30:30', 1),
('San pham 4', 'link 04', 'mo ta 4', 90, '2021-09-16 13:06:30', '2021-12-16 13:30:30', 1)
go

insert into products (title, thumbnail, description, views, created_at, updated_at, category_id)
values
('San pham 5', 'link 05', 'mo ta 5', 10, '2021-01-16 13:06:30', '2021-03-16 13:30:30', 2),
('San pham 6', 'link 06', 'mo ta 6', 90, '2021-09-16 13:06:30', '2021-12-16 13:30:30', 2)
go

-- Tim hieu ve select
select * from categories
select * from products

---- products: id, title, views, created, category_id
select id, title, views, created_at, category_id from products
go

select id, category_id, views, title, created_at from products
go

---- products: id -> MaSP, category_id -> MaDM, title -> Tieu De SP, created_at -> Ngay Tao, views -> Luot Xem
select id MaSP, category_id as MaDM, views 'Luot Xem', title 'Tieu De SP', created_at as 'Ngay Tao' from products
go

---- Nang cao cau lenh select
------ Yeu cau: Hien thi nhung san pham co luot views > 50
select id MaSP, category_id as MaDM, views 'Luot Xem', title 'Tieu De SP', created_at as 'Ngay Tao'
from products
where views > 50
go

------ Yeu cau: Hien thi nhung san pham co luot views > 50 & Danh Muc SP = 1
select id MaSP, category_id as MaDM, views 'Luot Xem', title 'Tieu De SP', created_at as 'Ngay Tao'
from products
where views > 50 and category_id = 1
go

------ Lam viec voi datetime: coi no nhu la so nguyen -> du lieu truyen vao theo string (nvarchar)
-------- Yeu cau: Hien thi sp -> tao ra trong khoang thoi gian: 2021-05-10 => 2021-09-20
select id MaSP, category_id as MaDM, views 'Luot Xem', title 'Tieu De SP', created_at as 'Ngay Tao'
from products
where created_at >= '2021-05-10' and created_at <= '2021-09-20'
go

select id MaSP, category_id as MaDM, views 'Luot Xem', title 'Tieu De SP', created_at as 'Ngay Tao'
from products
where created_at between '2021-05-10' and '2021-09-20'
go

---- Sap xep du lieu
select id MaSP, category_id as MaDM, views 'Luot Xem', title 'Tieu De SP', created_at as 'Ngay Tao'
from products
where views > 50
order by views asc
go

select id MaSP, category_id as MaDM, views 'Luot Xem', title 'Tieu De SP', created_at as 'Ngay Tao'
from products
where views > 50
order by views desc
go

---- Xoa du lieu
select * from products
go

-- Xoa du lieu products > id: 3
delete from products
where id = 3
go

-- Sua du lieu -> id: 2 => description: mo ta 222, views: 1000
update products set description = 'Mo ta 222', views = 1000
where id = 2
go

--- NANG CAO
-- students (id, rollno, fullname, ...) & class (id: tu tang, name)
-- Thiet ke 1 bang qua ly thong tin sinh vien trong 1 lop hoc
---- class_members (student_id, class_id)
create table students (
	id int primary key identity(1,1),
	rollno nvarchar(20) not null,
	fullname nvarchar(50)
)
go

create table class (
	id int primary key identity(1,1),
	name nvarchar(50)
)
go

insert into students(rollno, fullname)
values
('R001', 'A'),
('R002', 'B'),
('R003', 'C')
go

insert into class(name)
values
('C2110I'),
('C2110L')
go

select * from students
select * from class
select * from class_members

create table class_members (
	student_id int,
	class_id int,
	primary key (student_id, class_id)
)

drop table class_members
go

insert into class_members (student_id, class_id)
values
(1, 1)

insert into class_members (student_id, class_id)
values
(1, 2)