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 you don’t need to read past the “Adding Columns to the Table” section Then complete the Checks for Understanding.

Checks for Understanding

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

SQL Exercises

Now you’ll work through some of the exercises at pgexercises.com. In these exercises, you will use Postgresql. If needed, you can refer to Postgresql Documentation.

Work through the first section 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. If you enjoy these exercises, there are additional sections below, but you should prioritize completing other required intermission work before you return to the additional exercises.

To reiterate from before, you do not need to be a master of SQL coming out these exercises, so you should timebox these exercises. This first “Basics” section should be limited to 2 hours. The sections below on Joins and Aggregates are not required. 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. Unique and Order
6. Basic Aggregate

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?

Additional Practice - Not required

Joins

1. Intro to Joins
2. More Joins
3. Multiple Joins

Aggregates

1. Count
2. Group
3. Sum
4. Aggregates and Joins