Technical Pop Quiz

Alright, here's a pop quiz. And, it has a prize. If you can answer this question correctly, and live in Jax, I will get you an interview for a technical position that requires mad SQL skillz(you should be thinking Napoleon Dynamite right now).

There's a database. It has several tables. I need information from one primary table, a related detail table, and two lookup tables. The linked image below is a diagram of the database.


ERD for the quiz

Write me some stored procedures:

1. Get all customers
2. Get all customers whose customer name starts with 'Johnson'
3. Get all customers whose address contains 'FL'
4. Return a count of the number of orders per customer. I want the complete list of customers, even if they have zero orders.
5. Get me all of the details about an order. If there is an id field, return the description from the related table, instead of the ID. I will provide the order ID I am interested in.

Seriously, there is a junior dev position open at my work. They are looking for someone with strong sql skills. This position will eventually grow into a development position. There aren't really any requirements (i.e. college degree); other than being pretty good at SQL.

If you are interested, leave a comment with your stored procs, or link to a text file that contains your stored procs.