Introduction | SQL Flashcards
SQL
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.
row
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.
column
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.
primary key
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
ALTER TABLE lets you add columns to a table in a database.
CASE
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
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
DELETE statements are used to remove rows from a table.
INNER JOIN
An INNER JOIN will combine rows from different tables if the join condition is true.
INSERT
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.
OUTER JOIN
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
SELECT DISTINCT specifies that the statement is going to be a query that returns unique values in the specified column(s).
UPDATE
UPDATE statements allow you to edit rows in a table.
WITH
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 keyword.
The WITH clause is also known as common table expression (CTE) and subquery factoring.
AND
The 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.
AS
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
The OR operator allows multiple conditions to be combined. Records matching either condition joined by the OR are included in the result set.
%
The % wildcard can be used in a LIKE operator pattern to match zero or more unspecified character(s).
SELECT
The SELECT * statement returns all columns from the provided table in the result set.
_
The _ wildcard can be used in a LIKE operator pattern to match any single unspecified character.
ORDER BY
The 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 ASC/DESC.
LIKE
The LIKE operator can be used inside of a WHERE clause to match a specified pattern.
DISTINCT
Unique values of a column can be selected using a DISTINCT query.
BETWEEN
The BETWEEN operator can be used to filter by a range of values. The range of values can be text, numbers or date data.
LIMIT
The LIMIT clause is used to narrow, or limit, a result set to the specified number of rows.
NULL
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.
WHERE
The WHERE clause is used to filter records (rows) that match a certain condition.
Column References
The GROUP BY and ORDER BY clauses can reference the selected columns by number in which they appear in the SELECT statement.
SUM()
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()
The MAX() aggregate function in SQL takes the name of a column as an argument and returns the largest value in a column.
COUNT()
The COUNT() aggregate function in SQL returns the total number of rows that match the specified criteria.
GROUP BY
The 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 FROM or WHERE but must come before any ORDER BY or LIMIT clause.
MIN()
The MIN() aggregate function in SQL returns the smallest value in a column.
AVG()
The AVG() aggregate function returns the average value in a column.
HAVING
The 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
Aggregate functions perform a calculation on a set of values and return a single value:
ROUND()
The 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