Introduction to SQL

Introduction to SQL is an open-source guide designed to teach the basics of SQL and relational databases. It is tailored for:

• DevOps/SysOps engineers
• Developers
• Linux enthusiasts
• System administrators

Materialize is a streaming database designed for real-time analytics. Unlike traditional SQL engines that run queries against static snapshots, Materialize maintains the answers to SQL queries over time using materialized views. As new data flows in from various sources, the views are incrementally updated.

Supported input sources include:

• Streaming sources: e.g., Kafka
• Object stores: e.g., S3
• Database change feeds: e.g., PostgreSQL
• Files: CSV, JSON, and unstructured files like logs.

A subquery is a SQL query nested inside a larger (parent) query. The inner query executes first, and its results are passed to the outer query to be used in filtering or data manipulation.

Subqueries can be placed in the following clauses:

• SELECT clause: To return specific values for each row.
• FROM clause: To treat the subquery result as a temporary table.
• WHERE clause: To filter rows based on the results of the inner query (the most common usage).

Subqueries can be nested within SELECT, INSERT, UPDATE, or DELETE statements, and can even be nested inside other subqueries.

The HAVING clause is used to specify conditions that filter which groups appear in the result set. Unlike the WHERE clause, which imposes conditions on individual columns/rows, HAVING is specifically designed to work with aggregate functions and must follow the GROUP BY clause in a query.

Syntax:

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

A trigger is a stored procedure in a database that is automatically invoked when a specific event occurs, such as INSERT, UPDATE, or DELETE.

Triggers are commonly used to:

• Validate data before it is stored (using before triggers).
• Perform automated tasks after data changes (using after triggers), such as logging changes or updating related tables.

Trigger Levels

• Row-level triggers (for each row): The trigger executes for every individual row affected by the DML operation.
• Column-level triggers (for each column): The trigger executes when a specific column is affected.

create trigger [trigger_name] 
[before | after]  
{insert | update | delete}  
on [table_name]  
[for each row | for each column]  
[trigger_body] 

A LEFT JOIN and a RIGHT JOIN are functionally equivalent. You can convert a LEFT JOIN into a RIGHT JOIN by swapping the order of the tables in the FROM and JOIN clauses.

• TableA LEFT JOIN TableB is the same as TableB RIGHT JOIN TableA.

-- Original LEFT JOIN
SELECT users.*, posts.*
FROM posts
LEFT JOIN users 
ON posts.user_id = users.id;

-- Equivalent RIGHT JOIN
SELECT users.*, posts.*
FROM users
RIGHT JOIN posts 
ON posts.user_id = users.id;

A Candidate Key is a minimal Super Key. This means it is a set of attributes that uniquely identifies a tuple, but if you were to remove any attribute from the set, it would no longer be unique. Candidate Keys are the specific sets of attributes that are 'candidates' to become the Primary Key.

Example: If a Customer relation has the following Super Keys:

• [CustomerID]
• [CustomerID, CustomerName]
• [CustomerName, CustomerAddress]

The Candidate Keys would be [CustomerID] and [CustomerName, CustomerAddress], because the second one is a minimal set that provides uniqueness.

A Foreign Key is an attribute (or set of attributes) in one relation (the referencing relation) that refers to the Primary Key of another relation (the referenced relation).

Key Rules:

• Foreign keys implement referential integrity.
• The values in the foreign key column must exist in the Primary Key column of the referenced table.
• A relation can have multiple foreign keys.

Example: If you have a Customer table (referenced) and an Orders table (referencing):

• Customer Primary Key: CustomerID
• Orders structure: (Order ID, Customer ID, Order Date, ...)
• In this case, Customer ID in the Orders table is a Foreign Key referencing CustomerID in the Customer table. This ensures an order cannot be placed for a customer that does not exist.

The UNION ALL operator combines the result sets of two or more SELECT statements, but unlike UNION, it includes duplicate rows in the final output. This is often faster because the database does not need to perform the extra step of checking for and removing duplicates.

Just like the UNION clause, UNION ALL requires that all SELECT statements have the same number of columns, the same number of expressions, and compatible data types in the same order.

SELECT id, name, amount, date
   FROM customers
   LEFT JOIN orders
   ON customers.id = orders.customer_id
UNION ALL
   SELECT id, name, amount, date
   FROM customers
   RIGHT JOIN orders
   ON customers.id = orders.customer_id;

SQL aggregation collects a set of values to return a single value. An aggregate function groups multiple rows together based on certain criteria to form a single value of significant meaning.

Supported aggregate functions include:

• AVG(): Calculates the average of the values in a given column.
• SUM(): Calculates the sum of values in a given column.
• COUNT(): Returns the count of entries/values in a given column.
• MAX(): Returns the maximum value from the column.
• MIN(): Returns the minimum value from the column.