Introduction | SQL Flashcards
SQL, Structured Query Language, is a programming language designed to manage data stored in relational databases. SQL operates through simple, declarative statements. This keeps data accurate and secure, and it helps maintain the integrity of databases, regardless of size.
A database row represents a single, implicitly structured data record in a table. In simple terms, a database table can be thought of as consisting of rows and columns.
A database column is a set of data values of a particular simple type, one value for each row of the database. A column may contain text values, numbers, or even pointers to files in the operating system.
A primary key, sometimes labeled as the unique identifier, is a specific choice of a set of columns that uniquely identify a row in a relational table. Simply put, a primary key is the attributes which identify a record.
ALTER TABLE lets you add columns to a table in a database.
CASE statements are used to create different outputs (usually in the
SELECT statement). It is SQL’s way of handling if-then logic.
CREATE TABLE creates a new table in the database. It allows you to specify the name of the table and the name of each column in the table.
DELETE statements are used to remove rows from a table.
An INNER JOIN will combine rows from different tables if the join condition is true.
INSERT statements are used to add a new row to a table.
IS NULL / IS NOT NULL
IS NULL and
IS NOT NULL are operators used with the
WHERE clause to test for empty values.
An outer join will combine rows from different tables even if the join condition is not met. Every row in the left table is returned in the result set, and if the join condition is not met, then
NULL values are used to fill in the columns from the right table.
SELECT DISTINCT specifies that the statement is going to be a query that returns unique values in the specified column(s).
UPDATE statements allow you to edit rows in a table.
WITH clause lets you store the result of a query in a temporary table using an alias. You can also define multiple temporary tables using a comma and with one instance of the
WITH clause is also known as common table expression (CTE) and subquery factoring.
AND operator allows multiple conditions to be combined. Records must match both conditions that are joined by
AND to be included in the result set.
Columns or tables in SQL can be aliased using the
AS clause. This allows columns or tables to be specifically renamed in the returned result set.
OR operator allows multiple conditions to be combined. Records matching either condition joined by the
OR are included in the result set.
% wildcard can be used in a
LIKE operator pattern to match zero or more unspecified character(s).
SELECT * statement returns all columns from the provided table in the result set.
_ wildcard can be used in a
LIKE operator pattern to match any single unspecified character.
ORDER BY clause can be used to sort the result set by a particular column either alphabetically or numerically. It can be ordered in ascending (default) or descending order with
LIKE operator can be used inside of a
WHERE clause to match a specified pattern.
Unique values of a column can be selected using a
BETWEEN operator can be used to filter by a range of values. The range of values can be text, numbers or date data.
LIMIT clause is used to narrow, or limit, a result set to the specified number of rows.
Column values in SQL records can be
NULL, or have no value. These records can be matched (or not matched) using the
IS NULL and
IS NOT NULL operators in combination with the
WHERE clause is used to filter records (rows) that match a certain condition.
GROUP BY and
ORDER BY clauses can reference the selected columns by number in which they appear in the
SUM() aggregate function takes the name of a column as an argument and returns the sum of all the value in that column.
MAX() aggregate function in SQL takes the name of a column as an argument and returns the largest value in a column.
COUNT() aggregate function in SQL returns the total number of rows that match the specified criteria.
GROUP BY clause will group records in a result set by identical values in one or more columns. It is often used in combination with aggregate functions to query information of similar records. The
GROUP BY clause can come after
WHERE but must come before any
ORDER BY or
MIN() aggregate function in SQL returns the smallest value in a column.
AVG() aggregate function returns the average value in a column.
HAVING clause is used to further filter the result set groups provided by the
GROUP BY clause.
HAVING is often used with aggregate functions to filter the result set groups based on an aggregate property.
Aggregate functions perform a calculation on a set of values and return a single value:
ROUND() function will round a number value to a specified number of places. It takes two arguments: a number, and a number of decimal places. It can be combined with other aggregate functions