Introduction1. How to Retrieve Data From a Single Table1.1. The Five Clauses of the SELECT Statement1.2. Column Specifications1.3. LIKE and REGEXP Operators1.4. Arithmetic Operators1.5. Column Aliases1.6. Comparison Operators1.7. IS NULL, BETWEEN, IN Operators1.8. AND, OR, NOT Logical Operators1.9. DISTINCT Clause2. How to Retrieve Data from Multiple Tables2.1. The JOIN Clause2.2. Joining More Than Two Tables2.3. The OUTER JOIN Clause2.4. How to Code a UNION3. Using Functions3.1. Date Functions3.2. Numeric Functions3.3. String Functions4. How to Insert, Update, Delete Data in Tables4.1. The INSERT Clause With a Column List4.2. The INSERT Clause Without a Column List4.4. The UPDATE Clause With a Column List4.4. The DELETE Clause5. Summary Queries and Aggregate Functions5.1. Aggregate Functions5.2. Grouping Data5.3. Simple GROUP BY Query5.4. Improving the GROUP BY Query5.5. Using the HAVING Clause5.5. Using the HAVING and WHERE Clauses Together5.6. COUNT(column_name) and COUNT(*)5.7. Using the DISTINCT Statement6. Working With Subqueries6.1. The Subquery In a SELECT Statement6.2. The Subquery in an UPDATE statement6.3. Create a Duplicate Table From An Existing Table 6.4. The Subquery In a Delete Statement7. SQL Views7.1. SQL View Explained7.2. Benefits of Using Views7.3. Views That Allow UPDATE Statements8. SQL Indexes8.1. SQL Indexes Explained8.2. Clustered vs. Non-clustered Indexes8.3. Create an Index in Workbench Using an ERD8.4. How to Manually Add an Index to an Existing TableGlossaryIndex

Glossary

Aggregate FunctionSummary Queries and Aggregate Functions
Performs an operation on a set of records in a column and returns a single value.
Arithmetic OperatorsArithmetic Operators
Arithmetic operators ARE: * (multiplication), / (division), DIV (integer division), % (MOD) or remainder, + (addition), - (subtraction). These operators can be used in the SELECT, WHERE, and ORDER BY clauses. Operators are evaluated in the same way as arithmetic in other contexts.
AVG functionSummary Queries and Aggregate Functions
Returns the average of the non-null columns in the expression.
BETWEEN operatorIS NULL, BETWEEN, IN Operators
The BETWEEN operator is similar to >= and
CEILING functionNumeric Functions
Returns the next highest whole number no matter what the decimal point.
Column AliasesColumn Aliases
A column alias provides a way to create a clean or more descriptive header for a results set. A column alias cannot be used in a SELECT, WHERE, GROUP BY or HAVING clause due to the order of execution. You must refer to the original column name.
Column SpecificationsColumn Specifications
A column specification may be derived from a base table. Or it my be a calculated value as a result of an arithmetic expression or a function.
Comparison OperatorsComparison Operators
The comparison operators are = (equals), (greater than), =, (not equal), != (not equal). Comparison operators compare two expressions. The result of a comparison results to true or false. Comparison operators are not case sensitive and are used with text and dates as well as numbers.
Compound conditionAND, OR, NOT Logical Operators
When more than one logical operator (AND, OR, NOT) is used in the WHERE clause.
CONCAT functionString Functions
Combines a list of strings into a single string.
COUNT functionSummary Queries and Aggregate Functions
The number of the non-null columns in the expression.
CURRENT_DATE functionDate Functions
Returns current local date.
CURRENT_TIME functionDate Functions
Returns current local time
DATE functionDate Functions
Extracts the date from date/time input. If time is included it is dropped.
DELETE clauseThe DELETE Clause
SQL clause that deletes data from a table.
DISTINCT clauseDISTINCT Clause
The DISTINCT clause removes duplicate rows from a query.
FLOOR functionNumeric Functions
Returns the next lowest whole number no matter what the decimal point.
FROM clauseThe Five Clauses of the SELECT Statement
Specifies the base table(s) from which results will be retrieved.
GROUP BY clauseGrouping Data
Groups rows of a result set based on columns or expressions separated by commas.
HAVING clauseUsing the HAVING Clause
The HAVING CLAUSE allows you to use an aggregate function as a filter. This is not allowed in a WHERE clause.
IN operatorIS NULL, BETWEEN, IN Operators
The IN operator tests whether an expression is equal to a value or values in a list of expressions. The order of the items in the list does not matter. You can use the NOT operator to test for items not in the list. The IN clause may be used with a subquery.
IndexesSQL Indexes
A SQL index is like the index of a book. It speeds up the retrieval of a record. The relational database management system (RDBMS) can retrieve a record with the index key instead of having to perform a table scan.
INSERT clauseThe INSERT Clause With a Column List
SQL Clause used to insert data into a table.
IS NULL functionIS NULL, BETWEEN, IN Operators
Null values indicate an unknown or non-existent value and is different from an empty string (‘ ‘). To test for a null value you use the IS NULL clause. The test for a value use IS NOT NULL clause
JOIN (OUTER) clauseThe OUTER JOIN Clause
An outer join will return all the rows from one table and only the rows from the other table that match the join condition
JOIN clause The JOIN Clause
A JOIN clause allows you to access data from two or more tables in a query.
LEFT functionString Functions
Returns a substring starting from the left side of the string.
LIKE operator LIKE and REGEXP Operators
The LIKE keyword is used with the WHERE clause. The LIKE keyword and can use two symbols as wildcards. The percent ( % ) symbol matches any number of characters and the underscore ( _ ) matches a single character.
LIMIT clauseThe Five Clauses of the SELECT Statement
Specifies the number of rows to be returned.
Logical Operators: AND, OR, NOTAND, OR, NOT Logical Operators
Logical operators are used in the WHERE clause You may use multiple logical operators in a WHERE clause to create a compound condition. The order of evaluation when multiple operators are used is shown in the table above.
LTRIM functionString Functions
Removes leading spaces from a string.
MIN functionSummary Queries and Aggregate Functions
The lowest value off the non-null columns in the expression
NOW functionDate Functions
Returns current local date and time.
ORDER BY clauseThe Five Clauses of the SELECT Statement
SQL clause that orders a result set.
REGEXP operator LIKE and REGEXP Operators
REGEXP operator allows you to do more complex pattern matching than a LIKE keyword/ Some version of REGEXP exists in many computer languages. Refer to the “LIKE and REGEXP” handout for a full list of examples.
RIGHT function
Returns a substring starting from the right side of the string.
ROUND functionNumeric Functions
Rounds to the decimal specified.
RTRIM functionString Functions
Removes trailing spaces from a string.
SELECT clauseThe Five Clauses of the SELECT Statement
Specifies the columns that will appear in a SQL query result set.
SubqueryWorking With Subqueries
A subquery is a SELECT statement coded within another SELECT statement.
SUM functionSummary Queries and Aggregate Functions
The total of the non-null columns in the expression.
Summary QueryAggregate Functions
A query that uses at least one aggregate function.
TRIM functionString Functions
Removes leading and trailing spaces from a string.
TRUNCATE functionNumeric Functions
Returns the number truncated to the precision specified.
UNION clauseHow to Code a UNION
A UNION combines the results of two or more queries into a single result set.
UPDATE clause
SQL clause that updates data in a table.
UTC_DATE functionDate Functions
Returns current UTC date.
UTC_time functionDate Functions
Returns current UTC time.
VIEWSSQL Views
A SQL view is a SELECT statement that is stored as a database object.
WHERE functionThe Five Clauses of the SELECT Statement
Specifies any conditions for the results set (filter).