数据库模拟测试

1.	【MySQL】SQL存储过程：查询订单 #创建存储过程：total_order(IN orderid INT , OUT totals NUMERIC(9,2), OUT cid INT) 要求：根据输入的订单编号(order_id)，返回订单总金额(total_money),顾客编号(customer_id)。 订单表：orders如下：

2.

【MySQL】创建成绩表插入触发器 #创建触发器，插入学生成绩时，如果成绩不在0-100之间，设置成绩为0。 1、student（学生表）： SNO学号CHAR(7) SNAME姓名CHAR(10) SSEX性别CHAR(2) SAGE年龄SMALLINT SDEPT所在系 VARCHAR(20) 2、course（课程表） CNO课程号CHAR(10)   CNAME课程名VARCHAR(20) CCREDIT学分SMALLINT SEMSTER学期SMALLINT PERIOD学时SMALLINT 3、sc（选课表） SNO 学号CHAR(7) CNO  课程号CHAR(10) GRADE  成绩 SMALLINT [注意：SQL表名请用小写]

delimiter $$
create trigger test before insert on sc for each row
begin
if new.grade<0 or new.grade>100 then set new.grade=0;
end if;
end $$

3.

【MySQL】查找描述信息中包括robot的电影对应的分类名称以及电影数目 film表 字段  说明  film_id  电影id  title  电影名称  description  电影描述信息 CREATE TABLE IF NOT EXISTS film (  film_id smallint(5)  NOT NULL DEFAULT '0',  title varchar(255) NOT NULL,  description text,  PRIMARY KEY (film_id)); category表 字段  说明  category_id  电影分类id  name  电影分类名称  last_update  电影分类最后更新时间 CREATE TABLE category  (  category_id  tinyint(3)  NOT NULL ,  name  varchar(25) NOT NULL, `last_update` timestamp,  PRIMARY KEY ( category_id )); film_category表 字段  说明  film_id  电影id  category_id  电影分类id  last_update  电影id和分类id对应关系的最后更新时间 CREATE TABLE film_category  (  film_id  smallint(5)  NOT NULL,  category_id  tinyint(3)  NOT NULL, `last_update` timestamp); 查找描述信息中包括robot的电影对应的分类名称以及电影数目，而且还需要该分类对应电影数量>=5部

SELECT c.name AS name, COUNT(f.film_id) AS amount
FROM film AS f, film_category AS fc, category AS c,
(SELECT category_id FROM film_category GROUP BY category_id HAVING COUNT(category_id) >= 5) AS cc
WHERE f.description LIKE '%robot%'
AND f.film_id = fc.film_id
AND fc.category_id = c.category_id
AND c.category_id = cc.category_id

4.

【MySQL】统计各个部门对应员工涨幅的次数总和 统计各个部门对应员工涨幅的次数总和，给出部门编码dept_no、部门名称dept_name以及次数sum CREATE TABLE `departments` ( `dept_no` char(4) NOT NULL, `dept_name` varchar(40) NOT NULL, PRIMARY KEY (`dept_no`)); CREATE TABLE `dept_emp` ( `emp_no` int(11) NOT NULL, `dept_no` char(4) NOT NULL, `from_date` date NOT NULL, `to_date` date NOT NULL, PRIMARY KEY (`emp_no`,`dept_no`)); CREATE TABLE `salaries` ( `emp_no` int(11) NOT NULL, `salary` int(11) NOT NULL, `from_date` date NOT NULL, `to_date` date NOT NULL, PRIMARY KEY (`emp_no`,`from_date`)); 输入描述: 无 输出描述: dept_no dept_name sum d001 Marketing 24 d002 Finance 14 d003 Human Resources 13 d004 Production 24 d005 Development 25 d006 Quality Management 25

SELECT dm.dept_no, dm.dept_name, COUNT(s.salary) AS sum 
FROM salaries AS s 
	JOIN dept_emp AS d 
	ON s.emp_no = d.emp_no
	JOIN departments AS dm 
	ON d.dept_no = dm.dept_no
GROUP BY d.dept_no;

5.

【MySQL】查找各个部门当前领导当前薪水详情以及其对应部门编号dept_no 题目描述： 查找各个部门当前(to_date='9999-01-01')领导当前薪水详情以及其对应部门编号dept_no CREATE TABLE `dept_manager` ( `dept_no` char(4) NOT NULL, `emp_no` int(11) NOT NULL, `from_date` date NOT NULL, `to_date` date NOT NULL, PRIMARY KEY (`emp_no`,`dept_no`)); CREATE TABLE `salaries` ( `emp_no` int(11) NOT NULL, `salary` int(11) NOT NULL, `from_date` date NOT NULL, `to_date` date NOT NULL, PRIMARY KEY (`emp_no`,`from_date`)); 输入描述: 无 输出描述: emp_no salary from_date to_date dept_no 10002 72527 2001-08-02 9999-01-01 d001 10004 74057 2001-11-27 9999-01-01 d004 10005 94692 2001-09-09 9999-01-01 d003 10006 43311 2001-08-02 9999-01-01 d002 10010 94409 2001-11-23 9999-01-01 d006

select s.* , d.dept_no from salaries as s
inner join dept_manager as d
on s.emp_no=d.emp_no
where s.to_date='9999-01-01' and d.to_date='9999-01-01';

6.

【MYSQL】2. 查找员工信息 II 题目描述： 查找入职员工时间排名倒数第三的员工编码 CREATE TABLE employees ( emp_no int NOT NULL, birth_date date NOT NULL, first_name varchar(14) NOT NULL, last_name varchar(16) NOT NULL, gender char(1) NOT NULL, hire_date date NOT NULL, PRIMARY KEY (emp_no) ) 输入描述: 无 输出描述: emp_no birth_date first_name last_name gender hire_date 10005 1955-01-21 Kyoichi Maliniak M 1989-09-12

select  e.emp_no
from (
	select  * 
	from employees 
	order by hire_date desc limit 3
) 
    as e 
order by hire_date 
  limit 1

7.

【MYSQL】53. 换座位 小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。 其中纵列的 id 是连续递增的 小美想改变相邻俩学生的座位。 你能不能帮她写一个 SQL 语句来输出小美想要的结果呢？ 示例： +---------+---------+ |    id   | student | +---------+---------+ |    1    | Abbot   | |    2    | Doris   | |    3    | Emerson | |    4    | Green   | |    5    | Jeames  | +---------+---------+ 假如数据输入的是上表，则输出结果如下： +---------+---------+ |    id   | student | +---------+---------+ |    1    | Doris   | |    2    | Abbot   | |    3    | Green   | |    4    | Emerson | |    5    | Jeames  | +---------+---------+ 注意： 如果学生人数是奇数，则不需要改变最后一个同学的座位。

SELECT * FROM(
    SELECT id-1 AS id,student FROM seat WHERE id%2=0
    UNION
    SELECT id+1 AS id,student FROM seat WHERE id%2=1 AND (id+1) <= (SELECT COUNT(*) FROM seat)
    UNION
    SELECT id AS id,student FROM seat WHERE id%2=1 AND (id+1) > (SELECT COUNT(*) FROM seat)
) AS T1
ORDER BY id ASC

8.

【MySQL】如何获取emp_v和employees有相同的数据no 存在如下的视图： create view emp_v as select * from employees where emp_no >10005; 如何获取emp_v和employees有相同的数据？ CREATE TABLE `employees` ( `emp_no` int(11) NOT NULL, `birth_date` date NOT NULL, `first_name` varchar(14) NOT NULL, `last_name` varchar(16) NOT NULL, `gender` char(1) NOT NULL, `hire_date` date NOT NULL, PRIMARY KEY (`emp_no`)); 输出格式: emp_no birth_date first_name last_name gender hire_date 10006 1953-04-20 Anneke Preusig F 1989-06-02 10007 1957-05-23 Tzvetan Zielinski F 1989-02-10 10008 1958-02-19 Saniya Kalloufi M 1994-09-15 10009 1952-04-19 Sumant Peac F 1985-02-18 10010 1963-06-01 Duangkaew Piveteau F 1989-08-24 10011 1953-11-07 Mary Sluis F 1990-01-22

SELECT * FROM emp_v;

9.	【MySQL】创建一个actor_name表 对于如下表actor，其对应的数据为: actor_id  first_name  last_name  1  PENELOPE  GUINESS 2  NICK  WAHLBERG 创建一个actor_name表，将actor表中的所有first_name以及last_name导入该表。 actor_name表结构如下： 列表  类型  是否为NULL  含义  first_name  varchar(45)  not null  名字  last_name  varchar(45)  not null  姓氏

create table actor_name(first_name varchar(45) not null,
last_name varchar(45) not null);
insert into actor_name select first_name, last_name from actor;

10.	【MYSQL】39. 创建演员名表 对于如下表actor，其对应的数据为: actor_id  first_name  last_name  1  PENELOPE  GUINESS 2  NICK  WAHLBERG 创建一个actor_name表，将actor表中的所有first_name以及last_name导入该表。 actor_name表结构如下： 列表  类型  是否为NULL  含义  first_name  varchar(45)  not null  名字  last_name  varchar(45)  not null  姓氏

create table actor_name(
	first_name varchar(45) not null,
	last_name varchar(45) not null
);
insert into actor_name select first_name,last_name from actor;