May the 4th be with you!

In interviews, a software engineer is often asked to draw something on the board. One style of diagramming is a flow chart. Flowcharts are essential in a quality design processes.

A common type of solution software engineers must design is a recursive function – a function that calls itself to compute an answer to a given question. Interviewers want to know their hired developers are used to thinking this way.

Take for example, the following test:

It’s a simple test. It’s not designed to trip anyone up – but to see if the candidate has the knowledge to tackle this type of problem. Here is my simple solution:

I hope this little exercise was of use to you.

I have been an Apple boy. I’ve been a Linux and a PC. I’ve used blackberries and countless flips. iOS was king when she came out, but now I have a new favorite… Android running on the Google Pixel 2. It’s just so good.

The details.

I have an awesome case on my phone. Sure this is a phone review, but I love it so much, I had to mention it. I can store my license and 2 cards with ease. It even works as a kickstand and offers more protection for my favorite phone.

The first thing I love about the Pixel 2. The battery life is awesome! I can go days between charges if I haven’t been using my phone extensively, and even then it still lasts through an entire busy day, the rides to and from home,  a handful of phone calls, cooking a big dinner and resting with my family on the couch, browsing Reddit – on my phone. This phone truly goes all day (if you are having issues with your Pixel 2’s battery – it’s likely an app you installed. “Kaspersky Battery Life” app is useful in figuring out which one).

Call clarity is miles beyond my old phone. I’m not entirely sure whether that is my new carrier (Verizon) or that my old phone was a real piece of junk.

The screen on this phone is amazing. Crystal clear and works quite well in bright sun and at night. The best thing about the screen though is that it is an OLED screen. What that means is blacks are truly black! A black pixel emits no light as opposed to LCD screens that emit a black color light. The contrast on the phone is great in all types of light and OLED also cuts down drastically on battery usage

The camera. Fast shutter. Great angle. Crystal pictures. Fun effects – all the same effects available on an iPhone. Takes great pictures.

Speed. This thing is a powerhouse. I am running the 128GB edition, but my girlfriend has the 64 and she’s not had any issues either. It has all the power and memory to play all the latest games. It never lags between switching apps, all core functionality is very responsive. Rarely do I need to reboot the phone. I’ve never had such a unifying phone that just works well like this phone does.

Storage. With the cloud, storage space is becoming less important, but I still always opt for the most space. Simply put, computers (your phone is a computer) work better when they are using less than (very roughly) 50% of their available drive space. I want a high performance phone, so I opted for double the space.

The size. I’m glad I didn’t get the XL. The regular Pixel 2 fits my hands quite well. With one of Android’s latest features – being able to split the screen – I now have all the space I need to multitask. Split-screen is a little clunky still (new tech always is) but it’s already made great strides.

Widgets. My phone is running probably 50 or so widgets and an active desktop. It just feels and looks so cool. And all these widgets don’t eat my battery (make sure you disable notifications for apps that don’t really need it – seriously, does your diary app really need notifications?)

This phone is great. I highly recommend it.

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);