×
Post thumbnail

Beginner to Advanced : SQL cheatsheet | Hands-on-guide..

1. SELECT Statement

Meaning: Used to select data from a database.

Example:

SELECT first_name, last_name, age
FROM employees;

2. WHERE Clause

Meaning: Filters records that meet specified conditions.

Example:

SELECT first_name, last_name
FROM employees
WHERE age > 30;

3. GROUP BY + ORDER BY

GROUP BY Meaning: Groups rows that have the same values into summary rows. ORDER BY Meaning: Sorts the result set in ascending or descending order.

Example:

SELECT department, COUNT(*)
FROM employees
GROUP BY department
ORDER BY COUNT(*) DESC;

4. WHERE vs HAVING

  • WHERE: Filters rows before grouping.
  • HAVING: Filters groups after grouping.

Example:

SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

5. LIMIT + AS

  • LIMIT Meaning: Specifies the number of records to return.
  • AS Meaning: Renames a column or table with an alias.

Example:

SELECT first_name AS fname, last_name AS lname
FROM employees
LIMIT 10;
SQL Intermediate Guide

1. JOINs

  • INNER JOIN: Returns records that have matching values in both tables.
  • LEFT JOIN: Returns all records from the left table, and the matched records from the right table.
  • RIGHT JOIN: Returns all records from the right table, and the matched records from the left table.
  • SELF JOIN: A regular join but the table is joined with itself.

Example:

SELECT e1.first_name, e2.manager_name
FROM employees e1
INNER JOIN managers e2 ON e1.manager_id = e2.id;

2. UNION

  • UNION Meaning: Combines the result sets of two or more SELECT statements, removing duplicates.
  • UNION ALL Meaning: Combines the result sets of two or more SELECT statements, including duplicates.

Example:

SELECT first_name FROM employees
UNION
SELECT first_name FROM managers;

3. STRING Functions

  • LEFT(field, length): Extracts a substring from the left.
  • RIGHT(field, length): Extracts a substring from the right.
  • UPPER(field): Converts all characters to uppercase.
  • LOWER(field): Converts all characters to lowercase.
  • SUBSTRING(field, start, length): Extracts a substring starting at a specified position.
  • REPLACE(field, old, new): Replaces occurrences of a specified substring.
  • LOCATE(substring, field): Returns the position of the first occurrence of a substring.
  • CONCAT(str1, str2): Concatenates two or more strings.

Example:

SELECT CONCAT(UPPER(first_name), ' ', LOWER(last_name)) AS full_name
FROM employees;

4. CASE Statement

Meaning: Provides conditional logic in SQL queries.

Example:

SELECT first_name,
CASE
    WHEN gender = 'M' THEN 'Mr.'
    WHEN gender = 'F' THEN 'Ms.'
    ELSE 'Mx.'
END AS title
FROM employees;

5. Subqueries

Meaning: A query nested within another query. Example:

SELECT first_name, last_name
FROM employees
WHERE department_id = (SELECT id FROM departments WHERE name = 'Sales');

6. SQL Aggregate Functions

  • Meaning: Functions like SUM, AVG, MIN, MAX, COUNT that operate on sets of rows to return a single value.
  • Example:
    SELECT AVG(salary) AS avg_salary
    FROM employees;
    

7. Window Functions

  • OVER(): Defines a window for the function.
  • ROW_NUMBER(): Assigns a unique number to rows.
  • RANK(): Assigns a rank to rows, allowing for gaps.
  • DENSE_RANK(): Assigns a rank to rows without gaps.
  • Example:

    SELECT first_name, last_name, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num
    FROM employees;
    
Advanced SQL Guide

1. Common Table Expressions (CTE)

Meaning: Provides a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement.

Example:

WITH sales_department AS (
    SELECT * FROM employees WHERE department = 'Sales'
)
SELECT * FROM sales_department;

2. CREATE TEMPORARY TABLE

Meaning: Creates a temporary table that is deleted after the session ends.

Example:

CREATE TEMPORARY TABLE temp_employees AS
SELECT * FROM employees WHERE department = 'Sales';

3. CREATE PROCEDURE

Meaning: Creates a stored procedure, which is a prepared SQL code that you can save and reuse.

Example:

DELIMITER $$
CREATE PROCEDURE GetEmployeeByDepartment(IN dept_name VARCHAR(100))
BEGIN
    SELECT * FROM employees WHERE department = dept_name;
END $$
DELIMITER ;

4. CREATE TRIGGER

Meaning: A set of instructions that automatically execute in response to certain events on a particular table or view.

Example:

DELIMITER $$
CREATE TRIGGER update_timestamp BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
    SET NEW.updated_at = NOW();
END $$
DELIMITER ;

5. CREATE EVENT

Meaning: Schedules a command or set of commands to run at specified intervals.

Example:

DELIMITER $$
CREATE EVENT cleanup_event
ON SCHEDULE EVERY 1 DAY
DO
BEGIN
    DELETE FROM logs WHERE created_at < NOW() - INTERVAL 30 DAY;
END $$
DELIMITER ;

6. View Events

Meaning: Displays the current settings for scheduled events.

Example:

SHOW VARIABLES LIKE 'event_scheduler';
Additional SQL Concepts

Indexes

  • Meaning: Structures that improve the speed of data retrieval operations on a database table.
  • Example:
    CREATE INDEX idx_lastname ON employees(last_name);
    

Transactions

  • Meaning: A set of SQL commands that are executed as a single unit.
  • Example:
    START TRANSACTION;
    UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
    COMMIT;
    

Views

  • Meaning: Virtual tables that are based on the result-set of a SELECT statement.
  • Example:
    CREATE VIEW employee_view AS
    SELECT first_name, last_name, department
    FROM employees
    WHERE department = 'IT';
    

Constraints

  • Meaning: Rules enforced on data columns to ensure data integrity.
  • Example:
    ALTER TABLE employees
    ADD CONSTRAINT pk_employee_id PRIMARY KEY (employee_id);
    

SQL Injection

  • Meaning: A code injection technique that exploits vulnerabilities in a database application's software.
  • Example:
    SELECT * FROM users WHERE username = 'admin' AND password = 'password123';
    

Security Considerations

  • Meaning: Best practices for securing SQL databases and preventing unauthorized access.
  • Example:
    GRANT SELECT, INSERT, UPDATE, DELETE ON employees TO 'user'@'localhost' IDENTIFIED BY 'password';
    

Advanced Query Optimization Techniques

  • Meaning: Strategies to improve SQL query performance.
  • Example:
    EXPLAIN SELECT * FROM employees WHERE department = 'IT';
    

These additions 🧸 will provide a more comprehensive overview of SQL concepts for beginners 📚📚,


Additional Advanced SQL Concepts

Stored Procedures and Functions

  • Meaning: Precompiled SQL statements that can be stored and executed in the database.
  • Example (Stored Procedure):
    DELIMITER $$
    CREATE PROCEDURE sp_get_employee(IN emp_id INT)
    BEGIN
        SELECT * FROM employees WHERE employee_id = emp_id;
    END $$
    DELIMITER ;
    
  • Example (Function):
    CREATE FUNCTION get_employee_name(emp_id INT) RETURNS VARCHAR(100)
    BEGIN
        DECLARE emp_name VARCHAR(100);
        SELECT CONCAT(first_name, ' ', last_name) INTO emp_name FROM employees WHERE employee_id = emp_id;
        RETURN emp_name;
    END;
    

Database Normalization

  • Meaning: Process of organizing data to minimize redundancy and dependency.
  • Example:
    CREATE TABLE departments (
        department_id INT PRIMARY KEY,
        department_name VARCHAR(100)
    );
    
    CREATE TABLE employees (
        employee_id INT PRIMARY KEY,
        first_name VARCHAR(50),
        last_name VARCHAR(50),
        department_id INT,
        FOREIGN KEY (department_id) REFERENCES departments(department_id)
    );
    

Full-Text Search

  • Meaning: Technique for searching text data efficiently.
  • Example:
    SELECT * FROM articles
    WHERE MATCH (title, content) AGAINST ('MySQL' IN NATURAL LANGUAGE MODE);
    

JSON Support

  • Meaning: Ability to store and query JSON data.
  • Example:
    CREATE TABLE users (
        user_id INT PRIMARY KEY,
        info JSON
    );
    
    INSERT INTO users VALUES (1, '{"name": "John", "age": 30}');
    
    SELECT info->>'$.name' AS name FROM users;
    

Data Warehousing Concepts (OLAP)

  • Meaning: Techniques for analyzing data to support business decisions.
  • Example:
    SELECT date_trunc('month', order_date) AS month,
           SUM(total_amount) AS monthly_sales
    FROM orders
    GROUP BY month
    ORDER BY month;
    

Advanced Window Functions

  • Meaning: More complex uses of window functions for analytical queries.
  • Example:
    SELECT order_id, order_date, total_amount,
           SUM(total_amount) OVER (PARTITION BY EXTRACT(YEAR FROM order_date)) AS yearly_sales
    FROM orders;
    

Database Sharding

  • Meaning: Horizontal partitioning of data across multiple databases to improve scalability.
  • Example:
    CREATE TABLE transactions_2019 (
        transaction_id INT PRIMARY KEY,
        transaction_date DATE,
        amount DECIMAL(10, 2)
    );
    
    CREATE TABLE transactions_2020 (
        transaction_id INT PRIMARY KEY,
        transaction_date DATE,
        amount DECIMAL(10, 2)
    );
    

Optimization and Performance Tuning

  • Meaning: Techniques to improve query performance and database efficiency.
  • Example:
    • Indexing strategies, query optimization, database configuration tuning.

Cloud Database Services

  • Meaning: Utilizing managed database services like AWS RDS, Google Cloud SQL, Azure SQL Database.
  • Example:
    • Setting up and managing databases in cloud environments.

Advanced Security Features

  • Meaning: Techniques to secure databases, including encryption, role-based access control (RBAC), and audit logging.
  • Example:
    • Implementing SSL/TLS connections, and database encryption at rest.

These topics cover more specialized areas of SQL usage and administration 🎫🎟️, providing a broader understanding of database management  💻💻 beyond basic and intermediate levels.

Incorporating these into your guide 🪜🪜 will give beginners a solid foundation 🚂🚂 in advanced SQL concepts and practices.


Although, It seems scary to keep this stuff in your mind. But you should know that practice can make you do this easily, so practice more 🥷🏻🥷🏻.

• • •

Latest Opinions

No Opinion so far...

End.

No internet connection

Trying to reconnect...

Loading...