Which of the following select clauses could you use to assign an alias to the invoice_total column?

Relational Databases are made of tables
Tables are made of rows and columns
Rows and columns are sometimes called records and fields, respectively

CellIntersection of the rows and columnsColumnRepresents some sort of entity, like the amount of an invoiceComposite primary keyWhen a primary key uses two or more columnsConstraintWhen assigning column attributes, a _constraint_ restricts the type of data that can be stored in a column. For example, NOT NULL and UNIQUE.Data typeDetermines the type of information that is stored in the column
Try to assign the data type that minimizes the use of disk storage because that improves the performance of queries later.

• CHAR, VARCHAR
• INT, DECIMAL
• FLOAT
• DATE

Statements tha work with the data in a db

• SELECT - gets data from one or more tables
• INSERT - adds new rows to a table
• UPDATE - changes existing rows in a table
• DELETE - deletes existing rows from a table

Statements that create databases and work with the objects within a db (usually used by db admins)

• CREATE DATABASE - creates a new db on the server
• CREATE TABLE - creates a new table in a db
• CREATE INDEX - creates a new index for a table
• ALTER TABLE - changes the definition of an existing table
• ALTER INDEX - changes the structure of an existing index
• DROP DATABASE - deletes an existing db and all of its tables
• DROP TABLE - deletes an existing table
• DROP INDEX - deletes an existing index

• Table
• View
• Sequence
• Index
• Synonym

Building queries

Best practice to build queries one clause at a time.

Statements

Example:

WHERE terms_id IN (1, 2, 3);

WHERE vendor_state NOT IN ('CA', 'NV', 'OR');

BETWEEN - used in WHERE clause. Compares the value of the test expression to the range of values specified in the BETWEEN phrase. If the value falls within this range, it is included in the results. You can use the NOT operator with this.

Example:

WHERE invoice BETWEEN '2018-06-01' AND '2018-06-30';

WHERE  vendor_zip_code NOT BETWEEN 93600 AND 93799;

LIKE Wildcards:

REGEXP special characters/constructs

Example
WHERE vendor_city REGEXP '[A-Z][AEIOU]N$' - returns any vendor_city that ends with any letter, then a vowel, then the letter 'n'

**** or **** -

:

    IS NULL - used in **WHERE** clause. Can use IS NOT NULL too.
        Example:
            **SELECT** * FROM null_sample 
            **WHERE** invoice_total = 0;

            **SELECT** * 
            FROM null_sample 
            **WHERE** invoice_total IS NOT NULL;


ORDER BY - the clause that specifies the sort order for the rows in the result set. ASC is the default. 
    Can use column names, ASC, DESC, expressions, alias, column positions.
    To sort by more than one column, you list the values in the ORDER BY clause in the order that you want them sorted by. Called a nested sort. 
        Example:
            **SELECT** vendor_name,
                CONCAT(vendor_city, ', ', vendor_state, ' ', vendor_zip_code) AS address 
            FROM vendors 
            ORDER BY vendor_name DESC;

            **SELECT** vendor_name,
                CONCAT(vendor_city, ', ', vendor_state, ' ', vendor_zip_code) AS address
            FROM vendors
            ORDER BY CONCAT(vendor_contact_last_name, vendor_contact_first_name);

LIMIT - the clause that specifices the maximum number of rows or the range of rows (using offset) that are returned in the result set. Usually after the ORDER BY clause. 
    LIMIT [offset, ] row count
    The offset is 0-indexed, so the following clause returns 5 rows that start at the index 3:
        Example: 
            **SELECT** invoice_id, vendor_id, invoice_total
            FROM invoices
            ORDER BY invoice_id
            LIMIT 2, 3;

**SELECT** filters the columns, **WHERE** filters the rows 

    Model

    **SELECT** column_1, column_2, column_3 AS new_column_name
    FROM table_1
    WHERE column_1 - column_2 > 0
    ORDER BY column_1 

SELECT

Syntax

SELECT select_list 
[FROM table_source]
[WHERE search_condition]
[ORDER BY order_by_list]
[LIMIT row_limit]

Testing

Use SELECT clauses without a FROM clause to test expressions and functions.

SELECT CURRENT_DATE,
   DATE_FORMAT(CURRENT_DATE, '%m/%d/%y') AS 'MM/DD/YY',
   DATE_FORMAT(CURRENT_DATE, '%e-%b-%Y') AS 'DD-Mon-YYYY';

SELECT 12345.6789 AS value,
   ROUND(12345.6789) AS nearest_dollar,
   ROUND(12345.6789, 1) AS nearest_dime;

DISTINCT - use DISTINCT if you do not want the results table to include duplicate rows.

Column specifications

Base table value All columns = * Column name = column_name Calculated value Result of a calculation = arithmetic expression Result of a function = functions (CONCAT, etc.)

Arithmetic operators

* / DIV - integer division % (MOD) + -

Logical operators

AND and OR - combine two or more search conditions in to a compound condition

Example: WHERE vendor_state = 'NJ' AND vendor_city = 'Springfield'; WHERE vendor_state = 'NJ' OR vendor_city = 'Springfield'; NOT - used to negate a search condition

Example: WHERE NOT vendor_state = 'CA'; Vendors in CA are not returned Order of precedence

1. NOT
2. AND
3. OR

Functions

Functions are used in the SELECT clause

CONCAT - used to join strings.

CONCAT(string1[, string2]...)

SELECT  vendor_name,
   CONCAT(vendor_city, ', ', vendor_state, ' ', vendor_zip_code)
      AS address 
FROM vendors;

LEFT - used to extract the x amount of characters from the left of the results of the query

LEFT(column_name, x) LEFT(vendor_contact_first_name, 1)

SELECT  vendor_contact_first_name, vendor_contact_last_name,
   CONCAT(LEFT(vendor_contact_first_name, 1),
      LEFT(vendor_contact_last_name, 1)) AS initials
FROM vendors;

DATE_FORMAT - specify the format of a date. use the % signto identify a format code.

For example, '%m/%d/%y' returns 04/08/18

ROUND - rounds the value of the column to the nearest place, depending on the optional parameter. ROUND(nearest_penny, 2) returns 12.21 ROUND(nearest_dime, 1) returns 12.2 CURRENT_DATE - returns the current date

JOINS

Retrives data from two tables and joins it together in a single result set

INNER JOIN - most common type of join. Rows from the two tables in the join are included in the result table only if their related columns match. These columns are specified in the FROM clause of the SELECT statement.

SELECT vendor_name, invoice_number, invoice_date, invoice_total (1)
FROM vendors INNER JOIN invoices (2)
   ON vendors.vendor_id = invoices.vendor_id (3)
WHERE invoice_total >= 500 (4)
ORDER BY vendor_name, invoice_total DESC; (5)

1. SELECTs 4 rows FROM the vendors table that it will use in the result set
2. Defines the two tables that it wants to use to create an INNER JOIN (vendors and invoices)
3. Defines the common column(s) that must exist in each table (vendor_id) If a vendor in the vendors tables does not have an invoice listed in the invoices table, then it is not in the results table for this INNER JOIN. For example, a new vendor that has not yet generated an invoice.
4. Filters the overlapping column by a value
5. Sorts the column

Retrieve data from multiple tables

INNER JOIN

Combines columns from 2 or more tables into a result set based on the join conditions For inner joins, only the two rows that satisfy the join condition are included in the result set

How to code inner join:

Code the names of the 2 tables in the FROM clause along with the JOIN keyword and an ON phrase that specifies the join condition

SELECT invoice_number, vendor_name              (1)
FROM vendors INNER JOIN invoices                (2)
   ON vendors.vendor_id = invoices.vendor_id    (3)
ORDER BY invoice_number;                        (4)

1. Standard SELECT clause
2. FROM clause that names the two tables that are joined INNER keyword is optional
3. ON phrase that names the columns where the tables are joined and how they are compared columns preceded by table_name. are called 'qualified columns' because you have explain which table they came from
4. Standard ORDER BY clause

Implicit syntax

Not used often, prefer Explicit syntax outlined above. Code the tables in the FROM clause separated by commas.

SELECT invoice_number, vendor_name
FROM vendors v, invoices i  (1)
WHERE v.vendor_id = i.vendor_id
ORDER BY invoice_number;

1. Table names are separated by commas instead of 'INNER JOIN'

Table aliases

An alternative table name thats typically just a letter or two. Like a variable.
Makes the statement easier to read.

SELECT  invoice_number, vendor_name, invoice_due_date,
   invoice_total - payment_total - credit_total AS balance_due
FROM vendors v JOIN invoices i  (1)
   ON v.vendor_id = i.vendor_id    (2)
WHERE invoice_total - payment_total - credit_total > 0
ORDER BY invoice_due_date DESC;

1. FROM clause that names the two tables and assigns them aliases
2. use the aliases to qualify the columns

JOIN to a table in another db

Tables are organized into databases, which are also called schemas

SELECT vendor_name, customer_last_name, customer_first_name,
   vendor_state AS state, vendor_city AS city
FROM vendors v
   JOIN om.customers c (1)
   ON v.vendor_zip_code = c.customer_zip
ORDER BY state, city;

1. 'om' is the name of the db, 'customers' is the name of the table, 'c' is the name of the table alias

Compound JOIN

Create two or more comparisons in a join condition using the AND or OR operators

SELECT customer_first_name, customer_last_name
FROM customers c JOIN employees e 
   ON c.customer_first_name = e.first_name
   AND c.customer_last_name = e.last_name; (1)

1. Return first and last names of all customers in the Customers table whose first and last names also exist in the Employees table.

Self JOIN

Joins a table to itself. Useful for retrieving data that can't be retrieved any other way.

SELECT DISTINCT v1.vendor_name, v1.vendor_city,
   v1.vendor_state
FROM vendors v1 JOIN vendors v2     (1)
   ON v1.vendor_city = v2.vendor_city AND      (2)
      v1.vendor_state = v2.vendor_state AND
      v1.vendor_name = v2.vendor_name
ORDER BY v1.vendor_state, v1.vendor_city;

1. Use aliases to distinguish one occurence of the table from the other
2. Returns rows from the vendors table where the vendor is in a city and state that has at least one other vendor.
   It does not return a vendor if that vendor is th only vendor in that city and state.

JOIN more than two tables

This example joins tables based on the relationship between the primary key of one table and a foreign key of the other table.
The account_number column is the primary key of the General_Ledger_Accounts table and a foreign key of the Invoice_Line_Items table

SELECT vendor_name, invoice_number, invoice_date,
   line_item_amount, account_description
FROM vendors v
   JOIN invoices i
      ON v.vendor_id = i.vendor_id
   JOIN invoice_line_items li
      ON i.invoice_id = li.invoice_id
   JOIN general_ledger_accounts gl
      ON li.account_number = gl.account_number
WHERE invoice_total - payment_total - credit_total > 0
ORDER BY vendor_name, line_item_amount DESC;

OUTER JOINS

Returns all of the rows from one of the tables involved in the join, plus unmatched rows in the LEFT or RIGHT table, regardless of whether the join condition is true

LEFT or RIGHT keyword

LEFT - the result set includes all the rows from the first (left) table RIGHT - the result set includes all the rows from the second (right) table

The following joins the vendors and invoices tables, and includes rows from the vendor column even if no matching invoices are found. If none are found, null vbalues are returned for those columns.

SELECT vendor_name, invoice_number, invoice_total
FROM vendors LEFT JOIN invoices
   ON vendors.vendor_id = invoices.vendor_id 
ORDER BY vendor_name;

Combination of INNER and OUTER JOIN

SELECT department_name, last_name, project_number
FROM departments d  
   JOIN employees e
      ON d.department_number = e.department_number
   LEFT JOIN projects p 
      ON e.employee_id = p.employee_id
ORDER BY department_name, last_name;

USING keyword

Use the USING clause instead of an ON clause during an equijoin to specify the join. To join tables by multiple columns, put the multiple columns in the parentheses, separated by a column

equijoin/equi-join - When you use the equal operator to join two tables on a common column. Common for the columns that are being compared to have the same name.

Example 1:

SELECT invoice_number, vendor_name
FROM vendors
    JOIN invoices USING (vendor_id) (1)
ORDER BY invoice_number;

(1) USING (vendor_id) replaced 'ON vendors.vendor_id = invoices.vendor_id'

Example 2:

SELECT department_name, last_name, project_number
FROM departments
    JOIN employees USING (department_number)    (1)
    LEFT JOIN projects USING (employee_id)      (2)
ORDER BY department_name;

(1) INNER JOIN between departments and employees on the department_number column (2) LEFT JOIN between employees and projects using the employee_id column

NATURAL keyword

You don't specific the column that's used to join the two tables. The db joins the two tables based on all columns in the two tables that have the same name.

Example:

SELECT department_name AS dept_name, last_name, project_number
FROM departments
    NATURAL JOIN employees                      (1)
    LEFT JOIN projects USING (employee_id)      (2)
ORDER BY department_name;

(1) JOINs departments and employees on columns where they intersect (2) Creates a LEFT JOIN between employees and projects on employee_id column

CROSS JOINs

Produces a result set that includes each row from the first table joined with each row from the second table. Result set is called a Cartesian product

Example:

SELECT departments.department_number, department_name, employee_id, last_name
FROM departments CROSS JOIN employees   (1)
ORDER BY departments.department_number;

(1) JOIN the 4 columns together in a result set from left to right, not based on where they intersect

UNIONs

Used to connect two or more SELECT statements. The result set of each SELECT statement must have the same number of columns, and the data types of the corresponding columns in each table must be compatible.

Combines data from two or more result sets, instead of base tables. To sort the result of a UNION, add an ORDER BY cluase after the last SELECT statement. By default, UNIONs eliminates duplicate rows. To include dupes, add ALL keyword. Column names in the final result set are taken from the first SELECT clause.

Example:

    SELECT invoice_number, vendor_name, '33% Payment' AS payment_type,
    invoice_total AS total, invoice_total * 0.333 AS payment
    FROM invoices JOIN vendors
        ON invoices.vendor_id = vendors.vendor_id
    WHERE invoice_total > 10000
UNION
    SELECT invoice_number, vendor_name, '50% Payment' AS payment_type,
        invoice_total AS total, invoice_total * 0.5 AS payment
    FROM invoices JOIN vendors
        ON invoices.vendor_id = vendors.vendor_id
    WHERE invoice_total BETWEEN 500 AND 10000
UNION
    SELECT invoice_number, vendor_name, 'Full amount' AS payment_type,
        invoice_total AS total, invoice_total AS payment
    FROM invoices JOIN vendors
        ON invoices.vendor_id = vendors.vendor_id
    WHERE invoice_total < 500
ORDER BY payment_type, vendor_name, invoice_number;

Example:
Combining result sets from the same table.

    SELECT 'Active' AS source, invoice_number, invoice_date, invoice_total  (1)
    FROM invoices 
    WHERE invoice_total - payment_total - credit_total > 0
UNION 
    SELECT 'Paid' AS source, invoice_number, invoice_date, invoice_total    (2)
    FROM invoices 
    WHERE invoice_total - payment_total - credit_total <= 0
ORDER BY invoice_total DESC;

(1) Rows that have a balance due. Adds column named 'source' to indicate that each row is 'Active' (2) Rows that are paid in full. Adds column named 'source' to indicate that each row is 'Paid'

Simulate a FULL OUTER JOIN with a UNION

Simulate a FULL OUTER JOIN by creating a UNION that combines the result sets for a left outer join and a right outer join. NULL is displayed in rows with no values

Example:

    SELECT department_name AS dept_name, d.department_number AS d_dept_no,
        e.department_number AS e_dept_no, last_name
    FROM departments d
        LEFT JOIN employees e
        ON d.department_number = e.department_number
UNION
    SELECT department_name AS dept_name, d.department_number AS d_dept_no,
        e.department_number AS e_dept_no, last_name
        FROM departments d
            RIGHT JOIN employees e
            ON d.department_number = e.department_number
ORDER BY dept_name;

Create

Create a copy of the table to do testing with the following statement:

CREATE TABLE invoices_copy AS
SELECT *
FROM invoices;

Whey you create tables like this, MySQL copies only the column defnitions and data, not the primary keys, foreign keys, or indexes.

INSERT

Usually use this statement to add a single row to a table. Can also add multiple rows. You name the table on the INSERT clause, then add an optional list of columns, then add the new row

Example

INSERT INTO invoices (1)
   (vendor_id, invoice_number, invoice_total, terms_id, invoice_date, invoice_due_date) (2)
VALUES (3)
   (97, '456789', 8344.50, 1, '2018-08-01', '2018-08-31');

1. INSERT INTO clause that names the base table

2. Optional list of columns
   If you do not include a column list in the INSERT INTO clause, you must specify the values in the same order as in the table, and there must be a value for each column. To add multiple rows, specify multiple (value) lines in the INSERT clause.
   Use NULL to insert a NULL. Use DEFAULT to insert the default value or if a value is defined as autoincrement.

3. VALUES clause that lists the values to add to the new row

   You can omit columns that have default valudes, accept null values, or are automatically generated.

Subquery in INSERT statement

A subquery is a SELECT statement that is coded within another SQL statement.

Example
The subquery specifies the values for the new rows by selecting these values from another table.

INSERT INTO invoice_archive
   (invoice_id, vendor_id, invoice_number, invoice_total, credit_total, payment_total, terms_id, invoice_date, invoice_due_date)
SELECT 
    invoice_id, vendor_id, invoice_number, invoice_total, credit_total, payment_total, terms_id, invoice_data, invoice_due_date
FROM invoices
WHERE invoice_total - payment_total - credit_total = 0;

When you include a column list, you must list the columns in the same sequence as the SELECT clause of the subquery.
You can omit auto increment columns, columns that are defined with default values, and columns that allow null values.

UPDATE

Use the UPDATE statement to modify the data in one or more rows in the table.

Example Update two columns for a single row

UPDATE invoices                     (1)
SET payment_date = '2018-09-21',    (2)
   payment_total = 19351.18    
WHERE invoice_number = '97/522';    (3)

1. The UPDATE clause names the table to be updated.
2. The SET clause names the columns that you are updating and their new values
   You must update the columns with values that are compatible with the data type fo the column, including NULL and DEFAULT.
3. The WHERE clause specifies the condition a row must meet to be updated. Also called the search condition.

   Best practices The WHERE clause should refer to a primary or a foreign key. Before you execute an UPDATE statement, you should create a test SELECT statement with the same search condition in the WHERE clause. If the SELECT clause is correct, then you can copy its WHERE clause into the UPDATE statement.

Subquery in UPDATE statement

Create a subquery in the WHERE clause of an UPDATE statement to identify the rows that you want to update.

Example

UPDATE invoices 
SET terms_id = 1
WHERE vendor_id = 
   (SELECT vendor_id
    FROM vendors
    WHERE vendor_name = 'Pacific Bell');

A subquery is used in the WHERE clause to identify the invoices that are updated. Returns the vendor_id value for the vendor in the vendors table with the name "Pacific Bell".
In subqueries, the SELECT clause contains a column that the UPDATE clause table and the FROM subquery clause have in common.

DELETE

Use the DELETE statement to remove one or more rows from a table. A foreign key constraint may prevent you from deleting a row. If that is the case, you can delete the row only if you delete all child rows for that row first.

DELETE FROM invoice_line_items  (1)
WHERE invoice_id = 12;          (2)

1. DELETE clause specifies the name of the table and must include FROM keyword
2. The WHERE clause specifies a serch condition that identifies the rows to delete.
   This row is optional, but you should include it to ensure that you do not delete the entire table.

Best practices Before you execute an DELETE statement, you should create a test SELECT statement with the same search condition in the WHERE clause. If the SELECT clause is correct, then you can copy its WHERE clause into the DELETE statement.

Subquery in DELETE statement

To delete a row from the vendors table that has related rows in the invoices table, you must start by deleting the rows in the invoice_line_items table for the vendor's invoices, using a subquery.

DELETE FROM invoice_line_items  (1)
WHERE invoice_id IN             (2)
   (SELECT invoice_id
   FROM invoices
   WHERE vendor_id = 115);

1. DELETE clause specifies the name of the table and must include FROM keyword
2. WHERE clause uses a subquery to select all invoice IDs for the vendor from the invoices table. Then, the DELETE statement deletes all the invoice line items with those IDs.

Summary queries

Use aggregate functions that operate on a series of values and return a singe summary value. Sometimes called column functions because they typically operate on the values in columns. A query that contains one or more aggregate functions is typically referred to as a summary query.

Most common aggregate functions

• ALL keyword is a default. This includes all values except for null values.
• COUNT(*) includes null values
• Use DISTINCT if you do not want duplicate values included.
  • Most often, you will use DISTINCT with the COUNT function.
  • It does not have an effect on MIN or MAX

NOTE: The expression in the Result column is typically just a column name.

Examples

SELECT COUNT(*) AS number_of_invoices,
   SUM(invoice_total - payment_total - credit_total) AS total_due   (1)
FROM invoices
WHERE invoice_total - payment_total - credit_total > 0;             (2)

1. SUM function calculates the balance due of an invoice using 3 columns and an alias. The result is a single value that represents the total amount due for all the selected invoices.
2. The WHERE clause filters these to show where a balance is due.

SELECT 'After 1/1/2018' AS selection_date,
   COUNT(*) AS number_of_invoices,                      (1)
   ROUND(AVG(invoice_total), 2) AS avg_invoice_amt,     (2)
   SUM(invoice_total) AS total_invoice_amt              (3)
FROM invoices
WHERE invoice_date > '2018-01-01';

1. Counts the number of rows that pass the WHERE clause filter
2. Calulates the average amount of invoices that pass the WHERE clause filter
3. Calcualtes the total amount of invoices that pass the WHERE clause filter

SELECT COUNT(DISTINCT vendor_id) AS number_of_vendors,  (1)
	COUNT(vendor_id) AS number_of_invoices,             (2)
    ROUND(AVG(invoice_total), 2) AS avg_invoice_amt,    (3)
    SUM(invoice_total) AS total_invoice_amt             (4)
FROM invoices
WHERE invoice_date > '2018-01-01';

1. Counts the number of vendors that have a unique vendor_id value that also pass the WHERE clause filter
2. Counts every invoice that passes the WHERE clause filter. This function is used for clarity - you would normally use use COUNT(*).
3. Rounds the average of the invoice_total column to 2 decimal places and uses an alias
4. Sums the invoice_total column as an alias

GROUP BY and HAVING

The GROUP BY clause determines how the selected rows are grouped, and the HAVING cluase determines which groups are included in the final results. In GROUP BY, you can list more than one expression, separated by commas, and they are grouped in ascending sequence. These clauses are included after the WHERE clause and before the ORDER BY clause.

When you use GROUP BY, a single row is returned for each unique set of values in the grouped columns. For example, if a result set is grouped by the vendor_id column, only one row is returned for each vendor, and that vendor is summarized by the aggregate functions that are included in the SELECT clause.

Examples

SELECT vendor_id, ROUND(AVG(invoice_total), 2) AS average_invoice_amount
FROM invoices
GROUP BY vendor_id                      (1)
HAVING AVG(invoice_total) > 2000        (2)
ORDER BY average_invoice_amount DESC;

1. Groups the columns in the SELECT clause by vendor_id
2. Specifies a search condition for a group or an aggregate.

SELECT vendor_name, vendor_state,
   ROUND(AVG(invoice_total), 2) AS average_invoice_amount              (1)
FROM vendors JOIN invoices ON vendors.vendor_id = invoices.vendor_id
GROUP BY vendor_name        (2)
HAVING AVG(invoice_total) > 2000
ORDER BY average_invoice_amount DESC;

1. When you have an aggregate in the SELECT clause, the aggregate is calculated for each group specified by the GROUP BY clause.
2. Groups the columns in the SELECT clause by vendor_name

SELECT vendor_state, vendor_city, COUNT(*) AS invoice_qty,  (1)
   ROUND(AVG(invoice_total), 2) AS invoice_avg              (2)
FROM invoices JOIN vendors
   ON invoices.vendor_id = vendors.vendor_id                (3)
GROUP BY vendor_state, vendor_city                          (4)
HAVING COUNT(*) >= 2                                        (5)
ORDER BY vendor_state, vendor_city;

1. Use COUNT(*) to count the total number of invoices for each row in the result table
2. Round the average of the invoice total to 2 decimal places and use an alias
3. JOIN the tables on the vendor_id row
4. Group the columns in the SELECT clause by vendor_state, then vendor_city
5. The HAVING clause filters the result set to include only the state and city groups that have at least 2 invoices

HAVING clause vs. WHERE clause

WHERE clauses are applied to every row. HAVING are applied to each group of rows.

Example

SELECT vendor_name,
	COUNT(*) AS invoice_qty,                        (1)
    ROUND(AVG(invoice_total), 2) AS invoice_avg     (2)
FROM vendors JOIN invoices
	ON vendors.vendor_id = invoices.vendor_id
GROUP BY vendor_name                                (3)
HAVING AVG(invoice_total) > 500                     (4)
ORDER BY invoice_qty DESC;

1. Calculates an invoice count for the vendor_name column (that is listed in the GROUP BY column)
2. Calculates an average invoice amount
3. Groups the invoices in the invoice table by vendor name
4. Limits the groups in the result set to those that have an average invoice total greater than 500

SELECT vendor_name,
	COUNT(*) AS invoice_qty,                        (1)
    ROUND(AVG(invoice_total), 2) AS invoice_avg     (2)
FROM vendors JOIN invoices
	ON vendors.vendor_id = invoices.vendor_id
WHERE invoice_total > 500                           (3)
GROUP BY vendor_name                                (4)
ORDER BY invoice_qty;

1. Calculates an invoice count for the vendor_name column (that is listed in the GROUP BY column)
2. Calculates an average invoice amount
3. Limits the invoices in the groups to those that have an invoice total greater than 500. This is applied to every row before anything is grouped.
4. The results of the WHERE clause are grouped by vendor_name

BEST PRACTICE You can use either the WHERE or HAVING clause to code non-aggregte clauses, but it makes more sense to include them all in the HAVING clause for readability.

WITH ROLLUP operator

Use this operator at the end of the GROUP BY clause to add one or more summary rows to a result set that uses grouping and aggregates.

Example

SELECT vendor_id, COUNT(*) AS invoice_count,
   SUM(invoice_total) AS invoice_total
FROM invoices
GROUP BY vendor_id WITH ROLLUP;

This query adds a summary row to the end of the result set that summarizes all of the aggregate columns in the result set. If a column cannot be summarized, it contains a NULL value.

GROUPING function

Sometimes it is difficult to distinguish between the null values that are due to grouping and the null values that are due to summarizing. This function is commonly used to replace the nulls that are generated by WITH ROLLUP with literal values.

The GROUPING function returns a 1 if the expression is null because it's in a summary row, and returns 0 otherwise.

SELECT  IF(GROUPING(invoice_date) = 1, 'Grand totals', invoice_date)    (1)
      AS invoice_date,
   IF(GROUPING(payment_date) - 1, 'Invoice date totals', payment_date)  (2)
      AS payment_date,
   SUM(invoice_total) AS invoice_total,                                 
   SUM(invoice_total - credit_total - payment_total) AS balance_due     
FROM invoices
WHERE invoice_date BETWEEN '2018-07-24' AND '2018-07-31'               
GROUP BY invoice_date, payment_date WITH ROLLUP;

1. If the invoice_date column contains a null value because it's in a summary row, 'Grand totals' is displayed in the column.
2. If the payment_date column contains a null value because it's in a summary row, 'Invoice date totals' is displayed in the column.

Window functions

Similar to GROUP BY, except that the groups (or partitions), are not collapsed to a single row - all rows in the result set are returned.

Think of this as exposing a window of rows to a function.

To start, you code an aggregate window function by including the OVER clause. This clause defines the window that's used by the aggregate function. OVER() - uses a single parition. All the rows for that column will be the same value OVER(PARTITION BY [column_name]) - partitions the result set by the column parameter. The aggregate function is performed and grouped by each unique value in the specified column. A window consists of all the rows that are needed to evaluate the function for the current row.

Example

SELECT vendor_id, invoice_date, invoice_total,
   SUM(invoice_total) OVER() AS total_invoices,                     (1)
   SUM(invoice_total) OVER(PARTITION BY vendor_id) AS vendor_total  (2)
FROM invoices
WHERE invoice_total > 5000;

1. Uses the SUM function to calculate a total of the invoice_total column.

• The empty OVER() clause means that all of the rows in the result set are included in a single partition. This means that the total_invoices column contains the same value for each column, which is the total of all of the invoices in the result set.
• To calculate the total of all invoices, the SUM functino for each row needs a window into all of the other rows in the result set.

1. The PARTITION BY clause partitions the result set by the vendor_id column.

• This means that the sum of the invoice_total column is calculated for each vendor instead of all of the vendors.
• To calculate the total of all invoices for each vendor, the SUM function for each row needs a window into all the other rows for the same vendor.

SELECT vendor_id, invoice_date, invoice_total,
   SUM(invoice_total) OVER() AS total_invoices,
   SUM(invoice_total) OVER(PARTITION BY vendor_id
      ORDER BY invoice_total) AS vendor_total       (1)
FROM invoices
WHERE invoice_total > 5000;

1. When you add an ORDER BY clause in the OVER(PARTITION BY) clause, the rows within each partition are sorted and the values from one row to the next are cumulative.

• Includes a frame, which is all of the rows from the start of the partition through the current row.

Frames

The number of rows before and after the current row (ROW) or a range of values based on the value of the current row (RANGE).

A frame defines a subset of the current partition. Beacuse a frame is relateve to the current row, it can move within a partition as the current row changes.

If you specify just the starting row for a frame, the ending row is the current row. To specify both a sstarting and ending rtow, you use the BETWEEN clause. TWhen you use BETWEEN, the starting row for a frame must not come after the ending row.

SELECT vendor_id, invoice_date, invoice_total,
   SUM(invoice_total) OVER() AS total_invoices,
   SUM(invoice_total) OVER(PARTITION BY vendor_id ORDER BY invoice_date
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)     (1)
      AS vendor_total
FROM invoices
WHERE invoice_date BETWEEN '2018-04-01' AND '2018-04-30';

1. Selects the rows between the frist row in the partion and the current row.

peer - a row that has the same value as other rows in the sort column.

The following example

SELECT vendor_id, invoice_date, invoice_total,
   SUM(invoice_total) OVER() AS total_invoices,
   SUM(invoice_total) OVER(PARTITION BY vendor_id ORDER BY invoice_date
   RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)                       (1)
         AS vendor_total
FROM invoices
WHERE invoice_date BETWEEN '2018-04-01' AND '2018-04-30';

1. This frame includes all of the rows within the partition, along with the current row and any of its peers.

The following example calculates the 3-month avergage of the sum of invoice totals.

SELECT MONTH(invoice_date) AS month, SUM(invoice_total) AS total_invoices,
   ROUND(AVG(SUM(invoice_total)) OVER(ORDER BY MONTH(invoice_date)
      RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING), 2) AS 3_month_avg         (1)
FROM invoices
GROUP BY MONTH (invoice_date);

1. Uses RANGE and indicates the the current month, 1 month before, and 1 month after should be used to calculate the average. If there is no preceding or subsequent row, then just the available rows are used to calculate the average.

Named windows

In some cases, you may need to code a SELECT statement with two or more aggregate functions that use the same window. If so, then you may want to use a named window so that you don't have to repeat the definition for the window for each function.

Use a WINDOW clause after the HAVING clause and before the ORDER BY, when included.

Similar to using an alias for a column name.

SELECT vendor_id, invoice_date, invoice_total,
   SUM(invoice_total) OVER vendor_window AS vendor_total,           (2)
   ROUND(AVG(invoice_total) OVER vendor_window, 2) AS vendor_avg,   (2)
   MAX(invoice_total) OVER vendor_window AS vendor_max,             (2)
   MIN(invoice_total) OVER vendor_window AS vendor_min              (2)
FROM invoices
WHERE invoice_total > 5000
WINDOW vendor_window AS (PARTITION BY vendor_id);           (1)

1. Paritions the rows in the result set by the vendor_id column.
2. Applies the partition defined in the WINDOW clause to each OVER clause

Subqueries

A SELECT statement that is coded within another SQL statement. It is used to create queries that work with two or more tables. A subquery cannot include an ORDER BY clause.

4 ways to introduce a subquery in a SELECT statement

1. In a WHERE clause as a search condition
2. In a HAVING clause as a search condition
3. In the FROM clause as a table specification.
4. In the SELECT clause as a column specification.

Example

The following statement returns all the invoices from the invoices table that have invoice totals greater than the average of all the invoices.

SELECT invoice_number, invoice_date, invoice_total
FROM invoices
WHERE invoice_total >               (2)
   (SELECT AVG(invoice_total)       (1)
   FROM invoices)
ORDER BY invoice_total;

1. The suqeiry calculates the average of all the invoices
2. The search condition testes each invoice to see if its invoice total is greater than that average.

When to use subqueries (vs JOINS)

Most subqueries can be restated as JOINS, and vice versa. When you use a subquery in a WHERE clause, its results cannot ve included in the final set because it is not included in the FROM clause.

JOIN

• THE SELECT clause of a join can include columns from both tables.
• A JOIN is generally more intuitive when it uses an existin relationship between the two tables, such as a primary key to foreign key relationship.

SELECT invoice_number, invoice_date, invoice_total
FROM invoices JOIN vendors 
   ON invoices.vendor_id = vendors.vendor_id
WHERE vendor_state = 'CA'
ORDER BY invoice_date;

Subquery

• You can use a subquey to pass an aggregate value to the main query.
• A subquery tends to be more intuitive when it uses an ad hoc relationship between the two tables.
• Long, complex queries can sometimes be easier to code using subqueries.

SELECT invoice_number, invoice_datte, invoice_total
FROM invoices 
WHERE vendor_id IN
   (SELECT vendor_id                (1)
   FROM vendors
   WHERE vendor_state = 'CA')
ORDER BY invoice_date;

1. You cannot include a column name from the vendors table because it is not in the FROM clause, it is in the subquery.

Subqueries in the WHERE clause

Use the IN ooerator to test whetehr an expression is contained in a list of values. You can provide that list of values in a subquery.

When you use the IN operator with a subquery, the subquery must return a single column that provides the list of values. Statements that use the IN operator with a subquery can usually be restated as an OUTER JOIN.

SELECT vendor_id, vendor_name, vendor_state
FROM vendors
WHERE vendor_id NOT IN              (2)
   (SELECT DISTINCT vendor_id       (1)
   FROM invoices)
ORDER BY vendor_id;

1. Returns a list of each vendor that is in the invoices table.
2. Returns data about the vendors whose IDs are not in that list.

Comparison operators and subqueries

When you use a comparison operator to return a single value, you need to use an aggregate function.

SELECT invoice_number, invoice_date,
   invoice_total - payment_total - credit_total AS balance_due
FROM invoices
WHERE invoice_total - payment_total - credit_total > 0
  AND invoice_total - payment_total - credit_total < 
      (
         SELECT AVG(invoice_total - payment_total - credit_total)
         FROM invoices
         WHERE invoice_total - payment_total - credit_total > 0
      )
ORDER BY invoice_total DESC;

ALL keyword

Returns a boolean. Used to modify the comparison operator so that the condition must be true for all the values retuned by a subquery. If no rows are returned by the subqeuery, a comparison that uses the ALL keyword is always true.

Example Return invoices larger than the largest invoice or vendor 34

SELECT vendor_name, invoice_number, invoice_total
FROM invoices i JOIN vendors v ON i.vendor_id = v. vendor_id
WHERE invoice_total > ALL       (2)
   (SELECT invoice_total        (1)
    FROM invoices
    WHERE vendor_id = 34)
ORDER BY vendor_name;

1. Returns the invoice_total column from vendor 34
2. Where the invoice_total is greater than any value returned in the subquery.

ANY and SOME keywords

Used to test whether a comparison is true for any of the values returned by a subquery. These keywords are interchangeable.

Example

SELECT vendor_name, invoice_number, invoice_total
FROM invoices i JOIN vendors v ON i.vendor_id = v. vendor_id
WHERE invoice_total < ANY       (2)
   (SELECT invoice_total        (1)
    FROM invoices
    WHERE vendor_id = 115)

1. Returns the invoice_total column from vendor 115
2. Where the invoice_total value is less than any of the columns returned in the ANY subquery.

This subquery could and should be rewritten using the MAX function:

WHERE invoice_total <        
   (SELECT MAX(invoice_total)
    FROM invoices
    WHERE vendor_id = 115)

Correlated subqueries

Uncorrelated subquery is executed once for the entire query.
Correlated subquery is executed once for each row that's processed by the main query. This is similar to looping over a series of values.

Example Retrieves rows from the invoices table for those invoices that have an invoice total that's greater than the average of all the invoices for the same vendor.
Goes through each vendor_id on the invoice table and calcs the average invoice total where the vendor_id is equal to the vendor_id that is currently being evaluated in the original clause.

SELECT vendor_id, invoice_number, invoice_total
FROM invoices i
WHERE invoice_total > 
   (SELECT AVG(invoice_total)           (2)
    FROM invoices
    WHERE vendor_id = i.vendor_id)      (1) 
ORDER BY vendor_id, invoice_total;

1. Refers to the vendor_id value of the main query.
2. Returns the average of the invoice_total column from the invoice table where the vendor_id is equal to the vendor_id for the column it is querying.
   It does this for each row in the column and compares

FIGURE THIS OUT

EXISTS operator

Tests whether the subquery returns a result set (if it exists or not). Typically used with a correlated subquery.

SELECT vendor_id, vendor_name, vendor_state
FROM vendors
WHERE NOT EXISTS                            (2)
   (SELECT *                                (1)
    FROM invoices
    WHERE vendor_id = vendors.vendor_id)

1. Selects all invoices that have the same vendor_id as the current vendor in the outer query.
2. Uses NOT EXISTS to test whether any invoices were found for the current vendor. If not, then the vendor row is included in the result set.

Subqueries in the HAVING clause

Specify a search condition just like the WHERE clause.

Subqueries in the SELECT clause

Replace a column specification with a subquery. The result of a query must return a single value for that column. In most cases, you use a correlated subquery in the SELECT clause.
You can usually restate each as a JOIN, so subqueries are not usually used in SELECT clause.

SELECT vendor_name,
   (SELECT MAX(invoice_date) FROM invoices              (1)
    WHERE vendor_id = vendors.vendor_id) AS latest_inv
FROM vendors
ORDER BY latest_inv DESC;

1. Calculates the maximum invoice date for each vendor in the vendors table by referring to the vendor_id from the vendors table in the FROM clause of the main query.
   Goes through each vendor_id in the invoices table and calculates the max date for each vendor_id in the vendors table.

Restated as a JOIN

SELECT vendor_name, MAX(invoice_date) AS latest_inv
FROM vendors v
   LEFT JOIN invoices i ON v.vendor_id = i.vendor_id
GROUP BY vendor_name
ORDER BY latest_inv DESC;

Subqueries in the FROM clause

Code a subquery in place of a table specification. The result is sometimes referred to as an inline view.
When you code a subquery in the FROM clause, you must assign an alias.
In the subquery, you should use an alias for any columns in the subquery that perform calculations. The main query can refer to the columns by these names.

Example

Returns the largest invoice total for the top vendor in each state.

SELECT vendor_state, MAX(sum_of_invoices) AS max_sum_of_invoices    (2)
FROM 
(
   SELECT vendor_state, vendor_name,            (1)
      SUM(invoice_total) AS sum_of_invoices     (2)
   FROM vendors v JOIN invoices i
         ON v.vendor_id = i.vendor_id
   GROUP BY vendor_state, vendor_name
) t                                             (3)
GROUP BY vendor_state
ORDER BY vendor_state;

1. The subquery returns the sum of the invoice_total for every vendor that is in the invoices table
2. Alias in the subquery is used in the main query.
3. Alias for the subquery

Complex queries with subqueries

The following query retrieves the vendor from each state that has the largest invoice total. It uses 3 subqueries as outlined in the comments.

SELECT t1.vendor_state, vendor_name, t1.sum_of_invoices
FROM
   (
      -- 1. invoice totals by vendor
      SELECT vendor_state, vendor_name,
         SUM(invoice_total) AS sum_of_invoices
      FROM vendors v JOIN invoices i
         ON v.vendor_id = i.vendor_id
      GROUP BY vendor_state, vendor_name
   ) t1
   JOIN
      (
         -- 2. top invoice totals by state
         SELECT vendor_state,
            MAX(sum_of_invoices) AS sum_of_invoices
         FROM
         (
            -- 3. invoice totals by vendor
            SELECT vendor_state, vendor_name,
               SUM(invoice_total) AS sum_of_invoices
            FROM vendors v JOIN invoices i
               ON v.vendor_id = i.vendor_id
            GROUP BY vendor_state, vendor_name
         ) t2
         GROUP BY vendor_state
      ) t3
   ON t1.vendor_state = t3.vendor_state AND
      t1.sum_of_invoices = t3.sum_of_invoices
ORDER BY vendor_state;

Procedure for building complex queries

1. State the problem that you want to solve in plain English.

• Which vendor in each state has the largest invoice total?

1. Use pseudocode to outline the query.

SELECT vendor_state, vendor_name, sum_of_invoices
FROM (subquery returning vendor_state, vendor_name, sum_of_invoices)
JOIN (subquery returning vendor_state, largest_sum_of_invoices)
   ON vendor_state AND sum_of_invoices
ORDER BY vendor_state;

1. Code the subqueries and test them to be sure that they return the correct data.

• Code the first subquery:

SELECT vendor_state, vendor_name, SUM(invoice_total) AS sum_of_invoices
FROM vendors v JOIN invoices i
   ON v.vendor_id = i.vendor_id
GROUP BY vendor_state, vendor_name

• Code the second subquery:

SELECT vendor_state, MAX(sum_of_invoices) AS sum_of_invoices
FROM
(
   SELECT vendor_state, vendor_name,
      SUM(invoice_total) AS sum_of_invoices
   FROM vendors v JOIN invoices i
      ON v.vendor_id = i.vendor_id
   GROUP BY vendor_state, vendor_name
) t
GROUP By vendor_state

1. Code and test the final query.

Common table expressions (CTE)

A CTE is a SELECT statement that creates one or more named temporary result sets that can be used by the query that follows. Use CTEs to simplify complex queries that use subqueries.

• CTEs begin with the WITH keyword, followed bgy the definition of the CTE. Then, you code the statement that uses it.
• When you use multiple CTEs, separate them with columns.
• You can use them with SELECT, INSERT, UPDATE, and DELETE clauses, but they are most often used with SELECT.

WITH summary as
(
   SELECT vendor_state, vendor_name, SUM(invoice_total) AS sum_of_invoices
   FROM vendors v JOIN invoices i 
      ON v.vendor_id = i.vendor_id
   GROUP BY vendor_state, vendor_name
),
top_in_state AS 
(
   SELECT vendor_state, MAX(sum_of_invoices) AS sum_of_invoices
   FROM summary
   GROUP BY vendor_state
)
SELECT summary.vendor_state, summary.vendor_name,
      top_in_state.sum_of_invoices
FROM summary JOIN top_in_state
   ON summary.vendor_state = top_in_state.vendor_state AND
      summary.sum_of_invoices = top_in_state.sum_of_invoices
ORDER BY summary.vendor_state;

Recursive CTEs

A recursive query is a query that is able to loop through a result set and perform processing to return a final result set.

• Commonly used to return hierarchical data such as an organiational chart in which a parent element may have one or more child elements, and each child element may have one or more child elements.

WITH RECURSIVE employees_cte AS
(
         -- Nonrecursive query
         SELECT employee_id,
            CONCAT(first_name, ' ', last_name) AS employee_name,
            1 AS ranking
         FROM employees
         WHERE manager_id is NULL
      UNION ALL
         -- Recursive query
         SELECT employees.employee_id,
            CONCAT(first_name, ' ', last_name),
            ranking + 1
         FROM employees
            JOIN employees_cte
            ON employees.manager_id = employees_cte.employee.id
)
SELECT *
FROMM employees_cte
ORDER BY ranking, employee_id;

Data types

A data type specifies the kind of information the column is inteded to store. This determines the operations that can be performed on the column.

Data type table

Character types

The two most common character data types are CHAR and VARCHAR.

How the character types work with utf8mb4

utf8mb4 character set uses up to 4 bytes to store each character, so its called a multiple-byte character set. When it is used with CHAR, it uses 4 bytes per character. With VARCHAR, it uses 1 byte per character and 1 byte to store its location.

CHAR

• Stores fixed-length strings.
• Any data stored using this data type always occupies the same number of bytes regardless of the actual length of the string.
• Typically used to define columns that have a fized number of characters, for example, a state column
• If two characters are stored in a column defined as CHAR(5), then 3 additional spaces are stored after the two characters becuase it is a fixed-length data type.

VARCHAR

• Stores variable-length strings
• Data stored with this type occupies only the number of bytes needed to store the string plus an extra byte to store the length of the string
• Typically used to define columns whose lengths vary from one row to the next.

Integer types

These are positive or negative numbers that don't include a decimal point. The INT type is most commonly used because it can store a wide range of nunbers and only requires 4 bytes of storage.

UNSIGNED - attribute for an integer type to prevent negative values from being stored in the column. ZEROFILL - attribute that sets the UNSIGNED attribute automatically, and the integer is displayed with zeros padded from the left, up to the maximum display size. (max display size for INT is 10) BOOLEAN and BOOL - synonym for TINYINT(1). 0 is FALSE, 1 is TRUE.

Fixed-point and floating-point types

Fixed-point type

Floating-point type

Date and time types

Typically use TIMESTAMP to track when a row was inserted or last updated.

• Year - entries with 1 and 2-digit entries are acceptable, but converted to 4-digit automatically.

ENUM and SET types

Both types allow you to restrict the values for a column to a limited set of strings.

• ENUM can store only one member in a set of values
• SET can store 0, 1, or up to 64 different values

The large object types

These are designed to store large amounts of binary or character data.
BLOB types (binary large object) store strings of binary data. This data is often used to stroe images, sounds, and video.
TEST types store strings of characters and are sometimes referred to as character large object (CLOB) types.

Data conversion

Automatic conversions are called implicit conversions.

CAST AND CONVERT functions

Use the CAST and CONVERT functions to conver an expression to the data type that you specify.

CAST

SELECT invoice_id, invoice_date, invoice_total,
   CAST(invoice_date AS CHAR(10)) AS char_date,
   CAST(inoice_total AS SIGNED) AS integer_total
FROM invoices;

Explicitly cast string as an integer

SELECT *
FROM string_example
ORDER BY CAST(emp_id AS SIGNED);

Implicity cast string as an integer

SELECT *
FROM string_example
ORDER BY emp_id + 0;

### CONVERT
```sql
SELECT invoice_id, invoice_date, invoice_total,
   CONVERT(invoice_date AS CHAR(10)) AS char_date,
   CONVERT(inoice_total AS SIGNED) AS integer_total
FROM invoices;

FORMAT and CHAR functions

FORMAT converts a number to a string of characters. This makes large numbers easier to read. Additionally, it rounds the last number.

CHAR returns a binary string for each specified integer. This is typically used to output ASCII control characters that cannot be typed on your keyboard.

Functions

This section contains common functions and their uses.

Parsing a string

Use SUBSTRING_INDEX to parse a string

SELECT emp_name
   SUBSTRING_INDEX(emp_name, ' ', 1) AS first_name,      1)
   SUBSTRING_INDEX(emp_name, ' ', -1) AS last_name     (   )
FROM string_sample;

1. Returns all characters from the start of the string in the emp_name column up to the first space in that column.
2. Returns all characters from the end of the string in the emp_name column up to the last space in that column.

Use LOCATE to find a character in a string

SELECT emp_name,
   LOCATE(' ', emp_name) AS first_space,                                (1)
   LOCATE(' ', emp_name, LOCATE(' ', emp_name) + 1) AS second_space     (2)
FROM string_sample

1. Returns an integer value for the location of the first space.
2. Returns the location of the second space. This uses a nested LOCATE to start the search at the character after the first space.

Use SUBSTRING to parse a string

SELECT emp_name,
   SUBSTRING(emp_name, 1, LOCATE(' ', emp_name) -1) AS first_name       (1)
   SUBSTRING(emp_name, LOCATE(' ', emp_name) +1) AS last_name           (2)
FROM string_sample

1. Returns all characters from the beginning of the string to the first space.
2. Returns all characters after the efirst space to the end of the string.

Numeric data

Searching for floating-point numbers

You don't/can't search for exact numbers because floats and decimals are approximate numbers.

SELECT * 
FROM float_sample
WHERE float_value BETWEEN 0.99 AND 1.01

Or, you can search for values that round to an exact value:

SELECT *
FROM float_sample
WHERE ROUND(float_value, 2) = 1.00;

Working with date/time data

Date/time parsing functions

EXTRACT function with date/time

Use EXTRACT with any of the date/time units followed by the FROM keyword and a date/time value to extract the specified unit from the date/time value and retun an integer value that corresponds to that unit.

Formatting dates and times

Common codes for date/time format strings

Examples

Performing calculations with date/time

Searching for a date

Search for a range of dates

SELECT *
FROM date_sample
WHERE start_date >= '2018-02-28' AND start_date < '2018-03-01';

Search for month, day, and year integers

SELECT * 
FROM date_sample
WHERE MONTH(start_date) = 2 AND
   DAYOFMONTH(start_date) = 28 AND
   YEAR(start_date) = 2018;

Search for a formatted date

SELECT * 
FROM date_sample
WHERE DATE_FORMAT(start_date, '%m-%d-%Y') = '02-28-2018';

Searching for a time

Search for a time that has been formatted

SELECT * FROM date_sample
WHERE DATE_FORMAT(start_date, '%T') = '10:00:00'

Search for a time that hasn't been formatted

SELECT * FROM date_sample
WHERE EXTRACT(HOUR_SECOND FROM start_date) = 100000;

Search for an hour of the day

SELECT * FROM date_sample
WHERE HOUR(start_date) = 9;

Search for a range of times

SELECT * FROM date_sample
WHERE EXTRACT(HOUR_MINUTE FROM start_date) BETWEEN 900 AND 1200;

CASE function

Returns a value that's determined by the conditions you specify. When MySQL finds an expression in a WHEN clause that's equal to the input expression, it returns the expression specified in the matching THEN clause.

Example

The following example determines the status fo the invoices in the invoices table.

SELECT invoice_number, invoice_total, invoice_date, invoice_due_date,
   CASE
      WHEN DATEDIFF(NOW(), invoice_due_date) > 30
         THEN 'Over 30 days past due'
      WHEN DATEDIFF(NOW(), invoice_due_date) > 0
         THEN '1 to 30 days past due'
      ELSE 'Current'
   END AS invoice_status
FROM invoices
WHERE invoice_total - payment_total - credit_total > 0;

IF, IFNULL, COALESCE functions

IF function

Use the IF function to test a conditino and return one value if the condition is true or another value if the condition is false.

SELECT vendor_name,
   IF(vendor_city = 'Fresno', 'Yes', 'No') AS is_city_fresno
FROM vendors;

IFNULL function

Allows you to substituted non-null values for null values. The following example returns the first expression if it isn't null. Otherwise, it returns the replacement value you specify.

SELECT payment_date,
   IFNULL(payment_date, 'No Payment') AS new_date
FROM invoices;

COALESCE function

Allows you to substituted non-null values for null values from a list of values. Returns the first expression in the list that isn't null. If all of the expressions are null, this functionreturns a null value.

SELECT payment_date,
   COALESCE(payment_date, 'No Payment') AS new_date
FROM invoices;

Regular expression functions

Use a string pattern to search a string expression.

REGEXP_INSTR function

SELECT DISTINCT vendor_city, REGEXP_INSTR(vendor_city, ' ') AS space_index
FROM vendors
WHERE REGEXP_INSTR(vendor_city, ' ') > 0
ORDER BY vendor_city;

REGEXP_SUBSTR function

SELECT vendor_city, REGEXP_SUBSTR(vendor_city, '^SAN|LOS') AS city_match
FROM vendors
WHERE REGEXP_SUBSTR(vendor_city, '^SAN|LOS') IS NOT NULL;

REGEXP_REPLACE and REGEXP_LIKE functions

SELECT vendor_name, vendor_address1,
   REGEXP_REPLACE(vendor_address1, 'STREET', 'St') AS new_address1
FROM Vendors
WHERE REGEXP_LIKE(vendor_address1, 'STREET');

Ranking functions

These are non-aggregate window functions, sometimes called specialiazed window functions. These functions are grouped into 2 groups: Ranking functions and analytical functions.

ROW_NUMBER function

SELECT ROW_NUMBER() OVER(PARTITION BY vendor_state                  (2), (3)
   ORDER BY vendor_name) AS 'row_number', vendor_name, vendor_state (1)
FROM vendors;

1. Detemines the sequence of the rows within the partitions
2. Specifies the column that's used to divide the result set into groups.
3. Returns the number of the current row within its partition, starting at 1 for the first row in each partition.

RANK and DENSE_RANK functions

Both functions return the rank of each row within the partition of a result set. If there is a tie, both of these functions give the same rank to all rows that are tied.

SELECT RANK() OVER(ORDER BY invoice_total) AS 'rank',           (1)
   DENSE_RANK() OVER (ORDER BY invoice_total) AS 'dense_rank',  (2)
   invoice_total, invoice_number
FROM invoices;

1. To determine the rank for the next distinct row, RANK adds 1 to the total number of rows.
2. To determine the rank for the next distinct row, DENSE_RANK adds 1 to the rank for the previous row.

NTILE function

This function divides the rows in a partition into a specified number of groups and returns the group number of each row.

SELECT terms_description,
   NTILE(2) OVER (ORDER BY terms_id) AS tile2,
   NTILE(3) OVER (ORDER BY terms_id) AS tile3,
   NTILE(4) OVER (ORDER BY terms_id) AS tile4,
FROM terms;

Analytic functions

These functions let you perform calculations on ordered sets of data.

The FIRST_VALUE, LAST_VALUE, and NTH_VALUE let you return the first, last, and nth values in an ordered set of values. When you use the PARTITION BY clause with LAST_VALUE or NTH_VALUE, you typically include the ROWS or RANGE clause as well to define a subset of the current partition.

FIRST_VALUE, NTH_VALUE, and LAST_VALUE functions

SELECT sales_year, CONCAT(rep_first_name, ' ', rep_last_name) AS rep_name, sales total,
   FIRST_VALUE(CONCAT(rep_first_name, ' ', rep_last_name))
       OVER (PARTITION BY sales_year ORDER BY sales_total DESC)
       AS highest_sales,
    NTH_VALUE(CONCAT(rep_first_name, ' ', rep_last_name), 2)
       OVER (PARTITION BY sales_year ORDER BY sales_total DESC
       RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
       AS second_highest_sales,
    LAST_VALUE(CONCAT(rep_first_name, ' ', rep_last_name))
       OVER(PARTITION BY sales_year ORDER BY sales_total DESC
       RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
       AS lowest_sales
FROM sales_totals JOIN sales_reps ON sales_totals.rep_id = sales_reps.rep_id;

LAG function

SELECT rep_id, sales_year, sales_total AS current_sales,
   LAG(sales_total, 1, 0) OVER (PARTITION BY rep_id ORDER BY sales_year)    (1), (2)
      AS last_sales,
   sales_total - LAG(sales_total, 1, 0)
      OVER (PARTITION BY rep_id ORDER BY sales_year) AS 'change'            (1)
FROM sales_totals;

1. OVER clause is used to group the result set by the rep_id column and sort it by the sales_year column.
2. LAG function gets the sales total from one row prior to the current row (the offset).

PERCENT_RANK and CUME_DIST functions

The PERCENT_RANK calculates the rank of the values in a sorted set of values as a percent. Calculates a percent that indicates the rank of each row within a partition. The result of this function is always 0 or 1.

The CUME_DIST function calculates the percent of the values in a sorted set of values that are less than or equal to the current value. This represents the cumulative distribution - calculated by dividing the number of rows with the current value or a lower value by the total nubmer of rows in the partition.

SELECT sales_year, rep_id, sales_total,
   PERCENT_RANK() OVER (PARTITION BY sales_year ORDER BY sales_total)
      AS pct_rank,
   CUME_DIST() OVER (PARTITION BY sales_year ORDER BY sales_total)
      AS 'cume_dist'
FROM sales_totals;

Working with databases

Creating a database

CREATE DATABASE [IF NOT EXISTS] dbName

Dropping a database

CREATE DATABASE [IF EXISTS] dbName

Use statement

The USE statement selects the specified database and makes it the current database.

Creating a table

The CREATE TABLE statement creates a new table in the current database. Alternately, you can qualify the table name with the correct database. For example, sql CREATE TABLE dbName.tableName;.

Common column attributes

NOTE: NULL and UNIQUE are types of constraints.

Create a table without column attributes

CREATE TABLE vendors
(
    vendor_id       INT,
    vendor_name     VARCHAR(50)
)

Create a table with column attributes

CREATE TABLE vendors
(
    vendor_id       INT             NOT NULL        UNIQUE AUTO_INCREMENT,
    vendor_name     VARCHAR(50)     NOT NULL        UNIQUE  
)

Coding a primary key constraint

Constraints are used to enforce the integrity of the data in a table by defining rules about the values that can be stored in the columns of the table.

When you identify a column as the primary key, the following happens:

• the column is forced to be NOT NULL
• the column is forced to contain a unique value for each row.
• An index is created automatically based on the column.

Column-level constraints

Code a column-level constraint as part of the definition of the column it constrains.

CREATE TABLE vendors
(
    vendor_id       INT             PRIMARY KEY         AUTO_INCREMENT,
    vendor_name     VARCHAR(50)     NOT NULL            UNIQUE
)

Table-level constraints

Code a table-level constraint as if it is a separate column definition, and you name the columns it constrains within that definition.

CREATE TABLE vendors
(
    vendor_id           INT             AUTO_INCREMENT,
    vendor_name         VARCHAR(50)     NOT NULL,
    CONSTRAINT vendors_pk PRIMARY KEY (vendor_id),
    CONSTRAINT vendor_name_uq UNIQUE (vendor_name)
)

Foreign-key constraints

Also known as a reference constraint. This constraint requires values in one tabvle to match values in another table. Used to define the relationship between tables and to enforce referential integrity.

Column-level foreign key constraint

To create a foreign key constraing at the column level, you code the REFERENCES keyword followed by the name of the related table and the name of the related colum nin parentheses.

CREATE TABLE invoices
(
    invoice_id          INT             PRIMARY KEY,
    vendor_id           INT             REFERENCES vendors (vendor_id),
    invoice_number      VARCHAR(50)     NOT NULL        UNIQUE
)

Table-level foreign key constraint

To create a foreign key constraing at the table level, include the CONSTRAINT keyword followed by a name, followed by the FOREIGN KEY keywords. This allows you to provide a name for the foreign key, which is a best practice.

CREATE TABLE invoices
(
    invoice_id          INT             PRIMARY KEY,
    vendor_id           INT             NOT NULL,
    invoice_number      VARCHAR(50)     NOT NULL        UNIQUE,
    CONSTRAINT invoices_fk_vendors
       FOREIGN KEY (vendor_id) REFERENCES vendors (vendor_id)
)

Alter constraints of a table

Use the ALTER TABLE statement to add or drop the constraints of an existing table

Adds a new column

ALTER TABLE vendors
ADD last_transaction_date DATE

Drops a column

ALTER TABLE vendors
DROP COLUMN last_transaction_date

Change the length of the column

ALTER TABLE vendors
MODIFY vendor_name VARCHAR(100) NOT NULL

Change data type

ALTER TABLE vendors
MODIFY vendor_name CHAR(100) NOT NULL

Change default value of a column

ALTER TABLE vendors
MODIFY vendor_name VARCHAR(100) NOT NULL DEFAULT 'New Vendor';

Change name

ALTER TABLE vendors
RENAME COLUMN vendor_name TO v_name;

Alter the constraints of a table

To drop a foreign key constraint, yoiu must know its name. If you don't, use a GUI tool to look it up.

Add a primary key constraint

ALTER TABLE vendors
ADD PRIMARY KEY (vendor_id)

Add a foreign key constraint

ALTER TABLE invoices
ADD CONSTRAINT invoice_fk_vendors
   FOREIGN KEY (vendor_id) REFERENCES vendors (vendor_id)

Drop primary key constraint

ALTER TABLE vendors
DROP PRIMARY KEY

Drop foreign key constraint

ALTER TABLE invoices
DROP PRIMARY KEY invoice_fk_vendors

Rename, truncate, and drop a table

Be careful with these statements!

Rename a table

RENAME TABLE vendors TO vendor

Delete all data from a table

Delete table from the current database

Qualifies the table to be deleted

Indexes

An index speeds up joins and searches by providing a way for a database management system to go directly to a row rather than having to search through all the rows until if finds the one that you want. By default, MySQL creates indexes for primary keys, foreign keys, and uique keys of a table.

You may want to create indexes for other columns that are used frequently in search conditions or joins. Avoid creating indexes on columns that are updated frequently since this slows down insert, update, and delete operations. You can also drop indexes from a table.

Standard index naming convention

Create index based on single column

CREATE INDEX invoices_invoice_date_ix
   ON invoices (invoice_date)

Creates an index based on two columns

CREATE INDEX invoices_vendor_id_invoice_number_ix
   ON invoices (invoice_id, invoice_number)

Creates a unique index

CREATE UNIQUE INDEX vendors_vendor_phone_ix
   ON vendors (vendor_phone)

Creates an index sorted in DESC

CREATE INDEX invoices_invoice_total_ix
   ON invoices (vendor_phone DESC)

Drops an index

DROP INDEX vendors_vendor_phone_ix ON vendors

A script that creates an entire database

A script is a file that contains one or more SQL statements. You must create the tables that don't have foreign keys ffirst. That way, the other tables can define foreign keys that refer to them.

-- create the database
DROP DATABASE IF EXISTS ap;
CREATE DATABASE ap;

-- select the database
USE ap;

-- create the tables
CREATE TABLE general_ledger_accounts
(
  account_number        INT            PRIMARY KEY,
  account_description   VARCHAR(50)    UNIQUE
);

CREATE TABLE terms
(
  terms_id              INT            PRIMARY KEY,
  terms_description     VARCHAR(50)    NOT NULL,
  terms_due_days        INT            NOT NULL
);

CREATE TABLE vendors
(
  vendor_id                     INT            PRIMARY KEY   AUTO_INCREMENT,
  vendor_name                   VARCHAR(50)    NOT NULL      UNIQUE,
  vendor_address1               VARCHAR(50),
  vendor_address2               VARCHAR(50),
  vendor_city                   VARCHAR(50)    NOT NULL,
  vendor_state                  CHAR(2)        NOT NULL,
  vendor_zip_code               VARCHAR(20)    NOT NULL,
  vendor_phone                  VARCHAR(50),
  vendor_contact_last_name      VARCHAR(50),
  vendor_contact_first_name     VARCHAR(50),
  default_terms_id              INT            NOT NULL,
  default_account_number        INT            NOT NULL,
  CONSTRAINT vendors_fk_terms
    FOREIGN KEY (default_terms_id)
    REFERENCES terms (terms_id),
  CONSTRAINT vendors_fk_accounts
    FOREIGN KEY (default_account_number)
    REFERENCES general_ledger_accounts (account_number)
);
CREATE TABLE invoices
(
  invoice_id            INT            PRIMARY KEY   AUTO_INCREMENT,
  vendor_id             INT            NOT NULL,
  invoice_number        VARCHAR(50)    NOT NULL,
  invoice_date          DATE           NOT NULL,
  invoice_total         DECIMAL(9,2)   NOT NULL,
  payment_total         DECIMAL(9,2)   NOT NULL      DEFAULT 0,
  credit_total          DECIMAL(9,2)   NOT NULL      DEFAULT 0,
  terms_id              INT            NOT NULL,
  invoice_due_date      DATE           NOT NULL,
  payment_date          DATE,
  CONSTRAINT invoices_fk_vendors
    FOREIGN KEY (vendor_id)
    REFERENCES vendors (vendor_id),
  CONSTRAINT invoices_fk_terms
    FOREIGN KEY (terms_id)
    REFERENCES terms (terms_id)
);

CREATE TABLE invoice_line_items
(
  invoice_id              INT            NOT NULL,
  invoice_sequence        INT            NOT NULL,
  account_number          INT            NOT NULL,
  line_item_amount        DECIMAL(9,2)   NOT NULL,
  line_item_description   VARCHAR(100)   NOT NULL,
  CONSTRAINT line_items_pk
    PRIMARY KEY (invoice_id, invoice_sequence),
  CONSTRAINT line_items_fk_invoices
    FOREIGN KEY (invoice_id)
    REFERENCES invoices (invoice_id),
  CONSTRAINT line_items_fk_acounts
    FOREIGN KEY (account_number)
    REFERENCES general_ledger_accounts (account_number)
);

-- create an index
CREATE INDEX invoices_invoice_date_ix
  ON invoices (invoice_date DESC);

Storage engines

A storage engine determines how MySQL stores data and which database features are available to you.

View all the storage engines available on the server

View the default storage engine for a server

SHOW VARIABLES LIKE 'default_storage_engine';

View the storage engine for all the tables in a database

SELECT table_name, engine
FROM information_schema.tables
WHERE table_schema = 'ap';

Specify a storage engine for a table

CREATE TABLE product_descriptions
(
    product_id              INT             PRIMARY KEY,
    product_description     VARCHAR(200)    
)
ENGINE = MyISAM;

Specify an engine for an existing table

ALTER TABLE product_descriptions ENGINE = InnoDB;

Set the default storage engine for the current session

SET SESSION default_storage_engine = InnoDB;

Create views

A view is a SELECT statement that is stored in the database as a database object. Think of it as a virtyal table that consists of only the rows and columns specified in its CREATE VIEW statement.

When you save queries, you can store them in a script or you can save them in a view. Views are not stored in files, they are stored as part of the database. So, they can be used by SQL programmers and by custom applications that have access to the database.

Create view statement for view named vendors_min

CREATE VIEW vendors_min AS 
   SELECT vendor_name, vendor_state, vendor_phone
   FROM vendors;

CREATE VIEW vendors_sw AS
SELECT *
FROM vendors
WHERE vendor_state IN ('CA', 'AZ', 'NV', 'NM');

SELECT statement that uses vendors_min view

SELECT * FROM vendors_min
WHERE vendor_state = 'CA'
ORDER BY vendor_name;

UPDATE statement that uses vendors_min view

UPDATE vendors_min
SET vendor_phone = '(800) 555-3941'
WHERE vendor_name = 'Register of Copyrights';

Drop a view

Using REPLACE

When you code a view, you can specify that you want to automatically drop a view that has the same name as the view that you're creating by using the OR REPLACE keywords after the CREATE keyword.

CREATE OR REPLACE VIEW vendor_invoices AS 
   SELECT vendor_name, invoice_number, invioce_date, invoice_total
   FROM vendors
      JOIN invoices ON vendors.vendor_id = invoices.vendor_id;

Updatable view

To be an updatable view, a view must meet the following requirements:

• The SELECT list can't include the DISTINCT keyword or an aggregate function
• The SELECT statement can't include a GROUP BY or HAVING clause
• Two SELECT statements cannot be joined by a union operation

To INSERT rows into views, you must make sure that the INSERT statement includes the all the rows as the view.

If a view is updatable, you can use the INSERT, UPDATE, or DELETE clauses with them. If a view isn't updatable, its a read-only view.

WITH CHECK option clause

If you specify a WITH CHECK OPTION clause when you creat a view, an error will occur if you try to modify a row in such a way that it would no longer be included in the view.
This clause prevents an update if it causes the row to be excluded from the view.

CREATE OR REPLACE VIEW vendor_payment AS
   SELECT vendor_name, invoice_number, invoice_date, payment_date,
      invoice_total, credit_total, payment_total
   FROM vendors JOIN invoices ON vendors.vendor_id = invoices.vendor_id
   WHERE invoice_total - payment_total - credit_total >= 0
WITH CHECK OPTION;

Stored programs

Stored programs include procedural code that controls the flow of programs.

Script for stored procedure named test

NOTE: The DELIMITER // statement changes the delimter from the default semicolon delimiter (;) to two slashed (//)

USE ap;

DROP PROCEDURE IF EXISTS test;

-- Change statement delimiter from semicolon to double front slash 
DELIMITER //

CREATE PROCEDURE test()
BEGIN 
   DECLARE sum_balance_due_var DECIMAL(9, 2);

   SELECT SUM(invoice_total - payment_total - credit_total)
   INTO sum_balance_due_var
   FROM invoices
   WHERE vendor_id = 95;

   IF sum_balance_due_var > 0 THEN
      SELECT CONCAT('Balance due: $', sum_balance_due_var) AS message;
    ELSE
       SELECT 'Balance paid in full' AS message;
   END IF;
END//

-- Change statement delimiter from double front slash to semicolon 
DELIMITER ;

CALL test();

Flow of execution keywords

Display data and debug

DELIMITER //

CREATE PROCEDURE test()
BEGIN
   SELECT 'This is a test.' AS messages;
END//

How to declare and set variables

The following sets a variable to a literal value or an expression:

SET variable_name = {literal_value|expression};
SET vendor_id_var = 95;

The following sets a variable to a selected value

SELECT column_1[, column_2]...
INTO variable_name_1[, variable_name_2]...

SELECT MAX(invoice_total), MIN(invoice_total), COUNT(invoice_id)
INTO max_invoice_total, min_invoice_total, count_invoice_id

USE ap;

DROP PROCEDURE IF EXISTS test;

DELIMITER //

CREATE PROCEDURE test()
BEGIN
  DECLARE max_invoice_total  DECIMAL(9,2);
  DECLARE min_invoice_total  DECIMAL(9,2);
  DECLARE percent_difference DECIMAL(9,4);
  DECLARE count_invoice_id   INT;
  DECLARE vendor_id_var      INT;
  
  SET vendor_id_var = 95;

  SELECT MAX(invoice_total), MIN(invoice_total), COUNT(invoice_id)
  INTO max_invoice_total, min_invoice_total, count_invoice_id
  FROM invoices WHERE vendor_id = vendor_id_var;

  SET percent_difference = (max_invoice_total - min_invoice_total) / 
                         min_invoice_total * 100;
  
  SELECT CONCAT('$', max_invoice_total) AS 'Maximum invoice', 
         CONCAT('$', min_invoice_total) AS 'Minimum invoice', 
         CONCAT('%', ROUND(percent_difference, 2)) AS 'Percent difference',
         count_invoice_id AS 'Number of invoices';
END//

DELIMITER ;

CALL test();

Stored procedure that uses an IF statement

USE ap;

DROP PROCEDURE IF EXISTS test;

DELIMITER //

CREATE PROCEDURE test()
BEGIN
   DECLARE first_invoice_due_date DATE;

   SELECT MIN(invoice_due_date)
   INTO first_invoice_due_date
   FROM invoices
   WHERE invoice_total - payment_total - credit_total > 0;

   IF first_invoice_due_date < NOW() THEN
     SELECT 'Outstanding invoices are overdue!';
   ELSEIF first_invoice_due_date = SYSDATE() THEN
     SELECT 'Outstanding invoices are due today!';
   ELSE
     SELECT 'No invoices are overdue.';
   END IF;

  -- the IF statement rewritten as a Searched CASE statement
  /*
  CASE  
    WHEN first_invoice_due_date < NOW() THEN
      SELECT 'Outstanding invoices overdue!' AS Message;
    WHEN first_invoice_due_date = NOW() THEN
      SELECT 'Outstanding invoices are due today!' AS Message;
    ELSE
      SELECT 'No invoices are overdue.' AS Message;
  END CASE;
  */
  
END//

DELIMITER ;

CALL test();

Case statements

Similar to a switch statement in Java.

USE ap;

DROP PROCEDURE IF EXISTS test;

DELIMITER //

CREATE PROCEDURE test()
BEGIN
  DECLARE terms_id_var INT;

  SELECT terms_id INTO terms_id_var 
  FROM invoices WHERE invoice_id = 4;

  CASE terms_id_var
    WHEN 1 THEN 
      SELECT 'Net due 10 days' AS Terms;
    WHEN 2 THEN 
      SELECT 'Net due 20 days' AS Terms;      
    WHEN 3 THEN 
      SELECT 'Net due 30 days' AS Terms;      
    ELSE
      SELECT 'Net due more than 30 days' AS Terms;
  END CASE;
END//

While loops

USE ap;

DROP PROCEDURE IF EXISTS test;

DELIMITER //

CREATE PROCEDURE test()
BEGIN
  DECLARE i INT DEFAULT 1;
  DECLARE s VARCHAR(400) DEFAULT '';

  -- WHILE loop
  WHILE i < 4 DO
    SET s = CONCAT(s, 'i=', i, ' | ');
    SET i = i + 1;
  END WHILE;

  -- REPEAT loop
  /*
  REPEAT
    SET s = CONCAT(s, 'i=', i, ' | ');
    SET i = i + 1;
  UNTIL i = 4
  END REPEAT;
  */

  -- LOOP with LEAVE statement
  /*
  testLoop : LOOP
    SET s = CONCAT(s, 'i=', i, ' | ');
    SET i = i + 1;

    IF i = 4 THEN
       LEAVE testLoop;
    END IF;        
  END LOOP testLoop;
  */
  
  SELECT s AS message;

END//

DELIMITER ;

CALL test();

Using a cursor

Use a cursor when you need to work with the data in a result set one row at a time. Like a flag variable in Java.

-- Declare the cursor
DECLARE cursomr_name CURSOR FOR select_statement; 


-- Declare an error handler for when no rows are found in the cursor
DECLARE CONTINUE HANDLER FOR NOT FOUND handler_statement;

-- Open the cursor
OPEN cursor_name;

-- Get column values from the row and store them in series of variables
FETCH cursor_name INTO variable1[, variable2][, variable3]...

-- Close the cursor
CLOSE cursor_name;

Complete cursor example

USE ap;

DROP PROCEDURE IF EXISTS test;

DELIMITER //

CREATE PROCEDURE test()
BEGIN
  DECLARE invoice_id_var    INT;                                  (1)
  DECLARE invoice_total_var DECIMAL(9,2);  
  DECLARE row_not_found     TINYINT DEFAULT FALSE;
  DECLARE update_count      INT DEFAULT 0;

  DECLARE invoices_cursor CURSOR FOR                              (2)
    SELECT invoice_id, invoice_total  FROM invoices
    WHERE invoice_total - payment_total - credit_total > 0;
    
  DECLARE CONTINUE HANDLER FOR NOT FOUND                          (3)
    SET row_not_found = TRUE;

  OPEN invoices_cursor;                                           (4)
    
  WHILE row_not_found = FALSE DO                                  (5)
    FETCH invoices_cursor INTO invoice_id_var, invoice_total_var;

    IF invoice_total_var > 1000 THEN
      UPDATE invoices
      SET credit_total = credit_total + (invoice_total * .1)
      WHERE invoice_id = invoice_id_var;

      SET update_count = update_count + 1;
    END IF;
  END WHILE;
  
  CLOSE invoices_cursor;                                          (6)
    
  SELECT CONCAT(update_count, ' row(s) updated.');                (7)
  
END//

DELIMITER ;

CALL test();

1. Begins by declaring four variables.
2. Declares a cursor variable named invoices_cursor. Uses SELECT statement to define the result set for this cursor.
3. Declares an error handler that's executed when no more rows are found in the result set for the cursor.

• Sets row_not_found to TRUE
• Following WHILE loop executes only while the row_not_found variable is equal to FALSE, so this error handler causes the WHILE loop to stop executing.

1. Open the cursor
2. Use a WHILE loop to loop through each row in the cursor.

• FETCH statement gets the column values from the next row and stores them in the variables that were declared earlier.
• Then, the IF statement checks whether the value of the invoice_total column for the current row is greater than 1000.
• If it is, the UPDATE statement adds 10% of the invoice_total column to the credit_total column for the row, and a SET statement increments the count of the number of rows that have been updated.

1. Closes the cursor
2. Displays number of rows updated.

Error codes

The following are the most common error codes, but there are thousands of error codes.

Using condition handlers

This is error handling. You want to handle exceptions before you put your stored programs into production.

CONTINUE error handler

USE ap;

DROP PROCEDURE IF EXISTS test;

DELIMITER //

CREATE PROCEDURE test()
BEGIN
  DECLARE duplicate_entry_for_key INT DEFAULT FALSE;

  DECLARE CONTINUE HANDLER FOR 1062                                               (1)
    SET duplicate_entry_for_key = TRUE;                                           (2)

  INSERT INTO general_ledger_accounts VALUES (130, 'Cash');
  
  IF duplicate_entry_for_key = TRUE THEN
    SELECT 'Row was not inserted - duplicate key encountered.' AS message;
  ELSE
    SELECT '1 row was inserted.' AS message;    
  END IF;

END//

DELIMITER ;

CALL test();

1. Declares a handler for error code 1062 using the keyword CONTINUE. This allows the procdure to contine executing when the error is encounters.
2. Uses SET to set the variable to TRUE

EXIT error handler

This code exits the current block of code as soon as it encounters an error.

USE ap;

DROP PROCEDURE IF EXISTS test;

DELIMITER //

CREATE PROCEDURE test()
BEGIN
  DECLARE duplicate_entry_for_key INT DEFAULT FALSE;
  BEGIN
    DECLARE EXIT HANDLER FOR 1062
      SET duplicate_entry_for_key = TRUE;

    INSERT INTO general_ledger_accounts VALUES (130, 'Cash');
    
    SELECT '1 row was inserted.' AS message;    
  END;
  
  IF duplicate_entry_for_key = TRUE THEN
    SELECT 'Row was not inserted - duplicate key encountered.' AS message;
  END IF;
END//

DELIMITER ;

CALL test();

Named condition error handler

Use a named condition to handle the error that occurs when a row cannot be inserted. When this condition occurs, the stroed procedure displays a message that indicates that the row was not inserted because of a SQL exception.

This is like using a language-defined exception in Java, like IOException.

USE ap;

DROP PROCEDURE IF EXISTS test;

DELIMITER //

CREATE PROCEDURE test()
BEGIN
  DECLARE sql_error INT DEFAULT FALSE;
  BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
      SET sql_error = TRUE;

    INSERT INTO general_ledger_accounts VALUES (130, 'Cash');
    
    SELECT '1 row was inserted.' AS message;    
  END;
  
  IF sql_error = TRUE THEN
    SELECT 'Row was not inserted - SQL exception encountered.' AS message;	
  END IF;
END//

DELIMITER ;

CALL test();

Using multiple condition handlers

If you use multiple condition handlers, the most specific error handlers are executed first, and the least specific/most general are executed last.

USE ap;

DROP PROCEDURE IF EXISTS test;

DELIMITER //

CREATE PROCEDURE test()
BEGIN
  DECLARE duplicate_entry_for_key INT DEFAULT FALSE;
  DECLARE column_cannot_be_null   INT DEFAULT FALSE;
  DECLARE sql_exception           INT DEFAULT FALSE;
  
  BEGIN
    DECLARE EXIT HANDLER FOR 1062                           (1)
      SET duplicate_entry_for_key = TRUE;
    DECLARE EXIT HANDLER FOR 1048
      SET column_cannot_be_null = TRUE;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
      SET sql_exception = TRUE;

    INSERT INTO general_ledger_accounts VALUES (NULL, 'Test');
    
    SELECT '1 row was inserted.' AS message;    
  END;
  
  IF duplicate_entry_for_key = TRUE THEN
    SELECT 'Row was not inserted - duplicate key encountered.' AS message;
  ELSEIF column_cannot_be_null = TRUE THEN
    SELECT 'Row was not inserted - column cannot be null.' AS message;
  ELSEIF sql_exception = TRUE THEN
    SELECT 'Row was not inserted - SQL exception encountered.' AS message;	
  END IF;
END//

DELIMITER ;

CALL test();

1. Declared condition handlers from most to least specific.

Transactions and locking

Transactions

A transaction is a group of SQL statements that you combine into a single logical unit of work. Combining the SQL statements can prevent certain kinds of database errors.

NOTE: Some storage engines do not support transactions.

To start a transaction, code the START TRANSACTION statement. This turns off autocommit mode until the statements in the transaction are committed or rolled back. To commit changes, code a COMMIT statement. TO roll back the changes, use a ROLLBACK statment.

USE ap;

DROP PROCEDURE IF EXISTS test;

DELIMITER //

CREATE PROCEDURE test()
BEGIN
  DECLARE sql_error INT DEFAULT FALSE;
  
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    SET sql_error = TRUE;

  START TRANSACTION;                                  (1)
  
  INSERT INTO invoices
  VALUES (115, 34, 'ZXA-080', '2014-06-30', 
          14092.59, 0, 0, 3, '2014-09-30', NULL);

  INSERT INTO invoice_line_items 
  VALUES (115, 1, 160, 4447.23, 'HW upgrade');
  
  INSERT INTO invoice_line_items 
  VALUES (115, 2, 167, 9645.36, 'OS upgrade');
  
  IF sql_error = FALSE THEN
    COMMIT;                                           (2)
    SELECT 'The transaction was committed.';
  ELSE
    ROLLBACK;                                         (3)
    SELECT 'The transaction was rolled back.';
  END IF;
END//

DELIMITER ;

CALL test();

1. The START TRANSACTION statement
2. The COMMIT statement
3. The ROLLBACK statement

Working with save points

A SAVEPOINT statement is used to identify a save point a statement that is included in the script.
When you use save points, you can roll back a transaction to the beginnning of the transaction or to a particular save point.

USE ap;

START TRANSACTION;
  
SAVEPOINT before_invoice;                               (1)

INSERT INTO invoices
VALUES (115, 34, 'ZXA-080', '2015-01-18', 
        14092.59, 0, 0, 3, '2015-04-18', NULL);

SAVEPOINT before_line_item1;                            (1)

INSERT INTO invoice_line_items 
VALUES (115, 1, 160, 4447.23, 'HW upgrade');
  
SAVEPOINT before_line_item2;                            (1)

INSERT INTO invoice_line_items 
VALUES (115, 2, 167, 9645.36,'OS upgrade');

-- SELECT invoice_id, invoice_sequence FROM invoice_line_items WHERE invoice_id = 115;
ROLLBACK TO SAVEPOINT before_line_item2;                (2)

-- SELECT invoice_id, invoice_sequence FROM invoice_line_items WHERE invoice_id = 115;
ROLLBACK TO SAVEPOINT before_line_item1;                (2)

-- SELECT invoice_id, invoice_sequence FROM invoice_line_items WHERE invoice_id = 115;
ROLLBACK TO SAVEPOINT before_invoice;                   (2)

-- SELECT invoice_id, invoice_number FROM invoices WHERE invoice_id = 115;
COMMIT;

1. Create a save point with the specified name
2. Use the ROLLBACK TO SAVEPOINT statement to roll back a transaction to the specified save point

Concurrency and locking

Concurrency is when two or more users have access to the same database and they are working on the same data at the same time. Concurrency is a problem when one user updates data that other users are also viewing or updating.

Use START TRANSACTION and COMMIT to lock the data you are working to prevent concurrency issues.

Setting the transaction isolation level

The transaction isolation level controls the degree to which transactions are isolated from one another. At the more restrictive isolation levels, concurrency problems are reduced or eliminated.

Use the following for levels:

1. READ UNCOMMITTED
2. READ COMMITTED
3. REPEATABLE READ
4. SERIALIZABLE

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

How to lock selected rows

In some cases, the default isolation levels do not work the way you want. So, you can use the following statements:

• SELECT ... FOR SHARE locks the selected rows so that other transactions can read those rows but cannot modiy them until the transaction commits. If this statement attempts to read any rows that have been locked for update by another transaction, it waits until that transaction commits so that it can ue the most current values.
• SELECT ... FOR UPDATE locks the selected rows and any associated indexes so that other transactions can't read or modify them until the transaction commits. If this statement attempts to read any rows that have been locked for share or for update by another transaction, it waits until that transaction commits so it can use the most current values.
• SELECT ... FOR UPDATE NOWAIT, doesn't wait for a lock to be released, it returns an error immediatley.
• SELECT ... FOR UPDATE SKIP LOCKED, the statement doesn't wait for a lock to be released, it skips the locked rows and returns any rows that are not locked.

USE ex;

-- Transaction B
START TRANSACTION;
SELECT * FROM sales_reps WHERE rep_id < 5 FOR UPDATE;
COMMIT;

-- Transaction C
START TRANSACTION;
SELECT * FROM sales_reps WHERE rep_id < 5 FOR UPDATE NOWAIT;
COMMIT;

-- Transaction D
START TRANSACTION;
SELECT * FROM sales_reps WHERE rep_id < 5 FOR UPDATE SKIP LOCKED;
COMMIT;

Deadlocks

A deadlock occurs when neither of two transactins can be committed because each transaction has a lock on a resource needed by the other transaction.

Creating stored procedures and functions

Triggers

A trigger is a named database object that is executed, or fired, automatically when a particular type of SQL statement is executed. Triggers can be used to enforce rules for data consistency that can't be enforced by constraints.

AFTER triggers are used to store information about a statement after it executes. For example, an audit table.

Backup and restore

You should regularly back up the database in case the database becomes corrupted.
A full backup includes the structure and content of a database. You should perform these according to a regular schedule.
An incremental backup contains only changes that have been made to the structure and content of a database since the last full backup.
A point-in-time recovery (PITR) allows you to restore the data up to any specified point in time.

Which of the following SELECT statement would you use to prevent duplicate rows from being returned?

How would you code a SELECT clause so it returns all columns from the base table?

When you code a SELECT statement you must code the four main clauses in the following order SELECT from where order by?

When a column in a table is defined what determines the kind of data it can store?