Master MySQL

SQL databases organize structured information into related tables. NoSQL databases support other models, including documents, key-value pairs, graphs, and wide-column data.

Example

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    city VARCHAR(50)
);

SELECT * FROM customers;

MySQL is a relational database management system. You can use MySQL Workbench or its command-line client.

1. Download MySQL Installer for Windows.
2. Install MySQL Server and optionally MySQL Workbench.
3. Configure your root password securely.
4. Start the MySQL service and connect.

Download MySQL Installer

Connect from the terminal

mysql -u root -p

Check the version

SELECT VERSION();

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    department VARCHAR(50)
);

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    price DECIMAL(10,2),
    stock_quantity INT,
    created_date DATE
);

Use DECIMAL for financial values that require exact decimal precision.

Create a database

CREATE DATABASE company_db;
USE company_db;

Create a table and insert sample data

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10,2)
);

INSERT INTO employees
    (employee_id, employee_name, department, salary)
VALUES
    (1, 'Amit', 'Sales', 35000.00),
    (2, 'Priya', 'IT', 50000.00),
    (3, 'Rahul', 'Finance', 42000.00);

Retrieve records

SELECT * FROM employees;

SELECT employee_name, salary
FROM employees
WHERE salary > 40000;

Create — INSERT

INSERT INTO employees
    (employee_id, employee_name, department, salary)
VALUES (4, 'Neha', 'HR', 38000.00);

Read — SELECT

SELECT * FROM employees;

Update — UPDATE

UPDATE employees
SET salary = 55000
WHERE employee_id = 2;

Delete — DELETE

DELETE FROM employees
WHERE employee_id = 4;

Add a column

ALTER TABLE employees
ADD email VARCHAR(150);

Modify a column

ALTER TABLE employees
MODIFY COLUMN employee_name VARCHAR(150);

Rename a column

ALTER TABLE employees
RENAME COLUMN employee_name TO full_name;

Rename a table

RENAME TABLE employees TO staff;

DROP TABLE staff; removes the table and its data. Use it carefully.

WHERE, IN, BETWEEN

SELECT * FROM employees
WHERE salary > 40000;

SELECT * FROM employees
WHERE department IN ('Sales', 'Finance');

SELECT * FROM employees
WHERE salary BETWEEN 30000 AND 50000;

Pattern matching with LIKE

SELECT * FROM employees
WHERE employee_name LIKE 'P%';

% matches zero or more characters; _ matches one character.

Sort and limit

SELECT employee_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;

Check missing values

SELECT * FROM employees WHERE email IS NULL;
SELECT * FROM employees WHERE email IS NOT NULL;

SELECT department, AVG(salary) AS average_salary
FROM employees
WHERE salary > 30000
GROUP BY department
HAVING AVG(salary) > 40000
ORDER BY average_salary DESC
LIMIT 10;

SELECT * FROM employees
WHERE department = 'IT' AND salary >= 40000;

SELECT * FROM employees
WHERE department = 'IT' OR department = 'Finance';

SELECT employee_name, salary * 12 AS annual_salary
FROM employees;

Joins combine related rows from two or more tables.

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

CREATE TABLE employee_departments (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    department_id INT NULL
);

INNER JOIN

SELECT e.employee_name, d.department_name
FROM employee_departments AS e
INNER JOIN departments AS d
    ON e.department_id = d.department_id;

LEFT JOIN

SELECT e.employee_name, d.department_name
FROM employee_departments AS e
LEFT JOIN departments AS d
    ON e.department_id = d.department_id;

RIGHT JOIN

SELECT e.employee_name, d.department_name
FROM employee_departments AS e
RIGHT JOIN departments AS d
    ON e.department_id = d.department_id;

Find departments without employees

SELECT d.department_id, d.department_name
FROM departments AS d
LEFT JOIN employee_departments AS e
    ON d.department_id = e.department_id
WHERE e.employee_id IS NULL;

MySQL does not directly support FULL OUTER JOIN syntax.

SUM and COUNT

SELECT SUM(salary) AS total_salary
FROM employees;

SELECT COUNT(*) AS employee_count
FROM employees;

AVG, MIN, and MAX

SELECT
    AVG(salary) AS average_salary,
    MIN(salary) AS lowest_salary,
    MAX(salary) AS highest_salary
FROM employees;

GROUP BY

SELECT department, COUNT(*) AS employee_count,
       AVG(salary) AS average_salary
FROM employees
GROUP BY department;

HAVING

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) >= 2;

DISTINCT

SELECT DISTINCT department FROM employees;

SELECT COUNT(DISTINCT department) AS department_count
FROM employees;

Find the second-highest distinct salary

SELECT MAX(salary) AS second_highest_salary
FROM employees
WHERE salary < (
    SELECT MAX(salary) FROM employees
);

Find duplicate names

SELECT employee_name, COUNT(*) AS occurrences
FROM employees
GROUP BY employee_name
HAVING COUNT(*) > 1;

Find employees earning above average

SELECT employee_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Highest-paid employee in each department

SELECT employee_id, employee_name, department, salary
FROM (
    SELECT employee_id, employee_name, department, salary,
           DENSE_RANK() OVER (
               PARTITION BY department ORDER BY salary DESC
           ) AS salary_rank
    FROM employees
) AS ranked
WHERE salary_rank = 1;

Use CASE to categorize salaries

SELECT employee_name, salary,
    CASE
        WHEN salary >= 50000 THEN 'High'
        WHEN salary >= 35000 THEN 'Medium'
        ELSE 'Low'
    END AS salary_category
FROM employees;

Use a CTE

WITH department_summary AS (
    SELECT department, AVG(salary) AS average_salary
    FROM employees
    GROUP BY department
)
SELECT *
FROM department_summary
WHERE average_salary > 40000;