In this exercise, we'll learn the fundamentals of querying data with SQL using SELECT, FROM, and WHERE.
You will learn to:
- Retrieve all rows and columns from a table
- Select specific columns
- Filter rows using
WHERE
📖 In SQL, basic queries follow this form:
SELECT [columns]
FROM [table]
WHERE [condition];
The SELECT part is a comma separated list of columns from the table you want to retrieve. To get all columns, you can use * instead of the column names. The FROM part specifies which table you want to get data from. Lastly, WHERE is used to filter which rows you want to retrieve. To get all rows, simply omit the WHERE part.
✏️ Write a query that returns all rows and columns in the movies table. Browse the results and familiarize yourself with the contents of the table.
Hint
To get all columns, use * in the SELECT part. You don't need a WHERE clause if you want all rows.
Solution
SELECT *
FROM movies;
📖 Tables typically contain a lot of columns. If you only want to display a few of them in your app, it is wasteful to select all the columns. It also adds to the time it takes to transfer the data from the database server to your app.
✏️ Write a query that retrieves the name, date and kind of movies. Browse through the results and look at the different kinds.
Hint
Instead of *, list the column names you want separated by commas: SELECT column1, column2, column3 FROM ...
Solution
SELECT name, date, kind
FROM movies;
📖 It turns out the movies table contains not only movies, but also the names of series and their seasons, episodes of those series and even series of movies like "Home alone" 1, 2 and 3.
✏️ Write a query that retrieves all series.
💡 Tutorial: The WHERE statement
Hint
Use WHERE kind = '...' to filter by the kind column. Look at the results from exercise 2.2 to find the correct value for series.
Solution
SELECT *
FROM movies
WHERE kind = 'series';