Yêu cầu.

2020-12-11 08:13:29

create table hang hoa (
 id int primary(1,1),
 name nvarchar(50),
 nha_sx nvarchar(100),
 madein nvarchar (100),
 import_price float ,
 price float ,
 date 
)
insert into hang hoa (name,nha_sx,madein,impost_price,price,date),
values
('ao','abc','viet nam','250000vnd','300000vnd','2020-12-01'),
('quan','abc','trung quoc','150000vnd','200000vnd','2020-12-01'),
('ban chai','abc','thai lan','15000vnd','20000vnd','2020-12-01'),
('laptop','abc','viet nam','15000000vnd','20000000vnd','2020-12-01'),
('vay','abc','nhat ban','150000vnd','220000vnd','2020-12-01'),
('dien thoai','abc','viet nam','15000000vnd','20000000vnd','2020-12-01'),
('chuot','abc','mi','150000vnd','200000vnd','2020-12-01'),
('meo','abc','viet nam','150000vnd','200000vnd','2020-12-01'),
('chan','abc','han quoc','200000vnd','300000vnd','2020-12-01'),
('goi','abc','viet nam','150000vnd','200000vnd','2020-12-01'),
('ga nha','abc','viet nam','500000vnd','600000vnd','2020-12-01')

select *from product

create table hang hoa (
  id int primary(1,1),
  id_hanghoa
  ALTER TABLE hanghoa ADD CONSTRAINT Ma PRIMARY KEY (id hanghoa),
  REFERENCES id hanghoa,
  not text,
  date
  so_luong int
)
insert into hanghoa (id,id hanghoa,chu_thich,ngay_ban,so_luong),
values
('1','23','hai long','2019-08-02','23'),
('2','24','hailong','2019-03-13','2'),
('3','23','hai long','2019-07-12','8'),
('4','23','hai long','2019-06-22','7'),
('5','23','hai long','2019-01-08','20'),
('6','23','hai long','2019-04-02','1'),
('7','23','hai long','2019-02-06','26'),
('8','23','hai long','2019-06-02','23'),
('9','23','hai long','2019-08-02','27'),
('10','23','hai long','2019-09-02','03')
select *from product
create view view_orders
as
select orders. *, product.price_sell
from product, orders
where product.id = orders.id_hanghoa and num > 0

select * from view_orders

create proc proc_orders
	@made_in nvarchar(50)
as
begin
	select product.id, product.name, product.made_in, product.price_sell, orders.ngay_ban, orders.num
	from product, orders
	where product.id = orders.id_hanghoa and product.made_in = @made_in
end

exec proc_orders 'Korea'

create proc proc_total
	@product_id int, @total money output
as
begin
	select product.id, product.name, orders.num, product.price_sell, sum(orders.num) total
	from product, orders
	where product.id = orders.id_hanghoa and product.id = @product_id
	group by product.id, product.name, orders.num, product.price_sell

	select @total = SUM(total) from (
	select product.id, product.name, orders.num, product.price_sell, sum(orders.num) total
	from product, orders
	where product.id = orders.id_hanghoa and product.id = @product_id
	group by product.id, product.name, orders.num, product.price_sell
	) t
end

drop proc proc_total

declare @total money
exec proc_total 1, @total = @total output
print @total	

2020-12-11 08:12:25

create database quanlybanhang11_12_2020
use quanlybanhang11_12_2020

create table Product(
ID int identity(1,1),
Name nvarchar(200),
Manufacture  nvarchar(200),
Country nvarchar(200),
Price_In money,
Price_Out money,
Manufacture_Date date,
constraint PK_Product primary key (ID)
)

create table Sale(
ID int identity(1,1),
ProductID int, 
Note nvarchar(200),
SaleDay date,
Quantity int,
constraint PK_Sale primary key (ID),
constraint FK_Sale_ProductID foreign key (ProductID) references Product(ID)
)

--Them ban ghi
select * from Product
insert into  Product(Name,Manufacture,Country,Price_In,Price_Out,Manufacture_Date)
values
('Giay cong so V1','TrungDucShoes','VietNam','10','15','10-10-2020'),
('Giay leo nui Nike','Nike','Germany','20','25','10-10-2020'),
('Giay chay bo TD','ThuongDinhShoes','VietNam','5','10','10-10-2020'),
('Giay leo nui TDN','TrungDucShoes','VietNam','12','20','10-10-2020'),
('Giay cong so Ma1','Madoni','France','30','35','10-10-2020'),
('Giay the thao A1','Adidas','America','25','35','10-10-2020'),
('Giay leo nui T1','TienDatShoes','VietNam','08','12','10-10-2020'),
('Giay cong so VD','VanDiepShoes','VietNam','100','125','10-10-2020'),
('Giay the thao Lining ','Lining','China','20','30','10-10-2020')

select * from Sale
insert into Sale(ProductID,Note,SaleDay,Quantity)
values
(1,'Hang moi 100%','10-10-2020','3'),
(2,'Hang moi 100%','10-10-2020','1'),
(3,'Hang moi 100%','10-10-2020','5'),
(1,'Hang moi 100%','10-10-2020','2'),
(4,'Hang moi 100%','10-10-2020','7'),
(5,'Hang moi 100%','10-10-2020','5'),
(4,'Hang moi 100%','10-10-2020','2'),
(6,'Hang moi 100%','10-10-2020','9'),
(7,'Hang moi 100%','10-10-2020','1'),
(5,'Hang moi 100%','10-10-2020','6')

-- 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ế
select * from Product
select * from Sale

create view List_saled
as
select * from Sale

select * from List_saled

--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 List_saled_country
	@country nvarchar(200)
as
BEGIN
	select Sale.ProductID,Sale.SaleDay,Sale.Quantity,Product.Manufacture,Product.Country
	from Sale left join Product on Sale.ProductID = Product.ID
	where Product.Country = @country
END

exec List_saled_country VietNam

--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
create proc totalprice 
 @productID int, @total money output
 as
 begin
	
	select Sale.ProductID, Product.Name, Product.Price_Out * sum(Sale.Quantity) as tonggia
	from Sale left join Product on Sale.ProductID = Product.ID where Product.Id = @productID
	group by Sale.ProductID, Product.Name, Product.Price_Out

	select  @total = sum(tonggia) from 
	(select Sale.ProductID, Product.Name,  Product.Price_Out * sum(Sale.Quantity) as tonggia
	from Sale left join Product on Sale.ProductID = Product.ID where Product.Id = @productID
	group by Sale.ProductID, Product.Name, Product.Price_Out) as t
 end

 declare @total money
 exec totalprice 1, @total = @total output
 print @total

2020-12-11 08:11:57

create database quan_ly_ban_hang_2
use quan_ly_ban_hang_2

create table commondity(
id int primary key identity (1,1),
name_com nvarchar (100),
source nvarchar (100),
price_nhap float,
price_ban float,
ngay_san_xuat date,)

insert into commondity
values
('ban chai danh rang','vietnam','10000','15000','2020-09-26'),
('laptop','japan','100000','150000','2020-09-26'),
('ban ghe co','vietnam','10000','15000','2020-09-26'),
('ban chai danh rang','USA','10000','15000','2020-09-26'),
('ban chai danh rang','vietnam','10000','15000','2020-09-26'),
('ban chai danh rang','vietnam','10000','15000','2020-09-26'),
('ban chai danh rang','vietnam','10000','15000','2020-09-26'),
('ban chai danh rang','sweden','10000','15000','2020-09-26'),
('ban chai danh rang','spain','10000','15000','2020-09-26'),
('ban chai danh rang','spain','10000','15000','2020-09-26')

select * from commondity

create table sale (
id int identity (1,1) primary key ,
id_hanghoa int ,
constraint fk_id_hang_hoa foreign key (id) references commondity(id),
note text,
date_sale date,
amount nvarchar (50),)

alter table sale 
add constraint fk_id_hang_hoa foreign key (id) references commondity(id)

alter table sale 
drop constraint fk_id_hang_hoa

insert into sale
values
('2','hang da ban ra','2020-2-26','100'),
('3','hang da ban ra','2020-2-26','100'),
('4','hang da ban ra','2020-2-26','100'),
('5','hang da ban ra','2020-2-26','100'),
('6','hang da ban ra','2020-2-26','100'),
('7','hang da ban ra','2020-2-26','100'),
('8','hang da ban ra','2020-2-26','100'),
('9','hang da ban ra','2020-2-26','100'),
('10','hang da ban ra','2020-2-26','100'),
('11','hang da ban ra','2020-2-26','100')

alter table sale 
add note nvarchar(100);

select * from sale

update sale
set note = 'hang da ban ra'
where date_sale = '2020-02-26'

update sale
set note = 'hang chua dc ban ra'
where id=8

update sale
set note = 'hang chua dc ban ra'
where id=9

update sale
set note = 'hang chua dc ban ra'
where id=10


create view view_show
as
select sale.date_sale , sale.amount,commondity.name_com,sale.note,commondity.price_nhap,commondity.price_ban
from sale, commondity
where sale.id_hanghoa=commondity.id

select * from dbo.view_show

select * from commondity
select * from sale

create proc proc_show_source
@madein nvarchar (100)
as 
begin
select sale.date_sale,sale.amount,sale.id_hanghoa,sale.note,commondity.name_com 
from sale , commondity 
where  sale.id= commondity.id
and commondity.source=@madein
end

exec proc_show_source 'vietnam'
exec proc_show_source 'USA'

alter table commondity
add total_price money

update  commondity
set total_price=price_nhap* price_ban

create proc proc_total_1
@name nvarchar (100)
as
begin
select name_com, total_price
from commondity
where commondity.source= @name
end

exec  proc_total_1 vietnam
exec  proc_total_1 USA
exec  proc_total_1 japan

2020-12-11 08:05:42

create database QuanLyBanHang

use QuanLyBanHang

create table product (
	id int primary key identity (1,1),
	name nvarchar(100),
	nha_sx nvarchar(100),
	made_in nvarchar(50),
	price float,
	price_sell float,
	ngay_sx date
)

insert into product(name, nha_sx, made_in, price, price_sell, ngay_sx)
values
('Iphone 5s', 'Apple', 'US', '4.000', '2.000', '2018-05-12'),
('Iphone 6s', 'Apple', 'US', '9.000', '6.000', '2019-05-12'),
('Iphone 6s plus', 'Apple', 'China', '10.000', '8.000', '2019-07-12'),
('Iphone 7', 'Apple', 'China', '11.000', '8.000', '2019-06-20'),
('Iphone XS', 'Apple', 'US', '20.000', '21.000', '2020-05-12'),
('Iphone 12', 'Apple', 'UK', '42.000', '44.000', '2012-12-12'),
('Samsung 7', 'Samsung', 'Korea', '12.000', '13.000', '2017-04-30'),
('Samsung note 7', 'Samsung', 'Kkorea', '13.000', '10.000', '2018-08-25'),
('Samsung 10', 'Samsung', 'China', '32.000', '29.000', '2018-05-18'),
('Samsung 10s', 'Samsung', 'Korea', '40.000', '44.000', '2019-05-21')

select * from product

create table orders (
	id int primary key identity (1,1),
	id_hanghoa int references product(id),
	note nvarchar(500),
	ngay_ban date,
	num int
)

insert into orders(id_hanghoa, note, ngay_ban, num)
values
(1, '', '2018-05-12', 3),
(2, '', '2019-06-12', 5),
(3, '', '2019-08-21', 3),
(4, '', '2019-07-29', 5),
(5, '', '2018-02-12', 3),
(6, '', '2020-06-27', 4),
(7, '', '2018-02-12', 3),
(8, '', '2018-06-24', 4),
(9, '', '2018-07-23', 6),
(10, '', '2019-08-30', 8)

select * from orders

create view view_orders
as
select orders. *, product.price_sell
from product, orders
where product.id = orders.id_hanghoa and num > 0

select * from view_orders

create proc proc_orders
	@made_in nvarchar(50)
as
begin
	select product.id, product.name, product.made_in, product.price_sell, orders.ngay_ban, orders.num
	from product, orders
	where product.id = orders.id_hanghoa and product.made_in = @made_in
end

exec proc_orders 'Korea'

create proc proc_total
	@product_id int, @total money output
as
begin
	select product.id, product.name, orders.num, product.price_sell, sum(orders.num) total
	from product, orders
	where product.id = orders.id_hanghoa and product.id = @product_id
	group by product.id, product.name, orders.num, product.price_sell

	select @total = SUM(total) from (
	select product.id, product.name, orders.num, product.price_sell, sum(orders.num) total
	from product, orders
	where product.id = orders.id_hanghoa and product.id = @product_id
	group by product.id, product.name, orders.num, product.price_sell
	) t
end

drop proc proc_total

declare @total money
exec proc_total 1, @total = @total output
print @total

2020-12-11 08:01:28

create database banhang
use banhang

create table product(
	id int primary key identity(1,1),
	name nvarchar(100), 
	producer nvarchar(100),
	made_in nvarchar(100),
	import_price money,
	sell_price money,
	manu_date date
)

insert into product(name, producer, made_in, import_price, sell_price, manu_date)
values
('ao khoac da','abc','japan', 25200, 100635, '2020/11/25'),
('ao phong','abc','thailand', 100000, 250536, '2020/04/06'),
('vay','def','singapore', 260000, 206573, '2017/12/22'),
('quan bo','ghi','vietnam', 520000, 76005635, '2016/11/29'),
('quan dui','ghi','korea', 8000, 12034, '2019/04/18'),
('ao dai tay','abc','japan', 34562, 456725, '2020/11/27'),
('ao giu nhiet','abc','philipines', 56762, 897245, '2019/10/24'),
('chan vay','def','vietnam', 345600, 6734000, '2020/08/28'),
('vest','abc','vietnam', 650000, 7600000, '2015/12/25'),
('quan kaki','ghi','korea', 65700, 567300, '2020/01/21')

select * from product

update product set name = 'vay dai tay' where id = 3

create table sale(
	id int primary key identity(1,1),
	id_product int references product(id),
	note text, 
	sale_date date,
	amount int
)

insert into sale(id_product, note, sale_date, amount)
values
(4,'best seller', '2020/11/05',56),
(5,'newly arrived', '2020/11/27',65),
(3,'out of stock', '2020/11/23',496),
(7,'80 left', '2020/11/25',26),
(6,'newly arrived', '2020/11/27',85),
(8,'380 left', '2020/10/18',998),
(9,'346 left', '2020/09/24',57),
(10,'best seller', '2020/10/29',89),
(3,'newly arrived', '2020/08/30',564),
(2,'newly arrived', '2020/11/25',28)

select * from sale

CREATE VIEW view_order_detail
AS
SELECT sale.id as 'order id', product.id as 'product id', product.name, sale.note, sale.sale_date, sale.amount
FROM product, sale
WHERE product.id = sale.id_product

SELECT * FROM view_order_detail

create proc proc_view_order_madein
	@madein nvarchar(100)
AS
BEGIN
	SELECT sale.id as 'order id', product.id as 'product id', product.name, product.made_in, sale.sale_date, sale.amount
	FROM product, sale
	WHERE product.id = sale.id_product AND product.made_in = @madein
	GROUP BY sale.id, product.id, product.name, product.made_in, sale.sale_date, sale.amount
END

EXEC proc_view_order_madein 'vietnam'
EXEC proc_view_order_madein 'korea'

select sale.id, id_product, name, note, sale_date, amount, sell_price into order_table 
from product, sale
where product.id=sale.id_product

alter table order_table 
add total_price money

select * from order_table

update order_table set total_price=amount*sell_price

create proc proc_view_total_price
	@product_name nvarchar(100),
	@total_price money output
AS
BEGIN
	SELECT name, amount, sell_price
	FROM order_table
	WHERE order_table.name = @product_name 
	BEGIN
		select @total_price=amount*sell_price
		FROM order_table
	END
END

declare @total_price_product int
EXEC proc_view_total_price 'quan bo', @total_price = @total_price_product output

2020-12-11 07:58:46

CREATE DATABASE Banhang
USE Banhang

CREATE TABLE Product(
	ID INT PRIMARY KEY IDENTITY(1,1),
	ProductName NVARCHAR(50),
	Manufacturer NVARCHAR(50),
	MadeIn NVARCHAR(20),
	InportPrice MONEY,
	ExportPrice MONEY,
	NSX DATE
)

CREATE TABLE Buylist(
	ID INT PRIMARY KEY IDENTITY(1,1),
	ID_Product INT FOREIGN KEY REFERENCES dbo.Product(ID),
	Note NVARCHAR(100),
	Buytime DATE,
	Quantity INT
)

INSERT dbo.Product
        (
          ProductName ,
          Manufacturer ,
          MadeIn ,
          InportPrice ,
          ExportPrice ,
          NSX
        )
VALUES  (
          N'Bim bim' , -- ProductName - nvarchar(50)
          N'VFood' , -- Manufacturer - nvarchar(50)
          N'Việt Nam' , -- MadeIn - nvarchar(20)
          4000 , -- InportPrice - money
          5000 , -- ExportPrice - money
          GETDATE()  -- NSX - date
        ),
		(
          N'Thuốc lá' , -- ProductName - nvarchar(50)
          N'Nhà máy Thăng Long' , -- Manufacturer - nvarchar(50)
          N'Việt Nam' , -- MadeIn - nvarchar(20)
          17000 , -- InportPrice - money
          21000 , -- ExportPrice - money
          GETDATE()  -- NSX - date
        ),
		(
          N'Kẹo lạc' , -- ProductName - nvarchar(50)
          N'ABC' , -- Manufacturer - nvarchar(50)
          N'Thái Lan' , -- MadeIn - nvarchar(20)
          20000 , -- InportPrice - money
          25000 , -- ExportPrice - money
          GETDATE()  -- NSX - date
        ),
		(
          N'Coca' , -- ProductName - nvarchar(50)
          N'Cola' , -- Manufacturer - nvarchar(50)
          N'Mỹ' , -- MadeIn - nvarchar(20)
          10000 , -- InportPrice - money
          15000 , -- ExportPrice - money
          GETDATE()  -- NSX - date
        ),
		(
          N'Bia Hà Nội' , -- ProductName - nvarchar(50)
          N'Nhà máy bia hơi' , -- Manufacturer - nvarchar(50)
          N'Việt Nam' , -- MadeIn - nvarchar(20)
          10000 , -- InportPrice - money
          15000 , -- ExportPrice - money
          GETDATE()  -- NSX - date
        ),
		(
          N'Bánh quy' , -- ProductName - nvarchar(50)
          N'ABCXYZ' , -- Manufacturer - nvarchar(50)
          N'Pháp' , -- MadeIn - nvarchar(20)
          70000 , -- InportPrice - money
          77000 , -- ExportPrice - money
          GETDATE()  -- NSX - date
        ),
		(
          N'Kẹo mút' , -- ProductName - nvarchar(50)
          N'NSX kẹo mút' , -- Manufacturer - nvarchar(50)
          N'Lào' , -- MadeIn - nvarchar(20)
          500 , -- InportPrice - money
          1000 , -- ExportPrice - money
          GETDATE()  -- NSX - date
        ),
		(
          N'Bánh dừa khô' , -- ProductName - nvarchar(50)
          N'NSX bánh dừa' , -- Manufacturer - nvarchar(50)
          N'Việt Nam' , -- MadeIn - nvarchar(20)
          30000 , -- InportPrice - money
          35000 , -- ExportPrice - money
          GETDATE()  -- NSX - date
        ),
		(
          N'Kem ốc quế' , -- ProductName - nvarchar(50)
          N'Merino' , -- Manufacturer - nvarchar(50)
          N'Việt Nam' , -- MadeIn - nvarchar(20)
          10000 , -- InportPrice - money
          12000 , -- ExportPrice - money
          GETDATE()  -- NSX - date
        ),
		(
          N'Thịt bò khô' , -- ProductName - nvarchar(50)
          N'NSX thịt bò' , -- Manufacturer - nvarchar(50)
          N'Việt Nam' , -- MadeIn - nvarchar(20)
          20000 , -- InportPrice - money
          25000 , -- ExportPrice - money
          GETDATE()  -- NSX - date
        )
SELECT * FROM dbo.Product

GO 
INSERT dbo.Buylist
        (
          ID_Product ,
          Note ,
          Buytime ,
          Quantity
        )
VALUES  ( 
          1 , -- ID_Product - int
          N'Bim bim nhớ cay' , -- Note - nvarchar(100)
          GETDATE() , -- Buytime - date
          5  -- Quantity - int
        ),
		( 
          3 , -- ID_Product - int
          N'Không ngon không nhận đồ' , -- Note - nvarchar(100)
          GETDATE() , -- Buytime - date
          20  -- Quantity - int
        ),
		( 
          9 , -- ID_Product - int
          N'Ship 10 cái kem' , -- Note - nvarchar(100)
          GETDATE() , -- Buytime - date
          11  -- Quantity - int
        ),
		( 
          7 , -- ID_Product - int
          N'Không phải Milkita là được' , -- Note - nvarchar(100)
          GETDATE() , -- Buytime - date
          100  -- Quantity - int
        ),
		( 
          8 , -- ID_Product - int
          N'Ship đến nhà' , -- Note - nvarchar(100)
          GETDATE() , -- Buytime - date
          2  -- Quantity - int
        ),
		( 
          5 , -- ID_Product - int
          N'Phải tươi' , -- Note - nvarchar(100)
          GETDATE() , -- Buytime - date
          30  -- Quantity - int
        ),
		( 
          4 , -- ID_Product - int
          N'Mát lạnh' , -- Note - nvarchar(100)
          GETDATE() , -- Buytime - date
          7  -- Quantity - int
        ),
		( 
          2 , -- ID_Product - int
          N'Hút thuốc lá có thể gây ung thư phổi' , -- Note - nvarchar(100)
          GETDATE() , -- Buytime - date
          10  -- Quantity - int
        ),
		( 
          9 , -- ID_Product - int
          N'Lấy ốc quế không lấy kem' , -- Note - nvarchar(100)
          GETDATE() , -- Buytime - date
          20  -- Quantity - int
        ),
		( 
          6 , -- ID_Product - int
          N'Loại bánh giòn' , -- Note - nvarchar(100)
          GETDATE() , -- Buytime - date
          5  -- Quantity - int
        )
SELECT * FROM dbo.Buylist

GO
CREATE VIEW ShowProductBought
AS
SELECT * FROM dbo.Buylist

GO
SELECT * FROM dbo.ShowProductBought

GO
CREATE PROC ListSaleCountry
	@country NVARCHAR(20)
AS
BEGIN
	SELECT dbo.Buylist.ID_Product, dbo.Buylist.Buytime, dbo.Buylist.Quantity, dbo.Product.Manufacturer, dbo.Product.MadeIn
	FROM dbo.Buylist LEFT JOIN dbo.Product ON Product.ID = dbo.Buylist.ID_Product
	WHERE dbo.Product.MadeIn = @country
END

EXEC dbo.ListSaleCountry @country = N'Việt Nam' -- nvarchar(20)

go
CREATE PROC TotalPrice
	@productID int, @total MONEY OUTPUT
AS
BEGIN
	SELECT dbo.Buylist.ID_Product, dbo.Product.ProductName, SUM(dbo.Buylist.Quantity) AS Tongsoluong, dbo.Product.ExportPrice * SUM(dbo.Buylist.Quantity) AS TongGia
	FROM dbo.Buylist LEFT JOIN dbo.Product ON ID_Product = Product.ID
	WHERE ID_Product = @productID
	GROUP BY dbo.Buylist.ID_Product, dbo.Product.ProductName, dbo.Product.ExportPrice

	SELECT @total = SUM(TongGia) FROM
    (SELECT dbo.Buylist.ID_Product, dbo.Product.ProductName, SUM(dbo.Buylist.Quantity) AS TongSoLuong, dbo.Product.ExportPrice * SUM(dbo.Buylist.Quantity) AS TongGia
	FROM dbo.Buylist LEFT JOIN dbo.Product ON ID_Product = Product.ID
	WHERE ID_Product = @productID
	GROUP BY dbo.Buylist.ID_Product, dbo.Product.ProductName, dbo.Product.ExportPrice) AS t
END

GO
DECLARE @total MONEY
EXEC dbo.TotalPrice @productID = 9, -- int
    @total = @total OUTPUT -- money
PRINT @total