Querying tables

First Let us see what is mean by keyword, expression, clause, operator in SQL ?

Keywords

A keyword is a reserved word in SQL that has a predefined meaning or purpose. Keywords are the building blocks of clauses and SQL statements.

Expression

An expression is any valid combination of values, operators, functions, and keywords that evaluates to a single value or result. Expressions can be used in various parts of SQL statements, such as clauses, to specify conditions or calculations.

clause

A clause is a distinct component of a SQL statement that performs a specific function, typically consisting of one or more keywords and associated expressions. Clauses work together to define what data is retrieved, manipulated, or stored.

Operator

An operator is a symbol or keyword that performs an operation on one or more values called as operands, and returns a result. Operators are used in expressions to perform comparisons, arithmetic, logical operations, and some other.

Now let us learn some of the clauses and operators to construct queries.

Clauses in PostgreSQL

SELECT

The SELECT clause is used to retrieve data from one or more tables.

• It allows us to specify which columns to retrieve, apply conditions, and sort results.
• Multiple columns or expressions can be specified, separated by commas.

FROM

The FROM clause indicates the table or tables from which data is retrieved or deleted.

ORDER BY

The ORDER BY clause sorts the result set based on one or more columns, based on expressions like LENGTH() of a string etc.,

• Sorting can be ascending (ASC, default) or descending (DESC).
• It can be used without the WHERE clause.
• Always pair ORDER BY with FETCH or LIMIT for consistent results.

LENGTH() function accepts CHAR, VARCHAR, TEXT datatypes.

WHERE

The WHERE clause filters records based on specified conditions. It applies to individual rows only.

• It is executed after the FROM clause but before the SELECT clause.
• It filters rows before aggregation,
• The condition in the WHERE clause can evaluate to true, false, or unknown. It can be a single Boolean expression or a combination of expressions using logical operators - AND, OR, NOT etc.,
• Avoid using functions on columns in the WHERE clause if possible, as this can prevent the use of indexes Why can't we use WHERE with column functions - Thanks to Cesar Aguirre

HAVING

The HAVING clause filters results after grouping and aggregation. It applies to groups as a whole.

• Used with GROUP BY, it refines grouped data using conditions. If there is no GROUP BY clause, the HAVING clause is applied to the entire result as a single group.
• Unlike WHERE, it cannot be used independently.
• HAVING clause is an essential tool for querying and filtering aggregated data, enabling us to apply conditions to groups after aggregation.
• Whether we’re working with SUM(), COUNT(), or other aggregate functions, the HAVING clause helps us filter results based on aggregated conditions.

WHAT is Aggregation? An aggregate is a collection of people who happen to be at the same place at the same time but who have no other connection to one another

GROUP BY

The GROUP BY clause organizes rows into groups based on one or more column values.

• Only columns in the GROUP BY clause or used in aggregate functions can appear in the SELECT statement.
• The GROUP BY clause must appear after the FROM and WHERE clauses in a SQL query. The order of execution ensures that data is filtered before being grouped.
• NULL values are treated as a single group.

LIMIT

The LIMIT clause restricts the number of rows returned in a query.

• It is used to fetch the top N rows from the result set.
• To fetch the bottom N rows, sort in reverse order them apply LIMIT.

OFFSET

Used to skip specified number of rows at the beginning of a query result.

• Often used with the OFFSET clause for pagination.

FETCH

The FETCH clause retrieves a specific subset of rows from a result set.

• It is a part of SQL standard, ensures that our queries are portable across various databases like MySQL, ORACLE etc.,

Syntax

OFFSET start { ROW | ROWS } FETCH { FIRST | NEXT } [ row_count ] { ROW | ROWS } ONLY

• Use ROW for a single row and ROWS for multiple rows.
• start is an Integer (default: 0).
• row_count number of rows to fetch (default: 1).
• Use ORDER BY with FETCH for consistent results by sorting rows before fetching, especially useful for pagination.
• FETCH can limit rows processed in subqueries.

Operators in PostgreSQL

AND

Combines two or more conditions, returning true if all conditions are true.

OR

Combines two or more conditions, returning true if at least one condition is true.

IN

Checks whether a value matches any value in a specified list or subquery. Some of the key points about IN operator are :

• Simplifies checking against multiple values with list of values.
• Checks a column's value against results from a subquery.
• Replacing multiple OR conditions with a single IN clause,
• If the list of values or subquery contains NULL, the IN operator will not match any rows unless the column value being compared is also NULL.
• The IN operator is case-sensitive. To perform a case-insensitive comparison, use the ILIKE operator with pattern matching.
• For very large datasets, consider using EXISTS or JOIN operations as an alternative to the IN operator.

LIKE

Performs pattern matching for strings using wildcards. There are 2 special wildcard characters ( % , _ )for the purpose of pattern matching.

• Percent ( %) for matching any sequence of characters
• Underscore ( _ ) for matching any single character.
• Starts with Specific Letters
• Ends with Specific Letters
• Specific Letters in the Middle
• Case-Insensitive Matching
• NOT LIKE
• ESCAPE - Allows escaping wildcard characters (%, _) in patterns.

BETWEEN

Operator checks if a value falls within a specified range.

• Includes both boundaries (low and high)
• Use < or > for exclusive boundaries.
• Case Sensitivity with Strings: When using BETWEEN with string data types, the comparison is case-sensitive by default. In this case use LOWER() or UPPER()
• Excluding a range of Values with NOT BETWEEN
• Date and Time Zone Awareness: When using BETWEEN with date and time values that include time zones, be mindful of potential time zone differences that could impact the results.

NOTE : we use DVD rental database to perform SQL queries using clauses and operators. Set up DVD rental database

If you understand the topics till here you are ??GREAT, Try to implement these queries for hands on experience. Check out this page to find Number of keywords in PostgreSQL

Your Turn! mention any other commonly using operators, keywords, expressions or suggestions and insights in comments below.