Thiết kế CSDL quản lý bán hàng - create - alter - insert - update - delete trong CSDL - Học lập trình SQL Server

-- Create Tables
create table bt1770_product (
	id int primary key identity(1,1),
	title nvarchar(200),
	thumbnail nvarchar(500),
	content text
)

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

-- Alter
alter table bt1770_product
add price float

alter table bt1770_product
add num int

alter table bt1770_product
add created_at datetime

alter table bt1770_product
add updated_at datetime

alter table bt1770_product
add id_cat int

-- constraint
alter table bt1770_product
add constraint fk_category foreign key (id_cat) references bt1770_category(id)

-- Insert data
insert into bt1770_category(name)
values
('Thoi Trang Nam'),
('Thoi Trang Nu'),
('For Kids')

insert into bt1770_product (id_cat, title, thumbnail, content, price, num, created_at, updated_at)
values
(1, 'T-Shirt Abc', 'thumbnail 1', 'Noi dung 1', 10000, 20, '2021-03-24 08:00:00', '2021-03-24 08:00:00'),
(1, 'T-Shirt BBB', 'thumbnail 2', 'Noi dung 2', 20000, 10, '2021-03-24 08:00:00', '2021-03-24 08:00:00'),
(1, 'T-Shirt CCC', 'thumbnail 3', 'Noi dung 3', 30000, 30, '2021-03-24 08:00:00', '2021-03-24 08:00:00'),
(2, 'T-Shirt 000', 'thumbnail 4', 'Noi dung 4', 40000, 50, '2021-03-24 08:00:00', '2021-03-24 08:00:00'),
(3, 'T-Shirt UUU', 'thumbnail 5', 'Noi dung 5', 50000, 60, '2021-03-24 08:00:00', '2021-03-24 08:00:00')


insert into bt1770_product (id_cat, title, thumbnail, content, price, num, created_at, updated_at)
values
(3, 'T-Shirt KKK', 'thumbnail 6', 'Noi dung 6', 0, 20, '2021-03-24 08:00:00', '2021-03-24 08:00:00')

insert into bt1770_product (id_cat, title, thumbnail, content, num, created_at, updated_at)
values
(3, 'T-Shirt SSS', 'thumbnail 7', 'Noi dung 7', 20, '2021-03-24 08:00:00', '2021-03-24 08:00:00')

-- TEST
select * from bt1770_category
select * from bt1770_product

-- UPDATE
update bt1770_product set price = 5000 where price = 0 or price is null

update bt1770_product set price = price * 0.9 where created_at <= '2020-06-06'

-- DELETE
delete from bt1770_product where created_at <= '2016-12-31'