Nội dung kiến thức:
	- Tạo database
	- Kích hoạt database
	- Tạo tables
		- create table
			- primary key -> DONE
			- identity -> DONE
			- check -> DONE
			- unique -> DONE
		- alter table -> DONE
			- Thêm column mới vào table
			- Thay đổi kiểu dữ liệu của column
			- Xóa column
	- Insert -> DONE
	- Update -> DONE
	- Delete -> DONE
	- Drop table -> DONE
	- select -> DONE
		- Select căn bản -> DONE
		- Select where -> DONE
==========================================================
Table: customers
	- cust_no -> int
	- cust_name -> nvarchar(50)
	- birthday -> date
	- gender -> nvarchar(12)
	- email -> nvarchar(150)
	- address -> nvarchar(200)

Table: Items
	- item_no: nvarchar(12) -> primary key
	- title: nvarchar(250)
	- price float

-- Tao CSDL
-- doc: https://www.w3schools.com/sql/sql_alter.asp
create database BT2169
go

-- Kich hoat CSDL
use BT2169
go

-- Tao tables
create table customers (
	cust_no int not null,
	cust_name nvarchar(50),
	birthday date, --du lieu them vao: yyyy-mm-dd
	gender nvarchar(12)
)
go

-- Them column vao bang
alter table customers
add email nvarchar(150)
go

alter table customers
add address nvarchar(200)
go

alter table customers
add salary nvarchar(20)
go

-- Sua kieu du lieu cua 1 column
alter table customers
alter column salary money
go

-- Xoa 1 column -> Ko can su dung trong table
alter table customers
drop column salary
go

-- insert data
---- Insert 1 ban ghi
insert into customers (cust_no, cust_name, gender, birthday, email, address)
values
(1, 'KH A', 'Nam', '1999-02-12', 'a@gmail.com', 'Ha Noi')
go

---- Insert nhiêu bản ghi cùng lúc
insert into customers (cust_no, cust_name, gender, birthday, email, address)
values
(2, 'KH A', 'Nam', '1999-02-12', 'a@gmail.com', 'Ha Noi'),
(3, 'KH B', 'Nam', '1998-02-12', 'b@gmail.com', 'Ha Noi'),
(4, 'KH C', 'Nam', '1989-02-12', 'c@gmail.com', 'Ha Noi'),
(5, 'KH D', 'Nam', '1993-02-12', 'd@gmail.com', 'Ha Noi'),
(6, 'KH E', 'Nam', '1992-02-12', 'e@gmail.com', 'Ha Noi')
go

-- Xem du lieu
select * from customers
---- TH: hien thi 1 vai column: cust_no, cust_name, email
select cust_no, cust_name, email from customers
---- TH: hien thi 1 vai column: cust_no -> MaKH, cust_name TenKH, email Dia Chi Email
select cust_no MaKH, cust_name as TenKH, email 'Dia Chi Email' from customers

-- Select Where
---- Hien thi khach hang co cust_no > 2
select * from customers
where cust_no > 2
go

---- Hien thi khach hang co cust_no >= 2 && cust_no <= 5
select * from customers
where cust_no >= 2 and cust_no <= 5
go

---- Tuong tu cau lenh sau
select * from customers
where cust_no between 2 and 5
go

---- Hien thi thong tin KH co ten 'KH A'
select * from customers
where cust_name = 'KH A'
go

---- date
---- Hien thi danh sach sinh vien co ngay sinh: 1993-01-01 toi 1999-06-02
select * from customers
where birthday between '1993-01-01' and '1999-06-02'
go

select * from customers
where birthday >= '1993-01-01' and birthday <= '1999-06-02'
go

-- UPDATE
select * from customers

---- Sua thong tin KH cust_no = 3 -> address: Nam Dinh
update customers set address = 'Nam Dinh' where cust_no = 3
go

---- Sua thong tin KH cust_no = 2 -> address: Nam Dinh, gender = Nu
update customers set address = 'Nam Dinh', gender = 'Nu' where cust_no = 2
go

---- Se thay doi du lieu cua tat ca cac ban ghi: gender = Nu
update customers set gender = 'Nu'
go

update customers set address = 'Ninh Binh'
where cust_no = 5 or cust_no = 6
go

---- Viet lai thanh cau lenh sau
update customers set address = 'Ninh Binh'
where cust_no in (5, 6)
go

-- DELETE
select * from customers

delete from customers where cust_no = 4
go

delete from customers
go

update customers set address = ''

-- DROP
-- drop table customers
-- go

-- Insert Data
insert into customers (cust_no, cust_name, gender, birthday, email, address)
values
(1, 'KH A', 'Nam', '1999-02-12', 'a@gmail.com', 'Ha Noi')
go

select * from customers
go

insert into customers (cust_no, cust_name, gender, birthday, email, address)
values
(1, 'KH A', 'Nam', '1999-02-12', 'a@gmail.com', 'Ha Noi')
go

insert into customers (cust_no, cust_name, gender, birthday, email, address)
values
(1, 'KH A', 'Nam', '1999-02-12', 'a@gmail.com', 'Ha Noi')
go

update customers set address = 'Nam Dinh'
where cust_no = 1

-- Thiet ke table -> bat buoc co 1 column: primary key
---- Dieu kien 1 column tro thanh primary key
-------------- 1) Nhan gia tri duy nhat
-------------- 2) Ko dc de empty (rong) - NULL
-- Tao primary key -> cust_no
drop table customers

create table customers (
	cust_no int primary key,
	cust_name nvarchar(50),
	birthday date, --du lieu them vao: yyyy-mm-dd
	gender nvarchar(12),
	email nvarchar(150),
	address nvarchar(200)
)
go

insert into customers (cust_no, cust_name, gender, birthday, email, address)
values
(1, 'KH A', 'Nam', '1999-02-12', 'a@gmail.com', 'Ha Noi')
go

insert into customers (cust_no, cust_name, gender, birthday, email, address)
values
(1, 'KH A', 'Nam', '1999-02-12', 'a@gmail.com', 'Ha Noi')
go

-- Chuyen thanh cau lenh sau -> Thanh cong
insert into customers (cust_no, cust_name, gender, birthday, email, address)
values
(2, 'KH A', 'Nam', '1999-02-12', 'a@gmail.com', 'Ha Noi')
go

select * from customers

-- identity -> chi su dung vs int, tinyint, smallint, bigint -> integer
drop table customers

create table customers (
	cust_no int primary key identity(1,1),
	cust_name nvarchar(50),
	birthday date, --du lieu them vao: yyyy-mm-dd
	gender nvarchar(12),  -- gender: Nam, Nu, Khac
	email nvarchar(150),
	address nvarchar(200)
)
go

insert into customers (cust_name, gender, birthday, email, address)
values
('KH A', 'Nam', '1999-02-12', 'a@gmail.com', 'Ha Noi')
go

select * from customers

delete from customers

-- Tao bang items
create table items (
	item_no nvarchar(12) primary key,
	title nvarchar(150),
	price float
)
go

insert into items(item_no, title, price)
values
('R001', 'San Pham 1', 1000),
('R002', 'San Pham 2', 3000)
go

select * from items

insert into items(item_no, title, price)
values
('R003', 'San Pham 3', -2000)
go

delete from items where item_no = 'R003'
go

drop table items

create table items (
	item_no nvarchar(12) primary key,
	title nvarchar(150),
	price float,
	check (price > 0) -- Yeu cau price nhap vao > 0 : price > 1000 and price < 10000
)
go

drop table customers

create table customers (
	cust_no int primary key identity(1,1),
	cust_name nvarchar(50),
	birthday date, --du lieu them vao: yyyy-mm-dd
	gender nvarchar(12),  -- gender: Nam, Nu, Khac
	email nvarchar(150),
	address nvarchar(200),
	check (gender in ('Nam', 'Nu', 'Khac'))
)
go

insert into customers (cust_name, gender, birthday, email, address)
values
('KH A', '34435', '1999-02-12', 'a@gmail.com', 'Ha Noi')
go

insert into customers (cust_name, gender, birthday, email, address)
values
('KH A', 'Nam', '1999-02-12', 'a@gmail.com', 'Ha Noi')
go

select * from customers

-- unique <-> primary key
---- 1) Gia tri la duy nhat
---- 2) De trong (Diem khac vs primary key)
drop table customers

create table customers (
	cust_no int primary key identity(1,1),
	cust_name nvarchar(50),
	birthday date, --du lieu them vao: yyyy-mm-dd
	gender nvarchar(12),  -- gender: Nam, Nu, Khac
	email nvarchar(150),
	address nvarchar(200),
	check (gender in ('Nam', 'Nu', 'Khac')),
	unique (email)
)
go

insert into customers (cust_name, gender, birthday, email, address)
values
('KH A', 'Nam', '1999-02-12', 'a@gmail.com', 'Ha Noi')
go

insert into customers (cust_name, gender, birthday, email, address)
values
('KH A', 'Nam', '1999-02-12', 'b@gmail.com', 'Ha Noi')
go

select * from customers

insert into customers (cust_name, gender, birthday, address)
values
('KH A', 'Nam', '1999-02-12', 'Ha Noi')
go