=============================================

1.试题编号：1-2-1：《学生管理信息系统》项目

CREATE DATABASE StudentDB DEFAULT CHARSET utf8;
USE StudentDB;
CREATE TABLE T_student
(
stud_id VARCHAR(10) NOT NULL PRIMARY KEY,
stud_name VARCHAR(20),
stud_sex CHAR(2),
birth_date DATETIME,
entry_date DATETIME,
mobile VARCHAR(11),
birth_place VARCHAR(30),
reserve TEXT
);
CREATE TABLE T_course
(
course_id VARCHAR(10) NOT NULL PRIMARY KEY,
course_name VARCHAR(100),
teacher_name VARCHAR(30)
);
CREATE TABLE T_score
(
score_id BIGINT NOT NULL PRIMARY KEY,
course_id VARCHAR(10),
stud_id VARCHAR(10),
score DECIMAL(5,2),
FOREIGN KEY (course_id) REFERENCES T_course (course_id),
FOREIGN KEY (stud_id) REFERENCES T_student (stud_id)
);

在T_student表插入数据：“2009010001，张学友，男，1980-10-4，2009-9-1，15012345678，湖南长沙”，无；

INSERT T_student VALUES (‘2009010001′,’张学友’,’男’,’1980-10-4′,’2009-9-1′,’15012345678′,’湖南长沙’,’无’);

测试数据

INSERT T_student VALUES (‘2009010002′,’刘德华’,’男’,’1981-05-15′,’2009-9-1′,’15912345656′,’湖南常德’,’无’),
(‘2009010003′,’李小明’,’男’,’1982-05-23′,’2009-9-1′,’13746861681′,’湖南长沙’,’无’),
(‘2009010004′,’刘小芳’,’女’,’1983-06-01′,’2009-9-1′,’14898446465′,’湖南常德’,’无’),
(‘2009010005′,’张萌萌’,’女’,’1981-05-15′,’2009-9-1′,’15912345657′,’湖南常德’,’无’),
(‘2009010006′,’陈晓晓’,’女’,’1980-04-10′,’2009-9-1′,’15912345645′,’湖南衡阳’,’无’),
(‘2009010007′,’钱多多’,’男’,’1981-07-15′,’2009-9-1′,’15912345666′,’湖南常德’,’无’);
INSERT T_course VALUES (‘61020501′,’数据库应用’,’张华’),
(‘61020502′,’Java web’,’李刚’),
(‘61020503′,’Android’,’刘勇’),
(‘61020504′,’Linux’,’赵涛’);
INSERT T_score VALUES (1,’61020501′,’2009010001′,90),
(2,’61020501′,’2009010002′,86),
(3,’61020501′,’2009010003′,95),
(4,’61020501′,’2009010004′,78),
(5,’61020502′,’2009010001′,88),
(6,’61020502′,’2009010002′,78),
(7,’61020502′,’2009010003′,96),
(8,’61020502′,’2009010004′,85),
(9,’61020503′,’2009010001′,89);

查询出籍贯为“湖南长沙”的学生基本信息；

SELECT * FROM T_student WHERE birth_place=’湖南长沙’;

查询出姓名为“张学友”的学生所有课程的成绩；

SELECT course_id,score
FROM T_student JOIN T_score ON T_student.stud_id=T_score.stud_id
WHERE stud_name=’张学友’;

查询出“数据库应用”这门课的平均成绩；

SELECT AVG(score) AS ‘avgOfscore’
FROM T_score JOIN T_course ON T_score.course_id=T_course.course_id
WHERE course_name=’数据库应用’;

创建视图查询学生的姓名，手机号码和籍贯；

CREATE VIEW v_studentInfor
AS
SELECT stud_name,mobile,birth_place FROM T_student;
SELECT * FROM v_studentInfor;

创建存储过程，查询指定课程名称的最高成绩。

DELIMITER $$
CREATE PROCEDURE p_maxOfScore(IN c_name VARCHAR(100),OUT c_score DECIMAL(5,2))
BEGIN
SELECT MAX(score) INTO c_score
FROM T_score JOIN T_course ON T_score.course_id=T_course.course_id
WHERE course_name=c_name;
END$$
DELIMITER ;
CALL p_maxOfScore(‘Java Web’,@c_score);

SELECT @c_score;

2.试题编号：1-2-2：《教务管理系统》项目

创建数据库HNIUEAM

CREATE DATABASE HNIUEAM DEFAULT CHARSET utf8;

创建数据表T_Supplier、T_BookInfo、T_Order、T_OrderInfor。

USE HNIUEAM;
CREATE TABLE T_Supplier
(
supplier_id VARCHAR(8) PRIMARY KEY,
supplier_name VARCHAR(50) NOT NULL,
supplier_people VARCHAR(8) NOT NULL,
supplier_address VARCHAR(50),
supplier_phone VARCHAR(15),
supplier_postcode VARCHAR(6),
supplier_remark TEXT
);
CREATE TABLE T_BookInfo
(
book_id VARCHAR(8) PRIMARY KEY,
book_name VARCHAR(50) NOT NULL,
book_isbn VARCHAR(17) NOT NULL,
book_author VARCHAR(20) NOT NULL,
book_publisher VARCHAR(50) NOT NULL,
book_datetime DATETIME NOT NULL,
book_price FLOAT NOT NULL,
book_rkm TEXT
);
CREATE TABLE T_Order
(
order_id VARCHAR(8) PRIMARY KEY,
supplier_id VARCHAR(8) NOT NULL,
order_datetime DATETIME NOT NULL,
order_status BIT NOT NULL,
FOREIGN KEY (supplier_id) REFERENCES T_Supplier (supplier_id)
);
CREATE TABLE T_OrderInfor
(
orderdet_id VARCHAR(8) PRIMARY KEY,
order_id VARCHAR(8) NOT NULL,
book_id VARCHAR(8) NOT NULL,
orderdet_num INT NOT NULL,
orderdet_status BIT NOT NULL,
FOREIGN KEY (order_id) REFERENCES T_Order (order_id)
);

向T_BookInfo表插入数据：“BC0001, windows程序设计,0257-9413, 刘立,电子工业出版社,2010-11-10,42,无”；

INSERT T_BookInfo VALUES (‘BC0001′,’windows程序设计’,’0257-9413′,’刘立’,’电子工业出版社’,’2010-11-10′,42,’无’);

添加测试数据

INSERT T_BookInfo VALUES (‘BC0002′,’Java程序设计’,’0234-1234′,’李峰’,’机械工业出版社’,’2018-01-01′,45,’无’),
(‘BC0003′,’Python程序设计’,’0234-8692′,’王勇’,’电子工业出版社’,’2019-03-20′,65.5,’无’),
(‘BC0004′,’JavaScript’,’0234-7895′,’陈明’,’机械工业出版社’,’2018-10-01′,50,’无’),
(‘BC0005′,’Photoshop图形图像处理’,’0234-7956′,’刘强’,’电子工业出版社’,’2019-01-25′,42,’无’),
(‘BC0006′,’红楼梦’,’2654-1234′,’曹雪芹’,’湖南文艺出版社’,’2018-01-01′,68,’无’),
(‘BC0007′,’三国演义’,’2654-7898′,’罗贯中’,’湖南文艺出版社’,’2018-01-01′,68,’无’),
(‘BC0008′,’水浒传’,’2654-1789′,’施耐庵’,’湖南文艺出版社’,’2018-01-01′,68,’无’),
(‘BC0009′,’西游记’,’2654-7896′,’吴承恩’,’湖南文艺出版社’,’2018-01-01′,68,’无’);
INSERT T_Supplier VALUES (‘SP0001′,’电子工业出版社代理商’,’李文明’,’北京’,’15978659087′,’010′,’无’),
(‘SP0002′,’机械工业出版社代理商’,’王芳’,’北京’,’13678659015′,’010′,’无’),
(‘SP0003′,’湖南文艺出版社代理商’,’张华’,’长沙’,’13545679852′,’415000′,’无’);
INSERT T_Order VALUES (‘DR0001′,’SP0001′,’2020-01-01’,1),
(‘DR0002′,’SP0001′,’2020-01-01’,1),
(‘DR0003′,’SP0002′,’2020-01-01’,1),
(‘DR0004′,’SP0002′,’2020-01-01’,0),
(‘DR0005′,’SP0003′,’2020-01-01’,0);
INSERT T_OrderInfor VALUES (‘DT0001′,’DR0001′,’BC0001’,100,1),
(‘DT0002′,’DR0001′,’BC0003’,150,1),
(‘DT0003′,’DR0002′,’BC0005’,100,1),
(‘DT0004′,’DR0003′,’BC0004’,130,1),
(‘DT0005′,’DR0004′,’BC0002’,200,1),
(‘DT0006′,’DR0005′,’BC0006’,100,1),
(‘DT0007′,’DR0005′,’BC0007’,100,1),
(‘DT0008′,’DR0005′,’BC0008’,100,1),
(‘DT0009′,’DR0005′,’BC0009’,100,1);

查询出供应商名称为“电子工业出版社代理商”的订单编号及订单状态；

SELECT order_id,order_status
FROM T_Order JOIN T_Supplier ON T_Order.supplier_id=T_Supplier.supplier_id
WHERE supplier_name=’电子工业出版社代理商’;

查询教材名称为“windows程序设计”的订购日期；

SELECT order_datetime
FROM T_Order JOIN T_OrderInfor ON T_Order.order_id=T_OrderInfor.order_id
JOIN T_BookInfo ON T_OrderInfor.book_id=T_BookInfo.book_id
WHERE book_name=’windows程序设计’;

创建视图查询供应商名为“电子工业出版社代理商”所订购的教材的详细信息(包括名称，ISBN编号，作者，出版社，出版时间，价格，数量)；

CREATE VIEW v_book
AS
SELECT book_name,book_isbn,book_author,book_publisher,book_datetime,book_price,orderdet_num
FROM T_Supplier JOIN T_Order ON T_Supplier.supplier_id=T_Order.supplier_id
JOIN T_OrderInfor ON T_Order.order_id=T_OrderInfor.order_id
JOIN T_BookInfo ON T_OrderInfor.book_id=T_BookInfo.book_id
WHERE supplier_name=’电子工业出版社代理商’;
SELECT * FROM v_book;

创建存储过程，根据订单号，将订单状态修改为1

DELIMITER $$
CREATE PROCEDURE p_updateStatus(IN orderid VARCHAR(8))
BEGIN
UPDATE T_Order SET order_status=1 WHERE order_id=orderid;
END $$
DELIMITER ;
SELECT * FROM T_OrderInfor;
SELECT * FROM T_Order;
CALL p_updateStatus(‘DR0004’);

创建存储过程，当订单详情表中相应订单的状态为“1”时,修改订单表的订单状态为“1”。

DELIMITER $$
CREATE PROCEDURE p_updateStatus2()
BEGIN
DECLARE temp_id VARCHAR(8);
DECLARE done BOOLEAN DEFAULT TRUE;
DECLARE cur_order_id CURSOR FOR
SELECT DISTINCT order_id FROM T_OrderInfor WHERE orderdet_status=1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=FALSE;
OPEN cur_order_id;
FETCH cur_order_id INTO temp_id;
WHILE done DO
UPDATE T_Order SET order_status=1 WHERE order_id=temp_id;
FETCH cur_order_id INTO temp_id;
END WHILE;
CLOSE cur_order_id;
END $$
DELIMITER ;
SELECT * FROM T_OrderInfor;
SELECT * FROM T_Order;

CALL p_updateStatus();

3.试题编号：1-2-3：《教务管理系统》项目

创建数据库HNIUEAM

CREATE DATABASE HNIUEAM DEFAULT CHARSET utf8;

创建数据表T_Student、T_Course、T_Teacher及两个关系表（关系表的表名自拟）

USE HNIUEAM;
CREATE TABLE T_Student
(
student_id VARCHAR(8) PRIMARY KEY,
student_name VARCHAR(8) NOT NULL,
student_sex BIT NOT NULL,
student_age INT NOT NULL
);
CREATE TABLE T_Course
(
course_id VARCHAR(8) PRIMARY KEY,
course_name VARCHAR(50) NOT NULL,
course_classhour INT NOT NULL,
course_creditpoints FLOAT NOT NULL
);
CREATE TABLE T_Teacher
(
teacher_id VARCHAR(8) PRIMARY KEY,
teacher_professional VARCHAR(20) NOT NULL,
teacher_name VARCHAR(8) NOT NULL,
teacher_age INT,
teacher_sex BIT
);
CREATE TABLE T_TeaCou
(
teacher_id VARCHAR(8),
course_id VARCHAR(8),
teacher_course_hour DATETIME NOT NULL,
teacher_course_place VARCHAR(50) NOT NULL,
teacher_course_evaluation TEXT,
PRIMARY KEY (teacher_id,course_id),
FOREIGN KEY (teacher_id) REFERENCES T_Teacher (teacher_id),
FOREIGN KEY (course_id) REFERENCES T_Course (course_id)
);
CREATE TABLE T_StuCou
(
student_id VARCHAR(8),
course_id VARCHAR(8),
student_course_hour DATETIME NOT NULL,
student_course_score FLOAT NOT NULL,
PRIMARY KEY (student_id,course_id),
FOREIGN KEY (course_id) REFERENCES T_Course (course_id),
FOREIGN KEY (student_id) REFERENCES T_Student (student_id)
);

在T_Course表中插入数据：“KC10001，数据结构，72，4”；

INSERT T_Course VALUES (‘KC10001′,’数据结构’,72,4);

测试数据

INSERT T_Student VALUES (‘S001′,’Sam’,1,19),
(‘S002′,’Jarry’,1,19),
(‘S003′,’Kate’,0,18),
(‘S004′,’Tom’,1,18),
(‘S005′,’Marry’,0,19),
(‘S006′,’Adam’,1,20);
INSERT T_Course VALUES (‘KC10002′,’数据库’,72,4),
(‘KC10003′,’JAVA WEB’,96,6),
(‘KC10004′,’JavaScript’,72,4),
(‘KC10005′,’Android编程’,72,4);
INSERT T_Teacher (teacher_id,teacher_name,teacher_professional) VALUES (‘T001′,’王强’,’讲师’),
(‘T002′,’李超’,’讲师’),
(‘T003′,’刘勇’,’副高’);
INSERT T_TeaCou VALUES (‘T001′,’KC10001′,’2010-9-1′,’1机房’,’无’),
(‘T001′,’KC10002′,’2010-9-1′,’2机房’,’无’),
(‘T002′,’KC10003′,’2010-9-1′,’3机房’,’无’),
(‘T002′,’KC10004′,’2010-9-1′,’4机房’,’无’),
(‘T003′,’KC10005′,’2010-9-1′,’5机房’,’无’);
INSERT T_StuCou VALUES (‘S001′,’KC10001′,’2010-9-1’,90),
(‘S002′,’KC10001′,’2010-9-1’,91),
(‘S003′,’KC10001′,’2010-9-1’,89),
(‘S004′,’KC10001′,’2010-9-1’,85),
(‘S001′,’KC10002′,’2010-9-1’,75),
(‘S002′,’KC10002′,’2010-9-1’,85),
(‘S001′,’KC10003′,’2010-9-1’,90),
(‘S002′,’KC10003′,’2010-9-1’,89),
(‘S003′,’KC10003′,’2010-9-1’,92),
(‘S004′,’KC10003′,’2010-9-1’,95),
(‘S002′,’KC10004′,’2010-9-1’,89),
(‘S003′,’KC10004′,’2010-9-1’,85),
(‘S004′,’KC10004′,’2010-9-1’,87),
(‘S005′,’KC10004′,’2010-9-1’,95),
(‘S001′,’KC10005′,’2010-9-1’,98),
(‘S002′,’KC10005′,’2010-9-1’,91);

查询选修了“数据结构”的学生信息；

SELECT T_Student.*
FROM T_Student JOIN T_StuCou ON T_Student.student_id=T_StuCou.student_id
JOIN T_Course ON T_StuCou.course_id=T_Course.course_id
WHERE course_name=’数据结构’;

查询教师名称为“王强”的教师所授课程情况；

SELECT T_TeaCou.*
FROM T_Teacher JOIN T_TeaCou ON T_Teacher.teacher_id=T_TeaCou.teacher_id
WHERE teacher_name=’王强’;

计算所选课程的时间为“2010-9-1”，课程名称为“数据结构”的所有学生的总成绩；

SELECT SUM(student_course_score) AS ‘总成绩’
FROM T_StuCou JOIN T_Course ON T_StuCou.course_id=T_Course.course_id
WHERE course_name=’数据结构’ AND student_course_hour=’2010-9-1′;

创建视图查询授课教师“李超”所授课的学生情况；

CREATE VIEW v_studentInfor
AS
SELECT DISTINCT T_Student.*
FROM T_Student JOIN T_StuCou ON T_Student.student_id=T_StuCou.student_id
JOIN T_Course ON T_StuCou.course_id=T_Course.course_id
JOIN T_TeaCou ON T_Course.course_id=T_TeaCou.course_id
JOIN T_Teacher ON T_TeaCou.teacher_id=T_Teacher.teacher_id
WHERE teacher_name=’李超’;
SELECT * FROM v_studentInfor;

创建存储过程，根据课程编号统计优秀率，规定成绩大于等于90的为优秀。

DELIMITER $$
CREATE PROCEDURE p_rate(IN c_id VARCHAR(8),OUT rate DECIMAL(4,3))
BEGIN
DECLARE num1 INT;
DECLARE num2 FLOAT;
SELECT COUNT() INTO num1 FROM T_StuCou WHERE course_id=c_id AND student_course_score>=90; SELECT COUNT() INTO num2 FROM T_StuCou WHERE course_id=c_id;
SET rate=num1/num2;
END $$
DELIMITER ;
CALL p_rate(‘KC10003’,@rate);

SELECT @rate;

4.试题编号：1-2-4：《学生成绩管理系统》项目

创建数据库Student

CREATE DATABASE Student DEFAULT CHARSET utf8;

创建数据表T_student_info、T_course_info 、T_student_scores。

创建数据表间的关系

USE Student;
CREATE TABLE T_student_info
(
student_no CHAR(6) PRIMARY KEY,
student_name CHAR(20) NOT NULL,
sex CHAR(2) NOT NULL,
birth DATETIME NOT NULL,
enter_date INT NOT NULL,
address VARCHAR(50) NOT NULL
);
CREATE TABLE T_course_info
(
course_no CHAR(8) PRIMARY KEY,
course_name CHAR(50) NOT NULL,
credit INT NOT NULL,
classhour INT NOT NULL
);
CREATE TABLE T_student_scores
(
course_no CHAR(8),
student_no CHAR(6),
ordinary_scores FLOAT NOT NULL,
end_scores FLOAT NOT NULL,
total_scores FLOAT NOT NULL,
PRIMARY KEY (course_no,student_no),
FOREIGN KEY (course_no) REFERENCES T_course_info (course_no),
FOREIGN KEY (student_no) REFERENCES T_student_info (student_no)
);

每个表插入3条测试数据，比如向T_student_info表插入数据：“102011，刘德华，男，03/23/1991，2009，湖南长沙劳动东路168号”；

INSERT T_student_info VALUES (‘102011′,’刘德华’,’男’,’1991-03-23′,2009,’湖南长沙劳动东路168号’),
(‘102012′,’李晓明’,’男’,’1992-05-21′,2009,’湖南长沙劳动东路168号’),
(‘102013′,’赵晓妮’,’女’,’1991-03-23′,2009,’湖南长沙劳动东路168号’);
INSERT T_course_info VALUES (‘K0001′,’数据结构’,4,72),
(‘K0002′,’java程序设计’,4,72),
(‘K0003′,’数据库’,4,72);
INSERT T_student_scores VALUES (‘K0001′,’102011’,89,85,86),
(‘K0001′,’102012’,90,92,91),
(‘K0001′,’102013’,75,78,76),
(‘K0002′,’102011’,80,82,81),
(‘K0002′,’102012’,95,97,96),
(‘K0002′,’102013’,65,60,62),
(‘K0003′,’102011’,85,86,85),
(‘K0003′,’102012’,98,99,97),
(‘K0003′,’102013’,79,78,78);

查询出1991年出生的学生信息；

SELECT *
FROM T_student_info
WHERE YEAR(birth)=1991;

查询选修了“数据结构”的学生姓名、平时成绩、期末成绩、总评成绩；

SELECT student_name,ordinary_scores,end_scores,total_scores
FROM T_student_info JOIN T_student_scores ON T_student_info.student_no=T_student_scores.student_no
JOIN T_course_info ON T_student_scores.course_no=T_course_info.course_no
WHERE course_name=’数据结构’;

创建视图查询选修了“java程序设计”学生的学号、姓名、课程名、总评成绩；

CREATE VIEW v_stuscores
AS
SELECT T_student_info.student_no,student_name,course_name,total_scores
FROM T_student_info JOIN T_student_scores ON T_student_info.student_no=T_student_scores.student_no
JOIN T_course_info ON T_student_scores.course_no=T_course_info.course_no
WHERE course_name=’java程序设计’;
SELECT * FROM v_stuscores;

统计每门课程的选课人数、最高分、平均分，并将统计结果保存在表T_total中。

CREATE TABLE T_total AS
SELECT course_no,COUNT(student_no) AS ‘numOfStu’,MAX(total_scores) AS ‘maxOfScore’,AVG(total_scores) AS ‘minOfScore’
FROM T_student_scores
GROUP BY course_no;

SELECT * FROM T_total;

5.试题编号：1-2-5：《图书管理信息系统》项目

创建数据库BookDB

CREATE DATABASE BookDB DEFAULT CHARSET utf8;

创建数据表T_card、T_book、T_borrow

USE BookDB;
CREATE TABLE T_book
(
Book_no VARCHAR(20) PRIMARY KEY,
Book_name VARCHAR(100),
Author VARCHAR(50),
Price DECIMAL(10,2),
Qty INT,
Loan_qty INT
);
CREATE TABLE T_card
(
Card_no VARCHAR(20) PRIMARY KEY,
Card_name VARCHAR(30),
Address VARCHAR(200),
Mobile VARCHAR(11)
);
CREATE TABLE T_borrow
(
Borrow_id BIGINT PRIMARY KEY,
Book_no VARCHAR(20) NOT NULL,
Card_no VARCHAR(20) NOT NULL,
Borrow_date DATETIME,
Return_date DATETIME,
FOREIGN KEY (Book_no) REFERENCES T_book (Book_no),
FOREIGN KEY (Card_no) REFERENCES T_card (Card_no)
);

向每个表插入3条测试数据；

INSERT T_book VALUES (‘B1001′,’数据库应用’,’曾建华’,58,15,12),
(‘B1002′,’java程序设计’,’李勇’,45,15,20),
(‘B1003′,’html5与css3′,’刘强’,65,15,8),
(‘B1004′,’Python’,’王芳’,42,5,5),
(‘B1005′,’jsp编程’,’李小明’,60,5,7);
INSERT T_card VALUES (‘C1001′,’Kate’,’无’,’13578954578′),
(‘C1002′,’Marry’,’无’,’13596547896′),
(‘C1003′,’Jarry’,’无’,’13878954512′),
(‘C1004′,’Tom’,’无’,’13732148965′),
(‘C1005′,’Tinna’,’无’,’15978954532′);
INSERT T_borrow VALUES (1,’B1001′,’C1001′,’2010-11-15′,’2010-11-12′),
(2,’B1003′,’C1002′,’2010-11-16′,’2010-12-11′),
(3,’B1002′,’C1003′,’2010-11-30′,’2010-12-24′),
(4,’B1004′,’C1003′,’2020-02-15′,’2020-06-12′),
(5,’B1004′,’C1001′,’2020-06-15′,’2020-07-12′),
(6,’B1001′,’C1002′,’2020-10-15′,NULL);

将“李”姓作者的所有图书单价下调10%；

UPDATE T_book SET Price=0.9*Price WHERE Author LIKE ‘李%’;

查询出日期在2010-10-31至2010-11-30之间借出的图书信息；

SELECT T_book.*
FROM T_book JOIN T_borrow ON T_book.Book_no=T_borrow.Book_no
WHERE Borrow_date BETWEEN ‘2010-10-31’ AND ‘2010-11-30’

查询出没有还书的借书人姓名；

SELECT Card_name
FROM T_borrow JOIN T_card ON T_borrow.Card_no=T_card.Card_no
WHERE Return_date IS NULL;

查询出手机号为“135”开头的所有借书人姓名；

SELECT Card_name
FROM T_card
WHERE Mobile LIKE ‘135%’;

编写视图查询库存数量小于10册的图书信息；

CREATE VIEW v_bookInfor
AS
SELECT *
FROM T_book
WHERE Qty<10;
SELECT * FROM v_bookInfor;

编写存储过程，查询指定借阅者的借阅次数。

DELIMITER $$
CREATE PROCEDURE p_borrow(IN cardno VARCHAR(20),OUT num INT)
BEGIN
SELECT COUNT(card_no) INTO num
FROM T_borrow
WHERE card_no=cardno;
END $$
DELIMITER ;
CALL p_borrow(‘C1001’,@num);

SELECT @num;

6.试题编号：1-2-6：《学生食堂信息管理系统》项目

创建数据库

CREATE DATABASE CardDB DEFAULT CHARSET utf8;
USE CardDB;

创建数据表T_card、T_add_money、T_consume_money，其中将饭卡表的饭卡编号 (card_id)列设置为标识列，自动从1开始增长。

创建数据表之间的关系

创建表主键（三个表均设置）；

充值金额列（the_money）只能输入50-200之间的数；

录入时间列(register_date)默认值为当前录入时间（三个表均设置）。

CREATE TABLE T_card
(card_id INT PRIMARY KEY AUTO_INCREMENT,
student_id INT NOT NULL,
student_name VARCHAR(50) NOT NULL,
curr_money DOUBLE NOT NULL,
register_date DATETIME DEFAULT NOW()
);
CREATE TABLE T_add_money
(add_id INT PRIMARY KEY AUTO_INCREMENT,
card_id INT NOT NULL,
the_money DOUBLE NOT NULL CHECK (the_money>=50 AND the_money<=200),
register_date DATETIME DEFAULT NOW(),
FOREIGN KEY (card_id) REFERENCES T_card (card_id)
);
CREATE TABLE T_consume_money
(consume_id INT PRIMARY KEY AUTO_INCREMENT,
card_id INT NOT NULL,
the_money DOUBLE NOT NULL,
register_date DATETIME DEFAULT NOW(),
FOREIGN KEY (card_id) REFERENCES T_card (card_id)
);

在每个表中插入1条测试数据。

INSERT T_card (student_id,student_name,curr_money) VALUES (1001,’张三’,100);
INSERT T_add_money (card_id,the_money) VALUES (1,100);
INSERT T_consume_money (card_id,the_money) VALUES (1,50);

查询所有饭卡信息；

SELECT * FROM T_card;

查询所有饭卡的余额之和；

SELECT SUM(curr_money) AS ‘sumOfcurrmoney’ FROM T_card;

查询某日所有饭卡的充值金额之和(比如 2011 年 6 月 2 号总共充值金额是多少元)；

SELECT SUM(the_money) AS ‘sumOfaddmoney’
FROM T_add_money
WHERE YEAR(register_date)=2017 AND MONTH(register_date)=4 AND DAY(register_date)=1;

/*
创建存储过程，根据某个学生的饭卡 ID 上的余额，判断该饭卡是否能进行一定金额的消费。
（比如饭卡 ID 号为 1 的饭卡余额只有 3.5 元，则该饭卡不能购买 5.5 元的中餐）。
存储过程输入参数为饭卡 ID 号，本次消费金额，返回值为 0 或者 1。
*/
DELIMITER $$
CREATE PROCEDURE IscanConsume(IN cardid INT,IN consumemoney DOUBLE,OUT flag INT)
BEGIN
IF (SELECT curr_money FROM T_card WHERE card_id=cardid)>consumemoney THEN
SET flag=1;
ELSE
SET flag=0;
END IF;
END $$
DELIMITER ;
CALL IscanConsume(1,10,@flag);
SELECT @flag;

==========================================================

7.试题编号：1-2-7：《人力资源管理系统》项目

创建数据库

CREATE DATABASE ResourcesDB DEFAULT CHARSET utf8;
USE ResourcesDB;

创建数据表T_staff、T_educational

CREATE TABLE T_staff
(staff_no VARCHAR(16) NOT NULL PRIMARY KEY,
NAME VARCHAR(10) NOT NULL,
ic_card VARCHAR(18) NOT NULL,
age INT NULL,
degree VARCHAR(10),
bithday DATETIME NULL
);
CREATE TABLE T_educational
(id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
staff_no VARCHAR(16) NOT NULL,
degree VARCHAR(10) NOT NULL,
major VARCHAR(30) NOT NULL,
reg_time DATETIME NOT NULL,
length_of_schooling INT NOT NULL,
FOREIGN KEY(staff_no) REFERENCES T_staff(staff_no)
);

向每个表插入 2 条测试数据（样本数据包含下面题目中使用的数据）；

INSERT T_staff VALUES (‘s1′,’王芳’,’430721199702011245′,20,’大学专科’,’1997-02-01′),
(‘s2′,’李丹’,’430721197503017894′,42,’大学本科’,’1975-03-01′);
INSERT T_educational (staff_no,degree,major,reg_time,length_of_schooling) VALUES (‘s1′,’大学专科’,’软件技术’,’2015-09-02′,3),
(‘s2′,’大学本科’,’会计’,’1993-09-01′,4);
GO

查询出 T_staff 表中大于平均年龄的人员名单；

SELECT staff_no,NAME
FROM T_staff
WHERE age>(SELECT AVG(age) FROM T_staff);

查询出入学时间在 2015-9-1 之后的所有人员名单；

SELECT T_staff.staff_no,NAME
FROM T_educational JOIN T_staff ON T_educational.staff_no=T_staff.staff_no
WHERE reg_time>’2015-09-01′;

查询出学习“软件技术”专业的所有人员名单；

SELECT T_staff.staff_no,NAME
FROM T_educational JOIN T_staff ON T_educational.staff_no=T_staff.staff_no
WHERE major=’软件技术’;

创建存储过程，根据入学时间和学制计算每个人的毕业年份数。

DELIMITER $$
CREATE PROCEDURE p_graduate()
BEGIN
SELECT T_staff.staff_no,NAME,YEAR(reg_time)+length_of_schooling AS ‘yearofgra’
FROM T_educational JOIN T_staff ON T_educational.staff_no=T_staff.staff_no;
END $$
DELIMITER ;
CALL p_graduate();

==========================================================

8.试题编号：1-2-8：《人力资源管理系统》项目

创建数据库

CREATE DATABASE SalaryDB DEFAULT CHARSET utf8;
USE SalaryDB;

创建数据表

CREATE TABLE T_Department
(dep_no VARCHAR(4) NOT NULL PRIMARY KEY,
dep_name VARCHAR(20) NOT NULL,
dep_desc VARCHAR(50) NULL
);
CREATE TABLE T_Employee
(emp_no VARCHAR(16) NOT NULL PRIMARY KEY,
dep_no VARCHAR(4) NOT NULL,
NAME VARCHAR(10) NOT NULL,
post VARCHAR(20) NOT NULL,
work_time DATETIME NOT NULL,
salary DOUBLE NOT NULL,
bonus DOUBLE NULL,
FOREIGN KEY(dep_no) REFERENCES T_Department(dep_no)
);

向每个表插入 2 条测试数据（样本数据包含下面题目中使用的数据）；

INSERT T_Department VALUES (‘d003′,’技术部’,’主管技术’),
(‘d004′,’后勤部’,’提供后勤保障’);
INSERT T_Employee VALUES (‘e005′,’d003′,’李宁’,’经理’,’1995-01-01′,10000,2000),
(‘e006′,’d004′,’张宁’,’职员’,’1997-01-01′,2000,0);

将所有员工的工资上浮 10%；

UPDATE T_Employee
SET salary=salary*1.1;

查询出部门编号为“d001”的员工姓名、岗位、参加工作时间及工资。

SELECT NAME,post,work_time,salary
FROM T_Employee
WHERE dep_no=’d001′;

查询出某一特定岗位的平均工资；

SELECT AVG(salary) AS ‘AvgOfSalary’
FROM T_Employee
WHERE post=’经理’;

创建视图，计算出每个员工的年薪，并取列名为 SalaryOfYear，要求输出员工姓名及年薪；

CREATE VIEW v_salary
AS
SELECT NAME,12*(salary+bonus) AS ‘SalaryOfYear’
FROM T_Employee;
SELECT * FROM v_salary;

创建存储过程，显示工资高于 3000 或岗位为“经理”的所有员工的姓名、岗位、工资及部门名称。

DELIMITER $$
CREATE PROCEDURE p_salary()
BEGIN
SELECT NAME,post,salary,dep_name
FROM T_Department JOIN T_Employee ON T_Department.dep_no=T_Employee.dep_no
WHERE salary>3000 OR post LIKE ‘%经理’;
END $$
DELIMITER ;

CALL p_salary();

9.试题编号：1-2-9：《人力资源管理系统》项目

创建数据库

CREATE DATABASE SalaryDB DEFAULT CHARSET utf8;
USE SalaryDB;

创建数据表

向每个表插入2 条测试数据（样本数据包含下面题目中使用的数据）；

INSERT T_Department VALUES (‘d004′,’软件开发部’,’主管产品开发’),
(‘d005′,’后勤部’,’主管后勤服务’);
INSERT T_Employee VALUES (‘e014′,’d004′,’李明’,’经理’,’1990-01-01′,5000,1000),
(‘e015′,’d004′,’高明’,’经理助理’,’1990-01-01′,3000,500);

查询出所有已有的岗位，要求取出重复项；

SELECT DISTINCT post
FROM T_Employee;

查询出每个部门每种岗位的平均工资和最高工资。

SELECT dep_no,post,AVG(salary) AS ‘avgofsalary’,MAX(salary) AS ‘maxofsalary’
FROM T_Employee
GROUP BY dep_no,post
ORDER BY dep_no;

查询出部门名称为“软件开发部”的所有员工的姓名、工资、补助；

SELECT NAME,salary,bonus
FROM T_Department JOIN T_Employee ON T_Department.dep_no=T_Employee.dep_no
WHERE dep_name=’软件开发部’;

创建视图，显示所有没有补助的员工的姓名；

CREATE VIEW v_name
AS
SELECT NAME
FROM T_Employee
WHERE bonus IS NULL OR bonus=0;
SELECT * FROM v_name;

创建存储过程，显示平均工资低于3500 的部门编号、平均工资、最高工资，要求以平均工资升序排序。

DELIMITER $$
CREATE PROCEDURE p_dep()
BEGIN
SELECT dep_no,AVG(salary) AS ‘avgofsalary’,MAX(salary) AS ‘avgofsalary’
FROM T_Employee
GROUP BY dep_no
HAVING AVG(salary)<3500
ORDER BY AVG(salary);
END $$
DELIMITER ;

CALL p_dep();

10.试题编号：1-2-10：《银行信贷管理系统》项目

创建数据库BankCreditLoanDB

CREATE DATABASE BankCreditLoanDB DEFAULT CHARSET utf8;
USE BankCreditLoanDB;

创建数据表T_customer_info、T_cust_credit_level、T_his_cust_credit_level。其中,客户信用等级历史表中的序号为自动增长字段。

CREATE TABLE T_customer_info
(cust_id CHAR(10) NOT NULL PRIMARY KEY,
cust_name VARCHAR(60) NOT NULL,
legal_name VARCHAR(10) NOT NULL,
reg_address VARCHAR(60) NOT NULL,
post_code CHAR(6) NOT NULL
);
CREATE TABLE T_cust_credit_level
(cust_id CHAR(10) NOT NULL PRIMARY KEY,
credit_level CHAR(2) NOT NULL DEFAULT ’01’,
begin_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
FOREIGN KEY (cust_id) REFERENCES T_customer_info (cust_id)
);
CREATE TABLE T_his_cust_credit_level
(id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
cust_id CHAR(10) NOT NULL,
credit_level CHAR(2) NOT NULL,
begin_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
FOREIGN KEY (cust_id) REFERENCES T_customer_info (cust_id)
);

1、根据对逻辑数据模型的理解，分别向三个表中插入一条测试数据；

INSERT T_customer_info VALUES (‘C000000001′,’德华影视传播公司’,’刘德华’,’常德职业技术学院’,’415000′);
INSERT T_cust_credit_level VALUES (‘C000000001′,’A1′,’2009-01-01′,’2012-12-12’);
INSERT T_his_cust_credit_level (cust_id,credit_level,begin_date,end_date) VALUES (‘C000000001′,’A1′,’2009-01-01′,’2012-12-12’);

2、查询客户名称为“XX 公司”的信用等级（说明：“XX 公司”为插入测试数据中的公司名称）；

SELECT credit_level
FROM T_customer_info JOIN T_cust_credit_level
ON T_customer_info.cust_id=T_cust_credit_level.cust_id
WHERE cust_name=’德华影视传播公司’;

3、创建视图 V_his_cust_credit_level 用于查询客户的信用级别历史记录，视图列名显示为：客户名称、信用级别、评级有效起始日期、评级有效中止日期；

CREATE VIEW V_his_cust_credit_level
AS
SELECT cust_name AS ‘客户名称’,credit_level AS ‘信用等级’,
begin_date AS ‘评级有效起始日期’,end_date AS ‘评级有效终止日期’
FROM T_customer_info JOIN T_his_cust_credit_level
ON T_customer_info.cust_id=T_his_cust_credit_level.cust_id;
SELECT * FROM V_his_cust_credit_level;

4、创建带输入参数的存储过程P_cust_credit_level，根据输入参数更新客户信用等级表，并将更新前的记录插入到客户信用等级历史表。*

DELIMITER $$
CREATE PROCEDURE P_cust_credit_level(custid CHAR(10),creditlevel CHAR(2),begindate DATETIME,enddate DATETIME)
BEGIN
INSERT T_his_cust_credit_level (cust_id,credit_level,begin_date,end_date)
(SELECT * FROM T_cust_credit_level WHERE cust_id=custid);
UPDATE T_cust_credit_level
SET credit_level=creditlevel,begin_date=begindate,end_date=enddate
WHERE cust_id=custid;
END $$
DELIMITER ;

CALL P_cust_credit_level(‘C000000001′,’V1′,’2013-01-01′,’2016-12-12’);