美文网首页
牛客网SQL实战练习——26~40

牛客网SQL实战练习——26~40

作者: 西红柿炒番茄007 | 来源:发表于2020-03-31 11:42 被阅读0次

    牛客网SQL实战练习——26~40

    声明:练习牛客网SQL实战题目,整理笔记!
    26.汇总各个部门当前员工的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 `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);
    

    分析:本题的关键句在于当前员工所有的title以及该类型title对应的数目count,所以应该以dept_no和title进行分组。即group by b.dept_no,t.title,然后依次加上其他条件即可。
    参考代码:

    select b.dept_no,b.dept_name,t.title,count(*) count
    from titles as t,dept_emp as a,departments as b
    where a.dept_no=b.dept_no 
    and t.emp_no=a.emp_no
    and a.to_date='9999-01-01' 
    and t.to_date='9999-01-01'
    group by b.dept_no,t.title;
    

    27.给出每个员工每年薪水涨幅超过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`));
    

    分析:很显然,本题要复用salaries表,两张表内连接,限定为同一名员工,工资变更开始的日期 限定为1年,工资差大于5000,按照工资差降序排列。
    参考代码:

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

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

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

    分析:尤其注意:5部指各分类下面的电影总数>=5,不是指经robot等条件筛选后,分类下面的电影数目>=5,所以,筛选后的分类必须是分类后电影总数>=5的分类。使用嵌套查询控制各分类下面的电影总数>=5.
    参考代码:

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

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

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

    分析:LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行,即使在右表 (table_name2) 中没有匹配的行。所以这里使用left join on 关键字。最后使用not in 关键字以及嵌套查询,除去分类的电影。
    参考代码:

    select f.film_id,f.title
    from film as f left join film_category as fc
    on f.film_id = fc.film_id
    where f.film_id not in (select film_id from film_category);
    

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

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

    分析:本题中题目要求使用子查询,那么按照要求,使用两次子查询,第一次子查询使用film_id连接film与film_category两个表,第二次子查询使用category_id 连接film_category与category两个表,最后加上限制条件 where name like 'Action'即可。
    参考代码:

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

    31.获取select * from employees对应的执行计划
    分析:explain可以描述表的细节。查看SQL的执行计划,多用于发现优化性能的点。
    参考代码:

    explain select * 
    from employees
    

    32.将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`));
    

    分析:MySQL、SQL Server、Oracle等数据库支持CONCAT方法,
    而本题所用的SQLite数据库只支持用连接符号"||"来连接字符串
    参考代码:

    select last_name||" "||first_name as Name 
    from employees
    

    33.创建一个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 最后更新时间,默认是系统的当前时间
    分析:获取系统默认时间是datetime(‘now’,'localtime')
    参考代码:

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

    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')))
    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 
    values
    (1,"PENELOPE","GUINESS","2006-02-15 12:34:33"),
    (2,"NICK","WAHLBERG","2006-02-15 12:34:33");
    

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

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

    分析:在 SQLite 中,用 INSERT OR IGNORE 来插入记录,或忽略插入与表内UNIQUE字段都相同的记录。
    参考代码:

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

    36.对于如下表actor,其对应的数据为:
    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表,将actor表中的所有first_name以及last_name导入改表。 actor_name表结构如下
    列表 类型 是否为NULL 含义
    first_name varchar(45) not null 名字
    last_name varchar(45) not null 姓氏
    分析:题目使用的是sqlite3,如果是mysql,那么as可以去掉,也可以不去掉。
    参考代码:

    create table actor_name as
    select first_name,last_name from actor;
    

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

    对first_name创建唯一索引uniq_idx_firstname,对last_name创建普通索引idx_lastname
    分析:create (unique) index 索引名 on 表名(列名)
    参考代码:

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

    38.针对actor表创建视图actor_name_view,只包含first_name以及last_name两列,并对这两列重新命名,first_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 <视图名称> (<视图列名1>,<视图列名2>…)
    AS
    <select 语句>;
    参考代码:

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

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

    分析:MYSQL中强制索引查询使用:FORCE INDEX(indexname);
    SQLite中强制索引查询使用:INDEXED BY indexname;
    参考代码:

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

    40.存在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 ... ADD ... 语句可以向已存在的表插入新字段,并且能够与创建表时一样,在字段名和数据类型后加入NOT NULL、DEFAULT等限定。
    参考代码:

    alter table actor
    add `create_date` datetime not null default '0000-00-00 00:00:00'
    

    欢迎关注微信公众号:蛋炒番茄
    同步更新!!!

    相关文章

      网友评论

          本文标题:牛客网SQL实战练习——26~40

          本文链接:https://www.haomeiwen.com/subject/tncwuhtx.html