1.Thiết kế bảng hàng hóa gồm các column sau
- id kiểu số nguyên, khóa chính, tự tăng
- tên mặt hàng
- nhà sản xuất
- xuất xứ (Việt Nam, Japan, ...)
- giá nhập
- giá bán
- ngày sản xuất : kiểu dữ liệu date
->Thực hiện chèn them 10 bản ghi vào bảng
2. Thiết kế bảng bán hàng gồm các column sau
- id đơn hàng kiểu số nguyên, khóa chính, tự tăng
- id_hanghoa khóa ngoai liên kết vs khóa id của bang hàng hóa
- chú thich : dùng ghi lại cho mỗi đơn hàng
- ngày bán : kiểu date
- số lượng
- gia ban
->Thực hiện nhập 10 bản ghi cho bảng này
3. Thực hiện liệt kê tất cả các đơn hàng đã được bán ra -> Dùng view để thiết kế
4. Liệt kê các đơn hàng được bán ra có xuất xứ -> yêu cầu viết procedure có tham số truyền vào là xuất xứ
5. Thống kê tổng giá bán được cho từng mặt hàng. -> viết procedure có tham số truyền vào là mặt hàng và tham số đấu già là total
Theo dõi cập nhật nội dung học trên Youtube & Facebook
TRẦN VĂN ĐIỆP [Teacher]
Ngày viết: 15:03 02/03/2021
-- Tao database BT1801
create database BT1801
-- Kich hoat database
use BT1801
-- Bang hang hoa
create table product (
id int primary key identity(1,1),
title nvarchar(200),
producer_name nvarchar(200),
madein nvarchar(50),
buyer_price float,
sell_price float,
created_at date
)
-- Bang ban hang
create table orders (
id int primary key identity(1,1),
product_id int references product (id),
number int,
price float,
note nvarchar(500)
)
-- Insert into database
insert into product (title, producer_name, madein, buyer_price, sell_price, created_at)
values
('San pham 1', 'ABC', 'Viet Nam', 20000, 50000, '2021-01-05'),
('San pham 2', 'ABC', 'Viet Nam', 10000, 50000, '2021-01-05'),
('San pham 3', 'GokiSoft', 'Viet Nam', 40000, 50000, '2021-01-05'),
('San pham 4', 'ABC', 'JP', 20000, 50000, '2021-01-05'),
('San pham 5', 'GokiSoft', 'Viet Nam', 20000, 50000, '2021-01-05'),
('San pham 6', 'GokiSoft', 'JP', 30000, 50000, '2021-01-05'),
('San pham 7', 'GokiSoft', 'Viet Nam', 20000, 50000, '2021-01-05'),
('San pham 8', 'ABC', 'JP', 20000, 50000, '2021-01-05'),
('San pham 9', 'ABC', 'JP', 20000, 50000, '2021-01-05'),
('San pham 10', 'GokiSoft', 'Lao', 20000, 50000, '2021-01-05')
insert into orders(product_id, number, price)
values
(1, 3, 50000),
(2, 2, 50000),
(2, 7, 50000),
(1, 2, 50000),
(3, 8, 50000),
(3, 3, 50000),
(1, 3, 50000),
(5, 5, 50000),
(5, 3, 50000),
(5, 3, 50000)
-- Create view
create view view_product_by_orders
as
select product.id, product.producer_name, product.madein, product.sell_price, orders.number
from product inner join orders on product.id = orders.product_id
select * from view_product_by_orders
order by id asc
-- create proc
create proc proc_product_by_madein
@madein nvarchar(50)
as
begin
select product.id, product.producer_name, product.madein, product.sell_price, orders.number
from product inner join orders on product.id = orders.product_id
where product.madein = @madein
order by product.id asc
end
exec proc_product_by_madein 'Viet Nam'
-- create proc
create proc proc_statistic_product
@productId int,
@total float output
as
begin
-- TEST
select product.id, product.producer_name, product.madein, sum(orders.number * orders.price) as 'TotalMoney'
from product inner join orders on product.id = orders.product_id
group by product.id, product.producer_name, product.madein
order by TotalMoney desc
-- Chuc nang can lam
select @total = sum(orders.number * orders.price)
from product inner join orders on product.id = orders.product_id
where product.id = @productId
end
declare @total int
exec proc_statistic_product 3, @total = @total output
print N'Doanh thu cua san pham 3: ' + convert(nvarchar, @total)
nguyễn Sử [T2008A]
Ngày viết: 17:01 11/12/2020
create database management_program
use management_program
create table hang_hoa (
ID int identity (1,1) primary key,
ten_hang nvarchar (100),
nha_sx nvarchar(100),
xuat_xu nvarchar(100),
gia_nhap money,
gia_ban money,
ngay_sx date
)
go
insert into hang_hoa(ten_hang,nha_sx,xuat_xu,gia_nhap,gia_ban,ngay_sx)
values
('Áo','ABC','Việt Nam','100','200','2020-12-11'),
('Quần Vải','BBC','Japan','300','400','2020-12-01'),
('Túi Sách','CBC','Việt Nam','500','900','2020-12-02'),
('áo Len','DBC','Japan','700','900','2020-12-03'),
('Quần jean','EBC','China','100','300','2020-12-04'),
('áo M','QQBC','USA','100','200','2020-12-05'),
('áo L','WWBC','Việt Nam','100','200','2020-12-06'),
('áo U','EEEBC','Japan','100','200','2020-12-07'),
('áo O','TTTBC','Việt Nam','100','200','2020-12-08'),
('áo R','UUUBC','Japan','100','200','2020-12-09')
---- 2. Thiết kế bảng bán hàng gồm các column sau ---
create table ban_hang(
ID int identity (1,1) primary key,
ID_hang_hoa int,
chu_thich nvarchar(500),
ngay_ban date,
so_luong int
)
go
alter table ban_hang
add constraint fk_id_hanghoa foreign key (id_hang_hoa) references hang_hoa(id)
insert into ban_hang(chu_thich,ngay_ban,so_luong)
values
('very good','2020-12-11','11'),
('very good','2020-12-11','12'),
('very good','2020-12-11','13'),
('very good','2020-12-11','14'),
('very good','2020-12-11','15'),
('very good','2020-12-11','16'),
('very good','2020-12-11','17'),
('very good','2020-12-11','18'),
('very good','2020-12-11','19'),
('very good','2020-12-11','20')
--- inserting data ---
SELECT *FROM hang_hoa
SELECT *FROM ban_hang
---3. Thực hiện liệt kê tất cả các đơn hàng đã được bán ra -> Dùng view để thiết kế---
create view don_hang
AS
select hang_hoa.ten_hang, hang_hoa.gia_ban, hang_hoa.gia_nhap, hang_hoa.ngay_sx, hang_hoa.nha_sx, hang_hoa.xuat_xu, ban_hang.chu_thich, ban_hang.ngay_ban, ban_hang.so_luong
from hang_hoa,ban_hang
where hang_hoa.id=ban_hang.ID_hang_hoa
select *from don_hang
--- 4 Liệt kê các đơn hàng được bán ra có xuất xứ -> yêu cầu viết procedure có tham số truyền vào là xuất xứ
create proc xuat_xu
@xuatxu nvarchar(100)
as
begin
select hang_hoa.ten_hang, hang_hoa.gia_ban, hang_hoa.gia_nhap, hang_hoa.ngay_sx, hang_hoa.nha_sx, hang_hoa.xuat_xu, ban_hang.chu_thich, ban_hang.ngay_ban, ban_hang.so_luong
from hang_hoa,ban_hang
where hang_hoa.id=ban_hang.ID_hang_hoa and hang_hoa.xuat_xu = @xuatxu
end
exec xuat_xu 'China'
--- 5 ---
create gia_ban
@tenhang int,
@total money output
as
begin
select hang_hoa.ten_hang, hang_hoa.gia_ban, hang_hoa.gia_nhap, hang_hoa.ngay_sx, hang_hoa.nha_sx, hang_hoa.xuat_xu, ban_hang.chu_thich, ban_hang.ngay_ban, ban_hang.so_luong, hang_hoa.gia_ban
*sum(ban_hang.so_luong) as tong_gia
from hang_hoa,ban_hang
where hang_hoa.id=ban_hang.ID_hang_hoa and @tenhang = hang_hoa.id
group by Hang_hoa.ten_hang,Hang_hoa.Nha_sx,Hang_hoa.Xuat_xu,Hang_hoa.Gia_ban
select @total = tong_gia from
(select Hang_hoa.ten_hang,Hang_hoa.Nha_sx,Hang_hoa.Xuat_xu,Hang_hoa.Gia_ban * sum(ban_hang.so_luong) as tong_gia
from ban_hang,Hang_hoa
where Hang_hoa.id = ban_hang.id_hang_hoa and @mathang = Hang_hoa.id
group by Hang_hoa.ten_hang,Hang_hoa.Nha_sx,Hang_hoa.Xuat_xu,Hang_hoa.Gia_ban)as abc
end
declare @total money
exec gia_ban 1 ,@total = @total output
print @total
end
Triệu Văn Lăng [T2008A]
Ngày viết: 16:54 11/12/2020
create database QL_Banhang
use QL_Banhang
create table product (
id int primary key identity(1, 1),
name nvarchar(20),
producer nvarchar(20),
origin nvarchar(20),
import_price money,
price money,
date_of_manufacture date
)
create table sell (
id_order int primary key identity(1, 1),
id int foreign key references product(id),
note ntext,
date_sell date,
amount int
)
insert into product(name, producer, origin, import_price, price, date_of_manufacture)
values
('Tivi', 'Samsung', 'Korea', '2000', '3000', '2020-01-01'),
('Tu lanh', 'Panasonic', 'Japan', '3000', '4000', '2020-01-10'),
('Quat dien', 'Panasonic', 'Japan', '2000', '2500', '2020-02-01'),
('Lo Vi song', 'Panasonic', 'Japan', '1000', '2000', '2020-02-01'),
('Be ca mini', 'nhom kinh', 'Viet Nam', '1000', '2000', '2020-01-01'),
('May suoi', 'Panasonic', 'Japan', '1000', '2000', '2020-01-02'),
('Loa dai', 'loadai.com', 'Viet Nam', '1000', '2000', '2020-02-01'),
('May bom nuoc', 'NSX1', 'china', '800', '1500', '2020-02-01'),
('Laptop', 'NSX2', 'Viet Nam', '2000', '3000', '2020-01-01'),
('bep tu', 'Panasonic', 'Japan', '2000', '3000', '2020-01-01')
insert into sell(id, note, date_sell, amount)
values
(2, 'note 1', '2020-05-30', 2),
(6, 'note 2', '2020-06-30', 3),
(4, 'note 3', '2020-07-30', 1),
(8, 'note 4', '2020-08-30', 1),
(5, 'note 5', '2020-09-30', 4),
(1, 'note 6', '2020-10-30', 3),
(3, 'note 7', '2020-11-30', 1),
(1, 'note 8', '2020-12-30', 5),
(3, 'note 9', '2020-12-30', 3),
(9, 'note 10', '2020-12-30', 1)
select * from product
select * from sell
create view vw_sell
as
select sell.id_order, product.name, sell.date_sell, sell.amount
from sell, product
where product.id=sell.id
select * from vw_sell
create proc vw_origin
@origin nvarchar(20)
as
begin
select sell.id_order, product.name, sell.date_sell, sell.amount
from sell, product
where product.id=sell.id and [email protected]
end
exec vw_origin 'Japan'
create proc vw_tatal_price
@productId int,
@tatal_price float output
as
begin
select @tatal_price=sum(product.price*sell.amount)
from product, sell
where product.id=sell.id and [email protected]
end
declare @tatal_price float
exec vw_tatal_price 3, @[email protected]_price output
print @tatal_price
print N'Tong tien ban duoc: '+ convert(nvarchar(50), @tatal_price)
Nguyễn Tiến Đạt [T2008A]
Ngày viết: 16:43 11/12/2020
-- Tao database
CREATE DATABASE BT1801
-- Active database
USE BT1801
-- Tao tables
CREATE TABLE product (
id INT PRIMARY KEY IDENTITY(1,1),
name NVARCHAR(150) NOT NULL,
manufacturer_name NVARCHAR(50),
madein NVARCHAR(50),
buy FLOAT,
sell FLOAT,
created_at date
)
CREATE TABLE orders (
id INT PRIMARY KEY IDENTITY(1,1),
id_product INT REFERENCES dbo.product(id),
note NVARCHAR(200),
num INT,
created_at date
)
-- Insert du lieu
INSERT INTO dbo.product ( name ,manufacturer_name ,madein ,buy ,sell ,created_at)
VALUES
('san pham 1', 'ABC', 'Viet Nam', 10000, 20000, GETDATE()),
('san pham 2', '324', 'Viet Nam', 20000, 60000, GETDATE()),
('san pham 3', 'sfds', 'US', 100000, 200000, GETDATE()),
('san pham 4', '23424', 'Viet Nam', 10000, 20000, GETDATE()),
('san pham 5', '234', 'Viet Nam', 10000, 20000, GETDATE()),
('san pham 6', 'AB54C', 'JP',510000, 660000, GETDATE()),
('san pham 7', '65', 'JP', 10000, 20000, GETDATE()),
('san pham 8', '456', 'Viet Nam', 10000, 20000, GETDATE())
INSERT INTO dbo.orders ( id_product, note, num, created_at )
VALUES
(2, 'Ship ve Y Yen Nam Dinh', 2, GETDATE()),
(3, 'xin chao', 2, GETDATE()),
(4, 'hang fake', 2, GETDATE()),
(3, 'okok', 2, GETDATE()),
(2, 'madein china - hang JP', 2, GETDATE()),
(5, 'Ship ve Y Yen Nam Dinh', 2, GETDATE())
-- View: name, manufacturer_name, madein, sell, num, ngay ban
CREATE VIEW view_show_product_selling
AS
SELECT dbo.product.name, dbo.product.manufacturer_name, dbo.product.madein, dbo.product.sell, dbo.orders.num, dbo.orders.created_at AS 'OrderDate'
FROM dbo.product, dbo.orders
WHERE dbo.product.id = dbo.orders.id_product
SELECT * FROM view_show_product_selling
-- Tao produce
CREATE PROC proc_show_product_selling_by_madein
@madein nvarchar(50)
AS
BEGIN
SELECT dbo.product.name, dbo.product.manufacturer_name, dbo.product.madein, dbo.product.sell, dbo.orders.created_at AS 'OrderDate'
FROM dbo.product, dbo.orders
WHERE dbo.product.id = dbo.orders.id_product
AND dbo.product.madein = @madein
END
EXEC proc_show_product_selling_by_madein 'Viet Nam'
EXEC proc_show_product_selling_by_madein 'US'
-- PROC tinh tong so tien ban dc vs 1 mat hang
CREATE PROC proc_total_price_by_product_id
@productId int,
@totalPrice float OUTPUT
AS
BEGIN
SELECT @totalPrice = SUM(dbo.product.sell * orders.num)
FROM dbo.product, dbo.orders
WHERE dbo.product.id = dbo.orders.id_product
AND dbo.product.id = @productId
END
DECLARE @totalPrice FLOAT
EXEC proc_total_price_by_product_id 5, @totalPrice = @totalPrice OUTPUT
PRINT @totalPrice
PRINT N'Tong tien ban duoc: ' + CONVERT(nvarchar(50), @totalPrice)
Nguyên Phấn Đông [T2008A]
Ngày viết: 16:42 11/12/2020
create table bang_hang_hoa2(
id int primary key identity(1,1),
name nvarchar(50),
NSX nvarchar(100),
Xuat_xu nvarchar(50),
indate int,
outdate int,
NgSX date
)
go
insert into bang_hang_hoa2(name,NSX,Xuat_xu,indate,outdate,NgSX)
values
('quan','Dong1','VN','100','300','2019-1-1'),
('ao','Dong2','USA','100','250','2019-1-12'),
('giay','Dong3','USB','100','2000','2019-1-1'),
('dep','Dong4','JAP','100','400','2019-1-1'),
('mu','Dong5','US','100','180','2019-1-1'),
('kinh','Dong6','VN','100','200','2019-1-1'),
('tai nghe','Dong7','USD','100','400','2019-1-1'),
('dien thoai','Dong8','USA','100','3000','2019-1-1'),
('dong ho','Dong9','KOR','120','20000','2019-1-1'),
('tat','Dong10','JAP','211','300','2019-1-1')
create table bang_ban_hang2(
id int primary key identity(1,1),
id_hanghoa int references bang_hang_hoa2(id),
note text,
outdate date,
num int
)
go
insert into bang_ban_hang2(id_hanghoa,note,outdate,num)
values
('1','a','2020-12-10','100'),
('3','','2020-12-10','20'),
('5','','2020-12-10','19'),
('1','g','2020-12-10','60'),
('2','','2020-12-10','27'),
('5','n','2020-12-10','32'),
('3','','2020-12-10','200'),
('7','','2020-12-10','100'),
('9','','2020-12-10','11'),
('10','','2020-12-10','102')
create view view_show_selling1
as
select dbo.bang_hang_hoa2.name,dbo.bang_hang_hoa2.NSX,dbo.bang_ban_hang2.outdate,dbo.bang_hang_hoa2.Xuat_xu,dbo.bang_ban_hang2.num
from dbo.bang_ban_hang2,dbo.bang_hang_hoa2
where dbo.bang_ban_hang2.id_hanghoa=dbo.bang_hang_hoa2.id
select * from view_show_selling1
create view view_liet_ke as
select bang_hang_hoa2.name,bang_ban_hang2.num,bang_hang_hoa2.Xuat_xu,bang_ban_hang2.outdate
from bang_ban_hang2,bang_hang_hoa2
where bang_ban_hang2.id_hanghoa=bang_hang_hoa2.id
select * from view_liet_ke
create proc pr_don_hang
@Xuat_xu nvarchar(50)
AS
begin
select bang_hang_hoa2.name,bang_ban_hang2.note,bang_hang_hoa2.Xuat_xu,bang_ban_hang2.num,bang_ban_hang2.outdate
from bang_hang_hoa2 left join bang_ban_hang2 on bang_ban_hang2.id_hanghoa = bang_hang_hoa2.id
where bang_hang_hoa2.Xuat_xu = @Xuat_xu and bang_ban_hang2.num != 0
end
exec pr_don_hang Jap
select * from bang_ban_hang2
select id_hanghoa,sum(num) as tong
from bang_ban_hang2
group by id_hanghoa
create proc proc_total_price
@id int,
@totalprice int output
as
begin
select @totalprice= sum(bang_ban_hang2.num* bang_hang_hoa2.outdate)
from dbo.bang_ban_hang2,dbo.bang_hang_hoa2
where dbo.bang_ban_hang2.id_hanghoa=dbo.bang_hang_hoa2.id and dbo.bang_hang_hoa2.id [email protected]
end