Skip to content

Latest commit

 

History

History
83 lines (60 loc) · 3.17 KB

sql.md

File metadata and controls

83 lines (60 loc) · 3.17 KB

SQL

Projected Time

4 hours

Prerequisites

  • Desire to learn more about databases.

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.

Objective

Apprentices will be able to:

  • Create database tables
  • Add, update, and delete data
  • Query data
  • Design a basic relational database schema fitting a use case

Specific Things To Teach

  • Create a table
  • Query data from a table
  • Filter and limit
  • Joins
  • Insert data into a table
  • Update
  • Delete

Materials

Lesson

SQL (video walkthrough of slides)

Slides

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

  1. Work through the Codecademy SQL Tutorial: https://www.codecademy.com/learn/learn-sql

  2. SQLite is a version of SQL that comes installed on mac! Try it out:

  • In Terminal, type sqlite3 to use SQLite.
  • Try adding a table with a command like 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;

Try creating tables and adding, updating, deleting, and querying data yourself!

Challenge

  • In SQLite, create the tables for an app similar to Twitter. Your database should be able to store: user information, tweets, and which users follow each other.
  • Compare your table setup with another apprentice's.
  • Add data to the tables
  • Try writing queries that get data such as:
    • All the tweets by a given user
    • The 10 most recent tweets
    • Use a join to get a user's info along with their tweets
    • Find which user has the most followers
    • Make up your own!

Check for Understanding

Make a cheat sheet of the SQL operations you know and what they do.