DEV Community

Mohamed Idris
Mohamed Idris

Posted on

Part 01: What is a Database and SQL?

#sql #database #learning

Part of the "SQL: Zero to Ninja" series.

You build a login page. A user signs up. They close the browser, come back
tomorrow, and... they are still there. Their name, their email, their orders,
all still waiting for them. Where did all of that live overnight? It lived in a
database. And the way your app talks to that database is a language called
SQL. Let's get to know both.

The idea in one line

A database is an organized place to store your app's data, and SQL is
the language you use to ask that database questions and give it instructions.

The metaphor: a super-powered filing cabinet

Picture a filing cabinet in an office. It has labeled drawers. Each drawer holds
a stack of forms. Every form has the same blank lines to fill in: name, email,
country.

A database is that filing cabinet, but with superpowers. It never loses a form,
it can find one form out of ten million in a blink, and many people can use it
at the same time without bumping heads.

Now let's name the parts, because the names matter.

Filing cabinet   =  the database
One drawer       =  a table   (like "users")
One form         =  a row      (one single user)
A blank line     =  a column   (like "email")
Enter fullscreen mode Exit fullscreen mode

If you have ever used a spreadsheet, you already know this shape. A table is a
sheet. A row is a line. A column is a labeled field at the top.

What a table actually looks like

Here is a tiny users table. The top line is the columns (the labels). Each
line under it is a row (one real person).

users
+----+--------+-------------------+---------+
| id | name   | email             | country |
+----+--------+-------------------+---------+
| 1  | Sara   | sara@mail.com     | Egypt   |
| 2  | Omar   | omar@mail.com     | Jordan  |
| 3  | Lina   | lina@mail.com     | Tunisia |
+----+--------+-------------------+---------+
Enter fullscreen mode Exit fullscreen mode

See that id column? Every row gets its own unique number. That little number
is a big deal, and we will see why in a second.

SQL is how you talk to it

You do not open the cabinet and dig with your hands. You ask. SQL (say it
"sequel" or "S-Q-L", both are fine) is the language you ask in.

It reads almost like English. Want every user's name? You say:

SELECT name FROM users;
Enter fullscreen mode Exit fullscreen mode

Out loud that is just "select name from users." The database hears you, walks to
the users drawer, and hands back the names. That is the whole game: you ask,
it answers.

It is a lot like ordering from a menu. You do not march into the kitchen and
cook. You tell the waiter what you want, and the food comes out. SQL is your
order. The database is the kitchen.

Tables connect through ids

Here is the part that makes databases feel magical. Tables can point at each
other using those id numbers.

Our series uses these four tables the whole way through:

  • users (id, name, email, country, created_at)
  • orders (id, user_id, total, status, created_at)
  • products (id, name, price, category)
  • order_items (id, order_id, product_id, quantity)

Look at orders. It has a user_id. That is a pointer. An order with
user_id = 1 belongs to Sara, because Sara's id is 1.

users                 orders
+----+------+         +----+---------+-------+
| id | name |         | id | user_id | total |
+----+------+         +----+---------+-------+
| 1  | Sara |  <----  | 10 |    1    |  50   |
| 2  | Omar |  <----  | 11 |    1    |  20   |
+----+------+         +----+---------+-------+
Enter fullscreen mode Exit fullscreen mode

Both of those orders point back to Sara. So "a user has many orders" is just a
bunch of rows holding the same user_id. This linking idea is called
relational, and it is the heart of databases like Postgres and MySQL. We
will go deep on it later in the series. For now, just notice: ids are the glue.

The same SQL works almost everywhere

You will hear names like Postgres, MySQL, and SQLite. These are
different database programs, but they all speak SQL. The basics you learn here
(SELECT, WHERE, JOIN) work in all of them.

There are small differences (how you handle dates, tiny spelling changes), but
think of it like English in different countries. The accent changes a little,
yet you can still talk to everyone. Learn SQL once and you can work with all of
them.

Why every web developer needs this

Pretty much every app stores and reads data:

  • A user signs up and logs in, that is reading and writing the users table.
  • A blog shows posts, that is reading a posts table.
  • A shop shows your orders, that is reading the orders table.

No matter the framework, the buttons and the pretty screens sit on top of a
database. If you can talk to the data, you can build the real thing. If you
cannot, you are stuck gluing tutorials together.

Gotchas to know early

  1. A database is not a spreadsheet. It looks similar, but it is built for millions of rows and many users at once. Do not treat it like a toy.
  2. The id is sacred. It is the unique tag for each row. You almost never change it, because other tables point to it.
  3. SQL is not tied to one language. Python, JavaScript, PHP, they all send the same SQL to the database. You are learning a skill that travels.

Recap

  • A database is an organized, super-powered store for your app's data.
  • A table is a drawer, a row is one record, a column is one field.
  • SQL is the language you use to ask the database for things.
  • Tables link to each other through ids (this is the relational idea).
  • Almost every app you build sits on a database, so this skill pays off forever.

Next up: Part 02: SELECT, Your First Questions, where you start actually
pulling data out of these tables.

Your turn

Look at the users table above. In plain English, what would you ask to get
back only the emails? Now peek at this and see if you guessed it:

SELECT email FROM users;
Enter fullscreen mode Exit fullscreen mode

If you can explain to a friend what a row, a column, and a table are using the
filing cabinet picture, you have already started your climb to ninja.

Top comments (1)

Collapse
 
edriso profile image
Mohamed Idris •

Part 01 Practice: What is a Database and SQL?

Time to make it stick. These start as plain thinking questions, then end with
your very first real query. Try each one before you peek at the solution.

We are using the series tables:

  • users (id, name, email, country, created_at)
  • orders (id, user_id, total, status, created_at)
  • products (id, name, price, category)
  • order_items (id, order_id, product_id, quantity)

1. Name the parts

In the filing cabinet picture, match each database word to its everyday twin:
table, row, column.

Solution

table  = a drawer (or a spreadsheet sheet)
row    = one form / one record (one single user)
column = one labeled field (like "email")
Enter fullscreen mode Exit fullscreen mode

This is the vocabulary the whole series leans on, so lock it in now.

2. Spot the pieces in a real table

Look at this products table. How many columns does it have, and how many rows?

products
+----+----------+-------+----------+
| id | name     | price | category |
+----+----------+-------+----------+
| 1  | Keyboard | 30    | tech     |
| 2  | Mug      | 8     | home     |
+----+----------+-------+----------+
Enter fullscreen mode Exit fullscreen mode

Solution

columns: 4   (id, name, price, category)
rows:    2   (Keyboard, Mug)
Enter fullscreen mode Exit fullscreen mode

Columns are the labels across the top. Rows are the actual records under them.

3. Follow the pointer

An orders row has user_id = 2. In the users table, Omar has id = 2. Who
placed this order, and which column made the link?

Solution

Omar placed the order.
The link is orders.user_id pointing to users.id.
Enter fullscreen mode Exit fullscreen mode

Tables connect through ids. The user_id in orders points back to the id
in users. That is the relational idea in action.

4. Your first SELECT

Write one SQL line that asks the database for the name of every user.

Solution

SELECT name FROM users;
Enter fullscreen mode Exit fullscreen mode

SELECT says what you want (the name column). FROM users says which table
to grab it from. Read it as plain English and it just makes sense.

5. Ask for two columns

Now ask for both the name and the email of every user. Hint: you can
list more than one column, separated by a comma.

Solution

SELECT name, email FROM users;
Enter fullscreen mode Exit fullscreen mode

You can pull as many columns as you like, just put a comma between them. The
order you list them is the order they come back in.

6. Which database speaks SQL?

True or false: the query SELECT name FROM users; would work in Postgres,
MySQL, and SQLite.

Solution

True.
Enter fullscreen mode Exit fullscreen mode

The basics of SQL are shared across all of them. The small differences show up
later with things like dates, not with a plain SELECT.

You just wrote your first queries and read your first tables. That is the
foundation everything else is built on. See you in Part 02, where SELECT gets
a real workout.