Three days to learn MySQL - MySQL database chapter exercise

MySQL database chapter exercise

Lecturer: Song Hongkang

Microblog: Shang Silicon Valley - Song Hongkang

Section 1 Selection and filtering

[Title]
# 1. Query the total salary of employees for 12 months and alias it ANNUAL SALARY

# 2. Query the employees table to remove duplicate jobs_ Data after ID

# 3. Query the name and salary of employees whose salary is greater than 12000

# 4. Query the name and department number of the employee with employee number 176

# 5. Select the name and salary of employees whose salary is not between 5000 and 12000

# 6. Select the name and department number of the employee working in department 20 or 50

# 7. Select the names and jobs of employees without managers in the company_ id

# 8. Select the name, salary and bonus level of employees with bonus in the company

# 9. Select the employee whose third letter is a

# 10. Select the employee's name with letters a and e in the name

1. Query the total salary of employees for 12 months and alias it ANNUAL SALARY

SELECT employee_id , last_name,
salary * 12  "ANNUAL  SALARY"
FROM employees;

2. Query the employees table to remove duplicate jobs_ Data after ID

SELECT DISTINCT job_id
FROM employees;

3. Query the name and salary of employees whose salary is greater than 12000

SELECT last_name, salary
FROM employees
WHERE salary > 12000;

4. Query the name and department number of the employee with employee number 176

SELECT last_name, department_id
FROM employees
WHERE employee_id = 176;

5. Select the name and salary of employees whose salary is not between 5000 and 12000

SELECT last_name, salary
FROM employees
WHERE salary < 5000 OR salary > 12000;
SELECT last_name, salary
FROM employees
WHERE salary NOT BETWEEN 5000 AND 12000;

6. Select the name and department number of the employee working in department 20 or 50

SELECT last_name, department_id
FROM employees
WHERE department_id = 20 OR department_id = 50;
SELECT last_name, department_id
FROM employees
WHERE department_id IN(20, 50);

7. Select the names and jobs of employees without managers in the company_ id

SELECT last_name, job_id
FROM employees
WHERE manager_id IS NULL;

8. Select the name, salary and bonus level of employees with bonus in the company

SELECT last_name, salary, commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;

9. The third letter of the selected employee's name is the employee's name of a

SELECT last_name
FROM employees
WHERE last_name LIKE '__a%';

10. Select the employee's name with letters a and e in the name

SELECT last_name
FROM employees
WHERE last_name LIKE '%a%e%' OR last_name LIKE '%e%a%';

Section 2 multi table query-1

[Title]
# 1. Display the names, Department numbers and department names of all employees.

# 2. Query the job of the employee in department 90_ ID and location of department 90_ id

# 3. Select the last of all employees with bonus_ name , department_ name , location_ id , city

# 4. Select the last name of the city's employees working in Toronto_ name , job_ id , department_ id , department_ name 

# 5. Select the name and employee number of the designated employee, as well as the name and employee number of his manager. The result is similar to the following format
employees	Emp#	manager	Mgr#
kochhar		101	king	100

1. Display the names, Department numbers and department names of all employees.

SELECT last_name, e.department_id, department_name
FROM employees e
LEFT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`;

2. Query the job of the employee in department 90_ ID and location of department 90_ id

SELECT job_id, location_id
FROM employees e, departments d
WHERE e.`department_id` = d.`department_id`
AND e.`department_id` = 90;

or

SELECT job_id, location_id
FROM employees e
JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` = 90;

3. Select the last of all employees with bonus_ name , department_ name , location_ id , city

SELECT last_name , department_name , d.location_id , city
FROM employees e
LEFT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`
LEFT OUTER JOIN locations l
ON d.`location_id` = l.`location_id`
WHERE commission_pct IS NOT NULL;

4. Select the last name of the city's employees working in Toronto_ name , job_ id , department_ id , department_ name

SELECT last_name , job_id , e.department_id , department_name
FROM employees e, departments d, locations l
WHERE e.`department_id` = d.`department_id`
AND d.`location_id` = l.`location_id`
AND city = 'Toronto';

or

SELECT last_name , job_id , e.department_id , department_name
FROM employees e
JOIN departments d
ON e.`department_id` = d.`department_id`
JOIN locations l
ON l.`location_id` = d.`location_id`
WHERE l.`city` = 'Toronto';

5. Select the name and employee number of the designated employee, as well as the name and employee number of his manager. The result is similar to the following format

employees Emp# manager Mgr#
kochhar 101 king 100

SELECT emp.last_name employees, emp.employee_id "Emp#", mgr.last_name manager, mgr.employee_id "Mgr#"
FROM employees emp 
LEFT OUTER JOIN employees mgr
ON emp.manager_id = mgr.employee_id;

Section 2 multi table query-2

Reserve: table creation:
CREATE TABLE `t_dept` (
 `id` INT(11) NOT NULL AUTO_INCREMENT,
 `deptName` VARCHAR(30) DEFAULT NULL,
 `address` VARCHAR(40) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
 
CREATE TABLE `t_emp` (
 `id` INT(11) NOT NULL AUTO_INCREMENT,
 `name` VARCHAR(20) DEFAULT NULL,
  `age` INT(3) DEFAULT NULL,
 `deptId` INT(11) DEFAULT NULL,
empno int  not null,
 PRIMARY KEY (`id`),
 KEY `idx_dept_id` (`deptId`)
 #CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
 
 
INSERT INTO t_dept(deptName,address) VALUES('Huashan Mountain','Huashan Mountain');
INSERT INTO t_dept(deptName,address) VALUES('a group of beggars','Luoyang');
INSERT INTO t_dept(deptName,address) VALUES('Mount Emei','Mount Emei');
INSERT INTO t_dept(deptName,address) VALUES('Wudang','Wudang Mountain');
INSERT INTO t_dept(deptName,address) VALUES('Ming religion','Bright Summit');
INSERT INTO t_dept(deptName,address) VALUES('Shaolin Temple','Shaolin Temple');
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('Breezy',90,1,100001);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('yue buqun',50,1,100002);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('linghu chong',24,1,100003);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('master hongqi',70,2,100004);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('Qiao Feng',35,2,100005);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('Abbess extinction',70,3,100006);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('Zhou Zhiruo',20,3,100007);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('Zhang Sanfeng',100,4,100008);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('zhang wuji',25,5,100009);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('Wei Xiaobao',18,null,100010);
 
[Title]
#1. Information of all sect members 
( A,B (shared by two tables)

#2. List all users and display their organization information 
 (A (complete set of)

#3. List all sects 
(B (complete set of)

#4. All non sect members 
(A (unique to)

#5. All sects that no one enters 
(B (unique to)

#6. List the cross reference relationship of all personnel and institutions
(AB All have)
#MySQL Full Join is implemented because MySQL does not support FULL JOIN. The following is an alternative method 
#Left join + union + right join
 
#7. List all the people and sects who are not assigned
(A Unique+B (unique to)

1. Information of all sect members

(shared by tables A and B)

 select * 
 from t_emp a inner join t_dept b 
 on a.deptId = b.id; 

2. List all users and display their organization information

(complete works of A)

 select * 
 from t_emp a left join t_dept b 
 on a.deptId = b.id; 

3. List all sects

(complete works of B)

 select * 
 from  t_dept  b;

4. All non sect members

(unique to A)

select * 
from t_emp a left join t_dept b 
on a.deptId = b.id 
where b.id is null; 

5. All sects that no one enters

(unique to B)

 select * 
 from t_dept b left join  t_emp a 
 on a.deptId = b.id 
 where a.deptId is null; 

6. List the cross reference relationship of all personnel and institutions

(all AB)

#MySQL Full Join is implemented because MySQL does not support FULL JOIN. The following is an alternative method
#Left join + union + right join

SELECT * 
FROM t_emp A LEFT JOIN t_dept B 
ON A.deptId = B.id
UNION
SELECT * 
FROM t_emp A RIGHT JOIN t_dept B 
ON A.deptId = B.id

7. List all the people and sects who are not assigned

(unique to A + unique to B)

SELECT * 
FROM t_emp A LEFT JOIN t_dept B 
ON A.deptId = B.id 
WHERE B.`id` IS NULL
UNION
SELECT * 
FROM t_emp A RIGHT JOIN t_dept B 
ON A.deptId = B.id 
WHERE A.`deptId` IS NULL;

Section 3 single line function

[Title]
# 1. Display system time (Note: date + time)

# 2. Query the employee number, name, salary, and the result after the salary is increased by 20% (new salary)

# 3. Sort the names of employees by initials and write the length of the names

# 4. Query employee id,last_name,salary, and output as a column, alias OUT_PUT

# 5. Make a query and produce the following results
<last_name> earns <salary> monthly but wants <salary*3> Dream Salary
 King earns 24000 monthly but wants 72000

# 6. Use case when according to the following conditions:
job                  grade
AD_PRES              A
ST_MAN               B
IT_PROG              C
SA_REP               D
ST_CLERK             E

The following results are produced
Last_name	Job_id	Grade
king	    AD_PRES	A

1. Display system time (Note: date + time)

SELECT NOW() 
FROM DUAL;

2. Query the employee number, name, salary, and the result after the salary is increased by 20% (new salary)

SELECT employee_id, last_name, salary, salary * 1.2 "new salary"
FROM employees;

3. Sort the names of employees by initials and write the length of the names

SELECT last_name, LENGTH(last_name)
FROM employees
ORDER BY last_name DESC;

4. Query employee id,last_name,salary, and output as a column, alias OUT_PUT

SELECT CONCAT(employee_id, ',' , last_name , ',', salary) OUT_PUT
FROM employees;

5. Make a query and produce the following results

– <last_name> earns <salary> monthly but wants <salary*3>
– Dream Salary
– King earns 24000 monthly but wants 72000

SELECT CONCAT(last_name, ' earns ', TRUNCATE(salary, 0) , ' monthly but wants ', TRUNCATE(salary * 3, 0)) "Dream Salary"
FROM employees;

6. Use case when according to the following conditions:

– job grade
– AD_PRES A
– ST_MAN B
– IT_PROG C
– SA_REP D
– ST_CLERK E

– produce the following results
– Last_name Job_id Grade
– king AD_PRES A

SELECT last_name Last_name, job_id Job_id, CASE job_id WHEN 'AD_PRES' THEN 'A'
						       						   WHEN 'ST_MAN' THEN 'B'
						       						   WHEN 'IT_PROG' THEN 'C'
						       						   WHEN 'SA_REP' THEN 'D'
						      						   WHEN 'ST_CLERK' THEN 'E'
						       						   ELSE 'F'
						       						   END "grade"
FROM employees;

Section 4 grouping function

[Title]
#1. Can the where clause use group functions for filtering? 

#2. Query the maximum value, minimum value, average value and total of employee salary

#3. Query each job_ The maximum value, minimum value, average value, and sum of employee wages of ID

#4. Select each job_ Number of employees with ID

# 5. Query the DIFFERENCE between the maximum wage and the minimum wage

# 6. Query the minimum wage of employees under each manager. The minimum wage cannot be less than 6000, and employees without managers are not included

# 7. Query the names and locations of all departments_ ID, number of employees and average salary

#1. Can the where clause use group functions for filtering?

No!

#2. Query the maximum value, minimum value, average value and total of employee salary

SELECT MAX(salary), MIN(salary), AVG(salary), SUM(salary)
FROM employees;

#3. Query each job_ The maximum value, minimum value, average value, and sum of employee wages of ID

SELECT job_id, MAX(salary), MIN(salary), AVG(salary), SUM(salary)
FROM employees
GROUP BY job_id;

#4. Select each job_ Number of employees with ID

SELECT job_id, COUNT(*)
FROM employees
GROUP BY job_id;

5. Query the DIFFERENCE between the maximum wage and the minimum wage

SELECT MAX(salary), MIN(salary), MAX(salary) - MIN(salary) DIFFERENCE
FROM employees;

6. Query the minimum wage of employees under each manager. The minimum wage cannot be less than 6000, and employees without managers are not included

#select min(salary)
#from employees
#group by manager_id
#having min(salary) > 6000
#and manager_id is not null;

SELECT manager_id, MIN(salary)
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary) > 6000;

7. Query the names and locations of all departments_ ID, number of employees and average salary

SELECT department_name, location_id, COUNT(employee_id), AVG(salary)
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
GROUP BY department_name, location_id;

Section 5 sub query

[Title]
#1. Query the name and salary of employees in the same department as Zlotkey

#2. Query the employee number, name and salary of employees whose salary is higher than the average salary of the company.

#3. Query the employee number, name and salary of employees in each department whose salary is higher than the average salary of the Department (difficult)

#4. Query the employee number and name of the employee whose name contains the letter u in the same department

#5. Query the location of the Department_ Employee number of the employee working in the Department with ID 1700

#6. Query the name and salary of employees whose manager is King

#7. Query the minimum wage employee information: last_name, salary

#8. Query the information of the Department with the lowest average wage
		
#9. Query the information of the Department with the lowest average wage and the average wage of the Department (difficult)

#10. Query the job information with the highest average salary

#11. Query the departments whose average salary is higher than the average salary of the company?

#12. Query the details of all manager s in the company
	
#13. What is the minimum wage of the lowest of the highest wages in each department?

#14. Query the manager details of the Department with the highest average salary: last_name, department_id, email, salary

1. Query the name and salary of employees in the same department as Zlotkey

SELECT last_name, salary
FROM employees
WHERE department_id = (
	SELECT department_id
	FROM employees
	WHERE last_name = 'Zlotkey'
)

2. Query the employee number, name and salary of employees whose salary is higher than the average salary of the company

SELECT employee_id, last_name, salary
FROM employees
WHERE salary > (
	SELECT AVG(salary)
	FROM employees
)

3. Query the employee number, name and salary of employees in each department whose salary is higher than the average salary of the Department (difficult)

Method 1: related sub query

SELECT employee_id,last_name,salary
FROM employees e1
WHERE salary > (
		# Query the average of an employee's Department
		SELECT AVG(salary)
		FROM employees e2
		WHERE e2.department_id = e1.`department_id`
		);

Mode 2:

SELECT employee_id,last_name,salary
FROM employees e1,
(SELECT department_id,AVG(salary) avg_sal
FROM employees e2 GROUP BY department_id
) dept_avg_sal
WHERE e1.`department_id` = dept_avg_sal.department_id
AND e1.`salary` > dept_avg_sal.avg_sal;

4. Query the employee number and name of the employee whose name contains the letter u in the same department

SELECT employee_id, last_name
FROM employees
WHERE department_id = ANY(
	SELECT DISTINCT department_id
	FROM employees
	WHERE last_name LIKE '%u%'
)

5. Query the location of the Department_ Employee number of the employee working in the Department with ID 1700

SELECT employee_id
FROM employees
WHERE department_id IN (
	SELECT department_id
	FROM departments
	WHERE location_id = 1700
)

6. Query the name and salary of employees whose manager is King

SELECT last_name, salary
FROM employees
WHERE manager_id IN (
	SELECT employee_id
	FROM employees
	WHERE last_name = 'King'
)

7. Query the minimum wage employee information: last_name, salary

SELECT last_name,salary
FROM employees
WHERE salary = (
		SELECT MIN(salary)
		FROM employees
	       );

8. Query the information of the Department with the lowest average wage

#Mode 1:

SELECT *
FROM departments
WHERE department_id = (
			SELECT department_id
			FROM employees
			GROUP BY department_id
			HAVING AVG(salary) = (
						SELECT MIN(dept_avgsal)
						FROM (
							SELECT AVG(salary) dept_avgsal
							FROM employees
							GROUP BY department_id
							) avg_sal
						)
			);

#Mode 2:

SELECT *
FROM departments
WHERE department_id = (
			SELECT department_id
			FROM employees
			GROUP BY department_id
			HAVING AVG(salary) <= ALL(
						SELECT AVG(salary) avg_sal
						FROM employees
						GROUP BY department_id
						)
			);	

#Mode 3:

SELECT *
FROM departments 
WHERE department_id = (
			SELECT department_id
			FROM employees
			GROUP BY department_id
			HAVING AVG(salary) = (
					     SELECT AVG(salary) avg_sal
					     FROM employees
					     GROUP BY department_id
					     ORDER BY avg_sal
					     LIMIT 0,1
						)			
			)

#Mode 4:

SELECT d.*
FROM departments d,(
	SELECT department_id,AVG(salary) avg_sal
	FROM employees
	GROUP BY department_id
	ORDER BY avg_sal
	LIMIT 0,1) dept_avg_sal
WHERE d.department_id = dept_avg_sal.department_id

9. Query the information of the Department with the lowest average wage and the average wage of the Department (difficult)

#Mode 1:

SELECT d.*,(SELECT AVG(salary) FROM employees WHERE department_id = d.department_id) avg_sal
FROM departments d
WHERE department_id = (
			SELECT department_id
			FROM employees
			GROUP BY department_id
			HAVING AVG(salary) = (
						SELECT MIN(dept_avgsal)
						FROM (
							SELECT AVG(salary) dept_avgsal
							FROM employees
							GROUP BY department_id
							) avg_sal
						)
			);

#Mode 2:

SELECT d.*,(SELECT AVG(salary) FROM employees WHERE department_id = d.`department_id`) avg_sal
FROM departments d
WHERE department_id = (
			SELECT department_id
			FROM employees
			GROUP BY department_id
			HAVING AVG(salary) <= ALL(
						SELECT AVG(salary) avg_sal
						FROM employees
						GROUP BY department_id
						)
			);

#Mode 3:

SELECT d.*,(SELECT AVG(salary) FROM employees WHERE department_id = d.department_id) avg_sal
FROM departments d
WHERE department_id = (
			SELECT department_id
			FROM employees
			GROUP BY department_id
			HAVING AVG(salary) = (
					     SELECT AVG(salary) avg_sal
					     FROM employees
					     GROUP BY department_id
					     ORDER BY avg_sal
					     LIMIT 0,1
						)			
			)

#Mode 4:

SELECT d.*,dept_avg_sal.avg_sal
FROM departments d,(
	SELECT department_id,AVG(salary) avg_sal
	FROM employees
	GROUP BY department_id
	ORDER BY avg_sal
	LIMIT 0,1) dept_avg_sal
WHERE d.department_id = dept_avg_sal.department_id

10. Query the job information with the highest average salary

#Mode 1:

SELECT *
FROM jobs
WHERE job_id = (
		SELECT job_id
		FROM employees
		GROUP BY job_id 
		HAVING AVG(salary) = (
					SELECT MAX(avg_sal)
					FROM(
						SELECT AVG(salary) avg_sal
						FROM employees
						GROUP BY job_id
					    ) job_avgsal
					)
		);

#Mode 2:

SELECT *
FROM jobs
WHERE job_id = (
		SELECT job_id
		FROM employees
		GROUP BY job_id
		HAVING AVG(salary) >= ALL(
					SELECT AVG(salary) 
					FROM employees
					GROUP BY job_id
					)
		);

#Mode 3:

SELECT *
FROM jobs
WHERE job_id = (
		SELECT job_id
		FROM employees
		GROUP BY job_id
		HAVING AVG(salary) = (
					SELECT AVG(salary) avg_sal
					FROM employees
					GROUP BY job_id
					ORDER BY avg_sal DESC
					LIMIT 0,1
		                      )
		 );

#Mode 4:

SELECT j.*
FROM jobs j,(
		SELECT job_id,AVG(salary) avg_sal
		FROM employees
		GROUP BY job_id
		ORDER BY avg_sal DESC
		LIMIT 0,1 ) job_avg_sal
WHERE j.job_id = job_avg_sal.job_id

11. Query the departments whose average salary is higher than the average salary of the company?

SELECT department_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING AVG(salary) > (
		      SELECT AVG(salary)
		      FROM employees
			);

12. Query the details of all manager s in the company

#Mode 1:

SELECT employee_id,last_name,salary
FROM employees
WHERE employee_id IN (
		      SELECT DISTINCT manager_id
		      FROM employees
		    );

#Mode 2:

SELECT DISTINCT e1.employee_id, e1.last_name, e1.salary
FROM   employees e1 JOIN employees e2
WHERE e1.employee_id = e2.manager_id;

#Mode 3:

SELECT employee_id, last_name, salary
FROM   employees e1
WHERE  EXISTS ( SELECT 'X'
                 FROM   employees e2
                 WHERE  e2.manager_id = e1.employee_id);

13. What is the minimum wage of the lowest of the highest wages in each department?

#Mode 1

SELECT MIN(salary)
FROM employees
WHERE department_id = (
			SELECT department_id
			FROM employees
			GROUP BY department_id
			HAVING MAX(salary) = (
						SELECT MIN(max_sal)
						FROM (
						SELECT MAX(salary) max_sal
						FROM employees
						GROUP BY department_id) dept_max_sal
						)
			);

SELECT *
FROM employees
WHERE department_id = 10;

#Mode II

SELECT MIN(salary)
FROM employees
WHERE department_id = (
			SELECT department_id
			FROM employees
			GROUP BY department_id
			HAVING MAX(salary) <= ALL(						
						SELECT MAX(salary) max_sal
						FROM employees
						GROUP BY department_id
						)
			);

#Mode 3:

SELECT MIN(salary)
FROM employees
WHERE department_id = (
			SELECT department_id
			FROM employees
			GROUP BY department_id
			HAVING MAX(salary) = (
						SELECT MAX(salary) max_sal
						FROM employees
						GROUP BY department_id
						ORDER BY max_sal
						LIMIT 0,1
					     )
			)

#Mode 4:

SELECT employee_id,MIN(salary)
FROM employees e,
(SELECT department_id,MAX(salary) max_sal
FROM employees
GROUP BY department_id
ORDER BY max_sal
LIMIT 0,1) dept_max_sal
WHERE e.department_id = dept_max_sal.department_id

14. Query the manager details of the Department with the highest average salary: last_name, department_id, email, salary

Mode 1:

SELECT employee_id,last_name, department_id, email, salary
FROM employees
WHERE employee_id IN (
			SELECT DISTINCT manager_id
			FROM employees
			WHERE department_id = (
						SELECT department_id
						FROM employees
						GROUP BY department_id
						HAVING AVG(salary) = (
									SELECT MAX(avg_sal)
									FROM(
										SELECT AVG(salary) avg_sal
										FROM employees
										GROUP BY department_id
										) dept_sal
						)
			)

);

Mode 2:

SELECT employee_id,last_name, department_id, email, salary
FROM employees
WHERE employee_id IN (
					SELECT DISTINCT manager_id
					FROM employees
					WHERE department_id = (
										SELECT department_id
										FROM employees e
										GROUP BY department_id
										HAVING AVG(salary)>=ALL(
															SELECT AVG(salary)
															FROM employees
															GROUP BY department_id
																)
											)
						);

#Mode 3:

SELECT *
FROM employees
WHERE employee_id IN (
			SELECT DISTINCT manager_id
			FROM employees e,(
					SELECT department_id,AVG(salary) avg_sal
					FROM employees
					GROUP BY department_id
					ORDER BY avg_sal DESC
					LIMIT 0,1) dept_avg_sal
			WHERE e.department_id = dept_avg_sal.department_id
			)

#Summary:

Classification: related sub query (question 3) vs  Unrelated subquery

Format: sub query is flexible. Can appear in where,from,select,order by ...

Writing skills: ①Write from the outside in  ②Write from the inside out

Section 6 creating and managing tables

1. Operations related to books table

Case:

1. Create database test01_library

2. Create table books

Field nameField descriptiondata typeNull allowedonly
b_idBook numberint(11)noyes
b_nametitlevarchar(50)nono
authorsauthorvarchar(100)nono
pricePricefloatnono
pubdatePublication dateyearnono
noteexplainvarchar(100)yesno
numstockint(11)nono

3. Insert a record into the books table

1) Specify all field names to insert the first record

2) Insert the second record without specifying the field name

3) Insert multiple records at the same time (all remaining records)

b_idb_nameauthorspricepubdatenotenum
1Tal of AAADickes231995novel11
2EmmaTJane lura351993joke22
3Story of JaneJane Tim402001novel0
4Lovey DayGeorge Byron202005novel30
5Old landHonore Blade302010law0
6The BattleUpton Sara301999medicine40
7Rose HoodRichard haggard282008cartoon28

4. Increase the price of novel books by 5.

5. Change the price of the book named EmmaT to 40.

6. Delete record with inventory 0

#Create database test01_library
CREATE DATABASE test01_library;

#Specify which database to use
USE test01_library;

#Create table books
CREATE TABLE books(
	b_id INT,
	b_name VARCHAR(50),
	`authors` VARCHAR(100),
	price FLOAT,
	pubdate YEAR,
	note VARCHAR(100),
	num INT
);

#Specify all field names to insert the first record
INSERT INTO books (b_id,b_name,`authors`,price,pubdate,note,num)
VALUES(1,'Tal of AAA','Dickes',23,1995,'novel',11);

#Insert the second record without specifying the field name
INSERT INTO books 
VALUE(2,'EmmaT','Jane lura',35,1993,'Joke',22);

#Insert multiple records at the same time (all remaining records).
INSERT INTO books VALUES
(3,'Story of Jane','Jane Tim',40,2001,'novel',0),
(4,'Lovey Day','George Byron',20,2005,'novel',30),
(5,'Old land','Honore Blade',30,2010,'Law',0),
(6,'The Battle','Upton Sara',30,1999,'medicine',40),
(7,'Rose Hood','Richard haggard',28,2008,'cartoon',28);

#Increase the price of novel books by 5.
UPDATE books SET price=price+5 WHERE note = 'novel';

#Change the price of the book named EmmaT to 40.
UPDATE books SET price=40 WHERE b_name='EmmaT';

#Delete record with inventory 0
DELETE FROM books WHERE num=0;

2. Operations related to customers table

1. Create database test02_market

2. Create table customers

Field namedata type
c_numint(11)
c_namevarchar(50)
c_contactvarchar(50)
c_cityvarchar(50)
c_birthdate

**Requirement 3: * * C_ Move the contact field to C_ After the birth field

**Requirement 4: * * C_ Change the data type of name field to varchar(70)

**Requirement 5: * * C_ The contact field is renamed c_phone

**Requirement 6: * * add c_gender field to C_ After name, the data type is char(1)

**Requirement 7: * * change the table name to customers_info

**Requirement 8: * * delete field c_city

#1. Create a database Market and enter to operate
CREATE DATABASE test02_market;

#Specify which database to operate on
USE test02_market;

#2. Create the data table customers,
CREATE TABLE customers(
	c_num INT ,
	c_name VARCHAR(50),
	c_contact VARCHAR(50),
	c_city VARCHAR(50),
	c_birth DATE
);

#3. Will c_ The contact field is inserted into C_ After the birth field
ALTER TABLE customers MODIFY c_contact VARCHAR(50) AFTER c_birth;


#4. Will c_ Change the data type of name field to varchar(70)
ALTER TABLE customers MODIFY c_name VARCHAR(70);

#5. Will c_ The contact field is renamed c_phone.
ALTER TABLE customers CHANGE c_contact c_phone VARCHAR(50);


#6. Add c_gender field, data type char(1)
ALTER TABLE customers ADD c_gender CHAR(1) AFTER c_name;
#The default is in the last column
#Add first to the first column
#If you want to specify which column to follow, add the name of the after column


#7. Change the table name to customers_info
ALTER TABLE customers RENAME customers_info;


#8. Delete field c_city
ALTER TABLE customers_info DROP c_city ;

3. Operations related to the offices table

1. Create database company

2. Create form offices

Field namedata type
officeCodeint
cityvarchar(30)
addressvarchar(50)
countryvarchar(50)
postalCodevarchar(25)

3. Create table employees

Field namedata type
empNumint(11)
lastNamevarchar(50)
firstNamevarchar(50)
mobilevarchar(25)
codeint
jobTitlevarchar(50)
birthdate
Notevarchar(255)
Sexvarchar(5)

**Requirement 4: * * modify the mobile field of the employees table to the code field.

**Requirement 5: * * change the birth field of the employees table to birthday;

**Requirement 6: * * modify the sex field and the data type is char (1).

**Requirement 7: * * delete the field note;

**Requirement 8: * * add field name favorite_ Activity, the data type is varchar (100);

**Requirement 9: * * change the name of the table employees to employees_info

#Create database test03_company
CREATE DATABASE test03_company;

#Specify which database to use, that is, which database is targeted by the following sql statements
USE test03_company;

CREATE TABLE offices(
	officeCode INT,
	city VARCHAR(30),
	address VARCHAR(50),
	country VARCHAR(50) ,
	postalcode VARCHAR(25) 
);
CREATE TABLE employees(
	empNum INT(11),
	lastName VARCHAR(50),
	firstName VARCHAR(50),
	mobile VARCHAR(25),
	`code` INT ,
	jobtitle VARCHAR(50),
	birth DATE,
	note VARCHAR(255),
	sex VARCHAR(5)
);

#4. Modify the mobile field of the table employees to the code field.
ALTER TABLE employees MODIFY mobile VARCHAR(25)AFTER CODE;


#5. Change the birth field of the employees table to birthday;
ALTER TABLE employees CHANGE birth birthday DATE NOT NULL;

#6. Modify the sex field, and the data type is char (1)
ALTER TABLE employees MODIFY sex CHAR(1) ;

#7. Delete the field note;
ALTER TABLE employees DROP note;


#8. Add field name favor_ Activity, the data type is varchar (100);
ALTER TABLE employees ADD COLUMN favoriate_activity VARCHAR(100);


#9. Change the name of the table employees to employees_info
ALTER TABLE employees RENAME employees_info;

Section 7 addition, deletion and modification of data processing

1. Operations related to employee table

1. Create database test01db

2. Create the form employee and add records

idnamesexteladdrsalary
10001Zhang Yiyimale13456789000Shaoguan, Guangdong1001.58
10002Liu Xiaohongfemale13454319000Jiangmen, Guangdong1201.21
10003Li Simale0751-1234567Foshan, Guangdong1004.11
10004Liu Xiaoqiangmale0755-5555555Shenzhen, Guangdong1501.23
10005Wang Yanfemale020-1232133Guangzhou, Guangdong1405.16

**Requirement 3: * * query the employee information whose salary is between 1200 and 1300.

**Requirement 4: * * find out the job number, name and home address of the employee surnamed "Liu".

**Requirement 5: * * change the home address of "Li Si" to "Shaoguan, Guangdong"

**Requirement 6: * * query the employees with "small" in their names

-- Create database test01db
CREATE DATABASE test01db;

-- use test01db database
USE test01db;

#Create employee table
CREATE TABLE employee(
	id INT,
	`name` VARCHAR(20),
	sex VARCHAR(20),
	tel VARCHAR(20),
	addr VARCHAR(50),
	salary FLOAT
);

#Add information
INSERT INTO employee(id,`name`,sex,tel,addr,salary)VALUES
(10001,'Zhang Yiyi','male','13456789000','Shaoguan, Guangdong',1001.58),
(10002,'Liu Xiaohong','female','13454319000','Jiangmen, Guangdong',1201.21),
(10003,'Li Si','male','0751-1234567','Foshan, Guangdong',1004.11),
(10004,'Liu Xiaoqiang','male','0755-5555555','Shenzhen, Guangdong',1501.23),
(10005,'Wang Yan','male','020-1232133','Guangzhou, Guangdong',1405.16);


#Requirement 3: query the salary information between 1200 and 1300.
SELECT * FROM employee WHERE salary BETWEEN 1200 AND 1300;

#Requirement 4: find out the job number, name and home address of the employee surnamed "Liu".
SELECT id,addr FROM employee WHERE `name` LIKE 'Liu%';

#Requirement 5: change the home address of "Li Si" to "Shaoguan, Guangdong"
UPDATE employee SET addr='Shaoguan, Guangdong' WHERE `name`='Li Si';

#Requirement 6: query the employees with "small" in their names.
SELECT * FROM employee WHERE `name` LIKE '%Small%';

2. Related operations of pet table

1. Create database test02db

2. Create table pet

Field nameField descriptiondata type
namePet namevarchar(20)
ownerPet ownervarchar(20)
speciestypevarchar(20)
sexGenderchar(1)
birthdate of birthyear
deathDate of deathyear

3. Add record

nameownerspeciessexbirthdeath
FluffyharoldCatf20032010
ClawsgwenCatm2004
BuffyDogf2009
FangbennyDogm2000
bowserdianeDogm20032009
ChirpyBirdf2008

4. Add field owner's birthday owner_birth.

5. Change the owner of the cat named jaws to kevin

6. Change the owner of the undead dog to duck

7. Check the names of pets without owners;

8. Query the name, owner and time of death of the dead cat;

9. Delete dead dogs

10. Query all pet information

-- Create database test02db
CREATE DATABASE test02db;

-- Specify which database to use
USE test02db;

-- stay market Create data table in customers,
CREATE TABLE pet(
	`name` VARCHAR(20),
	`owner` VARCHAR(20),
	species VARCHAR(20),
	sex CHAR(1),
	birth	YEAR,
	death YEAR
);
-- 3,Add data
INSERT INTO pet VALUES('Fluffy','harold','Cat','f','2013','2010'); 				
INSERT INTO pet(`name`,`owner`,species,sex,Birth) VALUES('Claws','gwen','Cat','m','2014'); 
INSERT INTO pet(`name`,species,sex,Birth) VALUES('Buffy','Dog','f','2009');
INSERT INTO pet(`name`,`owner`,species,sex,Birth) VALUES('Fang','benny','Dog','m','2000');
INSERT INTO pet VALUES('bowser','diane','Dog','m','2003','2009');
INSERT INTO pet(`name`,species,sex,birth) VALUES('Chirpy','Bird','f','2008');

#4. Add field owner's birthday owner_birth. 
ALTER TABLE pet ADD COLUMN owner_birth DATE;

#5. Change the owner of the cat named jaws to kevin
UPDATE pet SET `owner`='kevin' WHERE `name`='Claws' AND species='Cat';

#6. Change the owner of the undead dog to duck
UPDATE pet SET `owner`='duck' WHERE species='Dog' AND death IS NULL;

#7. Check the names of pets without owners;
SELECT `name` FROM pet WHERE `owner` IS NULL;

#8. Query the name, owner and time of death of the dead cat;
SELECT `name`,`owner`,death FROM pet WHERE death IS NOT NULL;

#9. Delete dead dogs
DELETE FROM pet WHERE death IS NOT NULL;

#10. Query all pet information
SELECT * FROM pet;

Section 8 constraints

1. Operations related to books table

Case:

1. Create database test_library

2. Create table books

Field nameField descriptiondata type
b_idBook numberint(11)
b_nametitlevarchar(50)
authorsauthorvarchar(100)
pricePricefloat
pubdatePublication dateyear
noteexplainvarchar(100)
numstockint(11)

3. Use the alter statement to add corresponding constraints to books as follows

Field nameField descriptiondata typePrimary keyForeign keyNon emptyonlySelf increasing
b_idBook numberint(11)yesnoyesyesyes
b_nametitlevarchar(50)nonoyesnono
authorsauthorvarchar(100)nonoyesnono
pricePricefloatnonoyesnono
pubdatePublication dateyearnonoyesnono
noteexplainvarchar(100)nonononono
numstockint(11)nonoyesnono

4. Insert a record into the books table

1) Specify all field names to insert the first record

2) Insert the second record without specifying the field name

3) Insert multiple records at the same time (all remaining records)

b_idb_nameauthorspricepubdatenotenum
1Tal of AAADickes231995novel11
2EmmaTJane lura351993joke22
3Story of JaneJane Tim402001novel0
4Lovey DayGeorge Byron202005novel30
5Old landHonore Blade302010law0
6The BattleUpton Sara301999medicine40
7Rose HoodRichard haggard282008cartoon28

5. Books with a letter in the title of statistical books

6. Count the number of books with a letter in the title and the total inventory

7. Find the "novel" books and arrange them in descending order of price

8. Query the book information and arrange it in descending order according to the inventory. If the inventory is the same, arrange it in ascending order according to the note

9. Count the number of books by note

10. According to the inventory of the note classification statistics book, it displays the inventory of more than 30 books

11. Query all books, display 5 books per page, and display the second page

12. According to the inventory of the note classification statistics book, the company with the largest inventory now

13. Query the book with a title of 10 characters, excluding the spaces inside

14. Query the title and type of the book, where

note value is novel, law shows law, medicine shows medicine, cartoon shows cartoon and joke shows joke

15. Query book title and inventory, where

If the num value exceeds 30, it shows unsalable; if it is greater than 0 and less than 10, it shows best-selling; if it is 0, it needs to be out of stock

16. Count the inventory of each note and total the total

17. Count the number of each note and sum up the total

18. Statistics of the top three books in stock

19. Find the first book published

20. Find the highest book in novel

21. Find the book with the largest number of words in the title, without spaces

#Create database test_library
CREATE DATABASE test_library;

#Use test_library
USE test_library;

#Create table books
CREATE TABLE books(
	b_id INT,
	b_name VARCHAR(50),
	`authors` VARCHAR(100),
	price FLOAT,
	pubdate YEAR,
	note VARCHAR(100),
	num INT
);

#Give b_id add primary key constraint
ALTER TABLE books ADD PRIMARY KEY(b_id);

#Give B_ Add auto increment constraint to ID field
ALTER TABLE books MODIFY b_id INT AUTO_INCREMENT;

#Give B_ Add non empty constraints to fields such as name
ALTER TABLE books b_name VARCHAR(50) NOT NULL;
ALTER TABLE books `authors` VARCHAR(100) NOT NULL;
ALTER TABLE books price FLOAT NOT NULL;
ALTER TABLE books pubdate DATE NOT NULL;
ALTER TABLE books num INT NOT NULL;

#Specify all field names to insert the first record
INSERT INTO books (b_id,b_name,`authors`,price,pubdate,note,num)
VALUES(1,'Tal of AAA','Dickes',23,1995,'novel',11);

#Insert the second record without specifying the field name
INSERT INTO books 
VALUE(2,'EmmaT','Jane lura',35,1993,'Joke',22);

#Insert multiple records at the same time (all remaining records).
INSERT INTO books VALUES
(3,'Story of Jane','Jane Tim',40,2001,'novel',0),
(4,'Lovey Day','George Byron',20,2005,'novel',30),
(5,'Old land','Honore Blade',30,2010,'Law',0),
(6,'The Battle','Upton Sara',30,1999,'medicine',40),
(7,'Rose Hood','Richard haggard',28,2008,'cartoon',28);

#Look up books whose titles contain the letter a
SELECT * FROM books WHERE b_name LIKE '%a%';

#Count the number of books with a letter in the title and the total inventory
SELECT COUNT(*),SUM(num) FROM books WHERE b_name LIKE '%a%';

#Find the "novel" books and arrange them in descending order of price
SELECT * FROM books WHERE note = 'novel' ORDER BY price DESC;

#Query the book information and arrange it in descending order according to the inventory. If the inventory is the same, arrange it in ascending order according to the note
SELECT * FROM books ORDER BY num DESC,note ASC;

#Count the number of books by note
SELECT note,COUNT(*) FROM books GROUP BY note;

#According to the inventory of the note classification statistics book, it displays the inventory of more than 30 books
SELECT note,SUM(num) FROM books GROUP BY note HAVING SUM(num)>30;

#Query all books, display 5 books per page, and display the second page
SELECT * FROM books LIMIT 5,5;

#According to the inventory of the note classification statistics book, the company with the largest inventory now
SELECT note,SUM(num) FROM books GROUP BY note ORDER BY SUM(num) DESC LIMIT 0,1;

#Query the book with a title of 10 characters, excluding the spaces inside
SELECT * FROM books WHERE CHAR_LENGTH(REPLACE(b_name,' ',''))>=10;

/*
Query the title and type of the book, where
​note A value of novel shows a novel, law shows a law, medicine shows medicine, cartoon shows a cartoon, and joke shows a joke
*/
SELECT b_name AS "title" ,note, CASE note 
 WHEN 'novel' THEN 'novel'
 WHEN 'law' THEN 'law'
 WHEN 'medicine' THEN 'medicine'
 WHEN 'cartoon' THEN 'Cartoon'
 WHEN 'joke' THEN 'joke'
 END AS "type"
FROM books;

/*
Query book title and inventory, where
num If the value exceeds 30, it shows unsalable; if it is greater than 0 and less than 10, it shows best-selling; if it is 0, it needs to be out of stock, and other displays are normal
*/
SELECT b_name,num,CASE 
  WHEN num>30 THEN 'Unsalable'
  WHEN num>0 AND num<10 THEN 'Best seller'
  WHEN num=0 THEN 'No goods'
  ELSE 'normal'
  END AS "Inventory status"
FROM books;

#Count the inventory of each note and total the total
SELECT IFNULL(note,'Total inventory') AS note,SUM(num) FROM books GROUP BY note WITH ROLLUP;

#Count the number of each note and sum up the total
SELECT IFNULL(note,'Total') AS note,COUNT(*) FROM books GROUP BY note WITH ROLLUP;

#Statistics of the top three books in stock
SELECT * FROM books ORDER BY num DESC LIMIT 0,3;

#Find the first book published
SELECT * FROM books ORDER BY pubdate ASC LIMIT 0,1;

#Find the highest book in novel
SELECT * FROM books WHERE note = 'novel' ORDER BY price DESC LIMIT 0,1;

#Find the book with the largest number of words in the title
SELECT * FROM books ORDER BY CHAR_LENGTH(REPLACE(b_name,' ','')) DESC LIMIT 0,1;

2. Related operations of department and other tables

1. Create database: test_company

2. Create the following 3 tables under this database. The data type, width and whether it is empty are defined according to the actual situation.

A. Department table: department number (depid), department name (depname), Department profile (deinfo); The department number is the primary key.

B. Employee form: employee number (empid), name (name), sex (sex), title (title), birth date (birthday), department number (depid); among

  • Employee number is the primary key;
  • The department number is a foreign key, and the foreign key constraint level is (on update cascade and on delete set null);
  • Gender is male by default;

C. Salary table: employee number (empid), base salary, title salary, deduction. The employee number is the primary key.

3. Add a foreign key constraint to the employee number (empid) of the salary table. The foreign key constraint levels are (on update cascade and on delete cascade)

4. Add data as follows:

Department table:

Department numberDepartment nameDepartment profile
111Production departmentNull
222Sales DepartmentNull
333Ministry of PersonnelHuman resource management

Employee table:

Employee numberfull nameGendertitledate of birthDepartment No
1001Zhang Sanmalesenior engineer1975-1-1111
1002Li SifemaleAssistant Engineer1985-1-1111
1003Wang Wumaleengineer1978-11-11222
1004Zhang Liumaleengineer1999-1-1222

payroll:

Employee numberbase payJob salarydeduction
100122001100200
10021200200NULL
10032900700200
10041950700150

5. Query the employee number, name, professional title, department name, salary payable (basic salary + job salary) and paid salary (basic salary + job salary - deduction) of each employee.

6. Query the name and basic salary of employees in the sales department

7. Query all information and age of employees with surname "Zhang" and age less than 40

8. Query the basic salary and job salary of all male employees

9. Query the name, title and department name of employees whose basic salary is less than 2000

10. Query total number of employees

11. Total number of query departments

12. Query the average wage, maximum wage and minimum wage of wages payable

13. Calculate the average wage of wages payable according to the Department

14. Find out if the average salary of the Department's basic salary is lower than 2000

15. According to the employee number, name, basic salary, job salary and deduction, and in ascending order of job. If the job salary is the same, then in ascending order of basic salary

16. Query employee number, name, date of birth, and age group, where

If born 80 years ago, it is defined as "old age"; the post-80s are defined as "middle age" and the post-90s are defined as "young adults"“

17. Query all employee information and the name of his department

18. Query all department information and employee information of the Department

19. Query the number of male employees with "engineer" in all positions

20. Query the number and average basic salary of boys and girls in each department

#Create database: test_company
CREATE DATABASE test_company;

#Using the database test_company
USE test_company;

#Create department table
CREATE TABLE department(
	depid INT PRIMARY KEY,
	depname VARCHAR(20) NOT NULL,
	deinfo VARCHAR(50)
);

#Create employee table (emoloyee)
CREATE TABLE employee(
	empid INT PRIMARY KEY,
	`name` VARCHAR(20) NOT NULL,
	sex CHAR NOT NULL DEFAULT 'male',
	title VARCHAR(20) NOT NULL,
	birthday DATE,
	depid INT,
	FOREIGN KEY(depid) REFERENCES department(depid) ON UPDATE CASCADE ON DELETE SET NULL
);

#Create payroll
CREATE TABLE salary(
	empid INT PRIMARY KEY,
	basesalary DOUBLE,
	titlesalary DOUBLE,
	deduction DOUBLE
);

#Add a foreign key constraint to the employee number (empid) of the salary table. The foreign key constraint levels are (on update cascade and on delete cascade)
ALTER TABLE salary ADD FOREIGN KEY empid REFERENCES employee(empid) ON UPDATE CASCADE ON DELETE CASCADE;

#Add department table data
INSERT INTO department VALUES
(111,'Production department',NULL),
(222,'Sales Department',NULL),
(333,'Ministry of Personnel','Human resource management');

#Add employee table
INSERT INTO employee VALUES
(1001,'Zhang San',DEFAULT,'senior engineer','1975-1-1',111),
(1002,'Li Si','female','Assistant Engineer','1985-1-1',111),
(1003,'Wang Wu','male','engineer','1978-11-11',222),
(1004,'Zhang Liu',DEFAULT,'engineer','1999-1-1',222);

#Add payroll
INSERT INTO salary VALUES
(1001,2200,1100,200),
(1002,1200,200,NULL),
(1003,2900,700,200),
(1004,1950,700,150);


/*
Find out the employee number, name, professional title and department name of each employee,
Salary payable (basic salary + job salary),
Paid salary (basic salary + job salary - deduction).
*/
SELECT employee.empid,`name`,title,depname,
basesalary+titlesalary AS "Wages payable",
basesalary+titlesalary-IFNULL(deduction,0) AS "net salary"
FROM department INNER JOIN employee INNER JOIN salary
ON department.depid = employee.depid AND employee.empid = salary.empid;

#Query the name and basic salary of employees in the sales department
SELECT `name`,basesalary
FROM department INNER JOIN employee INNER JOIN salary
ON department.depid = employee.depid AND employee.empid = salary.empid
WHERE department.depname = 'Sales Department';

#Query all information and age of employees with surname "Zhang" and age less than 40
SELECT *,YEAR(CURRENT_DATE())-YEAR(birthday) AS "Age" 
FROM employee 
WHERE `name` LIKE 'Zhang%' AND YEAR(CURRENT_DATE())-YEAR(birthday)<40;

#Query the basic salary and job salary of all male employees
SELECT basesalary,titlesalary 
FROM employee INNER JOIN salary
ON employee.empid = salary.empid
WHERE employee.sex = 'male';

#Query the name, title and department name of employees whose basic salary is less than 2000
SELECT `name`,title,depname
FROM department INNER JOIN employee INNER JOIN salary
ON department.depid = employee.depid AND employee.empid = salary.empid
WHERE basesalary < 2000;

#Query total number of employees
SELECT COUNT(*) FROM employee;

#Total number of query departments
SELECT COUNT(*) FROM department;

#Query the average wage, maximum wage and minimum wage payable
SELECT AVG(basesalary+titlesalary) AS "Average salary payable",
	MAX(basesalary+titlesalary) AS "Maximum salary payable",
	MIN(basesalary+titlesalary) AS "Minimum wage payable"
FROM salary;

#Calculate the average wage of wages payable according to the Department
SELECT depid,AVG(basesalary+titlesalary)
FROM employee INNER JOIN salary
ON employee.`empid` = salary.`empid`
GROUP BY employee.`depid`;

#Find out if the average salary of the Department's basic salary is lower than 2000
SELECT depid,AVG(basesalary)
FROM employee INNER JOIN salary
ON employee.`empid` = salary.`empid`
GROUP BY employee.`depid`
HAVING AVG(basesalary)<2000;


#According to employee number, name, basic salary, job salary and deduction,
#They are arranged in ascending order by job. If the job salary is the same, they are arranged in ascending order by basic salary
SELECT emp.empid,`name`,basesalary,titlesalary,deduction
FROM employee emp INNER JOIN salary
ON emp.`empid` = salary.`empid`
ORDER BY emp.`title` ASC , basesalary ASC;

#Query employee number, name, date of birth, and age group, where
​#If born 80 years ago, it is defined as "old age"; the post-80s are defined as "middle age" and the post-90s are defined as "young adults"“
SELECT empid,`name`,birthday,
 CASE WHEN YEAR(birthday)<1980 THEN 'old age'
      WHEN YEAR(birthday)<1990 THEN 'middle age'
 ELSE 'Young adults' END "age group"
FROM employee;
 
#Query all employee information and the name of his department
SELECT emp.*,depname
FROM employee emp LEFT JOIN department dep
ON emp.`depid` = dep.`depid`;

#Query all department information and employee information of the Department
SELECT dep.*,emp.*
FROM employee emp RIGHT JOIN department dep
ON emp.`depid` = dep.`depid`;


#Query the number of male employees with "engineer" in all positions
SELECT COUNT(*) FROM employee WHERE sex='male' AND title LIKE '%engineer%';

#Query the number and average basic salary of boys and girls in each department
SELECT dep.depid,sex,COUNT(*),AVG(basesalary)
FROM department dep INNER JOIN employee INNER JOIN salary
ON dep.depid = employee.depid AND employee.empid = salary.empid
GROUP BY dep.depid,sex;

3. Related operations of department table

1. Create a database: test_school

2. Create the following table

Table 1 definition of department table

Field nameField descriptiondata typePrimary keyForeign keyNon emptyonly
DepNoDepartment numberint(10)yesnoyesyes
DepNameDepartment namevarchar(20)nonoyesno
DepNoteDepartment remarksVarchar(50)nononono

Table 2 definition of teacher table

Field nameField descriptiondata typePrimary keyForeign keyNon emptyonly
NumberFaculty numberintyesnoyesyes
Namefull namevarchar(30)nonoyesno
SexGendervarchar(4)nononono
Birthdate of birthdatenononono
DepNoDepartment numberintnoyesnono
Salarywagesfloatnononono
AddressHome addressvarchar(100)nononono

3. Add record

DepNoDepNameDepNote
601Department of software technologySoftware technology and other majors
602Department of network technologyMultimedia technology and other majors
603Department of art and designAdvertising art design and other majors
604Department of Management EngineeringMajor in chain operation and management
NumberNameSexBirthDepNoSalaryAddress
2001Tomfemale1970-01-106024500Mianyang City, Sichuan Province
2002Lucymale1983-12-186012500Changping District, Beijing
2003Mikemale1990-06-016041500Yuzhong District, Chongqing
2004Jamesfemale1980-10-206023500Chengdu City, Sichuan Province
2005Jackmale1975-05-306031200Nan'an District, Chongqing

4. Query all records of the Teacher table with the SELECT statement.

5. Find out the teaching staff number and department name of all teachers whose home address contains "Beijing", and require that the column titles in the display results be represented by Chinese aliases.

6. Get the highest paid faculty number and name in the Teacher table.

7. Find all faculty numbers with income between 2500 and 4000.

8. Find the name, gender and salary of teachers working in the Department of network technology.

#Create a database: test_school
CREATE DATABASE test_school;

#Use database
USE test_school;

#Create table
-- Department information sheet Department
CREATE TABLE Department(
	DepNo INT(10) PRIMARY KEY,
	DepName VARCHAR(20) NOT NULL,
	DepNote VARCHAR(50)
);
-- Create data table Teacher
CREATE TABLE Teacher(
	Number INT PRIMARY KEY,
	`Name` VARCHAR(30) UNIQUE,
	Sex VARCHAR(4),
	Birth DATE,
	DepNo INT,
	Salary FLOAT,
	Address VARCHAR(100),
	FOREIGN KEY (DepNo) REFERENCES Department(DepNo)
);
-- Insert the contents of Table 4 Department In the table
INSERT INTO Department VALUES (601,'Department of software technology','Software technology and other majors');
INSERT INTO Department VALUES (602,'Department of network technology','Multimedia technology and other majors');
INSERT INTO Department VALUES (603,'Department of art and design','Advertising art design and other majors');
INSERT INTO Department VALUES (604,'Department of Management Engineering','Major in chain operation and management');
-- Insert the contents of Table 3 Teacher In the table.
INSERT INTO Teacher VALUES(2001,'Tom','female','1970-01-10',602,4500,'Mianyang City, Sichuan Province');
INSERT INTO Teacher VALUES(2002,'Lucy','male','1983-12-18',601,2500,'Changping District, Beijing');
INSERT INTO Teacher VALUES(2003,'Mike','male','1990-06-01',604,1500,'Yuzhong District, Chongqing');
INSERT INTO Teacher VALUES(2004,'James','female','1980-10-20',602,3500,'Chengdu City, Sichuan Province');
INSERT INTO Teacher VALUES(2005,'Jack','male','1975-05-30',603,1200,'Nan'an District, Chongqing');

#Query all records of the Teacher table with the SELECT statement.
SELECT * FROM teacher;

#Find out the teaching staff number and department name of all teachers with "Beijing" in their home address, and require the titles of each column in the display results to be expressed in Chinese.
SELECT number AS Faculty number,Teacher.depno AS Department name
FROM Teacher INNER JOIN Department
ON Teacher.DepNo = Department.DepNo
WHERE address LIKE '%Beijing%';

#Get the highest paid faculty number and name in the Teacher table.
SELECT number,`name` FROM teacher WHERE salary = (SELECT MAX(salary) FROM teacher);
SELECT number,`name` FROM teacher ORDER BY salary DESC LIMIT 0,1;

#Find all faculty numbers with income between 2500 and 4000.
SELECT number FROM teacher WHERE salary BETWEEN 2500 AND 4000;

#Find the name, gender and salary of teachers working in the Department of network technology.
SELECT `name`,sex,salary FROM teacher 
WHERE depno=(SELECT depno FROM department WHERE depname='Department of network technology');

SELECT `name`,sex,salary 
FROM teacher INNER JOIN department
ON teacher.depno = department.depno
WHERE depname ='Department of network technology';

4. student table related operations

Case:

1. Establish database test_student

2. Create the following three tables and insert records

Table:Classes

majorclassfull nameSex seat
computer networkClass 1Zhang Sanmale8
software engineeringClass 2Li Simale12
Computer maintenanceClass 1Wang Wumale9
computer networkClass 2LILYfemale15
software engineeringClass 1cockroachmale20
Computer maintenanceClass 1CoCofemale18

Table:Score

full nameEnglishmathematicschinese
Zhang San657598
Li Si874586
Wang Wu988565
LILY758687
cockroach856058
CoCo968770

Table: Records

full namerecord
cockroachlate
cockroachcompassionate leave
Li SiAbsenteeism
Li SiAbsenteeism
Li Silate
CoCosick leave
LILYcompassionate leave

Requirement 3: write the SQL statement that modifies Zhang San's Chinese score to 88.

Requirement 4: search the average score of each course in class 1 of computer maintenance.

Requirement 5: search the list of people who have failed in the subject.

Requirement 6: query and record the names and grades of students who have recorded more than 2 times.

#Establish database test_student
CREATE DATABASE test_student;

#Use database
USE test_student;

#Create tables and add records
CREATE TABLE Classes(
	Pro_name VARCHAR(20) NOT NULL,
	Grade VARCHAR(10) NOT NULL,
	`name` VARCHAR(10) NOT NULL,
	sex VARCHAR(4) NOT NULL,
	seat INT(10) NOT NULL UNIQUE
);
CREATE TABLE Score(
	`name` VARCHAR(10) NOT NULL,
	En_score INT(10) NOT NULL,
	Ma_score INT(10) NOT NULL,
	Ch_score INT(10) NOT NULL
);
CREATE TABLE Records(
	`name` VARCHAR(10) NOT NULL,
	record VARCHAR(10)
);

-- towards classes Add data to
INSERT INTO classes VALUES('computer network','1 class','Zhang San','male',8);
INSERT INTO classes VALUES('software engineering','2 class','Li Si','male',12);
INSERT INTO classes VALUES('Computer maintenance','1 class','Wang Wu','male',9);
INSERT INTO classes VALUES('computer network','2 class','LILY','female',15);
INSERT INTO classes VALUES('software engineering','1 class','cockroach','male',20);
INSERT INTO classes VALUES('Computer maintenance','1 class','CoCo','female',18);

-- towards score Add data to
INSERT INTO Score VALUES('Zhang San',65,75,98);
INSERT INTO Score VALUES('Li Si',87,45,86);
INSERT INTO Score VALUES('Wang Wu',98,85,65);
INSERT INTO Score VALUES('LILY',75,86,87);
INSERT INTO Score VALUES('cockroach',85,60,58);
INSERT INTO Score VALUES('CoCo',96,87,70);

-- towards records Add data to
INSERT INTO records VALUES('cockroach','late');
INSERT INTO records VALUES('cockroach','compassionate leave');
INSERT INTO records VALUES('Li Si','Absenteeism');
INSERT INTO records VALUES('Li Si','Absenteeism');
INSERT INTO records VALUES('Li Si','late');
INSERT INTO records VALUES('CoCo','sick leave');
INSERT INTO records VALUES('LILY','compassionate leave');

#Requirement 3: write the SQL statement that modifies Zhang San's Chinese score to 88.
UPDATE score SET ch_score=88 WHERE `name`='Zhang San';

#Requirement 4: search the average score of each course in class 1 of computer maintenance.
SELECT AVG(en_score),AVG(ma_score),AVG(ch_score) FROM score 
WHERE `name` IN (SELECT `name` FROM classes WHERE Pro_name='Computer maintenance' AND grade='1 class');

#Requirement 5: search the list of people who have failed in the subject.
SELECT `name` FROM score WHERE en_score<60 OR ma_score<60 OR ch_score<60;

#Requirement 6: query and record the names and grades of students who have recorded more than 2 times.
SELECT * 
FROM score INNER JOIN 
(SELECT `name`,COUNT(*) FROM Records GROUP BY `name` HAVING COUNT(*)>2) temp
ON score.name = temp.name;

5. Related operations of student and other tables

1. Establish database: test_xuankedb

2. Create the following three tables:

Student form consists of five fields: student number (Sno), name (Sname), gender (Ssex), age (Sage) and department (Sdept). Sno is the keyword.

Curriculum consists of four fields: Course number (Cno), Course name (Cname), elective Course number (Cpno) and credit (Ccredit). Cno is the keyword.

The grade sheet SG consists of three fields: student number (Sno), course number (Cno) and grade (SNO, CNO) as keywords.

3. Add the "enrollment time (Scome)" column to the Student table, and its data type is date type.

4. Query the student number and grades of students who have taken No. 3 course, and the query results are arranged in descending order of scores.

5. Query the highest score and average score of students studying course 1.

6. Inquire about students studying in the same department as "Li Yang".

7. Set the grades of all students in the computer department to zero.

8. Delete the student record No. 05019 in the student form.

9. Delete the grade records of all students in the computer department.

-- Create a database: test_xuankedb
CREATE DATABASE test_xuankedb;

-- Use database
USE test_xuankedb;

-- Create student table
CREATE TABLE student(
	sno INT(10) PRIMARY KEY,
	sname VARCHAR(10),
	ssex VARCHAR(10),
	sage INT(10),
	sdept VARCHAR(40)
);

-- Create Curriculum
CREATE TABLE course(
	cno INT(10) PRIMARY KEY,
	cname VARCHAR(20),
	cpno VARCHAR(40),
	ccredit INT(20)
);

-- Create grade sheet
CREATE TABLE sg(
	sno INT(10),
	cno INT(10),
	grade INT(3),
	PRIMARY KEY(sno,cno),
	CONSTRAINT stu_s_sno_fk FOREIGN KEY (sno) REFERENCES student(sno),
	CONSTRAINT cou_s_sno_fk FOREIGN KEY (cno) REFERENCES course(cno)
);

#3. Add the column "enrollment time (Scome)" to the Student table, and its data type is date type.
ALTER TABLE student ADD COLUMN scome DATE;

#4. Query the student number and grades of students who have taken No. 3 course, and the query results are arranged in descending order of scores.
SELECT sno,grade FROM sg WHERE cno=3 ORDER BY grade DESC;

#5. Query the highest score and average score of students studying course 1.
SELECT MAX(grade),AVG(grade) FROM sg WHERE cno=1;

#6. Inquire about students studying in the same department as "Li Yang".
SELECT * FROM student WHERE sdept=(SELECT sdept FROM student WHERE sname='Li Yang');

#7. Set the scores of all students in the computer department to zero.
UPDATE sg SET grade=0 WHERE sno IN (SELECT sno FROM student WHERE sdept='Computer Department')

#8. Delete the student record No. 05019 in the student form.
DELETE FROM student WHERE sno=05019;

#9. Delete the grade records of all students in the computer department.
DELETE FROM sg WHERE sno IN (SELECT sno FROM student WHERE sdept='Computer Department');

6. Related operations of press and other tables

1. Establish database: test_library

2. Create the following three tables:
Table 1: press press
Attributes: number Presid (int), name presname (varchar), address(varchar)

Table 2: sort types
Attribute: number sortno(int), quantity Scout (int)

Table 2: book
Attributes: No. bid(int), name bname(varchar), type bsortno(int), press No. pressid(int)

3. Add a column of attributes to the sort table: descriptions (varchar)

4. Insert several pieces of data into each of the three tables

[the external chain picture transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-2WuNn3x2-1631281610414)(MySQL database chapter exercise. assets/1566490682634.png)]

[the external link image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-hr5JvOYq-1631281610417)(MySQL database chapter exercise. assets/1566490720312.png)]

[the external chain picture transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-352TBoHI-1631281610418)(MySQL database chapter exercise. assets/1566490751669.png)]

5. Query all information of books with Publisher id 100

6. Query all the information about the books published by FLTRP

7. Query the category with the number of books (scount) greater than 100

8. Query the publisher information with the largest variety of books

-- Establish database: test_library
CREATE DATABASE test_library;

-- Use database
USE test_library;

-- Create publisher table
CREATE TABLE press(
	pressid INT(10) PRIMARY KEY,
	pressname VARCHAR(30),
	address VARCHAR(50)
);

-- Create a category table
CREATE TABLE sort(
	sortno INT(10) PRIMARY KEY,
	scount INT(10)
);

-- Create book table
CREATE TABLE book(
	bid INT(10) PRIMARY KEY,
	bname VARCHAR(40),
	bsortno INT(10),
	pressid INT(10),
	CONSTRAINT p_b_pid_fk FOREIGN KEY (pressid) REFERENCES press(pressid),
	CONSTRAINT s_b_sno_fk FOREIGN KEY (bsortno) REFERENCES sort(sortno)
);

-- Add a list of attributes
ALTER TABLE sort ADD COLUMN describes VARCHAR(30);

-- Add data
INSERT INTO press VALUES(100,'FLTRP','Shanghai');
INSERT INTO press VALUES(101,'Peking University Press','Beijing');
INSERT INTO press VALUES(102,'Education Press','Beijing');

-- Add data
INSERT INTO sort(sortno,scount,describes)VALUES(11,50,'novel');
INSERT INTO sort(sortno,scount,describes)VALUES(12,300,'science fiction');
INSERT INTO sort(sortno,scount,describes)VALUES(13,100,'mythology');

-- Add data
INSERT INTO book VALUES(1,'Red and black',11,100);
INSERT INTO book VALUES(2,'Ice Fantasy',12,102);
INSERT INTO book VALUES(3,'MYTHOS',13,102);
INSERT INTO book VALUES(4,'The one thousand one night',13,102);

#5. Query all information of books with Publisher id 100
SELECT * FROM book WHERE pressid=100;

#6. Inquire all the information about the books published by FLTRP
SELECT * FROM book WHERE pressid=(SELECT pressid FROM press WHERE pressname='FLTRP');

#7. Query the category with the number of books (scount) greater than 100
SELECT * FROM sort WHERE scount>100;

#8. Query the information of the publishing house with the largest variety of books
SELECT * FROM press WHERE pressid=(
	SELECT temp.pressid FROM 
 (SELECT pressid,MAX(t.c) FROM (SELECT pressid,COUNT(*) AS c FROM book GROUP BY pressid ORDER BY c DESC ) AS t) AS temp);


SELECT * FROM press WHERE pressid=(
SELECT pressid
FROM (SELECT pressid,bsortno FROM book GROUP BY pressid,bsortno) temp
GROUP BY pressid
ORDER BY COUNT(*) DESC
LIMIT 0,1)

7. Related operations of agency and other tables

1. Establish database: test_tour

2. Create the following two tables:

Agency travel agency form:

Column name (English name)Column name (Chinese name)data typeNull value allowedexplain
IdTravel agency NointnoPrimary key
NameTravel agency namevarcharno
AddressTravel agency addressvarcharno
AreaidRegion IdIntyes

travel route table:

Column name (English name)Column name (Chinese name)data typeNull value allowedexplain
TidTravel route numberintnoPrimary key
TimeTime requiredvarcharno
Positiondestinationvarcharno
MoneyspendFloatyes
AidTravel agency idIntnoForeign key
CountNumber of applicantsIntyes

3. Add record

[the external link image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-umK3Yasq-1631281610420)(MySQL database chapter exercise. assets/1566490822909.png)]

[the external chain picture transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-xE2BlWfk-1631281610421)(MySQL database chapter exercise. assets/1566490863309.png)]

4. Find out the Hotels with the most travel routes

5. Find out the most popular travel routes (that is, find out the routes with the largest number of applicants)

6. Check travel routes that cost less than 5000

7. Find the most expensive travel agency name for a trip

8. Find out how long it takes to play all the travel routes of the youth hostel.

#Establish database: test_tour
CREATE DATABASE test_tour;

#Use database
USE test_tour;

CREATE TABLE agency(
	id INT PRIMARY KEY NOT NULL,
	NAME VARCHAR(20) NOT NULL,
	address VARCHAR(100) NOT NULL,
	areaid INT 
);

CREATE TABLE trval(
	tid INT PRIMARY KEY NOT NULL,
	TIME VARCHAR(50) NOT NULL,
	POSITION VARCHAR(100) NOT NULL,
	money FLOAT,
	aid INT NOT NULL,
	rcount INT,
	CONSTRAINT bk_aid FOREIGN KEY trval(aid) REFERENCES agency(id)
);

INSERT INTO agency(id,NAME,address) VALUES (101,'Youth Travel Agency','Haidian, Beijing');
INSERT INTO agency(id,NAME,address) VALUES (102,'Tiantian travel agency','Tianjin Haiyuan');

INSERT INTO trval(tid,TIME,POSITION,money,aid,rcount) VALUES (1,'5 day','badaling section of the great wall',3000,101,10);
INSERT INTO trval(tid,TIME,POSITION,money,aid,rcount) VALUES (2,'7 day','Water Great Wall',5000,101,14);
INSERT INTO trval(tid,TIME,POSITION,money,aid,rcount) VALUES (3,'8 day','Water Great Wall',6000,102,11);

SELECT * FROM agency;
SELECT * FROM trval;

#4. Find out the Hotels with the most travel routes
SELECT * 
FROM agency INNER JOIN 
(SELECT t.aid,MAX(t.c) FROM (SELECT aid,COUNT(*) AS c FROM trval GROUP BY aid)  AS t)temp
ON agency.id = temp.aid

#5. Find out the most popular travel routes (that is, find out the routes with the largest number of applicants)
SELECT * FROM trval WHERE rcount=(SELECT MAX(rcount) FROM trval);

#6. Check travel routes that cost less than 5000
SELECT * FROM trval WHERE money<5000;


#7. Find the most expensive travel agency name for a trip
SELECT NAME FROM agency WHERE id =
(SELECT aid FROM trval WHERE money =(SELECT MAX(money) FROM trval ));

#8. Find out how long it takes to play all the travel routes of the youth hostel.
SELECT SUM(TIME) FROM trval WHERE aid=(SELECT id FROM agency WHERE NAME='Youth Travel Agency');

Tags: MySQL

Posted on Fri, 10 Sep 2021 21:54:09 -0400 by Chalks