EmployeesAnalysis

-- Querying in MySQL with a database on employee information to gain insights for a company. 


Select *
FROM Departments; 

--- Select all people from the “employees” table whose first name is “Elvis”.
SELECT *
FROM employees
WHERE first_name = 'Elvis';

--- Retrieve a list with all female employees whose first name is Kellie. 

SELECT *
FROM employees
WHERE first_name = 'Kellie' 
AND gender = 'F';


--- Retrieve a list with all employees whose first name is either Kellie or Aruna.

SELECT *
FROM employees
WHERE first_name = 'Kellie' OR first_name = 'Aruna';


--- Retrieve a list with all female employees whose first name is either Kellie or Aruna.

SELECT *
FROM employees
WHERE gender = 'F' and (first_name = 'Kellie' OR first_name = 'Aruna');

--- Select all individuals from the “employees” table, whose first name is either “Denis”, or “Elvis”.

SELECT *
FROM employees
WHERE first_name IN ('Dennis', 'Elvis');


--- Extract all records from the ‘employees’ table, aside from those with employees named John, Mark, or Jacob.

SELECT *
FROM employees
WHERE first_name NOT IN ('John', 'Mark', 'Jacobl');

--- Select the data about all individuals, whose first name starts with “Mark”;

SELECT *
FROM employees 
WHERE first_name LIKE 'Mark%';

--- Retrieve a list with all employees who have been hired in the year 2000.

SELECT *
FROM employees
WHERE hire_date LIKE '%2000%';

--- Retrieve a list with all employees whose employee number is written with 5 characters, and starts with “1000”. 

SELECT * 
FROM employees 
WHERE emp_no LIKE '1000_';

--- Select all the information from the “salaries” table regarding contracts from 66,000 to 70,000 dollars per year.

SELECT *
FROM salaries
WHERE salary BETWEEN '66,000' AND '70,000';

--- Retrieve a list with all individuals whose employee number is not between ‘10004’ and ‘10012’.

SELECT *
FROM employees
WHERE emp_no NOT BETWEEN '10004' AND '10012';


--- Select the names of all departments with numbers between ‘d003’ and ‘d006’.

SELECT dept_name
FROM departments 
WHERE dept_no BETWEEN 'd003' AND 'd006';

--- Select the names of all departments whose department number value is not null.

SELECT dept_name
FROM departments
WHERE dept_no IS NOT NULL;

--- Retrieve a list with data about all female employees who were hired in the year 2000 or after.

SELECT *
FROM employees
WHERE gender = 'f' 
AND hire_date >= '2000-01-01';

--- Extract a list with all employees’ salaries higher than $150,000 per annum.

SELECT *
FROM salaries
WHERE salary > 150000;

--- Obtain a list with all different “hire dates” from the “employees” table.

SELECT DISTINCT hire_date
FROM employees;

--- How many annual contracts with a value higher than or equal to $100,000 have been registered in the salaries table?

SELECT COUNT(salary)
FROM salaries
WHERE salary >= 100000;

--- How many managers do we have in the “employees” database?

SELECT COUNT(*)
FROM dept_manager;

--- Select all data from the “employees” table, ordering it by “hire date” in descending order.

SELECT *
FROM employees 
ORDER BY hire_date DESC;


--- Write a query that obtains two columns. The first column must contain annual salaries higher than 80,000 dollars.
--- The second column, renamed to “emps_with_same_salary”, must show the number of employees contracted to that salary.
--- Lastly, sort the output by the first column.

SELECT salary, COUNT(emp_no) as emps_with_same_salary
FROM salaries
WHERE salary > 80000
GROUP BY salary
ORDER BY salary

--- Select all employees whose average salary is higher than $120,000 per annum.

SELECT emp_no, AVG(salary)
FROM salaries
GROUP BY emp_no
HAVING AVG(salary) > 120000
ORDER BY emp_no;


--- Select the employee numbers of all individuals who have signed more than 1 contract after the 1st of January 2000.

SELECT emp_no
FROM dept_emp
WHERE from_date > '2000-01-01'
GROUP BY emp_no
HAVING COUNT(from_date) > 1
ORDER BY emp_no;

--- Select the first 100 rows from the ‘dept_emp’ table.

SELECT *
FROM dept_emp
LIMIT 100;

--- Insert information about employee number 999903. State that he/she is a “Senior Engineer”, who has started working in this position on October 1st, 1997.

INSERT INTO titles 
(emp_no,
title,
from_date) VALUES
( 999903,
'Senior Engineer',
 1997-10-01);
 
 
 --- Insert information about the individual with employee number 999903 into the “dept_emp” table. He/She is working for department number 5, and has started work on  October 1st, 1997; her/his contract is for an indefinite period of time.
 
SELECT *
FROM dept_emp
ORDER BY emp_no DESC
LIMIT 10;

INSERT INTO dept_emp
( emp_no,
dept_no,
from_date,
to_date) VALUES 
( 999903,
'd005',
'1997-10-01',
'9999-01-10');


--- Create a new department called “Business Analysis”. Register it under number ‘d010’.

INSERT INTO departments 
VALUES
( 'd010',
'Business Analysis');


--- Change the “Business Analysis” department name to “Data Analysis”.

UPDATE departments
SET dept_name = 'Data Analysis'
WHERE dept_no = 'd010';


--- Remove the department number 10 record from the “departments” table.

DELETE FROM departments 
WHERE dept_no = 'd010';


--- How many departments are there in the “employees” database?

SELECT COUNT(DISTINCT dept_no)
FROM dept_emp;


--- What is the total amount of money spent on salaries for all contracts starting after the 1st of January 1997?

SELECT SUM(salary)
FROM salaries
WHERE from_date > '1997-01-01';


--- Which is the lowest & highest employee number in the database?

SELECT MIN(emp_no)
FROM employees; 

SELECT MAX(emp_no)
FROM employees;


--- What is the average annual salary paid to employees who started after the 1st of January 1997?

SELECT ROUND(AVG(salary),2) as 'Average_Salary'
FROM salaries
WHERE from_date > '1997-01-01';


--- Creating table dup

DROP TABLE IF EXISTS departments_dup;

CREATE TABLE departments_dup
(dept_no CHAR(4) NULL,
dept_name VARCHAR(40) NULL);
 

INSERT INTO departments_dup
(dept_no,
dept_name)
SELECT *
FROM departments;

 

INSERT INTO departments_dup 
(dept_name)
VALUES
('Public Relations');

 

DELETE FROM departments_dup
WHERE dept_no = 'd002'; 

   

INSERT INTO departments_dup(dept_no) VALUES ('d010'), ('d011');

SELECT *
FROM departments_dup;


DROP TABLE IF EXISTS dept_manager_dup;

CREATE TABLE dept_manager_dup (

  emp_no int(11) NOT NULL,

  dept_no char(4) NULL,

  from_date date NOT NULL,

  to_date date NULL

  );

 

INSERT INTO dept_manager_dup

select * from dept_manager;

 

INSERT INTO dept_manager_dup (emp_no, from_date)

VALUES                (999904, '2017-01-01'),

                                (999905, '2017-01-01'),

                               (999906, '2017-01-01'),

                               (999907, '2017-01-01');

 

DELETE FROM dept_manager_dup

WHERE

    dept_no = 'd001';