Now that we know, from a high level point of view, about how databases works it’s time to start experimenting with the Database Management System (DBMS) choosed, in our case: PostgreSQL. However, so many times people tend to go over it without giving enough context, so this time what we need to understand first is how to design a database, or at least what kind of tools we can use to do that. 

We'll need an entire post about this topic, for now we need to learn the basics about how to design a very simple database. I used to use a tool called https://app.diagrams.net/ You can connect this tool with your Google drive or save it in your own device. The important thing here is to design the basics about the things we want to do in SQL

Open the tool and then select the following Figure

Then let’s create our first entity changing the title and items from the figure choosed. 

In the database world, an entity is a synonym of “Table”. An entity is an object that exists. It doesn't have to do anything; it just has to exist. An entity can be a single thing, person, place, or object. Data can be stored about such entities. A design tool, like the one we’re using, allows database administrators to view the relationships between several entities is called the entity relationship diagram (ERD). Right now we have just one entity, but later we’ll be adding more. 

In database administration, only those things about which data will be captured or stored is considered an entity. If you aren't going to capture data about something, there's no point in creating an entity in a database.

As you can see our entity or table is called User and each user will have the same attributes. Attributes in this case is a synonym of Columns:

Once we have mapped out our first entity let’s see it on the following way

An entity or table can have hundreds or millions of rows. These rows are also called records. A table can also have many columns/attributes and have a descriptive name

This is the way we can imagine how the data will be stored in a DBMS like PostgreSQL

Let’s analyze this:

Now it's time for coding. We have to access our PostgreSQL installation through our command line and type next things.

➜  ~ sudo su - postgres

[sudo] password for daniel: 

postgres@daniel-MS-7A15:~$ psql

psql (13.3 (Ubuntu 13.3-1.pgdg16.04+1), server 9.6.22)

Type "help" for help.

postgres=# 

We’re ready to start writing statements. A SQL statement is an atomic unit of work and either completely succeeds or completely fails. A SQL statement is a set of instructions that consists of identifiers, parameters, variables, names, data types, and SQL reserved words that compiles successfully.

There are many types of statements. Statements are text that the database recognizes as a valid command. Statements always end with semicolon “;”3

The structure of the statements may vary. The number of lines does not matter. A statement can be written on one line or on multiple lines if that makes it easier to read.

Let’s start with our first command. Let’s list the current databases on our system

postgres=# \list

With the command backslash list we can list all databases. 

We should have anyone yet

To go out from this new view, just type “q” which stands for “quit”

Let's create our first database

postgres=# CREATE DATABASE marvel_tshirts;

CREATE DATABASE

postgres=# \list marvel_tshirts

                                            List of databases

             Name              |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   

-------------------------------+----------+----------+-------------+-------------+-----------------------

 marvel_tshirts                | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 

Now let’s do 3 operations to create the Table/Entity called Users under the newly created database: marvel_tshirts

postgres=# \c marvel_tshirts;

psql (13.3 (Ubuntu 13.3-1.pgdg16.04+1), server 9.6.22)

You are now connected to database "marvel_tshirts" as user "postgres".

marvel_tshirts=# CREATE TABLE users (id INTEGER, name TEXT, age INTEGER);

CREATE TABLE

marvel_tshirts=# SELECT * FROM users;

 id | name | age 

----+------+-----

(0 rows)

Now let’s insert our first user and the list all the users:

marvel_tshirts=# INSERT INTO users (id, name, age)

marvel_tshirts-# VALUES (1, 'Justin', 22);

INSERT 0 1

marvel_tshirts=# SELECT * FROM users;

 id |  name  | age 

----+--------+-----

  1 | Justin |  22

(1 row)

As you can see, now we have our first user in the database!

But with this syntax we need to insert one by one. It exist other way if we want to insert more than one record (or row) at the same time

marvel_tshirts=# INSERT INTO users (id, name, age)

marvel_tshirts-# VALUES

marvel_tshirts-# (2, 'Ana', 21),

marvel_tshirts-# (3, 'Daniel', 29),

marvel_tshirts-# (4, 'Viktor', 38);

INSERT 0 3

marvel_tshirts=# SELECT * FROM users;

 id |  name  | age 

----+--------+-----

  1 | Justin |  22

  2 | Ana    |  21

  3 | Daniel |  29

  4 | Viktor |  38

(4 rows)

Note: you have to take care about colons, semicolons and single quotes. If you skip any of them you’ll see an error. Also as you can see we’re doing everything on multiple lines which makes it easier to read. 

Now what happens if we want to update a single row with a nre value. For instance, let’s say that yesterday was Viktor's birthday, so he is now 39. How can we update that data?

marvel_tshirts=# UPDATE users

marvel_tshirts-# SET age = 39

marvel_tshirts-# WHERE id = 4;

UPDATE 1

marvel_tshirts=# SELECT * FROM users;

 id |  name  | age 

----+--------+-----

  1 | Justin |  22

  2 | Ana    |  21

  3 | Daniel |  29

  4 | Viktor |  39

(4 rows)

The key here to do the update is to know the primary key (id) of the record we want to update. In this case is id: 4

The last operation we want to know about in this post is delete. Let’s delete the record associated with Daniel. 

marvel_tshirts=# DELETE from users

marvel_tshirts-# WHERE name = 'Daniel';

DELETE 1

marvel_tshirts=# SELECT * FROM users;

 id |  name  | age 

----+--------+-----

  1 | Justin |  22

  2 | Ana    |  21

  4 | Viktor |  39

(3 rows)

In this case we have to consider the name we want to delete, because if we have more “Daniel”s in the database under the column “name” all of them will be deleted.

So far we’ve executed the common used behaviors in a database, like Create, Read, Update and Delete. Let’s recap

Next post we’ll be seeing more details about other statements. For now we’ve been learning a lot

Thanks for reading!

Daniel Morales