By GokiSoft.com| 21:35 15/12/2023|
SQL Server/MySQL

SQL - Thiết kế CSDL quản lý bán hàng - Lập trình SQL Server

Yêu cầu.

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ảng2. 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

->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 ra4. Liệt kê các đơn hàng được bán ra có xuất xứ Việt Nam 5. Thống kê tổng giá bán được cho từng mặt hàng.

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]
Hoàng Thái Sơn

2021-04-24 09:52:26

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 vn


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)


create proc proc_view_sum

@product_ nvarchar(10),

@tong int output

as begin

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

from Product, Sell

where Product.id = Sell.id_product

and Product.name = @product_

group by Product.name, Product.price_out

end

declare @count int

exec proc_view_sum 'noi com', @tong = @count output

print N'Tong gia tri: ' + CONVERT(NVARCHAR(10), @count)

Vũ Ngọc Văn [community,C2010L]
Vũ Ngọc Văn

2021-04-22 12:45:53



create database db0422_quanLyBanHang
use db0422_quanLyBanHang

create table hangHoa (
	id int primary key identity(1,1),
	tenMatHang nvarchar(50),
	nhaSanXuat nvarchar(50),
	xuatXu nvarchar(50),
	giaNhap int,
	giaBan int,
	ngaySanXuat date
)

insert into hangHoa
values
	('Sua Tuoi', 'Vinamilk', 'Viet Nam', 30000, 50000, '2021-04-20'),
	('Sua Dac', 'Vinamilk', 'Viet Nam', 20000, 30000, '2021-04-15'),
	('Tam Tre Em', 'Chicco', 'Trung Quoc', 100000, 200000, '2020-04-20'),
	('Goi Tre Em', 'Chicco', 'Trung Quoc', 70000, 150000, '2020-04-20'),
	('Sua Tre Em', 'Meiji', 'Nhat Ban', 200000, 300000, '2021-03-20'),
	('Banh Tre Em', 'Meiji', 'Nhat Ban', 300000, 500000, '2021-02-20'),
	('Tay Rua Bon Cau', 'Duck', 'My', 100000, 200000, '2020-04-20'),
	('Bot Thong Cong', 'Lorem', 'Duc', 100000, 200000, '2020-05-20'),
	('Nuoc Rua Bat', 'Lorem Ame', 'My', 50000, 100000, '2021-03-20'),
	('Bia', 'Lorem Bia', 'Duc', 30000, 50000, '2021-03-20')

create table banHang (
	id int primary key identity(1,1),
	id_hangHoa int foreign key references hangHoa(id),
	chuThich nvarchar(500),
	ngayBan date,
	soLuong int
)

insert into banHang
values
	(7, '', '2021-05-22', 20),
	(6, '', '2021-05-20', 10),
	(5, '', '2021-05-19', 30),
	(5, 'Lorem ipsum', '2021-05-15', 2),
	(3, '', '2021-05-23', 5),
	(2, '', '2021-05-01', 9),
	(2, '', '2021-04-22', 2),
	(8, 'Day la ghi chu', '2021-05-22', 15),
	(10, '', '2021-05-23', 30),
	(10, '', '2021-05-17', 40)

select hangHoa.id, hangHoa.tenMatHang, hangHoa.xuatXu, banHang.soLuong, banHang.ngayBan
from hangHoa left join banHang on hangHoa.id = banHang.id_hangHoa
order by hangHoa.id

select hangHoa.id, hangHoa.tenMatHang, hangHoa.xuatXu, banHang.soLuong, banHang.ngayBan
from hangHoa left join banHang on hangHoa.id = banHang.id_hangHoa
where hangHoa.xuatXu like '%Viet%Nam%'
order by hangHoa.id


select hangHoa.id, hangHoa.tenMatHang, hangHoa.xuatXu, sum(banHang.soLuong) 'tongSoLuongDaBan'
from hangHoa left join banHang on hangHoa.id = banHang.id_hangHoa
group by hangHoa.id, hangHoa.tenMatHang, hangHoa.xuatXu
order by hangHoa.id

select hangHoa.id, hangHoa.tenMatHang, hangHoa.xuatXu, sum(banHang.soLuong) 'tongSoLuongDaBan', hangHoa.giaBan - hangHoa.giaNhap 'tienLai', sum(banHang.soLuong * (hangHoa.giaBan - hangHoa.giaNhap)) 'tongTienLai'
from hangHoa left join banHang on hangHoa.id = banHang.id_hangHoa
group by hangHoa.id, hangHoa.tenMatHang, hangHoa.xuatXu, hangHoa.giaBan - hangHoa.giaNhap
order by hangHoa.id


Đào Mạnh Dũng [C2010L]
Đào Mạnh Dũng

2021-04-06 14:27:02

create database banHang


use banHang




create table item (


id int primary key identity (1,1),

name nvarchar(30),

make_by nvarchar(30),

make_in nvarchar(20),

price_in money,

price_out money,

day_create date

)


create table sell (

id int primary key identity (1,1),

id_item int,

note nvarchar(300),

day_sell date,

soLuong int,

FOREIGN KEY (id_item) REFERENCES item (id)

)



insert into item (name,make_by,make_in,price_in,price_out,day_create)

values

('tofu','tofudo','china',1,10,'2020-02-19'),

('coconul','hawal','japan',2,100,'2020-02-19'),

('litchi','hawal','japan',1,10,'2020-02-19'),

('cona','doli','japan',1,1000,'2020-02-19'),

('gà','trại','china',0,10000,'2020-02-19'),

('grape','hawal','japan',1,10,'2020-02-19'),

('mangoes','hawal','japan',1,10,'2020-02-19'),

('bamboo','forest','japan',1,10,'2020-02-19')




 select * from item

 

 id int primary key identity (1,1),

id_item int,

note nvarchar(300),

day_sell date,

soLuong int,


 

insert into sell (id_item,note,day_sell,soLuong)

values

(2,'adasdasdasd','2020-02-19',5),

(8,'sdasdasdasdasdas','2020-02-19',9),

(9,'dsdasdasd','2020-02-19',78),

(1,'ádasdasdasda','2020-02-19',44),

(1,'đâsdadasdasdasd','2020-02-19',4),

(5,'dâsdadadasaa','2020-02-19',6),

(7,'âddsdadadadasdadadadadas','2020-02-19',54),

(9,'ádasdada','2020-02-19',444)

 select * from item

 --cau 3

 select * from sell


 -- cau 4

  select * from sell,item

  where sell.id_item=item.id_item

  and item.make_in='vn'


  --cau 5


select item.name, sum(item.price_out * sell.soLuong) 'Tong'

from sell left join item on sell.id_item = item.id_item

group by item.name


















Nguyễn Hùng Phương [community,C2010L]
Nguyễn Hùng Phương

2021-04-06 14:16:26



create table hang_hoa(
	id int primary key identity(1,1),
	ten_mat_hang nvarchar(50),
	nha_san_xuat nvarchar(50),
	xuat_xu nvarchar(50),
	gia_nhap float,
	gia_ban float,
	ngay_san_xuat date
)
create table ban_hang(
	id_don_hang int primary key identity(1,1),
	id_hang_hoa int foreign key references hang_hoa(id),
	chu_thich nvarchar(100),
	ngay_ban date,
	so_luong int
)
---Them 10 ban ghi vao bang hang hoa
insert into hang_hoa (ten_mat_hang,nha_san_xuat,xuat_xu,gia_nhap,gia_ban,ngay_san_xuat)values
('Coca','Aptech','Viet Nam',5500,10000,'2021-04-06'),
('Pepsi','Fpt','Nhat Ban',6000,12500,'2021-04-06'),
('Lavi','Oop','Hàn Quốc',4500,5000,'2021-04-06'),
('Cosi','Kft','Trung Quốc',45800,63000,'2021-04-06'),
('Banh keo','Lqs','Viet Nam',5000,90000,'2021-04-06'),
('Fanta','Poi','Đuc',3800,6500,'2021-04-06'),
('Vina','Kka','Usa',8900,15000,'2021-04-06'),
('Sprite','Klp','Viet Nam',5500,10000,'2021-04-06'),
('Demi','Mns','Y',3900,8500,'2021-04-06'),
('Sliver 90','Ado','Viet Nam',2200,14000,'2021-04-06')
---Them 10 ban ghi vao bang ban hang
insert into ban_hang (id_hang_hoa,chu_thich,ngay_ban,so_luong) values
(01,'oqjs','2021-09-24',2928),
(02,'oqjs','2021-09-24',392),
(03,'sks','2021-10-02',90),
(04,'ods','2021-04-14',100),
(05,'osa','2021-06-03',50),
(06,'sss','2021-09-12',150),
(07,'aoq','2021-08-16',3002),
(08,'ods','2021-05-30',999),
(09,'ossa','2021-12-12',12019),
(010,'ska','2021-09-29',8212)
---Liet ke tat ca cac don hang duoc ban ra
select * from hang_hoa
select * from ban_hang
select ban_hang.id_don_hang, hang_hoa.ten_mat_hang, hang_hoa.gia_ban, ban_hang.so_luong, ban_hang.ngay_ban, hang_hoa.xuat_xu
from ban_hang, hang_hoa 
where ban_hang.id_hang_hoa=hang_hoa.id
---Liet ke cac don hang co cuat xu Viet nam
select ban_hang.id_don_hang, hang_hoa.ten_mat_hang, hang_hoa.gia_ban, ban_hang.so_luong, ban_hang.ngay_ban, hang_hoa.xuat_xu
from ban_hang, hang_hoa 
where ban_hang.id_hang_hoa=hang_hoa.id and hang_hoa.xuat_xu='Viet Nam'
---Thong ke tong gia ban cua cac mat hang
select hang_hoa.ten_mat_hang, (hang_hoa.gia_ban*count(ban_hang.so_luong))'Tong gia ban'
from ban_hang, hang_hoa 
where ban_hang.id_hang_hoa=hang_hoa.id
group by hang_hoa.ten_mat_hang, hang_hoa.gia_ban


nguyen hoang viet [community,C2009I]
nguyen hoang viet

2021-01-23 09:53:36

create table BangHangHoa (

id int primary key identity(1,1),

ten_mat_hang nvarchar(50),

nha_san_xuat nvarchar(50),

xuat_xu nvarchar(30),

gia_nhap int,

gia_ban int,

check (gia_nhap>0),

check (gia_ban>0),

ngay_san_xuat date

)

insert into BangHangHoa(ten_mat_hang, nha_san_xuat, xuat_xu, gia_nhap, gia_ban, ngay_san_xuat)

values

('Coca-cola', 'Coca-cola Company', 'America' ,10000 ,12000 ,'2020-10-29'),

('Pepsi', 'PepsiCo', 'America', 11000, 13000, '2020-11-14'),

('Fanta', 'Coca-cola Company', 'America', 9000, 10000, '2021-01-10'),

('Sprite', 'Coca-cola Company', 'America', 8000, 10000, '2020-12-22'),

('Mirinda', 'PepsiCo', 'America', 7000, 9000,'2020-11-11'),

('Mountain Dew', 'PepsiCo', 'America', 9000, 11000, '2020-12-11'),

('Dr Pepper', 'Dr Pepper', 'America', 5000, 6000, '2020-10-11'),

('Crush', 'Dr Pepper', 'America', 6000, 7000, '2020-11-17'),

('7 Up', 'Dr Pepper', 'America', 8000, 10000, '2020-08-03'),

('Maaza', 'Coca-cola Company', 'America', 8500, 11000, '2020-09-09')

select * from BangHangHoa

alter table BangHangHoa

alter row

create table BangBanHang(

id int primary key identity(1,1),

id_hang_hoa int references dbo.BangHangHoa(id),

chu_thich nvarchar(200),

ngay_ban date,

so_luong int,

check (so_luong>0)

)

insert into BangBanHang(id_hang_hoa, chu_thich, ngay_ban, so_luong)

values

(4, 'none', '2021-2-2', 100),

(5, 'none', '2021-2-3', 50),

(1, 'none', '2021-2-4', 1000),

(1, 'none', '2021-2-5', 500),

(3, 'none', '2021-2-6', 150),

(8, 'none', '2021-2-7', 100),

(1, 'none', '2021-2-8', 1000),

(2, 'none', '2021-2-9', 1000),

(3, 'none', '2021-2-10', 400),

(2, 'none', '2021-2-11', 600)


select * from BangBanHang

select BangHangHoa.ten_mat_hang, BangHangHoa.nha_san_xuat, BangHangHoa.xuat_xu, BangBanHang.chu_thich, BangBanHang.ngay_ban, BangBanHang.so_luong from BangBanHang, BangHangHoa where BangBanHang.id_hang_hoa = BangHangHoa.id

select BangHangHoa.ten_mat_hang, BangHangHoa.nha_san_xuat, BangHangHoa.xuat_xu, BangBanHang.chu_thich, BangBanHang.ngay_ban, BangBanHang.so_luong from BangBanHang, BangHangHoa where BangBanHang.id_hang_hoa = BangHangHoa.id and BangHangHoa.xuat_xu = 'Viet Nam'


select sum(BangBanHang.so_luong) 'Tong so luong ban', BangHangHoa.ten_mat_hang, BangBanHang.id_hang_hoa into Thong_ke

from BangHangHoa inner join BangBanHang on BangBanHang.id_hang_hoa=BangHangHoa.id

group by BangHangHoa.ten_mat_hang, BangBanHang.id_hang_hoa 


select Thong_ke.[Tong so luong ban], BangHangHoa.ten_mat_hang, BangHangHoa.gia_ban, (BangHangHoa.gia_ban*Thong_ke.[Tong so luong ban]) 'Tong gia ban'
from Thong_ke inner join BangHangHoa on Thong_ke.ten_mat_hang=BangHangHoa.ten_mat_hang 

Trinh Huy Hung [community,C2009I]
Trinh Huy Hung

2021-01-23 09:07:17



create table HangHoa(
	Id int identity(100,1) primary key,
	TenHang nvarchar(100),
	NhaSanXuat nvarchar(100),
	XuatXu nvarchar(50),
	GiaNhap float,
	GiaBan float,
	NgaySanXuat date
)

insert into HangHoa(TenHang ,NhaSanXuat, XuatXu, GiaNhap, GiaBan, NgaySanXuat)
values
('Lap Top Z', 'ASUS', 'Trung Quoc', 15000000, 20000000, '2021-01-21'),
('Lap Top Y', 'ASUS', 'Trung Quoc', 25000000, 30000000, '2021-01-22')

select * from HangHoa

insert into HangHoa(TenHang ,NhaSanXuat, XuatXu, GiaNhap, GiaBan, NgaySanXuat)
values
('Lap Top H', 'Acer', 'Trung Quoc', 29000000, 34000000, '2021-01-23'),
('Ban Phim Co Logitech', 'Logitech', 'Trung Quoc', 500000, 700000, '2021-01-22'),
('Ban Phim Co ASUS', 'ASUS', 'Trung Quoc', 200000, 299000, '2021-01-21'),
('Chuot Gaming Predator', 'Acer', 'Trung Quoc', 1350000, 1499000, '2021-01-23'),
('Chuot Gaming G102', 'logitech', 'Trung Quoc', 200000, 300000, '2021-01-11'),
('Tai Nghe Asus', 'ASUS', 'Trung Quoc', 500000, 650000, '2021-01-11'),
('Tai Nghe Razer', 'Razer', 'Trung Quoc', 700000, 900000, '2021-01-09'),
('PS5', 'Sony', 'Nhat Ban', 20000000, 25000000, '2021-01-11'),
('PS4', 'Sony', 'Nhat Ban', 8000000, 12000000, '2020-10-21'),
('Man Hinh LG', 'LG', 'Han Quoc', 8000000, 11000000, '2021-01-22')

create table BanHang(
	Id_DonHang int identity(10001,3) primary key,
	Id_Hanghoa int foreign key references Hanghoa(Id),
	Note nvarchar(355),
	NgayBan date,
	Soluong int,
)

insert into BanHang(Id_Hanghoa, Note, NgayBan, Soluong)
values
(102,'Tra Gop','2021-01-28',1),
(103,'Khong co','2021-01-27',1),
(102,'Tra Gop','2021-01-31',1),
(103,'Khong co','2021-01-31',1),
(104,'Khong co','2021-01-25',1),
(105,'Khong co','2021-01-31',1),
(106,'Khong co','2021-01-26',1),
(107,'Khong co','2021-01-27',1),
(108,'Khong co','2021-01-31',1),
(109,'Tra Gop','2021-02-15',1),
(110,'Tra Gop','2021-01-15',1),
(111,'Khong co','2021-01-31',1)

select * from HangHoa
select * from BanHang

select BanHang.Id_DonHang, HangHoa.TenHang, HangHoa.GiaBan, BanHang.Soluong, BanHang.NgayBan, HangHoa.XuatXu
from BanHang, HangHoa 
where BanHang.Id_Hanghoa=HangHoa.Id

select BanHang.Id_DonHang, HangHoa.TenHang, HangHoa.GiaBan, BanHang.Soluong, BanHang.NgayBan, HangHoa.XuatXu
from BanHang, HangHoa 
where BanHang.Id_Hanghoa=HangHoa.Id and HangHoa.XuatXu='Nhat Ban'

select HangHoa.TenHang, (HangHoa.GiaBan*count(BanHang.Soluong))'Tong Gia Ban'
from BanHang, HangHoa 
where BanHang.Id_Hanghoa=HangHoa.Id
group by HangHoa.TenHang, HangHoa.GiaBan


Vũ Trung Kiên [C2009I]
Vũ Trung Kiên

2021-01-23 08:59:29


#QuanLyBanHang.sql


create table HangHoa (
	id int identity(1,1) primary key,
	TenHang nvarchar(50),
	NhaSX nvarchar(50),
	XuatXu nvarchar(50),
	GiaNhap float,
	GiaBan float,
	NgaySX date
)

insert into HangHoa(TenHang, NhaSX, XuatXu, GiaNhap, GiaBan, NgaySX)
values
('aaa', 'aa', 'VN', '200.000', '250.000', '2021-10-10'),
('bbb', 'bb', 'EN', '300.000', '350.000', '2020-10-11'),
('ccc', 'cc', 'AM', '400.000', '450.000', '2020-9-10'),
('ddd', 'dd', 'SG', '500.000', '550.000', '2020-10-20'),
('eee', 'ee', 'VN', '600.000', '650.000', '2019-10-10'),
('aaaa', 'aaa', 'VN', '250.000', '270.000', '2020-10-10'),
('bbbb', 'bbb', 'EN', '350.000', '370.000', '2020-12-10'),
('cccc', 'ccc', 'AM', '450.000', '470.000', '2020-8-10'),
('dddd', 'ddd', 'SG', '550.000', '570.000', '2018-10-10'),
('eeee', 'eee', 'VN', '650.000', '670.000', '2020-11-10')

create table BanHang (
	idDonHang int identity(1,1) primary key,
	id int,
	note nvarchar(200),
	NgayBan date,
	Soluong int
)	

alter table BanHang
add constraint fk_id foreign key (id) references HangHoa (id)

insert into BanHang(id, note, NgayBan, Soluong)
values
(5, 'asdf', '2020-11-12', 3),
(2, 'sad', '2021-1-12', 3),
(7, 'dasd', '2020-10-2', 3),
(4, 'dasdsd', '2018-12-12', 3),
(3, 'aaaa', '2020-12-12', 3),
(10, 'sssss', '2020-12-12', 3),
(1, 'fffff', '2020-12-10', 3),
(8, 'asddddf', '2020-6-12', 3),
(9, 'asdssf', '2020-12-12', 3),
(6, 'aasdfss', '2020-12-5', 3)

select BanHang.idDonHang, HangHoa.id, HangHoa.TenHang, HangHoa.NhaSX, HangHoa.XuatXu, HangHoa.GiaNhap, HangHoa.GiaBan, HangHoa.NgaySX, BanHang.NgayBan, BanHang.Soluong, BanHang.note 
	from BanHang, HangHoa
		where BanHang.id = HangHoa.id
select BanHang.idDonHang, HangHoa.id, HangHoa.TenHang, HangHoa.NhaSX, HangHoa.XuatXu, HangHoa.GiaNhap, HangHoa.GiaBan, HangHoa.NgaySX, BanHang.NgayBan, BanHang.Soluong, BanHang.note 
	from BanHang, HangHoa
		where BanHang.id = HangHoa.id
			and HangHoa.XuatXu = 'VN'
select HangHoa.id, HangHoa.TenHang, HangHoa.GiaBan, BanHang.Soluong, HangHoa.GiaBan*BanHang.Soluong as Tong
	from HangHoa, BanHang
		where BanHang.id = HangHoa.id


Nguyễn đình quân [T2008A]
Nguyễn đình quân

2020-11-30 08:08:44



----bán hàng hóa----
create table hang_hoa(
id int identity (1,1) primary key,
name nvarchar (200),
producer nvarchar (200),
madein nvarchar (100),
import_price float ,
price float 
date 
)
insert into hang hoa (name,producer,madein,impost_price,price,date),
values
('ao','2021-12-1','vietnam','150.000vnd','200.000vnd','2020-1-12'),
('quan','2021-12-1','vietnam','150.000vnd','200.000vnd','2020-1-12'),
('vay','2021-12-1','vietnam','150.000vnd','200.000vnd','2020-1-12'),
('giay','2021-12-1','vietnam','150.000vnd','200.000vnd','2020-1-12'),
('dep','2021-12-1','vietnam','150.000vnd','200.000vnd','2020-1-12'),
('ABC','2021-12-1','vietnam','150.000vnd','200.000vnd','2020-1-12'),
('XYZ','2021-12-1','vietnam','150.000vnd','200.000vnd','2020-1-12'),
('DFG','2021-12-1','vietnam','150.000vnd','200.000vnd','2020-1-12'),
('QWE','2021-12-1','vietnam','150.000vnd','200.000vnd','2020-1-12'),
('ZXC','2021-12-1','vietnam','150.000vnd','200.000vnd','2020-1-12'),
SELECT * FROM dbo.product

INSERT INTO dbo.orders
        (id_product, note, created_at, num )
VALUES
(2,N'OKOK',GETDATE(),2),
(1,N'OKOK',GETDATE(),21),
(6,N'OKOK',GETDATE(),5),
(2,N'OKOK',GETDATE(),12),
(1,N'OKOK',GETDATE(),27),
(2,N'OKOK',GETDATE(),28),
(9,N'OKOK',GETDATE(),34),
(2,N'OKOK',GETDATE(),21),
(5,N'OKOK',GETDATE(),20)

SELECT * FROM dbo.orders
-- Hien thi thong tin don hang dc ban ra: id san pham, tieu de, ghi chu, so luong
SELECT dbo.product.id, dbo.product.title, dbo.orders.note, dbo.orders.num
FROM dbo.product, dbo.orders
WHERE dbo.product.id = dbo.orders.id_product
ORDER BY dbo.product.id ASC
-- -- Hien thi thong tin don hang dc ban ra: id san pham, tieu de, ghi chu, so luong (tinh tong so luong ban ra)

SELECT dbo.product.id, dbo.product.title, dbo.orders.num, dbo.orders.note
FROM dbo.product, dbo.orders
WHERE dbo.product.id = dbo.orders.id_product
ORDER BY dbo.product.id ASC

SELECT dbo.product.id, dbo.product.title, dbo.orders.note, SUM(dbo.orders.num) AS 'Total'
FROM dbo.product, dbo.orders
WHERE dbo.product.id = dbo.orders.id_product
GROUP BY dbo.product.id, dbo.product.title, dbo.orders.note
ORDER BY dbo.product.id ASC

SELECT dbo.product.id, dbo.product.title, SUM(dbo.orders.num)
FROM dbo.product, dbo.orders
WHERE dbo.product.id = dbo.orders.id_product
GROUP BY dbo.product.id, dbo.product.title
ORDER BY dbo.product.id ASC

-- -- Hien thi thong tin don hang dc ban ra: id san pham, tieu de, MADEIN, ghi chu, so luong (tinh tong so luong ban ra)
SELECT dbo.product.id, dbo.product.title, dbo.product.madein, SUM(dbo.orders.num)
FROM dbo.product, dbo.orders
WHERE dbo.product.id = dbo.orders.id_product
GROUP BY dbo.product.id, dbo.product.title, dbo.product.madein
ORDER BY dbo.product.id ASC

SELECT dbo.product.id, dbo.product.title, dbo.product.madein, SUM(dbo.orders.num)
FROM dbo.product, dbo.orders
WHERE dbo.product.id = dbo.orders.id_product
	AND dbo.product.madein = 'Viet Nam'
GROUP BY dbo.product.id, dbo.product.title, dbo.product.madein
ORDER BY dbo.product.id ASC

SELECT dbo.product.id, dbo.product.title, dbo.orders.note, dbo.orders.num, dbo.product.madein
FROM dbo.product, dbo.orders
WHERE dbo.product.id = dbo.orders.id_product
	AND dbo.product.madein = 'Viet Nam'
ORDER BY dbo.product.id ASC


Nguyễn Tiến Đạt [T2008A]
Nguyễn Tiến Đạt

2020-11-30 07:22:01



-- create database
CREATE DATABASE bt1776

-- active database
USE bt1776

-- create tables
CREATE TABLE product (
	id INT PRIMARY KEY IDENTITY(1,1),
	title NVARCHAR(150),
	manufacturer_name NVARCHAR(100),
	madein NVARCHAR(50),
	buy_price FLOAT,
	sell_price FLOAT,
	created_at DATE
)

CREATE TABLE orders (
	id INT PRIMARY KEY IDENTITY(1,1),
	id_product INT REFERENCES dbo.product(id),
	note NVARCHAR(500),
	created_at DATE,
	num INT
)

-- insert data
INSERT INTO product( title ,manufacturer_name ,madein ,buy_price ,sell_price ,created_at)
VALUES
( N'san pham 1' ,N'gokisoft' ,N'Viet Nam' ,20.0 ,50.0 ,GETDATE()),
( N'san pham 2' ,N'gokisoft' ,N'Viet Nam' ,20.0 ,50.0 ,GETDATE()),
( N'san pham 3' ,N'gokisoft' ,N'Viet Nam' ,20.0 ,50.0 ,GETDATE()),
( N'san pham 4' ,N'gokisoft' ,N'Viet Nam' ,20.0 ,50.0 ,GETDATE()),
( N'san pham 5' ,N'gokisoft' ,N'Viet Nam' ,20.0 ,50.0 ,GETDATE()),
( N'san pham 6' ,N'gokisoft' ,N'Viet Nam' ,20.0 ,50.0 ,GETDATE()),
( N'san pham 7' ,N'gokisoft' ,N'Viet Nam' ,20.0 ,50.0 ,GETDATE()),
( N'san pham 8' ,N'gokisoft' ,N'Viet Nam' ,20.0 ,50.0 ,GETDATE()),
( N'san pham 9' ,N'gokisoft' ,N'Viet Nam' ,20.0 ,50.0 ,GETDATE()),
( N'san pham 10' ,N'gokisoft' ,N'Viet Nam' ,20.0 ,50.0 ,GETDATE())

SELECT * FROM dbo.product

INSERT INTO dbo.orders
        (id_product, note, created_at, num )
VALUES
(2,N'OKOK',GETDATE(),2),
(1,N'OKOK',GETDATE(),21),
(6,N'OKOK',GETDATE(),5),
(2,N'OKOK',GETDATE(),12),
(1,N'OKOK',GETDATE(),27),
(2,N'OKOK',GETDATE(),28),
(9,N'OKOK',GETDATE(),34),
(2,N'OKOK',GETDATE(),21),
(5,N'OKOK',GETDATE(),20)

SELECT * FROM dbo.orders
-- Hien thi thong tin don hang dc ban ra: id san pham, tieu de, ghi chu, so luong
SELECT dbo.product.id, dbo.product.title, dbo.orders.note, dbo.orders.num
FROM dbo.product, dbo.orders
WHERE dbo.product.id = dbo.orders.id_product
ORDER BY dbo.product.id ASC
-- -- Hien thi thong tin don hang dc ban ra: id san pham, tieu de, ghi chu, so luong (tinh tong so luong ban ra)

SELECT dbo.product.id, dbo.product.title, dbo.orders.num, dbo.orders.note
FROM dbo.product, dbo.orders
WHERE dbo.product.id = dbo.orders.id_product
ORDER BY dbo.product.id ASC

SELECT dbo.product.id, dbo.product.title, dbo.orders.note, SUM(dbo.orders.num) AS 'Total'
FROM dbo.product, dbo.orders
WHERE dbo.product.id = dbo.orders.id_product
GROUP BY dbo.product.id, dbo.product.title, dbo.orders.note
ORDER BY dbo.product.id ASC

SELECT dbo.product.id, dbo.product.title, SUM(dbo.orders.num)
FROM dbo.product, dbo.orders
WHERE dbo.product.id = dbo.orders.id_product
GROUP BY dbo.product.id, dbo.product.title
ORDER BY dbo.product.id ASC

-- -- Hien thi thong tin don hang dc ban ra: id san pham, tieu de, MADEIN, ghi chu, so luong (tinh tong so luong ban ra)
SELECT dbo.product.id, dbo.product.title, dbo.product.madein, SUM(dbo.orders.num)
FROM dbo.product, dbo.orders
WHERE dbo.product.id = dbo.orders.id_product
GROUP BY dbo.product.id, dbo.product.title, dbo.product.madein
ORDER BY dbo.product.id ASC

SELECT dbo.product.id, dbo.product.title, dbo.product.madein, SUM(dbo.orders.num)
FROM dbo.product, dbo.orders
WHERE dbo.product.id = dbo.orders.id_product
	AND dbo.product.madein = 'Viet Nam'
GROUP BY dbo.product.id, dbo.product.title, dbo.product.madein
ORDER BY dbo.product.id ASC

SELECT dbo.product.id, dbo.product.title, dbo.orders.note, dbo.orders.num, dbo.product.madein
FROM dbo.product, dbo.orders
WHERE dbo.product.id = dbo.orders.id_product
	AND dbo.product.madein = 'Viet Nam'
ORDER BY dbo.product.id ASC


Đặng Trần Nhật Minh [T2008A]
Đặng Trần Nhật Minh

2020-11-30 06:58:08



create database Sale
use Sale

create table product (
	id int primary key identity(1, 1),
	name nvarchar(200),
	manufacturer nvarchar(200),
	madein nvarchar(200),  
	import_price nvarchar(200),
	sale_price nvarchar(200),
	manu_date date,
)
go

insert into product(name, manufacturer, madein, import_price, sale_price, manu_date)
values
('aaa', 'aa', 'VN', '200.000', '250.000', '2020-10-10'),
('bbb', 'bb', 'EN', '300.000', '350.000', '2020-10-10'),
('ccc', 'cc', 'AM', '400.000', '450.000', '2020-10-10'),
('ddd', 'dd', 'SG', '500.000', '550.000', '2020-10-10'),
('eee', 'ee', 'VN', '600.000', '650.000', '2020-10-10'),
('aaaa', 'aaa', 'VN', '250.000', '270.000', '2020-10-10'),
('bbbb', 'bbb', 'EN', '350.000', '370.000', '2020-10-10'),
('cccc', 'ccc', 'AM', '450.000', '470.000', '2020-10-10'),
('dddd', 'ddd', 'SG', '550.000', '570.000', '2020-10-10'),
('eeee', 'eee', 'VN', '650.000', '670.000', '2020-10-10')

create table sale (
	id int primary key identity(1, 1),
	id_hanghoa int foreign key references product(id),
	chuthich text,
	sale_date date,
	quantity int,
) 
go

insert into sale(id_hanghoa, chuthich, sale_date, quantity)
values
(1, 'asdf', '2020-12-12', 3),
(2, 'sad', '2020-12-12', 3),
(3, 'dasd', '2020-12-12', 3),
(4, 'dasdsd', '2020-12-12', 3),
(5, 'aaaa', '2020-12-12', 3),
(6, 'sssss', '2020-12-12', 3),
(7, 'fffff', '2020-12-12', 3),
(8, 'asddddf', '2020-12-12', 3),
(9, 'asdssf', '2020-12-12', 3),
(10, 'aasdfss', '2020-12-12', 3)

select * from product
select * from sale

select product.id, name, manufacturer, madein, sale_price, manu_date, chuthich, sale_date, quantity from product, sale
where product.id = sale.id_hanghoa and product.madein = 'Vietnam'

alter table sale add total_price float

select sale.id, id_hanghoa, name, chuthich, sale_date, quantity, sale_price, total_price into don_hang from sale, product
where product.id = sale.id_hanghoa

select * from don_hang

update don_hang set total_price = quantity * sale_price


Đăng nhập để làm bài kiểm tra

Chưa có kết quả nào trước đó