Proj_002

Vita Wiebe
IT125
Project #2
*/

USE ex;

# Section 1
# Section 1.1: Use

#1, 24 rows returned.
SELECT CONCAT(customer_last_name, ", ", customer_first_name) AS "Customer Name", customer_phone AS "Phone Number"
FROM customers;

#2, 24 rows returned.
SELECT customer_last_name AS "Last", customer_first_name AS "first", customer_phone as Phone
FROM customers;

#3, 40 rows returned.
SELECT invoice_number AS Invoice, ROUND(invoice_total * 0.01, 2) AS "With Interest"
FROM active_invoices;

#4, 8 rows returned.
# Note to self: cannot use aliases in WHERE clause.
SELECT invoice_number AS Invoice, invoice_total AS Total, invoice_due_date AS "Due Date"
FROM active_invoices
WHERE invoice_total > 1000;

#5, 5 rows returned.
SELECT DISTINCT terms_id
FROM active_invoices;

#6, 2 rows returned.
SELECT invoice_id AS Invoice, invoice_total AS Total, credit_total AS Credit
FROM active_invoices
WHERE invoice_total >= 1000 AND credit_total > 0;

#7, 2 rows returned.
SELECT CONCAT(customer_last_name, ", ", customer_first_name) AS "Customer Name", customer_zip AS "Zip Code",
    customer_city AS City
FROM customers
WHERE customer_state = 'IL' AND customer_city = "Normal" or customer_city = "Peoria";

#8, 36 rows returned.
SELECT invoice_number AS Invoice, invoice_date AS 'Date', terms_id AS TermsID
FROM active_invoices
WHERE terms_id REGEXP '[2, 3, 4]';

#9, 36 rows returned.
SELECT invoice_number AS Invoice, invoice_date AS 'Date', terms_id AS TermsID
FROM active_invoices
WHERE terms_id BETWEEN 2 AND 4;

#10, 16 rows returned.
SELECT invoice_number
FROM active_invoices
WHERE invoice_number LIKE '%/%' OR invoice_number LIKE '%-%';

#11, 4 rows returned.
SELECT invoice_id AS ID, invoice_total AS Total
FROM null_sample
WHERE invoice_total IS NOT NULL;
# The following is to verify that the results of #11 are reasonable.
SELECT * FROM null_sample;

#12, 1 row returned.
SELECT customer_last_name, customer_first_name, customer_phone
FROM customers
WHERE customer_state = 'IN'
ORDER BY customer_last_name, customer_first_name;

# the following 2 queries were to verify the results in #12,
# as I thought it odd that only one customer was in Indiana.
SELECT customer_last_name, customer_first_name, customer_phone
FROM customers
WHERE customer_state LIKE '%IN%';
# Returns all the customers, ordered by state.
SELECT *
FROM customers
ORDER BY customer_state;

#13, 8 rows returned.
SELECT invoice_number, invoice_due_date AS 'Due Date', invoice_total AS Total
FROM active_invoices
WHERE invoice_total > 1000
ORDER BY invoice_total DESC;

#14, 0 rows returned (?!!).
SELECT invoice_id
FROM active_invoices
WHERE (invoice_id = 4) AND (invoice_total BETWEEN 100 AND 2000) AND (invoice_due_date LIKE '2014-07%')
ORDER BY invoice_due_date DESC;
# Attempting to confirm suspect results from #14...
SELECT *
FROM active_invoices
WHERE invoice_due_date LIKE '2014-07%'
ORDER BY invoice_due_date DESC;

#15, 2 rows returned.
SELECT customer_last_name, customer_first_name, customer_state
FROM customers
WHERE (customer_last_name LIKE 'R%' OR customer_last_name LIKE 'S%' OR customer_last_name LIKE 'T%')
    AND customer_state IN ('WA', 'CA', 'OR')
ORDER BY customer_state, customer_last_name, customer_first_name;

# Section 1.2 Design
/*
Table: AncientCityNames
PK (FK to City table, NN): AncientName CHAR(35) # This would be a red key; can't be NULL.
                                                # Addt'l ancient city names for the same city would
                                                # get their own row in this table.
FK (must be NN): ModernName CHAR(35) # This would be a red diamond; solid, as it can't be NULL.  Maps
        (corresponds) to City table's Name field.
(Composite FK corresponding to Code in Country table, NN): CountryCode CHAR(3)
(Composite FK corresponding to Name field/PK of Country table, NN): CountryName CHAR(52)
Last_Year_Used INT(4) # Can't be NULL (must be NN).
Era ENUM('BCE', 'CE') # Can't be NULL (must be NN).
Mayor_Name CHAR(60) # This can be NULL if they don't have a mayor or similar figurehead.
*/

# Section 2
/*
Numbers 12, 14, and 15 yielded somewhat suspect results, but as written in my corresponding comments,
I was able to confirm the results were accurate with additional queries.
*/