Working with SQL
Surf to SQL Try Editor at W3Schools.com
Answer the following data queries. Keep track of the SQL you write by pasting it into this document under its appropriate header below. You will be submitting that through the regular fork, change, pull process.
Clicking the Restore Database
button in the page will repopulate the database with the original data and discard all changes you have made.
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.
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".
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.
delete all customers that have no orders. Should delete 17 (or 18 if you haven't deleted the record added) records.
-
use
SQLite Studio
to create a database, name itbudget.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.
- the