College of Charleston SQL Programming Code

INFM 330 – Exercise 05Section A: ClassicModels database queries
Use the ClassicModels script and create the database, including its values. Run the queries in
Workbench.
Don’t forget to type in “USE ClassicModels” before running queries!
1. Who are the employees in Boston?
2. Report total payments for October 28, 2004.
47411.33
3. List the products ordered on a Monday.
4. Which orders have a value greater than $5,000? (Hint: group by having)
5. Which customers are located in Germany?
6. How many customers are located in Germany?
13
7. List the city and the location of all customers in Germany.
8. How far apart are Stuttgart customers from Berlin customers in km? Hint: Use
ST_AsText(customerLocation); Divide by 1000 in order to get km
512.8392178396546
9. Create a list that displays the distances in km from Stuttgart to all other cities in Germany that
have customers. Hint: build on the previous
As your deliverable, please submit one .sql file that contains all the SQL queries in OAKS.
Section B: Build a room reservation system in Knack.com
I would like you to re-create a version of the reservation system with a different tool. Get an account
with Knack. Note that you have only a two-week trial period for this. So make sure you get this all done
before your account expires.
For simplicity, set up the following data model with two entities:
Room:
Reservation:
Create a 1:m connection between both that looks as follows:
Go to Pages. Knack has already provided you with some generated Pages. Your goal is to create a
calendar Page, i.e., a page that shows a calendar with varies reservation entries.
Go to this video: https://www.youtube.com/watch?v=4XIfKNy6684 (27 minutes)
As your deliverables, please submit one Word file that contains a screen shoot (or two) along with the
answer to the following question: Which tool did you like better (Caspio or Knack)? Tell me why!