Tell me about the full syntax of the SELECT statement in a relational database.

The full syntax of the SELECT statement includes the following main parts:

1. SELECT: List of columns or expressions to retrieve.
   • * - select all columns.
   • column1, column2 - select specified columns.
   • expression AS alias - select the result of an expression and assign an alias.
   • DISTINCT column - select only unique values in a column.
2. FROM: Specifies the table or tables from which to select.
   • table_name - select from one table.
   • table1 JOIN table2 ON join_condition - join tables.
3. WHERE: Condition for filtering rows. Applied before grouping.
   • condition - logical expression defining which rows to include.
4. GROUP BY: Groups rows by specified columns. Used with aggregate functions.
   • column1, column2 - group by one or multiple columns.
5. HAVING: Condition for filtering groups created by GROUP BY.
   • condition - logical expression defining which groups to include.
6. ORDER BY: Sorts the result set by specified columns.
   • column [ASC | DESC] - sort by column in ascending (ASC) or descending (DESC) order. Default is ASC.
   • column1 ASC, column2 DESC - sort by multiple columns.
7. LIMIT / OFFSET (or equivalents in different DBMS, e.g., TOP in SQL Server): Limits the number of returned rows or skips a specified number.
   • LIMIT count - return the first count rows.
   • LIMIT offset, count - skip offset rows and return the next count.

Example of the logical execution order of the SELECT operator:

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT (including DISTINCT)
6. ORDER BY
7. LIMIT / OFFSET

Example syntax:

-- Example of a SQL query with full syntax
SELECT
    c.CustomerID,          -- Select customer ID column
    COUNT(o.OrderID) AS NumberOfOrders, -- Count of orders with alias
    SUM(o.TotalAmount) AS TotalOrderValue -- Sum of orders with alias
FROM
    Customers c             -- From the Customers table (with alias c)
JOIN
    Orders o ON c.CustomerID = o.CustomerID -- Join with Orders table (alias o) on CustomerID
WHERE
    c.City = 'London'       -- Filter customers from London
GROUP BY
    c.CustomerID           -- Group by CustomerID to count orders per customer
HAVING
    COUNT(o.OrderID) > 5   -- Filter groups: select only customers with more than 5 orders
ORDER BY
    TotalOrderValue DESC   -- Sort by total order value in descending order
LIMIT 10;                   -- Return only the first 10 results