SQL Intermission Work

SQL is a programming language used to interact with a database. Learning to interact with databases is crucial to your work as a backend developer. While we eventually want to use libraries that will write SQL for us, it is important to be familiar with how SQL works under the hood so that you understand what your code is doing at the database level.

For your SQl intermission work, you will complete an introductory tutorial, and then will complete some exercises to help you practice the most important SQL concepts. This assignment is meant as an introduction to sql, so don’t be alarmed if you don’t feel like you have mastered these concepts. Use the accompanying Checks for Understanding to assess yourself.

Jumpstart Lab Tutorial

Work through the Jumpstart Lab SQL Tutorial, but do not complete the “Sequel” tutorial linked at the bottom of the Jumpstart Lab page. Then complete the Checks for Understanding.

Checks for Understanding

1. What is a database?
2. What is SQL?
3. What is SQLite3?
4. What is a Table?
5. What is a primary key?
6. What is a foreign key?
7. Explain what each of the following SQL commands do:
   • insert
   • select
   • where
   • order by
   • inner join

SQL Exercises

Now you’ll work through some of the exercises at pgexercises.com. In the Jumpstart Lab tutorial, you used SQLite3. In these exercises, you will use Postgresql. It’s largely the same, but make sure that when you are looking at documentation you are looking at the Postgresql Documentation.

Work through the three following sections (Basic, Joins, Aggregates) of exercises on the site. Make sure you are also reading the Answers and Discussion section at the bottom, even if you get the answer right. There are some helpful explanations and alternative solutions.

To reiterate from before, you do not need to be a master of SQL coming out these exercises, so you should timebox these exercises. Timebox each of the three sections to no more than 2 hours for a maximum of 6 hours spent on these exercises. There will likely be exercises that you have difficulty completing, and that’s okay! Use the provided hints, give it your best effort, and skip to the answer if you can’t get it. Just make sure you read and understand the answer before moving on.

After you complete the exercises, answer the Checks for Understanding.

Basics

1. Getting Started. Skip the “I want to use my own Postgres system” at the end.
2. Select All
3. Selecting Columns
4. Where
5. Where 2
6. String Search
7. Where 3
8. Case
9. Dates
10. Unique and Order
11. Basic Aggregate

Joins

1. Intro to Joins
2. More Joins
3. Joining a Table to Itself
4. Multiple Joins
5. Multiple Joins 2

Aggregates

1. Count
2. More Counting
3. Group
4. Sum
5. More Summing
6. Having
7. Aggregates and Joins

Checks for Understanding

1. How can you limit which columns you select from a table?
2. How can you limit which rows you select from a table?
3. How can you give a selected column a different name in your output?
4. How can you sort your output from a SQL statement?
5. What is joining? When do you need to join?
6. What is an aggregate function?
7. List three aggregate functions and what they do.
8. What does the group statement do?
9. How does the group statement relate to aggregates?