MySQL Code

Let’s start off with some code snippets. In the code below, written for a MySQL database, I show off several different types of joins, creating and calling procedures, and some other various MySQL tools to get the job done right.

Here is my database setup/teardown code and some simple insert scripts:

USE IMT;

# First clean any existing database
DROP PROCEDURE IF EXISTS latest_orders;
DROP PROCEDURE IF EXISTS state_products;
DROP PROCEDURE IF EXISTS add_option;
DROP TABLE IF EXISTS options;
DROP TABLE IF EXISTS companies;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS customerData;
DROP TABLE IF EXISTS orders;
DROP DATABASE IF EXISTS IMT;

# Create database and use it
CREATE DATABASE IMT;
USE IMT;

# Add initial tables

CREATE TABLE orders (
orderNumber INT,
customerOid INT,
orderInformationOid INT,
productOid INT,
totalPremium DECIMAL,
purchaseDateTime DATETIME
);

CREATE TABLE customerData (
customerOid INT,
firstName VARCHAR(50),
middleInitial VARCHAR(1),
lastName VARCHAR(50),
street1 VARCHAR(100),
street2 VARCHAR(100),
city VARCHAR(50),
state VARCHAR(2),
zip VARCHAR(10)
);

CREATE TABLE products (
productOid INT,
productName VARCHAR(50),
companyOid INT
);

CREATE TABLE companies (
companyOid INT,
companyName VARCHAR(100),
street1 VARCHAR(100),
street2 VARCHAR(100),
city VARCHAR(50),
stateCode VARCHAR(2),
zip VARCHAR(10)
);

# Populate tables with realistic test data

INSERT INTO companies (companyOid, companyName, street1, street2, city, stateCode, zip) VALUES (1,"IMT","10 Elm St","Warwick",NULL,"RI","12345");
INSERT INTO companies (companyOid, companyName, street1, street2, city, stateCode, zip) VALUES (2,"Lapis","11 Birch Ln","Suite 22","Cranston","NY","67890");
INSERT INTO companies (companyOid, companyName, street1, street2, city, stateCode, zip) VALUES (3,"Progeny","12 Holly Rd",NULL,"E Greenwich","VT","23456");
INSERT INTO companies (companyOid, companyName, street1, street2, city, stateCode, zip) VALUES (4,"RITE Solutions","13 Ginkgo Ct","Apt 7","W. Greenwich","NH","78901");
INSERT INTO companies (companyOid, companyName, street1, street2, city, stateCode, zip) VALUES (5,"Anteon","14 Hazelnut Cir",NULL,"Exeter","MA","89456");
INSERT INTO companies (companyOid, companyName) VALUES (6, "Jim Bob's Insurance");

INSERT INTO products (productOid, productName, companyOid) VALUES (1,"Widget",5);
INSERT INTO products (productOid, productName, companyOid) VALUES (2,"Whatchamacallit",4);
INSERT INTO products (productOid, productName, companyOid) VALUES (3,"Whosawhatsit",3);
INSERT INTO products (productOid, productName, companyOid) VALUES (4,"Ball",2);
INSERT INTO products (productOid, productName, companyOid) VALUES (5,"Pre-owned Lexus", 6);

INSERT INTO customerData (customerOid, firstName, middleInitial, lastName, street1, street2, city, state, zip) VALUES (1,"Nate","J","Washor","1 Main St",NULL,"Coventry","RI","02816");
INSERT INTO customerData (customerOid, firstName, middleInitial, lastName, street1, street2, city, state, zip) VALUES (2,"Sammy",NULL,"Wu","2 Side Rd","Apt 3","Keene","NH","03431");
INSERT INTO customerData (customerOid, firstName, middleInitial, lastName, street1, street2, city, state, zip) VALUES (3,"Zane","Y","Roo","3 Hidden Court",NULL,"Plymouth","MA","02860");

INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (1,1,11,1,1.33,"2007-4-22 0:00:00");
INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (2,1,12,2,5.55,"2007-4-27 0:00:00");
INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (3,3,13,3,27.55,"2007-5-2 0:00:00");
INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (4,2,14,4,23.55,"2007-5-7 0:00:00");
INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (5,2,15,1,32.55,"2007-5-12 0:00:00");
INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (6,3,16,2,28.55,"2007-5-17 0:00:00");
INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (7,2,17,3,24.55,"2007-5-22 0:00:00");
INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (8,2,18,4,33.55,"2007-5-27 0:00:00");
INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (9,3,19,1,29.55,"2007-6-1 0:00:00");
INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (10,2,20,2,25.55,"2007-6-6 0:00:00");

And here are some queries, written so all you need to do is copy and past into a MySQL database. I used Google.

# Solutions...
# ---
# (1) Write a query to report the name, address and order number of the
# 50 most recent orders.

DELIMITER //

CREATE PROCEDURE latest_orders
(IN inMax INT)
BEGIN
SELECT orders.orderNumber,
CONCAT_WS(' ',
NULLIF(customerData.firstName, ''),
NULLIF(customerData.middleInitial, ''),
NULLIF(customerData.lastName, '')
) AS customerName,
CONCAT_WS(', ',
NULLIF(customerData.street1, ''),
NULLIF(customerData.street2, ''),
NULLIF(customerData.city, ''),
NULLIF(customerData.state, ''),
NULLIF(customerData.zip, '')
) AS customerAddress
FROM orders INNER JOIN customerData
ON orders.customerOid = customerData.customerOid
ORDER BY orders.purchaseDateTime DESC LIMIT inMax;
END//

DELIMITER ;

# Test results
CALL latest_orders(50);

# ---
# (2) Write a query to report all products purchased by residents of
# Rhode Island.

DELIMITER //

CREATE PROCEDURE state_products
(IN inStateAbbrev VARCHAR(2))
BEGIN
SELECT DISTINCT products.productName
FROM products INNER JOIN orders
ON products.productOid = orders.productOid INNER JOIN customerData
ON customerData.customerOid = orders.customerOid AND customerData.state = inStateAbbrev;
END//

DELIMITER ;

# Test results
CALL state_products("RI");

# ---
# (3) Revise the query you formulated in step (1) to bring in the optionName
# from this table. Even if there are no entries in this table, we still
# want the order to to be displayed.

CREATE TABLE options (
orderNumber INT,
optionName VARCHAR(50)
);

DELIMITER //

DROP PROCEDURE IF EXISTS latest_orders;
CREATE PROCEDURE latest_orders
(IN inMax INT)
BEGIN
SELECT orders.orderNumber,
CONCAT_WS(' ',
NULLIF(customerData.firstName, ''),
NULLIF(customerData.middleInitial, ''),
NULLIF(customerData.lastName, '')
) AS customerName,
CONCAT_WS(', ',
NULLIF(customerData.street1, ''),
NULLIF(customerData.street2, ''),
NULLIF(customerData.city, ''),
NULLIF(customerData.state, ''),
NULLIF(customerData.zip, '')
) AS customerAddress,
options.optionName
FROM orders INNER JOIN customerData
ON orders.customerOid = customerData.customerOid LEFT OUTER JOIN options
ON options.orderNumber = orders.orderNumber
ORDER BY orders.purchaseDateTime DESC LIMIT inMax;
END//

DELIMITER ;

# Test results
CALL latest_orders(50);

# ---
# (4) Given the table added in question (3), let us assume that we want
# to add the option "Rental Car Coverage" to all previously
# purchased orders from company "Jim Bob's Insurance". Assume that
# no orders have this coverage already. What query would you build
# to accomplish this?

DELIMITER //

CREATE PROCEDURE add_option_to_company_orders(IN inCompanyName VARCHAR(100), IN inOption VARCHAR(50))
BEGIN
INSERT INTO options (orderNumber, optionName)
SELECT orders.orderNumber, "Rental Car Coverage"
FROM orders INNER JOIN products
ON orders.productOid = products.productOid INNER JOIN companies
ON products.companyOid = companies.companyOid AND companies.companyName = "Jim Bob's Insurance"
WHERE NOT EXISTS (SELECT 1 FROM options WHERE options.orderNumber = orders.orderNumber);
END//

DELIMITER ;

CALL add_option_to_company_orders("Jim Bob's Insurance", "Rental Car Coverage");

# Call a few extra times to test no duplicate entries possible

CALL add_option_to_company_orders("Jim Bob's Insurance", "Rental Car Coverage");
CALL add_option_to_company_orders("Jim Bob's Insurance", "Rental Car Coverage");
CALL add_option_to_company_orders("Jim Bob's Insurance", "Rental Car Coverage");

SELECT * FROM options;

# Let's check changes are reflected from (3) post (4)

CALL latest_orders(50);