Yêu cầu.

2020-11-27 09:55:54

create database BT1776
go

use BT1776

create table hanghoa (
	id int primary key identity (1,1),
	name nvarchar(100) not null,
	nhasx nvarchar(50),
	made nvarchar(50),
	price float,
	sell float,
	ngaysx date
)

create table banhang (
	id int primary key identity (1,1),
	id_hanghoa int references hanghoa(id),
	note nvarchar(500),
	ngayban date,
	soluong int
)


insert into hanghoa(name, nhasx, made, price, sell, ngaysx)
values
('Tivi', 'Sony', 'VIET NAM', 10.000, 12.000, '2020-11-27'),
('Tu Lanh', 'Samsung', 'KOREA', 9.000, 10.000, '2020-10-27'),
('May Hut Bui', 'LG', 'VIET NAM', 7.000, 8.000, '2020-11-26'),
('Bep', 'Sony', 'JAPAN', 12.000, 13.000, '2020-11-25'),
('Tivi', 'Samsung', 'KOREA', 10.000, 12.500, '2020-10-25'),
('Dieu Hoa', 'LG', 'VIET NAM', 11.000, 12.000, '2020-10-26'),
('Quat', 'Sony', 'VIET NAM', 4.000, 5.000, '2020-11-23'),
('May Giat', 'Sony', 'JAPAN', 8.900, 9.000, '2020-10-23'),
('Tivi', 'LG', 'VIET NAM', 10.000, 12.500, '2020-11-24'),
('Tvi', 'Samsung', 'JAPAN', 11.000, 12.700, '2020-10-24')


insert into banhang(id_hanghoa, note, ngayban, soluong)
values
(1, '', '2020-10-30', 5),
(2, '', '2020-10-27', 4),
(3, '', '2020-10-28', 3),
(4, '', '2020-10-29', 6),
(5, '', '2020-11-30', 5),
(6, '', '2020-11-28', 5),
(7, '', '2020-11-29', 3),
(8, '', '2020-11-30', 4),
(9, '', '2020-10-30', 2),
(10, '', '2020-11-30', 1)


select * from hanghoa
select * from banhang

---- Liệt kê tất cả đơn hàng được bán ra
select banhang.id_hanghoa, hanghoa.name, hanghoa.sell, banhang.ngayban, banhang.soluong 
into donhang
from hanghoa, banhang
where hanghoa.id = banhang.id_hanghoa

select * from donhang

----- Liệt kê các đơn hàng được bán ra có xuất xứ VN
select hanghoa.name, banhang.soluong, hanghoa.sell, hanghoa.ngaysx, hanghoa.nhasx, hanghoa.made
into donhang2
from hanghoa, banhang
where hanghoa.id = banhang.id_hanghoa

select * from donhang2 where made = 'VIET NAM'


----- Thống kê tổng giá bán được cho từng mặt hàng
select hanghoa.name, banhang.soluong, hanghoa.sell
into donhang3
from hanghoa, banhang
where hanghoa.id = banhang.id_hanghoa

select * from donhang3

alter table donhang3
add total_price float

update donhang3 set total_price = donhang3.soluong * donhang3.sell

2020-11-27 09:42:01

-- bang quan ly hang hoa
create table commondity(
id int identity(1,1) primary key,
namesp nvarchar (100),
nsx nvarchar (100),
source nvarchar (100),
price_nhap float,
price_ban float,
MFG date,)
-- bang ban hang
create table sale(
id int identity(1,1) primary key,
id_hanghoa int foreign key references commondity(id),
note nvarchar(500),
date_sale date,
soluong nvarchar(100),
)

insert into commondity
values
('can sa','snoopdog','USA','12','40','2020-9-26'),
('may rua bat','snoopdog','japan','12','40','2020-9-26'),
('ngoi nha','snoopdog','uc','12','40','2020-9-26'),
('bna ghe','snoopdog','korea','12','40','2020-9-26'),
('tu lanh','snoopdog','germany','12','40','2020-9-26'),
('ban hoc','snoopdog','vietnam','12','40','2020-9-26'),
('ban an ','snoopdog','vietnam','12','40','2020-9-26'),
('ghe sofa','snoopdog','vietnam','12','40','2020-9-26'),
('dien thoai','snoopdog','vietnam','12','40','2020-9-26'),
('laptop','snoopdog','vietnam','12','40','2020-9-26')

insert into sale
values
('4','da ban may rua bat xuat xu USA','2020-9-26','muoi'),
('5','ban hoc xuat xu VN','2020-11-1','hai muoi'),
('6','ban an xuat xu VN','2020-11-1','hai muoi'),
('7','ghe sofa xuat xu VN','2020-11-1','hai muoi'),
('8','dien thoai xuat xu VN','2020-11-1','hai muoi'),
('9','laptop xuat xu VN','2020-11-1','hai muoi'),
('10','can than hang de vo','2020-9-26','muoi'),
('11','can than hang de vo','2020-9-26','muoi'),
('12','can than hang de vo','2020-9-26','muoi'),
('13','can than hang de vo','2020-9-26','muoi')

select id_hanghoa , soluong , note from sale where date_sale = '2020-11-1'
--Thực hiện liệt kê tất cả các đơn hàng đã được bán ra

alter table sale 
add nguon nvarchar(100)

alter table sale 
add sumsale float

update sale set nguon = 'vietnam' where id=14 and id=18 

alter table sale 
drop column nguon

alter table sale 
add nguon nvarchar(100)

update sale set nguon ='viet nam' where id=14
update sale set nguon ='viet nam' where id=15
update sale set nguon ='viet nam' where id=16
update sale set nguon ='viet nam' where id=17
update sale set nguon ='viet nam' where id=18

update sale set sumsale = '1000000' where nguon='viet nam'

-- xem select cac bang 
select * fro-m commondity
select * from sale

select id_hanghoa,note,date_sale,soluong,nguon from sale where sumsale = '1000000'
--Liệt kê các đơn hàng được bán ra có xuất xứ Việt Nam 

select sumsale,nguon,note from sale where date_sale = '2020-11-01'
-- Thống kê tổng giá bán được cho từng mặt hàng

2020-11-27 09:41:40

create database Quan_Li
use Quan_Li
create table hang_hoa(
	id int primary key identity(1,1),
	name_item nvarchar(50),
	producer nvarchar(50),
	origin nvarchar(30),
	import_price int,
	price int,
	date_production date
)
insert into hang_hoa(name_item,producer,origin,import_price,price,date_production)
values
('Cam','CTY ASS','Japan','3000','4000','2020-10-11'),
('Quyt','CTY PRO','Viet Nam','1000','1500','2020-10-11'),
('Buoi','CTY CR7','Viet Nam','2000','5000','2020-10-11'),
('Gao','CTY ASS','Japan','4000','6000','2020-10-11'),
('Sau Rieng','CTY ASS','Japan','5000','7500','2020-10-11'),
('Du Du','CTY ASS','Japan','7000','8500','2020-10-11'),
('Le','CTY ASS','Japan','6000','8000','2020-10-11'),
('Nhan','CTY KOBE','USA','2500','5000','2020-10-11'),
('Man','CTY ASS','Japan','8000','9000','2020-10-11'),
('Dua Leo','CTY M10','Viet Nam','9000','10000','2020-10-11')
select * from hang_hoa
create table ban_hang(
	id int primary key identity(1,1),
	id_hanghoa int,
	constraint fk_id_hanghoa foreign key (id_hanghoa) references hang_hoa(id),
	note text,
	date_price date,
	num int
)
insert into ban_hang(id_hanghoa,note,date_price,num)
values
(2,'abcxyz','2020-11-11','2'),
(3,'abcxyz','2020-11-11','3'),
(5,'abcxyz','2020-11-11','4'),
(6,'abcxyz','2020-11-11','5'),
(7,'abcxyz','2020-11-11','3'),
(3,'abcxyz','2020-11-11','1'),
(2,'abcxyz','2020-11-11','3'),
(10,'abcxyz','2020-11-11','2'),
(7,'abcxyz','2020-11-11','1'),
(3,'abcxyz','2020-11-11','6')
select * from ban_hang
select ban_hang.id_hanghoa,hang_hoa.name_item,hang_hoa.producer,hang_hoa.origin,hang_hoa.import_price,hang_hoa.price,hang_hoa.date_production
from hang_hoa,ban_hang
where ban_hang.id_hanghoa=hang_hoa.id

select ban_hang.id_hanghoa,hang_hoa.name_item,hang_hoa.producer,hang_hoa.origin,hang_hoa.import_price,hang_hoa.price,hang_hoa.date_production
from hang_hoa,ban_hang
where ban_hang.id_hanghoa=hang_hoa.id and origin='Viet Nam'
select hang_hoa.name_item,hang_hoa.price,ban_hang.num,sum(price) as sum_price
from hang_hoa,ban_hang
group by hang_hoa.name_item,hang_hoa.price,ban_hang.num

2020-11-27 09:35:20

create database quanlybanhang1776
use quanlybanhang1776
create table hanghoa (
	id int primary key identity(1,1),
	tenmathang nvarchar(50),
	tennhasanxuat nvarchar(50),
	xuatxu nvarchar(20),
	gianhap float,
	giaban float,
	ngaysx date
)

create table banhang (
	id int primary key identity(1,1),
	id_hoanghoa int references hanghoa(id),
	note nvarchar(500),
	ngayban date,
	soluong int
)

select * from hanghoa
select * from banhang


insert into hanghoa (tenmathang, tennhasanxuat, xuatxu, gianhap, giaban, ngaysx)
values 
('Iphone 12', 'Apple', 'USA', 22000000, 23000000, '2020-08-12'),
('Iphone 11', 'Apple', 'USA', 18000000, 23000000, '2019-08-12'),
('Samsung S8', 'Samsung', 'VN', 26000000, 27000000, '2020-05-11'),
('Samsung Note10', 'Samsung', 'VN', 34000000, 45000000, '2020-04-22'),
('Iphone 8', 'Apple', 'USA', 22000000, 23000000, '2020-08-12'),
('Iphone 7', 'Apple', 'USA', 22000000, 23000000, '2020-08-12'),
('Iphone 6 Plus', 'Apple', 'USA', 22000000, 23000000, '2020-08-12'),
('Samsung S8', 'Samsung', 'VN', 26000000, 27000000, '2020-05-11'),
('Iphone XS', 'Apple', 'USA', 22000000, 23000000, '2020-08-12'),
('Iphone 12 Pro Max', 'Apple', 'USA', 32000000, 33000000, '2020-08-12')

insert into banhang (id_hoanghoa, note, ngayban, soluong)
values 
(1, 'Iphone cao cap', '2020-09-22', 2),
(1, 'Iphone cao cap', '2020-09-22', 1),
(3, 'Dien thoai', '2020-09-22', 12),
(3, 'Co camera', '2020-09-22', 22),
(5, 'Smartphone', '2020-09-22', 662),
(5, 'Iphone cao cap', '2020-09-22', 4),
(7, 'Iphone cao cap 2', '2020-09-22', 55),
(6, 'Iphone cao cap 3', '2020-09-22', 76),
(9, 'Iphone cao cap 5', '2020-09-22', 29)


-- Liet ke don hang gom id_hoanghoa(banhang), tenmathang(hanghoa), ten nha SX, note, ngayban, gia ban, so luong

select banhang.id_hoanghoa, hanghoa.tenmathang, hanghoa.tennhasanxuat, banhang.note, hanghoa.xuatxu, banhang.ngayban, banhang.soluong, hanghoa.giaban
into donhang
from banhang, hanghoa
where hanghoa.id = banhang.id_hoanghoa

select * from donhang



-- Liet ke don hang co xuat xu VN
select * from donhang 
where donhang.xuatxu = 'VN'

-- Tong gia ban cho tung mat hang:
alter table donhang
add tonggiaban float

update donhang set tonggiaban = donhang.soluong*donhang.giaban

select id_hoanghoa, tenmathang, sum(tonggiaban) as sum_mathang
from donhang
group by id_hoanghoa, tenmathang
having sum(tonggiaban) > 0
order by sum_mathang asc