Understand sql DISTINCT, GROUP BY, COUNT, SUM, AVG, MIN, MAX functions.

aggregate_functions_example.sql

CREATE TABLE IF NOT EXISTS employee (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    birthdate DATE,
    hire_date DATE,
    job_title VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);
INSERT IGNORE INTO employee (employee_id, first_name, last_name, birthdate, hire_date, job_title, department, salary)
VALUES
    (1, 'John', 'Doe', '1990-05-15', '2015-03-10', 'Software Developer', 'IT', 75000.00),
    (2, 'Jane', 'Smith', '1985-08-20', '2018-06-25', 'Web Designer', 'Design', 60000.00),
    (3, 'Michael', 'Johnson', '1982-12-03', '2010-09-15', 'Project Manager', 'Management', 85000.00),
        (4, 'Emily', 'Brown', '1993-04-28', '2017-02-18', 'Database Administrator', 'IT', 70000.00),
    (5, 'David', 'Lee', '1995-07-10', '2019-11-30', 'Frontend Developer', 'IT', 72000.00),
    (6, 'Michael', 'Gorby', '1951-01-30', '2011-09-16', 'Chairman', 'Management', 12345.00);

SELECT COUNT(employee_id) "Number of Employees" FROM employee;
SELECT SUM(salary) "Salary Fund" FROM employee;
SELECT AVG(salary) "Average Salary" FROM employee;
SELECT MIN(salary) as "MIN Salary" FROM employee;
SELECT MAX(salary) as "MAX Salary" FROM employee;
SELECT department, COUNT(employee_id) "Number of per Department Employees" FROM employee GROUP BY department;
SELECT department, SUM(salary) "By Department Salary" FROM employee GROUP BY department;
SELECT department, AVG(salary) "By Department AVG Salary" FROM employee GROUP BY department;
SELECT department, MIN(salary) "By Department MIN Salary" FROM employee GROUP BY department;
SELECT department, MAX(salary) "By Department MAX Salary" FROM employee GROUP BY department;
SELECT first_name, COUNT(1) FROM employee GROUP BY first_name;
SELECT DISTINCT * FROM employee;
SELECT DISTINCT first_name "First Name" FROM employee;