牛客网数据库SQL实战练习61题

以你之姓@ 2023-06-28 06:27 70阅读 0赞

1.  查找最晚入职员工的所有信息。

select * from 'employees' where hire_date in (select MAX(hire_date) from 'employees');

1.  查找入职员工时间排名倒数第三的员工所有信息。

select * from(select * from 'employees' order by hire_date desc) limit 1 offset 2;

1.  查找各个部门当前(to\_date=‘9999-01-01’)领导当前薪水详情以及其对应部门编号dept\_no。

select a.emp_no, a.salary, a.from_date, a.to_date, d.dept_no from 
    (select * from 'salaries' where to_date='9999-01-01') a, 'dept_manager' d
    where a.emp_no=d.emp_no and d.to_date='9999-01-01';

1.  查找所有已经分配部门的员工的last\_name和first\_name。

select last_name,first_name,dept_no from dept_emp d,employees e
    where e.emp_no = d.emp_no;

1.  查找所有员工的last\_name和first\_name以及对应部门编号dept\_no，也包括展示没有分配具体部门的员工。

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

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

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

1.  查找薪水涨幅超过15次的员工号emp\_no以及其对应的涨幅次数t。

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

1.  找出所有员工当前(to\_date=‘9999-01-01’)具体的薪水salary情况，对于相同的薪水只显示一次,并按照逆序显示。

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

1.  获取所有部门当前manager的当前薪水情况，给出dept\_no, emp\_no以及salary，当前表示to\_date=‘9999-01-01’。

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

1.  获取所有非manager的员工emp\_no

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

1.  获取所有员工当前的manager，如果当前的manager是自己的话结果不显示，当前表示to\_date=‘9999-01-01’。  
    结果第一列给出当前员工的emp\_no,第二列给出其manager对应的manager\_no。

select emp_no,manager_no from dept_emp de,
    (select dept_no,emp_no as manager_no from dept_manager where to_date='9999-01-01') dm
    where de.dept_no = dm.dept_no and emp_no <> manager_no and to_date='9999-01-01';

1.  获取所有部门中当前员工薪水最高的相关信息，给出dept\_no, emp\_no以及其对应的salary。

select dept_no,de.emp_no,MAX(salary)
    from dept_emp de, salaries s
    where de.emp_no = s.emp_no and de.to_date='9999-01-01' 
    and de.to_date = s.to_date
    group by dept_no;

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

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

1.  从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。注意对于重复的emp\_no进行忽略。

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

1.  查找employees表所有emp\_no为奇数，且last\_name不为Mary的员工信息，并按照hire\_date逆序排列。

select * from employees where last_name <> 'Mary'
    and round(emp_no/2.0) <> emp_no/2
    order by hire_date desc;

1.  统计出当前各个title类型对应的员工当前（to\_date=‘9999-01-01’）薪水对应的平均工资。结果给出title以及平均工资avg。

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

1.  获取当前（to\_date=‘9999-01-01’）薪水第二多的员工的emp\_no以及其对应的薪水salary。

select emp_no,salary from salaries
    where to_date='9999-01-01'
    order by salary desc limit 1 offset 1;

1.  查找当前薪水(to\_date=‘9999-01-01’)排名第二多的员工编号emp\_no、薪水salary、last\_name以及first\_name，不准使用order by。

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

1.  查找所有员工的last\_name和first\_name以及对应的dept\_name，也包括暂时没有分配部门的员工。

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

1.  查找员工编号emp\_no为10001其自入职以来的薪水salary涨幅值growth。

select MAX(salary)-MIN(salary) as growth
    from salaries
    where emp_no = 10001;

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

select e.emp_no,(s1.salary-s2.salary) as growth
    from employees as e
    inner join salaries as s1
    on e.emp_no = s1.emp_no and s1.to_date = '9999-01-01'
    inner join salaries as s2
    on e.emp_no = s2.emp_no and hire_date = s2.from_date
    order by growth asc;

1.  统计各个部门的工资记录数，给出部门编码dept\_no、部门名称dept\_name以及次数sum。

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

1.  对所有员工的当前(to\_date=‘9999-01-01’)薪水按照salary进行按照1-N的排名，相同salary并列且按照emp\_no升序排列。

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 s1.to_date = s2.to_date
    and s1.salary <= s2.salary
    group by s1.emp_no
    order by s1.salary desc,s1.emp_no asc;

1.  获取所有非manager员工当前的薪水情况，给出dept\_no、emp\_no以及salary ，当前表示to\_date=‘9999-01-01’。

select de.dept_no,e.emp_no,salary
    from dept_emp de, dept_manager dm, employees e, salaries s
    where e.emp_no not in (select emp_no from dept_manager)
    and e.emp_no = de.emp_no and e.emp_no = s.emp_no
    and de.dept_no = dm.dept_no
    and de.to_date = '9999-01-01'
    and de.to_date = s.to_date;

1.  获取员工其当前的薪水比其manager当前薪水还高的相关信息，当前表示to\_date=‘9999-01-01’,结果第一列给出员工的emp\_no，第二列给出其manager的manager\_no，第三列给出该员工当前的薪水emp\_salary,第四列给该员工对应的manager当前的薪水。

select m2.eno,manager_no,emp_salary,manager_salary from
    (select dm.dept_no as dno,dm.emp_no as manager_no, s1.salary as manager_salary
    from dept_manager dm, salaries s1
    where dm.emp_no = s1.emp_no
    and dm.to_date='9999-01-01'
    and dm.to_date=s1.to_date) m1,
    (select de.emp_no as eno, de.dept_no as dno, s2.salary as emp_salary
    from dept_emp de, salaries s2
    where de.emp_no = s2.emp_no
    and de.to_date = '9999-01-01'
    and de.to_date = s2.to_date) m2
    where m2.dno = m1.dno
    and emp_salary > manager_salary;

1.  汇总各个部门当前员工的title类型的分配数目，结果给出部门编号dept\_no、dept\_name、其当前员工所有的title以及该类型title对应的数目count。

select d.dept_no,dept_name,title,count from departments d,
    (select t.emp_no,de.dept_no as dno,title,count(title) as count 
    from dept_emp de, titles t
    where de.emp_no = t.emp_no and de.to_date='9999-01-01' and de.to_date=t.to_date
    group by de.dept_no,title) t1
    where d.dept_no = t1.dno;

1.  给出每个员工每年薪水涨幅超过5000的员工编号emp\_no、薪水变更开始日期from\_date以及薪水涨幅值salary\_growth，并按照salary\_growth逆序排列。  
    提示：在sqlite中获取datetime时间对应的年份函数为strftime(’%Y’, to\_date)

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

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

select name,count(fc.film_id) as amount from
    (select category_id,count(film_id) as amount
    from film_category
    group by category_id 
    having amount >= 5) m1,film f, category c,film_category fc
    where description like '%robot%'
    and f.film_id= fc.film_id
    and c.category_id = m1.category_id
    and fc.category_id = c.category_id;

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

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

1.  使用子查询的方式找出属于Action分类的所有电影对应的title,description。

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

1.  获取select \* from employees对应的执行计划。

explain select * from employees;

1.  将employees表的所有员工的last\_name和first\_name拼接起来作为Name，中间以一个空格区分。

select last_name||' '||first_name as Name from employees;

1.  创建一个actor表

create table actor(
     actor_id smallint(5) primary key not null,
     first_name varchar(45) not null,
     last_name varchar(45) not null,
     last_update timestamp default(datetime('now','localtime')) not null
    );

1.  对于表actor批量插入。

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

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

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

1.  创建一个actor\_name表，将actor表中的所有first\_name以及last\_name导入该表。

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;

1.  对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);

1.  针对actor表创建视图actor\_name\_view，只包含first\_name以及last\_name两列，并对这两列重新命名，first\_name为first\_name\_v，last\_name修改为last\_name\_v。

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

1.  针对salaries表emp\_no字段创建索引idx\_emp\_no，查询emp\_no为10005, 使用强制索引。

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

1.  在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;

1.  构造一个触发器audit\_log，在向employees\_test表中插入一条数据的时候，触发插入相关的数据到audit中。

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

1.  删除emp\_no重复的记录，只保留最小的id对应的记录。

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

1.  将所有to\_date为9999-01-01的全部更新为NULL,且 from\_date更新为2001-01-01。

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

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

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

1.  将titles\_test表名修改为titles\_2017。

alter table titles_test rename to titles_2017;

1.  在audit表上创建外键约束，其emp\_no对应employees\_test表的主键id。

drop table audit;
    create table audit(
        EMP_no INT NOT NULL,
        create_date datetime NOT NULL,
        foreign key(EMP_no) references employees_test(ID))

1.  存在如下的视图：  
    create view emp\_v as select \* from employees where emp\_no >10005;  
    如何获取emp\_v和employees有相同的数据？

select e1.* from emp_v e1,employees e2
    where e1.emp_no=e2.emp_no;

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

update salaries
    set salary = salary*1.1 where to_date='9999-01-01';

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

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

1.  将employees表中的所有员工的last\_name和first\_name通过(’)连接起来。

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

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

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

1.  获取Employees中的first\_name，查询按照first\_name最后两个字母，按照升序进行排列。

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

1.  按照dept\_no进行汇总，属于同一个部门的emp\_no按照逗号进行连接，结果给出dept\_no以及连接出的结果employees。

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

1.  查找排除当前最大、最小salary之后的员工的平均工资avg\_salary。

select AVG(salary) as avg_salary
    from salaries
    where salary not in (select MAX(salary) from salaries)
    and salary not in (select MIN(salary) from salaries)
    and to_date='9999-01-01';

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

select * from employees limit 5,5;

1.  获取所有员工的emp\_no、部门编号dept\_no以及对应的bonus类型btype和received ，没有分配具体的员工不显示。

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

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

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

1.  获取employees中的行数据，且这些行也存在于emp\_v中。注意不能使用intersect关键字。

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

1.  获取有奖金的员工相关信息。

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

1.  统计salary的累计和running\_total，其中running\_total为前两个员工的salary累计和，其他以此类推。

select emp_no,salary,(
    select SUM(s2.salary) from salaries s2
    where s1.emp_no >= s2.emp_no and s2.to_date='9999-01-01') as running_total
    from salaries s1
    where s1.to_date='9999-01-01';

1.  对于employees表中，给出奇数行的first\_name。

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

以上是我之前在牛客网做的一些SQL练习，如果发现有的语句存在问题或者有好的写法欢迎留言评论。