Week 2 lecture notes

#USE selects the database to be queried or altered.
# This is one way of writing comments, using hashtags the way Python does.
USE ap;

-- This is also a comment.  There must be a space after the dashes to work.
DESCRIBE invoices;

/*
This is a way of writing
multi-line comments.
*/
# Can list several operations, separating them with commas.
SELECT 4 * 53, 11 MOD 2, 20 * 5 + 2;

# Will show all the rows and columns associated with invoices table.
# (Select all columns, all rows by default).
SELECT *
FROM invoices;

# specify certain columns
SELECT invoice_number, invoice_date, credit_total
FROM invoice_archive;

# AS allows visual renaming of columns in output.
SELECT invoice_number AS 'ID', invoice_date AS 'date', invoice_total AS 'total'
FROM invoices;

# Can specify format output precision and with commas,
# as shown below, using FORMAT;
# FORMAT returns output as text, however, not numbers.
# (Not required).
SELECT invoice_id AS 'Id', FORMAT(invoice_total * 1.034, 2) AS 'total times some stuff'
FROM invoices;

# DISTINCT prevents outputting duplicate values.
# The more lists, the fewer duplicates will be caught,
# since it will be comparing sets of data rather than just multiple columns.
# (Affects every column on the line after it in list, taken as a set).
SELECT DISTINCT vendor_id
FROM invoices;

# show all data from ex's active_invoices table.
USE ex;
SELECT * FROM active_invoices;

# show all data from active_invoices table of
# ex's invoice numbers, invoice dates, and invoice totals.
SELECT invoice_number, invoice_date, invoice_total
FROM active_invoices;

# show all invoice nums plus the difference beetween
# the invoice total and the credit total.
SELECT invoice_number, invoice_total - credit_total
FROM active_invoices;

# same as above, but rename columns in displayed output.
SELECT invoice_number AS 'invoice', invoice_total - credit_total AS 'remaining amount'
FROM active_invoices;

# To display last name and first name as distinct columns.
SELECT customer_last_name AS 'last name', customer_first_name AS 'first name'
FROM customers;

# Concatenate the first and last name columns together in
# last, first format (with comma).
SELECT CONCAT(customer_last_name, ', ', customer_first_name) AS 'name'
FROM customers;

# Conditions: used to filter results
SELECT customer_last_name AS 'post-M last names'
FROM customers
WHERE customer_last_name>'M';

# Returns nothing because no literal that says "Illinois"
SELECT CONCAT(customer_last_name, ", ", customer_first_name)
FROM customers
WHERE customer_state='Illinois';

SELECT CONCAT(customer_last_name, ", ", customer_first_name) AS 'full name'
FROM customers
WHERE customer_state='IL';

SELECT customer_last_name AS 'last name', customer_first_name AS 'first name'
FROM customers
WHERE customer_state='WA' OR customer_state='OR';

# Displays last names of all customers whose last name is Jones or comes after.
SELECT customer_last_name
FROM customers
WHERE customer_last_name > 'Jones';

# Display phone numbers to observe format.
SELECT customer_phone
FROM customers;

# Return all customers who have phone numbers
# in the 503 area code.
SELECT customer_last_name, customer_first_name
FROM customers
WHERE customer_phone LIKE '(503)%';

SELECT customer_last_name, customer_first_name
FROM customers
WHERE customer_state IN ('WA', 'ID', 'WY');

# Sort your output by listing one or more fields in the ORDER BY clause
# e.g. ORDER BY last_name
# or multi-level sort: ORDER BY last_name, first_name
SELECT customer_address AS 'address'
FROM customers
WHERE customer_state > 'M'
ORDER BY customer_last_name, customer_first_name;

#
SELECT *
FROM active_invoices
WHERE invoice_date LIKE '2014-04%' AND invoice_total > 1500
ORDER BY invoice_date DESC;

# Get a list of vendor IDs for invoices whose terms ID is 4 or 5.
# No duplicates, sort by vendor ID.
SELECT DISTINCT vendor_id as 'Vendor ID'
FROM active_invoices
WHERE terms_id IN (4, 5)
ORDER BY vendor_id;

SELECT invoice_number
FROM active_invoices
WHERE invoice_number LIKE '%-%';

SELECT invoice_number
FROM active_invoices
WHERE invoice_number NOT LIKE '%-%';

    SELECT customer_last_name, customer_first_name, 'Accounting' AS Dept
    FROM customers
    WHERE customer_city LIKE 'S%'
UNION
    SELECT customer_last_name, customer_first_name, 'Marketing' AS Dept
    FROM customers
    WHERE customer_city LIKE 'L%';