The fundamental SQL command used to retrieve data from one or more tables in a database, specifying which columns to return and from which tables.
This is the most frequently used SQL command and forms the basis for all data retrieval operations.
Card 2
FROM clause
Answer
The part of a SELECT statement that specifies the table or tables from which the data will be retrieved.
Without the FROM clause, the database would not know where to find the columns specified in the SELECT list.
Card 3
WHERE clause
Answer
A clause used in SELECT, UPDATE, and DELETE statements to filter rows based on a specified condition, returning only those rows where the condition evaluates to true.
The WHERE clause allows you to retrieve a specific subset of data, like finding all employees earning over a certain salary.
Card 4
ORDER BY clause
Answer
A clause used to sort the result set of a SELECT query in ascending (ASC) or descending (DESC) order based on one or more specified columns.
This clause helps organize query results for better readability and analysis, such as listing products from most expensive to least.
Card 5
DISTINCT keyword
Answer
A keyword used in a SELECT statement to eliminate duplicate rows from the result set, ensuring that each returned row is unique across all selected columns.
Use DISTINCT when you only want to see each unique value once, for example, to get a list of all unique cities where customers reside.
Card 6
Column Alias
Answer
A temporary, alternative name given to a column or an expression in the SELECT list of a query, often used to make the output more readable.
Aliases are helpful when column names are long, or when an aggregate function creates a column without a clear name, improving clarity in the result set.
Card 7
INNER JOIN
Answer
A type of JOIN that combines rows from two or more tables based on a related column between them, returning only the rows where there is a match in all joined tables.
An INNER JOIN is like an intersection, showing only the records that exist in both tables according to the specified join condition.
Card 8
LEFT JOIN
Answer
A type of JOIN that returns all rows from the left table and the matching rows from the right table, filling with NULLs in right table columns where no match exists.
Use LEFT JOIN when you want to see all records from one table, even if they don't have corresponding records in another table, such as all customers and their orders if any.
Card 9
RIGHT JOIN
Answer
A type of JOIN that returns all rows from the right table and the matching rows from the left table, filling with NULLs in left table columns where no match exists.
RIGHT JOIN is symmetrical to LEFT JOIN; it ensures all records from the right table are included, showing associated left table data where available.
Card 10
FULL OUTER JOIN
Answer
A type of JOIN that returns all rows when there is a match in either the left or the right table, including rows with no matches in the other table and filling with NULLs.
This join provides a complete picture, showing all data from both tables and indicating where matches occur or are absent.
Card 11
ON clause (for joins)
Answer
The clause used with JOIN operations to specify the condition or criteria for linking rows between tables, typically based on matching column values.
The ON clause defines how tables relate to each other, distinguishing it from a WHERE clause which filters the results after the join.
Card 12
COUNT() aggregate function
Answer
An aggregate function that returns the number of rows that satisfy a specified condition, or the number of non-NULL values in a specified column.
COUNT(*) counts all rows including NULLs, while COUNT(column_name) only counts rows where 'column_name' is not NULL.
Card 13
SUM() aggregate function
Answer
An aggregate function that calculates the total sum of all non-NULL values in a specified numeric column for a group of rows.
SUM is useful for calculating totals like the total sales amount for a product category or the total salary paid to employees.
Card 14
AVG() aggregate function
Answer
An aggregate function that calculates the average (mean) of all non-NULL values in a specified numeric column for a group of rows.
AVG provides a central tendency measure, like the average price of items in an inventory or the average score on an exam.
Card 15
GROUP BY clause
Answer
A clause used with aggregate functions to group rows that have the same values in specified columns into summary rows, allowing aggregates to be calculated for each group.
If you want to find the total sales for each product category, you would GROUP BY the product category column.
Card 16
HAVING clause
Answer
A clause used to filter groups based on a specified condition after the GROUP BY clause has been applied, often used with aggregate functions.
Unlike WHERE, which filters individual rows before grouping, HAVING filters entire groups; for example, showing only product categories with total sales exceeding a certain amount.
Card 17
Subquery
Answer
A query nested inside another SQL query, acting as an input to the outer query, which can return a single value, a list of values, or a table.
Subqueries allow for complex filtering or data retrieval that cannot be achieved with simple WHERE clauses or joins alone, like finding employees who earn more than the average salary.
Card 18
Correlated Subquery
Answer
A type of subquery that depends on the outer query for its values and executes once for each row processed by the outer query.
Correlated subqueries are evaluated row-by-row and can sometimes be less performant than non-correlated subqueries or joins, but are necessary for certain complex conditions.
Card 19
EXISTS operator
Answer
A boolean operator used with subqueries to test for the existence of any rows returned by the subquery, returning true if the subquery returns at least one row.
EXISTS is efficient for checking if related data exists without needing to retrieve the actual data, such as finding customers who have placed at least one order.
Card 20
UNION operator
Answer
A set operator used to combine the result sets of two or more SELECT statements into a single result set, removing duplicate rows by default.
UNION is useful for consolidating data from different tables or different parts of the same table, provided the columns selected are compatible in number and data type.
Card 21
INTERSECT operator
Answer
A set operator that returns only the distinct rows that are present in the result sets of both SELECT statements.
INTERSECT is useful for finding common records between two queries, for example, customers who ordered both product A and product B.
Card 22
LIMIT / TOP clause
Answer
A clause used to restrict the number of rows returned by a SELECT query, typically used to retrieve the first N rows or a specific range of rows.
LIMIT (MySQL, PostgreSQL) or TOP (SQL Server) is crucial for pagination or when you only need a small sample of the data, like the top 10 most expensive products.
Card 23
LIKE operator
Answer
An operator used in the WHERE clause to search for a specified pattern in a column, often used with wildcard characters such as '%' (any sequence) and '_' (any single character).
LIKE is essential for partial string matching, such as finding all names that start with 'Sm' or contain 'son'.
Card 24
IS NULL operator
Answer
An operator used in the WHERE clause to test whether a column's value is NULL, which represents missing or undefined data, as NULL cannot be compared with '=' or '!='.
Using 'column_name = NULL' will always return false; you must use 'column_name IS NULL' to correctly identify rows with missing values.
Card 25
Database Index
Answer
A data structure that improves the speed of data retrieval operations on a database table by allowing the database system to quickly locate specific rows without scanning the entire table.
Indexes are similar to the index in a book, providing quick lookup points for data, but they can slow down data modification operations like INSERT, UPDATE, and DELETE.
Card 26
Clustered Index
Answer
A type of index that physically reorders the rows of a table according to the index key, meaning the data rows themselves are stored in the order of the index.
A table can only have one clustered index because the data rows can only be physically sorted in one way; it is often created automatically on the primary key.
Card 27
Non-clustered Index
Answer
A type of index that does not physically reorder the data rows of a table but instead stores a separate sorted list of index keys with pointers to the actual data rows.
A table can have multiple non-clustered indexes, acting like separate directories that point to the data without altering its physical storage order.
Card 28
First Normal Form (1NF)
Answer
A property of a database table where each column contains atomic (indivisible) values, and there are no repeating groups of columns.
Achieving 1NF means each cell in the table contains a single value and there are no multi-valued attributes stored in a single column, simplifying data manipulation.
Card 29
Second Normal Form (2NF)
Answer
A property of a database table that is in 1NF and all non-key attributes are fully functionally dependent on the entire primary key.
2NF addresses partial dependencies, ensuring that no non-key attribute depends on only a part of a composite primary key, reducing data redundancy.
Card 30
Third Normal Form (3NF)
Answer
A property of a database table that is in 2NF and has no transitive dependencies, meaning non-key attributes do not depend on other non-key attributes.
3NF eliminates transitive dependencies, ensuring that all non-key attributes directly describe the primary key and not another non-key attribute, further minimizing redundancy.