操作2222222

1、 有一个关于商品供应及顾客订单的数据库。其中包括四个表，表中信息如下：

供应表apply（id、name、sid、price）

说明：id 供应厂家编号，name 供应厂家名称，sid 商品编号，price 商品价格。

 

顾客表customers（gid、name、address、balance）

说明：gid 顾客编号，address 地址，balance 余额。

 

订单表orders（sid、gid、date）

说明：sid 商品编号，gid 顾客编号，date 订单日期。

 

商品表goods（sid、name、count）

说明：sid 商品编号，name 商品名称，count 商品数量

 

写出SQL完成如下操作的SQL语句：

1)查询出2008-8-8这一天顾客的订单信息，要求包括顾客姓名、商品名称及订单日期.

2)将商品表中商品编号为'204'的商品名称更改为百事可乐

3)将顾客表上余额不足1000元的，将其订单日期延后10天

4) 删除订单表中商品编号为102的订单记录

5)将商品表中没有顾客订购的商品信息删除

学生答案：

得分：10 分

1.select customers.name, goods.name,orders.data

from customers,goods,orders

where customers.gid=orders.gid and orders.sid=goods.sid and date="2008-8-8"

 

2.update goods set name='百事可乐' where sid='204';

 

3.update orders set date=DATE_ADD(NOW(),INTERVAL 3 DAY)

where gid=(select gid from customers where balance<100)

 

4.delete from orders where sid='102';

 

2、 有一个关于商品供应及顾客订单的数据库。其中包括四个表，表中信息如下：

供应表apply（id、name、sid、price）

说明：id 供应厂家编号，name 供应厂家名称，sid 商品编号，price 商品价格。

 

顾客表customers（gid、name、address、balance）

说明：gid 顾客编号，address 地址，balance 余额。

 

订单表orders（sid、gid、date）

说明：sid 商品编号，gid 顾客编号，date 订单日期。

 

商品表goods（sid、name、count）

说明：sid 商品编号，name 商品名称，count 商品数量

 

1)从供应表中查询全体供应厂商的基本信息

2)从顾客表中查询地址在"长春"的顾客的顾客编号、顾客姓名及余额.

3)从商品表中查询以"可乐"两个字结尾的商品名称及数量，并按商品数量降序排序

4)从订单表中查询购买商品编号为"101"商品的顾客编号及订单日期.

5)向商品表中追加一条纪录(204,可口可乐,900）

6)从商品表中查询最多商品数量、最少商品数量及商品总数量的记录信息

学生答案：

得分：10 分

1)SELECT apply.id,apply.name,goods.name,goods.count,apply.price

FROM apply,goods

WHERE apply.sid=goods.sid;

 

2)SELECT gid,name,balance

FROM customers

WHERE address='长春';

 

3)SELECT name,count

FROM goods

WHERE name LIKE '%可乐'  ORDERBY count DESC;

 

4)SELECT gid,date FROM orders WHERE sid=101;

 

5)INSERT INTO goods VALUES(204, '可口可乐' ,900);

 

6)SELECT MAX(count),MIN(count),SUM(count)

FROM goods;

 

3、 已知选课管理系统Xkglxt包含如下数据表：

1）学生表: 学生id, 学号, 姓名, 专业, 年级, 姓名拼音缩写

2）课程表: 课程id, 课程名称, 学分  

3）选课表: 选课id, 学生id, 课程id, 成绩

 

请完成如下操作的SQL语句,表名和列名必须和上面的一致:

1、创建以上三个表的SQL语句；

2、创建一个存储过程（名字自定），实现选课表的插入，需要判断当前课程id和学生id是否存在。

学生答案：

得分：10 分

DELIMITER $$

CREATE PROCEDURE  插入(IN 选课id_in INT ,IN 学生id_in INT,IN 课程id_in INT,IN 成绩_in INT )

BEGIN

IF EXISTS(SELECT * FROM 学生表,课程表 WHERE 学生id=学生id_in AND 课程id=课程id_in)THEN

INSERT INTO 选课表 VALUES(选课id_in,学生id_in,课程id_in,成绩_in);

ELSEIF NOT EXISTS(SELECT * FROM 学生表,课程表 WHERE 学生id=学生id_in AND 课程id=课程id_in)THEN

SELECT '不存在学生id或课程id';

END IF;

END $$

4、 已知选课管理系统Xkglxt包含如下数据表：

1）学生表: 学生id, 学号, 姓名, 专业, 年级, 姓名拼音缩写

2）课程表: 课程id, 课程名称, 学分  

3）选课表: 选课id, 学生id, 课程id, 成绩

 

请完成如下操作的SQL语句,表名和列名必须和上面的一致:

1、创建以上三个表的SQL语句；

2、创建视图 V_选课,其中包含这些字段: 选课id, 学号, 姓名, 课程名, 成绩；

3、查询张三的数据开发技术课程的成绩。

学生答案：

(1) CREATE TABLE学生表(

学生id INT,

学号INT,

姓名VARCHAR(50),

专业VARCHAR(50),

年级VARCHAR(50),

姓名拼音缩写VARCHAR(50));

 

CREATE TABLE课程表(

课程id INT,

课程名称 VARCHAR(50),

学分DECIMAL(10,2));

 

CREATE TABLE选课表(

选课id INT,

学生id INT,

课程id VARCHAR(50),

成绩DECIMAL(10,2));

 

(2) CREATE VIEW V_选课 AS

SELECT选课id,学号,姓名,课程名称,成绩

FROM选课表,学生表,选课表

WHERE选课表.学生id=学生表.学生id AND选课表.课程id=课程表.课程id);

(3)SELECT 成绩 FROM V_选课 WHERE 姓名=张三AND课程名称=’数据库开发技术’;

得分：10 分

 

 

5、 已知选课管理系统Xkglxt包含如下数据表和自定义函数(函数已定义好可直接使用)：

1）学生表: 学生id, 学号, 姓名, 专业, 年级, 姓名拼音缩写

2）自定义函数拼音缩写查询: pysx('中文字符串') 返回 该中文字符串的拼音缩写

 

请完成如下操作的SQL语句,表名和列名必须和上面的一致:

1、创建选课管理系统数据库以及学生表的SQL语句；

2、在学生表上创建一个触发器(名字自定),当修改学生表的姓名时自动处理姓名拼音缩写

学生答案：

1. CREATE DATABASE xkglxt;

CREATE TABLE `学生表`(

`学生id`INT,

`学号`INT,

`姓名`VARCHAR(50),

`专业`VARCHAR(50),`

年级`VARCHAR(50),`

姓名拼音缩写`VARCHAR(50));

 

2.DELIMITER$$

CREATE TRIGGER auto_add_pyjx

BEFORE UPDATE ON 学生表 FOR EACH ROW

BEGIN

SET new.姓名拼音缩写=(SELECT 'ssss');

END$$

 

6、 已知选课管理系统Xkglxt包含如下数据表：

1）学生表: 学生id, 学号, 姓名, 专业, 年级, 姓名拼音缩写

2）课程表: 课程id, 课程名称, 学分  

3）选课表: 选课id, 学生id, 课程id, 成绩

 

请完成如下操作的SQL语句,表名和列名必须和上面的一致:

1、创建以上三个表的SQL语句；

2、在课程表上创建一个触发器(名字自定)。,当删除课程信息时自动删除该课程的选课信息。

学生答案：

2、DELIMITER$$

CREATE TRIGGER mytrigger

AFTER DELETE ON 课程表 FOR EACH ROW

BEGIN

DELETE FROM 选课表 WHERE 选课表.课程id=old.课程id；

END$$

 

7、 已知选课管理系统Xkglxt包含如下数据表：

1）学生表: 学生id, 学号, 姓名, 专业, 年级, 姓名拼音缩写

2）课程表: 课程id, 课程名称, 学分  

3）选课表: 选课id, 学生id, 课程id, 成绩

 

请完成如下操作的SQL语句,表名和列名必须和上面的一致:

1、创建以上三个表的SQL语句；

2、在学生表上创建一个触发器(名字自定),当删除学生信息时自动删除该学生的选课信息。

学生答案：

2、DELIMITER$$

CREATE TRIGGER t2

BEFORE DELETE ON  学生表 FOR EACH ROW

BEGIN

DELETE FROM 选课表 WHERE 学生id=old.学生id;

END$$

 

8、 已知选课管理系统Xkglxt包含如下数据表和自定义函数(函数已定义好可直接使用)：

1）学生表: 学生id, 学号, 姓名, 专业, 年级, 姓名拼音缩写

2）自定义函数拼音缩写查询: pysx('中文字符串') 返回 该中文字符串的拼音缩写

 

请完成如下操作的SQL语句,表名和列名必须和上面的一致:

1、创建选课管理系统数据库以及学生表的SQL语句；

2、在学生表上创建一个触发器(名字自定),当新增学生时自动处理姓名拼音缩写。

学生答案：

DELIMITER$$

CREATE TRIGGER t1

AFTER INSERT ON学生表 FOR EACH ROW

BEGIN

DECLARE  sx VARCHAR(10)DEFAULT '';

SET sx=(pysx(new.姓名));

UPDATE 学生表 SET 姓名拼音缩写=sx WHERE 学生id=new.学生id;

END$$

 

9、 已知选课管理系统Xkglxt包含如下数据表：

1）学生表: 学生id, 学号, 姓名, 专业, 年级, 姓名拼音缩写

2）课程表: 课程id, 课程名称, 学分  

3）选课表: 选课id, 学生id, 课程id, 成绩

 

请完成如下操作的SQL语句,表名和列名必须和上面的一致:

1、创建以上三个表的SQL语句；

2、创建一个存储过程（名字自定），实现学生表的插入，需要判断当前学号的学生是否存在。

学生答案：

DELIMITER$$

CREATE PROCEDURE 插入2(IN 学生id_in INT,IN 学号_in VARCHAR(15),IN 姓名_in VARCHAR(20),IN 专业_in VARCHAR(20),IN 年级_in VARCHAR(20),IN 姓名拼音缩写_in VARCHAR(10))

BEGIN

IF NOT EXISTS(SELECT * FROM 学生表 WHERE 学号=学号_in)THEN

INSERT INTO 学生表 VALUES(学生id_in,学号_in,姓名_in,专业_in,年级_in,姓名拼音缩写_in);

ELSEIF EXISTS(SELECT * FROM 学生表 WHERE 学号=学号_in)THEN

SELECT '已存在此学号';

END IF;

END$$

 

 

 

10、 已知产品销售数据库cpxs包含如下数据表：

1）产品表：产品编号，产品名称，价格，库存量。

2）销售商表：客户编号，客户名称，地区，负责人，电话。

3）产品销售表：销售日期，产品编号，客户编号，数量，销售额。

写出SQL完成如下操作的SQL语句：

1.写出产品表的建表语句

2.向产品表中插入如下记录：

0001  空调  3000  200

0002  冰箱  2500  300

3.将产品表中每种商品价格打八折后进行显示

4.查找价格在2000到2900之间的产品名称。

5.在产品销售表上创建“冰箱”产品表的视图bxcp

学生答案：

1、create table产品表

(产品编号varchar(10)primary key,

产品名称varchar(255),

价格int,

库存量int)charsetutf8;

 

create table产品销售表(

销售日期date,

产品编号varchar(10),

客户编号varchar(10),

数量int,

销售额int,

foreign key(产品编号) references产品表(产品编号))charsetutf8;

 

2、insert into产品表values(’0001’,’空调’,3000,200);

insert into产品表values(’0002’,’冰箱’,2500,300);

 

3、update产品表set价格=价格*0.8;

select * from产品表;

 

4、select产品名称from产品表where价格between 2000 and 2900;

 

5、create view bxcp as select产品表.产品名称,产品销售表.销售日期,产品销售表.数量

from产品表,产品销售表

where产品表.产品编号=产品销售表.产品编号and产品表.产品名称=’冰箱’;

得分：10 分

 

 

 

11、 已知进销存数据库Jxcxt包含如下数据表：

1）商品表：商品id(自增1), 商品名称, 单位, 单价, 商品状态

2）库存表：库存id(自增1), 商品id, 库存数量

 

请完成如下操作的SQL语句,表名和列名必须和上面的一致:

1、创建以上两个表的SQL语句；

2、在商品表上创建一个触发器(名字自定),当新增商品时自动在库存表中插入该商品的库存信息(库存数量为0)。

学生答案：

1、create table商品表(

商品id int not null auto_increment,

商品名称varchar(255),

单位varchar(10),

单价int,

商品状态varchar(10),

primarykey(商品id))charsetutf8;

 

create table库存表(

库存id int not null auto_increment,

商品id int,

库存数量int,

foreign key(商品id)references商品表(商品id),

primary key(库存id, 商品id))charsetutf8;

 

2、delimiter$$

create trigger增商品触库存

after insert on商品表for each row

begin

insert into库存表values(null,new.商品id,0);

end$$

得分：10 分

 

 

 

12、 已知进销存数据库Jxcxt包含如下数据表：

1）商品表：商品id, 商品名称, 单位, 单价, 商品状态

2）库存表：库存id, 商品id, 库存数量

 

请完成如下操作的SQL语句,表名和列名必须和上面的一致:

1、创建以上两个表的SQL语句；

2、创建一个存储过程（名字自定），实现商品表的的插入，需要判断当前商品名称是否存在。

学生答案：

create table商品表(

商品id int not null auto_increment,

商品名称varchar(255),

单位varchar(10),

单价int,

商品状态varchar(10),

primarykey(商品id))charsetutf8;

 

create table库存表(

库存id int not null auto_increment,

商品id int,

库存数量int,

foreign key(商品id) references 商品表 (商品id),

primary key(库存id, 商品id))charsetutf8;

 

createviewv_库存asselect库存id,商品名称,单位,库存数量,库存数量*单价as库存金额from商品表,库存表where商品表.商品id=库存表.商品id;

得分：10 分

 

 

 

13、 已知选课管理系统Xkglxt包含如下数据表：

1）学生表: 学生id, 学号, 姓名, 专业, 年级, 姓名拼音缩写

2）课程表: 课程id, 课程名称, 学分  

3）选课表: 选课id, 学生id, 课程id, 成绩

 

请完成如下操作的SQL语句,表名和列名必须和上面的一致:

1、创建以上三个表的SQL语句；

2、创建一个存储过程（名字自定），实现课程表的插入，需要判断当前课程名称的课程是否存在。

学生答案：

 

 

14、已知进销存数据库Jxcxt包含如下数据表：

1）商品表：商品id(自增1), 商品名称, 单位, 单价, 商品状态

2）库存表：库存id(自增1), 商品id, 库存数量

 

请完成如下操作的SQL语句,表名和列名必须和上面的一致:

1、创建以上两个表的SQL语句；

2、在商品表上创建一个触发器(名字自定),当删除商品时自动删除该商品在库存表中的记录。

学生答案：