刷SQL 题目练习笔记 简单难度

前言

想学好数据库.SQL语句,写在这里,一是当笔记,二是分享交流,三是给自己一点动力,也是压力 加油!

SQL13 从titles表获取按照title进行分组

从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);
如插入：
INSERT INTO titles VALUES(10001,‘Senior Engineer’,‘1986-06-26’,‘9999-01-01’);
INSERT INTO titles VALUES(10002,‘Staff’,‘1996-08-03’,‘9999-01-01’);
INSERT INTO titles VALUES(10003,‘Senior Engineer’,‘1995-12-03’,‘9999-01-01’);
INSERT INTO titles VALUES(10004,‘Engineer’,‘1986-12-01’,‘1995-12-01’);
INSERT INTO titles VALUES(10004,‘Senior Engineer’,‘1995-12-01’,‘9999-01-01’);
INSERT INTO titles VALUES(10005,‘Senior Staff’,‘1996-09-12’,‘9999-01-01’);
INSERT INTO titles VALUES(10005,‘Staff’,‘1989-09-12’,‘1996-09-12’);
INSERT INTO titles VALUES(10006,‘Senior Engineer’,‘1990-08-05’,‘9999-01-01’);
INSERT INTO titles VALUES(10007,‘Senior Staff’,‘1996-02-11’,‘9999-01-01’);
INSERT INTO titles VALUES(10007,‘Staff’,‘1989-02-10’,‘1996-02-11’);
INSERT INTO titles VALUES(10008,‘Assistant Engineer’,‘1998-03-11’,‘2000-07-31’);
INSERT INTO titles VALUES(10009,‘Assistant Engineer’,‘1985-02-18’,‘1990-02-18’);
INSERT INTO titles VALUES(10009,‘Engineer’,‘1990-02-18’,‘1995-02-18’);
INSERT INTO titles VALUES(10009,‘Senior Engineer’,‘1995-02-18’,‘9999-01-01’);
INSERT INTO titles VALUES(10010,‘Engineer’,‘1996-11-24’,‘9999-01-01’);
INSERT INTO titles VALUES(10010,‘Engineer’,‘1996-11-24’,‘9999-01-01’);

SQL15查找employees表所有emp_no为奇数

题目描述
查找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`));

如插入：

INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10003,'1959-12-03','Parto','Bamford','M','1986-08-28');
INSERT INTO employees VALUES(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01');
INSERT INTO employees VALUES(10005,'1955-01-21','Kyoichi','Maliniak','M','1989-09-12');
INSERT INTO employees VALUES(10006,'1953-04-20','Anneke','Preusig','F','1989-06-02');
INSERT INTO employees VALUES(10007,'1957-05-23','Tzvetan','Zielinski','F','1989-02-10');
INSERT INTO employees VALUES(10008,'1958-02-19','Saniya','Kalloufi','M','1994-09-15');
INSERT INTO employees VALUES(10009,'1952-04-19','Sumant','Peac','F','1985-02-18');
INSERT INTO employees VALUES(10010,'1963-06-01','Duangkaew','Piveteau','F','1989-08-24');
INSERT INTO employees VALUES(10011,'1953-11-07','Mary','Sluis','F','1990-01-22');

答案:

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

SQL20 查找员工编号emp_no为10001其自入职以来的薪水salary涨幅(总共涨了多少)growth

查找员工编号emp_no为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 max(salary)-min(salary) as growth
from salaries
where emp_no=10001
group by emp_no

先group by 然后选出emp_no =10001

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

(注：sqllite,字符串拼接为 || 符号，不支持concat函数，mysql支持concat函数)
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 concat_ws(space(1),last_name,first_name) as Name from employees