Databases · Lesson 1.1
Asking for certain columns
Simplified, the app you're learning on, keeps every learner in a Postgres table called users.
Its developers made users with CREATE TABLE, giving it five columns: ID, email, name, XP and created at.
And each learner who signs up gets one row, like Ada's.
So 2,000,000 learners make 2,000,000 rows.
Say Simplified adds a new lesson, and emails every learner about it.
Each email opens with the learner's name.
So Simplified's server needs two columns from every row: name and email.
So how does the server ask Postgres for just those two columns?
It sends a query, written in SQL, that starts with SELECT.
SELECT names the columns you want back.
Then FROM names the table to read them from.
So the query says SELECT name, email FROM users.
And a semicolon ends it.
Postgres reads users one row at a time.
From Ada's row, it takes just her name and her email.
Then Ben's, then Cy's, all the way to the last learner.
It leaves out ID, XP and created at.
Because the query never named them.
So what it sends back has just two columns, name and email, for every learner.
And what if Simplified's analytics page needs just each learner's XP?
Then the query names one column: SELECT XP FROM users.
So from each row, Postgres takes just the XP.
And it sends back one column, XP, with a row for every learner.
And what if Simplified's team wants each learner's sign-up date?
Then the query names two columns: SELECT name, created at FROM users.
So from each row, Postgres takes the name and the created at date.
And it sends back each learner's name, and the date their row was made.
So SELECT picks which columns come back, and every row comes back with them.
Simplified's users table has five columns, id, email, name, xp and created_at.
Its server sends Postgres SELECT name, xp FROM users;.
How many columns come back?
Explanation
First, SELECT names the columns to send back: name and xp.
Then, Postgres leaves out the three columns the query didn't name.
Therefore, 2 columns come back.
Which SQL keyword names the table a query reads from, as in SELECT name FROM users;?
Databases · Lesson 1.2
Asking for every column
So SELECT name, email FROM users sent back just name and email.
Now say Simplified's team wants every column of every learner.
users has five columns, so the team would have to name all five.
So how do you ask for every column, without typing all five names?
Where the column names go, you write a star.
The star means every column.
So SELECT star FROM users sends back all five: ID, email, name, XP and created at.
And it takes them from every row: a full copy of users.
So is the star any different from naming all five columns?
No: both queries ask for the same five columns.
So both send back the same result.
Now say Simplified keeps its lessons in a table called lessons.
So what does SELECT star FROM lessons send back?
Say lessons has three columns: ID, title and position.
So the star sends back all three, with a row for every lesson.
And what might Spotify's server get from SELECT star FROM songs?
Say songs has four columns: ID, title, artist and plays.
So the star sends back all four, with a row for every song.
So the star sends back every column of the table, whatever the table has.
Simplified's users table has five columns, id, email, name, xp and created_at, and six rows.
Its server sends Postgres SELECT * FROM users;.
How many columns come back?
Explanation
First, * means every column of users.
Then, users has five: id, email, name, xp and created_at.
Therefore, 5 columns come back.
Say YouTube keeps one channel's videos in a Postgres table called videos, with columns id, title and views, and 100 rows. YouTube's server sends SELECT * FROM videos;.
What does the result hold?
Say Netflix keeps its shows in a Postgres table called shows, with columns id, title, genre and year, and 50 rows. Netflix's server sends SELECT * FROM shows;.
How many cells does the result hold? (A cell is where one row meets one column.)
Simplified's users table has five columns: id, email, name, xp and created_at.
Which query sends back the same result as SELECT * FROM users;?
Say Instagram keeps posts in a Postgres table called posts, with columns id, caption and likes. Instagram's server sends SELECT * FROM posts;.
Which columns come back?
Simplified's users table has five columns: id, email, name, xp and created_at.
How many more columns does SELECT * FROM users; send back than SELECT name FROM users;?
Which symbol, written after SELECT, means every column?