MySQL上机考试题(习题-答案)

MySQL上机考试题(答案)
建表详情：
/*
-- Records of saler
-- ----------------------------
INSERT INTO `saler` V ALUES ('000061', '山东大德日用', '华中', '刘威华', ''); INSERT INTO `saler` V ALUES ('000145', '北京天成', '华北', '刘茂', '');
INSERT INTO `saler` V ALUES ('000165', '喜田有限公司', '华北', '王总', ''); INSERT INTO `saler` V ALUES ('001547', '丹尼斯', '华南', '胡总', '');
INSERT INTO `saler` V ALUES ('037102', '天景商贸', '华北', '王磊', '');
INSERT INTO `saler` V ALUES ('059741', '成建股份有限公司', '华中', '徐总', ''); INSERT INTO `saler` V ALUES ('087412', '沐阳集团', '西部', '鲁宣', '');
INSERT INTO `saler` V ALUES ('089412', '新建德', '华南', '赵总', '40025684562');
数据详情：
表名：pros 产品表
表名：saler 销售商表
表名：pro_sal 销售表
一、基于product数据库的三个表，用SQL语言完成以下查询
（1）查询所有产品的产品编号和销售额，并将销售额增加10%后设置别名为“增长额" 查询语句：select ps.`产品编号`,ps.`销售额`,ps.`销售额`*0.1 AS '增长额' from pro_sal ps
查询结果：
（2）查询“华中、华北、东南”地区的销售商名称和地区。

查询语句：select 销售商名称,地区from saler
where 地区in('华中','华北','东南');
查询结果：
（3）查找销售商名称的第二个字符是’建’并且只有三个字符的销售商编号和名称。

查询语句：
select s.`销售商编号`,s.`销售商名称` from saler s
where s.`销售商名称` like'_建_';
查询结果：
（4）查询有电话的销售商信息。

查询语句：
select * from saler where 电话is not null;
查询结果：
（5）查询销售额在2000到5000的并且数量大于100的产品编号和销售日期。

查询语句：
select ps.`产品编号`,ps.`销售日期` from pro_sal ps
where ps.`销售额` BETWEEN 2000 and 5000
and ps.`数量`>100;
查询结果：（本无结果，为达到效果另插入一条数据）
（6）求销售了产品的销售商总数。

查询语句：
select DISTINCT count(ps.`销售商编号`) as '销售商总数' from pro_sal ps
查询结果：
（7）求产品编号为“0001”的产品的平均销售额、最高销售额、最低销售额。

查询语句：
select A VG(ps.`销售额`) 平均, MAX(ps.`销售额`) 最高,MIN(ps.`销售额`) 最低from pro_sal ps
where ps.`产品编号`=0001;
查询结果：
（8）查询销售产品种类超过2类的销售商编号和种类数。

并按购买种类数从大到小排序。

查询语句：
select * from(
select ps.`销售商编号`,count(ps.`产品编号`) 销售种类from pro_sal ps
GROUP BY ps.`销售商编号`
ORDER BY COUNT(DISTINCT 产品编号) DESC) s
where s.`销售种类`>1;
查询结果：
（9）查询销售商的销售商编号和名称、销售的产品编号和数量。

查询语句：
select s.`销售商编号`,s.`销售商名称`,ps.`产品编号`,ps.`数量` from
saler s,pro_sal ps
WHERE s.`销售商编号`=ps.`销售商编号`;
查询结果：
（10）查询至少销售过1次“0001”号产品的销售商编号和购买次数，并按购买次数多少降序排列。

查询语句：
select ps.`销售商编号` ,COUNT(*) 购买次数
from pro_sal ps
where ps.`产品编号`=0001
GROUP BY ps.`销售商编号`
ORDER BY count(*) desc;
查询结果：
（11）查询与‘喜田有限公司’在同一地区的销售商名称，地区和负责人。

查询语句：
SELECT s.`销售商名称`,s.`地区`,s.`负责人` from saler s
where s.`地区` like(select 地区from saler WHERE `销售商名称`='喜田有限公司')
and s.`销售商名称` !='喜田有限公司';
查询结果：
（12）查询销售商的销售情况，要求包括销售了产品的销售商和没有销售的销售商，显示他们的销售商编号、销售商名称、产品编号、销售日期。

查询语句：
select s.`销售商编号`,s.`销售商名称`,ps.`产品编号`,ps.`销售日期`
from saler s,pro_sal ps
WHERE s.`销售商编号`=ps.`销售商编号`;
查询结果：
（13）查询销售额小于平均销售额的产品编号、产品名称和价格、销售额。

select p.`产品编号`,p.`产品名称`,p.`价格`,ps.`销售额`
from pros p,pro_sal ps
WHERE p.`产品编号`=ps.`产品编号`
and ps.`销售额`<(SELECT A VG(销售额) from pro_sal)
（14）查询没被销售商销售过的产品信息。

查询语句：
SELECT * from pros p
where p.`产品编号` not in(select ps.`产品编号` from pro_sal ps )
查询结果：
（15）查询销售了“0001”但没有销售“0002”号产品的销售商编号和产品编号。

查询语句
select ps.`销售商编号`,ps.`产品编号` from pro_sal ps
where ps.`产品编号`=0001
and ps.`销售商编号` not in (SELECT `销售商编号` from pro_sal where `产品编号`=0002)
查询结果
--------------------------------------------------------------------------------------------------------------------- 产品销售数据库中包含三个表，分别是产品表、销售商表和产品销售表。

产品表（产品编号，产品名称，价格，库存量）
销售商表（销售商编号，销售商名称，地区，负责人，电话）
产品销售表（销售日期，产品编号，销售商编号，数量，销售额）
产品表、销售商表和产品销售表的表结构如表1、2和3所示。

表1产品表结构
列名说明数据类型约束
产品编号产品唯一的产品编号字符串，长度为20 主键
产品名称产品的产品名称字符串，长度为50 非空值
表2销售商表结构
表3产品销售表结构
主键为：(销售日期, 产品编号,客户编号)
二、基于基于product数据库的三个表，完成以下操作(略)
*(1) 为销售商表的“地区”字段添加一个默认值约束”华中”，约束名为address (2) 向产品表插入一个新产品，（‘0006’，‘节能灯’，5 ，100）（3）把节能灯的价格增加2元。