数据库练习SQL

1. 查找最晚入职员工的所有信息
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`));

【解答】select * from employees order by hire_date desc limit 1;

 

2. 查找入职员工时间排名倒数第三的员工所有信息
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`));

【解答】select emp_no,birth_date,first_name,last_name,gender,hire_date

from employees

order by hire_date desc limit 2,1

 

3.查找各个部门当前(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`));

 

【解析】

select salaries.*,dept.dept_no

         from salaries salaries left join dept_manager dept on

   salaries.emp_no=dept.emp_no

   where salaries.to_date='9999-01-01' and dept.to_date='9999-01-01’

 

 

 

 

 

 

 

4.查找所有已经分配部门的员工的last_name和first_name

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`));

 

 

 

【解析】

select emp.last_name,emp.first_name,dep.dept_no

   from employees emp ,dept_emp dep

Where  emp.emp_no = dep.emp_no

 

5.查找所有员工入职时候的薪水情况，给出emp_no以及salary， 并按照emp_no进行逆序

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`));

【解析】

select  t.emp_no,p.salary  

   from employees t  inner join salaries p

   on t.emp_no=p.emp_no and t.hire_date=p.from_date order by  p.emp_no  desc

 

6.查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t

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`));

【解析】 select emp_no,count(emp_no) as t from salaries group by emp_no having t>15

 

 

 

 

 

 

7.找出所有员工当前(to_date='9999-01-01')具体的薪水salary情况，对于相同的薪水只显示一次,并按照逆序显示

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`));

【解析】

Select  distinct(salary) from salaries where o_date='9999-01-01'

 

 

8.获取所有部门当前manager的当前薪水情况，给出dept_no, emp_no以及salary，当前表示to_date='9999-01-01'

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`));

【解析】

select m.dept_no,m.emp_no,s.salary

    from dept_manager m inner join salaries s

on m.emp_no=s.emp_no and m.to_date=s.to_date and  s.to_date='9999-01-01'

 

 

 

 

9.获取所有非manager的员工emp_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`));

 

 

 

【解析】select emp_no from employees not in (

Select emp_no from dept_manager )

 

 

 

 

 

10.获取所有员工当前的manager，如果当前的manager是自己的话结果不显示，当前表示to_date='9999-01-01'。

结果第一列给出当前员工的emp_no,第二列给出其manager对应的manager_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`));

 

【解析】

SELECT de.emp_no,dm.emp_no AS manager_no

FROM dept_manager AS dm,dept_emp AS de

WHERE de.emp_no <> dm.emp_no AND de.dept_no = dm.dept_no

AND dm.to_date='9999-01-01';

 

11.获取所有部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary

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`));

【解析】

A  select a.dept_no,a.emp_no,max(a.salary)  from(

select t.emp_no,t.dept_no,s.salary

 from dept_emp t,salaries s

 where t.emp_no==s.emp_no and t.to_date = '9999-01-01' AND s.to_date = '9999-01-01') a group by a.dept_no

 

B  select  d.dept_no,d.emp_no,s.salary

From dept_emp d,(select emp_no ,salary from salaries order by salary desc limit 1) s

Where d.emp_no=s.emp_no

 

12.从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。

CREATE TABLE IF NOT EXISTS "titles" (

`emp_no` int(11) NOT NULL,

`title` varchar(50) NOT NULL,

`from_date` date NOT NULL,

`to_date` date DEFAULT NULL);

【解析】select title, count(distinct emp_no) as t from titles group by titles  having t>=2

 

13.查找employees表所有emp_no为奇数，且last_name不为Mary的员工信息，并按照hire_date逆序排列

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`));

【解析】select * from employees t where t.last_name!=’Mary’  and emp_no%2==1

 order by hire_date desc

 

14.统计出当前各个title类型对应的员工当前薪水对应的平均工资。结果给出title以及平均工资avg。

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`));

【解析】

A. select a.title,avg(a.salary) as avg

 from(

select t.emp_no,s.salary,t.title

 from titles t,salaries s

 where t.emp_no=s.emp_no  and t.to_date = '9999-01-01' AND s.to_date = '9999-01-01') a  group by a.title

 

 

15获取当前（to_date='9999-01-01'）薪水第二多的员工的emp_no以及其对应的薪水salary

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`));

【解析】select  emp_no, salary

 from salaries where to_date=’9999-01-01’ order by salary limit 1,1

关于关键词limit的描述：limit是mysql的语法select * from table limit m,n其中m是指记录开始的index，从0开始，表示第一条记录n是指从第m+1条开始，取n条;

 

 

16.查找当前薪水(to_date='9999-01-01')排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不准使用order by

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`));

【解析】

A. select s.emp_no,max(s.salary),e.last_name,e.first_name

 from employees e,salaries s

 where  s.emp_no=e.emp_no  and s.to_date="9999-01-01"

 and s.salary !=(select max(salary) from salaries)    and e.emp_no=s.emp_no;

B. select  e.emp_no,a.salary

 from employee e,(

select  emp_no, Max(salary) as salary   from salaries

 where salary !=(

Select  Max(salary)  from salaries) ) a

Where e.emp_no=a.emp_no  and  to_date="9999-01-01";

 

17.查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工

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`));

 

 

 

 

 

 

【解析】

select e.last_name,e.first_name,de.dept_name from  employees e

left join dept_emp t on  t.emp_no=e.emp_no  

left join    departments de on de.dept_no=t.dept_no

 

 

18.查找员工编号emp_now为10001其自入职以来的薪水salary涨幅值growth

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`));

【解析】

Select emp_no,(Max(salary)-Min(salary)) as growth

 from salaries where emp_no=’10001’

 

19查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_noy以及其对应的薪水涨幅growth，并按照growth进行升序

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`));

【解析】

SELECT sCurrent.emp_no, (sCurrent.salary-sStart.salary) AS growth

FROM (SELECT s.emp_no, s.salary FROM employees e, salaries s WHERE e.emp_no = s.emp_no AND s.to_date = '9999-01-01') AS sCurrent,        --现在的工资

     (SELECT s.emp_no, s.salary FROM employees e, salaries s WHERE e.emp_no = s.emp_no AND s.from_date = e.hire_date) AS sStart   --原始的工资

WHERE sCurrent.emp_no = sStart.emp_no

ORDER BY growth

 

20.统计各个部门对应员工涨幅的次数总和，给出部门编码dept_no、部门名称dept_name以及次数sum

CREATE TABLE `departments` (

`dept_no` char(4) NOT NULL,

`dept_name` varchar(40) NOT NULL,

PRIMARY KEY (`dept_no`));

【解析】

SELECT

de.dept_no, dp.dept_name, COUNT(s.salary) AS sum

FROM ( dept_emp AS de INNER JOIN salaries AS s ON de.emp_no = s.emp_no)

INNER JOIN departments AS dp ON de.dept_no = dp.dept_no

GROUP BY de.dept_no

 

 

21.对所有员工的当前(to_date='9999-01-01')薪水按照salary进行按照1-N的排名，相同salary并列且按照emp_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`));

【解析】

SELECT s1.emp_no, s1.salary, COUNT(DISTINCT s2.salary) AS rank

FROM salaries AS s1, salaries AS s2

WHERE s1.to_date = '9999-01-01'  AND s2.to_date = '9999-01-01' AND s1.salary <= s2.salary

GROUP BY s1.emp_no

ORDER BY s1.salary DESC, s1.emp_no ASC

 

22.获取所有非manager员工当前的薪水情况，给出dept_no、emp_no以及salary ，当前表示to_date='9999-01-01'

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 `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`));

【解析】

  Select e.dept_no,e.emp_no,s.salary

 From dept_emp e

Inner join salaries s

 on e.emp_no=s.emp_no and s.todate=’9999-01-01’

Where e.emp_no not in

(select distinct(emp_no) from dept_manager  where todate=’9999-01-01’)

 

 

 

 

 

23. 获取员工其当前的薪水比其manager当前薪水还高的相关信息，当前表示to_date='9999-01-01',
结果第一列给出员工的emp_no，
第二列给出其manager的manager_no，
第三列给出该员工当前的薪水emp_salary,
第四列给该员工对应的manager当前的薪水manager_salary
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`));

 

 

 

 

 

 

【解析】

select w.emp_no as emp_no,p.emp_no as manager_no,w.salary as emp_salary,p.salary as manager_salary

from

 (select d.emp_no,s.salary,d.dept_no from dept_emp d inner join  salaries s on d.emp_no=s.emp_no and  s.to_date='9999-01-01') w,

 (select m.emp_no,s.salary,m.dept_no from dept_manager m inner join salaries s on m.emp_no=s.emp_no and s.to_date='9999-01-01') p

 where w.dept_no=p.dept_no and w.salary > p.salary

 

24. 汇总各个部门当前员工的title类型的分配数目，结果给出部门编号dept_no、dept_name、其当前员工所有的title以及该类型title对应的数目count
CREATE TABLE `departments` (
`dept_no` char(4) NOT NULL,
`dept_name` varchar(40) NOT NULL,
PRIMARY KEY (`dept_no`));

CREATE TABLE IF NOT EXISTS "titles" (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);

【解析】select a.dept_no,

       (select dept_name from departments where dept_no = a.dept_no) as 'dept_name',

       b.title,

       count(*) as 'count'

  from dept_emp as a inner join titles as b on a.emp_no = b.emp_no--统计部门的tile的各个中类型的个数

 where a.to_date = '9999-01-01'

   and b.to_date = '9999-01-01'

 group by a.dept_no, b.title--根据部门代码和title种类进行分类

 order by a.dept_no a.dept_no

25.给出每个员工每年薪水涨幅超过5000的员工编号emp_no、薪水变更开始日期from_date以及薪水涨幅值salary_growth，并按照salary_growth逆序排列。

提示：在sqlite中获取datetime时间对应的年份函数为strftime('%Y', to_date)

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`));

【解析】：

SELECT s2.emp_no, s2.from_date, (s2.salary - s1.salary) AS salary_growth

FROM salaries AS s1, salaries AS s2

WHERE s1.emp_no = s2.emp_no

AND salary_growth > 5000

AND (strftime("%Y",s2.to_date) - strftime("%Y",s1.to_date) = 1

     OR strftime("%Y",s2.from_date) - strftime("%Y",s1.from_date) = 1 )

ORDER BY salary_growth DESC

 

26.查找描述信息中包括robot的电影对应的分类名称以及电影数目，而且还需要该分类对应电影数量>=5部

 

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));

 

CREATE TABLE category  (

category_id  tinyint(3)  NOT NULL ,

name  varchar(25) NOT NULL, `last_update` timestamp,

PRIMARY KEY ( category_id ));

film_category表

CREATE TABLE film_category  (

film_id  smallint(5)  NOT NULL,

category_id  tinyint(3)  NOT NULL, `last_update` timestamp);

 

 

【解析】SELECT c.name, COUNT(fc.film_id) FROM

 (select category_id, COUNT(film_id) AS category_num FROM  film_category GROUP BY category_id HAVING count(film_id)>=5) AS cc,

 film AS f, film_category AS fc, category AS c

WHERE  f.description LIKE '%robot%'

AND f.film_id = fc.film_id

AND c.category_id = fc.category_id

AND c.category_id=cc.category_id  

 

27.使用join查询方式找出没有分类的电影id以及名称

 

【film表】

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表】

CREATE TABLE category  (

category_id  tinyint(3)  NOT NULL ,

name  varchar(25) NOT NULL, `last_update` timestamp,

PRIMARY KEY ( category_id ));

【film_category表】

CREATE TABLE film_category  (

film_id  smallint(5)  NOT NULL,

category_id  tinyint(3)  NOT NULL, `last_update` timestamp);

 

【解析】

Select a.id,a.title from(

Select f.id,f.titile,fc.category_id

 From film f  left join film_category fc on f.film_id=cf.film_id ) a

Where  a.category_id is null;

 

28.使用子查询的方式找出属于Action分类的所有电影对应的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));

 

 

 

 

CREATE TABLE category  (

category_id  tinyint(3)  NOT NULL ,

name  varchar(25) NOT NULL, `last_update` timestamp,

PRIMARY KEY ( category_id ));

 

 

CREATE TABLE film_category  (

film_id  smallint(5)  NOT NULL,

category_id  tinyint(3)  NOT NULL, `last_update` timestamp);

【解析】

A.

select f.title,f.description

from film as f inner join film_category as fc on f.film_id = fc.film_id

               inner join category as c on c.category_id = fc.category_id

where c.name = 'Action';

B.

select title, description

  from film  where film_id in (select fc.film_id

                          from film_category fc

                         where fc.category_id in

                               (select c.category_id

                                  from category c

                                 where c.name = 'Action'))

29.获取select * from employees对应的执行计划

【解析】

EXPLAIN SELECT * FROM employees

30.将employees表的所有员工的last_name和first_name拼接起来作为Name，中间以一个空格区分

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`));

【解析】SELECT last_name||" "||first_name AS Name FROM employees

 

31.创建一个actor表，包含如下列信息

列表  类型  是否为NULL  含义

actor_id  smallint(5)  not null  主键id

first_name  varchar(45)  not null  名字

last_name  varchar(45)  not null  姓氏

last_update  timestamp  not null  最后更新时间，默认是系统的当前时

 

【解析】

create table actor(

   actor_id  smallint(5) not null PRIMARY KEY,

   first_name varchar(45) not null,

   last_name varchar(45) not null,

   last_update timestamp not null DEFAULT (datetime('now','localtime'))

)

 

32.对于表actor批量插入如下数据

CREATE TABLE IF NOT EXISTS actor (

actor_id smallint(5) NOT NULL PRIMARY KEY,

first_name varchar(45) NOT NULL,

last_name varchar(45) NOT NULL,

last_update timestamp NOT NULL DEFAULT (datetime('now','localtime')))

 

actor_id	first_name	last_name	last_update
1	PENELOPE	GUINESS	2006-02-15 12:34:33
2	NICK	WAHLBERG	2006-02-15 12:34:33

 

【解析】

Insert into actor(actor_id,first_name,last_name,last_update)

Values (1, 'PENELOPE', 'GUINESS', '2006-02-15 12:34:33'),

(2, 'NICK', 'WAHLBERG', '2006-02-15 12:34:33');

 

33.对于表actor批量插入如下数据,如果数据已经存在，请忽略，不使用replace操作

【解析】

 insert or ignore into actor values ('3','ED','CHASE','2006-02-15 12:34:33')

-- insert or replace：如果不存在就插入，存在就更新

-- insert or ignore： 如果不存在就插入，存在就忽略

 

 

33.创建一个actor_name表，将actor表中的所有first_name以及last_name导入改表

【解析】create table actor_name  as  select first_name,last_name from actor

 

 

 

 

 

 

 

 

 

 

34.针对如下表actor结构创建索引：

CREATE TABLE IF NOT EXISTS actor (

actor_id smallint(5) NOT NULL PRIMARY KEY,

first_name varchar(45) NOT NULL,

last_name varchar(45) NOT NULL,

last_update timestamp NOT NULL DEFAULT (datetime('now','localtime')))

对first_name创建唯一索引uniq_idx_firstname，对last_name创建普通索引idx_lastname

【解析】create unique index  uniq_idx_firstname on actor(first_name);

create index idx_lastname on actor(last_name);

 

35.针对actor表创建视图actor_name_view，只包含first_name以及last_name两列，并对这两列重新命名，fist_name为first_name_v，last_name修改为last_name_v：

CREATE TABLE IF NOT EXISTS actor (

actor_id smallint(5) NOT NULL PRIMARY KEY,

first_name varchar(45) NOT NULL,

last_name varchar(45) NOT NULL,

last_update timestamp NOT NULL DEFAULT (datetime('now','localtime')))

【解析】

CREATE VIEW actor_name_view AS

select  first_name as fist_name_v,last_name  as last_name_v   from actor

 

36. 针对salaries表emp_no字段创建索引idx_emp_no，查询emp_no为10005, 使用强制索引。
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`));
create index idx_emp_no on salaries(emp_no);

【解析】SELECT * FROM salaries INDEXED BY idx_emp_no WHERE emp_no = 10005

【关于索引的描述】

1. 创建索引：create 【unique/】   index  索引名字 on表明（列名）

2. 对于一张表，如果表中的某一列设置了主键，则会在该列上创建一个索引，主索引unique index

3. 查询的时候走索引，可以提高查询的速度，使用强制索引的方法

      Select * from 表名 indexed by 索引名 where 需要强制走索引的列名 +条件

4. 索引，如果有索引，先从索引走，占用空间比较大，索引是加在字段上的，orcale自动回创建索引，orcale回自动创建一个索引，会给款卫衣的约束。如果where字段使用了函数，则不走函数。

5. Drop index 索引名字

6. 创建组合索引，crate index 索引名字 on 表明（列名1，列名2）

 

 

 

37.存在actor表，包含如下列信息：

CREATE TABLE IF NOT EXISTS actor (

actor_id smallint(5) NOT NULL PRIMARY KEY,

first_name varchar(45) NOT NULL,

last_name varchar(45) NOT NULL,

last_update timestamp NOT NULL DEFAULT (datetime('now','localtime')));

现在在last_update后面新增加一列名字为create_date, 类型为datetime, NOT NULL，默认值为'0000 00:00:00'

【解析】

Alter table actor  add create_date datetime default ‘0000-00-00 00:00:00’ not null

 

37. 构造一个触发器audit_log，在向employees表中插入一条数据的时候，触发插入相关的数据到audit中。
CREATE TABLE employees_test(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
【解析】

create trigger audit_log

after insert on employees_test

for each row

  Begin

     insert into audit(EMP_no,NAME) values(new.ID,new.NAME);

  END;

 

38.删除emp_no重复的记录，只保留最小的id对应的记录。

CREATE TABLE IF NOT EXISTS titles_test (

id int(11) not null primary key,

emp_no int(11) NOT NULL,

title varchar(50) NOT NULL,

from_date date NOT NULL,

to_date date DEFAULT NULL);

【解析】

DELETE FROM titles_test WHERE id NOT IN (

SELECT MIN(id) FROM titles_test GROUP BY emp_no)

 

 

 

 

 

 

39.将所有to_date为9999-01-01的全部更新为NULL,且 from_date更新为2001-01-01。

CREATE TABLE IF NOT EXISTS titles_test (

id int(11) not null primary key,

emp_no int(11) NOT NULL,

title varchar(50) NOT NULL,

from_date date NOT NULL,

to_date date DEFAULT NULL);

【解析】

UPDATE titles_test SET to_date = NULL, from_date = '2001-01-01'

WHERE to_date = '9999-01-01';

 

40.将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变，使用replace实现。

CREATE TABLE IF NOT EXISTS titles_test (

id int(11) not null primary key,

emp_no int(11) NOT NULL,

title varchar(50) NOT NULL,

from_date date NOT NULL,

to_date date DEFAULT NULL);

【解析】

Update titles_test set emp_no=replace(emp_no,’10001’,’10005’) where id=5;

 

41.将titles_test表名修改为titles_2017。

CREATE TABLE IF NOT EXISTS titles_test (

id int(11) not null primary key,

emp_no int(11) NOT NULL,

title varchar(50) NOT NULL,

from_date date NOT NULL,

to_date date DEFAULT NULL);

【解析】

Alter table  titles_tes  rename to  titles_2017;

 

 

 

 

 

 

 

 

 

 

 

 

 

42.在audit表上创建外键约束，其emp_no对应employees_test表的主键id。

CREATE TABLE employees_test(

ID INT PRIMARY KEY NOT NULL,

NAME TEXT NOT NULL,

AGE INT NOT NULL,

ADDRESS CHAR(50),

SALARY REAL

);

【解析】

A.  DROP TABLE audit;

CREATE TABLE audit(

    EMP_no INT NOT NULL,

    create_date datetime NOT NULL,

FOREIGN KEY(EMP_no) REFERENCES employees_test(ID));

B. alter table audit add foreign key(EMP_no) references employees_test(ID);

 

 

43.存在如下的视图：

create view emp_v as select * from employees where emp_no >10005;

如何获取emp_v和employees有相同的数据？

 

 

 

 

 

 

 

 

 

 

【解析】

Select * from employees where emp_no >10005;

44.将所有获取奖金的员工当前的薪水增加10%。

create table emp_bonus(

emp_no int not null,

recevied datetime not null,

btype smallint not null);

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`));

【解析】update emp_bonus set salary=salary*1.1

 

45.针对库中的所有表生成select count(*)对应的SQL语句

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`));

create table emp_bonus(

emp_no int not null,

recevied datetime not null,

btype smallint not null);

 

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`));

输出格式:

cnts

select count(*) from employees;

select count(*) from departments;

select count(*) from dept_emp;

select count(*) from dept_manager;

select count(*) from salaries;

select count(*) from titles;

select count(*) from emp_bonus;

【解析】SELECT "select count(*) from " || name || ";" AS cnts

FROM sqlite_master WHERE type = 'table'

 

46. 将employees表中的所有员工的last_name和first_name通过(')连接起来。
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`));

【解析】

Select last_name ||  “’”  ||  first_name from employee;

 

47. 查找字符串'10,A,B' 中逗号','出现的次数cnt

【解析】

SELECT (length("10,A,B")-length(replace("10,A,B",",","")))/length(",") AS cnt from dual;

 

48.获取Employees中的first_name，查询按照first_name最后两个字母，按照升序进行排列

【解析】

A. SELECT first_name FROM employees ORDER BY substr(first_name,-2)

B. select t.first_name FROM employees t  

order by substr(t.first_name,length(t.first_name)-1,length(t.first_name));

【关于subStr函数的描述】

 

 

 

49.按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果employees

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`));

【解析】SELECT dept_no, group_concat(emp_no) as employees

 from dept_emp group by dept_no;[此函数MYSQL]

【关于聚合函数的相关描述】

(select t.nsrmc ,(wm_concat( distinct(t.qxqybz))) as a  from tax_source_by_details t where t.nd=2018  group by t.nsrmc)

 

 

 

 

 

 

 

 

 

 

 

 

50.查找排除当前最大、最小salary之后的员工的平均工资avg_salary。

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`));

输出格式:  avg_salary

69462.5555555556

【解析】

A.  Select avg(salary) as avg_salary

From salaries where salary !=(select min(salary) from salaries)

   And salary !=(select max(salary) from salaries)

               And to_date = '9999-01-01' ;

 

B  SELECT AVG(salary) AS avg_salary FROM salaries 

WHERE to_date = '9999-01-01' 

AND salary NOT IN (SELECT MAX(salary) FROM salaries)

AND salary NOT IN (SELECT MIN(salary) FROM salaries)

 

51.分页查询employees表，每5行一页，返回第2页的数据

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`));

【解析】

方法一：利用 LIMIT 和 OFFSET 关键字。LIMIT 后的数字代表返回几条记录，OFFSET 后的数字代表从第几条记录开始返回（第一条记录序号为0），也可理解为跳过多少条记录后开始返回。

SELECT * FROM employees LIMIT 5 OFFSET 5

 

方法二：只利用 LIMIT 关键字。注意：在 LIMIT X,Y 中，Y代表返回几条记录，X代表从第几条记录开始返回（第一条记录序号为0），切勿记反。

SELECT * FROM employees LIMIT 5,5

 

方法三：常见的分页语句

        Select rownum ,t.*          from             

                         (     select * from 表名字  );此时，原表上面就会存在行号                               

 

 

 

 

52.使用含有关键字exists查找未分配具体部门的员工的所有信息。

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`));

【解析】

Select * from employees  t  not exists (

Select  de.emp_no from dept_emp de  where de.emp_no=t.emp_no);

 

 

53.对于employees表中，给出奇数行的first_name

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`));

【解析】

A. Select  first_name

     From employee e1

where ( select count(*) from employees as e2  

 where e2.first_name <=e1.first_name  ) %2 =1 

B.select a.first_name

  from (select rownum as w, t.* from (select t.* from employee t order by t.frist_name) t) a

 where mod(a.w , 2 )= 1;