2019-10-18

Introduction to SQL - Designing, Creating, Inserting and Displaying Data

From a high-level point of view, we know how databases work, so it's time to start experimenting with the Database Management System (DBMS) chosen, in our case: PostgreSQL. However, people often 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 what kind of tools we can use to do that.

Database schema design

We'll need an entire blog post about this topic, but for now, we need to learn the basics about how to design a straightforward database. I used to use a tool called https://app.diagrams.net/. You can connect this tool with your Google Drive or save it on your own device. The important thing here is to design the basics about what 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 chosen.

An entity is a synonym of "Table" in the database world. An entity is an object that exists. It doesn't have to do anything; it just has to live. An entity can be a single thing, person, place, or object. Data can be stored about such entities. Like the one we're using, a design tool that 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 are considered entities. So 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 exact attributes. Attributes, in this case, is a synonym of Columns:

* Id as an Integer
* Name as a String
* Age as an Integer

Once we have mapped out our first entity, let's see it the following way.

Tables/Entities: Rows and Columns

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

We can imagine how the data will be stored in a DBMS like PostgreSQL.

Let's analyze this:

* A Table/Entity is a collection of data organized in Rows and Columns/Attributes.
* A Column/Attribute is a dataset of a particular type: id, name, age
* A Row is a single record in the Table/Entity, for example: id = 1, name = Justin, age = 22
* All the data stored in a relational database (called marvel-tshirt in this case) has a specific data type: INTEGER, TEXT, DATE, BOOLEAN

CRUD Statements

Now it's time for coding. But, first, we must access our PostgreSQL installation through our command line and type the following 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 that either 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 compile successfully.

There are many types of statements. Statements are text that the database recognizes as a valid command. Statements always end with a 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 multiple lines, making 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

* Change pointer from all databases to marvel_tshirt with command \c [database_name]
* Type statement to create a database
* Select all data from Users table/entity (Right now, we don't have anything saved there, but we can list it)

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 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. So another way exists if we want to insert more than one record (or row) simultaneously.

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 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.

What happens if we want to update a single row with a new 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 update is to know the primary key (id) of the record we want to edit. In this case, is id: 4

The last operation we want to know about in this post is deleted. But, first, 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 commonly used behaviors in a database, like Create, Read, Update and Delete. Let's recap

* Create: INSERT INTO
* Read: SELECT * FROM
* Update: UPDATE SET
* Delete: DELETE from

Next post, we'll be seeing more details about other statements. But, for now, we've been learning a lot.

Thanks for reading!

Daniel Morales