First, the financial management system – database module

1. Task Overview

Feng Shuai, a programmer, straightened out the student system. The leader saw that his SQL was good, so he arranged to follow a new project. The project was financial management for the company’s financial department. Below are the data tables and functional requirements required for the project

Data sheets:

Employee table: Employee number (empID, primary key), name (name), sex (sex), title (title), Date of birth (depID)
Department: Department number (depID, primary key), department name (depname)
Salary schedule: employee number (empid), basic salary, titlesalary, deduction

Requirements:

1. Modify the table structure and add the department introduction field to the department table
2. Change Li Si’s professional title to “Engineer”, and change her basic salary to 2000 and post salary to 700
3. Delete the records of the personnel department
4. Find out the employee number of each employee, real salary, payable salary
5. Query employee records whose surname is Zhang and age is younger than 40
6. Query employee id, name, title, department name, real pay
7. Query the name and salary of employees in the sales department
8. Count the number of people with various professional titles
9. Make statistics of the department name, the sum of real wages and the average salary of each department
10. Query the name of the employee whose base salary is higher than that of all employees in the sales department

2. Reference code

2.1 Data Preparation

Employee table (employee)

/* Navicat Premium Data Transfer Source Server : localhost_3306 Source Server Type : MySQL Source Server Version : 50731 Source Host : localhost:3306 Source Schema : demo Target Server Type : MySQL Target Server Version : 50731 File Encoding : 65001 Date: 20/03/2021 21:34:24 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for employee -- ---------------------------- DROP TABLE IF EXISTS `employee`; CREATE TABLE `employee` ( `empid` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `sex` varchar(4) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `title` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `birthday` datetime(0) NULL DEFAULT NULL, `depid` int(11) NULL DEFAULT NULL, PRIMARY KEY (`empid`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 17 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of employee -- ---------------------------- INSERT INTO `employee` VALUES (1, 'Tang SAN ',' Male ', 'Chairman ', '2000-01-01 21:10:26', 6); INSERT INTO 'employee' VALUES (2, '小 dance ', '女', 'vice chairman ', '1900-01-01 21:11:22', 6); INSERT INTO 'employee' VALUES (3, '50 ',' 50 ', 1); INSERT INTO 'employee' VALUES (4, 'xx ',' xx ', 'xx ', 1); INSERT INTO 'employee' VALUES (5, 'Oscar ',' male ', 'sales manager ', '1999-01-02 21:16:58', 2); INSERT INTO 'employee' VALUES (6, 'employee ',' sales executive ', '2001-03-20 21:17:36', 2); INSERT INTO 'employee' VALUES (7, 'gao ',' male ', '01-01 21:18:30', 3); INSERT INTO' employee 'VALUES (7,' Gao ', 'male ',' 01-01 21:18:30', 3); INSERT INTO 'employee' VALUES (8, 'Titan ',' male ', 'Manager ', '1990-06-06 21:18:57', 3); INSERT INTO 'employee' VALUES (9, '03 ',' 03 ', '03 ', 4); INSERT INTO 'employee' VALUES (10, 'xx ',' xx ', 'xx ',' xx ', 4); INSERT INTO 'employee' VALUES (11, 'xx ',' xx ', 'xx ',' xx ', 5); INSERT INTO 'employee' VALUES (12, 'xx ',' xx ', 'xx ',' xx ', 'xx ', 5); INSERT INTO 'employee' VALUES (13, '2000-12-01 ', '2000-12-01 21:23:19', 2); INSERT INTO 'employee' VALUES (14, '2003-01-30 21:24:25', 2); INSERT INTO 'employee' VALUES (14, '2003-01-30 21:24:25', 2); INSERT INTO 'employee' VALUES (15, '张伟', '男', 'vice chairman ', '1970-01-01 21:25:52', 6) INSERT INTO 'employee' VALUES (16, 'zhang ',' male ', 'engineer ', '1999-11-11 21:27:08', 1); SET FOREIGN_KEY_CHECKS = 1;Copy the code

Departments (the department)

/* Navicat Premium Data Transfer Source Server : localhost_3306 Source Server Type : MySQL Source Server Version : 50731 Source Host : localhost:3306 Source Schema : demo Target Server Type : MySQL Target Server Version : 50731 File Encoding : 65001 Date: 20/03/2021 21:34:13 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for department -- ---------------------------- DROP TABLE IF EXISTS `department`; CREATE TABLE `department` ( `depid` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, `depname` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, PRIMARY KEY (`depid`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of department -- ---------------------------- INSERT INTO `department` VALUES (1, 'Personnel '); INSERT INTO 'department' VALUES (2, 'sales '); INSERT INTO 'department' VALUES (3, 'department '); INSERT INTO 'department' VALUES (4, 'department '); INSERT INTO 'department' VALUES (5, 'department '); INSERT INTO 'department' VALUES (6, 'admin '); SET FOREIGN_KEY_CHECKS = 1;Copy the code

Payroll (salary)

/* Navicat Premium Data Transfer Source Server : localhost_3306 Source Server Type : MySQL Source Server Version : 50731 Source Host : localhost:3306 Source Schema : demo Target Server Type : MySQL Target Server Version : 50731 File Encoding : 65001 Date: 20/03/2021 21:34:31 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for salary -- ---------------------------- DROP TABLE IF EXISTS `salary`; CREATE TABLE `salary` ( `empid` int(11) NULL DEFAULT NULL, `basesalary` decimal(10, 2) NULL DEFAULT NULL, `titlesalary` decimal(10, 2) NULL DEFAULT NULL, `deduction` decimal(10, 2) NULL DEFAULT NULL ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of salary -- ---------------------------- INSERT INTO `salary` VALUES (1, 10000.00, 10000.00, 0.00); INSERT INTO 'salary' VALUES (2, 10000.00, 9000.00, 0.00); INSERT INTO 'salary' VALUES (3, 9000.00, 5000.00, 5.00); INSERT INTO 'salary' VALUES (4, 6000.00, 600.00, 0.00); INSERT INTO 'salary' VALUES (5, 8000.00, 5000.00, 16.00); INSERT INTO 'salary' VALUES (6, 6000.00, 600.00, 0.00); INSERT INTO 'salary' VALUES (7, 8000.00, 500.00, 0.00); INSERT INTO 'salary' VALUES (8, 8000.00, 5000.00, 100.00); INSERT INTO 'salary' VALUES (9, 8000.00, 5000.00, 0.00); INSERT INTO 'salary' VALUES (10, 6000.00, 600.00, 600.00); INSERT INTO 'salary' VALUES (11, 8000.00, 5000.00, 0.00); INSERT INTO 'salary' VALUES (12, 5000.00, 5000.00, 0.00); INSERT INTO 'salary' VALUES (13, 5000.00, 3000.00, 25.00); INSERT INTO 'salary' VALUES (14, 5000.00, 2500.00, 0.00); INSERT INTO 'salary' VALUES (15, 10000.00, 9500.00, 0.00); INSERT INTO 'salary' VALUES (16, 7000.00, 4000.00, 100.00); SET FOREIGN_KEY_CHECKS = 1;Copy the code

2.2 Refer to SQL Statements

1. Modify the table structure and add the department introduction field to the department table

alter table department add introduction varchar(100);
Copy the code

2. Change Li Si’s professional title to “Engineer”, and change her basic salary to 2000 and post salary to 700

Update employee set title=' id 'where id =' id'; Update salary set basesalary=2000,titlesalary=700 WHERE empid = (select empID from employee where name=" 四");Copy the code

3. Delete the records of the personnel department

(Delete the information of the department and all the people in the department, including the records in the payroll table, employee table and department table)

delete from salary where empid in ( select empid from employee where depid = ( select depid from department where Depname =" depname ");Copy the code

Delete from employee where depid = (select depid from department where depname=" depname ");Copy the code

Delete from department where depname = "depname ";Copy the code

4. Find out the employee number of each employee, real salary, payable salary

(Personal understanding: Actual salary = Basesalary + Titlesalary – Deduction, salary payable = Basesalary + Titlesalary)

Select a.emid as' employee id ', (a.basesalary+ a.telesalary - a.eduction) as' actual salary ', (A.besesalary + A.tate salary) as' payable 'from a;Copy the code

5. Query employee records whose surname is Zhang and age is younger than 40

(Personal understanding: query all records of eligible employees in the three tables)

Select the records that meet the conditions

Select * from employee where name like 'zhang %' and year(curdate())-year(birthday)>40;Copy the code

Join other tables to get all the information

select employee.*,salary.basesalary,salary.titlesalary,salary.deduction,department.depname,department.introduction from employee join salary on salary.empid = employee.empid join department on department.depid = employee.depid where Employee. Name like 'zhang %' and year(curdate())-year(employee. Birthday)>40;Copy the code

6. Query employee id, name, title, department name, real pay

select employee.empid,employee.name,employee.title,department.depname,(salary.basesalary+salary.titlesalary-salary.deduction) As' salary 'from employee join department on employee.depid= department.depID join salary on employee.empID =salary. Empid order by employee.empid;Copy the code

7. Query the name and salary of employees in the sales department

select employee.name,employee.title,salary.basesalary,salary.titlesalary,salary.deduction from employee join salary on Employee. Empid =salary.empid WHERE employee. Depid in (select depID from department where depname=' sales ');Copy the code

8. Count the number of people with various professional titles

select title,count(*) from  employee group by title;
Copy the code

9. Make statistics of the department name, the sum of real wages and the average salary of each department

This query is complex and can be broken down step by step. Please refer to the implementation method of # 17 of this blog, the gate <(￣) ￣)↗[GO!]. @& goodbye firefly &【05- database _ database advanced _SQL advanced exercises (part of exercises) 】【第17题】

1. Obtain a new table containing the employee ID, actual salary, and department name

select newsalary.*,department.depname
from(
	select salary.empid,(salary.basesalary+salary.titlesalary-salary.deduction) as actualsalary from salary
) as newsalary
join employee on newsalary.empid=employee.empid
join department on employee.depid=department.depid;
Copy the code

2, on the query results, use the aggregate function, and group by depName

Select temp. Depname,sum(actualsalary) as' salary ', AVG (actualsalary) as' salary 'from (select newsalary.*,department.depname  from( select salary.empid,(salary.basesalary+salary.titlesalary-salary.deduction) as actualsalary from salary ) as newsalary join employee on newsalary.empid=employee.empid join department on employee.depid=department.depid ) as temp group by temp.depname;Copy the code

10. Query the name of the employee whose base salary is higher than that of all employees in the sales department

1. Got the highest basic salary in the sales department

select max(salary.basesalary) from salary where salary.empid in ( select employee.empid from employee left join Depid =department.depid where department.depname=' department.depname ')Copy the code

Id of an employee whose base salary is higher than that of all employees in the sales department

select salary.empid from salary where salary.basesalary>( select max(salary.basesalary) from salary where salary.empid in ( select employee.empid from employee left join department on employee.depid=department.depid where Depname =' depname ');Copy the code

Employee ID joins employee table to get employee name

select employee.name from ( select salary.empid from salary where salary.basesalary>( select max(salary.basesalary) from  salary where salary.empid in ( select employee.empid from employee left join department on Depid =department.depid where department.depname=' Sales department ')))as temp left join employee on employee.empid=temp.empid;Copy the code

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

Task card _05- Database _-MySql advanced tasks

First, the financial management system – database module

1. Task Overview

2. Reference code

2.1 Data Preparation

2.2 Refer to SQL Statements

1. Modify the table structure and add the department introduction field to the department table

2. Change Li Si’s professional title to “Engineer”, and change her basic salary to 2000 and post salary to 700

3. Delete the records of the personnel department

4. Find out the employee number of each employee, real salary, payable salary

5. Query employee records whose surname is Zhang and age is younger than 40

6. Query employee id, name, title, department name, real pay

7. Query the name and salary of employees in the sales department

8. Count the number of people with various professional titles

9. Make statistics of the department name, the sum of real wages and the average salary of each department

10. Query the name of the employee whose base salary is higher than that of all employees in the sales department

Task card _05- Database _-MySql advanced tasks

First, the financial management system – database module

1. Task Overview

2. Reference code

2.1 Data Preparation

2.2 Refer to SQL Statements

1. Modify the table structure and add the department introduction field to the department table

2. Change Li Si’s professional title to “Engineer”, and change her basic salary to 2000 and post salary to 700

3. Delete the records of the personnel department

4. Find out the employee number of each employee, real salary, payable salary

5. Query employee records whose surname is Zhang and age is younger than 40

6. Query employee id, name, title, department name, real pay

7. Query the name and salary of employees in the sales department

8. Count the number of people with various professional titles

9. Make statistics of the department name, the sum of real wages and the average salary of each department

10. Query the name of the employee whose base salary is higher than that of all employees in the sales department

Related Posts

How can companies increase the value of data

Spring IOC detailed solution and Bean life cycle detailed process, read the direct blow the interviewer!

Talk about ILeaseService from RocketMQ-Streams