《數(shù)據(jù)庫SQL實戰(zhàn)》全部答案

最近正好有點時間，就整理了一下?？途W(wǎng)上的數(shù)據(jù)庫SQL實戰(zhàn)中的題，里面的每一道題我都親自編寫并運行了一下，有的題可能不止一個解，并且有些做了備注與分析。這里的題很具有代表性，對于軟件開發(fā)人員有很大幫助，開發(fā)人員需要理解這些題，然后做到舉一反三應(yīng)用到實際的工作中去，當(dāng)然最重要的還是貴在積累。
注：數(shù)據(jù)庫sql實戰(zhàn)中用的是sqlite3數(shù)據(jù)庫，這里面的腳本都能在sqlite3中運行，有些還給出了在mysql、oracal等數(shù)據(jù)庫上的運行腳本

1、查找最晚入職員工的所有信息

表結(jié)構(gòu)說明

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

腳本 1

select * 
from employees 
order by hire_date desc limit 1;

運行時間：17ms
占用內(nèi)存：3320k

腳本 2

select *
from employees 
where hire_date = (select max(hire_date) from employees);

運行時間：25ms
占用內(nèi)存：3300k

2、查找入職員工時間排名倒數(shù)第三的員工所有信息

表結(jié)構(gòu)說明

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

腳本 1

select * 
from employees 
order by hire_date desc limit 2,1;

運行時間：31ms
占用內(nèi)存：4332k

腳本 2

select * 
from employees 
where hire_date = (select hire_date from employees order by hire_date desc limit 2,1);

運行時間：18ms
占用內(nèi)存：3284k

3、查找各個部門當(dāng)前(to_date='9999-01-01')領(lǐng)導(dǎo)當(dāng)前薪水詳情以及其對應(yīng)部門編號dept_no

表結(jié)構(gòu)說明

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

腳本 1

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

運行時間：24ms
占用內(nèi)存：3304k

4、查找所有已經(jīng)分配部門的員工的last_name和first_name

表結(jié)構(gòu)說明

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

腳本 1

select e.last_name, e.first_name, d.dept_no 
from dept_emp d 
inner join employees e 
on e.emp_no=d.emp_no;

運行時間：19ms
占用內(nèi)存：3284k

5、查找所有員工的last_name和first_name以及對應(yīng)部門編號dept_no，也包括展示沒有分配具體部門的員工

表結(jié)構(gòu)說明

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

腳本 1

select e.last_name, e.first_name, d.dept_no 
from employees e 
left join dept_emp d 
on e.emp_no=d.emp_no;

運行時間：19ms
占用內(nèi)存：3284k

6、查找所有員工入職時候的薪水情況，給出emp_no以及salary， 并按照emp_no進行逆序

表結(jié)構(gòu)說明

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

腳本 1

select s.emp_no, s.salary 
from salaries s, employees e 
where s.emp_no=e.emp_no 
and s.from_date = e.hire_date 
order by s.emp_no desc;

運行時間：29ms
占用內(nèi)存：3284k

腳本 2

select s.emp_no, s.salary 
from salaries s 
inner join employees e 
on s.emp_no=e.emp_no 
and s.from_date = e.hire_date 
order by s.emp_no desc;

運行時間：29ms
占用內(nèi)存：3304k

腳本 3

select emp_no, salary 
from salaries 
group by emp_no 
having min(from_date) 
order by emp_no desc;

運行時間：22ms
占用內(nèi)存：3436k

7、查找薪水漲幅超過15次的員工號emp_no以及其對應(yīng)的漲幅次數(shù)t

表結(jié)構(gòu)說明

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

腳本 1

select emp_no, count(emp_no) as t 
from salaries 
group by emp_no having t > 15;

運行時間：32ms
占用內(nèi)存：4180k

腳本 2

select emp_no, count(distinct salary) as t 
from salaries group by emp_no having t > 15;

運行時間：26ms
占用內(nèi)存：3416k

腳本 3

select e.emp_no, count(e.emp_no) as t 
from (select emp_no, salary 
      from salaries group by emp_no,salary) e 
group by e.emp_no having t > 15;

運行時間：27ms
占用內(nèi)存：3308k

8、找出所有員工當(dāng)前(to_date='9999-01-01')具體的薪水salary情況，對于相同的薪水只顯示一次,并按照逆序顯示

表結(jié)構(gòu)說明

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

腳本 1

select distinct salary 
from salaries 
where to_date='9999-01-01' 
order by salary desc;

運行時間：15ms
占用內(nèi)存：3552k

腳本 2

select salary 
from salaries 
where to_date='9999-01-01' 
group by salary 
order by salary desc;

運行時間：29ms
占用內(nèi)存：3424k
備注：大家可以參考一下這個網(wǎng)址（http://www.itdecent.cn/p/34800d06f63d）關(guān)于distinct和group by的效率分析

9、獲取所有部門當(dāng)前manager的當(dāng)前薪水情況，給出dept_no, emp_no以及salary，當(dāng)前表示to_date='9999-01-01'

表結(jié)構(gòu)說明

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

腳本 1

select m.dept_no, m.emp_no, s.salary 
from dept_manager m, salaries s 
where m.to_date='9999-01-01' 
and s.to_date='9999-01-01' 
and m.emp_no = s.emp_no;

運行時間：19ms
占用內(nèi)存：3320k

腳本 2

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

運行時間：20ms
占用內(nèi)存：3428k

10、獲取所有非manager的員工emp_no

表結(jié)構(gòu)說明

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

腳本 1

select emp_no 
from employees 
where emp_no not in (select emp_no from dept_manager);

運行時間：24ms
占用內(nèi)存：3428k

腳本 2

select e.emp_no 
from employees e 
left join dept_manager m on e.emp_no = m.emp_no 
where m.emp_no is null;

運行時間：14ms
占用內(nèi)存：3420k

腳本 3

select emp_no 
from employees 
except select emp_no from dept_manager;

運行時間：26ms
占用內(nèi)存：3428k

腳本 4

select e.emp_no 
from employees e 
where not exists (select emp_no 
                  from dept_manager m 
              where m.emp_no = e.emp_no);

運行時間：19ms
占用內(nèi)存：3192k

備注：看運行時間可以看出，join > exists > in > except

11、獲取所有員工當(dāng)前的manager，如果當(dāng)前的manager是自己的話結(jié)果不顯示，當(dāng)前表示to_date='9999-01-01'。結(jié)果第一列給出當(dāng)前員工的emp_no,第二列給出其manager對應(yīng)的manager_no。

表結(jié)構(gòu)說明

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

腳本 1

select e.emp_no, m.emp_no as manager_no 
from dept_emp e 
inner join dept_manager m 
on m.dept_no = e.dept_no 
and m.to_date='9999-01-01' 
and e.emp_no <> m.emp_no;

運行時間：25ms
占用內(nèi)存：4440k

腳本 2

select e.emp_no, m.emp_no as manager_no 
from dept_emp e 
left join dept_manager m on m.dept_no = e.dept_no 
where m.to_date='9999-01-01' 
and m.emp_no != e.emp_no;

運行時間：28ms
占用內(nèi)存：3432k

12、獲取所有部門中當(dāng)前員工薪水最高的相關(guān)信息，給出dept_no, emp_no以及其對應(yīng)的salary, 當(dāng)前表示to_date='9999-01-01'

表結(jié)構(gòu)說明

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

腳本 1

select e.dept_no, s.emp_no, s.salary 
from salaries s 
inner join dept_emp e on e.emp_no = s.emp_no 
where e.to_date='9999-01-01' 
group by e.dept_no having max(s.salary);

運行時間：28ms
占用內(nèi)存：3432k

腳本 2

select e.dept_no, s.emp_no, s.salary 
from salaries s 
left join dept_emp e on e.emp_no = s.emp_no 
where e.to_date='9999-01-01' 
group by e.dept_no having max(s.salary);

運行時間：24ms
占用內(nèi)存：3300k

腳本 3

select e.dept_no, s.emp_no, max(s.salary) 
from salaries s 
left join dept_emp e on e.emp_no = s.emp_no 
where e.to_date='9999-01-01' 
group by e.dept_no;

運行時間：20ms
占用內(nèi)存：3336k

13、從titles表獲取按照title進行分組，每組個數(shù)大于等于2，給出title以及對應(yīng)的數(shù)目t。

表結(jié)構(gòu)說明

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

腳本 1

select title, count(title) as t 
from titles 
group by title having t >= 2;

運行時間：20ms
占用內(nèi)存：3320k

14、從titles表獲取按照title進行分組，每組個數(shù)大于等于2，給出title以及對應(yīng)的數(shù)目t。 注意對于重復(fù)的emp_no進行忽略。

表結(jié)構(gòu)說明

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

腳本 1

select title, count(distinct emp_no) as t 
from titles 
group by title having t >= 2;

運行時間：24ms
占用內(nèi)存：3428k

腳本 2

select title, count(*) as t 
from (select distinct emp_no, title from titles) 
group by title having t >= 2;

運行時間：32ms
占用內(nèi)存：3420k

15、查找employees表所有emp_no為奇數(shù)，且last_name不為Mary的員工信息，并按照hire_date逆序排列

表結(jié)構(gòu)說明

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

腳本 1

select * 
from employees 
where last_name != 'Mary' 
and emp_no % 2 = 1 
order by hire_date desc;

運行時間：28ms
占用內(nèi)存：3404k

16、統(tǒng)計出當(dāng)前各個title類型對應(yīng)的員工當(dāng)前薪水對應(yīng)的平均工資。結(jié)果給出title以及平均工資avg。

表結(jié)構(gòu)說明

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

腳本 1

select t.title, avg(s.salary) 
from salaries s 
inner join titles t on s.emp_no = t.emp_no and s.to_date='9999-01-01' and t.to_date='9999-01-01' 
group by t.title;

運行時間：30ms
占用內(nèi)存：3224k

腳本 2

select t.title, avg(s.salary) 
from salaries s 
inner join titles t on s.emp_no = t.emp_no 
where s.to_date='9999-01-01' and t.to_date='9999-01-01' 
group by t.title;

運行時間：24ms
占用內(nèi)存：3296k

17、獲取當(dāng)前（to_date='9999-01-01'）薪水第二多的員工的emp_no以及其對應(yīng)的薪水salary

表結(jié)構(gòu)說明

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

腳本 1

select emp_no, salary 
from salaries 
where to_date='9999-01-01' 
and salary = (select distinct salary from salaries order by salary desc limit 1,1);

運行時間：25ms
占用內(nèi)存：3548k

18、查找當(dāng)前薪水(to_date='9999-01-01')排名第二多的員工編號emp_no、薪水salary、last_name以及first_name，不準(zhǔn)使用order by

表結(jié)構(gòu)說明

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

腳本 1

select e.emp_no, max(s.salary), e.last_name, e.first_name 
from salaries s 
left join employees e on s.emp_no = e.emp_no 
where s.to_date='9999-01-01' 
and salary < (select max(salary) from salaries where to_date='9999-01-01');

運行時間：27ms
占用內(nèi)存：3420k

腳本 2

select e.emp_no, max(s.salary), e.last_name, e.first_name 
from salaries s, employees e 
where s.emp_no = e.emp_no 
and s.to_date='9999-01-01' 
and salary < (select max(salary) from salaries where to_date='9999-01-01');

運行時間：16ms
占用內(nèi)存：3416k

腳本 3

select e.emp_no, s.salary, e.last_name, e.first_name 
from salaries s inner join employees e on s.emp_no = e.emp_no 
where s.to_date='9999-01-01' 
and salary = (
    select max(salary) 
    from salaries 
    where to_date='9999-01-01' 
    and salary < (
        select max(salary) 
        from salaries 
        where to_date='9999-01-01')
    );

運行時間：15ms
占用內(nèi)存：3420k

腳本 4

select e.emp_no, salary, last_name, first_name
from employees as e inner join salaries as s on e.emp_no = s.emp_no
where s.to_date='9999-01-01'
and salary = (select max(salary) 
              from salaries 
              where to_date='9999-01-01' 
              and salary < (select max(salary) from salaries  where to_date='9999-01-01'));

運行時間：22ms
占用內(nèi)存：3292k

腳本 5

select e.emp_no, s.salary, e.last_name, e.first_name 
from salaries s, employees e 
where s.to_date='9999-01-01' 
and s.emp_no = e.emp_no
and salary = (
    select max(salary) 
    from salaries 
    where to_date='9999-01-01' 
    and salary < (
        select max(salary) 
        from salaries 
        where to_date='9999-01-01')
    );

運行時間：19ms
占用內(nèi)存：3320k

19、查找所有員工的last_name和first_name以及對應(yīng)的dept_name，也包括暫時沒有分配部門的員工

表結(jié)構(gòu)說明

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

腳本 1

select e.last_name, e.first_name, d.dept_name 
from employees e 
left join dept_emp de on de.emp_no = e.emp_no 
left join departments d on d.dept_no = de.dept_no;

運行時間：27ms
占用內(nèi)存：3320k

腳本 2

select e.last_name, e.first_name, tmp.dept_name
from employees e
left join (select d.dept_name, de.emp_no
    from dept_emp de
    left join departments d
    on d.dept_no = de.dept_no) tmp
on tmp.emp_no = e.emp_no;

運行時間：23ms
占用內(nèi)存：3684k

20、查找員工編號emp_no為10001其自入職以來的薪水salary漲幅值growth

表結(jié)構(gòu)說明

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

腳本 1

select (max(salary) - min(salary)) as growth
from salaries
where emp_no=10001;

運行時間：18ms
占用內(nèi)存：3320k

腳本 2

select (
    (select salary from salaries where emp_no=10001 order by to_date desc limit 1) - 
    (select salary from salaries where emp_no=10001 order by to_date asc limit 1)
) as growth

運行時間：23ms
占用內(nèi)存：3448k

21、查找所有員工自入職以來的薪水漲幅情況，給出員工編號emp_no以及其對應(yīng)的薪水漲幅growth，并按照growth進行升序

表結(jié)構(gòu)說明

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

腳本 1

select current.emp_no, (current.salary - start.salary) as growth 
from (
    select e1.emp_no, s1.salary 
    from employees e1 
    left join salaries s1 on e1.emp_no = s1.emp_no 
    where s1.to_date = '9999-01-01'
) as current
inner join (
    select e2.emp_no, s2.salary 
    from employees e2 
    left join salaries s2 on e2.emp_no = s2.emp_no 
    where e2.hire_date = s2.from_date
) as start on start.emp_no = current.emp_no
order by growth;

運行時間：25ms
占用內(nèi)存：3304k

腳本 2

select current.emp_no, (current.salary - start.salary) as growth
from (
    select e2.emp_no, s2.salary 
    from employees e2 
    left join salaries s2 on e2.emp_no = s2.emp_no 
    where e2.hire_date = s2.from_date
) as start
inner join (
    select emp_no, salary 
    from salaries 
    where to_date = '9999-01-01'
) as current
on start.emp_no = current.emp_no
order by growth;

運行時間：21ms
占用內(nèi)存：3260k

腳本 3

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

運行時間：21ms
占用內(nèi)存：3312k

腳本 4

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.from_date = e.hire_date) AS sStart,
(SELECT emp_no, salary 
 FROM salaries 
 WHERE to_date = '9999-01-01') AS sCurrent
WHERE sCurrent.emp_no = sStart.emp_no
ORDER BY growth

運行時間：21ms
占用內(nèi)存：3308k

22、統(tǒng)計各個部門對應(yīng)員工漲幅的次數(shù)總和，給出部門編碼dept_no、部門名稱dept_name以及次數(shù)sum

表結(jié)構(gòu)說明

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

腳本 1

select de.dept_no, d.dept_name, count(s.salary) as sum
from dept_emp de
left join salaries s on de.emp_no = s.emp_no
left join departments d on de.dept_no = d.dept_no
group by de.dept_no;

運行時間：14ms
占用內(nèi)存：3420k

腳本 2

select de.dept_no, d.dept_name, count(s.salary) as sum
from dept_emp de, salaries s, departments d
where de.emp_no = s.emp_no
and de.dept_no = d.dept_no
group by de.dept_no;

運行時間：14ms
占用內(nèi)存：3420k

23、對所有員工的當(dāng)前(to_date='9999-01-01')薪水按照salary進行按照1-N的排名，相同salary并列且按照emp_no升序排列

表結(jié)構(gòu)說明

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

腳本 1

select s1.emp_no, s1.salary, count(distinct s2.salary) as rank
from salaries s1, salaries 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, s2.emp_no asc;

運行時間：15ms
占用內(nèi)存：3556k

24、獲取所有非manager員工當(dāng)前的薪水情況，給出dept_no、emp_no以及salary ，當(dāng)前表示to_date='9999-01-01'

表結(jié)構(gòu)說明

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

腳本 1

select de.dept_no, e.emp_no, s.salary
from employees e
inner join salaries s on e.emp_no = s.emp_no and s.to_date = '9999-01-01'
inner join dept_emp de on de.emp_no = e.emp_no
left join dept_manager dm on e.emp_no = dm.emp_no
where dm.dept_no is null;

運行時間：23ms
占用內(nèi)存：3300k

腳本 2

select de.dept_no, ee.emp_no, s.salary
from (select e.emp_no
      from employees e 
      left join dept_manager dm on dm.emp_no = e.emp_no 
      where dm.dept_no is null) as ee
inner join salaries s on ee.emp_no = s.emp_no and s.to_date = '9999-01-01'
inner join dept_emp de on ee.emp_no = de.emp_no;

運行時間：15ms
占用內(nèi)存：3424k

25、獲取員工其當(dāng)前的薪水比其manager當(dāng)前薪水還高的相關(guān)信息，當(dāng)前表示to_date='9999-01-01',結(jié)果第一列給出員工的emp_no，第二列給出其manager的manager_no，第三列給出該員工當(dāng)前的薪水emp_salary,第四列給該員工對應(yīng)的manager當(dāng)前的薪水manager_salary

表結(jié)構(gòu)說明

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

腳本 1

select de.emp_no as emp_no, dm.emp_no as manager_no, de.salary as emp_salary, dm.salary as manager_salary
from (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.to_date='9999-01-01') as de
inner join (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 s.to_date='9999-01-01')
            as dm on de.dept_no = dm.dept_no
where emp_salary > manager_salary and de.dept_no = dm.dept_no;

運行時間：19ms
占用內(nèi)存：3304k

26、匯總各個部門當(dāng)前員工的title類型的分配數(shù)目，結(jié)果給出部門編號dept_no、dept_name、其當(dāng)前員工所有的title以及該類型title對應(yīng)的數(shù)目count

表結(jié)構(gòu)說明

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

腳本 1

select d.dept_no, d.dept_name, t.title, count(*) as count
from titles t
inner join dept_emp e on e.emp_no = t.emp_no and e.to_date='9999-01-01' and t.to_date='9999-01-01'
inner join departments d on e.dept_no = d.dept_no
group by d.dept_no, t.title;

運行時間：26ms
占用內(nèi)存：3292k

27、給出每個員工每年薪水漲幅超過5000的員工編號emp_no、薪水變更開始日期from_date以及薪水漲幅值salary_growth，并按照salary_growth逆序排列。

提示：在sqlite中獲取datetime時間對應(yīng)的年份函數(shù)為strftime('%Y', to_date)

表結(jié)構(gòu)說明

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

腳本 1

select s2.emp_no, s2.from_date, (s2.salary - s1.salary) as salary_growth
from salaries s1, salaries s2
where s1.emp_no = s2.emp_no
and salary_growth > 5000
and (strftime('%Y', s2.from_date) - strftime('%Y', s1.from_date) = 1 or
     strftime('%Y', s2.to_date) - strftime('%Y', s1.to_date) = 1)
order by salary_growth desc;

運行時間：23ms
占用內(nèi)存：3416k

腳本 2

select s2.emp_no, s2.from_date, (s2.salary - s1.salary) as salary_growth
from salaries s1
inner join salaries s2 on s1.emp_no = s2.emp_no
and (strftime('%Y', s2.from_date) - strftime('%Y', s1.from_date) = 1 or
     strftime('%Y', s2.to_date) - strftime('%Y', s1.to_date) = 1)
where salary_growth > 5000
order by salary_growth desc;

運行時間：20ms
占用內(nèi)存：3300k

28、查找描述信息中包括robot的電影對應(yīng)的分類名稱以及電影數(shù)目，而且還需要該分類對應(yīng)電影數(shù)量>=5部

表結(jié)構(gòu)說明

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

腳本 1

select c.name, count(f.film_id) as count
from film f, film_category fc, category 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

運行時間：27ms
占用內(nèi)存：3556k

29、使用join查詢方式找出沒有分類的電影id以及名稱

表結(jié)構(gòu)說明

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

腳本 1

select f.film_id, f.title
from film f
left join film_category fc on fc.film_id = f.film_id
where fc.category_id is null;

運行時間：25ms
占用內(nèi)存：3556k

30、使用子查詢的方式找出屬于Action分類的所有電影對應(yīng)的title,description

表結(jié)構(gòu)說明

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

腳本 1

select title, description
from film
where film_id in 
(select film_id 
 from film_category 
 where category_id in 
 (select category_id from category where name="Action")
);

運行時間：25ms
占用內(nèi)存：3432k

腳本 2

select title, description
from film
where film_id in 
(select fc.film_id 
 from film_category fc 
 inner join category c 
 on c.category_id = fc.category_id 
 and c.name="Action");

運行時間：27ms
占用內(nèi)存：3552k

腳本 3

使用非子查詢的方式找出屬于Action分類的所有電影對應(yīng)的title,description

select f.title, f.description
from film f
left join film_category fc on fc.film_id = f.film_id
left join category c on c.category_id = fc.category_id
where c.name = "Action";

運行時間：28ms
占用內(nèi)存：3448k

31、獲取select * from employees對應(yīng)的執(zhí)行計劃

腳本 1

explain select * from employees;

運行時間：19ms
占用內(nèi)存：3300k

32、將employees表的所有員工的last_name和first_name拼接起來作為Name，中間以一個空格區(qū)分

表結(jié)構(gòu)說明

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

腳本 1

sqlite3

SELECT last_name||' '||first_name AS Name FROM employees

運行時間：19ms
占用內(nèi)存：3300k

腳本 2

mysql、oracle、sql server

SELECT concat(last_name, " ", first_name) AS Name FROM employees

33、創(chuàng)建一個actor表，包含如下列信息

表結(jié)構(gòu)說明

列表          類型          是否為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        最后更新時間，默認是系統(tǒng)的當(dāng)前時間

腳本 1

CREATE TABLE `actor` ( 
`actor_id` smallint(5) NOT NULL,
`first_name` varchar(45) NOT NULL,
`last_name` varchar(45) NOT NULL,
`last_update` timestamp NOT NULL default (datetime('now','localtime')),
PRIMARY KEY (`actor_id`)
);

運行時間：19ms
占用內(nèi)存：3292k

34、對于表actor批量插入如下數(shù)據(jù)

表結(jié)構(gòu)說明

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

數(shù)據(jù)

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

腳本 1

insert into actor values(1, 'PENELOPE', 'GUINESS', '2006-02-15 12:34:33'),(2, 'NICK', 'WAHLBERG', '2006-02-15 12:34:33');

運行時間：19ms
占用內(nèi)存：3420k

35、對于表actor批量插入如下數(shù)據(jù),如果數(shù)據(jù)已經(jīng)存在，請忽略，不使用replace操作

表結(jié)構(gòu)說明

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

數(shù)據(jù)

actor_id    first_name  last_name   last_update
'3'         'ED'        'CHASE'     '2006-02-15 12:34:33'

腳本 1

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

運行時間：23ms
占用內(nèi)存：4332k

36、對于表actor批量插入如下數(shù)據(jù),如果數(shù)據(jù)已經(jīng)存在，請忽略，不使用replace操作

表結(jié)構(gòu)說明

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

數(shù)據(jù)

actor_id    first_name  last_name   last_update
'3'         'ED'        'CHASE'     '2006-02-15 12:34:33'

腳本 1

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

運行時間：23ms
占用內(nèi)存：4332k

37、創(chuàng)建一個actor_name表，將actor表中的所有first_name以及l(fā)ast_name導(dǎo)入改表。

對于如下表actor，其對應(yīng)的數(shù)據(jù)為:

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

actor_name表結(jié)構(gòu)如下：

列表          類型          是否為NULL         含義
first_name  varchar(45)     not null        名字
last_name   varchar(45)     not null        姓氏

腳本 1

create table actor_name as select first_name, last_name from actor;

運行時間：24ms
占用內(nèi)存：3424k

腳本 2

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;

運行時間：18ms
占用內(nèi)存：3292k

38、針對如下表actor結(jié)構(gòu)創(chuàng)建索引,對first_name創(chuàng)建唯一索引uniq_idx_firstname，對last_name創(chuàng)建普通索引idx_lastname

表結(jié)構(gòu)說明

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

腳本 1

create unique index uniq_idx_firstname on actor(first_name);
create index idx_lastname on actor(last_name);

運行時間：24ms
占用內(nèi)存：3208k

39、針對actor表創(chuàng)建視圖actor_name_view，只包含first_name以及l(fā)ast_name兩列，并對這兩列重新命名，first_name為first_name_v，last_name修改為last_name_v

表結(jié)構(gòu)說明

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

腳本 1

create view actor_name_view as select first_name as first_name_v, last_name as last_name_v from actor;

運行時間：20ms
占用內(nèi)存：3296k

腳本 2

create view actor_name_view(first_name_v, last_name_v) as select first_name, last_name from actor;

運行時間：21ms
占用內(nèi)存：3428k

40、針對salaries表emp_no字段創(chuàng)建索引idx_emp_no，查詢emp_no為10005, 使用強制索引。

表結(jié)構(gòu)說明

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

腳本 1

sqlite3

select * from salaries indexed by idx_emp_no where emp_no = 10005;

運行時間：29ms
占用內(nèi)存：3428k
參考：http://www.runoob.com/sqlite/sqlite-indexed-by.html

腳本 2

mysql、oracle、sql

select * from salaries force index idx_emp_no where emp_no = 10005

運行時間：21ms
占用內(nèi)存：3428k
參考：http://www.jb51.net/article/49807.htm

41、存在actor表，現(xiàn)在在last_update后面新增加一列名字為create_date, 類型為datetime, NOT NULL，默認值為'0000-00-00 00:00:00'

表結(jié)構(gòu)說明

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

腳本 1

alter table actor add create_date datetime NOT NULL default '0000-00-00 00:00:00';

運行時間：24ms
占用內(nèi)存：3312k

42、構(gòu)造一個觸發(fā)器audit_log，在向employees_test表中插入一條數(shù)據(jù)的時候，觸發(fā)插入相關(guān)的數(shù)據(jù)到audit中。

表結(jié)構(gòu)說明

CREATE TABLE employees_test(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);

CREATE TABLE audit(
EMP_no INT NOT NULL,
NAME TEXT NOT NULL
);

腳本 1

create trigger audit_log after insert on employees_test
begin
    insert into audit values(new.id, new.name);
end;

運行時間：22ms
占用內(nèi)存：3436k

43、刪除emp_no重復(fù)的記錄，只保留最小的id對應(yīng)的記錄。

表結(jié)構(gòu)說明

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

insert into titles_test values 
('1', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('2', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('3', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('4', '10004', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('5', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('6', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('7', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01');

腳本 1

delete from titles_test where id not in (select min(id) from titles_test group by emp_no);

運行時間：20ms
占用內(nèi)存：3320k

腳本 2

delete from titles_test 
where id in (
    select a.id 
    from titles_test a, titles_test b 
    where a.emp_no = b.emp_no
    and a.id > b.id);

運行時間：22ms
占用內(nèi)存：3552k

44、將所有to_date為9999-01-01的全部更新為NULL,且 from_date更新為2001-01-01。

表結(jié)構(gòu)說明

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

insert into titles_test values 
('1', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('2', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('3', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('4', '10004', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('5', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('6', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('7', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01');

腳本 1

update titles_test set to_date = null, from_date='2001-01-01' where to_date='9999-01-01';

運行時間：22ms
占用內(nèi)存：3328k

45、將id=5以及emp_no=10001的行數(shù)據(jù)替換成id=5以及emp_no=10005,其他數(shù)據(jù)保持不變，使用replace實現(xiàn)。

表結(jié)構(gòu)說明

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

insert into titles_test values 
('1', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('2', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('3', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('4', '10004', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('5', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('6', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('7', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01');

腳本 1

replace into titles_test values('5', '10005', 'Senior Engineer', '1986-06-26', '9999-01-01');

運行時間：23ms
占用內(nèi)存：3330k

腳本 2

update titles_test set emp_no = replace(emp_no, 10001, 10005) where emp_no = 10001;

運行時間：21ms
占用內(nèi)存：3304k

45、將titles_test表名修改為titles_2017。

表結(jié)構(gòu)說明

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

insert into titles_test values 
('1', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('2', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('3', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('4', '10004', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('5', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('6', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('7', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01');

腳本 1

alter table titles_test rename to titles_2017;

運行時間：18ms
占用內(nèi)存：3192k

腳本 2

mysql、oracle、sql

rename table titles_test to titles_2017;

運行時間：21ms
占用內(nèi)存：3304k

45、在audit表上創(chuàng)建外鍵約束，其emp_no對應(yīng)employees_test表的主鍵id。

表結(jié)構(gòu)說明

CREATE TABLE employees_test(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);

CREATE TABLE audit(
EMP_no INT NOT NULL,
create_date datetime NOT NULL
);

腳本 1

DROP TABLE audit;
CREATE TABLE audit(
    EMP_no INT NOT NULL,
    create_date datetime NOT NULL,
    FOREIGN KEY(EMP_no) REFERENCES employees_test(ID));

運行時間：20ms
占用內(nèi)存：3568k

46、如何獲取emp_v和employees有相同的數(shù)據(jù)？

表結(jié)構(gòu)說明

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

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

腳本 1

select em.* from employees em, emp_v ev where em.emp_no = ev.emp_no;

運行時間：18ms
占用內(nèi)存：3312k

腳本 2

select * from employees intersect select * from emp_v;

運行時間：23ms
占用內(nèi)存：3552k

腳本 3

select * from emp_v;

運行時間：22ms
占用內(nèi)存：3300k

47、將所有獲取獎金的員工當(dāng)前的薪水增加10%。

表結(jié)構(gòu)說明

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

腳本 1

update salaries set salary = salary * 1.1 
where emp_no in (select emp_no from emp_bonus);

運行時間：24ms
占用內(nèi)存：3412k

48、針對庫中的所有表生成select count(*)對應(yīng)的SQL語句

表結(jié)構(gòu)說明

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

腳本 1

select "select count(*) from "||name||";" as cnts 
from sqlite_master
where type='table'

運行時間：23ms
占用內(nèi)存：3420k

49、將employees表中的所有員工的last_name和first_name通過(')連接起來

表結(jié)構(gòu)說明

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

腳本 1

select last_name||"'"||first_name from employees;

運行時間：25ms
占用內(nèi)存：3320k

49、將employees表中的所有員工的last_name和first_name通過(')連接起來

表結(jié)構(gòu)說明

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

腳本 1

select last_name||"'"||first_name from employees;

運行時間：25ms
占用內(nèi)存：3320k

腳本 2

select concat(last_name, "'", first_name) from employees;

運行時間：25ms
占用內(nèi)存：3320k

50、查找字符串'10,A,B' 中逗號','出現(xiàn)的次數(shù)cnt。

腳本 1

select (length('10,A,B') - length(replace('10,A,B', ',', ''))) / length(',') as cnt;

運行時間：19ms
占用內(nèi)存：3292k

51、獲取Employees中的first_name，查詢按照first_name最后兩個字母，按照升序進行排列

表結(jié)構(gòu)說明

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

腳本 1

select first_name from employees order by substr(first_name, -2)

運行時間：14ms
占用內(nèi)存：3296k

腳本 2

select first_name 
from employees 
order by substr(first_name, length(first_name) - 1)

運行時間：15ms
占用內(nèi)存：3296k

52、按照dept_no進行匯總，屬于同一個部門的emp_no按照逗號進行連接，結(jié)果給出dept_no以及連接出的結(jié)果employees

表結(jié)構(gòu)說明

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

腳本 1

select dept_no, group_concat(emp_no) as employees 
from dept_emp 
group by dept_no;

運行時間：14ms
占用內(nèi)存：3308k

53、查找排除當(dāng)前最大、最小salary之后的員工的平均工資avg_salary。

表結(jié)構(gòu)說明

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

腳本 1

select avg(salary) as avg_salary
from salaries 
where to_date='9999-01-01' 
and salary not in (select min(salary) from salaries)
and salary not in (select max(salary) from salaries);

運行時間：21ms
占用內(nèi)存：3408k
備注：這個居然通過了，應(yīng)該是下面的那個帶有日期的才是正確的

腳本 2

select avg(salary) as avg_salary
from salaries 
where to_date='9999-01-01' 
and salary not in (select min(salary) from salaries where to_date='9999-01-01')
and salary not in (select max(salary) from salaries where to_date='9999-01-01');

運行時間：21ms
占用內(nèi)存：3408k
備注：這個居然沒有通過

54、分頁查詢employees表，每5行一頁，返回第2頁的數(shù)據(jù)

表結(jié)構(gòu)說明

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

腳本 1

select * from employees limit(2-1)*5, 5;

運行時間：15ms
占用內(nèi)存：3292k

55、獲取所有員工的emp_no、部門編號dept_no以及對應(yīng)的bonus類型btype和recevied，沒有分配具體的員工不顯示

表結(jié)構(gòu)說明

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 `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 `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 `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 table emp_bonus(
`emp_no` int not null,
`recevied` datetime not null,
`btype` smallint not null);

腳本 1

select e.emp_no, de.dept_no, b.btype, b.recevied
from employees e
inner join dept_emp de on de.emp_no = e.emp_no
left join emp_bonus as b on b.emp_no  = e.emp_no;

運行時間：28ms
占用內(nèi)存：3404k

腳本 2

select de.emp_no, de.dept_no, b.btype, b.recevied
from dept_emp de
left join emp_bonus as b on b.emp_no  = de.emp_no;

運行時間：20ms
占用內(nèi)存：3424k

56、使用含有關(guān)鍵字exists查找未分配具體部門的員工的所有信息

表結(jié)構(gòu)說明

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

腳本 1

select *
from employees e
where not exists (select emp_no from dept_emp de where e.emp_no=de.emp_no);

運行時間：20ms
占用內(nèi)存：3300k

57、獲取employees中的行數(shù)據(jù)，且這些行也存在于emp_v中。注意不能使用intersect關(guān)鍵字

表結(jié)構(gòu)說明

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

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

腳本 1

select e.* from employees e, emp_v ev where ev.emp_no = e.emp_no;

運行時間：22ms
占用內(nèi)存：3288k

58、給出emp_no、first_name、last_name、獎金類型btype、對應(yīng)的當(dāng)前薪水情況salary以及獎金金額bonus

bonus類型btype為1其獎金為薪水salary的10%，btype為2其獎金為薪水的20%，其他類型均為薪水的30%。 當(dāng)前薪水表示to_date='9999-01-01'

表結(jié)構(gòu)說明

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

腳本 1

select e.emp_no, e.first_name, e.last_name, b.btype, s.salary, (
    case b.btype
    when 1 then s.salary * 0.1
    when 2 then s.salary * 0.2
    else s.salary * 0.3 end) as bonus
from employees e
inner join salaries s on s.emp_no = e.emp_no and s.to_date='9999-01-01'
inner join emp_bonus b on b.emp_no = e.emp_no;

運行時間：28ms
占用內(nèi)存：3300k

59、按照salary的累計和running_total，其中running_total為前兩個員工的salary累計和，其他以此類推

表結(jié)構(gòu)說明

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

腳本 1

select s1.emp_no, s1.salary, (
    select sum(s2.salary) 
    from salaries s2
    where s2.emp_no <= s1.emp_no 
    and s2.to_date='9999-01-01'
    ) as running_total 
from salaries as s1
where s1.to_date='9999-01-01'
order by s1.emp_no;

運行時間：25ms
占用內(nèi)存：3296k

腳本 2

select s.emp_no,s.salary,(
    select sum(salary) 
    from salaries 
    where rowid<=s.rowid 
    and to_date ="9999-01-01") as running_total 
from salaries s where s.to_date ="9999-01-01"

運行時間：25ms
占用內(nèi)存：3424k

60、對于employees表中，在對first_name進行排名后，選出奇數(shù)排名對應(yīng)的first_name

表結(jié)構(gòu)說明

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

腳本 1

select e1.first_name
from (
    select e2.first_name,(
        select count(*) 
        from employees as e3
        where e3.first_name <= e2.first_name) as rowid
    from employees as e2) as e1
where e1.rowid % 2 = 1;

運行時間：21ms
占用內(nèi)存：3196k

腳本 2

select e1.first_name
from employees e1
where (select count(*) 
       from employees e2
       where e1.first_name <= e2.first_name) % 2 = 1;

運行時間：20ms
占用內(nèi)存：3292k

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容