数据库期中考试脚本题目

1、1. 已存在名为employees的数据表：Drop table if exist employees;CREATE TABLE employees (emp_no(11) NOT NULL,birth_date date NOT NULL,_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);INSERTINSERT INSERT INSERT INSERT INSERT INSERT INS

2、ERT INSERT INSERTINSERTO employees VALUES(10001,1953-09-02,Gei,Facello,M,1986-06-26);O employees VALUES(10002,1964-06-02,Bezalel,Simmel,F,1985-11-21);O employees VALUES(10003,1959-12-03,Parto,Bamford,M,1986-08-28);O employees VALUES(10004,1954-05-01,Chirstian,Koblick,M,1986-12-01); O employees VALUE

3、S(10005,1955-01-21,Kyoichi,Maliniak,M,1989-09-12); O employees VALUES(10006,1953-04-20,Anneke,Preusig,F,1989-06-02);O employees VALUES(10007,1957-05-23,Tzvetan,Zielinski,F,1989-02-10); O employees VALUES(10008,1958-02-19,Saniya,Kalloufi,M,1994-09-15); O employees VALUES(10009,1952-04-19,Sumant,Peac,

4、F,1985-02-18);O employees VALUES(10010,1963-06-01,Duangkaew,Piveteau,F,1989-08-24); O employees VALUES(10011,1953-11-07,Mary,Sluis,F,1990-01-22);（1） 请给出以下SQL 语句的执行结果：SELECT * from employees WHERE hire_date =(SELECT max(hire_date) FROM employees);（2） 请给出以下SQL 语句的执行结果：SELECT emp_no,FROM employees_name

5、, last_name, genderWHERE emp_no % 2 = 1 AND last_name != MaryORDER BY hire_date desc;（3） 请给出以下SQL 语句的执行结果：SELECT_nameFROM employeesORDER BY substr(_name,length(_name)-1);2. 已存在名为employees的数据表：以及名为salaries的数据表：Drop table if exist employees;CREATE TABLE employees (emp_no(11) NOT NULL,birth_date date N

6、OT NULL,_name varchar(14) NOT NULL,10004last_name varchar(16) NOT NULL,gender char(1) NOT NULL,hire_date date NOT NULL, PRIMARY KEY (emp_no);INSERTINSERT INSERTO employees VALUES(10002,1964-06-02,Bezalel,Simmel,F,1985-11-21);O employees VALUES(10003,1959-12-03,Parto,Bamford,M,1986-08-28);O employees

7、 VALUES(10004,1953-04-20,Anneke,Preusig,F,1989-06-02);Drop table if exist salaries ;CREATE TABLE salaries (emp_nosalary(11) NOT NULL,(11) NOT NULL,from_date date NOT NULL,to_date date NOT NULL,PRIMARY KEY (emp_no,from_date);INSERTOsalariesVALUES(10002,72527,1996-08-03,1997-08-03);INSERTOsalariesVALU

8、ES(10002,72527,1997-08-03,1998-08-03); INSERTO salaries VALUES(10002,72527,1998-08-03,1999-08-03);INSERTOsalariesVALUES(10002,72527,1999-08-03,2000-08-02);INSERTOsalariesVALUES(10002,72527,2000-08-02,2001-08-02); INSERTO salaries VALUES(10002,72527,2001-08-02,9999-01-01);INSERTOsalariesVALUES(10003,

9、40006,1995-12-03,1996-12-02);INSERTOsalariesVALUES(10003,43616,1996-12-02,1997-12-02); INSERTO salaries VALUES(10003,43466,1997-12-02,1998-12-02);INSERTOsalariesVALUES(10003,43636,1998-12-02,1999-12-02);INSERTOsalariesVALUES(10003,43478,1999-12-02,2000-12-01); INSERTO salaries VALUES(10003,43699,200

10、0-12-01,2001-12-01);INSERTOsalariesVALUES(10003,43311,2001-12-01,9999-01-01);INSERTOsalariesVALUES(10004,40054,1986-12-01,1987-12-01); INSERTO salaries VALUES(10004,42283,1987-12-01,1988-11-30);INSERTOsalariesVALUES(10004,42542,1988-11-30,1989-11-30);INSERTOsalariesVALUES(10004,46065,1989-11-30,1990

11、-11-30); INSERTO salaries VALUES(10004,48271,1990-11-30,1991-11-30);INSERTOsalariesVALUES(10004,50594,1991-11-30,1992-11-29);INSERTOsalariesVALUES(10004,52119,1992-11-29,1993-11-29); INSERTO salaries VALUES(10004,54693,1993-11-29,1994-11-29);INSERTOsalariesVALUES(10004,58326,1994-11-29,1995-11-29);I

12、NSERTOsalariesVALUES(10004,60770,1995-11-29,1996-11-28); INSERTO salaries VALUES(10004,62566,1996-11-28,1997-11-28);INSERTOsalariesVALUES(10004,64340,1997-11-28,1998-11-28);INSERTOsalariesVALUES(10004,67096,1998-11-28,1999-11-28); INSERTO salaries VALUES(10004,69722,1999-11-28,2000-11-27);INSERTOsal

13、ariesVALUES(10004,70698,2000-11-27,2001-11-27);INSERTOsalariesVALUES(10004,74057,2001-11-27,9999-01-01);（1） 请给出以下SQL 语句的执行结果：SELECT (MAX(salary)-MIN(salary) AS growth FROM salaries WHERE emp_no = 10003;（2） 请给出以下SQL 语句的执行结果：SELECT e.emp_no, MAX(s.salary) AS salary, e.last_name, e. FROM employees AS e

14、 INNER JOIN salaries AS sON e.emp_no = s.emp_no_nameWHERE s.to_date = 9999-01-01AND s.salary NOT IN (SELECT MAX(salary) FROM salaries WHERE to_date = 9999-01-01);（3）请给出以下SQL 语句的执行结果：SELECT s1.emp_no, s1.salary, COUNT(DISTINCT s2.salary) AS r FROM salaries AS s1, salaries AS s2WHERE s1.to_date = 9999

15、-01-01AND s2.to_date = 9999-01-01 AND s1.salary = s2.salaryGROUP BY s1.emp_noORDER BY s1.salary DESC, s1.emp_no ASC;（4）请给出以下SQL 语句的执行结果SELECT a.emp_no, a.salary - b.salary growth FROM salaries a, salaries bWHERE a.emp_no = b.emp_no and a.to_date = 9999-01-01 AND b.from_date = (SELECT min(from_date) FROM salariesWHERE emp_no = b.emp_no) ORDER BY growth;（5）请编写一条 SQL 语句，将所有