Niuke.com SQL Practice Questions

01. Find all the information of the latest employee

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

problem

  1. Please find all the information of the latest employee in employees.
    Solution-01
SELECT emp_no , birth_date , first_name , last_name , gender , hire_date 
FROM employees 
ORDER BY hire_date DESC LIMIT 1

Solution-02

SELECT emp_no , birth_date , first_name , last_name , gender , hire_date 
FROM employees 
WHERE hire_date = (SELECT max(hire_date) FROM employees) LIMIT 1
  1. Please find all the information of the third-to-last employee in employees in employees.
    Solution-01
    The problem with this solution is if you encounter the problem of duplication of on-boarding time:
SELECT * FROM employees ORDER  BY hire_date DESC LIMIT 2 , 1

Solution-02
remove duplicate entry time

SELECT * FROM employees 
WHERE 
hire_date = 
(SELECT distinct hire_date FROM employees ORDER BY hire_date DESC LIMIT 2 , 1)

02 Find current salary details and department number dept_no

Create table

	DROP TABLE IF EXISTS `salaries`;
	CREATE TABLE `salaries` (
		`emp_no` int(11) NOT NULL COMMENT '员工编号',
		`salary` int(11) NOT NULL COMMENT '薪资' ,
		`from_date` date NOT NULL COMMENT '开始时间',
		`to_date` date NOT NULL COMMENT '到期时间',
	PRIMARY KEY (`emp_no`,`from_date`))
	COMMENT '工资表';
	
	INSERT INTO salaries (emp_no , salary ,from_date , to_date) 
	VALUES 
	(10001 , 88958 , '2002-06-22' , '9999-01-01'),
	(10002 , 72527 , '2001-08-02' , '9999-01-01'),
	(10003 , 43311 , '2001-12-01' , '9999-01-01')
	
	
	DROP TABLE IF EXISTS `dept_manager`;
	CREATE TABLE `dept_manager` (
		`dept_no` char(4) NOT NULL COMMENT '部门编号',
		`emp_no` int(11) NOT NULL COMMENT '员工编号',
		`to_date` date NOT NULL ,
	PRIMARY KEY (`emp_no`,`dept_no`))
	COMMENT '部门经理表';
	
	INSERT INTO dept_manager (dept_no , emp_no , to_date) 
	VALUES
	('d001' , 10001 , '9999-01-01'),
	('d002' , 10003 , '9999-01-01')

problem

  1. Please find the salary details of the current leaders of each department and the corresponding department number dept_no. The output results are sorted in ascending order of salaries.emp_no, and please note that the dept_no column in the output result is the last column

Scheme-01

SELECT s.emp_no , s.salary , s.from_date , s.to_date,  d.dept_no FROM salaries as s 
INNER JOIN dept_manager as d ON s.emp_no = d.emp_no 
ORDER  BY s.emp_no ASC

Scheme-02

SELECT s.emp_no , s.salary , s.from_date , s.to_date,  d.dept_no FROM salaries as s 
INNER JOIN dept_manager as d ON s.emp_no = d.emp_no 
where s.to_date='9999-01-01' and d.to_date='9999-01-01'
ORDER  BY s.emp_no ASC

04 Find the last_name and first_name and dept_no of all employees in the assigned department

Create database table

	DROP TABLE IF EXISTS `dept_emp`;
	CREATE TABLE `dept_emp` (
	`emp_no` int(11) NOT NULL COMMENT '员工编号',
	`dept_no` char(4) NOT NULL COMMENT '部门编号',
	`from_date` date NOT NULL COMMENT '合同起始时间',
	`to_date` date NOT NULL COMMENT '合同终止时间',
	PRIMARY KEY (`emp_no`,`dept_no`))
	COMMENT '部门表';
	
	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','9999-01-01');
	INSERT INTO dept_emp VALUES(10009,'d006','1985-02-18','9999-01-01');
	INSERT INTO dept_emp VALUES(10010,'d006','2000-06-26','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');
	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');

problem

  1. Please find the last_name, first_name and dept_no of all the employees of the assigned department, the employees of the unassigned department will not be displayed
SELECT DISTINCT e.last_name , e.first_name , d.dept_no FROM employees AS e 
INNER JOIN dept_emp AS d ON e.emp_no = d.emp_no 

05 Find the last_name and first_name of all employees and the corresponding department number dept_no

Create database table

Same as question 04 database table

problem

  1. Please find the last_name, first_name and dept_no of all employees who have been assigned to the department, including employees who have not been assigned to a specific department.

solution

SELECT DISTINCT e.last_name , e.first_name , d.dept_no FROM employees AS e 
LEFT JOIN dept_emp AS d ON e.emp_no = d.emp_no 

07 Find the employee number emp_no with salary records more than 15 times and the corresponding record times t

Create a table as above

	DROP TABLE IF EXISTS `salaries`;
	CREATE TABLE `salaries` (
		`emp_no` int(11) NOT NULL COMMENT '员工编号',
		`salary` int(11) NOT NULL COMMENT '薪资' ,
		`from_date` date NOT NULL COMMENT '开始时间',
		`to_date` date NOT NULL COMMENT '到期时间',
	PRIMARY KEY (`emp_no`,`from_date`))
	COMMENT '工资表';
	
	INSERT INTO salaries VALUES(10001,60117,'1986-06-26','1987-06-26');
	INSERT INTO salaries VALUES(10001,62102,'1987-06-26','1988-06-25');
	INSERT INTO salaries VALUES(10001,66074,'1988-06-25','1989-06-25');
	INSERT INTO salaries VALUES(10001,66596,'1989-06-25','1990-06-25');
	INSERT INTO salaries VALUES(10001,66961,'1990-06-25','1991-06-25');
	INSERT INTO salaries VALUES(10001,71046,'1991-06-25','1992-06-24');
	INSERT INTO salaries VALUES(10001,74333,'1992-06-24','1993-06-24');
	INSERT INTO salaries VALUES(10001,75286,'1993-06-24','1994-06-24');
	INSERT INTO salaries VALUES(10001,75994,'1994-06-24','1995-06-24');
	INSERT INTO salaries VALUES(10001,76884,'1995-06-24','1996-06-23');
	INSERT INTO salaries VALUES(10001,80013,'1996-06-23','1997-06-23');
	INSERT INTO salaries VALUES(10001,81025,'1997-06-23','1998-06-23');
	INSERT INTO salaries VALUES(10001,81097,'1998-06-23','1999-06-23');
	INSERT INTO salaries VALUES(10001,84917,'1999-06-23','2000-06-22');
	INSERT INTO salaries VALUES(10001,85112,'2000-06-22','2001-06-22');
	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','1997-08-03');

problem

  1. Please find the employee number emp_no with salary records more than 15 times and the corresponding record times t

Solution-01

Wrong point: When group by, use HAVING WHERE as filter condition

SELECT emp_no, COUNT(DISTINCT from_date) AS t FROM salaries
GROUP BY emp_no
HAVING t > 15

08 Find out the current salary situation of all employees

Create database table

	drop table if exists  `salaries` ; 
	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 salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
	INSERT INTO salaries VALUES(10002,88958,'2002-06-22','9999-01-01');
	INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
	INSERT INTO salaries VALUES(10004,43311,'2001-12-01','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');
	INSERT INTO salaries VALUES(10008,88070,'2002-02-07','9999-01-01');
	INSERT INTO salaries VALUES(10009,95409,'2002-02-14','9999-01-01');
	INSERT INTO salaries VALUES(10011,25828,'1990-01-22','9999-01-01');

problem

  1. Please find out the specific salary situation of all employees, and display the same salary only once, and display it in reverse order

Solution-01

	# 这里是创建索引试试,索引是个很好玩的东西
	# DROP INDEX salary ON salaries ;
	# CREATE INDEX salary ON salaries (salary) ;
	SELECT DISTINCT salary FROM salaries ORDER BY salary DESC ;
	# DROP INDEX salary ON salaries;

09 Get all non-manager employees emp_no

Create table

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

problem

  1. Please find out all employees who are not department leaders emp_no

Solution-01

SELECT  e.emp_no FROM employees AS e 
LEFT JOIN dept_manager as d ON e.emp_no = d.emp_no 
WHERE d.emp_no is NULL

10 Get the current manager of all employees

Create table

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

problem

  1. Get all employees and their corresponding managers, if the employee is a manager, it will not be displayed

Solution-O1

There are two grammatical problems:
1. When sql_mode=only_full_group_by, all fields after select must be added after GROUP BY.
2. During DISTINCT, if multiple fields are queried, the field after DISTINCT should be placed in the first one after select Field

SELECT  e.emp_no AS emp_no , m.emp_no AS manager  FROM dept_emp AS e 
INNER JOIN dept_manager AS m ON e.dept_no = m.dept_no 
WHERE e.emp_no != m.emp_no
GROUP BY e.emp_no , m.emp_no

to sum up

There are a total of 80 to sql questions, which will be completed within a certain period of time.
Now the simplicity of the ORM framework gradually weakens the sql ability. I personally think that the sql ability is still very important
and the sql ability also accounts for a large part of the interview. So find some contact questions on Niuke.com.

Small experience:
Regarding the test case problem, in the online editor of Niuke.com, you can set it to contact mode, and then enter a piece of SQL that can be compiled but failed to run, and then you can copy the database creation statement and the test data SQL statement. Lest you knock them one by one. The drawback is to reduce your submission pass rate, but this is nothing, what you really learn is true, and you don’t care what the pass rate is.

Note:
Regarding the problem of inconsecutive question numbers, some questions can be combined into one question, and I merged them, but the question numbers are based on Niuke.com's question numbers.

The answer is not the best solution, but it must be a solution that passes the test cases. This article is a long-term completion. After a round, I will
read an article before sql optimization . If you go deep into a certain point, you must first understand the whole thing. To build a car, you must first build the car, and then talk about it when you can run it. I'm thinking about optimizing the engine to increase power when I run