You have made it here so far. You are much familiar with databases and SQL. By now, you should be able to:

  • Create new databases
  • Create roles and users
  • Know everything about SQL data types
  • Create tables, set constraints and relationships among them

You have come a long way indeed, but you still have more things to learn to use and harness the power of SQL fully.

If you want to go over the previous articles, here’s the list:

In this article, I will take you into more depth on CRUD operations. Some stuff you may already know from previous articles, but you still have a lot to learn to qualify as an SQL Ninja.

You can start by reading more about what CRUD means in programming.

Inserting data into tables

Databases are designed to store our data for future processing. So the first step is obviously to get data into our database.

Data insertion is done using the INSERT INTO command. You already know this from the introductory article. The syntax for this again is:

INSERT INTO products (name, price, quantity, max_quantity)
VALUES ('product1', 15.50, 50, 50);

Notice that you don’t need to give a value to the id column manually. As I said before, when using SERIAL data type for the primary key, Postgres will take care of this instead of INTEGER.

You also aren’t limited to inserting one single row at a time. You can provide multiple rows separated by a comma:

INSERT INTO products (name, price, quantity, max_quantity) VALUES 
('product1', 15.50, 50, 50),
('product2', 9.99, 30, 40),
('product3', 4.99, 80, 100);

Besides entering rows data manually, you can also insert the result of an SQL query.

Let’s assume your products table has a rating column to store clients' ratings, and you created a new table, top_products, to hold a list of the top-rated product in your store. You can query these products and automatically insert them into the new table:

INSERT INTO top_products (name, price, rating)
SELECT name, price, rating FROM products WHERE rating > 4.0;

There are two parts to this command:

  1. The rows with a rating greater than 4.0 are first queried from the products table using the SELECT command.
  2. The returned rows are given to the INSERT INTO command and inserted into the target table, top_products

Don’t worry if it’s a bit confusing as we are diving into SQL queries next!

Selecting data from tables

Probably the most common operation you will be performing when working with databases is reading data from them. Retrieving data from a database is called a query. Queries can be as simple as selecting whole tables, selecting data from different tables, joining them, or doing different arithmetic and filtering operations.

Basic queries

Queries are performed using the SELECT command, followed by a list of columns we want to select, then FROM <tablename> to specify which table we want to select from. Conditions can be specified using the WHERE clause, as you will see in a bit.

The simplest query is to retrieve a whole table. If you want to get a list of all products and all their information, the syntax will look like this:

SELECT * FROM products;

The asterisk here * means “select all columns”. Alternatively, you can specify a list of columns instead of selecting all of them:

SELECT name, price FROM products;

You can also perform arithmetic operations on columns values and obtain the result. Let’s suppose you want to know how much space is left before the store is full for each item (remember that we have quantity and max_quantity columns).

To do that, you can write:

SELECT name, max_quantity - quantity FROM products;

This query will return a list of products names and the remaining free space for new items in the store.

Conditional queries

Many times you will need to make queries that return rows that satisfy a certain condition. To do that, you can use the WHERE clause, followed by a condition:

SELECT max_quantity - quantity FROM products WHERE name = 'product1';

The previous query will get the available storage space for the product with the name product1.

Besides the equality operator =, there are many other comparison operators. Here is a full list of them:

Comparison Operators

You can use all these operators with any built-in data types that have a natural ordering, such as numbers, strings, dates, and so o

Notice that <> is the standard “not equal” operator in SQL, while != is just an alias, meaning that it is converted to <> anyway.

These simple comparison operators can be chained together, using logical operators to produce more complex conditions.

There are two main logical operators in SQL: AND and OR. They take two booleans and return one boolean as follows:

Logical operators

For example, if you want to query products in a certain price range, you can write:

SELECT * FROM products WHERE price > 10 AND price < 20;

This query will return all columns for all products with prices between 10$ and 20$, with 10$ and 20$ not included (you can use >= and <= instead to include them).

In addition to comparison operators, there are also comparison predicates. They behave very similarly to comparison operators but have their own—clearer—syntax.

Alternatively, you can use the comparison predicates instead of chained conditions as in the previous example.

Comparison predicates

The following two queries are identical:

SELECT * FROM products WHERE price >= 10 AND price <= 20;

SELECT * FROM products WHERE price BETWEEN 10 AND 20;

There are some slight differences between comparison operators and predicates, which you learn about in the official documentation if you are interested.

Other uses of the SELECT command

The SELECT command can do more than just selecting data. Actually, you can omit the columns and the table name entirely. The following syntax is still a valid SQL query:

SELECT 5 + 8;

And will return the sum of two numbers.

Also, you can write something more useful, similar to this:

SELECT random();

random() is a function that returns a randomly generated number.

We will explore other functions in a later article and see how you can practically use them.

Ordering query results

If you want your query results to be ordered in a specific way, you can use ORDER BY followed by the name of column(s) whose values will be used to order the rows. A useful example is ordering the returned rows from the products table by the product’s price. To achieve that, you can write:

SELECT * FROM products ORDER BY price;

This query will return all the rows ordered in ascending order by price, from the cheapest to the most expensive.

You can add the keywords ASC and DESC to order the rows in ascending or descending order. ASC is the default behavior, and you don’t have to specify it explicitly. Alternatively, if you want a descending order, you must specify this:

SELECT * FROM products ORDER BY price DESC;

You can specify more complex ordering conditions, not just columns names, the same way you did with the SELECT statement:

SELECT * FROM products ORDER BY max_quantity - quantity;

You can specify a list of more than one column/expression to order the returned rows. The first column/expression is used to sort the rows, and when two or more rows are equal on that expression, the second column/expression is used to order these rows further.

A real-world scenario is when you want to sort your products by price, and if two products have the same price, you want to sort them by user rating:

SELECT * FROM products ORDER BY price, rating DESC;

This query orders the rows from the products table by price first, in ascending order (cheapest to most expensive). If two rows share the same price, it uses a rating column to sort them in descending order (best to worst rating).

Updating existing data

In our store database, many columns need to be updated continuously, take products' available quantities. For example, these need to be updated automatically with every purchase made on our app or when new items are added to the inventory.

Updates to the data are performed using the UPDATE command. This command updates the specified columns in all the rows that satisfy a given condition.

To update the value for a specific column for a specific row, you can use the following syntax:

UPDATE clients SET email = '[email protected]' WHERE id=1;

The command updates the email column for the client with an id of 1.

The name of the table follows UPDATE to update. SET is used to set the current value for the column to a new value. WHERE is used to narrow the change to a single row. If you omit WHERE you’ll update the email in all the rows.

You can also update many columns at once:

UPDATE clients SET email = '[email protected]', phone='+1111111' WHERE id=1;

You don’t need to update a single row at a time, and of course, you don’t have to use the id column (the primary key) to select rows. You can use any condition you want.

Imagine if your products table had a category column. A bunch of your products belongs to the category beauty. For the sake of example only, let’s say you had a typo while inserting some of them, and instead of beauty, you typed beaty. You can fix that for all columns by running:

UPDATE products SET category = 'beauty' WHERE category = 'beaty';

Deleting data

The last CRUD operation is data deletion. You will always find yourself needing to delete data for many reasons. Data deletion is a straightforward operation. It is done using the DELETE command:

DELETE FROM products WHERE id = 1;

This way, you will delete a specific product using its primary key from the products table.

After successful deletion, the command will show you a count of deleted rows in operation.

By default, this command doesn’t show the deleted rows to you, only their count. You can change this behavior using RETURNING keyword, followed by a list of columns to return, or * to return everything:

DELETE FROM products WHERE category = 'beauty' RETURNING *;

RETURNING works similarly to SELECT. This will delete all products in the beauty category and show you all columns from the deleted rows.

RETURNING is not a part of standard SQL, but it is a PostgreSQL extension.

If you don’t specify a deletion condition, and for example, you write:

DELETE FROM products;

This query will delete every row in the table.

It will work if you intend to empty a table (or a set of tables), but it’s not the best approach. Alternatively, you can use the TRUNCATE command:

TRUNCATE products;

TRUNCATE is more efficient than DELETE because it doesn’t scan the table first. If you want to delete few rows, use DELETE. If you want to empty the table, use TRUNCATE.

Conclusion

Your SQL skills are getting sharper, and today you learned a lot about CRUD operations and how to use them effectively. CRUD operations are the cornerstone of every database system. As a developer, they will represent the majority of your interaction with databases.

In the next article, we will go beyond these operations to learn new useful tricks before we move on to creating a project as a practical application covering all that you learned.