【SQL】牛客网SQL训练Part2 中等难度

查找当前薪水详情以及部门编号dept_no

查找

1、各个部门当前领导的薪水详情以及其对应部门编号dept_no，

2、输出结果以salaries.emp_no升序排序，

3、并且请注意输出结果里面dept_no列是最后一列

drop table if exists  `salaries` ; 
drop table if exists  `dept_manager` ; 
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 `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
INSERT INTO dept_manager VALUES('d001',10002,'9999-01-01');
INSERT INTO dept_manager VALUES('d002',10006,'9999-01-01');
INSERT INTO dept_manager VALUES('d003',10005,'9999-01-01');
INSERT INTO dept_manager VALUES('d004',10004,'9999-01-01');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
INSERT INTO salaries VALUES(10004,74057,'2001-11-27','9999-01-01');
INSERT INTO salaries VALUES(10005,94692,'2001-09-09','9999-01-01');
INSERT INTO salaries VALUES(10006,43311,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10007,88070,'2002-02-07','9999-01-01');

-- 按员工号联表查询
SELECT
	s.*,
	d.`dept_no`
FROM 
	`salaries` AS s
	LEFT JOIN `dept_manager` AS d ON s.`emp_no` = d.`emp_no`
WHERE 
	d.`dept_no` IS NOT NULL -- 必须要有部门才可以
	AND d.TO_DATE = '9999-01-01'  -- 日期必须是当前时间
ORDER BY s.`emp_no` ASC -- 按薪资表的员工号排序

查找所有员工的last_name和first_name以及对应部门编号dept_no

包括暂时没有分配具体部门的员工

drop table if exists  `dept_emp` ; 
drop table if exists  `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`));
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 dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d002','1996-08-03','9999-01-01');
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');

-- 左查询完成
SELECT
	e.`last_name`,
	e.`first_name`,
	e_no.`dept_no`
FROM 
	`employees` AS e
	LEFT JOIN `dept_emp` AS e_no ON e.`emp_no` = e_no.`emp_no`

获取所有员工当前的manager

获取所有的员工和员工对应的经理，如果员工本身是经理的话则不显示

drop table if exists  `dept_emp` ; 
drop table if exists  `dept_manager` ; 
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`));
INSERT INTO dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01');
INSERT INTO dept_emp VALUES(10003,'d002','1995-12-03','9999-01-01');
INSERT INTO dept_manager VALUES('d001',10002,'1996-08-03','9999-01-01');
INSERT INTO dept_manager VALUES('d002',10003,'1990-08-05','9999-01-01');

-- 1、先用部门进行关联，2、再对员工则进行不等关联，3、取指定时间
SELECT
	emp.emp_no,
	mgr.emp_no AS manager
FROM
	`dept_emp` AS emp
	 JOIN `dept_manager` AS mgr ON emp.dept_no = mgr.dept_no AND emp.emp_no != mgr.emp_no
WHERE 
	mgr.to_date = '9999-01-01'
	AND emp.to_date = '9999-01-01'

统计出当前各个title类型对应的员工当前薪水对应的平均工资

统计出各个title类型对应的员工薪水对应的平均工资avg。

结果给出title以及平均工资avg，并且以avg升序排序

drop table if exists  `salaries` ; 
drop table if exists  titles;
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 titles (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);
INSERT INTO salaries VALUES(10001,88958,'1986-06-26','9999-01-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
INSERT INTO salaries VALUES(10004,74057,'1995-12-01','9999-01-01');
INSERT INTO salaries VALUES(10006,43311,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10007,88070,'2002-02-07','9999-01-01');

INSERT INTO titles VALUES(10001,'Senior Engineer','1986-06-26','9999-01-01');
INSERT INTO titles VALUES(10003,'Senior Engineer','2001-12-01','9999-01-01');
INSERT INTO titles VALUES(10004,'Senior Engineer','1995-12-01','9999-01-01');
INSERT INTO titles VALUES(10006,'Senior Engineer','2001-08-02','9999-01-01');
INSERT INTO titles VALUES(10007,'Senior Staff','1996-02-11','9999-01-01');

-- 联表，然后分组处理，计算AVG薪资
SELECT 
	t.title,AVG(s.salary) AS `avg_salary`
FROM 
	`salaries` AS s
	LEFT JOIN `titles` AS t ON s.emp_no = t.emp_no
GROUP BY t.title 
ORDER BY `avg_salary` ASC

查找所有员工的last_name和first_name以及对应的dept_name

查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工

drop table if exists  `departments` ; 
drop table if exists  `dept_emp` ; 
drop table if exists  `employees` ; 
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`));
INSERT INTO departments VALUES('d001','Marketing');
INSERT INTO departments VALUES('d002','Finance');
INSERT INTO departments VALUES('d003','Human Resources');
INSERT INTO dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01');
INSERT INTO dept_emp VALUES(10003,'d002','1990-08-05','9999-01-01');
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');

-- 全部左连，因为查询所有员工，所以员工表是主表
SELECT
	E.last_name,
	E.first_name,
	D.dept_name
FROM
	employees AS E
	LEFT JOIN dept_emp AS DE ON E.emp_no = DE.emp_no
	LEFT JOIN departments AS D ON D.dept_no = DE.dept_no

统计各个部门的工资记录数

给出部门编码dept_no、部门名称dept_name以及部门在salaries表里面有多少条记录sum，

按照dept_no升序排序

drop table if exists  `departments` ; 
drop table if exists  `dept_emp` ; 
drop table if exists  `salaries` ; 
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`));
INSERT INTO departments VALUES('d001','Marketing');
INSERT INTO departments VALUES('d002','Finance');
INSERT INTO dept_emp VALUES(10001,'d001','2001-06-22','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01');
INSERT INTO dept_emp VALUES(10003,'d002','1996-08-03','9999-01-01');
INSERT INTO salaries VALUES(10001,85097,'2001-06-22','2002-06-22');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'1996-08-03','9999-01-01');
INSERT INTO salaries VALUES(10003,32323,'1996-08-03','9999-01-01');

-- 1、各个部门，即按主表先查询，然后是中间表和薪资表
-- 2、不能对不查询的字段进行分组，所以GroupBy只能对部门号和部门名分组
-- 3、分组后对其他字段进行COUNT计数即可
SELECT
 D.dept_no,
 D.dept_name,
 COUNT(S.salary) AS sum
FROM
	`departments` AS D
	LEFT JOIN dept_emp AS DE ON D.dept_no = DE.dept_no
	LEFT JOIN salaries AS S ON DE.emp_no = S.emp_no
GROUP BY D.dept_no
ORDER BY D.dept_no ASC

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

drop table if exists  film ;
drop table if exists  category  ; 
drop table if exists  film_category  ; 
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);
INSERT INTO film VALUES(1,'ACADEMY DINOSAUR','A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies');
INSERT INTO film VALUES(2,'ACE GOLDFINGER','A Astounding Epistle of a Database Administrator And a Explorer who must Find a Car in Ancient China');
INSERT INTO film VALUES(3,'ADAPTATION HOLES','A Astounding Reflection of a Lumberjack And a Car who must Sink a Lumberjack in A Baloon Factory');

INSERT INTO category VALUES(1,'Action','2006-02-14 20:46:27');
INSERT INTO category VALUES(2,'Animation','2006-02-14 20:46:27');
INSERT INTO category VALUES(3,'Children','2006-02-14 20:46:27');
INSERT INTO category VALUES(4,'Classics','2006-02-14 20:46:27');
INSERT INTO category VALUES(5,'Comedy','2006-02-14 20:46:27');
INSERT INTO category VALUES(6,'Documentary','2006-02-14 20:46:27');
INSERT INTO category VALUES(7,'Drama','2006-02-14 20:46:27');
INSERT INTO category VALUES(8,'Family','2006-02-14 20:46:27');
INSERT INTO category VALUES(9,'Foreign','2006-02-14 20:46:27');
INSERT INTO category VALUES(10,'Games','2006-02-14 20:46:27');
INSERT INTO category VALUES(11,'Horror','2006-02-14 20:46:27');

INSERT INTO film_category VALUES(1,6,'2006-02-14 21:07:09');
INSERT INTO film_category VALUES(2,11,'2006-02-14 21:07:09');

-- 左连接 + IS NULL 
SELECT
	F.film_id,
	F.title
FROM
	film AS F
	LEFT JOIN film_category AS FC ON F.film_id = FC.film_id
	LEFT JOIN category AS C ON FC.category_id = C.category_id
WHERE FC.film_id IS NULL

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

drop table if exists   film ;
drop table if exists  category  ; 
drop table if exists  film_category  ; 
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);
INSERT INTO film VALUES(1,'ACADEMY DINOSAUR','A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies');
INSERT INTO film VALUES(2,'ACE GOLDFINGER','A Astounding Epistle of a Database Administrator And a Explorer who must Find a Car in Ancient China');
INSERT INTO film VALUES(3,'ADAPTATION HOLES','A Astounding Reflection of a Lumberjack And a Car who must Sink a Lumberjack in A Baloon Factory');

INSERT INTO category VALUES(1,'Action','2006-02-14 20:46:27');
INSERT INTO category VALUES(2,'Animation','2006-02-14 20:46:27');
INSERT INTO category VALUES(3,'Children','2006-02-14 20:46:27');
INSERT INTO category VALUES(4,'Classics','2006-02-14 20:46:27');
INSERT INTO category VALUES(5,'Comedy','2006-02-14 20:46:27');
INSERT INTO category VALUES(6,'Documentary','2006-02-14 20:46:27');

INSERT INTO film_category VALUES(1,1,'2006-02-14 21:07:09');
INSERT INTO film_category VALUES(2,1,'2006-02-14 21:07:09');
INSERT INTO film_category VALUES(3,6,'2006-02-14 21:07:09');

-- 子查询
-- 第一步查询分类类型为action的记录，只取主键
SELECT category_id FROM category WHERE `name` = 'action'

-- 第二步通过中间表获取电影的主键列表 只取电影主键
SELECT film_id FROM film_category WHERE category_id = (
SELECT category_id FROM category WHERE `name` = 'action'
)
-- 第三步 查询电影表 嵌套上述的子查询
SELECT `title`, `description`  FROM film WHERE film_id IN (
  SELECT film_id FROM film_category WHERE category_id = (
    SELECT category_id FROM category WHERE `name` = 'action'
  )
)

创建一个actor表

-- 直接把题目的表格粘贴过来改改就行了
CREATE TABLE `actor` (
  actor_id smallint(5)	not null comment '主键id',
  first_name varchar(45) not null comment '名字',
  last_name varchar(45) not null comment '姓氏',
  last_update date not null comment '日期'
)

批量插入数据，不使用replace操作

对于表actor插入如下数据,如果数据已经存在，请忽略

(不支持使用replace操作)

drop table if exists actor;
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  DATETIME NOT NULL);
insert into actor values ('3', 'WD', 'GUINESS', '2006-02-15 12:34:33');


# mysql中常用的三种插入数据的语句:
# insert into表示插入数据，数据库会检查主键，如果出现重复会报错；
# replace into表示插入替换数据，需求表中有PrimaryKey，
#             或者unique索引，如果数据库已经存在数据，则用新数据替换，如果没有数据效果则和insert into一样；
# insert ignore表示，如果中已经存在相同的记录，则忽略当前新数据；
insert ignore into actor values("3","ED","CHASE","2006-02-15 12:34:33");

对first_name创建唯一索引uniq_idx_firstname

针对如下表actor结构创建索引：

(注:在 SQLite 中,除了重命名表和在已有的表中添加列,ALTER TABLE 命令不支持其他操作，

mysql支持ALTER TABLE创建索引)

对first_name创建唯一索引uniq_idx_firstname，对last_name创建普通索引idx_lastname

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  datetime NOT NULL);

-- ALTER TABLE 语法添加
ALTER TABLE `now-coder-sql`.`actor` 
ADD UNIQUE INDEX `UNIQ_IDX_FIRST_NAME`(`first_name`) USING BTREE;

ALTER TABLE `now-coder-sql`.`actor` 
ADD INDEX `IDX_LAST_NAME`(`last_name`) USING BTREE;

针对actor表创建视图actor_name_view

actor表创建视图actor_name_view，只包含first_name以及last_name两列，并对这两列重新命名，

first_name为first_name_v，last_name修改为last_name_v：

后台会插入2条数据:

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

然后打印视图名字和插入的数据

drop table if exists actor;
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 datetime NOT NULL);
insert into actor values 
('1', 'PENELOPE', 'GUINESS', '2006-02-15 12:34:33'), 
('2', 'NICK', 'WAHLBERG', '2006-02-15 12:34:33');


-- 对查询之前编写一个视图SQL创建语法
CREATE VIEW `now-coder-sql`.`actor_name_view` AS 

SELECT 
	first_name AS `first_name_v`,
	last_name AS `last_name_v`,
FROM actor;

-- 调用视图
SELECT * FROM actor_name_view

针对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 NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
create index idx_emp_no on salaries(emp_no);

-- 强制使用索引语法
SELECT * 
FROM salaries 
FORCE INDEX (idx_emp_no)
WHERE emp_no = 10005

强制使用索引：

https://www.yangdx.com/2020/05/151.html

在last_update后面新增加一列名字为create_date

在last_update后面新增加一列名字为create_date, 类型为datetime, NOT NULL，默认值为'2020-10-01 00:00:00'

drop table if exists actor;
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  datetime NOT NULL);

-- 指定字段后面追加
ALTER TABLE actor 
ADD COLUMN `create_date` datetime NOT NULL DEFAULT '2020-10-01 00:00:00' 
AFTER `last_update` ;

构造一个触发器audit_log

构造一个触发器audit_log，在向employees_test表中插入一条数据的时候，触发插入相关的数据到audit中。

后台会往employees_test插入一条数据:

INSERT INTO employees_test (ID,NAME,AGE,ADDRESS,SALARY)VALUES (1, 'Paul', 32, 'California', 20000.00 );

然后从audit里面使用查询语句:

select * from audit;

drop table if exists audit;
drop table if exists employees_test;

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

-- 创建表格
CREATE TRIGGER `audit_log` 
AFTER INSERT ON `employees_test` FOR EACH ROW 
INSERT INTO audit VALUES(NEW.ID, NEW.`NAME`);

-- 插入测试
INSERT INTO employees_test (ID,NAME,AGE,ADDRESS,SALARY) VALUES 
(1, 'Paul', 32, 'California', 20000.00 );

在audit表上创建外键约束，其emp_no对应employees_test表的主键id

后台会判断是否创建外键约束，创建输出1，没创建输出0

drop table if exists audit;
drop table if exists employees_test;
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
);

-- 设置约束外键，指定引用字段
ALTER TABLE audit
ADD CONSTRAINT FOREIGN KEY (emp_no)
REFERENCES employees_test(id);

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

请你写出更新语句，将所有获取奖金的员工当前的(salaries.to_date='9999-01-01')薪水增加10%。

(emp_bonus里面的emp_no都是当前获奖的所有员工，不考虑获取的奖金的类型)。

drop table if exists  emp_bonus; 
drop table if exists  `salaries`;
 
create table emp_bonus(
emp_no int not null,
btype smallint not null
);

CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary`  float(11,1) default NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`)
);

insert into emp_bonus values(10001,1);
INSERT INTO salaries VALUES(10001,85097,'2001-06-22','2002-06-22');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');

-- 联表更新
UPDATE `salaries` AS S
JOIN `emp_bonus` AS B ON S.emp_no = B.emp_no
SET S.salary = S.salary * 1.1
WHERE S.to_date = '9999-01-01'

将employees表中的所有员工的last_name和first_name通过引号连接

drop table if exists  `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`));
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');

-- CONCAT 函数实现
SELECT CONCAT(`last_name`, '\'', `first_name`) AS `name` 
FROM employees

查找字符串中逗号出现的次数

drop table if exists strings;
CREATE TABLE strings(
   id int(5)  NOT NULL PRIMARY KEY,
   string  varchar(45) NOT NULL
 );
insert into strings values
(1, '10,A,B'),
(2, 'A,B,C,D'),
(3, 'A,11,B,C,D,E');

-- 1、将引号替换成无字符
-- REPLACE(string, ',', '')

-- 2、源字符长度获取
-- LENGTH(string)

-- 3、无引号字符长度
-- LENGTH( REPLACE(string, ',', ''))

-- 4、引号的个数 = 源字符长度 - 无引号字符长度
-- LENGTH(string) - LENGTH( REPLACE(string, ',', ''))

-- 5、最终SQL 
-- string长度减去 将逗号替换为空字符串的长度 即是 逗号数量 
SELECT
	id,
	LENGTH(string) - LENGTH( REPLACE(string, ',', '')) AS cnt 
FROM strings

获取employees中的first_name

将employees中的first_name，并按照first_name最后两个字母升序进行输出。

drop table if exists  `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`));
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 `first_name` 
FROM `employees` 
ORDER BY RIGHT(`first_name`, 2) ASC

-- 或者直接截取出来排序
SELECT `first_name` 
FROM `employees` 
ORDER BY SUBSTR(`first_name`, -2) ASC

按照dept_no进行汇总

dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果employees

drop table if exists  `dept_emp` ; 
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`));
INSERT INTO dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01');
INSERT INTO dept_emp VALUES(10003,'d004','1995-12-03','9999-01-01');
INSERT INTO dept_emp VALUES(10004,'d004','1986-12-01','9999-01-01');
INSERT INTO dept_emp VALUES(10005,'d003','1989-09-12','9999-01-01');
INSERT INTO dept_emp VALUES(10006,'d002','1990-08-05','9999-01-01');
INSERT INTO dept_emp VALUES(10007,'d005','1989-02-10','9999-01-01');
INSERT INTO dept_emp VALUES(10008,'d005','1998-03-11','2000-07-31');
INSERT INTO dept_emp VALUES(10009,'d006','1985-02-18','9999-01-01');
INSERT INTO dept_emp VALUES(10010,'d005','1996-11-24','2000-06-26');
INSERT INTO dept_emp VALUES(10010,'d006','2000-06-26','9999-01-01');


-- 分组后再用 GROUP_CONCAT 分组合并处理
SELECT `dept_no`, GROUP_CONCAT(`emp_no`) AS `employees`
FROM `dept_emp`
GROUP BY `dept_no`

平均工资

查找排除在职(to_date = '9999-01-01' )员工的最大、最小salary之后，其他的在职员工的平均工资avg_salary。

drop table if exists  `salaries` ; 
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` float(11,3) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
INSERT INTO salaries VALUES(10001,85097,'2001-06-22','2002-06-22');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,43699,'2000-12-01','2001-12-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
INSERT INTO salaries VALUES(10004,70698,'2000-11-27','2001-11-27');
INSERT INTO salaries VALUES(10004,74057,'2001-11-27','9999-01-01');

-- SQL编写思路
-- 1、查询最大的和最小的记录
-- 2、INNER JOIN 内连接 联表取反
-- 3、补充WHERE条件
-- 4、对查询字段AVG
-- 5、小数位过多，ROUND限制
SELECT
  ROUND( AVG(S.`salary`), 3) AS avg_salary
FROM
  salaries AS S
  JOIN (SELECT * FROM salaries WHERE to_date = '9999-01-01' ORDER BY salary ASC LIMIT 1) AS MIN 
    ON S.emp_no != MIN.emp_no
  JOIN (SELECT * FROM salaries WHERE to_date = '9999-01-01' ORDER BY salary DESC LIMIT 1) AS MAX 
    ON S.emp_no != MAX.emp_no
WHERE S.to_date = '9999-01-01'

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

drop table if exists  `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`));
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');


-- LIMIT X OFFSET Y  
-- X size 展示多少
-- Y offset 从哪条记录开始

-- 如果不写 OFFSET , 就反过来， X是偏移，Y是展示数
SELECT *
FROM `employees`
LIMIT 5 OFFSET 5

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

drop table if exists employees;
drop table if exists dept_emp;
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`));
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');
INSERT INTO dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01');
INSERT INTO dept_emp VALUES(10003,'d004','1995-12-03','9999-01-01');
INSERT INTO dept_emp VALUES(10004,'d004','1986-12-01','9999-01-01');
INSERT INTO dept_emp VALUES(10005,'d003','1989-09-12','9999-01-01');
INSERT INTO dept_emp VALUES(10006,'d002','1990-08-05','9999-01-01');
INSERT INTO dept_emp VALUES(10007,'d005','1989-02-10','9999-01-01');
INSERT INTO dept_emp VALUES(10008,'d005','1998-03-11','2000-07-31');
INSERT INTO dept_emp VALUES(10009,'d006','1985-02-18','9999-01-01');
INSERT INTO dept_emp VALUES(10010,'d005','1996-11-24','2000-06-26');
INSERT INTO dept_emp VALUES(10010,'d006','2000-06-26','9999-01-01');


-- 查询存在 D表中关联E表的记录
-- 如果存在 关联不到的记录，触发NOT EXISTS 条件 
SELECT * 
FROM employees e 
WHERE NOT EXISTS ( 
	SELECT emp_no 
	FROM dept_emp d 
	WHERE d.emp_no = e.emp_no 
);

刷题通过的题目排名

输出通过的题目的排名，通过题目个数相同的，排名相同，此时按照id升序排列

drop table if exists passing_number;
CREATE TABLE `passing_number` (
`id` int(4) NOT NULL,
`number` int(4) NOT NULL,
PRIMARY KEY (`id`));

INSERT INTO passing_number VALUES
(1,4),
(2,3),
(3,3),
(4,2),
(6,4),
(5,5);

-- 8版本开窗函数解决
SELECT
	id,
	number,
	dense_rank() over ( ORDER BY number DESC ) AS t_rank 
FROM
	passing_number;
	
-- 5版本解决办法，找大于等于自己的SQL
SELECT
	p1.id,
	p1.number,
	( SELECT count( DISTINCT p2.number ) 
	FROM passing_number AS p2 
	WHERE p2.number >= p1.number ) AS t_rank 
FROM
	passing_number AS p1 
ORDER BY
	number DESC,
	id ASC

考试分数(二)

查询用户分数大于其所在工作(job)分数的平均分的所有grade的属性，并且以id的升序排序

drop table if exists grade;
CREATE TABLE  grade(
`id` int(4) NOT NULL,
`job` varchar(32) NOT NULL,
`score` int(10) NOT NULL,
PRIMARY KEY (`id`));

INSERT INTO grade VALUES
(1,'C++',11001),
(2,'C++',10000),
(3,'C++',9000),
(4,'Java',12000),
(5,'Java',13000),
(6,'JS',12000),
(7,'JS',11000),
(8,'JS',9999),
(9,'Java',12500);

-- 先做平均分表，然后联表计算
SELECT MAIN.*
FROM `grade` AS MAIN
JOIN (
  SELECT `job`, AVG(`score`) AS `AVG_SCORE`
  FROM `grade`
  GROUP BY `job`
) AS AVG_TAB ON MAIN.`job` = AVG_TAB.`job`
WHERE MAIN.`score` > AVG_TAB.`AVG_SCORE`
ORDER BY MAIN.`id` ASC

课程订单分析(二)

查询在2025-10-15以后，

同一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程的user_id，并且按照user_id升序排序

drop table if exists order_info;
CREATE TABLE order_info (
id int(4) NOT NULL,
user_id int(11) NOT NULL,
product_name varchar(256) NOT NULL,
status varchar(32) NOT NULL,
client_id int(4) NOT NULL,
date date NOT NULL,
PRIMARY KEY (id));

INSERT INTO order_info VALUES
(1,557336,'C++','no_completed',1,'2025-10-10'),
(2,230173543,'Python','completed',2,'2025-10-12'),
(3,57,'JS','completed',3,'2025-10-23'),
(4,57,'C++','completed',3,'2025-10-23'),
(5,557336,'Java','completed',1,'2025-10-23'),
(6,57,'Java','completed',1,'2025-10-24'),
(7,557336,'C++','completed',1,'2025-10-25');

-- 主要是日期的查询
SELECT `user_id`
FROM `order_info`
WHERE 1 = 1
　 AND `date` >= '2025-10-15'
  AND `status` = 'completed'
  AND `product_name` IN ('C++', 'Java', 'Python')
GROUP BY `user_id`
HAVING COUNT(`user_id`) > 1

课程订单分析(三)

查询在2025-10-15以后，

同一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程的订单信息，并且按照order_info的id升序排序

drop table if exists order_info;
CREATE TABLE order_info (
id int(4) NOT NULL,
user_id int(11) NOT NULL,
product_name varchar(256) NOT NULL,
status varchar(32) NOT NULL,
client_id int(4) NOT NULL,
date date NOT NULL,
PRIMARY KEY (id));

INSERT INTO order_info VALUES
(1,557336,'C++','no_completed',1,'2025-10-10'),
(2,230173543,'Python','completed',2,'2025-10-12'),
(3,57,'JS','completed',3,'2025-10-23'),
(4,57,'C++','completed',3,'2025-10-23'),
(5,557336,'Java','completed',1,'2025-10-23'),
(6,57,'Java','completed',1,'2025-10-24'),
(7,557336,'C++','completed',1,'2025-10-25');


-- 先筛选买了两套以上的，再看其他条件
SELECT *
FROM `order_info` WHERE 
	`user_id` IN (
		SELECT `user_id`
		FROM `order_info`
		GROUP BY `user_id`
		HAVING COUNT(`user_id`) > 1
	)
	AND `date` >= '2025-10-15'
	AND `status` = 'completed'
	AND `product_name` IN ('C++', 'Java', 'Python')
ORDER BY `id` ASC

课程订单分析(六)

查询在2025-10-15以后，

同一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程的订单id，

是否拼团以及客户端名字信息，最后一列如果是非拼团订单，则显示对应客户端名字，如果是拼团订单，则显示NULL，并且按照order_info的id升序排序

drop table if exists order_info;
drop table if exists client;
CREATE TABLE order_info (
id int(4) NOT NULL,
user_id int(11) NOT NULL,
product_name varchar(256) NOT NULL,
status varchar(32) NOT NULL,
client_id int(4) NOT NULL,
date date NOT NULL,
is_group_buy varchar(32) NOT NULL,
PRIMARY KEY (id));

CREATE TABLE client(
id int(4) NOT NULL,
name varchar(32) NOT NULL,
PRIMARY KEY (id)
);

INSERT INTO order_info VALUES
(1,557336,'C++','no_completed',1,'2025-10-10','No'),
(2,230173543,'Python','completed',2,'2025-10-12','No'),
(3,57,'JS','completed',0,'2025-10-23','Yes'),
(4,57,'C++','completed',3,'2025-10-23','No'),
(5,557336,'Java','completed',0,'2025-10-23','Yes'),
(6,57,'Java','completed',1,'2025-10-24','No'),
(7,557336,'C++','completed',0,'2025-10-25','Yes');

INSERT INTO client VALUES
(1,'PC'),
(2,'Android'),
(3,'IOS'),
(4,'H5')


-- 1、用上一题的作为主表左连接客户端表，存在没有客户端的情况
-- 2、然后对NULL的客户端名称进行 空处理
SELECT
	MAIN.id,
	MAIN.is_group_buy,
	IFNULL(client.`name`, 'None') AS `client_name`
FROM 
 (SELECT *
FROM `order_info` WHERE 
	`user_id` IN (
		SELECT `user_id`
		FROM `order_info`
		GROUP BY `user_id`
		HAVING COUNT(`user_id`) > 1
	)
	AND `date` >= '2025-10-15'
	AND `status` = 'completed'
	AND `product_name` IN ('C++', 'Java', 'Python')
ORDER BY `id` ASC) AS MAIN
LEFT JOIN client ON client.id =  MAIN.client_id

实习广场投递简历分析(二)

查询在2025年内投递简历的每个岗位，

每一个月内收到简历的数量，并且按先按月份降序排序，再按简历数目降序排序

drop table if exists resume_info;
CREATE TABLE resume_info (
id int(4) NOT NULL,
job varchar(64) NOT NULL,
date date NOT NULL,
num int(11) NOT NULL,
PRIMARY KEY (id));

INSERT INTO resume_info VALUES
(1,'C++','2025-01-02',53),
(2,'Python','2025-01-02',23),
(3,'Java','2025-01-02',12),
(4,'C++','2025-01-03',54),
(5,'Python','2025-01-03',43),
(6,'Java','2025-01-03',41),
(7,'Java','2025-02-03',24),
(8,'C++','2025-02-03',23),
(9,'Python','2025-02-03',34),
(10,'Java','2025-02-04',42),
(11,'C++','2025-02-04',45),
(12,'Python','2025-02-04',59),
(13,'Python','2025-03-04',54),
(14,'C++','2025-03-04',65),
(15,'Java','2025-03-04',92),
(16,'Python','2025-03-05',34),
(17,'C++','2025-03-05',34),
(18,'Java','2025-03-05',34),
(19,'Python','2026-01-04',230),
(20,'C++','2026-02-06',231);

-- 主要是对记录进行两个字段的分组，和月份的处理
SELECT
	job,
	date_format( date, '%Y-%m' ) AS mon,
	sum( num ) AS cnt 
FROM
	resume_info 
WHERE
	YEAR(date) = 2025 
GROUP BY job, mon 
ORDER BY mon DESC, cnt DESC;

最差是第几名(一)

如果一个学生知道了自己综合成绩以后，最差是排第几名? 结果按照grade升序排序

drop table if exists class_grade;
CREATE TABLE class_grade (
grade varchar(32) NOT NULL,
number int(4) NOT NULL
);

INSERT INTO class_grade VALUES
('A',2),
('D',1),
('C',2),
('B',2);

-- 查询
SELECT
	c1.grade,
	SUM( c2.number ) AS t_rank 
FROM
	class_grade c1
	CROSS JOIN class_grade c2 ON c1.grade >= c2.grade 
GROUP BY
	c1.grade 
ORDER BY
	c1.grade ASC;

获得积分最多的人(一)

查找积分增加最高的用户的名字，以及他的总积分是多少(此题数据保证积分最高的用户有且只有1个)

drop table if exists user;
drop table if exists grade_info;

CREATE TABLE user (
id  int(4) NOT NULL,
name varchar(32) NOT NULL
);

CREATE TABLE grade_info (
user_id  int(4) NOT NULL,
grade_num int(4) NOT NULL,
type varchar(32) NOT NULL
);

INSERT INTO user VALUES
(1,'tm'),
(2,'wwy'),
(3,'zk'),
(4,'qq'),
(5,'lm');

INSERT INTO grade_info VALUES
(1,3,'add'),
(2,3,'add'),
(1,1,'add'),
(3,3,'add'),
(4,3,'add'),
(5,3,'add');

-- 查询最大的那一个，然后联表获取
  SELECT `USER`.`NAME`,
	t.grade_sum 
FROM
	( 
		SELECT user_id, sum( grade_num ) AS grade_sum 
		FROM grade_info 
		GROUP BY user_id 
		ORDER BY grade_sum DESC 
		LIMIT 1 
	) t
	JOIN `USER` ON t.user_id = USER.id

商品交易(网易校招笔试真题)

查找购买个数超过20,质量小于50的商品，按照商品id升序排序

CREATE TABLE `goods` (
  `id` int(11) NOT NULL,
  `name` varchar(10)  DEFAULT NULL,
  `weight` int(11) NOT NULL,
  PRIMARY KEY (`id`)
);
CREATE TABLE `trans` (
  `id` int(11) NOT NULL,
  `goods_id` int(11) NOT NULL,
  `count` int(11) NOT NULL,
  PRIMARY KEY (`id`)
);
insert into goods values(1,'A1',100);
insert into goods values(2,'A2',20);
insert into goods values(3,'B3',29);
insert into goods values(4,'T1',60);
insert into goods values(5,'G2',33);
insert into goods values(6,'C0',55);
insert into trans values(1,3,10);
insert into trans values(2,1,44);
insert into trans values(3,6,9);
insert into trans values(4,1,2);
insert into trans values(5,2,65);
insert into trans values(6,5,23);
insert into trans values(7,3,20);
insert into trans values(8,2,16);
insert into trans values(9,4,5);
insert into trans values(10,1,3);

-- 联表之后再进行分组查询
SELECT
	g.id,
	g.NAME,
	g.weight,
	sum( t.count ) ss 
FROM
	trans t
	LEFT JOIN goods g ON t.goods_id = g.id 
GROUP BY
	t.goods_id 
HAVING
	( ss > 20 AND g.weight < 50 ) 
ORDER BY
	g.id ASC

posted @ 2022-05-29 11:09 emdzz 阅读(48) 评论(0) 编辑收藏举报

刷新页面返回顶部

Silent1376

Emancipate the mind,Seek truth from the facts.

【SQL】牛客网SQL训练Part2 中等难度

查找当前薪水详情以及部门编号dept_no

查找所有员工的last_name和first_name以及对应部门编号dept_no

获取所有员工当前的manager

统计出当前各个title类型对应的员工当前薪水对应的平均工资

查找所有员工的last_name和first_name以及对应的dept_name

统计各个部门的工资记录数

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

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

创建一个actor表

批量插入数据，不使用replace操作

对first_name创建唯一索引uniq_idx_firstname

针对actor表创建视图actor_name_view

针对salaries表emp_no字段创建索引idx_emp_no

在last_update后面新增加一列名字为create_date

构造一个触发器audit_log

在audit表上创建外键约束，其emp_no对应employees_test表的主键id

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

将employees表中的所有员工的last_name和first_name通过引号连接

查找字符串中逗号出现的次数

获取employees中的first_name

按照dept_no进行汇总

平均工资

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

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

刷题通过的题目排名

考试分数(二)

课程订单分析(二)

课程订单分析(三)

课程订单分析(六)

实习广场投递简历分析(二)

最差是第几名(一)

获得积分最多的人(一)

商品交易(网易校招笔试真题)

公告

Silent1376

Emancipate the mind,Seek truth from the facts.

【SQL】 牛客网SQL训练Part2 中等难度

查找当前薪水详情以及部门编号dept_no

查找所有员工的last_name和first_name以及对应部门编号dept_no

获取所有员工当前的manager

统计出当前各个title类型对应的员工当前薪水对应的平均工资

查找所有员工的last_name和first_name以及对应的dept_name

统计各个部门的工资记录数

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

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

创建一个actor表

批量插入数据，不使用replace操作

对first_name创建唯一索引uniq_idx_firstname

针对actor表创建视图actor_name_view

针对salaries表emp_no字段创建索引idx_emp_no

在last_update后面新增加一列名字为create_date

构造一个触发器audit_log

在audit表上创建外键约束，其emp_no对应employees_test表的主键id

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

将employees表中的所有员工的last_name和first_name通过引号连接

查找字符串中逗号出现的次数

获取employees中的first_name

按照dept_no进行汇总

平均工资

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

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

刷题通过的题目排名

考试分数(二)

课程订单分析(二)

课程订单分析(三)

课程订单分析(六)

实习广场投递简历分析(二)

最差是第几名(一)

获得积分最多的人(一)

商品交易(网易校招笔试真题)

公告

【SQL】牛客网SQL训练Part2 中等难度