By GokiSoft.com|
19:42 15/12/2023|
SQL Server/MySQL
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
Yêu cầu thiết kế hệ thống quản trị CSDL cho một của hàng thời trang tại hà nôi
- Thiết kế bảng product gồm các trường
id : trường khoá chính tự tăng
title : tên sản phẩm
thumbnail : đường dẫn hình ảnh
content : kiểu longtext
- Thiết kế bảng danh mục sản phẩm gồm các trường
id : trường khoá tự tăng
name : tên danh mục
- Bổ sung các cột vào bảng sản phẩm
price : giá bán
num: số lượng hàng tồn kho
created_at : ngày đăng sản phẩm
updated_at : ngày sửa thông tin sản phẩm
id_cat : id danh mục => liên kết với column id trong bảng danh mục sản phẩm
Yêu cầu:
Tạo bảng theo thứ tự yêu cầu trên
Thêm 5 sản phẩm vào bảng sản phẩm & 3 sản phầm vào bảng danh mục sản phẩm
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
sửa giá bán => giảm 10% giá bán với các sản phẩm đăng trước ngày 2020/06/06 (Ví dụ sản phẩm A có giá 50$ => sản 10% sẽ có giá là : 45$)
Xoá sản phẩm đăng trước ngày 2016/12/31
Tags:
Phản hồi từ học viên
5
(Dựa trên đánh giá ngày hôm nay)
![Hoàng Thái Sơn [C2010L]](https://www.gravatar.com/avatar/f7030644ed6e4a7a1fd3fd27abf6ff02.jpg?s=80&d=mm&r=g){kind=avatar}
Hoàng Thái Sơn
2021-04-17 12:46:38
#testtin03-04-2021.sql
Use Database3_04_2021ShopThoiTrang;
Create table Product (
id INT PRIMARY KEY identity(1,1),
title Nvarchar(25),
thumbnail text,
content text
)
drop table product
Create table Product_Category (
id INT PRIMARY KEY identity(1,1),
name_cate Nvarchar(50)
)
drop table Product_Category
select * from Product
select * from Product_Category
alter table product
add price INT
alter table product
add num INT
alter table product
add created_at DATE
alter table product
add updated_at DATE
alter table product
add id_cate INT
alter table product
ADD CONSTRAINT FK_id_cat
FOREIGN KEY (id_cate) REFERENCES Product_Category(id);
insert into Product (title, thumbnail, content, price, num, created_at, updated_at, id_cate)
values
('AoBaba', 'https://www.img2go.com/vi/resize-image', 'ishdfidichiu',6000, 99, '1010-12-12', '1010-12-13', 1)
insert into Product (title, thumbnail, content, price, num, created_at, updated_at, id_cate)
values
('AoBab', 'https://www.img2go.com/vi/resize-image', 'ishdfidichiu',500, 99, '1010-12-12', '1010-12-13', 2)
insert into Product (title, thumbnail, content, price, num, created_at, updated_at, id_cate)
values
('AoBa', 'https://www.img2go.com/vi/resize-image', 'ishdfidichiu',0, 99, '1010-12-12', '1010-12-13', 3)
insert into Product (title, thumbnail, content, num, created_at, updated_at, id_cate)
values
('AoB', 'https://www.img2go.com/vi/resize-image', 'ishdfidichiu', 99, '1010-12-12', '1010-12-13', 2)
insert into Product (title, thumbnail, content, price, num, created_at, updated_at, id_cate)
values
('Ao', 'https://www.img2go.com/vi/resize-image', 'ishdfidichiu',6020,99, '1010-12-12', '1010-12-13', 1)
insert into Product (title, thumbnail, content, price, num, created_at, updated_at, id_cate)
values
('A', 'https://www.img2go.com/vi/resize-image', 'ishdfidichiu',602,99, '2017-12-12', '1010-12-13', 1)
DELETE FROM Product WHERE title='Ao';
insert into Product_Category (name_cate)
values
('TenAo'),
('Teno'),
('Teo')
UPDATE Product
SET Price = 5000
WHERE Price = 0;
UPDATE Product
SET Price = 5000
WHERE title = 'AoB';
UPDATE Product
SET Price = price - price*0.1
Where created_at < '2020-06-06';
Delete from Product where created_at < '2016-12-31';
![Võ Như Việt [C2010L]](https://www.gravatar.com/avatar/fb93c99beb23339eb21f4d4ffe8981af.jpg?s=80&d=mm&r=g){kind=avatar}
Võ Như Việt
2021-04-05 16:15:29
create Database Cua_Hang_Thoi_Trang
use Cua_Hang_Thoi_Trang
-- bang Product--
create table product(
id int primary key identity(1,1),
title nvarchar(100),
thumbnail nvarchar(1000),
content text
)
-- bang San Pham --
create table Item(
id int primary key identity(1,1),
name_Item nvarchar(50)
)
--Bo sung danh muc vao Product--
alter table product
add Price nvarchar(50),
num int,
created_at smalldatetime,
updated_at smalldatetime,
id_cat int,
constraint fk_product_id foreign key (id_cat) references Item (id);
--sua gia tri trong bang Product--
alter table product
alter column created_at date;
alter table product
alter column updated_at date;
------------------------------------
select * from product
--them 5 san pham vao bang Product--
insert into product(title,thumbnail,content,price,num,created_at,updated_at)
values
('ao khoac','link','mua dong',10000,25,'2020-04-03','2020-04-05'),
('quan bo','link','rach mong',20000,15,'2021-04-01','2021-04-04'),
('ao long vu','link','sieu mong',30000,10,'2021-04-02','2021-04-05'),
('quan au','link','dang dep',20000,20,'2021-04-1','2021-04-04'),
('dong phuc','link','cong so',40000,5,'2021-04-03','2021-04-05')
-- them 3 san pham vao bang Item--
insert into Item(name_Item)
values
('do nam'),
('do nu'),
('full bo')
select * from Item
-- dieu kien 1--
update product set price = 5000 where price = 0 and price = '' and price = 'null'
-- dieu kien 2--
update product set price = * 10% where created_at > '2020/06/06';
-- xoa san pham dang --
delete from product where created_at >'2016-12-31'![TRẦN VĂN ĐIỆP [Teacher]](https://www.gravatar.com/avatar/fc6ba9324e017d540af3613b3a77dd21.jpg?s=80&d=mm&r=g){kind=avatar}
TRẦN VĂN ĐIỆP
2021-03-24 01:41:03
-- 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'![Trinh Huy Hung [community,C2009I]](https://www.gravatar.com/avatar/c5cd1f25c7a1fbe45b7ee35a66ceeb6c.jpg?s=80&d=mm&r=g){kind=avatar}
Trinh Huy Hung
2021-01-26 10:05:26
create database Store1
use Store1
create table Product(
id int identity(1, 1) primary key,
title nvarchar(50),
thumbnail nvarchar(200),
content text
)
create table ProductList(
id int identity(1, 1) primary key,
name nvarchar(50)
)
alter table Product
add price float
alter table Product
add num int
alter table Product
add created_at date
alter table Product
add updated_at date
alter table Product
add id_cat int references ProductList(id)
insert into ProductList(name)
values
('Camera'),
('Screen'),
('PC')
insert into Product(title, thumbnail, content, price, num, created_at, updated_at, id_cat)
values
('Camera 01', 'picture1234.com', 'Camera Product 01', 0, 56, '2020-03-20', '2021-03-20', 1),
('Camera 02', 'picture1233.com', 'Camera Product 02', 750000, 40, '2020-05-22', '2021-03-22', 1),
('Screen 01', 'picture1134.com', 'Screen Product 01', 450000, 56, '2020-04-23', '2021-03-22', 2),
('Screen 02', 'picture1133.com', 'Screen Product 02', 900000, 56, '2016-02-24', '2021-03-21', 2),
('PC 01', 'picture1334.com', 'PC Product 01', 15000000, 30, '2020-10-20', '2021-02-20', 3)
select * from Product
update Product
set price=5000
where price=0 or price=Null or price=''
update Product
set price=price*90/100
where created_at < '2020-06-06'
delete from Product Where created_at < '2016-12-31'
select * from Product![Vũ Trung Kiên [C2009I]](https://www.gravatar.com/avatar/abf994de48c0204239812f8d9a7dcf8c.jpg?s=80&d=mm&r=g){kind=avatar}
Vũ Trung Kiên
2021-01-25 15:05:26
#CuaHangThoiTrang.sql
create database CuaHangThoiTrang
use CuaHangThoiTrang
create table Product
(
id int primary key identity(1,1),
title nvarchar(50),
thumbnail nvarchar(50),
content nvarchar(max)
)
create table ProductList
(
id int primary key identity(1,1),
name nvarchar(50)
)
alter table Product
add price int,
num int,
created_at date,
updated_at date,
id_cat int
alter table Product
add constraint fk_id foreign key (id_cat) references ProductList (id)
insert into ProductList(name)
values
('nuoc cham'),
('trai cay'),
('dien tu')
insert into Product(title, thumbnail, content, price, num, created_at, updated_at, id_cat)
values
('chuoi', 'abc', 'sagbsjahgbksaegjsh g;sig', '15000', '15', '2020/04/15', '2020/04/16', '2'),
('cam', 'abc', 'sagbahgbkdfssaegjsh g;sig', '10000', '125', '2020/05/15', '2020/06/16', '2'),
('quyt', 'abc', 'sagbsjmej5aegjsh g;sig', '25000', '85', '2019/04/15', '2020/02/16', '2'),
('nuoc mam', 'abc', 'sagbsehshrehsrehh g;sig', '12000', '21', '2020/04/20', '2021/01/6', '1'),
('dien thoai', 'abc', 'sagbsdhsdhrdhdshrhs', '150000', '150', '2020/10/15', '2021/04/16', '3')
update Product set price = price - (0.1 * price)
where created_at <= '2020/06/06'
select * from Product
select * from ProductList![Nguyễn Hữu Hiếu [T2008A]](https://www.gravatar.com/avatar/ca2884508b617fee77f000c7d99c219d.jpg?s=80&d=mm&r=g){kind=avatar}
Nguyễn Hữu Hiếu
2020-12-15 08:56:23
create database 1770
use 1770
create table product (
id int primary key identity(1,1),
tilte nvarchar(250),
thumbnail nvarchar(500),
content text,
price money,
num int,
created_at date,
update_at date,
id_category int references category(id)
)
create table category(
id int primary key identity(1,1),
name nvarchar(250)
)
insert into category(name)
values
('Ca phe'),
('Banh'),
('Bia'),
('Keo'),
('Nuoc')
insert into product (tilte,thumbnail,content,price,num,created_at,update_at,id_category)
values
('Ca phe G7','g7.png','Ca phe pha san',32000,20,'2020-09-15','2020-10-20',1),
('Danisa','da.png','Banh ngon lam',1122000,20,'2020-09-20','2020-10-20',2),
('Danisa','da.png','Banh ngon lam',1122000,20,'2020-09-15','2020-10-20',2),
('Danisa','da.png','Banh ngon lam',1122000,20,'2020-09-15','2020-10-20',2),
('Danisa','da.png','Banh ngon lam',1122000,20,'2020-09-15','2020-10-20',2)
select * from product
DELETE FROM product WHERE created_at <= '2020-09-15'
![Nguyễn Anh Vũ [T2008A]](https://www.gravatar.com/avatar/8863d24ed74b396082becbc4db8331fd.jpg?s=80&d=mm&r=g){kind=avatar}
Nguyễn Anh Vũ
2020-11-30 06:42:03
create database BT1770
go
use BT1770
go
create table product (
id int primary key identity(1, 1),
title nvarchar(100),
thumbnail nvarchar(500),
content text
)
go
create table Sanpham (
id int primary key identity(1, 1),
name nvarchar(100) not null
)
go
alter table Sanpham
add price float,
num int,
created_at date,
updated_at date
alter table Sanpham
add id_cat int
select * from product
select * from Sanpham
insert into product(title, thumbnail, content)
values
('Ao da bong 1', 'https://www.sporter.vn/wp-content/uploads/2017/06/Ao-mu-san-nha-1-2-300x300.jpg', 'San pham 1'),
('Ao da bong 2', 'https://www.sporter.vn/wp-content/uploads/2017/06/Ao-mu-san-khach-1-3.jpg', 'San pham 2'),
('Ao da bong 3', 'https://dothethao.net.vn/wp-content/uploads/2020/04/ao-dau-ngoai-hang-anh-2020-manchester-united.jpg', 'San pham 3'),
('Ao da bong 4', 'https://cf.shopee.vn/file/6b93b5930502a32ea5769943909fb175', 'San pham 4'),
('Ao da bong 5', 'https://thumblr.uniid.it/product/186829/928d42dae20c.jpg', 'San pham 5')
insert into Sanpham(name, price, num, created_at, updated_at, id_cat)
values
('Ao 1', 120.000, 12, '2020-11-12', '2020-11-23', 1),
('Ao 2', 120.000, 12, '2020-11-12', '2020-11-23', 2),
('Ao 3', 120.000, 12, '2020-11-12', '2020-11-23', 3),
('Ao 4', 120.000, 12, '2020-11-12', '2020-11-23', 4),
('Ao 5', 120.000, 12, '2020-11-12', '2020-11-23', 5)
update Sanpham
set price = 5000 where price in (0,null,'')![Trần Văn Lâm [T2008A]](https://www.gravatar.com/avatar/cfc15c8cb7781ad669b013e01f9f1a6b.jpg?s=80&d=mm&r=g){kind=avatar}
Trần Văn Lâm
2020-11-28 14:43:19
create database quan_li_ban_hang
use quan_li_ban_hang
create table product(
id int primary key identity(1,1),
title nvarchar(50),
thumbnail nvarchar(500),
content text,
)
create table product_directory(
id int identity(1,1),
name nvarchar(50)
)
select * from product
select * from product_directory
alter table product
add price int,
num int,
created_at date,
updated_at date,
id_cat int
insert into product(title,thumbnail,content,price,num,created_at,updated_at,id_cat)
values
('Dao','urlaaa','abcxyz','3000','3','2020-5-5','2020-6-5',1),
('Dao','urlaaa','abcxyz','3000','3','2020-5-5','2020-6-5',2),
('Dao','urlaaa','abcxyz','3000','3','2020-5-5','2020-6-5',3),
('Dao','urlaaa','abcxyz','3000','3','2020-5-5','2020-6-5',4),
('Dao','urlaaa','abcxyz','3000','3','2020-5-5','2020-6-5',5)
insert into product_directory(name)
values
('abc'),
('abc'),
('abc'),
('abc'),
('abc')
update product
set price = 5000
where price = 3000
select * from product
update product
set price = price - price*0.1
where created_at <= '2020-06-05'![Trần Thị Khánh Huyền [T2008A]](https://www.gravatar.com/avatar/554e115833778e4294a01aebe228f3d6.jpg?s=80&d=mm&r=g){kind=avatar}
Trần Thị Khánh Huyền
2020-11-27 08:43:23
#Lesson3.sql
CREATE DATABASE BT1770
CREATE TABLE product(
id varchar(4) PRIMARY KEY,
title varchar(50),
thumbnail varchar(500),
content longtext
)
CREATE TABLE product_list(
id varchar(4) PRIMARY KEY,
name varchar(200)
)
ALTER TABLE product
ADD price varchar(50);
ALTER TABLE product
ADD num varchar(50);
ALTER TABLE product
ADD created_at date;
ALTER TABLE product
ADD updated_at date;
ALTER TABLE product
ADD id_cat varchar(10);
INSERT INTO product (id, title, thumbnail, content, price, num, created_at, updated_at, id_cat)
VALUES('1','SP1','123','abcd', '500', '50', '2020/08/09', '2020/10/10', 'A'),
('2','SP2','124','abce', '500', '0', '2020/02/02', '2020/10/10', 'A'),
('3','SP3','125','abcf', '500', '50', '2020/08/10', '2020/10/10', 'B'),
('4','SP4','126','abcg', '500', '50', '2020/02/09', '2020/10/10', 'B'),
('5','SP5','127','abch', '500', '50', '2020/09/09', '2020/10/10', 'B')
INSERT INTO product_list (id, name)
VALUES ('A', 'SP1'),
('B', 'SP2'),
('C' 'SP3')
UPDATE product
set price=5000,
where price='0' or price='' or price='null'
UPDATE product
set price=price*0.9
where created_at<2020/06/06
DELETE product where created_at <2016/12/31
![Triệu Văn Lăng [T2008A]](https://www.gravatar.com/avatar/1348e3562c6492c26f796cb1f45982a1.jpg?s=80&d=mm&r=g){kind=avatar}
Triệu Văn Lăng
2020-11-27 08:03:55
create database bt1770
use bt1770
create table product (
id int primary key identity(1,1),
title nvarchar(100),
thumbnail nvarchar(500),
content text
)
go
create table danhmuc (
id int primary key identity(1,1),
name nvarchar(100)
)
go
alter table product
add price float,
num int,
created_at datetime,
update_at datetime;
alter table product
add id_cat int
alter table product
add constraint fk_id_cat foreign key (id_cat) references danhmuc(id)
select * from product
select * from danhmuc
insert into danhmuc(name)
values
('Iphone'),
('Oppo'),
('Samsung')
insert into product(title, thumbnail, content, price, num, created_at, update_at, id_cat)
values
('Iphone 11', 'url_ip1', 'san pham 1', '1000', '10', '2020-11-26 8:00:00', '2020-12-30 23:59:00', '1'),
('Iphone 11 promax', 'url_ip2', 'san pham 1', '2000', '10', '2020-11-26 8:00:00', '2020-12-30 23:59:00', '1'),
('Iphone 11 pro', 'url_ip3', 'san pham 1', '3000', '10', '2020-11-26 8:00:00', '2020-12-30 23:59:00', '1'),
('Iphone 12', 'url_ip4', 'san pham 1', '4000', '10', '2020-11-26 8:00:00', '2020-12-30 23:59:00', '1'),
('Iphone 12 promax', 'url_ip5', 'san pham 1', '5000', '10', '2020-11-26 8:00:00', '2020-12-30 23:59:00', '1')
insert into product(title, thumbnail, content, price, num, created_at, update_at, id_cat)
values
('Oppo reno 3', 'url_op1', 'san pham 2', '1100', '10', '2020-11-26 8:00:00', '2020-12-30 23:59:00', '2'),
('Oppo reno 4', 'url_op2','san pham 2', '2200', '10', '2020-11-26 8:00:00', '2020-12-30 23:59:00', '2'),
('Oppo reno 3 pro', 'url_op3', 'san pham 2', '3300', '10', '2020-11-26 8:00:00', '2020-12-30 23:59:00', '2'),
('Oppo reno 4 pro', 'url_op4', 'san pham 2', '4400', '10', '2020-11-26 8:00:00', '2020-12-30 23:59:00', '2'),
('Oppo reno 3 promax', 'url_op5', 'san pham 2', '5500', '10', '2020-11-26 8:00:00', '2020-12-30 23:59:00', '2')
insert into product(title, thumbnail, content, price, num, created_at, update_at, id_cat)
values
('Samsung 1', 'url_ss1', 'san pham 3', '1500', '10', '2020-11-26 8:00:00', '2020-12-30 23:59:00', '3'),
('Samsung 2', 'url_ss2', 'san pham 3', '2500', '10', '2020-11-26 8:00:00', '2020-12-30 23:59:00', '3'),
('Samsung 3', 'url_ss3', 'san pham 3', '3500', '10', '2020-11-26 8:00:00', '2020-12-30 23:59:00', '3'),
('Samsung 4', 'url_ss4', 'san pham 3', '4500', '10', '2020-11-26 8:00:00', '2020-12-30 23:59:00', '3'),
('Samsung 5', 'url_ss5', 'san pham 3', '5500', '10', '2020-11-26 8:00:00', '2020-12-30 23:59:00', '3')
