SQL (Single table operations)
Projected Time
About 3.5-4 hours
Prerequisites
Motivation
Most software applications store data of some sort – user data, content, etc. Databases are used to store data, so software developers should know how to use them. Relational databases are a common type of database that are good for storing many types of data. Most companies use relational databases to store their data.
Objectives
Participants will be able to:
- Interact with SQLite through the command line and through a GUI SQLite browser such as DB Browser for SQLite
- Create database tables
- Add, update, and delete data
- Query data
- Design a basic relational database schema fitting a use case
Specific Things to Learn
- Create a table
- Query data from a table
- Filter and limit
- Insert data into a table
- Update
- Delete
Materials
Lesson
- Read through lesson slides SQL
- Video walkthrough of lesson slides SQL
Common Mistakes / Misconceptions
- SQL commands are case-insensitive. This is unlike most programming languages! For example, in SQL, these two commands will both do the same thing:
SELECT * FROM food;select * From fOOd;
- Don’t forget the semicolons! SQL commands will only run if you put a semicolon at the end of them.
Independent Practice
Section 1: Creating tables and basic querying
- Work through the Codecademy SQL Tutorial Section 1 (Manipulation):
- SQLite is a version of SQL that comes installed on mac! Try it out:
- In Terminal, type
sqlite3 food.sqlite3 to use SQLite. This command will open sqlite and save your database to a file named food.sqlite3.
- Try adding a table with the command
CREATE TABLE food (name TEXT, calories INTEGER);
- See that the table was created by typing
.tables to see the list of all existing tables.
- Try adding some data:
INSERT INTO food VALUES ("pizza", 500);
- See the data:
SELECT * from food;
Using SQLite, try creating tables and adding, updating, deleting, and querying data yourself!
Download and install DB Browser for SQLite via its download page. Try opening the database you just created! Browse the data and get a feel for how to move around. One way to view SQL data is through the command line, and another way is through graphical tools like the SQLite Browser.
- Try writing queries for the following using the
food table:
- Add a column to the table called
meal
- Update “pizza” to have its meal be “dinner”
- Insert 4 more rows into the food DB. Be sure to include values for all 3 columns –
name, calories, and meal
- Update “pizza” to have a different calories number
- Select only the names of all the foods
- Make up a query of your own using another command you’ve learned
Section 2: Queries
Work through the Codecademy SQL Tutorial Section 2 (Queries)
- In sqlite, try writing queries for the following using the
food table. Add some data to the table that meets the criteria below so you’ll have something to query for.
- Select all foods that have under 100 calories
- Select the names of all foods that start with the letter “a”
- Select all foods that start with “a” AND have meal equal to “dinner”
- Select all foods where meal is “breakfast”, sorted by calorie number
- Make up a query of your own using another command you’ve learned
Section 3: Advanced queries
Now do the Codecademy SQL Tutorial Section 3 (Aggregate functions)
- Try writing queries for the following using the
food table:
- Count the number of rows in the food table
- Find the food with the maximum number of calories
- Find the average number of calories for breakfast food
- Make up a query of your own using another command you’ve learned
Supplemental Materials