Nội dung:
- Tạo bảng
	- primary key
	- unique
	- check
	- default
- Tạo bảng
	- Thêm column mới vào bảng
	- Sửa kiểu dữ liệu của 1 bảng
	- Xóa 1 column mới đi
- Dữ liệu
	- Sửa dữ liệu
	- Xóa dữ liệu đi

-- Create database
create database C2010G

-- Active database
use C2010G

-- Create table
create table Customer (
	cust_no int,
	cust_name nvarchar(50),
	phone_no nvarchar(20)
)

create table Items (
	item_no int,
	description nvarchar(500),
	price float
)

create table orders (
	ord_no int,
	ord_date date,
	cust_no int
)

create table orders_detail (
	ord_no int,
	item_no int,
	qty int
)

-- Insert Data
insert into Customer(cust_no, cust_name, phone_no)
values
(2, 'TRAN VAN B', '34123')

insert into Customer(cust_no, cust_name, phone_no)
values
(3, 'TRAN VAN C', '3453454'),
(4, 'TRAN VAN D', '341636423'),
(5, 'TRAN VAN E', '2132124')

select * from Customer

insert into Items(item_no, description, price)
values
(1, 'Noi dung 1', 1000),
(2, 'Noi dung 2', 2000),
(3, 'Noi dung 3', 3000),
(4, 'Noi dung 4', 2000),
(5, 'Noi dung 5', 1000)

insert into orders(ord_no, cust_no, ord_date)
values
(1, 1, '2021-06-25'),
(2, 1, '2021-02-05'),
(3, 2, '2021-09-15')

insert into orders_detail(ord_no, item_no, qty)
values
(1, 1, 1),
(1, 2, 3),
(1, 3, 1),
(2, 1, 1),
(2, 3, 1),
(3, 2, 1),
(3, 3, 1)

select * from Customer
select * from Items
select * from Orders
select * from orders_detail
-- Chi hien thi 2 column: cust_no, cust_name
select cust_no, cust_name from Customer

-- Chi hien thi 2 column: cust_no -> Ma KH, cust_name -> Ten KH
select cust_no 'Ma KH', cust_name as 'Ten KH' from Customer

-- Kien Thuc Moi.
create table Student (
	rollno nvarchar(20) primary key,
	fullname nvarchar(50),
	email nvarchar(150) unique,
	address nvarchar(200) default 'Nam Dinh',
	brithday date
)

drop table Student

insert into Student (rollno, fullname, email, brithday, address)
values
('R001', 'TRAN VAN A', 'a@gmail.com', '1999-02-06', 'Ha Noi')

insert into Student (rollno, fullname, email, brithday, address)
values
('R002', 'TRAN VAN B', 'b@gmail.com', '1999-08-16', 'Nam Dinh')

insert into Student (rollno, fullname, email, brithday)
values
('R003', 'TRAN VAN C', 'c@gmail.com', '1999-08-16')

select * from Student

-- Bang mon hoc
create table Subject (
	subject_id int primary key identity(1,1),
	subject_name nvarchar(50)
)

insert into Subject(subject_name)
values
('EPC'),
('HTML/CSS/JS'),
('Bootstrap/jQuery')

select * from Subject
drop table Subject

delete from Subject

create table Marks(
	rollno nvarchar(20),
	subject_id int,
	marks float,
	primary key (rollno, subject_id)
)

alter table Marks
add constraint check_mark check (marks >= 0 and marks <= 10)

alter table Marks
add constraint check_mark check (marks between 0 and 10)

insert into Marks (rollno, subject_id, marks)
values
('R001', 1, 6),
('R001', 2, 8),
('R002', 1, 6)

insert into Marks (rollno, subject_id, marks)
values
('R002', 2, 10)

select * from Marks

-- Student -> Thieu column: gender
alter table Student
add gender int

select * from Student

-- Student: Sai column gender -> nvarchar(10)
alter table Student
alter column gender nvarchar(10)

alter table Student
add test nvarchar(10)

alter table Student
drop column test

-- Xoa du lieu dieu kien: rollno = R003
delete from Student where rollno = 'R003'

select * from Student where rollno = 'R003'

update Student set gender = 'Nam'

update Student set gender = 'Nu' where rollno = 'R003'

-- Tim hieu ky hon ve select
-- Hien thi sinh vien co ngay sinh >= 1999-08-08
select * from Student where brithday >= '1999-08-08'

-- Hien thi sinh vien co ngay sinh >= 1999-08-08 & gender = 'Nam'
select * from Student where brithday >= '1999-08-08' and gender = 'Nam'

-- Hien thi sinh vien co ngay sinh >= 1999-08-08 & <= 2020-01-01 & gender = 'Nam'
select * from Student where brithday >= '1999-08-08' and brithday <= '2020-01-01' and gender = 'Nam'
select * from Student where (brithday between '1999-08-08' and '2020-01-01') and gender = 'Nam'