Yêu cầu.

2021-04-24 09:34:38

Create database QuanLyBanHang

go

use QuanLyBanHang

go

Create table Product (

id int primary key identity (1,1),

name nvarchar(50),

producer nvarchar(50),

made_in nvarchar(30),

price_in int,

price_out int,

day_released date

)

go

Create table Sell (

id int primary key identity(1,1),

id_product int,

note text,

day_sell date,

quantity int,

FOREIGN KEY (id_product) references Product(id)

)

go

insert into Product(name, producer, made_in, price_in, price_out, day_released)

values 

('noi com','LG','vn',5,7,'2021-04-01'),

('tu lanh','Samsung','Kor',40,70,'2021-04-01'),

('quat','Mitsubishi','japan',4,6,'2021-04-01'),

('brake','Porsche','Ger',1900,2000,'2021-04-01'),

('Calculator','Ticktack','china',80,90,'2021-04-01'),

('Charger','Indi','Indo',45,50,'2021-04-01'),

('Toto','Bucha','japan',47,57,'2021-04-01'),

('Ross','Canada','Canada',1,10,'2021-04-01')

('noi com','LG','vn',5,7,'2021-04-01'),

go

('tu lanh','Samsung','Kor',40,70,'2021-04-01')

insert into Sell (id_product, note, day_sell, quantity)

values

(1,'ahifha', '2021-04-01', 99),

(3,'sdgs', '2021-04-01', 99),

(5,'afsgs', '2021-04-01', 99),

(4,'sgfd', '2021-04-01', 99),

(10,'ahifha', '2021-04-01', 99),

(9,'sdgs', '2021-04-01', 99),

(7,'afsgs', '2021-04-01', 99),

(8,'sgfd', '2021-04-01', 99),

(6,'ahifha', '2021-04-01', 99),

(2,'ahifha', '2021-04-01', 99)

go

select * from Product

select * from Sell

create view view_sold_product 

as 

select sell.id 'Prodcut code', Product.name, Product.made_in, sell.day_sell, sell.quantity

from Sell, Product

where sell.id_product = Product.id

select * from view_sold_product

create proc proc_view_sold_product

@origin nvarchar(20)

as begin

select sell.id 'Prodcut code', Product.name, Product.made_in,  sell.day_sell, sell.quantity

from Sell, Product

where Sell.id_product=Product.id and

Product.made_in = @origin 

end

exec proc_view_sold_product Canada

select Product.name, SUM(Product.price_out * Sell.quantity) 'Total_price'

from Sell left join Product on Sell.id_product = Product.id

group by Product.name

create proc proc_sum_price_product

@mat_hang nvarchar(20),

@total int output

as begin

select @total = SUM(Product.price_out * Sell.quantity)

from Product, Sell

where Sell.id_product = Product.id

and Product.name = @mat_hang

group by Product.name, Product.price_out

end

drop proc proc_sum_price_product

declare @count int

exec proc_sum_price_product 'tu lanh', @total = @count output

print N'Tong gia tri: ' + + CONVERT(nvarchar(20), @count)

2021-03-02 08:03:46

-- 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)

2020-12-11 10:01:19

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
	

2020-12-11 09:54:05

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 product.origin=@origin
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 product.id=@productId
end

declare @tatal_price float 
exec vw_tatal_price 3, @tatal_price=@tatal_price output 
print @tatal_price 
print N'Tong tien ban duoc: '+ convert(nvarchar(50), @tatal_price)

2020-12-11 09:43:18

-- 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)

2020-12-11 09:42:18

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 =@id
end

2020-12-11 09:29:03

create database quanlybanhang1801
use quanlybanhang1801
create table product (
	id int primary key identity(1,1),
	name nvarchar(50),
	nameproducer nvarchar(100),
	madeby nvarchar(20),
	price_in money,
	price_out money,
	dateofmanufacture date
)

insert into product (name, nameproducer, madeby, price_in, price_out,dateofmanufacture)
values
('Cafe G7 21', 'Trung Nguyen','Viet Nam', 35000, 42000,'2020-10-02'),
('Cafe G7 50', 'Trung Nguyen','Viet Nam', 35000, 42000,'2020-10-22'),
('Pepsi', 'PepsiCo','Thai Lan', 22000, 25000,'2020-10-25'),
('Iphone 12', 'Iphone','USA', 111221000, 1222111000,'2020-11-12'),
('Samsung S10', 'SamSung','Han Quoc', 135000, 242000,'2020-12-22'),
('Cafe G7 50', 'Trung Nguyen','Viet Nam', 35000, 42000,'2020-10-22'),
('S to', 'Trung Nguyen','Viet Nam', 38000, 46000,'2020-10-15'),
('Cafe CF2', 'Trung Nguyen','Viet Nam', 315000, 412000,'2020-10-22'),
('Cafe Sang tao 2', 'Trung Nguyen','Viet Nam', 135000, 342000,'2020-10-11'),
('Cafe Sang tao 2', 'Trung Nguyen','Viet Nam', 235000, 242000,'2020-08-16')

create table donhang (
	id int primary key identity(1,1),
	id_product int references product(id),
	note nvarchar(200),
	date_sale date,
	num int
)

insert into donhang (id_product, note, date_sale, num)
values
(2, 'Cafe hoa tan', '2020-12-11', 20),
(4, 'Sp dien tu', '2020-12-11', 1),
(4, 'Sp dien tu', '2020-10-11', 2),
(5, 'Sp dien tu', '2020-10-11', 4),
(2, 'Cafe hoa tan', '2020-10-11', 4),
(1, 'Cafe hoa tan', '2020-11-11', 12),
(2, 'Cafe hoa tan', '2020-11-11', 4),
(8, 'Cafe phin', '2020-12-15', 3),
(7, 'Cafe phin', '2020-12-13', 5),
(8, 'Cafe phin', '2020-12-22', 10)


select * from product
select * from donhang
--View Don hang gom có: ID san pham, ten san pham, xuat xu, ngay sx, gia ban, num, note
create view view_show_order_detail
as
select donhang.id_product, product.name, product.madeby, product.dateofmanufacture, product.price_out, donhang.num, donhang.note
from product, donhang
where product.id = donhang.id_product

select * from view_show_order_detail
order by id_product asc

--Proc liet ke don hang co Xuat xu == ???
create proc proc_donhang_madeBy
	@madeby nvarchar(20)
as
begin
	select donhang.id_product, product.name, product.madeby, product.dateofmanufacture, product.price_out, donhang.num, donhang.note
	from product, donhang
	where product.id = donhang.id_product and product.madeby = @madeby
end

exec proc_donhang_madeBy USA
exec proc_donhang_madeBy 'Viet Nam'

--Tong gia ban cho tung mat hang
create proc sum_total_productok
	@name nvarchar(50),
	@total money output
as
begin
	SELECT @total = sum(view_show_order_detail.price_out * view_show_order_detail.num)
	FROM view_show_order_detail
	WHERE view_show_order_detail.name = @name
end

DECLARE @total money
EXEC sum_total_productok 'S to', @total=@total output
PRINT @total

2020-12-11 09:03:15

#ban hang.sql

Create database Quanlybanhang
use Quanlybanhang

create table product(
	id int primary key identity(1,1),
	name nvarchar(50),
	producer nvarchar(50),
	origin nvarchar(30),
	price_in money,
	price_out money,
	created_date date
)
insert into product(name, producer, origin, price_in, price_out,created_date)
values('SP1','NSX1', 'VietNam','50','70','05-05-2019'),
('SP2','NSX2', 'Viet Nam','20','70','05-05-2019'),
('SP3','NSX4', 'Trung Quoc','30','70','05-05-2019'),
('SP4','NSX5', 'Thai Lan','50','70','05-05-2019'),
('SP5','NSX7', 'Han Quoc','50','70','05-05-2019'),
('SP6','NSX8', 'VietNam','50','70','05-05-2019'),
('SP7','NSX2', 'VietNam','50','70','05-05-2019'),
('SP8','NSX3', 'VietNam','50','70','05-05-2019'),
('SP9','NSX1', 'VietNam','50','70','05-05-2019'),
('SP10','NSX1', 'VietNam','50','70','05-05-2019')

create table selling(
	id int primary key identity(1,1),
	id_hanghoa int constraint fk_id foreign key references product(id),
	note nvarchar(100),
	sell_date date,
	num int
)
insert into selling(id_hanghoa, note, sell_date,num)
values('2', 'hang loai 1', '02-02-2020', 20),
('3', 'hang loai 2', '02-02-2020', 30),
('4', 'hang loai 3', '02-02-2020', 20),
('3', 'hang loai 4', '02-02-2020', 50),
('5', 'hang loai 5', '02-02-2020', 20),
('6', 'hang loai 6', '02-02-2020', 60),
('1', 'hang loai 7', '02-02-2020', 20),
('9', 'hang loai 8', '02-02-2020', 10),
('5', 'hang loai 9', '02-02-2020', 20),
('1', 'hang loai 10', '02-02-2020', 50)

select*from selling
create view donbanra as
Select selling.id_hanghoa,selling.note, selling.num, selling.sell_date, product.name, product.producer,product.origin,product.price_in, product.price_out,product.created_date
From selling, product
Where product.id=selling.id_hanghoa

select*from donbanra

create proc proc_origin_product
	@origin nvarchar(100)
as
begin
select selling.id_hanghoa,selling.note, selling.num, selling.sell_date, product.name, product.producer,product.origin,product.price_in, product.price_out,product.created_date
	from product,selling
	where product.origin = @origin and product.id=selling.id_hanghoa
end
exec proc_origin_product 'VietNam'
exec proc_origin_product 'Han Quoc'

create proc proc_price_total
   @name int, @totalprice int
as
begin
	select product.id, product.price_out, selling.id_hanghoa, selling.num
	from product, selling
	where product.id=selling.id_hanghoa 
	group by @name, product.price_out*selling.num as @totalprice 
end

2020-12-11 08:22:49

create database Quan_li_ban_hang_VIP
use Quan_li_ban_hang_VIP
create table Product(
	id int primary key identity(1,1),
	title nvarchar(100),
	producer nvarchar(50),
	origin nvarchar(50),
	import_price money,
	price money,
	ngay_san_xuat date
)
create table Ban_hang(
	id int primary key identity(1,1),
	id_hanghoa int references Product(id),
	note text,
	ngay_ban date,
	num int
)
select * from Product
insert into Product(title,producer,origin,import_price,price,ngay_san_xuat)
values
('fish','FLC','VietNam',10,15,'2020-12-01'),
('crab','Toyota','Japan',30,45,'2020-12-01'),
('shrimp','Vingroup','VietNam',15,25,'2020-12-01'),
('shark','LamTran entertainment','VietNam',20,25,'2020-12-01'),
('cuttle','Alibaba','China',40,55,'2020-12-01'),
('orange','ABC','USA',10,15,'2020-12-01'),
('banana','Obama','USA',5,10,'2020-12-01'),
('coconut','Shiaba','Japan',10,25,'2020-12-01'),
('kiwi','Lamgroup','VietNam',30,45,'2020-12-01'),
('chicken','HuanHoaHongGroup','VietNam',20,25,'2020-12-01')
select * from Ban_hang
insert into Ban_hang(id_hanghoa,note,ngay_ban,num)
values
(1,'abcxyz','2020-12-10',3),
(3,'abcxyz','2020-12-10',5),
(2,'abcxyz','2020-12-10',2),
(2,'abcxyz','2020-12-10',1),
(4,'abcxyz','2020-12-10',2),
(6,'abcxyz','2020-12-10',4),
(4,'abcxyz','2020-12-10',1),
(7,'abcxyz','2020-12-10',2),
(1,'abcxyz','2020-12-10',3),
(3,'abcxyz','2020-12-10',2)
select * from Product
select * from Ban_hang
select Product.title,Product.price,Ban_hang.id_hanghoa,Ban_hang.note,Ban_hang.ngay_ban,Ban_hang.num
from Product,Ban_hang
where Product.id = Ban_hang.id_hanghoa
order by Ban_hang.num asc

create view cac_don_hang
as
select Product.title,Ban_hang.id_hanghoa,Ban_hang.note,Ban_hang.ngay_ban,Ban_hang.num
from Product,Ban_hang
where Product.id = Ban_hang.id_hanghoa
select * from cac_don_hang

select  Product.origin,Product.title,Product.price,Ban_hang.id_hanghoa,Ban_hang.note,Ban_hang.ngay_ban,Ban_hang.num
from Product,Ban_hang
where Product.id = Ban_hang.id_hanghoa
	and Product.origin = 'Japan'
create proc proc_view_origin
	@origin nvarchar(50)
as 
begin
	select  Product.origin,Product.title,Product.price,Ban_hang.id_hanghoa,Ban_hang.note,Ban_hang.ngay_ban,Ban_hang.num
from Product,Ban_hang
where Product.id = Ban_hang.id_hanghoa
	and Product.origin = @origin
end
exec proc_view_origin 'VietNam'

create proc proc_view_total
	@title int, @total money output
as
begin
	select Product.id, Product.title,Ban_hang.num, Product.price, sum(Ban_hang.num) total
	from Product, Ban_hang
	where Product.id = Ban_hang.id_hanghoa and Product.id = @title
	group by Product.id, Product.title,Ban_hang.num,Product.price

	select @total = SUM(total) from (
	select Product.id,Product.title,Ban_hang.num,Product.price, sum(Ban_hang.num) total
	from Product, Ban_hang
	where Product.id = Ban_hang.id_hanghoa and Product.id = @title
	group by Product.id, Product.title, Ban_hang.num, Product.price
	) t
end
declare @total money
exec proc_view_total ,@total = @total output
print @total

2020-12-11 08:14:59

create database qunlybanhang
use qunlybanhang

create table Hang_hoa(
id int identity(1,1) primary key,
Ten_mat_hang nvarchar (100),
Nha_sx nvarchar(100),
Xuat_xu nvarchar(100),
Gia_nhap money,
Gia_ban money,
Ngay_san_xuat date
)
insert into Hang_hoa (Ten_mat_hang,Nha_sx,Xuat_xu,Gia_nhap,Gia_ban,Ngay_san_xuat)
values
('Ban an','A','Trung Quoc','100','150','2020-10-20'),
('Ban an B ','A','Viet Nam','100','150','2020-10-20'),
('Ban B','A','Nhat Ban','100','150','2020-10-20'),
('Ban C','B','Viet Nam','100','150','2020-10-20'),
('Ban EE','C','Viet Nam','100','150','2020-10-20'),
('Ban HHH','A','Nhat Ban','100','150','2020-10-20'),
('Ban KKK','B','Trung Quoc','100','150','2020-10-20'),
('Ban aPP','C','Viet Nam','100','150','2020-10-20'),
('Ban HHJ','A','Nhat Ban','100','150','2020-10-20'),
('Ban OOP<L','C','Viet Nam','100','150','2020-10-20')

create table ban_hang(
id int identity(1,1) primary key,
id_hang_hoa int,
chu_thich text,
Ngay_ban date,
so_luong int
)
insert into ban_hang (id_hang_hoa,chu_thich,Ngay_ban,so_luong)
values
('1','ok good','2020-12-03','1'),
('2','ok good','2020-12-25','3'),
('3','ok good','2020-12-01','2'),
('4','ok good','2020-12-18','1'),
('5','ok good','2020-12-20','1'),
('6','ok good','2020-12-16','4'),
('7','ok good','2020-12-27','2'),
('8','ok good','2020-12-03','1'),
('9','ok good','2020-12-09','7'),
('10','ok good','2020-12-13','1')

ygg
create view liet_ke_hoa_don
as
select Hang_hoa.Ten_mat_hang,Hang_hoa.Nha_sx,Hang_hoa.Xuat_xu,Hang_hoa.Gia_ban,ban_hang.chu_thich,ban_hang.so_luong,ban_hang.Ngay_ban
from ban_hang,Hang_hoa
where Hang_hoa.id = ban_hang.id_hang_hoa

select * from liet_ke_hoa_don

create proc KT_xuat_su
@_xuatsu nvarchar(100)
as
select Hang_hoa.Ten_mat_hang,Hang_hoa.Nha_sx,Hang_hoa.Xuat_xu,Hang_hoa.Gia_ban,ban_hang.chu_thich,ban_hang.so_luong,ban_hang.Ngay_ban
from ban_hang,Hang_hoa
where Hang_hoa.id = ban_hang.id_hang_hoa and Hang_hoa.Xuat_xu = @_xuatsu

exec KT_xuat_su 'Viet Nam'

alter proc ktspppaaa
@mathang int,
@total money output
as
begin
select Hang_hoa.Ten_mat_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_mat_hang,Hang_hoa.Nha_sx,Hang_hoa.Xuat_xu,Hang_hoa.Gia_ban
select @total = tong_gia from
(select Hang_hoa.Ten_mat_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_mat_hang,Hang_hoa.Nha_sx,Hang_hoa.Xuat_xu,Hang_hoa.Gia_ban)as abc
end

declare @total money
exec ktspppaaa 6 ,@total = @total output
print @total