Home / Tech / Basic SQL

Tech

Basic SQL cheat sheet

Basic SQL cheat sheet. Explore our ultimate quick reference for Basic SQL.

This cheat sheet covers the essential concepts of SQL (Structured Query Language), including how to query, filter, and aggregate data in relational databases. It focuses on commonly used commands such as SELECT, WHERE, and aggregate functions with syntax examples.

SQL Overview

What is SQL?

  • SQL (Structured Query Language) is used to manage and manipulate relational databases.
  • It supports querying, inserting, updating, and deleting data.
  • Common SQL dialects include MySQL, PostgreSQL, SQLite, and SQL Server.

Basic SQL Query Structure

SELECT column1, column2 
FROM table_name 
WHERE condition;

Querying Data

Selecting Data

  • Select All Columns 

    SELECT * FROM table_name;

  • Select Specific Columns 

    SELECT column1, column2 FROM table_name;

  • Distinct Values 

    SELECT DISTINCT column_name FROM table_name;

Filtering Data with WHERE

  • Basic Filtering 

    SELECT * FROM table_name WHERE column_name = 'value';

  • Comparison Operators
    Use =, >, <, >=, <=, and <> for filtering.
    Example: 

    SELECT * FROM table_name WHERE column_name > 3;

  • Range Filtering with BETWEEN 

    SELECT * FROM table_name WHERE column_name BETWEEN value1 AND value2;

  • Pattern Matching with LIKE
    Use % for multiple character wildcards and _ for single characters. 

    SELECT * FROM table_name WHERE column_name LIKE 'P%';

Sorting and Limiting Results

  • Sorting Results 

    SELECT * FROM table_name ORDER BY column_name ASC|DESC;

  • Limiting Rows 

    SELECT * FROM table_name LIMIT number;

Aggregating Data

Aggregate Functions

  • SUM 

    SELECT SUM(column_name) FROM table_name;

  • AVG (Average) 

    SELECT AVG(column_name) FROM table_name;

  • MAX and MIN 

    SELECT MAX(column_name), MIN(column_name) FROM table_name;

  • COUNT 

    SELECT COUNT(column_name) FROM table_name;

Grouping and Filtering Aggregated Results

  • Group By 

    SELECT column1, AGG_FUNC(column2) FROM table_name GROUP BY column1;

  • Filtering Groups with HAVING 

    SELECT column1, COUNT(column2) FROM table_name GROUP BY column1 HAVING COUNT(column2) > value;

Advanced Querying

Joining Tables

  • Inner Join 

    SELECT a.column1, b.column2
FROM table1 a
JOIN table2 b ON a.common_column = b.common_column;

  • Left Join 

    SELECT a.column1, b.column2
FROM table1 a
LEFT JOIN table2 b ON a.common_column = b.common_column;

Handling Null Values

  • Check for NULL Values 

    SELECT * FROM table_name WHERE column_name IS NULL;

  • Check for Non-NULL Values 

    SELECT * FROM table_name WHERE column_name IS NOT NULL;

Tips and Sources

Useful Tips

  • Aliases: Use aliases (AS) to simplify long column or table names.
  • Use LIMIT: Always test queries on a smaller dataset with LIMIT before running on large datasets.
  • Optimize Joins: Ensure proper indexing for tables being joined to improve performance.

Sources

LISTEN