-- Tao database
create database BT1770
go

-- Kich hoat database
use BT1770
go

-- Tao products
create table product (
	id int primary key identity(1,1),
	title nvarchar(250),
	thumbnail nvarchar(500),
	content ntext
)
go

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

-- Bo sung columns vao table product
alter table product
add price float
go

alter table product
add num int
go

alter table product
add created_at datetime
go

alter table product
add updated_at datetime
go

alter table product
add id_cat int references category (id)
go

-- Them du lieu
insert into category (name)
values
('Bep Tu'),
('Bep Ga'),
('Hut Mui')
go

insert into product(title, thumbnail, price, num, content, created_at, updated_at)
values
('San pham 1', '', 1000, 20, 'Xin chao', '2022-01-09 13:00:00', '2022-01-09 13:00:00'),
('San pham 2', '', 2000, 50, 'OKOK', '2022-01-09 13:00:00', '2022-01-09 13:00:00'),
('San pham 3', '', 2500, 50, 'OKOK sdfsdf', '2022-01-09 13:00:00', '2022-01-09 13:00:00'),
('San pham 4', '', 3000, 50, 'AAA OKOK', '2022-01-09 13:00:00', '2022-01-09 13:00:00'),
('San pham 5', '', 5000, 50, 'OKOK skdjfhdk', '2022-01-09 13:00:00', '2022-01-09 13:00:00')
go

select * from product

update product set id_cat = 1

update product set id_cat = 2 where id > 3

insert into product(title, thumbnail, num, content, created_at, updated_at)
values
('San pham 6', '', 20, 'Xin chao', '2022-01-09 13:00:00', '2022-01-09 13:00:00'),
('San pham 7', '', 20, 'Xin chao', '2022-01-09 13:00:00', '2022-01-09 13:00:00')
go

insert into product(title, thumbnail, price, num, content, created_at, updated_at)
values
('San pham 6', '', '', 20, 'Xin chao', '2022-01-09 13:00:00', '2022-01-09 13:00:00'),
('San pham 7', '', '', 20, 'Xin chao', '2022-01-09 13:00:00', '2022-01-09 13:00:00')
go

-- sửa giá bán price = 5000 với điều kiện giá bán đang = 0 hoặc rỗng hoặc null
update product set price = 5000
where price = 0 or price is null -- is null | is not null

update product set price = price * 0.9
where updated_at <= '2020-06-06'

insert into product(title, thumbnail, price, num, content, created_at, updated_at)
values
('San pham 10', '', 1000, 20, 'Xin chao', '2020-01-09 13:00:00', '2020-01-09 13:00:00')
go

update product set price = price * 0.9
where updated_at <= '2020-06-06'

-- Xoa san pham
delete from product
where created_at < '2016-12-31'
go