SQL Lesson 1: SELECT Queries 101
Learn the fundamentals of SQL SELECT statements with interactive exercises using the Pixar movies database.
SQL Lesson 1: SELECT Queries 101
Welcome to Interactive SQL Learning!
This is a series of interactive lessons and exercises designed to help you quickly learn SQL right in your browser with hands-on practice and automatic grading.
What is SQL?
SQL, or Structured Query Language, is a language designed to allow both technical and non-technical users to query, manipulate, and transform data from a relational database. And due to its simplicity, SQL databases provide safe and scalable storage for millions of websites and mobile applications.
Did you know?
There are many popular SQL databases including SQLite, MySQL, Postgres, Oracle and Microsoft SQL Server. All of them support the common SQL language standard, which is what this lesson will be teaching, but each implementation can differ in the additional features and storage types it supports.
Relational Databases
Before learning the SQL syntax, it’s important to have a model for what a relational database actually is. A relational database represents a collection of related (two-dimensional) tables. Each of the tables are similar to an Excel spreadsheet, with a fixed number of named columns (the attributes or properties of the table) and any number of rows of data.
For example, if the Department of Motor Vehicles had a database, you might find a table containing all the known vehicles that people in the state are driving. This table might need to store the model name, type, number of wheels, and number of doors of each vehicle for example.
Table: Vehicles
| Id | Make/Model | # Wheels | # Doors | Type |
|---|---|---|---|---|
| 1 | Ford Focus | 4 | 4 | Sedan |
| 2 | Tesla Roadster | 4 | 2 | Sports |
| 3 | Kawasaki Ninja | 2 | 0 | Motorcycle |
| 4 | McLaren Formula 1 | 4 | 0 | Race |
| 5 | Tesla S | 4 | 4 | Sedan |
In such a database, you might find additional related tables containing information such as a list of all registered drivers in the state, the types of driving licenses that can be granted, or even driving violations for each driver.
By learning SQL, the goal is to learn how to answer specific questions about this data, like“What types of vehicles on the road have less than four wheels?”, or “How many models of cars does Tesla produce?”, to help us make better decisions down the road.
SELECT Statements
To retrieve data from a SQL database, we need to write SELECT statements, which are often colloquially referred to as queries. A query in itself is just a statement which declares what data we are looking for, where to find it in the database, and optionally, how to transform it before it is returned. It has a specific syntax though, which is what we are going to learn in the following exercises.
As we mentioned above, you can think of a table in SQL as a type of an entity (ie. Dogs), and each row in that table as a specific instance of that type (ie. A pug, a beagle, a different colored pug, etc). This means that the columns would then represent the common properties shared by all instances of that entity (ie. Color of fur, length of tail, etc).
And given a table of data, the most basic query we could write would be one that selects for a couple columns (properties) of the table with all the rows (instances).
Select query for specific columns
SELECT column, another_column, …
FROM mytable;The result of this query will be a two-dimensional set of rows and columns, effectively a copy of the table, but only with the columns that we requested.
Select query for all columns
SELECT *
FROM mytable;If we want to retrieve absolutely all the columns of data from a table, we can use the asterisk (*) shorthand in place of listing all the column names individually. This query, in particular, is really useful because it’s a simple way to inspect a table by dumping all the data at once.
About the Lessons
Since most users will be learning SQL to interact with an existing database, the lessons begin by introducing you to the various parts of an SQL query. The later lessons will then show you how to alter a table (or schema) and create new tables from scratch.
Each lesson will introduce a different concept and end with an interactive exercise. Go at your pace and don’t be afraid to spend time experimenting with the exercises before continuing! If you happen to be familiar with SQL already, you can skip ahead using the lesson navigation, but we would recommend you work through the lessons anyways!
By the end, we hope you will be able to have a strong foundation for using SQL in your own projects and beyond.
Exercise
We will be using a database with data about some of Pixar’s classic movies for most of our exercises. This first exercise will only involve the Movies table, and the default query below currently shows all the properties of each movie. To continue onto the next lesson, alter the query to find the exact information we need for each task.
Table: movies
Exercise 1 — Tasks
- Find the
titleof each film - Find the
directorof each film - Find the
titleanddirectorof each film - Find the
titleandyearof each film - Find
allthe information about each film
Solve all tasks to continue to the next lesson.