MySQL Practice-employees Database

Install a database example--employees

Use tool for navicat--(CTRL + Q: Open new Query window

CTRL + R: Runs all statements within the current window)

Reference http://www.cnblogs.com/chenyucong/p/5734800.html

There are 6 associations in the database, and the recorded data is the payroll for all employees in 9 departments of a company, including turnover employees, and the following is a diagram. First sheet: Departments

The department number and department name are recorded for 9 departments.

Table Two: Dept_emp

Department employee data, employee ID and Department ID, in fact time and end time (note: 9999 means still in service)

Count the amount of data on this watch, 331,603 records.

SELECT COUNT (*
 from Dept_emp

And then count the number of Emp_no of the employee ID, 300,024 records

SELECT COUNT (DISTINCT  emp_no)  from Dept_emp

Difference 3W, why? There are duplicate employee IDs, we try to filter out duplicate employee IDs

SELECT Emp_no  from dept_emp GROUP  by Emp_no  having COUNT (EMP_NO) > 1

Okay, now with that nearly 3W employee ID with duplicate values, but we still don't know the reason for the repetition, now we need to filter out the complete information by repeating the employee ID.

SELECT *  from dept_emp WHERE inch (SELECT  emp_no from dept_empGROUP by emp_no  having COUNT (EMP_NO) > 1 )

Now the results are clear, they are the department! So the table record is the true meaning of the data is the time span each employee has to spend in each department.

But at the same time, there is an interesting phenomenon, that is, no employee has been adjusted two times, the following proof

SELECT *  from dept_emp WHERE inch (SELECT  emp_no from dept_empGROUP by emp_no  having COUNT (EMP_NO) > 2 )

Let's see if we can find it in the table we're looking at.

Third sheet: Dept_manger

Similar to the second table structure, each manager in each department has a total of 24 people, each with at least two managers in each department.

SELECT COUNT (DISTINCT as manger_sum from dept_managerGROUP by Dept_no

Table Four: Employees

Employee Information Sheet, Emp_no is the unique key value,

The statistical results are consistent with the data obtained from table Ii.

SELECT COUNT (* from employees;

Fifth sheet: Salaries

Record each employee's salary for each period of time!

Sixth table: Title

Record job titles for each period of time for each employee! However, please note that the period is different from the fifth table, because you will pay a raise in the same position.

MySQL Practice-employees Database