Basic SQL: SELECT, INSERT, UPDATE, DELETE

Basic SQL

Welcome back.

In the previous lesson, you learned about databases and tables.

You learned that a database contains tables.

Tables contain rows and columns.

Rows are records.

Columns describe the data.

Very organized.

Very PostgreSQL.

Today we learn the four basic actions you will use all the time:

SELECT;
INSERT;
UPDATE;
DELETE.

These are the core commands for working with data.

They let you:

read data;
add data;
change data;
remove data.

This is often called CRUD.

CRUD means:

Create
Read
Update
Delete

Not the most beautiful word.

Sounds like something under an old keyboard.

But it is very important.

Almost every application does CRUD.

A blog.

A shop.

A task manager.

A booking system.

A dashboard.

Even a fancy modern app with animations and emotional gradients is probably doing CRUD somewhere in the background.

Databases are humble.

But powerful.

What You Will Learn

In this lesson, you will learn:

how to read rows with SELECT;
how to select specific columns;
how to add rows with INSERT;
how to insert multiple rows;
how to update rows with UPDATE;
why WHERE is extremely important;
how to delete rows with DELETE;
how to avoid dangerous beginner mistakes;
how to practice CRUD on a real table.

By the end of this lesson, you will be able to work with data in a PostgreSQL table.

Not just create tables.

Actually use them.

This is where the database starts feeling alive.

A little scary.

But alive.

Prepare the Database

Open PostgreSQL:

sudo -iu postgres psql

Connect to the database from the previous lessons:

\c learning_postgresql

If you do not have this database, create it:

CREATE DATABASE learning_postgresql;

Then connect:

\c learning_postgresql

Now we need a table for practice.

We will use a table named products.

If it already exists, you can remove it first:

DROP TABLE IF EXISTS products;

Then create it:

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  category VARCHAR(100),
  price INTEGER,
  available BOOLEAN
);

This table stores products.

Each product has:

id;
name;
category;
price;
available.

Simple.

Clear.

Ready for action.

The database table is standing there like:

Please give me data.

Very polite.

For now.

INSERT: Add Data

INSERT adds new rows to a table.

Example:

INSERT INTO products (name, category, price, available)
VALUES ('Laptop', 'Electronics', 900, true);

PostgreSQL should say:

INSERT 0 1

That means one row was inserted.

Now add more products:

INSERT INTO products (name, category, price, available)
VALUES ('Mouse', 'Electronics', 25, true);

INSERT INTO products (name, category, price, available)
VALUES ('Keyboard', 'Electronics', 70, false);

INSERT INTO products (name, category, price, available)
VALUES ('Desk Chair', 'Furniture', 150, true);

Now we have data.

Real rows.

The table is no longer empty.

A table without data is like a shop with shelves but no products.

Technically ready.

Emotionally sad.

INSERT Syntax

The basic structure is:

INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);

For our table:

INSERT INTO products (name, category, price, available)
VALUES ('Monitor', 'Electronics', 250, true);

Important:

Text values use single quotes:

'Monitor'
'Electronics'

Numbers do not use quotes:

Booleans use:

true
false

PostgreSQL is strict.

If you write text without quotes, it will not guess.

PostgreSQL does not do emotional interpretation.

It does structure.

That is why we like it.

Mostly.

Insert Multiple Rows

You can insert multiple rows at once.

Example:

INSERT INTO products (name, category, price, available)
VALUES
  ('Notebook', 'Office', 5, true),
  ('Pen', 'Office', 2, true),
  ('Desk Lamp', 'Furniture', 45, true);

This is useful when you want to add several rows together.

Now read the table:

SELECT * FROM products;

You should see all products.

If you do not, check:

did you connect to the correct database?
did you create the table?
did you forget a semicolon?
did PostgreSQL show an error?

PostgreSQL usually tells the truth.

Sometimes brutally.

SELECT: Read Data

SELECT reads data from a table.

The simplest query:

SELECT * FROM products;

This means:

Give me all columns from all products.

The * means all columns.

This is useful for learning.

But in real applications, it is often better to select only the columns you need.

Example:

SELECT name, price FROM products;

This shows only:

name;
price.

Much cleaner.

PostgreSQL likes precise requests.

So do people.

Usually.

SELECT Specific Columns

Try:

SELECT name FROM products;

This returns only product names.

Try:

SELECT name, category FROM products;

This returns product names and categories.

Try:

SELECT name, price, available FROM products;

This returns names, prices, and availability.

You do not always need everything.

If you only need product names, ask for product names.

Do not ask for the whole database cabinet.

The database is not your emotional support suitcase.

SELECT with ORDER BY

You can sort results with ORDER BY.

Example:

SELECT name, price FROM products
ORDER BY price;

This sorts products by price from lowest to highest.

To sort from highest to lowest:

SELECT name, price FROM products
ORDER BY price DESC;

DESC means descending.

There is also ASC for ascending:

SELECT name, price FROM products
ORDER BY price ASC;

Ascending is the default.

So this:

ORDER BY price

is the same as:

ORDER BY price ASC

Sorting is useful.

Especially when your data stops being tiny and cute.

SELECT with WHERE

WHERE filters rows.

Example:

SELECT * FROM products
WHERE category = 'Electronics';

This means:

Give me products where category is Electronics.

Another example:

SELECT * FROM products
WHERE available = true;

This shows only available products.

Another:

SELECT * FROM products
WHERE price > 100;

This shows products with price greater than 100.

Important for MDX and documentation:

When writing operators like >, <, >=, <= in normal text, put them in backticks.

In SQL code blocks, they are fine.

MDX is sensitive.

Like PostgreSQL, but with more frontend drama.

WHERE Is Important

WHERE is extremely important.

It tells PostgreSQL which rows you want.

Without WHERE, commands often affect all rows.

This is fine for:

SELECT * FROM products;

But dangerous for:

UPDATE products
SET price = 1;

This updates every product.

All prices become 1.

Congratulations.

You destroyed your shop.

Very generous.

Very bad.

Always think before running UPDATE or DELETE.

Especially without WHERE.

UPDATE: Change Data

UPDATE changes existing rows.

Example:

UPDATE products
SET price = 950
WHERE name = 'Laptop';

This changes the price of the Laptop.

Now check:

SELECT * FROM products
WHERE name = 'Laptop';

You should see the updated price.

Basic structure:

UPDATE table_name
SET column_name = new_value
WHERE condition;

Example:

UPDATE products
SET available = true
WHERE name = 'Keyboard';

This makes the Keyboard available.

Good.

The keyboard has returned to society.

Update Multiple Columns

You can update multiple columns at once.

Example:

UPDATE products
SET price = 80,
    available = true
WHERE name = 'Keyboard';

This updates:

price;
available.

Always use WHERE unless you really want to update every row.

And if you really want to update every row, stop and think again.

Then maybe do it.

But carefully.

With coffee.

And backups.

The Dangerous UPDATE Mistake

Dangerous:

UPDATE products
SET price = 10;

This updates all products.

Every product now costs 10.

Laptop?

10.

Mouse?

10.

Desk Chair?

10.

Congratulations, you invented bankruptcy.

Correct:

UPDATE products
SET price = 10
WHERE name = 'Pen';

The WHERE clause saves you.

WHERE is not decoration.

It is a seatbelt.

Use it.

DELETE: Remove Data

DELETE removes rows from a table.

Example:

DELETE FROM products
WHERE name = 'Pen';

This deletes the product named Pen.

Now check:

SELECT * FROM products;

The Pen should be gone.

No goodbye.

No ceremony.

Just SQL.

Basic structure:

DELETE FROM table_name
WHERE condition;

Again:

Use WHERE.

Please.

The Dangerous DELETE Mistake

Dangerous:

DELETE FROM products;

This deletes all rows from the table.

The table still exists.

But all data is gone.

Empty.

Silent.

Like a fridge after teenagers visit.

Correct:

DELETE FROM products
WHERE name = 'Notebook';

This deletes only Notebook.

Before deleting, it is smart to test the condition with SELECT.

Example:

SELECT * FROM products
WHERE name = 'Notebook';

If the result is correct, then run:

DELETE FROM products
WHERE name = 'Notebook';

This habit can save you from pain.

Serious pain.

Database pain.

SELECT Before UPDATE or DELETE

Good habit:

Before this:

UPDATE products
SET price = 100
WHERE category = 'Office';

Run this:

SELECT * FROM products
WHERE category = 'Office';

Before this:

DELETE FROM products
WHERE available = false;

Run this:

SELECT * FROM products
WHERE available = false;

First check the rows.

Then update or delete.

This is not paranoia.

This is professionalism.

Database professionals are just people who learned fear in a structured way.

RETURNING

PostgreSQL has a useful feature called RETURNING.

It can show rows affected by INSERT, UPDATE, or DELETE.

Example:

INSERT INTO products (name, category, price, available)
VALUES ('Webcam', 'Electronics', 60, true)
RETURNING *;

This inserts the row and immediately shows it.

Update example:

UPDATE products
SET price = 65
WHERE name = 'Webcam'
RETURNING *;

Delete example:

DELETE FROM products
WHERE name = 'Webcam'
RETURNING *;

RETURNING is very useful.

It lets you see what happened.

PostgreSQL says:

Here is what I changed.

Very polite.

Slightly dramatic.

NULL in INSERT

If a column allows NULL, you can omit it during insert.

Example:

INSERT INTO products (name, price, available)
VALUES ('Mystery Box', 99, false);

Here we did not provide category.

If category allows NULL, PostgreSQL accepts it.

Check:

SELECT * FROM products
WHERE name = 'Mystery Box';

You should see an empty category value.

That is NULL.

NULL means missing value.

Not zero.

Not empty string.

Missing.

SQL takes missing seriously.

Like a detective.

Check Your Table

At this point, run:

SELECT * FROM products;

You should have several rows.

Try:

SELECT name, price FROM products
ORDER BY price DESC;

Try:

SELECT * FROM products
WHERE available = true;

Try:

SELECT * FROM products
WHERE category = 'Electronics';

This is practice.

And practice is where SQL becomes normal.

At first, SQL feels strange.

Then one day you write:

SELECT * FROM products WHERE available = true;

and it feels natural.

That is when the database has accepted you.

Maybe.

Common Mistakes

Forgetting the Semicolon

Wrong:

SELECT * FROM products

Correct:

SELECT * FROM products;

If psql keeps waiting, you probably forgot ;.

PostgreSQL is not frozen.

It is waiting for you to finish the sentence.

Very formal.

Forgetting Quotes Around Text

Wrong:

WHERE name = Laptop;

Correct:

WHERE name = 'Laptop';

Text needs single quotes.

Column names do not.

This distinction matters.

PostgreSQL will not guess.

It is a database, not your grandmother.

Updating Without WHERE

Dangerous:

UPDATE products
SET available = false;

This makes all products unavailable.

Correct:

UPDATE products
SET available = false
WHERE name = 'Keyboard';

WHERE saves data.

Respect WHERE.

Deleting Without WHERE

Dangerous:

DELETE FROM products;

This deletes all rows.

Correct:

DELETE FROM products
WHERE name = 'Keyboard';

Before deleting, run a SELECT with the same condition.

This is a strong habit.

Keep it.

Practice

Create a table named tasks.

CREATE TABLE tasks (
  id SERIAL PRIMARY KEY,
  title VARCHAR(150) NOT NULL,
  status VARCHAR(50),
  priority INTEGER
);

Insert tasks:

INSERT INTO tasks (title, status, priority)
VALUES ('Learn SELECT', 'open', 1);

INSERT INTO tasks (title, status, priority)
VALUES ('Practice INSERT', 'open', 2);

INSERT INTO tasks (title, status, priority)
VALUES ('Be careful with DELETE', 'open', 3);

Read all tasks:

SELECT * FROM tasks;

Update one task:

UPDATE tasks
SET status = 'done'
WHERE title = 'Learn SELECT';

Delete one task:

DELETE FROM tasks
WHERE title = 'Practice INSERT';

Read again:

SELECT * FROM tasks;

This gives you full CRUD practice.

Small table.

Big lesson.

Mini Challenge

Create a table named customers.

It should have:

id;
name;
email;
city;
active.

Insert at least five customers.

Then write queries to:

show all customers;
show only names and emails;
show only active customers;
update one customer city;
deactivate one customer;
delete one customer.

Before every UPDATE or DELETE, first run a SELECT with the same WHERE.

This is the habit.

Not optional.

Well, technically optional.

But so is wearing a helmet.

You understand.

Summary

Today you learned:

INSERT adds rows;
SELECT reads rows;
UPDATE changes rows;
DELETE removes rows;
WHERE filters rows;
ORDER BY sorts results;
RETURNING shows affected rows;
text values need single quotes;
booleans use true or false;
UPDATE without WHERE changes all rows;
DELETE without WHERE deletes all rows;
checking with SELECT before UPDATE or DELETE is a professional habit.

This is one of the most important lessons in the course.

With these four commands, you can do the basic work of almost any database-backed application.

Create data.

Read data.

Change data.

Remove data.

Simple words.

Big responsibility.

PostgreSQL gives you power.

Use it carefully.

Especially near DELETE.

Next Lesson

In the next lesson, we will learn data types and constraints.

We will look at:

INTEGER;
VARCHAR;
TEXT;
BOOLEAN;
DATE;
NOT NULL;
UNIQUE;
basic rules for safer tables.

Because databases are not just storage.

They are also guardians of structure.

Strict guardians.

With semicolons.