Mahesh Peiris

Project Overview

Basic MySQL query on employee dataset. Please find more information about employee dataset on here.

Structure of the MySQL employee dataset.

SELECT * FROM TableName can use to select the all the data from any table.

  SELECT * FROM employees;   # Select all from employees table

  SELECT * FROM salaries;    # Select all from the salaries table

We can use “concat” to join two columns in the tables.

 # Join two columns in the table

 SELECT	first_name, last_name, concat(first_name, ' ', last_name) as 'Name' FROM employees;

Let’s group employee’s salaries by each year.

 # Select total salaries for each year

 SELECT 
     DATE_FORMAT(to_date, '%Y') AS Year,
     SUM(salary) AS Total_Salary
 FROM
     salaries
 GROUP BY Year
 ORDER BY Year;

We can add average salary to the results by adding “AVG(salary) AS Average_Salary”

 # Select total salaries for each year

 SELECT 
     DATE_FORMAT(to_date, '%Y') AS Year,
     SUM(salary) AS Total_Salary,
     AVG(salary) AS Average_Salary
 FROM
     salaries
 GROUP BY Year
 ORDER BY Year;

We can join employees and salaries tables by using the “JOIN” statement.


  SELECT * FROM employees AS emp
  JOIN salaries AS sal 
  ON emp.emp_no = sal.emp_no;

Using two join statements.

 # Two join statement
 SELECT 
 dept_name, last_name, emp.emp_no
 FROM
 employees as emp
 JOIN
 dept_manager ON emp.emp_no = dept_manager.emp_no
 JOIN
 departments
 ON dept_manager.dept_no=departments.dept_no ORDER BY dept_name,last_name;

Let’s select the total salaries for each year only for the male employees.

 SELECT  gender, SUM(salary) AS Total_Salary, DATE_FORMAT(to_date, '%Y') AS Year 
 FROM employees AS emp
 JOIN salaries AS sal 
 ON emp.emp_no = sal.emp_no
 WHERE gender = 'M'
 GROUP BY Year
 ORDER BY Year;

Subquery is a query nested within another query such as SELECT, INSERT, UPDATE or DELETE. In addition, a subquery can be nested inside another subquery.

 # Subquery 
 SELECT DISTINCT emp_no FROM dept_manager;

 # Distinct department manager selection is using as a subquery in below.
 SELECT 
    date_format(from_date,'%Y') AS year,
    sum(salary) as total_salary,
    min(salary) as min_salary,
    max(salary) as max_salary,
    avg(salary) as avg_salary
 FROM
    salaries
 WHERE
    emp_no NOT IN (SELECT DISTINCT
            emp_no
        FROM
            dept_manager) GROUP BY year;

To combine conditions, we can use ‘AND’

 #Current employees to_date = ‘9999-01-01’.	
 SELECT 
  dept_name, first_name, last_name, title
 FROM 
  employees AS emp,
  dept_emp AS de,
  departments AS dept,
  titles AS tl
 WHERE 
  emp.emp_no=de.emp_no AND
  de.dept_no=dept.dept_no AND
  emp.emp_no=tl.emp_no AND
  de.to_date='9999-01-01'
  AND tl.to_date = '9999-01-01'
  ORDER BY dept.dept_name, last_name;

Difference between natural join and inner join is that natural join will avoid the repeated column and not required to use ‘ON’.

 SELECT  *  FROM 
  employees AS emp
 INNER JOIN
  dept_emp as de ON emp.emp_no=de.emp_no
 INNER JOIN departments as dept 
  ON de.dept_no=dept.dept_no
 INNER JOIN 
  titles as tl ON emp.emp_no=tl.emp_no
 WHERE de.to_date='9999-01-01'
  AND tl.to_date = '9999-01-01'
 ORDER BY dept.dept_name, last_name;

 # ON is not required
 SELECT  *  FROM 
  employees AS emp
 NATURAL JOIN
  dept_emp as de
 NATURAL JOIN
  departments as dept
 NATURAL JOIN
  titles as tl
 WHERE de.to_date='9999-01-01'
  AND tl.to_date = '9999-01-01'
 ORDER BY dept.dept_name, last_name;

LEFT JOIN returns all the rows from the left table. Where no matches have been found in the table on the right, NULL is returned.

 # LEFT JOIN
 SELECT * FROM employees AS emp
 LEFT JOIN dept_manager AS dm
 ON emp.emp_no=dm.emp_no;

 # removing NULL rows
 SELECT 
  emp.emp_no, dm.emp_no, first_name , last_name
 FROM employees AS emp
 LEFT JOIN dept_manager AS dm
  ON emp.emp_no=dm.emp_no
 WHERE dm.emp_no IS  NOT NULL;

Use of NOT EQUAL to select former employees.

 # Current employees to_date = ‘9999-01-01’.
 SELECT * 
 FROM employees AS emp
 LEFT JOIN
  titles AS tl
  ON emp.emp_no=tl.emp_no
 WHERE tl.to_date !='9999-01-01';

Same results from different queries.

 
 SELECT * FROM employees WHERE emp_no IN (SELECT emp_no FROM titles WHERE to_date='9999-01-01');

 SELECT emp.emp_no, birth_date, first_name , last_name, gender, hire_date
 FROM employees AS emp
 LEFT JOIN
  titles AS tl
 ON emp.emp_no=tl.emp_no AND tl.to_date='9999-01-01'
 WHERE tl.emp_no IS NOT NULL;

Few charts using Power BI for employee dataset (can directly query the MySQL employee database using Power BI inbuild MySQL connection.) Power BI file can be download here.

Complete MySQL file can be download here.

Case Study: MySQL employees dataset

Project Overview

Want me to help with your project?