SQL基础知识（二）

最新推荐文章于 2023-08-01 21:50:25 发布
最新推荐文章于 2023-08-01 21:50:25 发布
阅读量627 收藏
点赞数
分类专栏： 机试编程 文章标签： 数据库 sqlite3 复试上机 牛客
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Remoa_Dengqinyi/article/details/104474901
版权

SQL基础知识（二）

目录：

  1. 查找所有员工自入职以来的薪水涨幅情况
  2. 统计各个部门对应员工涨幅的次数总和
  3. 对所有员工的当前薪水按照salary进行按照1-N的排名
  4. 获取所有非manager员工当前的薪水情况
  5. 获取员工其当前的薪水比其manager当前薪水还高的相关信息
  6. 汇总各个部门当前员工的title类型的分配数目
  7. 给出每个员工每年薪水涨幅超过5000的员工编号emp_no
  8. 查找描述信息中包括robot的电影对应的分类名称以及电影数目
  9. 使用join查询方式找出没有分类的电影id以及名称
  10. 使用子查询的方式找出属于Action分类的所有电影对应的title,description
  11. 获取select
  12. 将employees表的所有员工的last_name和first_name拼接起来作为Name，中间以一个空格区分
  13. 创建一个actor表，包含如下列信息
  14. 批量插入数据
  15. 批量插入数据,如果数据已经存在，请忽略，不使用replace操作
  16. 创建一个actor_name表，将actor表中的所有first_name以及last_name导入改表
  17. 对first_name创建唯一索引uniq_idx_firstname，对last_name创建普通索引idx_lastname
  18. 针对actor表创建视图actor_name_view
  19. 针对上面的salaries表emp_no字段创建索引idx_emp_no，查询emp_no为10005
  20. 在last_update后面新增加一列名字为create_date
  21. 构造一个触发器audit_log，在向employees表中插入一条数据的时候，触发插入相关的数据到audit中
  22. 删除emp_no重复的记录，只保留最小的id对应的记录
  23. 将所有to_date为9999-01-01的全部更新为NULL,且from_date更新为2001-01-01
  24. 将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变，使用replace实现
  25. 将titles_test表名修改为titles_2017
  26. 在audit表上创建外键约束，其emp_no对应employees_test表的主键id
  27. 如何获取emp_v和employees有相同的数据no
  28. 将所有获取奖金的员工当前的薪水增加10%
  29. 针对库中的所有表生成select
  30. 将employees表中的所有员工的last_name和first_name通过(')连接起来
  31. 查找字符串'10,A,B'
  32. 获取Employees中的first_name，查询按照first_name最后两个字母，按照升序进行排列
  33. 按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果employees
  34. 查找排除当前最大、最小salary之后的员工的平均工资avg_salary
  35. 分页查询employees表，每5行一页，返回第2页的数据
  36. 获取所有员工的emp_no
  37. 使用含有关键字exists查找未分配具体部门的员工的所有信息
  38. 获取有奖金的员工相关信息
  39. 统计salary的累计和running_total
  40. 对于employees表中，给出奇数行的first_name

一、示例题目

1、题目描述：查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_no以及其对应的薪水涨幅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`));
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`));

  • 输出描述：
emp_nogrowth
100110
省略省略
1001054496
1000434003

示例SQL：

SELECT e.emp_no, (b.salary - a.salary) AS growth
FROM employees AS e
INNER JOIN salaries AS a
ON e.emp_no = a.emp_no
AND e.hire_date = a.from_date
INNER JOIN salaries AS b
ON e.emp_no = b.emp_no
AND b.to_date = '9999-01-01'
ORDER BY growth;

2、题目描述：统计各个部门的工资记录数，给出部门编码dept_no、部门名称dept_name以及次数sum
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`));

  • 输出描述：
dept_nodept_namesum
d001Marketing24
d002Finance14
d003Human Resources13
d004Production24
d005Development25
d006Quality Management25

示例SQL：

SELECT departments.dept_no, dept_name, count(*) AS sum
FROM salaries
INNER JOIN dept_emp
ON salaries.emp_no = dept_emp.emp_no
INNER JOIN departments
ON departments.dept_no = dept_emp.dept_no
GROUP BY departments.dept_no;

3、题目描述：对所有员工的当前(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`));

  • 输出描述：
emp_nosalaryrank
10005946921
10009944092
10010944092
10001889583
10007880704

示例SQL：

SELECT a.emp_no, a.salary, COUNT(DISTINCT b.salary) AS RANK
FROM salaries as a, salaries as b
WHERE a.to_date = '9999-01-01'
AND b.to_date = '9999-01-01'
AND a.salary <= b.salary
GROUP BY a.emp_no
ORDER BY a.salary DESC, a.emp_no ASC;

4、题目描述：获取所有非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 `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`));

  • 输出描述：
dept_noemp_nosalary
d0011000188958
d0041000343311
d0051000788070
d0061000995409

样例SQL：

SELECT dept_emp.dept_no, dept_emp.emp_no, salary
FROM employees
LEFT JOIN dept_emp
ON employees.emp_no = dept_emp.emp_no
LEFT JOIN salaries
ON salaries.emp_no = employees.emp_no
WHERE employees.emp_no NOT IN (
    SELECT emp_no
    FROM dept_manager
    WHERE to_date = '9999-01-01'
)
AND dept_emp.to_date = '9999-01-01'
AND salaries.to_date = '9999-01-01';

5、题目描述：获取员工其当前的薪水比其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`));
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`));

  • 输出描述：
emp_nomanager_noemp_salarymanager_salary
10001100028895872527
10009100109540994409

示例SQL：

SELECT dept_emp.emp_no, s2.emp_no AS manager_no, s1.salary AS emp_salary, s2.salary AS manager_salary
FROM dept_emp
INNER JOIN dept_manager
ON dept_manager.dept_no = dept_emp.dept_no
INNER JOIN salaries s1
ON s1.emp_no = dept_emp.emp_no
INNER JOIN salaries s2
ON s2.emp_no = dept_manager.emp_no
WHERE dept_emp.emp_no NOT IN (
    SELECT emp_no
    FROM dept_manager
    WHERE to_date = '9999-01-01'
)
AND dept_emp.to_date = '9999-01-01'
AND s1.to_date = '9999-01-01'
AND s2.to_date = '9999-01-01'
AND s2.salary < s1.salary;

6、题目描述：汇总各个部门当前员工的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);

  • 输出描述：
dept_nodept_nametitlecount
d001MarketingSenior Engineer1
d001MarketingStaff1
d002FinanceSenior Engineer1
d003Human ResourcesSenior Staff1
d004ProductionSenior Engineer2
d005DevelopmentSenior Staff1
d006Quality ManagementEngineer2
d006Quality ManagementSenior Engineer1

样例SQL：

SELECT departments.dept_no, dept_name, title, COUNT(*) AS count
FROM departments
INNER JOIN dept_emp
ON departments.dept_no = dept_emp.dept_no
INNER JOIN titles
ON titles.emp_no = dept_emp.emp_no
WHERE dept_emp.to_date = '9999-01-01'
AND titles.to_date = '9999-01-01'
GROUP BY departments.dept_no, title;

7、题目描述：给出每个员工每年薪水涨幅超过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`));

  • 输出描述：
emp_nofrom_datesalary_growth
100031995-12-0324178
100081998-03-1120843
100082000-03-105997

样例SQL：

SELECT s1.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
WHERE (
    strftime("%Y",s2.to_date) - strftime("%Y",s1.to_date) = 1
    OR strftime("%Y",s2.from_date) - strftime("%Y",s1.from_date) = 1
)
AND salary_growth > 5000
ORDER BY salary_growth DESC;

8、题目描述：查找描述信息中包括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 ));
CREATE TABLE film_category  (
film_id  smallint(5)  NOT NULL,
category_id  tinyint(3)  NOT NULL, `last_update` timestamp);

样例SQL：

SELECT category.name, count(film.film_id)
FROM film, (
    SELECT *
    FROM film_category
    GROUP BY category_id
    HAVING COUNT(*) >= 5
)AS a, category, film_category  
WHERE film.film_id = film_category.film_id
AND category.category_id = a.category_id
AND film_category.category_id = category.category_id
AND film.description like '%robot%';

9、题目描述：使用join查询方式找出没有分类的电影id以及名称
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);

样例SQL：

SELECT film.film_id, title
FROM film
LEFT JOIN film_category
ON film.film_id = film_category.film_id
WHERE film_category.film_id is NULL;

10、题目描述：使用子查询的方式找出属于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);

样例SQL：

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

11、题目描述：获取select * from employees对应的执行计划

样例SQL：

EXPLAIN SELECT * FROM employees;

12、题目描述：将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`));

  • 输出描述：
Name
Facello Georgi
Simmel Bezalel
Bamford Parto
Koblick Chirstian
Maliniak Kyoichi
Preusig Anneke

样例SQL：

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

13、题目描述：创建一个actor表，包含如下列信息

列表类型是否为NULL含义
actor_idsmallint(5)not null主键id
first_namevarchar(45)not null名字
last_namevarchar(45)not null姓氏
last_updatetimestampnot null最后更新时间，默认是系统的当前时间

样例SQL：

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

14、题目描述：对于表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_idfirst_namelast_namelast_update
1PENELOPEGUINESS2006-02-15 12:34:33
2NICKWAHLBERG2006-02-15 12:34:33

样例SQL：

INSERT INTO actor
VALUES(1, 'PENELOPE', 'GUINESS', '2006-02-15 12:34:33'),
(2, 'NICK', 'WAHLBERG', '2006-02-15 12:34:33');

15、题目描述：对于表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')))

actor_idfirst_namelast_namelast_update
'3''ED''CHASE''2006-02-15 12:34:33'

样例SQL：

INSERT OR IGNORE INTO actor
VALUES(3, 'ED', 'CHASE', '2006-02-15 12:34:33');

16、题目描述：对于如下表actor，其对应的数据为：

actor_idfirst_namelast_namelast_update
1PENELOPEGUINESS2006-02-15 12:34:33
2NICKWAHLBERG2006-02-15 12:34:33

创建一个actor_name表，将actor表中的所有first_name以及last_name导入改表。 actor_name表结构如下：

列表类型是否为NULL含义
first_namevarchar(45)not null名字
last_namevarchar(45)not null姓氏

示例SQL：

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;

17、题目描述：针对如下表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

示例SQL：

CREATE UNIQUE INDEX uniq_idx_firstname ON actor(first_name);
CREATE INDEX idx_lastname ON actor(last_name);

18、题目描述：针对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')))

示例SQL：

CREATE VIEW actor_name_view AS
SELECT first_name AS first_name_v, last_name AS last_name_v
FROM actor ;

19、题目描述：针对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);

示例SQL：

SELECT *
FROM salaries
FORCED INDEXED BY idx_emp_no
WHERE emp_no = 10005;

附注：

（1）MySQL中，强制索引使用为：SELECT * FROM salaries FORCE INDEX (idx_emp_no) WHERE emp_no = 10005

20、题目描述：存在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:00:00'

ALTER TABLE actor
ADD COLUMN create_date datetime NOT NULL DEFAULT('0000-00-00 00:00:00');

21、题目描述：构造一个触发器audit_log，在向employees_test表中插入一条数据的时候，触发插入相关的数据到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 TABLE audit(
EMP_no INT NOT NULL,
NAME TEXT NOT NULL
);

示例SQL：

CREATE TRIGGER audit_log AFTER INSERT
ON employees_test
BEGIN
    INSERT INTO audit(EMP_no, NAME)
    VALUES(NEW.ID, NEW.NAME);
END;

22、题目描述：删除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);

示例SQL：

DELETE FROM titles_test
WHERE id NOT IN(
    SELECT MIN(id)
    FROM titles_test
    GROUP BY emp_no
);

23、题目描述：将所有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);

示例SQL：

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

24、题目描述：将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);

示例SQL：

UPDATE titles_test
SET emp_no = REPLACE(emp_no, 10001, 10005)
WHERE id = 5;

25、题目描述：将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);

示例SQL：

ALTER TABLE titles_test RENAME TO titles_2017;

26、题目描述：在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
);
CREATE TABLE audit(
EMP_no INT NOT NULL,
create_date datetime NOT NULL
);

示例SQL：

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

27、题目描述：存在如下的视图：
create view emp_v as select * from employees where emp_no >10005;
如何获取emp_v和employees有相同的数据？
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`));

  • 输出描述：
emp_nobirth_datefirst_namelast_namegenderhire_date
100061953-04-20AnnekePreusigF1989-06-02
100071957-05-23TzvetanZielinskiF1989-02-10
100081958-02-19SaniyaKalloufiM1994-09-15
100091952-04-19SumantPeacF1985-02-18
100101963-06-01DuangkaewPiveteauF1989-08-24
100111953-11-07MarySluisF1990-01-22

示例SQL：

SELECT *
FROM emp_v;

28、题目描述：将所有获取奖金的员工当前的薪水增加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`));

示例SQL：

UPDATE salaries
SET salary = salary * 1.1
WHERE emp_no IN (
    SELECT salaries.emp_no
    FROM salaries
    INNER JOIN emp_bonus
    ON emp_bonus.emp_no = salaries.emp_no
    WHERE to_date = '9999-01-01'
);

29、题目描述：针对库中的所有表生成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 `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`));

  • 输出描述：
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;

示例SQL：

SELECT 'select count(*) from ' || name || ';' AS cnts
FROM sqlite_master
WHERE type = 'table';

30、题目描述：将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`));

  • 输出描述：
name
Facello'Georgi
Simmel'Bezalel
Bamford'Parto
Koblick'Chirstian
Maliniak'Kyoichi
Preusig'Anneke
Zielinski'Tzvetan
Kalloufi'Saniya
Peac'Sumant
Piveteau'Duangkaew
Sluis'Mary

示例SQL：

SELECT last_name || "'" || first_name
FROM employees;

31、题目描述：查找字符串'10,A,B' 中逗号','出现的次数cnt。

示例SQL：

SELECT LENGTH("10,A,B") - LENGTH(REPLACE("10,A,B",",","")) AS cnt;

32、题目描述：获取Employees中的first_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`));

  • 输出描述：
first_name
Chirstian
Tzvetan
Bezalel
Duangkaew
Georgi
Kyoichi
Anneke
Sumant
Mary
Parto
Saniya

示例SQL：

SELECT first_name
FROM employees
ORDER BY SUBSTR(first_name, LENGTH(first_name) - 1, 2) ASC;

附注：

（1）substr(string string,num start,num length)：string为字符串；start为起始位置，第一个字符的位置为1，不是从0开始计算；length为要截取的长度。

33、题目描述：按照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`));

  • 输出描述：
dept_noemployees
d00110001,10002
d00210006
d00310005
d00410003,10004
d00510007,10008,10010
d00610009,10010

示例SQL：

SELECT dept_no, GROUP_CONCAT(emp_no, ",") AS employees
FROM dept_emp
GROUP BY dept_no;

附注：

（1）聚合函数：group_concat(x[,y])：x为连接字段，y为连接分隔符（可省略，默认为逗号）。该函数要和group by一起使用

34、题目描述：查找排除当前最大、最小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

示例SQL：

SELECT AVG(salary) AS avg_salary
FROM salaries
WHERE salary != (
    SELECT MAX(salary)
    FROM salaries
)
AND salary != (
    SELECT MIN(salary)
    FROM salaries
)
AND to_date = '9999-01-01';

35、题目描述：分页查询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`));

示例SQL：

SELECT *
FROM employees
LIMIT 5 OFFSET 5;

附注：

（1）select * from employees order by xxx limit xxx offset xxx中limit：返回显示记录，offset：从第几条记录开始返回（第一条记录为0）。

36、题目描述：获取所有员工的emp_no、部门编号dept_no以及对应的bonus类型btype和received ，没有分配具体的员工不显示。
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,
received datetime not null,
btype smallint not null);

  • 输出描述：
emp_nodept_nobtypereceived
10001d00112010-01-01
10002d00122010-10-01
10003d00432011-12-03
10004d00412010-01-01
10005d003 
10006d002 
10007d005 
10008d005 
10009d006 
10010d005 
10010d006 

示例SQL：

SELECT employees.emp_no, dept_emp.dept_no, emp_bonus.btype, emp_bonus.received
FROM employees
INNER JOIN dept_emp
ON dept_emp.emp_no = employees.emp_no
LEFT JOIN emp_bonus
ON emp_bonus.emp_no = employees.emp_no;

37、题目描述：使用含有关键字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`));
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`));

  • 输出描述：
emp_nobirth_datefirst_namelast_namegenderhire_date
100111953-11-07MarySluisF1990-01-22

示例SQL：

SELECT *
FROM employees
WHERE NOT EXISTS(
    SELECT emp_no
    FROM dept_emp
    WHERE employees.emp_no = dept_emp.emp_no
);

38、获取有奖金的员工相关信息。
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`));
给出emp_no、first_name、last_name、奖金类型btype、对应的当前薪水情况salary以及奖金金额bonus。 bonus类型btype为1其奖金为薪水salary的10%，btype为2其奖金为薪水的20%，其他类型均为薪水的30%。 当前薪水表示to_date='9999-01-01'

  • 输出描述：
emp_nofirst_namelast_namebtypesalarybonus
10001GeorgiFacello1889588895.8
10002BezalelSimmel27252714505.4
10003PartoBamford34331112993.3
10004ChirstianKoblick1740577405.7

示例SQL1：

SELECT employees.emp_no, first_name, last_name, btype, salary, salary*btype*0.1 AS bonus
FROM employees
INNER JOIN salaries
ON employees.emp_no = salaries.emp_no
INNER JOIN emp_bonus
ON emp_bonus.emp_no  = employees.emp_no
WHERE salaries.to_date = '9999-01-01';

示例SQL2：

SELECT employees.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
INNER JOIN salaries
ON employees.emp_no = salaries.emp_no
INNER JOIN emp_bonus
ON emp_bonus.emp_no  = employees.emp_no
WHERE salaries.to_date = '9999-01-01';

39、题目描述：按照salary的累计和running_total，其中running_total为前两个员工的salary累计和，其他以此类推。 具体结果如下Demo展示。。
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`));

  • 输出描述：
emp_nosalaryrunning_total
100018895888958
1000272527161485
1000343311204796
1000474057278853
1000594692373545
1000643311416856
1000788070504926
1000995409600335
1001094409694744
1001125828720572

样例SQL：

SELECT s1.emp_no, s1.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'
ORDER BY s1.emp_no ASC;

40、题目描述：对于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`));

  • 输出描述：
first_name
Georgi
Chirstian
Anneke
Tzvetan
Saniya
Mary

示例SQL：

SELECT e1.first_name
FROM employees e1
WHERE (
    SELECT count(*)
    FROM employees e2
    WHERE e1.first_name >= e2.first_name
)%2 = 1;

 

Remoa
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
SQL基础知识
PassionZheng的博客
01-05 375
文章目录一.联结表（join）1.创建联结表1.1 Where子句的重要性（等值联结与笛卡尔积）1.2 内联接1.3 联结多个表2.创建高级联结2.1 使用表别名2.2 使用不同类型的联结1.自联结（self join）2.自然联结（nature join）3.外联结（outer join）4.使用带聚集函数的联结总结：二.组合查询1.UNION规则：2.包含或取消重复的行3.对组合查询进行排序4...
SQL练习39：针对上面的salaries表emp_no字段创建索引idx_emp_no，查询emp_no为10005
向日葵的专属太阳
02-07 590
SQL练习39：针对上面的salaries表emp_no字段创建索引idx_emp_no，查询emp_no为10005 题目链接：牛客网 题目描述 针对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 NO
数据库SQL实战 --37.针对salaries表emp_no字段创建索引idx_emp_no
我在阴山下
04-30 649
题目描述 针对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, PRIMA...
MySQL的varchar(45)可以存放多少个汉字
永立的专栏
05-21 6774
说明 MySQL在4版本以前，用的是字节存储，一个UTF8汉字占用3字节，故，varchar(45)可以存放15个汉字； MySQL在5版本以后，用的是字符存储，一个UTF8汉字和一个英文字母一样，也是一个字符，故，varchar(45)可以存放45个汉字； 测试 参考： https://stackoverflow.com/questions/1997540/mysql-varchar-len...
初识MySQL（一）
m0_74199136的博客
12-11 363
初识MySQL数据库（Database）以及初步了解数据库的基本操作
mysql varchar 45_mysql varchar(45) utf8mb4_general_ci 可以存多少个汉字
weixin_42120283的博客
02-03 1388
1.存储单位MySQL在5版本以后，varchar(45)，指的是45个字符，可以存放45个汉字，或者45个英文字母(一个UTF8汉字和一个英文字母一样，都占用1个字符)。MySQL在4版本以前，varchar(45)，指的是45个字节，可以存放15个汉字，或者45个英文字母(一个UTF8汉字占用3个字节，一个英文字母占用1个字节)。2.字符编码在UTF-8编码中，一个字符占用三个字节的存储空间；...
SQL基础知识第2版.pdf
10-08
非常详细的SQL基础知识，适合新手学习，高手复习！
SQL基础知识.zip
03-28
SQL基础知识，PDF版书籍
SQL基础知识 精品文档.docx
10-25
SQL 基础知识精要 SQL 是一种结构化查询语言，用于操作数据库。SQL 语言是数据库的标准语言，无论何时访问一个数据库，都需要使用 SQL 语言。 一、 SQL 介绍 SQL 是一种标准语言，用于操作数据库。SQL 语言可以...
OracleSQL基础知识论述.pptx
04-17
Oracle SQL 基础知识论述 Oracle SQL 是一种基于关系型数据库管理系统（RDBMS）的查询语言，由 IBM Corporation, Inc. 开发，用于实现 Dr. E. F. Codd 的关系模型。1979 年，Relational Software, Inc.（现在的 ...
SQL学习笔记一SQL基础知识
09-11
以下是一些关于SQL基础知识的详细说明： 1. 字段类型： - `bit`：用于存储0或1的值，通常用于布尔型数据。 - `datetime`：存储日期和时间，精确到毫秒级别。 - `int`：整数类型，存储整数值。 - `varchar`：可...
SQL29 使用join查询方式找出没有分类的电影id以及名称
Robin_Pi的博客
02-13 338
SQL29 使用join查询方式找出没有分类的电影id以及名称
网络笔记：0301、数据库基础
最新发布
m0_59115955的博客
08-01 77
(2) varchar(45)：可变长度的，最长是45，实际存”Hello“,数据库最多存8000个字节，varchar存8000个字符。(2)插入部分列数据： insert into 表名(列名1，列名2...) values(数据1，数据2.....)(1) char(45)：字符串长度必须为45，存"Hello",实际存”Hello000000......“。(3) nvarchar(45)：n宽字节，一个字符占两个字节，最多存4000个字符。
（中等）SQL练习29：使用join查询方式找出没有分类的电影id以及名称
向日葵的专属太阳
02-05 1099
SQL练习29：使用join查询方式找出没有分类的电影id以及名称 题目链接：牛客网 题目描述 使用join查询方式找出没有分类的电影id以及名称。 film表 字段 说明 film_id 电影id title 电影名称 description 电影描述信息 category表 字段 说明 category_id 电影分类id name 电影分类名称 last_update 电影分类最后更新时间 film_category表 字段 说明
针对salaries表emp_no字段创建索引idx_emp_no，查询emp_no为10005, 使用强制索引。
为什么他的emp没后座
03-17 999
问题描述 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_...
数据库针对上面的salaries表emp_no字段创建索引idx_emp_no
不爱赖床的懒虫博客
04-08 274
针对上面的salaries表emp_no字段创建索引idx_emp_no 针对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 NUL...
mysql的触发器
weixin_44945027的博客
10-11 419
mysql的触发器 CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt 例如 CREATE TRIGGER chufaqi AFTER INSERT ON mytest FOR EACH ROW INSERT INTO `mytest1` (`id`, `name`, `age`, `sex`) VALUES('1','啊啊啊','1','1'); trigger_nam
机试编程（四）——数学问题（一）
Remoa的休闲茶馆
03-04 1143
一、基础知识 1、进制转换 2、最大公约数和最小公倍数 3、分解质因数 4、质数（素数） 二、应用实例 1、二进制数【北京邮电大学】 2、进制转换【清华大学】 3、10进制VS2进制【清华大学】 4、进制转换2【清华大学】 5、八进制【华中科技大学】 6、又一版A+B【浙江大学】 7、进制转换【北京大学】 8、数制转换【北京大学】 9、最大公约数【计算机历年考研复试上机题】 10、最小公倍数【华为机试在线训练】 11、最简真分数【北京大学】 12、素数判定【哈尔滨工业大学】 13、素数【北京航天航空大学】

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值