Embedded Files
DATABASE /
1. COLUMN REFERENCE, LITERALS, OPERATORS, LARGE DATASET CONSIDERATIONS
SQL: Structured Query Language. A language to access and manage relational databases, or in other words: creating, retrieving, updating, deleting (CRUD) and so on.
RDBMS: Relational Database Management System, or relational database system which accepts SQL as a script language for manipulation.
All queries (commands) are closed with a semicolon.
Referencing a column in a statement:
if there is one table: column_name
if there are more tables: table_name.column_name
Literals:
integer: 123
float: 123.45
text: 'text'
date: 'yyyy-mm-dd'
time: 'hh:mm:ss'
datetime: 'yyyy-mm-dd hh:mm:ss'
boolean: TRUE, FALSE
null: NULL
Relation signs and logical operators: =, <, <=, >, >=, AND, OR, NOT, IN, NOT IN, EXISTS, NOT EXISTS, LIKE.
Usage of IN:
checking against a list: IN (element1, element2, ...)
checking against a subquery result: IN (SELECT ...)
If the subquery can result NULL (which can lead to FALSE value in the condition the IN is used), use EXISTS instead.
Usage of LIKE:
%: represents zero, one, or multiple characters (A% means any value starting with "A")
_: represents one character ("A_" matches any value starting with "A" and followed by one character like "Ax", "A1" and so on, "___" means 3 characters)
the LIKE pattern is usually case sensitive, in some databases it depends on collation setting (e.g. MySQL)
Considerations for large datasets:
choose the smallest appropriate data type for each column
when using the same query many times, enable caching, and divide the table to partitions
avoid using functions in the WHERE clause
use LIMIT to avoid unnecessary data retrieval, reduce time and memory usage
upon deletion, use LIMIT and run DELETE several times instead of one execution on the whole table
create index on the columns used with aggregate functions
create index on columns used in GROUP BY and HAVING
use WINDOW instead of GROUP BY for more efficient calculations
avoid using subqueries if the inner query executes for all rows, use JOIN or CTEs instead
index the columns being used in joins
for joining, use INNER JOIN whenever possible, as it is generally faster than OUTER JOIN
minimize joining unnecessary tables
avoid over-indexing, it can slow down operations
identify bottlenecks with execution plans
avoid storing large objects (images, videos) directly in the database
2. SELECT
The SELECT query is for retrieving data. Can return a new table with minimum one column and one row, or a value.
The returned table or value is temporary, unless it is specifically stored in a new table or variable.
Data can be retrieved from more sources.
Avoid SELECT * to reduce memory usage and time-consuming.
For aggregated queries, make sure to use specific columns instead of selecting unnecessary data.
For selection from more tables, use join or subqueries, see examples under their sections:
use JOINs when the relationship between tables is straightforward and requires multiple columns
use Subqueries for more complex filtering or when one table depends on the result of another query
For large datasets, use LIMIT to avoid unnecessary data retrieval, reduce time and memory usage.
2.1. BASIC SELECT, DATA FILTERING, LIMITING RESULT, ORDERING RESULT TABLE
The DISTINCT forces unique values in the given column (filters out duplicates). In case of more columns, their combination will be unique.
The WHERE serves as a data filter.
The LIMIT defines the number of retrieved rows.
EXAMPLE FOR BASIC SELECT SYNTAX:
EXAMPLE FOR BASIC SELECT:
2.2. AGGREGATORS FOR CALCULATING BASIC STATISTICS
The aggregate functions create a value from a set of rows. In other words, collapse the rows into a single value.
In large datasets, create index on the columns used with aggregate functions.
EXAMPLE FOR AGGREGATE FUNCTIONS SYNTAX:
EXAMPLE FOR AGGREGATE FUNCTIONS:
2.3. GROUP BY FOR COLLAPSING RESULT TABLE
The GROUP BY clause collapses the results following a given value or values, in other words segregates the results. For this, it is usually used along with an aggregator. It will create groups by the given values and aggregates by the group members.
The HAVING is a filter for the GROUP BY clause.
In large datasets, create index on columns used in GROUP BY and HAVING. Furthermore, use WINDOW instead of GROUP BY for more efficient calculations.
EXAMPLE FOR GROUP BY SYNTAX:
EXAMPLE FOR GROUP BY:
2.4. EXPRESSIONS AND FUNCTIONS, ALIASES
Mathematical expressions can be used on columns or values.
The AS clause creates alias for a column, a table or an expression, valid only in that query. Usage of AS:
for increasing readability
in case readability means using special character (e.g. space), put the name into "" or [] in some SQL dialects
in some SQL dialects, using AS is optional (using it increases readability)
the alias will be live only in the query
the alias can be live longer if it is defined in a view or a subquery
alias can not be used in the WHERE clause
Functions:
UPPER(), LOWER(): converts the column uppercase, lowercase
CONCAT(column1, column2, ...): concatenates 2 columns into one
SUBSTRING(column_name, start, length): extracts a substring from a column
DATE(year, month, day): creates a date from integers
CURRENT_DATE: gets the current date (MySQL, PostgreSQL)
GETDATE(): gets actual date (SQL Server)
SYSDATETIME(): gets current date and time (SQL Server)
DATEDIFF(date1, date2): calculates the difference between two dates, in days
DATE_ADD(date, INTERVAL value type): adds a time period to a date
YEAR(date), MONTH(date), DAY(date): returns the marked portion of the date
EXAMPLE FOR SELECT WITH MATH EXPRESSIONS AND AS CLAUSES:
EXAMPLE FOR CONCATENATION:
EXAMPLE FOR DATE FUNCTIONS:
2.5. WINDOW FUNCTIONS FOR CALCULATING NEW COLUMN
These functions are for calculating a new column without collapsing rows. Usually used for generating ranking, running totals, moving averages etc.
Window functions can be computationally expensive, especially on large datasets, as they often require sorting and partitioning. Using proper indexing and optimizing query logic can help improve performance.
When to Use Window Functions:
when you need row-level calculations that depend on other rows
for tasks like rankings, percentages, moving averages, cumulative sums, and time-based comparisons
to avoid using complex subqueries or self-joins
Running totals: sum(n) = sum(values(1..n)).
Counter: value(n) = n.
Moving averages: average(n) = average(values(1..n)).
Running maximum: maximum(n) = maximum(values(1..n)).
Running maximum: minimum(n) = minimum(values(1..n)).
EXAMPLE FOR WINDOW FUNCTION SYNTAX:
EXAMPLE FOR A QUERY WITH WINDOW FUNCTION SYNTAX:
Ranking functions:
ROW_NUMBER(): assigns a unique number to each row within a partition
RANK(): assigns a rank to each row within a partition; rows with the same value get the same rank, and the next rank is skipped
DENSE_RANK(): similar to RANK(), but no ranks are skipped
NTILE(n): divides rows into n buckets of approximately equal size and assigns a bucket number
Aggregate functions:
it will use it as a running (moving) value generator
Value functions:
LAG(column, offset, default): returns the value from a previous row.
LEAD(column, offset, default): returns the value from a subsequent row.
FIRST_VALUE(column): fetches the first value within a window.
LAST_VALUE(column): fetches the last value within a window.
NTH_VALUE(column, n): fetches the nth value within a window.
Other functions:
CUME_DIST(): calculates the cumulative distribution of a value within a partition.
PERCENT_RANK(): calculates the relative rank of a value as a percentage.
The OVER clause specifies that the function is used as a window function.
The PARTITION BY clause divides the result set into partitions or groups for the calculation, which means the function will be executed on these groups independently.
The ORDER BY clause:
it defines the order of rows for the calculation
it will order independently by partitions if given, or it orders the whole table)
it defines the column on which the function must be executed
in case of aggregate functions, since they have the targeted column in argument, the ORDER BY clause becomes optional
EXAMPLE FOR RANKING:
EXAMPLE FOR RUNNING TOTAL:
EXAMPLE FOR LAG:
The WINDOW clause lets reusing the result of a window. This clause
uses the given original columns
creates a window and give it a new name
the rest of the SELECT will use the new name as a column or a value
EXAMPLE FOR USING THE WINDOW CLAUSE:
2.6. UNION, INTERSECT, EXCEPT OR MINUS OF QUERIES
Combines two query results into one set:
UNION: get all rows of the tables but filters out duplicated rows
UNION ALL: get all rows from all tables
INTERSECT: gets only the common records without duplicating them
EXCEPT (OR MINUS in Oracle): all rows of the first table without duplicates
EXAMPLE FOR UNION:
2.7. SAVE RESULTS TO A NEW TABLE
The result of a query can be saved as a new table with the SELECT command, or add to an existing one with the INSERT INTO command.
The FROM clause can define table(s) and or view(s).
Properties of the new table:
the new table will inherit the column definitions (data types, sizes) from the view
the new table will not automatically include indexes, primary keys, or constraints
In case of inserting, giving an argument list is optional:
if given, it will define the targeted columns in order of the SELECT columns; these columns will be populated with data while the rest of the columns in the targeted table will get NULL or default value
if not given, the database assumes that columns in the targeted table are in the same order as they are in the SELECT
Column types must match. If the order of the columns are not matching, and or there are more columns in the targeted table, define the argument list.
EXAMPLE FOR SAVING QUERY RESULT INTO A NEW TABLE:
EXAMPLE FOR SAVING QUERY RESULT INTO AN EXISTING TABLE:
3. SUBQUERIES, COMMON TABLE EXPRESSIONS (CTE), RECURSIVE QUERIES
3.1. SUBQUERIES
Subqueries are queries nested inside another query.
Can appear in SELECT, FROM and WHERE tags. The nested query creates a new table or value which will be used as an existing table or value in the outer query.
In large datasets, avoid using it if the inner query executes for all rows, use JOIN or CTEs instead.
EXAMPLE FOR NESTED QUERIES:
Subqueries can be used for selection from more tables. Use it for more complex filtering or when one table depends on the result of another query. Use JOINs when the relationship between tables is straightforward and requires multiple columns.
In such case, using EXISTS instead of IN is safer when there is a possibility that there is no matching department.
EXAMPLE FOR SELECT FROM MORE TABLES WITH SUBQUERY, USING IN:
EXAMPLE FOR SELECT FROM MORE TABLES WITH SUBQUERY, USING EXISTS:
3.2. SEQUENTIAL QUERIES OR COMMON TABLE EXPRESSIONS (CTE)
CTEs simplify complex queries by breaking them into smaller, manageable parts.
EXAMPLE FOR CTE:
3.3. RECURSIVE QUERIES
Useful for hierarchical or tree-like data, like organizational charts or file directories.
EXAMPLE FOR RECURSIVE QUERIES:
4. JOIN
Connects two tables by matching rows:
INNER JOIN: returns only matching rows from both tables
LEFT JOIN: returns all rows from the left table and matching rows from the right table
RIGHT JOIN: returns all rows from the right table and matching rows from the left table
FULL OUTER JOIN: combines LEFT JOIN and RIGHT JOIN
The simple JOIN equals with INNER JOIN.
Joins on large datasets can be costly. Optimizing for large datasets:
index the columns being used in the join
use INNER JOIN whenever possible, as it is generally faster than OUTER JOIN
minimize joining unnecessary tables
EXAMPLE FOR JOIN SYNTAX:
EXAMPLE FOR INNER JOIN:
JOINs can be used for selection from more tables. Use JOINs when the relationship between tables is straightforward and requires multiple columns. Use Subqueries for more complex filtering or when one table depends on the result of another query.
EXAMPLE FOR SELECT FROM MORE TABLES WITH JOIN:
Google Sites
Report abuse