ajb85 / webdb-i-challenge

Web DB I Module Challenge

SQL Exercises

Topics

• Structured Query Language (SQL)
• Relational Databases
• SQLite
• Writing Basic Queries

Assignment

For this lab you will:

• write SQL statements against a pre-populated database using an online tool. Once you have the correct SQL Statement for each query, write it inside the queries.md file under the appropriate heading.
• install SQLite Studio and use it to create a database.
• using SQLite Studio add a table to the database you just created.

Write Basic Queries

Visit SQL Try Editor at W3Schools.com using the Google Chrome (or Chromium if you use Linux) browser and write SQL queries for the following requirements:

• find all customers that live in London. Returns 6 records.
• find all customers with postal code 1010. Returns 3 customers.
• find the phone number for the supplier with the id 11. Should be [phone].
• list orders descending by the order date. The order with date 1997-02-12 should be at the top.
• find all suppliers who have names longer than 20 characters. You can use length(SupplierName) to get the length of the name. Returns 11 records.
• find all customers that include the word "market" in the name. Should return 4 records.

Clicking the Restore Database button in the page will repopulate the database with the original data and discard all changes you have made.

Create Database and Table

• use SQLite Studio to create a database, name it budget.sqlite3.

• add an accounts table with the following schema:

  • id, numeric value with no decimal places that should autoincrement.
  • name, string, add whatever is necessary to make searching by name faster.
  • budget numeric value.

• constraints

  • the id should be the primary key for the table.
  • account name should be unique.
  • account budget is required.

Stretch Problems

The following exercises require research, the concepts needed to complete them have not been covered in class yet.

• add a customer record for "The Shire", the contact name is "Bilbo Baggins" the address is "1 Hobbit-Hole" in "Bag End", postal code "111" and the country is "Middle Earth".
• update Bilbo Baggins record so that the postal code changes to "11122".
• delete all customers that have no orders. Should delete 18 records.
• list orders grouped by customer showing the number of orders per customer. Rattlesnake Canyon Grocery should have 7 orders.
• list customers names and the number of orders per customer. Sort the list by number of orders in descending order. Ernst Handel should be at the top with 10 orders followed by QUICK-Stop, Rattlesnake Canyon Grocery and Wartian Herkku with 7 orders each.
• list orders grouped by customer's city showing number of orders per city. Returns 58 Records with Aachen showing 2 orders and Albuquerque showing 7 orders.