《MYSQL高级查询与编程》综合机试试卷 - 云南农职美和易思

01/13 10:08
阅读数 118

题目:银行mysql数据库系统管理

目录

题目:银行mysql数据库系统管理

一、语言和环境

二、题目(100分):

功能需求:

要求:

三、提交方式

四、评分标准:

五、实现代码:

创建表结构:

插入数据:

1、对deposite表进行统计,按银行统计存款总数,显示为b_id, bank_name, total;(注:将结果保存为视图,并将结果截图,视图和图片命名为answer01);

2、对deposite, customer, bank进行查询,查询条件为location在广州、苏州、济南的客户,存款在300000至500000之间的存款记录,显示客户姓名name、银行名称bank name、存款金额amount;将结果保存在视图中;(注:将结果保存为视图,并将结果截图,视图和图片命名为answer02)

3、查询在农业银行存款前2名的客户存款信息(显示信息:客户姓名,银行名称,存款金额);(注:将结果保存为视图,并将结果截图,视图和图片命名为answer03);

4、更新customer表的salary属性,将salary低于5000的客户的salary     变为原来的2倍(注:执行你所编写sql语句,检查结果变化,不需要保存sql,将更新前的数据截图保存为answer04(old),更新后的数据截图为answer04(new)); 

5、查询日期为2011-04-05这一天到目前进行过存款的客户ID,客户姓名,银行名称,存款金额,按存款金额降序排列;将结果保存在视图中(注:将结果保存为视图,并将结果截图,视图和图片命名为answer05);

6、查询郭海在工商银行的存款信息(显示信息:客户id,客户姓名,银行标识,银行名称,存款日期,存款金额)(注:将结果保存为视图,并将结果截图,视图和图片命名为answer06);

7、创建存储过程,使用int类型传入参数10000,并查询工商银行存款大于等于一万的客户姓名,金额,存款期限(查询使用表链接和子查询两种方式实现)(注:sql语句以 .txt文件保存,命名为answer07);

数据库完整结构和代码:


一、语言和环境

  1. 实现语言:sql。
  2. 开发环境:mySql,navicat。

二、题目(100分):

功能需求:

a、创建数据库,名称为myBank;

b、创建数据表customer(客户)、deposite(存款)、bank(银行),表结构如下:

customer的表结构

deposite的表结构

bank的表结构

 c、录入以下数据

customer的数据如下:

bank的数据如下:

deposite的数据如下:

要求:

  1. 对deposite表进行统计,按银行统计存款总数,显示为b_id, bank_name, total;(:将结果保存为视图,并将结果截图,视图和图片命名为answer01);
  2. 对deposite, customer, bank进行查询,查询条件为location在广州、苏州、济南的客户,存款在300000至500000之间的存款记录,显示客户姓名name、银行名称bank name、存款金额amount;将结果保存在视图中;(:将结果保存为视图,并将结果截图,视图和图片命名为answer02
  3. 查询在农业银行存款前2名的客户存款信息(显示信息:客户姓名,银行名称,存款金额);(:将结果保存为视图,并将结果截图,视图和图片命名为answer03);
  4. 更新customer表的salary属性,将salary低于5000的客户的salary     变为原来的2倍(:执行你所编写sql语句,检查结果变化,不需要保存sql,将更新前的数据截图保存为answer04(old)更新后的数据截图为answer04(new)); 
  5. 查询日期为2011-04-05这一天到目前进行过存款的客户ID,客户姓名,银行名称,存款金额,按存款金额降序排列;将结果保存在视图中(:将结果保存为视图,并将结果截图,视图和图片命名为answer05);
  6. 查询郭海在工商银行的存款信息(显示信息:客户id,客户姓名,银行标识,银行名称,存款日期,存款金额)(:将结果保存为视图,并将结果截图,视图和图片命名为answer06);
  7. 创建存储过程,使用int类型传入参数10000,并查询工商银行存款大于等于一万的客户姓名,金额,存款期限(查询使用表链接和子查询两种方式实现)(:sql语句以 .txt文件保存,命名为answer07);

注:保存为视图的2种方式:

一、create view view_name as select column_name from table_name  where condition;

二、使用工具创建视图,保存;

推荐实现步骤

  1. 打开Navicat,创建数据库myBank;
  2. 创建数据表customer(客户)、deposite(存款)、bank(银行),建表时注意  主键、外健以及相关约束、类型和长度、并根据以上表格数据进行数据的录入(可用sql语句,也可使用navicat直接填入数据)。
  3. 按要求完成查询和修改将sql语句根据相应的备注信息保存查询或视图;sql语句可以外联或子查询,方法不做限定;建立视图可以直接用navicat视图工具也可以写代码创建。

三、提交方式

文件以压缩包提交, 压缩包文件命名方式 :学号+中文名字.zip, 比如:193610202139张玉苗.zip,压缩包必需在按规定的时间以内, 按监考老师的要求提交.

、评分标准:

题目:银行管理系统

该程序评分标准如下:

30

数据库和表结构的正确创建,并根据提示正确录入数据

 

5

数据库的创建(包含命名)

 

15

正确创建表结构(包括表的命名和主键、外检以及非空约束)

 

10

正确录入数据

70

根据要求查询或修改并保存查询或建立视图

 

10

要求1,结果正确

 

10

 

要求2,结果正确

 

10

要求3,结果正确

 

10

要求4,结果正确

 

10

要求5,结果正确

 

10

要求6,结果正确

 

10

要求7,结果正确

总分

100

五、实现代码:

创建表结构:

-- ----------------------------
-- Table structure for bank
-- ----------------------------
CREATE TABLE `bank` (
  `b_id` char(5) NOT NULL,
  `bank_name` char(30) NOT NULL,
  PRIMARY KEY (`b_id`)
);
-- ----------------------------
-- Table structure for customer
-- ----------------------------
CREATE TABLE `customer` (
  `c_id` char(6) NOT NULL,
  `name` varchar(30) NOT NULL,
  `location` varchar(30) DEFAULT NULL,
  `salary` double(8,2) DEFAULT NULL,
  PRIMARY KEY (`c_id`)
);
-- ----------------------------
-- Table structure for deposite
-- ----------------------------
CREATE TABLE `deposite` (
  `d_id` int(11) NOT NULL,
  `c_id` char(6) DEFAULT NULL,
  `b_id` char(5) DEFAULT NULL,
  `dep_date` date DEFAULT NULL,
  `dep_type` int(11) DEFAULT NULL,
  `amount` double(10,3) DEFAULT NULL,
  PRIMARY KEY (`d_id`)
);

插入数据:

bank表:

INSERT INTO `bank` VALUES ('B0001', '工商银行');
INSERT INTO `bank` VALUES ('B0002', '建设银行');
INSERT INTO `bank` VALUES ('B0003', '中国银行');
INSERT INTO `bank` VALUES ('B0004', '农业银行');

customer表:

INSERT INTO `customer` VALUES ('101001', '孙杨', '广州', '1234.00');
INSERT INTO `customer` VALUES ('101002', '郭海', '南京', '3526.00');
INSERT INTO `customer` VALUES ('101003', '卢江', '苏州', '6892.00');
INSERT INTO `customer` VALUES ('101004', '郭惠', '济南', '3492.00');

deposite表:

INSERT INTO `deposite` VALUES ('1', '101001', 'B0001', '2011-04-05', '3', '42526.000');
INSERT INTO `deposite` VALUES ('2', '101002', 'B0003', '2012-07-15', '5', '66500.000');
INSERT INTO `deposite` VALUES ('3', '101003', 'B0002', '2010-11-24', '1', '42366.000');
INSERT INTO `deposite` VALUES ('4', '101004', 'B0004', '2008-03-31', '1', '62362.000');
INSERT INTO `deposite` VALUES ('5', '101001', 'B0003', '2002-02-07', '3', '56346.000');
INSERT INTO `deposite` VALUES ('6', '101002', 'B0001', '2004-09-23', '3', '353626.000');
INSERT INTO `deposite` VALUES ('7', '101003', 'B0004', '2003-12-14', '5', '36236.000');
INSERT INTO `deposite` VALUES ('8', '101004', 'B0002', '2007-04-21', '5', '26267.000');
INSERT INTO `deposite` VALUES ('9', '101001', 'B0002', '2011-02-11', '1', '435456.000');
INSERT INTO `deposite` VALUES ('10', '101002', 'B0004', '2012-05-13', '1', '234626.000');
INSERT INTO `deposite` VALUES ('11', '101003', 'B0003', '2001-01-24', '5', '26243.000');
INSERT INTO `deposite` VALUES ('12', '101004', 'B0001', '2009-08-23', '3', '45671.000');

1、对deposite表进行统计,按银行统计存款总数,显示为b_id, bank_name, total;(:将结果保存为视图,并将结果截图,视图和图片命名为answer01);

CREATE VIEW answer01 AS
SELECT b.b_id b_id, b.bank_name bank_name,SUM(d.amount) total 
FROM deposite d,bank b 
WHERE d.b_id=b.b_id GROUP BY b.bank_name;

2、对deposite, customer, bank进行查询,查询条件为location在广州、苏州、济南的客户,存款在300000至500000之间的存款记录,显示客户姓名name、银行名称bank name、存款金额amount;将结果保存在视图中;(:将结果保存为视图,并将结果截图,视图和图片命名为answer02

CREATE VIEW answer02 AS
SELECT name 客户姓名,bank_name 银行姓名,amount 存款金额 
FROM deposite d,customer c,bank b 
WHERE b.b_id=d.b_id AND d.c_id=c.c_id 
AND location IN ('广州','苏州','济南') AND (amount BETWEEN 300000 AND 500000);

3、查询在农业银行存款前2名的客户存款信息(显示信息:客户姓名,银行名称,存款金额);(:将结果保存为视图,并将结果截图,视图和图片命名为answer03);

CREATE VIEW answer03 AS
SELECT name 客户姓名,bank_name 银行名称,amount 存款金额 
FROM deposite d,customer c,bank b 
WHERE b.b_id=d.b_id AND bank_name='农业银行' AND d.c_id=c.c_id LIMIT 2;

4、更新customer表的salary属性,将salary低于5000的客户的salary     变为原来的2倍(:执行你所编写sql语句,检查结果变化,不需要保存sql,将更新前的数据截图保存为answer04(old)更新后的数据截图为answer04(new)); 

SELECT * FROM customer WHERE salary<5000;
UPDATE customer SET salary=(salary*2) WHERE salary<5000;
SELECT * FROM customer WHERE salary;

5、查询日期为2011-04-05这一天到目前进行过存款的客户ID,客户姓名,银行名称,存款金额,按存款金额降序排列;将结果保存在视图中(:将结果保存为视图,并将结果截图,视图和图片命名为answer05);

CREATE VIEW answer05 AS
SELECT c.c_id 客户id,name 客户姓名,b.b_id 银行标识,b.bank_name 银行名称,amount 存款金额
FROM deposite d,customer c,bank b
WHERE b.b_id=d.b_id AND d.c_id=c.c_id 
AND dep_date>='2011-04-05' ORDER BY amount; 

6、查询郭海在工商银行的存款信息(显示信息:客户id,客户姓名,银行标识,银行名称,存款日期,存款金额)(:将结果保存为视图,并将结果截图,视图和图片命名为answer06);

CREATE VIEW answer06 AS
SELECT c.c_id 客户id,name 客户姓名,b.b_id 银行标识,b.bank_name 银行名称,dep_date 存款日期,amount 存款金额
FROM deposite d,customer c,bank b
WHERE b.b_id=d.b_id AND d.c_id=c.c_id 
AND `name`='郭海' AND bank_name='工商银行';

7、创建存储过程,使用int类型传入参数10000,并查询工商银行存款大于等于一万的客户姓名,金额,存款期限(查询使用表链接和子查询两种方式实现)(:sql语句以 .txt文件保存,命名为answer07);

-- 使用表连接查询的方式创建存储过程
delimiter $$
create procedure answer07(
	in num INT
)
begin
	SELECT name 客户姓名,amount 金额,dep_type 存款期限 
	FROM deposite d,customer c,bank b 
	WHERE b.b_id=d.b_id AND d.c_id=c.c_id  
	AND bank_name='工商银行' AND amount>=num;
end
$$
delimiter ;
-- 实现存储过程
SET @num=10000;
CALL answer07(@num);

数据库完整结构和代码:

/*
Navicat MySQL Data Transfer

Source Server         : test
Source Server Version : 50646
Source Host           : localhost:3306
Source Database       : mybank

Target Server Type    : MYSQL
Target Server Version : 50646
File Encoding         : 65001

Date: 2021-01-11 22:39:04
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for bank
-- ----------------------------
DROP TABLE IF EXISTS `bank`;
CREATE TABLE `bank` (
  `b_id` char(5) NOT NULL,
  `bank_name` char(30) NOT NULL,
  PRIMARY KEY (`b_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of bank
-- ----------------------------
INSERT INTO `bank` VALUES ('B0001', '工商银行');
INSERT INTO `bank` VALUES ('B0002', '建设银行');
INSERT INTO `bank` VALUES ('B0003', '中国银行');
INSERT INTO `bank` VALUES ('B0004', '农业银行');

-- ----------------------------
-- Table structure for customer
-- ----------------------------
DROP TABLE IF EXISTS `customer`;
CREATE TABLE `customer` (
  `c_id` char(6) NOT NULL,
  `name` varchar(30) NOT NULL,
  `location` varchar(30) DEFAULT NULL,
  `salary` double(8,2) DEFAULT NULL,
  PRIMARY KEY (`c_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of customer
-- ----------------------------
INSERT INTO `customer` VALUES ('101001', '孙杨', '广州', '1234.00');
INSERT INTO `customer` VALUES ('101002', '郭海', '南京', '3526.00');
INSERT INTO `customer` VALUES ('101003', '卢江', '苏州', '6892.00');
INSERT INTO `customer` VALUES ('101004', '郭惠', '济南', '3492.00');

-- ----------------------------
-- Table structure for deposite
-- ----------------------------
DROP TABLE IF EXISTS `deposite`;
CREATE TABLE `deposite` (
  `d_id` int(11) NOT NULL,
  `c_id` char(6) DEFAULT NULL,
  `b_id` char(5) DEFAULT NULL,
  `dep_date` date DEFAULT NULL,
  `dep_type` int(11) DEFAULT NULL,
  `amount` double(10,3) DEFAULT NULL,
  PRIMARY KEY (`d_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of deposite
-- ----------------------------
INSERT INTO `deposite` VALUES ('1', '101001', 'B0001', '2011-04-05', '3', '42526.000');
INSERT INTO `deposite` VALUES ('2', '101002', 'B0003', '2012-07-15', '5', '66500.000');
INSERT INTO `deposite` VALUES ('3', '101003', 'B0002', '2010-11-24', '1', '42366.000');
INSERT INTO `deposite` VALUES ('4', '101004', 'B0004', '2008-03-31', '1', '62362.000');
INSERT INTO `deposite` VALUES ('5', '101001', 'B0003', '2002-02-07', '3', '56346.000');
INSERT INTO `deposite` VALUES ('6', '101002', 'B0001', '2004-09-23', '3', '353626.000');
INSERT INTO `deposite` VALUES ('7', '101003', 'B0004', '2003-12-14', '5', '36236.000');
INSERT INTO `deposite` VALUES ('8', '101004', 'B0002', '2007-04-21', '5', '26267.000');
INSERT INTO `deposite` VALUES ('9', '101001', 'B0002', '2011-02-11', '1', '435456.000');
INSERT INTO `deposite` VALUES ('10', '101002', 'B0004', '2012-05-13', '1', '234626.000');
INSERT INTO `deposite` VALUES ('11', '101003', 'B0003', '2001-01-24', '5', '26243.000');
INSERT INTO `deposite` VALUES ('12', '101004', 'B0001', '2009-08-23', '3', '45671.000');

-- ----------------------------
-- View structure for answer01
-- ----------------------------
DROP VIEW IF EXISTS `answer01`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `answer01` AS select `b`.`b_id` AS `b_id`,`b`.`bank_name` AS `bank_name`,sum(`d`.`amount`) AS `total` from (`deposite` `d` join `bank` `b` on((`b`.`b_id` = `d`.`b_id`))) where (`d`.`b_id` = `b`.`b_id`) group by `b`.`bank_name` ;

-- ----------------------------
-- View structure for answer02
-- ----------------------------
DROP VIEW IF EXISTS `answer02`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `answer02` AS select `c`.`name` AS `客户姓名`,`b`.`bank_name` AS `银行姓名`,`d`.`amount` AS `存款金额` from ((`deposite` `d` join `customer` `c`) join `bank` `b`) where ((`b`.`b_id` = `d`.`b_id`) and (`d`.`c_id` = `c`.`c_id`) and (`c`.`location` in ('广州','苏州','济南')) and (`d`.`amount` between 300000 and 500000)) ;

-- ----------------------------
-- View structure for answer03
-- ----------------------------
DROP VIEW IF EXISTS `answer03`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `answer03` AS select `c`.`name` AS `客户姓名`,`b`.`bank_name` AS `银行名称`,`d`.`amount` AS `存款金额` from ((`deposite` `d` join `customer` `c`) join `bank` `b`) where ((`b`.`b_id` = `d`.`b_id`) and (`b`.`bank_name` = '农业银行') and (`d`.`c_id` = `c`.`c_id`)) limit 2 ;

-- ----------------------------
-- View structure for answer05
-- ----------------------------
DROP VIEW IF EXISTS `answer05`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `answer05` AS select `c`.`c_id` AS `客户id`,`c`.`name` AS `客户姓名`,`b`.`b_id` AS `银行标识`,`b`.`bank_name` AS `银行名称`,`d`.`amount` AS `存款金额` from ((`deposite` `d` join `customer` `c`) join `bank` `b`) where ((`b`.`b_id` = `d`.`b_id`) and (`d`.`c_id` = `c`.`c_id`) and (`d`.`dep_date` >= '2011-04-05')) order by `d`.`amount` ;

-- ----------------------------
-- View structure for answer06
-- ----------------------------
DROP VIEW IF EXISTS `answer06`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `answer06` AS select `c`.`c_id` AS `客户id`,`c`.`name` AS `客户姓名`,`b`.`b_id` AS `银行标识`,`b`.`bank_name` AS `银行名称`,`d`.`dep_date` AS `存款日期`,`d`.`amount` AS `存款金额` from ((`deposite` `d` join `customer` `c`) join `bank` `b`) where ((`b`.`b_id` = `d`.`b_id`) and (`d`.`c_id` = `c`.`c_id`) and (`c`.`name` = '郭海') and (`b`.`bank_name` = '工商银行')) ;

-- ----------------------------
-- Procedure structure for answer07
-- ----------------------------
DROP PROCEDURE IF EXISTS `answer07`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `answer07`(
	in num INT
)
begin
	SELECT name 客户姓名,amount 金额,dep_type 存款期限 
	FROM deposite d,customer c,bank b 
	WHERE b.b_id=d.b_id AND d.c_id=c.c_id  
	AND bank_name='工商银行' AND amount>=num;
end
;;
DELIMITER ;

 

展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部