The title is as follows:

Single table query:

1. Display the basic information of all employees.

2. Query the department numbers of the departments to which all employees belong, and do not display duplicate department numbers.

3. Find the number of all employees.

4. List the highest and lowest wages.

5. List the average salary and total salary of employees.

6. Create a new table with only the employee number, name and work participation, called the work date table.

7. Display the ages of all female employees.

8. List the employee number, name and date of birth of all employees surnamed Liu.

9. List the names and working dates of employees born before 1960.

10. List the names of all employees whose salary is between 1000-2000.

11. List the names of all employees surnamed Chen and Li.

12. List all employee numbers, names, and party members whose department numbers are 2 and 3.

13. Sort the employees in the employee table worker according to the order of birth.

14. Display the employee numbers and names of the top 3 employees with the highest salary.

15. Find the number of party members in each department.

16. Statistics of wages and average wages of various departments

Multi-table query:

1. Query all records of the student table

2. Query the 2nd to 4th records of the student table

3. Query the student number (id), name (name) and department (department) information of all students from the student table

4. Query the information of the students of the computer department and the English department from the student table

5. Query the information of students aged 18~22 from the student table

6. Query how many people are in each department from the student table

7. Query the highest score of each subject from the score table

8. Query Li Si's test subjects (c_name) and test results (grade)

9. Query all student information and test information by connecting

10. Calculate the total grade of each student

11. Calculate the average grade for each exam subject

12. Query the information of students whose computer scores are lower than 95

13. Query the information of students who take both computer and English exams

14. Sort computer test scores from high to low

15. Query the student ID number from the student table and score table, and then merge the query results

16. Query the name, department, examination subjects and grades of students surnamed Zhang or Wang

17. Query the names, ages, departments, examination subjects and grades of students from Hunan

Single table query:

# Preparation :

CREATE TABLE `worker` (
 `department number` int(11) NOT NULL,
 `employee number` int(11) NOT NULL,
 `operating hours` date NOT NULL,
 `salary` float(8,2) NOT NULL,
 `political status` varchar(10) NOT NULL DEFAULT 'the masses',
 `Name` varchar(20) NOT NULL,
 `date of birth` date NOT NULL,
 PRIMARY KEY (`employee number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
INSERT INTO `worker` (`department number`, `employee number`, `operating hours`, `salary`, `political status`, `Name`, `date of birth`) VALUES (101, 1001, '2015-5-4', 3500.00, 'the masses', 'Zhang San', '1990-7-1');
INSERT INTO `worker` (`department number`, `employee number`, `operating hours`, `salary`, `political status`, `Name`, `date of birth`) VALUES (101, 1002, '2017-2-6', 3200.00, 'member', 'Li Si', '1997-2-8');
INSERT INTO `worker` (`department number`, `employee number`, `operating hours`, `salary`, `political status`, `Name`, `date of birth`) VALUES (102, 1003, '2011-1-4', 8500.00, 'party member', 'Wang Liang', '1983-6-8');
INSERT INTO `worker` (`department number`, `employee number`, `operating hours`, `salary`, `political status`, `Name`, `date of birth`) VALUES (102, 1004, '2016-10-10', 5500.00, 'the masses', 'Zhao Liu', '1994-9-5');
INSERT INTO `worker` (`department number`, `employee number`, `operating hours`, `salary`, `political status`, `Name`, `date of birth`) VALUES (102, 1005, '2014-4-1', 4800.00, 'party member', 'Qian Qi', '1992-12-30');
INSERT INTO `worker` (`department number`, `employee number`, `operating hours`, `salary`, `political status`, `Name`, `date of birth`) VALUES (102, 1006, '2017-5-5', 4500.00, 'party member', 'Sun Ba', '1996-9-2');

Question 1:

mysql8.0.32 [chap01]>select * from worker;

Question 2:

mysql8.0.32 [chap01]>select distinct  department number from worker;

Question 3:

mysql8.0.32 [chap01]>select count(1) from worker;

Question 4:

mysql8.0.32 [chap01]>select max(salary) as maximum salary,min(salary) as minimum wage from worker;

Question 5:

mysql8.0.32 [chap01]>select avg(salary),sum(salary) from worker;

Question 6:

mysql8.0.32 [chap01]>create table work date table (

         employee number int(11) not null,  

        Name varchar(20) not null,

        operating hours date not null
        );

Question 7:

mysql8.0.32 [chap01]>select year(now())-year(date of birth) as age from worker;,

Question 8:

mysql8.0.32 [chap01]>select employee number,Name,date of birth from worker where Name like 'liu%';

Question 9:

mysql8.0.32 [chap01]>select Name,operating hours from  worker where year(date of birth)<1999;

Question 10:

mysql8.0.32 [chap01]>select Name from worker where salary between 1000 and 4000;

Question 11:

mysql8.0.32 [chap01]>select Name from worker where Name like 'plum%' or 'List%';
or

mysql8.0.32 [chap01]>select Name from worker where Name regexp '(^List|^plum)';

Question 12:

mysql8.0.32 [chap01]>select department number,employee number,Name,if(political status="party member",'yes','no') as party member from worker;

Question 13:

mysql8.0.32 [chap01]>select * from worker order by date of birth ;

Question 14:

mysql8.0.32 [chap01]>select employee number,Name from worker order by salary desc limit 3;

Question 15:

mysql8.0.32 [chap01]>select department number,count(1) number of party members from worker where political status='party member' group by department number ;

Question 16:

mysql8.0.32 [chap01]>select department number,sum(salary) salary,avg(salary) average salary from worker group by department number;

Question 17:

mysql8.0.32 [chap01]>select department number,count(1) total people from worker group by department number having total people>=4;

Multi-table query:

# Preparation :

1.create student and score surface
CREATE TABLE student (
id INT(10) PRIMARY KEY,
name VARCHAR(20) NOT NULL ,
sex VARCHAR(4) ,
birth YEAR,
department VARCHAR(20) ,
address VARCHAR(50)
);
create score surface. SQL code show as below:
CREATE TABLE score (
id INT(10) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,
stu_id INT(10) NOT NULL,
c_name VARCHAR(20) ,
grade INT(10)
);
2.for student table and score table add record
 Towards student table insert record INSERT The statement is as follows:
INSERT INTO student VALUES( 901,'Boss Zhang', 'male',1985,'computer science', 'Haiding district, Beijing');
INSERT INTO student VALUES( 902,'Zhang Laoer', 'male',1986,'Department of Chinese', 'Changping district, Beijing');
INSERT INTO student VALUES( 903,'Zhang San', 'female',1990,'Department of Chinese', 'Yongzhou City, Hunan Province');
INSERT INTO student VALUES( 904,'Li Si', 'male',1990,'English major', 'Fuxin City, Liaoning Province');
INSERT INTO student VALUES( 905,'Wang Wu', 'female',1991,'English major', 'Xiamen City, Fujian Province');
INSERT INTO student VALUES( 906,'Wang Liu', 'male',1988,'computer science', 'Hengyang City, Hunan Province');
Towards score table insert record INSERT The statement is as follows:
INSERT INTO score VALUES(NULL,901, 'computer',98);
INSERT INTO score VALUES(NULL,901, 'English', 80);
INSERT INTO score VALUES(NULL,902, 'computer',65);
INSERT INTO score VALUES(NULL,902, 'Chinese',88);
INSERT INTO score VALUES(NULL,903, 'Chinese',95);
INSERT INTO score VALUES(NULL,904, 'computer',70);
INSERT INTO score VALUES(NULL,904, 'English',92);
INSERT INTO score VALUES(NULL,905, 'English',94);
INSERT INTO score VALUES(NULL,906, 'computer',90);
INSERT INTO score VALUES(NULL,906, 'English',85)

Question 1:

select * from student;

Question 2:

mysql8.0.32 [chap01]>select * from student limit 1,3;

Question 3:

mysql8.0.32 [chap01]>select id,name,department from student ;

Question 4:

mysql8.0.32 [chap01]>select * from student where department regexp '^computer|^English';

Question 5:

mysql8.0.32 [chap01]>select * from student where year(now())-birth between 18 and 35;

Question 6:

mysql8.0.32 [chap01]>select department,count(1) number of people from student group by department;

Question 7:

mysql8.0.32 [chap01]>select c_name,max(grade) highest score from score group by c_name;

Question 8:

mysql8.0.32 [chap01]>select c_name,grade from score  inner join student on score.stu_id=student.id and name='Li Si';
or

mysql8.0.32 [chap01]>select c_name,grade from score sc ,student st where sc.stu_id=st.id and name='Li Si';

Question 9:

mysql8.0.32 [chap01]>select * from score sc join student st on sc.stu_id=st.id;

Question 10:

mysql8.0.32 [chap01]>select name,sum(grade) Overall result from score sc join student st on sc.stu_id=st.id group by name;

Question 11:

mysql8.0.32 [chap01]>select c_name,sum(grade)/count(1) average score from score group by c_name;

Question 12:

mysql8.0.32 [chap01]>select * from score sc,student st where sc.stu_id=st.id and c_name='computer' and grade<95;

Question 13:

mysql8.0.32 [chap01]>select sc.stu_id,group_concat(sc.c_name),st.name from score sc,student st where st.id=sc.stu_id group bystu_id having group_concat(c_name)='computer,English';

Question 14:

mysql8.0.32 [chap01]>select c_name,grade from score where c_name='computer' order by grade desc;

Question 15:

mysql8.0.32 [chap01]>select * from student st,score sc where st.id=sc.stu_id;

Question 16:

mysql8.0.32 [chap01]>select name,department,c_name,grade from score sc,student st where sc.stu_id=st.id and name regexp '^open|^king';

Question 17:

mysql8.0.32 [chap01]>select name,year(now())-birth age,department,c_name,grade from student st,score sc where sc.stu_id=st.id and address like 'Hunan%';