DSPython Logo DSPython

Basic SQL Queries

Learn to fetch data with SELECT, WHERE, and ORDER BY.

SQL Beginner 45 min

💡 Introduction: The Core Query

SQL is the language of databases. The fundamental goal of most SQL queries is to **read** data. The process starts with asking the database: *From which table* should I fetch *which columns*?

The **SELECT** and **FROM** statements are the bedrock of any query, defining the scope and structure of the data you want to retrieve (the **result-set**).

[Image of basic sql query flow chart]

📋 Topic 1: The Basics: SELECT and FROM

The basic query structure is simple: we first specify the **columns** (SELECT) and then the **table** (FROM).

📝 Key Syntax Rules:

  • SELECT *: The asterisk (`*`) is a wildcard that retrieves **all columns** in the table. Use sparingly in large tables.
  • SELECT col1, col2: Retrieves only the specified columns, which is better for performance.

💻 Example: Selecting Columns

-- Retrieve all columns (all data)
SELECT * FROM Employees;

-- Retrieve only Name and Email columns
SELECT Name, Email FROM Employees;

🔎 Topic 2: Filtering Rows: WHERE Clause

The **WHERE** clause is used to filter individual rows based on a specific condition. It limits the size of the result set before any other operations occur.

⚖️ Logical Operators (for combining conditions):

  • AND / OR: Combine multiple conditions.
  • IN: Checks if a value is present in a specified list (e.g., City IN ('NY', 'LA')).
  • BETWEEN: Checks if a value is within a given range (inclusive).

💻 Example: Filtering with IN and BETWEEN

-- Employees in 'IT' OR 'HR' department
SELECT * FROM Employees WHERE Department IN ('IT', 'HR');

-- Products with price between 50 and 100
SELECT * FROM Products WHERE Price BETWEEN 50 AND 100;

⬇️ Topic 3: Sorting (ORDER BY) & Pattern Matching (LIKE)

ORDER BY (Sorting Results):

The **ORDER BY** clause sorts the final result set. Use **DESC** for the highest value first, and **ASC** (default) for the lowest value first.

You can sort by multiple columns, with the first column being the primary sort key.

💻 Example: Multi-Column Sort

-- Sort by Category (A-Z), then by Price (Highest first)
SELECT * FROM Products
ORDER BY Category ASC, Price DESC;

🔎 Topic 4: DISTINCT, LIMIT, and LIKE

LIKE and Wildcards:

The **LIKE** operator searches for patterns in text columns using **Wildcards**:

Wildcard Meanings:

  • **% (Percent):** Matches any sequence of zero or more characters (e.g., `LIKE 'A%'` finds all names starting with 'A').
  • **_ (Underscore):** Matches **exactly one single character** (e.g., `LIKE 'T_m'` finds 'Tom', 'Tim', 'Tam').

💻 Example: Pattern Matching

-- Find all products with 'chair' anywhere in the name
SELECT * FROM Products WHERE ProductName LIKE '%chair%';

DISTINCT and LIMIT:

**DISTINCT** is used in the `SELECT` line to ensure only unique values are returned. **LIMIT** restricts the total number of rows returned by the query.

-- Get the 10 most expensive items
SELECT * FROM Items ORDER BY Price DESC LIMIT 10;

-- Get the 5th to 10th row (Pagination)
SELECT * FROM Orders ORDER BY Date ASC LIMIT 5 OFFSET 5;

📚 Module Summary

  • SELECT/FROM: What data, from where.
  • WHERE: Filters rows (AND, OR, IN, BETWEEN).
  • ORDER BY: Sorts the result (ASC/DESC).
  • LIMIT/OFFSET: Used for pagination (viewing specific pages of results).
  • DISTINCT/LIKE: Retrieves unique values and searches for patterns.

🤔 Interview Q&A

Tap on the questions below to reveal the answers.

The = operator is used for exact string matching (City = 'New York'). The LIKE operator is used for pattern matching using wildcards (% or _), allowing for partial matches (City LIKE 'New%').

You should use the IN operator for better readability and performance when checking a column against a list of values. Instead of (City = 'A' OR City = 'B'), use City IN ('A', 'B').

The OFFSET clause is used in conjunction with LIMIT for pagination. It tells the query how many initial rows to skip before beginning to count the LIMIT amount (e.g., LIMIT 10 OFFSET 20 shows rows 21 through 30).

The database filters the data using WHERE first (to reduce the number of rows), and then sorts the filtered results using ORDER BY last. This is efficient as it sorts fewer rows.