30 Frequently Used SQL Queries for Beginners
Structured Query Language, universally known as SQL, stands as one of the most enduring and widely adopted technologies in the entire history of computing. Decades after its initial development, SQL continues to serve as the primary language for interacting with relational databases across industries ranging from healthcare and finance to retail and social media. Every time a user searches for a product on an e-commerce website, a bank processes a transaction, or a hospital retrieves a patient record, SQL queries are almost certainly running in the background, retrieving, filtering, and organizing the data that powers those interactions. For anyone entering the technology field — whether as a software developer, data analyst, business intelligence specialist, or database administrator — a solid command of SQL is not merely useful but genuinely essential.
The good news for beginners is that SQL has a relatively gentle learning curve compared to many programming languages. Its syntax reads almost like natural English, and a relatively small set of core commands covers the vast majority of real-world database tasks. The 30 queries presented in this guide represent the most frequently used SQL operations that beginners will encounter in actual work environments, covering data retrieval, filtering, sorting, aggregation, joining tables, modifying records, and managing database objects. Each query is accompanied by a clear explanation of what it does, why it matters, and how it is written, giving beginners not just the syntax but the understanding required to adapt these patterns to their own specific data needs. Working through this material systematically and practicing each query type against a real database will build the foundation that all further SQL learning depends upon.
SELECT Statement for Data Retrieval
The SELECT statement is the most fundamental SQL command and the starting point for virtually every database interaction a beginner will perform. It retrieves data from one or more tables and returns it as a result set that can be viewed, analyzed, or used as the basis for further operations. In its simplest form, SELECT is followed by a list of column names and a FROM clause that specifies the table containing those columns. To retrieve the first name, last name, and email address of every record in a customers table, for example, the query would be written as SELECT first_name, last_name, email FROM customers. The result set contains exactly those three columns for every row in the table, presented in the order the columns were listed in the query.
When a beginner wants to retrieve every column from a table without specifying each column name individually, the asterisk wildcard character serves as a convenient shorthand. Writing SELECT FROM customers returns all columns for all rows in the customers table, which is useful for quickly exploring the structure and content of an unfamiliar table. However, using SELECT in production queries is generally discouraged because it retrieves more data than may be necessary, consuming additional network bandwidth and memory, and because it makes queries fragile — if someone adds or removes a column from the table, a SELECT query’s behavior changes silently. Specifying column names explicitly is the professional habit that distinguishes thoughtful SQL practitioners from those who rely on convenient shortcuts regardless of context.
WHERE Clause for Filtering Rows
The WHERE clause is the primary mechanism for filtering rows in SQL queries, allowing you to retrieve only the records that meet specific conditions rather than returning every row in a table. A WHERE clause follows the FROM clause and contains one or more conditions that each row must satisfy to be included in the result set. To retrieve only customers from a specific city, for example, you would write SELECT first_name, last_name FROM customers WHERE city = ‘New York’. Only rows where the city column contains exactly the value ‘New York’ will appear in the results — all other rows are excluded regardless of their other column values.
WHERE clauses support a rich variety of comparison operators that give beginners significant expressive power for filtering data. The equality operator (=) checks for exact matches, while the inequality operators (> , <, >=, <=, <>) support numeric and date range comparisons. The BETWEEN operator provides a convenient shorthand for range conditions — WHERE salary BETWEEN 50000 AND 80000 is equivalent to WHERE salary >= 50000 AND salary <= 80000 but reads more clearly. The IN operator allows you to test whether a column value matches any value in a specified list — WHERE department IN (‘Sales’, ‘Marketing’, ‘Support’) returns rows where the department column contains any of those three values. These operators can be combined using AND and OR logical operators to express complex multi-condition filters, and parentheses can be used to control the order in which conditions are evaluated when combining AND and OR in the same WHERE clause.
ORDER BY for Sorting Results
The ORDER BY clause sorts the rows in a query result set according to the values in one or more specified columns. Without an ORDER BY clause, SQL makes no guarantee about the order in which rows are returned — the database engine is free to return them in whatever order is most efficient for its internal processing, which may vary between executions of the same query. When the order of results matters — for displaying records to users, generating reports, or processing records in sequence — ORDER BY is essential. To retrieve customers sorted alphabetically by last name, you would write SELECT first_name, last_name FROM customers ORDER BY last_name. The default sort direction is ascending (A to Z for text, smallest to largest for numbers, earliest to latest for dates), which can be made explicit by appending ASC after the column name.
To sort in the reverse direction, the DESC keyword specifies descending order. A query that retrieves products sorted from most expensive to least expensive would use ORDER BY price DESC. Multiple columns can be specified in an ORDER BY clause, with each subsequent column serving as a tiebreaker when rows have identical values in the preceding columns. ORDER BY last_name ASC, first_name ASC sorts primarily by last name in ascending order, and for customers who share the same last name, sorts secondarily by first name in ascending order. This multi-column sorting is particularly useful for generating formatted reports where records need to be organized hierarchically by multiple attributes simultaneously.
LIMIT and TOP for Row Count
Retrieving every row from a large table is rarely what a beginner actually wants to do, and doing so against tables with millions of rows can produce query results that take a long time to return and are impractical to review. The LIMIT clause in MySQL, PostgreSQL, and SQLite restricts the number of rows returned by a query to a specified maximum. Writing SELECT product_name, price FROM products ORDER BY price DESC LIMIT 10 retrieves only the ten most expensive products rather than the entire products table. This pattern of sorting by a relevant column and then limiting the result is one of the most practically useful query patterns beginners will use regularly in data exploration and reporting contexts.
Microsoft SQL Server and older versions of Oracle use a different syntax for the same purpose. SQL Server uses the TOP keyword placed immediately after SELECT — SELECT TOP 10 product_name, price FROM products ORDER BY price DESC — while Oracle uses a ROWNUM pseudocolumn in a WHERE clause or the FETCH FIRST syntax in more recent versions. The functional result is identical across all these syntactic variations: only the specified number of rows is returned. Beginners working with multiple database platforms benefit from awareness of these syntactic differences, as a query that works perfectly in MySQL will produce a syntax error in SQL Server if it uses LIMIT without adjustment. Most modern databases also support the OFFSET clause alongside LIMIT, which allows pagination by skipping a specified number of rows before beginning to return results — essential for implementing page-by-page data browsing in applications.
DISTINCT Keyword Removes Duplicates
The DISTINCT keyword instructs SQL to return only unique combinations of the specified column values, eliminating duplicate rows from the result set. This is particularly useful when you need to identify what unique values exist in a column without seeing repeated entries for the same value. To find out which unique cities your customers come from, without seeing the same city name repeated hundreds of times because many customers share that city, you would write SELECT DISTINCT city FROM customers ORDER BY city. The result contains each city name exactly once, regardless of how many customer records share that city value.
DISTINCT operates on the combination of all columns listed in the SELECT clause, not just the first one. SELECT DISTINCT city, state FROM customers returns unique combinations of city and state, so if the same city name exists in multiple states — for example, Springfield in Illinois and Springfield in Missouri — both combinations appear in the result set. This behavior is important to understand because beginners sometimes expect DISTINCT to operate on each column independently, which is not how it functions. When you need to count how many unique values exist in a column, combining DISTINCT with the COUNT function — SELECT COUNT(DISTINCT city) FROM customers — produces that count in a single query without needing to first retrieve the distinct values as a separate step.
COUNT Function for Counting Rows
The COUNT function is one of the most fundamental aggregate functions in SQL, returning the number of rows that match specified criteria. In its simplest form, SELECT COUNT() FROM orders returns the total number of rows in the orders table, providing a quick way to understand the size of a dataset. COUNT can also be applied to a specific column — SELECT COUNT(email) FROM customers — which counts only the rows where the email column contains a non-null value, making it useful for assessing data completeness by identifying how many records are missing values in important columns.
COUNT becomes significantly more powerful when combined with WHERE clauses and GROUP BY operations. SELECT COUNT() FROM orders WHERE status = ‘completed’ counts only the completed orders, while SELECT COUNT() FROM orders WHERE order_date >= ‘2024-01-01’ counts orders placed since the beginning of 2024. These filtered counts are among the most common queries in analytical and reporting contexts, answering fundamental business questions like how many transactions occurred in a given period, how many customers are in a particular segment, or how many products are below a specified inventory threshold. Understanding COUNT as a building block that combines with filtering and grouping to produce increasingly specific analytical results is one of the conceptual leaps that transforms beginners from people who can write individual queries into people who can think analytically about data.
SUM and AVG Aggregate Functions
The SUM and AVG functions are aggregate operations that perform arithmetic across sets of rows, producing a single value that summarizes the numeric data in a specified column. SUM adds all the values in a column together — SELECT SUM(order_total) FROM orders calculates the total revenue across all orders in the table, while SELECT SUM(order_total) FROM orders WHERE order_date >= ‘2024-01-01’ calculates revenue for the current year. This type of query is the foundation of virtually every financial report, sales dashboard, and performance summary produced from relational database data.
AVG divides the sum of values by the count of non-null rows, returning the arithmetic mean of a column’s values. SELECT AVG(salary) FROM employees calculates the average salary across the entire workforce, while combining AVG with a WHERE clause filters the average to a specific subset — SELECT AVG(salary) FROM employees WHERE department = ‘Engineering’ returns the average engineering salary for comparison with other departments. A common beginner mistake is forgetting that AVG, like COUNT, ignores null values in its calculation — it divides by the number of rows with non-null values rather than the total row count, which can produce misleading results if a significant proportion of rows contain nulls in the column being averaged. Understanding this behavior and checking for null values before relying on AVG results is a good analytical habit to develop early.
MIN and MAX Value Functions
The MIN and MAX functions return the smallest and largest values in a specified column respectively, providing a quick way to identify the boundaries of a dataset. SELECT MIN(price) FROM products and SELECT MAX(price) FROM products return the cheapest and most expensive product prices in a single query each. These functions work with numeric data, date and time data, and text data — for text, MIN and MAX return the first and last values in alphabetical order respectively, which can be useful for identifying the earliest and latest entries alphabetically or for finding the oldest and most recent dates in a date column.
Combining MIN and MAX in the same query provides both boundaries simultaneously — SELECT MIN(order_date), MAX(order_date) FROM orders returns the date of the earliest and most recent orders in the table, immediately conveying the time span covered by the data. MIN and MAX are also frequently used with GROUP BY to find the extreme values within each group — finding the highest-paid employee in each department, for example, or the most recent transaction for each customer account. One important behavior to understand is that MIN and MAX return a value from the dataset but not the entire row containing that value. To retrieve the full record for the row with the maximum price, for example, a subquery or a different query structure is required — a nuance that beginners frequently encounter and that leads naturally into learning more advanced query patterns.
GROUP BY for Data Aggregation
The GROUP BY clause divides query results into groups based on shared values in one or more columns and applies aggregate functions to each group independently, producing one result row per group rather than one row per data row. This capability is essential for analytical queries that need to summarize data by category, time period, geography, or any other grouping dimension. SELECT department, COUNT() as employee_count FROM employees GROUP BY department returns one row for each unique department value, with each row showing the department name and the number of employees in that department. Without GROUP BY, COUNT() would return a single number representing all employees — with GROUP BY, it returns a separate count for each group.
Multiple columns can be included in a GROUP BY clause to create more granular groupings. SELECT department, job_title, AVG(salary) FROM employees GROUP BY department, job_title produces average salaries for each unique combination of department and job title, which is far more informative than a single overall average. An important rule that beginners must understand is that every column in the SELECT list must either appear in the GROUP BY clause or be wrapped in an aggregate function — including a non-aggregated column that is not in the GROUP BY clause will produce an error in most database systems (or incorrect results in systems that allow it). This rule exists because SQL needs to know how to produce a single output row for each group, and a non-aggregated column with multiple different values within the same group has no unambiguous single value to display.
HAVING Clause Filters Groups
The HAVING clause filters groups produced by a GROUP BY operation, performing a role analogous to what WHERE performs for individual rows. While WHERE filters rows before grouping occurs, HAVING filters groups after aggregation has been computed, allowing conditions to reference aggregate values that do not exist until grouping is complete. To find departments with more than ten employees, for example, you cannot use a WHERE clause because the employee count per department does not exist as a column value — it is computed by the GROUP BY operation. The correct approach is SELECT department, COUNT() as employee_count FROM employees GROUP BY department HAVING COUNT() > 10, which first groups employees by department, computes the count for each group, and then filters out groups where the count does not exceed ten.
A common beginner confusion involves deciding whether to use WHERE or HAVING for a given filtering condition, and the distinction is actually straightforward once the underlying logic is clear. If the condition references a column that exists in the raw data before any grouping, use WHERE. If the condition references a value that is produced by an aggregate function like COUNT, SUM, or AVG, use HAVING. In many queries, both clauses appear together — WHERE filters the rows that participate in the grouping, and HAVING filters the groups that appear in the final result. SELECT department, AVG(salary) FROM employees WHERE status = ‘active’ GROUP BY department HAVING AVG(salary) > 70000 first restricts the analysis to active employees, groups them by department, computes the average salary for each department, and then returns only departments where that average exceeds 70,000.
INNER JOIN Combining Two Tables
The INNER JOIN is the most commonly used type of join operation in SQL, combining rows from two tables based on a matching condition and returning only the rows where a match exists in both tables. Most relational databases organize data across multiple tables to avoid redundancy — customer information in a customers table, order information in an orders table, and product information in a products table — and joins are the mechanism for reassembling this distributed data into meaningful combined result sets. To retrieve orders alongside the customer name for each order, you would join the orders and customers tables on the column they share: SELECT customers.first_name, customers.last_name, orders.order_date, orders.order_total FROM orders INNER JOIN customers ON orders.customer_id = customers.customer_id.
The ON clause specifies the condition that determines which rows from each table are considered matches. In the example above, a row from the orders table is matched with a row from the customers table when the customer_id values in both rows are identical. The INNER JOIN returns only pairs of rows where this match condition is satisfied — orders with customer IDs that do not exist in the customers table are excluded, and customers who have placed no orders are also excluded. When multiple tables have columns with the same name — like customer_id appearing in both tables — prefixing column references with the table name or a table alias prevents ambiguity. Using table aliases, which are short abbreviated names assigned to tables within a query, keeps complex join queries readable — SELECT c.first_name, c.last_name, o.order_total FROM orders o INNER JOIN customers c ON o.customer_id = c.customer_id conveys the same logic more concisely.
LEFT JOIN Keeping All Left Rows
The LEFT JOIN, also written as LEFT OUTER JOIN, returns all rows from the left table in the join and the matching rows from the right table. When a row in the left table has no matching row in the right table, the query still includes that left table row in the result, with null values filling the columns that would have come from the right table. This behavior makes LEFT JOIN essential for queries that need to identify records in one table that have no corresponding records in another — for example, finding customers who have never placed an order.
To find all customers and their orders including customers with no orders, you would write SELECT c.first_name, c.last_name, o.order_id, o.order_total FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id. Customers who have placed orders appear once for each order they have placed, while customers with no orders appear exactly once with null values in the order_id and order_total columns. To filter this result down to only customers with no orders — the common use case of identifying unmatched records — add WHERE o.order_id IS NULL to the query. This pattern of LEFT JOIN combined with an IS NULL filter on a right-table column is one of the most elegant and efficient ways to find records that lack a corresponding record in a related table, a query pattern that appears constantly in data quality analysis and reporting.
RIGHT JOIN and FULL OUTER JOIN
The RIGHT JOIN is the mirror image of LEFT JOIN — it returns all rows from the right table and matching rows from the left table, with nulls filling left-table columns for right-table rows that have no match. In practice, RIGHT JOIN is used far less frequently than LEFT JOIN because any RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the order of the tables in the query, and most SQL practitioners prefer to standardize on LEFT JOIN for consistency. SELECT c.first_name, o.order_id FROM orders o RIGHT JOIN customers c ON o.customer_id = c.customer_id produces the same result as the equivalent LEFT JOIN written with the tables in reversed order, and most teams choose one approach and apply it consistently across their SQL codebase.
The FULL OUTER JOIN combines the behavior of LEFT JOIN and RIGHT JOIN, returning all rows from both tables with nulls where no match exists in the other table. A row appears in the result for every row in the left table (matched or unmatched) and for every row in the right table (matched or unmatched). FULL OUTER JOIN is most useful for data reconciliation tasks where you need to identify records that exist in one table but not the other, as well as records that exist in both. It is worth noting that MySQL does not natively support FULL OUTER JOIN syntax, though the equivalent result can be achieved by combining a LEFT JOIN and a RIGHT JOIN with a UNION operator. Beginners who encounter FULL OUTER JOIN errors in MySQL should be aware of this platform-specific limitation and use the UNION-based workaround when necessary.
INSERT Statement Adding New Records
The INSERT INTO statement adds new rows of data to a table, and it is the SQL command that populates databases with the information that SELECT queries later retrieve. The basic syntax specifies the target table name, the columns to populate, and the corresponding values for each column — INSERT INTO customers (first_name, last_name, email, city) VALUES (‘Sarah’, ‘Johnson’, ‘sarah.johnson@email.com’, ‘Chicago’). The column list and values list must match in order and count — the first value corresponds to the first column, the second to the second, and so on. Columns not included in the INSERT statement will receive their default values, typically null unless a non-null default is defined in the table schema.
Multiple rows can be inserted in a single INSERT statement by providing multiple sets of values separated by commas — INSERT INTO products (product_name, price, category) VALUES (‘Laptop’, 999.99, ‘Electronics’), (‘Mouse’, 29.99, ‘Electronics’), (‘Desk Chair’, 249.99, ‘Furniture’). This multi-row INSERT is considerably more efficient than executing separate INSERT statements for each row, particularly when inserting large volumes of data, because it reduces the number of round trips between the application and the database. Another common INSERT pattern copies data from one table into another — INSERT INTO archive_orders SELECT FROM orders WHERE order_date < ‘2023-01-01’ moves all pre-2023 orders from the active orders table into an archive table in a single operation, combining the power of INSERT with a SELECT subquery.
UPDATE Statement Modifying Records
The UPDATE statement modifies existing data in a table, changing the values of specified columns for rows that match a given condition. The syntax specifies the target table, uses a SET clause to define which columns should be changed and what their new values should be, and uses a WHERE clause to identify which rows should be affected. To update the email address for a specific customer, you would write UPDATE customers SET email = ‘new.email@example.com’ WHERE customer_id = 1042. The WHERE clause is critically important — without it, the UPDATE statement modifies every row in the table, which is one of the most consequential mistakes a beginner can make and one that is unfortunately irreversible without a database backup.
Multiple columns can be updated in a single UPDATE statement by listing them in the SET clause separated by commas. UPDATE employees SET salary = 75000, job_title = ‘Senior Analyst’, updated_at = CURRENT_TIMESTAMP WHERE employee_id = 238 updates three columns simultaneously for the specified employee in one operation. UPDATE statements can also use expressions rather than literal values — UPDATE products SET price = price * 1.10 WHERE category = ‘Electronics’ increases the price of all electronics products by ten percent by multiplying the current price value by 1.10. Before executing UPDATE statements that affect multiple rows, many experienced SQL practitioners first run the equivalent SELECT query with the same WHERE clause to verify exactly which rows will be affected, reducing the risk of unintended modifications.
DELETE Statement Removing Records
The DELETE statement removes rows from a table that match specified conditions. Like UPDATE, DELETE requires a carefully considered WHERE clause to target only the intended rows — DELETE FROM orders WHERE order_id = 5589 removes the specific order with that ID, while DELETE FROM orders without a WHERE clause removes every row in the orders table entirely. The consequences of an accidental DELETE without a WHERE clause on a production table are severe and potentially irreversible, which is why many organizations implement database user permissions that prevent application users from executing DELETE statements directly, reserving that capability for database administrators operating under change control procedures.
When deleting related records across multiple tables, the order of deletion matters because foreign key constraints may prevent deletion of a parent record that has corresponding child records in another table. In a database where orders have related order_items records, attempting to delete an order while its order items still exist will typically fail with a foreign key violation error. The correct approach is to delete the child records first — DELETE FROM order_items WHERE order_id = 5589 — and then delete the parent record. Some database schemas define CASCADE DELETE behavior on foreign keys, which automatically deletes child records when the parent is deleted, but this behavior must be explicitly configured in the schema and beginners should verify whether it is in effect before relying on it. TRUNCATE TABLE is an alternative to DELETE without a WHERE clause that removes all rows more efficiently but cannot be rolled back in most database systems and bypasses triggers — it should be used with caution and only in appropriate administrative contexts.
ALTER TABLE Modifying Structure
The ALTER TABLE statement modifies the structure of an existing table, allowing database administrators and developers to add, remove, or modify columns and constraints after the table has been created and potentially populated with data. Adding a new column to an existing table uses the syntax ALTER TABLE customers ADD COLUMN phone_number VARCHAR(20) — this adds a phone_number column to the customers table, with null values in that column for all existing rows. Adding a column that does not allow null values to a table that already contains rows requires either providing a default value — ALTER TABLE customers ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT ‘active’ — or updating existing rows with appropriate values immediately after adding the column.
Modifying an existing column’s data type or constraints uses ALTER TABLE with a MODIFY COLUMN or ALTER COLUMN clause depending on the database system — MySQL uses MODIFY COLUMN while SQL Server and PostgreSQL use ALTER COLUMN. Renaming a column uses RENAME COLUMN in most modern databases. Dropping a column uses DROP COLUMN — ALTER TABLE customers DROP COLUMN phone_number removes the column and all its data permanently. Because schema changes can have significant downstream effects on applications, queries, and stored procedures that reference the affected columns, ALTER TABLE operations on production databases should be performed thoughtfully and tested thoroughly in development environments first. Many production database environments also require that schema changes be applied through migration scripts that are version-controlled alongside application code, ensuring that database structure changes are tracked, reviewable, and reversible.
CREATE TABLE Defining New Tables
The CREATE TABLE statement defines a new table in a database, specifying the table name, the columns it contains, the data type of each column, and any constraints that govern the values each column may contain. A basic CREATE TABLE statement for a customers table might look like this: CREATE TABLE customers (customer_id INT PRIMARY KEY AUTO_INCREMENT, first_name VARCHAR(100) NOT NULL, last_name VARCHAR(100) NOT NULL, email VARCHAR(255) UNIQUE NOT NULL, city VARCHAR(100), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP). This single statement creates a table with six columns, a primary key that auto-increments for each new row, required fields enforced by NOT NULL constraints, and a unique constraint on the email column that prevents duplicate email addresses.
Choosing appropriate data types for each column is one of the most important decisions in table design and has implications for storage efficiency, query performance, and data integrity. VARCHAR stores variable-length text and requires only as much storage as the actual text length, while CHAR stores fixed-length text that is padded with spaces to its defined length — VARCHAR is appropriate for most text columns while CHAR is best suited for fixed-length values like country codes or status flags. INT stores whole numbers, DECIMAL or NUMERIC stores exact decimal numbers appropriate for financial values, FLOAT and DOUBLE store approximate floating-point numbers suitable for scientific calculations, and DATE, TIME, DATETIME, and TIMESTAMP store temporal values. Using a VARCHAR column to store what is conceptually a numeric value, or a TEXT column to store a date, prevents the database from enforcing data type integrity and disables the relevant comparison and arithmetic operations — beginners who define column types thoughtfully from the start avoid many data quality problems later.
Subqueries Within SQL Queries
A subquery is a SQL query nested inside another SQL query, allowing the result of one query to be used as input for another. Subqueries can appear in SELECT, FROM, WHERE, and HAVING clauses, making them a versatile tool for expressing complex analytical logic that cannot be achieved with a single flat query. A common use of subqueries in WHERE clauses filters rows based on aggregate values — SELECT product_name, price FROM products WHERE price > (SELECT AVG(price) FROM products) retrieves all products whose price exceeds the average price across all products. The inner query calculates the average price and returns a single value, which the outer query uses as the comparison threshold in its WHERE clause.
Subqueries in FROM clauses create derived tables — temporary result sets that the outer query treats as if they were regular tables. SELECT department, avg_salary FROM (SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department) AS dept_averages WHERE avg_salary > 60000 first computes average salaries by department in the inner query and then filters the resulting derived table to departments where the average exceeds 60,000. This pattern achieves the same result as using HAVING on the original query, but derived table subqueries are more flexible because they allow conditions to reference computed column aliases that HAVING cannot always access directly. As beginners grow more comfortable with subqueries, they provide a pathway to increasingly sophisticated analytical queries that combine multiple aggregation levels, conditional logic, and cross-table calculations into coherent, expressive SQL statements.
UNION Combining Query Results
The UNION operator combines the result sets of two or more SELECT queries into a single result set, stacking the rows from each query on top of each other. For UNION to work correctly, each SELECT statement must have the same number of columns, and the corresponding columns must have compatible data types. UNION automatically removes duplicate rows from the combined result — if the same row appears in both queries, it appears only once in the UNION output. To retain duplicates, UNION ALL is used instead, which is also faster because it does not perform the duplicate elimination step. For large datasets where duplicates are known not to exist or where duplicates are acceptable, UNION ALL is the more efficient choice.
A practical use case for UNION is combining similar data from multiple tables that store information in the same format but represent different categories or time periods. SELECT customer_id, order_date, order_total FROM current_orders UNION ALL SELECT customer_id, order_date, order_total FROM archived_orders produces a complete combined list of all orders from both tables, which can then be further filtered, sorted, or aggregated as if it came from a single table. UNION is also useful for combining different types of records into a unified list — a query that retrieves both customers and suppliers into a single contact list with a label column indicating the record type uses UNION to combine two queries against different tables. Column names in the UNION result are taken from the first SELECT statement, so if the columns have different names in different source tables, aliases in the first query define the output column names.
CASE Expression for Conditional Logic
The CASE expression brings conditional logic into SQL queries, allowing query results to vary based on the values in specified columns. It functions similarly to an if-else statement in programming languages, evaluating conditions sequentially and returning the value associated with the first condition that evaluates to true. The searched CASE syntax — CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ELSE default_result END — is the most flexible form and can test any logical condition in each WHEN clause. SELECT product_name, price, CASE WHEN price < 25 THEN ‘Budget’ WHEN price BETWEEN 25 AND 100 THEN ‘Mid-Range’ WHEN price > 100 THEN ‘Premium’ ELSE ‘Unknown’ END AS price_category FROM products adds a computed price_category column to the result based on each product’s price value.
CASE expressions can appear in SELECT, WHERE, ORDER BY, GROUP BY, and HAVING clauses, giving them broad applicability across different types of queries. Using CASE in an ORDER BY clause allows custom sort orders that do not correspond to alphabetical or numeric sequence — ORDER BY CASE status WHEN ‘urgent’ THEN 1 WHEN ‘normal’ THEN 2 WHEN ‘low’ THEN 3 ELSE 4 END sorts rows by a business-defined priority rather than alphabetically by the status string. Using CASE inside aggregate functions enables conditional aggregation — SELECT COUNT(CASE WHEN status = ‘completed’ THEN 1 END) AS completed_count, COUNT(CASE WHEN status = ‘cancelled’ THEN 1 END) AS cancelled_count FROM orders counts completed and cancelled orders in separate columns within a single query, which is a powerful pattern for building summary pivot-style reports without needing a true SQL PIVOT operation.
IS NULL and IS NOT NULL
Null values in SQL represent the absence of data — a column with a null value contains nothing, not zero and not an empty string, but literally no value at all. This distinction has important implications for how null values behave in comparisons and arithmetic operations. Null is not equal to anything, including itself — the expression NULL = NULL evaluates not to true but to unknown, which means that using a standard equality operator to test for null values never returns results. The correct way to test whether a column value is null is with the IS NULL operator, and the correct way to test whether a column has any value at all is IS NOT NULL. SELECT first_name, last_name FROM customers WHERE email IS NULL finds all customers who have no email address recorded, while adding NOT reverses the filter to find customers who do have an email address.
The behavior of null values in aggregate functions and arithmetic is another area where beginners frequently encounter unexpected results. Aggregate functions like COUNT, SUM, AVG, MIN, and MAX all ignore null values in their calculations — a SUM of a column with ten rows where three contain null values adds only the seven non-null values. Arithmetic operations involving null return null — if a row has null in the discount column, then price – discount evaluates to null rather than to price, which can cause calculated columns to contain unexpected nulls. The COALESCE function provides a clean solution by substituting a specified default value for nulls — COALESCE(discount, 0) returns the discount value if it exists and zero if it is null, ensuring that arithmetic operations produce meaningful results even when source data contains nulls.
String Functions for Text Data
SQL provides a rich set of built-in string functions that allow beginners to manipulate and transform text data within their queries without needing to retrieve data and process it in application code. UPPER and LOWER convert text to uppercase and lowercase respectively, which is useful for normalizing data for comparison or display — WHERE LOWER(email) = LOWER(‘User@Example.com’) performs a case-insensitive email lookup. TRIM removes leading and trailing whitespace from a string value, which is helpful for cleaning data that may have been entered with accidental spaces. LENGTH or LEN (depending on the database system) returns the number of characters in a string, enabling queries like SELECT first_name FROM customers WHERE LENGTH(first_name) > 15 that filter based on string length.
CONCAT combines multiple string values into a single string — SELECT CONCAT(first_name, ‘ ‘, last_name) AS full_name FROM customers produces a formatted full name column by combining first name, a space, and last name. SUBSTRING (or SUBSTR) extracts a portion of a string given a starting position and length — SUBSTRING(phone_number, 1, 3) extracts the first three characters of a phone number, which might represent an area code. REPLACE substitutes all occurrences of one substring with another — REPLACE(phone_number, ‘-‘, ”) removes all hyphens from phone numbers stored with separators. The LIKE operator, used in WHERE clauses with wildcard characters, performs pattern matching against text values — WHERE last_name LIKE ‘Mc%’ matches all last names beginning with ‘Mc’, and WHERE email LIKE ‘%@gmail.com’ matches all Gmail addresses. These string functions collectively give beginners a powerful toolkit for data cleaning, normalization, and transformation directly within SQL queries.
Date Functions and Time Queries
Working with dates and times is a constant requirement in real-world SQL work, and every major database system provides functions for extracting date components, calculating differences between dates, and formatting dates for display. CURRENT_DATE or GETDATE() returns the current date, while CURRENT_TIMESTAMP returns both the current date and time. To find orders placed in the last 30 days, you would write SELECT order_id, order_date FROM orders WHERE order_date >= CURRENT_DATE – INTERVAL 30 DAY (MySQL syntax) or WHERE order_date >= DATEADD(day, -30, GETDATE()) (SQL Server syntax). The syntactic differences between database systems for date arithmetic are one of the most frequent portability challenges beginners encounter when moving code between platforms.
YEAR, MONTH, and DAY functions extract the respective components of a date value — SELECT YEAR(order_date), MONTH(order_date), COUNT() FROM orders GROUP BY YEAR(order_date), MONTH(order_date) generates a monthly order count summary across all years in the dataset. DATEDIFF calculates the difference between two dates in a specified unit — DATEDIFF(day, order_date, ship_date) returns the number of days between order placement and shipment, enabling service level analysis. DATE_FORMAT in MySQL and FORMAT in SQL Server format dates as strings for display — DATE_FORMAT(order_date, ‘%M %d, %Y’) converts a date value to a string like ‘January 15, 2024’. Becoming comfortable with date functions is particularly valuable because time-based analysis — trends over periods, age calculations, scheduling queries, and deadline monitoring — represents a substantial proportion of the analytical SQL work that beginners encounter in real job roles.
INDEX Creation for Performance
Indexes are database structures that improve the speed of data retrieval operations by allowing the database engine to find rows matching specified conditions without scanning every row in a table. The concept is analogous to a book index — instead of reading every page to find a topic, you consult the index to find the specific page. CREATE INDEX idx_customer_email ON customers (email) creates an index on the email column of the customers table, which dramatically speeds up queries that search or filter by email address. Without the index, a query like SELECT * FROM customers WHERE email = ‘user@example.com’ must examine every row in the table; with the index, the database engine can locate matching rows in a fraction of the time.
Beginners should understand the trade-offs of indexing because adding indexes to every column is not the optimal strategy. Each index consumes additional storage space and adds overhead to INSERT, UPDATE, and DELETE operations because the index must be updated whenever the indexed column values change. Tables with very high write volumes can become significantly slower if they have many indexes. The columns most appropriate for indexing are those that appear frequently in WHERE clauses, JOIN conditions, and ORDER BY clauses in frequently executed queries, particularly those returning a small proportion of the table’s rows. Primary key columns are automatically indexed in most database systems, and columns used as foreign keys in JOIN operations are strong candidates for indexing because they participate in join operations constantly. Understanding when to add an index and why is a skill that develops with experience, but awareness of the concept and its trade-offs gives beginners a foundation for discussing query performance intelligently.
Conclusion
The 30 SQL queries presented throughout this guide represent the practical foundation that every SQL practitioner builds their expertise upon, regardless of whether they ultimately work as a data analyst, software engineer, database administrator, or business intelligence developer. Each query pattern addresses a real category of data problem — retrieving specific information, filtering by conditions, summarizing through aggregation, combining data from multiple tables, modifying records, defining data structures, and optimizing performance — that appears constantly in actual database work across industries and organizational contexts. A beginner who can write, read, and reason about all 30 of these query types with genuine understanding has crossed the threshold from novice to competent SQL practitioner, possessing the core toolkit that the vast majority of day-to-day database tasks require.
The most important advice for anyone working through these query types is to practice relentlessly against real data in a real database environment rather than simply reading and nodding at the explanations. SQL is a practical skill that develops through doing, and the gap between understanding a concept when explained and being able to apply it confidently when facing a blank query editor is bridged only through repeated hands-on practice. Free database environments are readily available — MySQL, PostgreSQL, and SQLite can all be installed locally at no cost, and platforms like DB Fiddle, SQLiteOnline, and Mode Analytics provide browser-based SQL environments requiring no installation at all. Sample datasets are equally accessible, with options like the classic Northwind database, the Chinook music store database, and countless publicly available datasets from government and research sources providing realistic data to query.
As you practice, adopt the habit of thinking about what result you want before writing any SQL, then constructing your query as a logical expression of that intent rather than as a collection of syntax to assemble. SQL rewards clear thinking — queries that are easy to read and understand tend to be both correct and efficient, while queries written by stringing syntax together without a clear logical plan tend to produce unexpected results and performance problems. Read the queries of experienced practitioners, study how they structure complex logic, and ask yourself why each clause is written the way it is rather than simply accepting that it works. This analytical approach to learning SQL transforms isolated syntax knowledge into genuine problem-solving capability that compounds over time, building toward the kind of deep database fluency that distinguishes truly effective data practitioners from those who can execute familiar patterns but struggle when faced with novel data challenges.
The journey from these 30 foundational queries to advanced SQL topics like window functions, common table expressions, recursive queries, stored procedures, and query optimization is a natural progression that each of these beginner queries points toward. GROUP BY leads naturally to window functions, which perform calculations across row groups without collapsing them into single output rows. Subqueries lead naturally to common table expressions, which express the same logic more readably for complex multi-step analyses. Understanding indexes as a beginner builds the intuition for reading query execution plans and optimizing query performance at a more sophisticated level later. Every concept in this guide is a doorway to deeper expertise, and the investment made in genuinely understanding these foundational queries pays dividends at every subsequent stage of SQL learning and professional practice.
- Category: Others
