(SQL statement questions exercise and teacher problem-solving ideas + personal problem-solving ideas)

SQL field Description and data

=======================================================================
First, the Department table field Description:
Dp_no Department ID
Dp_name Department Name
Location of Dp_loc Department

Second, the Employee table field Description:
ENo Employee Number
ENAME Employee Name
Ejob Employee Responsibilities
Emgr boss Number
Ehiredate Time of entry
Esal Wages
Ecomm Tax Deduction
Dp_no Department ID

Create DATABASE and data tables
CREATE DATABASE IF not EXISTS sqltest;

CREATE TABLE IF not EXISTS dept (
Dp_no Int (ten) not null primary key,
Dp_name varchar (25),
Dp_loc varchar (25)
);

CREATE TABLE IF not EXISTS EMP (
ENo Int (+) NOT null primary key,
ename varchar (45),
Ejob varchar (45),
emgr Int (25),
Ehiredate date,
esal Int (45),
Ecomm Int (45),
Dp_no Int (25)
);

Add Dept Table data:
INSERT INTO Dept (Dp_no, Dp_name, Dp_loc) VALUES (' ACCOUNTING ', ' NEW YORK ');
INSERT INTO Dept (Dp_no, Dp_name, Dp_loc) VALUES (+, ' the ' ", ' DALLAS ');
INSERT INTO Dept (Dp_no, Dp_name, Dp_loc) VALUES (+, ' esales ', ' CHICAGO ');
INSERT INTO Dept (Dp_no, Dp_name, Dp_loc) VALUES (+, ' OPERATIONS ', ' BOSTON ');
Commit

Add EMP Table data: ENo, ename, Ejob, Emgr, Ehiredate, Esal, Ecomm, Dp_no
Insert into EMP (ENo, ename, Ejob, Emgr, Ehiredate, Esal, Ecomm, Dp_no) VALUES (7369, ' SMITH ', ' clerk ', 7902, ' 1980-12-17 ', , NULL, 20);
Insert into EMP (ENo, ename, Ejob, Emgr, Ehiredate, Esal, Ecomm, Dp_no) VALUES (7499, ' ALLEN ', ' Esalesman ', 7698, ' 1981-02-2 0 ', 1600, 300, 30);
Insert into EMP (ENo, ename, Ejob, Emgr, Ehiredate, Esal, Ecomm, Dp_no) VALUES (7521, ' WARD ', ' Esalesman ', 7698, ' 1981-02-2 2 ', 1250, 500, 30);
Insert into EMP (ENo, ename, Ejob, Emgr, Ehiredate, Esal, Ecomm, Dp_no) VALUES (7566, ' JONES ', ' MANAGER ', 7839, ' 1981-04-02 ', 2975, NULL, 20);
Insert into EMP (ENo, ename, Ejob, Emgr, Ehiredate, Esal, Ecomm, Dp_no) VALUES (7654, ' MARTIN ', ' Esalesman ', 7698, ' 1981-09 -28 ', 1250, 1400, 30);
Insert into EMP (ENo, ename, Ejob, Emgr, Ehiredate, Esal, Ecomm, Dp_no) VALUES (7698, ' BLAKE ', ' MANAGER ', 7839, ' 1981-05-01 ', 2850, NULL, 30);
Insert into EMP (ENo, ename, Ejob, Emgr, Ehiredate, Esal, Ecomm, Dp_no) VALUES (7782, ' CLARK ', ' MANAGER ', 7839, ' 1981-06-09 ', 2450, NULL, 10);
Insert into EMP (ENo, ename, Ejob, Emgr, Ehiredate, Esal, Ecomm, Dp_no) VALUES (7788, ' SCOTT ', ' ANALYST ', 7566, ' 1987-04-19 ', +, NULL, 20);
Insert into EMP (ENo, ename, Ejob, Emgr, Ehiredate, Esal, Ecomm, Dp_no) VALUES (7839, ' KING ', ' president ', NULL, ' 1981-11-1 7 ', +, NULL, 10);
Insert into EMP (ENo, ename, Ejob, Emgr, Ehiredate, Esal, Ecomm, Dp_no) VALUES (7844, ' TURNER ', ' Esalesman ', 7698, ' 1981-09 -08 ', 1500, 0, 30);
Insert into EMP (ENo, ename, Ejob, Emgr, Ehiredate, Esal, Ecomm, Dp_no) VALUES (7876, ' ADAMS ', ' clerk ', 7788, ' 1987-05-23 ') , 1100, NULL, 20);
Insert into EMP (ENo, ename, Ejob, Emgr, Ehiredate, Esal, Ecomm, Dp_no) VALUES (7900, ' JAMES ', ' clerk ', 7698, ' 1981-12-03 ') , 950, NULL, 30);
Insert into EMP (ENo, ename, Ejob, Emgr, Ehiredate, Esal, Ecomm, Dp_no) VALUES (7902, ' FORD ', ' ANALYST ', 7566, ' 1981-12-02 ') , +, NULL, 20);
Insert into EMP (ENo, ename, Ejob, Emgr, Ehiredate, Esal, Ecomm, Dp_no) VALUES (7934, ' MILLER ', ' clerk ', 7782, ' 1982-01-23 ') , 10);

Operation:

--------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------

SQL Basic Operations case
=======================================================================

Teachers ' questions and thinking of solving problems
"1". Find out who Jones's leader is (who Jones reports to).
Sub-query (Low efficiency):
Select
Ename
From
EMP where ENo in (select Emgr from EMP where ename= ' JONES ');

Join notation (Efficient):
Select
E2.ename
from (select Emgr from EMP where ename= ' JONES ') E1
Left join EMP E2
on E1. Emgr=e2.eno;
=======================================================================
"2". Jones leads who. (Who reports to Jones).
Subquery (Low efficiency):
Select
ename
from EMP
where Emgr in (select Emgr from emp where ename= ' JONES ');

Join notation (Efficient):
Select
E2.ename
From (select ENo from EMP where ename= ' JONES ') E1
Left JOIN EMP E2
On E1.eno=e2.emgr;
=======================================================================
"3". Find the maximum, minimum, average, sum of employees ' wages for each position
(Question: What does it look like in each department, above the posts?) -->ejob, the positions should be more simple, grouped by position)
The first step:
Select Ejob,max (esal) as ' the most well-paid ' from the EMP group by Ejob;
Select Ejob,min (esal) as ' minimum wage ' from the EMP group by Ejob;
Select Ejob,avg (esal) as ' average salary ' from the EMP Group by Ejob; (to keep the decimal point, round cannot write as, write outside);
Step Two:
Select
Ejob,
Max (esal) as ' minimum wage ',
Min (esal) as ' minimum wage ',
AVG (esal) as ' flat pay ',
SUM (esal) as ' Sum '
From EMP
Group BY Ejob;

====================================================================
"4". Select the number of employees with individual ejob (hint: to group the ejob, the topic is not very clear that ...) )
Select
Ejob as ' post ',
Count (ejob) as ' number of jobs '
From EMP
Group BY Ejob;

5. Check the gap between the maximum wage and the minimum wage for the employee, listed as difference;
Select Max (esal)-min (esal) as ' difference ' from EMP;
====================================================================
"6". Check the minimum wage for each manager's staff, where the minimum wage cannot be less than 800, Employees without managers
are not counted
====================================================================
"7". Querying department names for all departments DP _name, location Dp_loc, number of employees and average wage;
The first step: each department number, the number of employees and the average wage, as a table, with the idea of join!!!
Select Dp_no,count (ENO) as ' employee Number ', AVG (esal) as ' payroll average ' from EMP Group by Dp_no;
B, A, department number, number of employees, and average wage

Select Dp_name as ' department name ', Dp_loc as ' position ', nums as ' employee number ', avgesal as ' average salary ' from
(select Dp_no as No,count (ENO) as Nums, Av G (Esal) as avgesal from EMP Group by Dp_no) e
Left JOIN dept D on D.dp_no=e.no;

Select
D.dp_name as ' department name ',
D.dp_loc as ' location ',
E.nums as ' number of employees ',
E.avgesal as ' average salary '
from Dept D
Left JOIN (select Dp_no as No,count (ENO) as Nums, AVG (esal) as avgesal from EMP Group by Dp_no) e
on d.dp_no=e.no;

Efficient: What's the difference between what I write and what it says???
Select
D.dp_name,
D.dp_loc,
Count (ENO),
Round (if (AVG (esal) is Null,0,avg (Esal)), 2) as ' average salary '
From Dept D
Left join EMP e
on D.dp_no=e.dp_no
Group by E.dp_no;

# # #round (xxx,2) retains two decimal places
Round (if (AVG (esal) is Null,0,avg (Esal)), 2) as ' average salary '
# # # #判断avg (esal) is Null,0,avg ( Esal) is 0 empty, otherwise with itself avg (esal)
if (avg (esal) is Null,0,avg (esal))

======================================== ============================
"8". Query and Scott same department employee name Ename and hire date ehiredate
Common notation (Low efficiency):
Select
E.ename,
E.ehiredate
from the EMP e
where dp_no= (select Dp_no from emp where ename= ' SCOTT ');

Join notation (efficient):
Select
E2.ename,
e2.ehiredate
from (select Dp_no from EMP where ename= ' SCOTT ') E1
Lef T join EMP E2
on E1.dp_no=e2.dp_no;

====================================================================
"9". Check the employee number of all employees who have a higher salary than the company's average wage Eno, Name ename and Salary esal.
Subquery (Low efficiency):
Select ENo as ' number ', ename as ' name ', esal as ' payroll ' from EMP where
esal> (select AVG (esal) from EMP);

Join query (Efficient):
Select
E2.eno,
E2.ename,
E2.esal
From (select AVG (esal) as Avg. from EMP) E1
Left JOIN EMP E2
On e1.avg<=e2.esal;

====================================================================
"10". The employee number ENO and name of the employee in the same department who has the letter U in the query and name ename
Sub-query (low efficiency);
Select
ENo as ' number ',
ename as ' name '
From EMP
where Dp_no in (select Dp_no from EMP Where ename like '%u% ');

If in is written as =, the result of the returned rows is more than one row will report the following error, so it is best to use in!
ERROR 1242 (21000): subquery returns more than 1 row

Join Query 1 (high efficiency, including the names of the U-Letters are also found):
Select
E2.eno,
E2.ename
From (select Dp_no to EMP where ename like '%u% ') E1
Left JOIN EMP E2
On E1.dp_no=e2.dp_no;

Join Query 2 (efficient, remove the name including the U-letter):
Select
E2.eno,
E2.ename
From (select Dp_no,ename to EMP where ename like '%u% ') E1
Left JOIN EMP E2
On E1.dp_no=e2.dp_no
where e1.ename!= e2.ename;
=================================================================
"11". Query the employee name Ename of the employee who worked in the department dp_loc for NewYork,
Department name Dp_name and job title Ejob

Sub-query:
Select
ename as ' name ',
Dp_name as ' department ',
Eejob as ' Jobs '
From EMP E, dept D
where E.dp_no =d.dp_no and d.dp_dp_loc= ' NEW YORK ';

Select
ename as ' name ',
Dp_name as ' department ',
Eejob as ' Jobs '
From Dept D
Left JOIN EMP E
On E.dp_no =d.dp_no
where d.dp_dp_loc= ' NEW YORK ';

Select
E.ename as ' name ',
D.dp_name as ' department ',
E.ejob as ' Jobs '
From (select Dp_no,dp_name from dept where dept.dp_loc= ' NEW YORK ') d
Left JOIN EMP E
On E.dp_no =d.dp_no;

====================================================================
"12". The query manager is King's employee name ename and salary Esal
Select
ename as ' name ',
Esal as ' salary '
From EMP
where Emgr in (select ENo from EMP where ename= ' KING ');

Select
E2.ename,
E2.esal
From (select ENo from EMP where ename= ' KING ') E1
Left JOIN EMP E2
On E1.eno=e2.emgr;
====================================================================

"13". Show what positions are available in the accounting department
General Query:
Select
Ejob as ' Jobs '
From Dept D,emp E
where D.dp_no=e.dp_no and d.dp_name= ' ACCOUNTING ';

Join notation:
Select
E2.ejob as ' Jobs '
From (select Dp_no from dept where Dp_name= ' ACCOUNTING ') d
Left JOIN EMP E2
On D.dp_no=e2.dp_no

====================================================================

"14". Number of employees and department names in various departments with wages greater than 1500

Select
D.dp_name,
E.num as ' number of departments with salaries greater than 1500 '
From (select Dp_no, Count (eNo) as num from EMP Where esal>1500 Group by Dp_no) E
Left JOIN Dept D
On E.dp_no=d.dp_no;

====================================================================
"15". Which employees pay higher than the entire company's average wage, list the employee's name and salary (descending)
Sub-query:
Select
E.ename,
E.esal
From EMP E
where Esal > (select AVG (esal) from EMP)
ORDER BY esal Desc;

Join notation:
Select
E2.ename as ' employee name ' above the entire company's average wage,
E2.esal as ' corresponding salary '
From (select AVG (esal) as Avg. from EMP) E1
Left JOIN EMP E2
On E2.esal>e1.avg
ORDER BY e2.esal Desc;

====================================================================
"16". Employee name with average salary above 1500 in the Department
(if the department's average salary is higher than 1500, export all employee names in this department)

Select
E2.ename as ' Employee name '
From (select Dp_no,avg (esal) as Avg. EMP GROUP by Dp_no have avg>1500) E1
Left JOIN EMP E2
On E2.dp_no=e1.dp_no;

Select Dp_no,avg (esal) as AVG from EMP GROUP by Dp_no have avg>1500
Can only be used with having not to use where,having is to filter after doing the grouping
====================================================================

"17". List information about the highest-paid employees in each department: Employee name, department name, salary (a bit of a problem OH)

Sub-query:
Select
ename as ' name ',
Dp_no as ' department number ',
Esal as ' salary '
From the EMP where esal in (select Max (esal) from the EMP Group by Dp_no);

Join notation:
Select
E.ename as ' name ',
D.dp_name as ' department name ',
e.es as ' salary '
From Dept D
Left JOIN (select Ename,dp_no,max (esal) as ES from EMP Group by Dp_no) E
on d.dp_no = E.dp_no;
====================================================================

"18". Which department has the highest average wage, which lists the department number, the average wage

Select
Dp_no,
AVG (esal) as Esal
From EMP
GROUP BY Dp_no
ORDER BY esal Desc
Limit 1;

What if the average salary of more than two departments is the highest?
====================================================================
How does Max make it? Train of thought: Group find each department average salary, Department number table E1, in from Find the maximum value do table 2 is left table
and use similar table E1 as the right table
Select
E3.dp_no,
E3.avg
From
(
Select
Max (E1.AVG) as Max
From (select Dp_no,avg (esal) as Avg. from EMP GROUP by Dp_no) E1
) E2
Left JOIN (select Dp_no,avg (esal) as Avg. from EMP GROUP by Dp_no) E3
On E2.max=e3.avg;

Select

From (select
E1.avg as Avg_max
From (select Dp_no,avg (esal) as Avg. from EMP GROUP by Dp_no) E1) E2

Left Join

---------------------------------------------------------------------------------------------------

Personal Problem Solving Ideas:

--1. Find out who Jones's leader is.
SELECT emgr from EMP WHERE ename= ' JONES '--query leader number by employee name
SELECT ename from EMP WHERE eno=7839--Query employee name based on employee number
--merger
SELECT ename from EMP WHERE eno= (
SELECT emgr from EMP WHERE ename= ' JONES ')
--2.JONES who leads.
SELECT eNo from EMP WHERE ename= ' JONES '--Find employee numbers based on employee name
SELECT eNo from EMP WHERE emgr=7566--Find employee names based on employee number
SELECT ename from EMP WHERE eNo in (
SELECT eNo from EMP WHERE emgr=7566)--Find employee names by boss number

SELECT ename from EMP WHERE eNo in (
SELECT eNo from EMP WHERE emgr=
(SELECT eNo from EMP WHERE ename= ' JONES ')) --Find employees according to their boss's name

--3. Find the maximum, minimum, average, sum of employees ' wages for each position
SELECT Esal,ejob from EMP-salary for each position employee
SELECT Max (esal), ejob from EMP GROUP by Ejob-the maximum wage for each position employee
SELECT min (esal), ejob from EMP GROUP by ejob-minimum wage for each position employee
SELECT AVG (esal), ejob from EMP GROUP by Ejob--average employee wage for each position
SELECT sum (esal), ejob from EMP GROUP by Ejob--sum of employees ' salaries for each position
--4. Select the number of employees with individual ejob
--Understanding: Query the number of employees who choose each ejob
SELECT COUNT (eNo), ejob from EMP GROUP by Ejob
--5. Check the gap between the maximum wage and the minimum wage for the employee, listed as difference
SELECT MAX (esal) from EMP--query employee maximum wage
SELECT MIN (esal) from EMP--query employee minimum wage
SELECT (MAX (esal)-MIN (esal)) as difference from EMP
---6. Check the minimum wage of the employees of each manager, the minimum wage must not be less than 800, no manager's staff
--Not counted

--Start by querying the managers who have the employees, and then calculate the minimum wage, and the minimum wage can not be less than 800, with the manager number group
SELECT emgr from EMP GROUP by emgr--query individual manager numbers
--Check employee number, employee name, manager number, employee minimum wage, and group by manager number
SELECT eno,ename,emgr,min (esal) from the EMP WHERE emgr in (
SELECT emgr from EMP Group by Emgr GROUP by Emgr have MIN (esal) >=800

--7. Search all departments for Department name Dp_name, location Dp_loc, number of employees and average wage
--Query the department name of all departments, where
SELECT dp_name,dp_loc from Dept
-- Query the number of employees based on Department ID
Select COUNT (eNo) from
--INNER JOIN query, group query
SELECT d.dp_name,d.dp_loc,count (eNo), AVG (esal) from EMP E INNER JOIN Dept D
on D.dp_no =e.dp_no GROUP by D.dp_no
--8. Find employee names Ename and hire dates for the same department as Scott Ehiredate
--query Scott's Department ID
Select Dp_no,ename from emp where ename= ' Scott '
--query employee name and hire date according to Department ID
SELECT ename,ehiredate from EMP where DP _no in (
SELECT dp_no from EMP WHERE ename= ' Scott ')

---9. Check the employee number of all employees whose wages are higher than the company's average salary ENO, name ename and salary Esal
SELECT AVG (esal) from EMP--query company average salary
SELECT esal from EMP GROUP by ENo--check the employee's salary
SELECT ENo from EMP have Esal>avg (esal)--wrong
Select Eno,ename,esal from emp WHERE esal> (SELECT AVG (esal) from EMP)--correct
--10. Query and name the employee in the same department who contains the letter U employee number ENO and name ename
--Query the employee whose name contains the letter U and the Department ID
SELECT ename,eno,dp_no from EMP WHERE ename like ' u% ' or ename like '%u% ' or ename like '%u '
--Query the employee number and name in the department based on the Department ID
SELECT Ename,eno from EMP WHERE dp_no=30
--merger
SELECT Ename,eno from EMP WHERE dp_no in (
SELECT dp_no from EMP WHERE ename like ' u% ' or ename like '%u% ' or ename like '%u ')

---11. Query the employee name Ename of the employee working in the department dp_loc for NewYork,
--Department name Dp_name and job title Ejob

--Query Department ID for Department name NewYork
SELECT dp_no from Dept WHERE dp_loc= ' NEW YORK '
--The employee name of the employee who inquires the department dp_loc to work for the NewYork department ename
SELECT ename from EMP WHERE dp_no=10
--merger
SELECT ename from EMP WHERE dp_no in (
SELECT dp_no from Dept WHERE dp_loc= ' NEW YORK ')

--12. The query manager is King's employee name ename and salary Esal
--Query King's employee number
SELECT eNo from EMP WHERE ename= ' KING '--7839
--Query The boss number is 7839 employee name, salary Esal
SELECT ename,esal from EMP WHERE emgr=7839
--merger
SELECT ename,esal from EMP WHERE emgr in (
SELECT eNo from EMP WHERE ename= ' KING ')

--13. Show what positions are available in the accounting department
--Query the department ID of the accounting department
SELECT dp_no from Dept WHERE dp_name= ' ACCOUNTING '--10
--Query the position with Department ID 10
SELECT ejob from EMP WHERE dp_no=10
--merger
SELECT ejob from EMP WHERE dp_no in (
SELECT dp_no from Dept WHERE dp_name= ' ACCOUNTING ')

--14. Number of employees and department name in each department with wages greater than 1500
--Query the employee number and department number for wages greater than 1500
SELECT eno,dp_no from EMP WHERE esal>1500
--Query the number of employees with a salary greater than 1500 for each department number
SELECT COUNT (eNo), dp_no from EMP WHERE esal>1500 GROUP by Dp_no
--Query department name according to department number
SELECT dp_name from Dept WHERE dp_no=10

--Use INNER JOIN query, group query, condition query
SELECT COUNT (eNo), d.dp_name from EMP e INNER joins dept D on D.dp_no=e.dp_no
WHERE esal>1500 GROUP by D.dp_no

--15. The employee's salary, which is higher than the average wage of the entire company, lists the employee's name and salary (descending order)
--With the nineth question
Select Eno,ename,esal from emp WHERE esal> (SELECT AVG (esal) from EMP) ORDER by Esal DESC

--16. Employee name of the department with average salary above 1500, department number
SELECT eno,dp_no from EMP WHERE esal>1500
--+ Department Name
SELECT e.ename,e.esal,d.dp_name from EMP e INNER joins dept D on D.dp_no=e.dp_no
GROUP by E.eno have AVG (esal) >1500

--17. List the highest-paid employees in each department: Employee name, department name, salary
-The employee number with the highest wage in each department
SELECT MAX (esal), dp_no,ename from EMP GROUP by Dp_no
--Inner joins, group queries, aggregate functions
SELECT MAX (esal), ename,d.dp_name,d.dp_no from EMP e INNER joins dept D on E.dp_no=d.dp_no GROUP by D.dp_no

-18. Which department has the highest average wage, showing the department number, the average wage
--Query the average salary of each department
SELECT AVG (esal) from the EMP GROUP by Dp_no
--Query the highest average wage for the department number
Select MAX (b), a.dp_no from (SELECT AVG (esal) as B,dp_no from EMP GROUP by Dp_no) as a
--+ Department Name
Select MAX (b), a.dp_no,d.dp_name from (select AVG (esal) as B,dp_no from EMP GROUP by Dp_no) as a INNER joins dept D on D.dp_no =a.dp_no

(SQL statement questions exercise and teacher problem-solving ideas + personal problem-solving ideas)