数据库SQL问题

Question

1.创建数据库 shopping

2.在此数据库中建表:
(1)参数表：param [param_id(自增长主键), 参数名称(param_name)]
向表中增加数据：[日用品，电器，食品，其它]
(2)商品表：goods[goods_id(自增长主键), 商品名称(goods_name varchar)，商品类型(goods_type, int, 外键，参考参数表中的param_id)，单价(price float), 库中原始数量(init_num int), 备注]
向表中增加两条数据
(3)商品出库表: goods_out[goods_out_id(自增长主键), 商品（外键），出库数量， 出库时间，备注]

3. 查询出所有的商品信息

4. 查询所有 日用品 的商品信息

5. 查询所有从来没有出库的商品的信息

6. 查询出目前所的商品的库存数量

7. 查询出出库数量最多的商品信息
给点好使的啊

Answer 1

--1.创建数据库 shopping
create database shopping
go

use shopping
go

--(1)创建参数表：param [param_id(自增长主键), 参数名称(param_name)]
create table param
(
param_id int primary key identity(1,1),
param_name varchar(20),
)
go

--创建商品表：goods[,goods_id(自增长主键) 商品名称(goods_name varchar)，商品类型(goods_type, int, 外键，参考参数表中的param_id)，单价(price float), 库中原始数量(init_num int), 备注]
create table goods
(
goods_id int primary key identity(1,1),
goods_name varchar(20),
goods_type int foreign key (goods_type) references param(param_id),
price float,
init_num int ,
remark varchar(50)
)
go

--创建商品出库表: goods_out[goods_out_id(自增长主键), 商品（外键），出库数量， 出库时间，备注]
create table goods_out
(
goods_out_id int primary key identity(1,1),
goods int not null foreign key (goods) references goods(goods_id),
goods_out int default 0 ,
out_time datetime default getdate(),
remark varchar(50)
)
go

--向参数表添加信息
insert into param(param_name) values ('日用品')
insert into param values ('电器')
insert into param values ('食品')
insert into param values ('其他')
go

--向商品表添加信息
insert into goods values ('硬盘',2,320.0,200,'这是硬盘')
insert into goods values ('牙膏',1,12.2,100,'这是牙膏')
insert into goods values ('巧克力',3 ,2.9, 300,'这是巧克力')
insert into goods values ('钢笔',4,30.1,200,'这是钢笔')
insert into goods values ('毛笔',4,30.1,120,'这是毛笔')
go

--向商品出库表添加信息
insert into goods_out values (1,20,'2009-01-21','买了')
insert into goods_out values (2,32,'2009-01-21','买了')
insert into goods_out values (3,11,'2009-01-21','买了')
go

--查询三张表
select * from param
select * from goods
select * from goods_out
go

--查询出所有的商品信息
select * from param ,goods,goods_out
where goods.goods_id *= goods_out.goods
and goods.goods_type *= param.param_id
go

--查询所有 日用品 的商品信息
select * from param ,goods,goods_out
where param.param_name = '日用品'
and goods.goods_id *= goods_out.goods
and goods.goods_type = param.param_id
go

--查询所有从来没有出库的商品的信息
select * from goods left join param
on goods.goods_type = param.param_Id
where
goods.goods_id not in
(
select goods from goods_out
)
go

--查询出目前所有的商品的库存数量
select goods_name, goods.init_num - a.out_num, goods.init_num, a.out_num
from goods, (select goods, sum(goods_out) out_num from goods_out group by goods) a
where goods.goods_id *= a.goods
go

--查询出出库数量最多的商品信息
select goods.*, param.param_name from
(select max(out_num) max_num
from (select goods, sum(goods_out) out_num from goods_out group by goods) a) b,
(select goods, sum(goods_out) out_num from goods_out group by goods) c,
goods,
param
where b.max_num = c.out_num
and goods.goods_Id = c.goods
and goods.goods_type = param.param_id

Answer 2

自己写的:
1. Create DataBase shopping
USE shopping --如果只是在这题中的话,这句可以不要.
2.
⑴ Create Table param
(
param_id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
param_name VARCHAR(30)
)
insert into param VALUES('日用品')
insert into param VALUES('电器')
insert into param VALUES('食品')
insert into param VALUES('其它')
⑵ Create Table goods
(
goods_id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
goods_name Varchar(30),
goods_type INT FOREIGN KEY REFERENCES param ( param_id ),
price Float,
init_num INT,
Remark NVARCHAR(300)
)
insert into goods VALUES('冰箱',2,100,0,'')
insert into goods VALUES('话梅',3,3,0,'')
⑶ Create Table goods_out
(
goods_out_id INT IDENTITY(1,1) NOT NULL PRIMARY KEY ,
goods_id INT FOREIGN KEY REFERENCES goods ( goods_id ),
OutQty FLOAT,
OutDate DATETIME,
Remark NVARCHAR(300)
)

3. SELECT * FROM goods
4. SELECT A.* FROM goods A LEFT JOIN param B ON A.goods_type=B.param_id WHERE B.param_name='日用品'
5. Select * From Goods WHERE goods_id Not In (Select goods_id From goods_out)
6. Select A.goods_name,ISNULL(A.init_num-B.OutQty,0) As [库存数量] FROM goods A
Left Join (Select goods_id,ISNULL(Sum(OutQty),0) As OutQty From goods_out GROUP BY goods_id) B ON A.goods_id=B.goods_id
7. Select A.* FROM goods A
INNER Join (Select goods_id,MAX(OutQty) As OutQty From goods_out GROUP BY goods_id) B ON A.goods_id=B.goods_id