Anyone working in the IT sector faces the day when he or she gets in contact with any kind of database. It represents one of the main columns in any application and everybody should have a rough idea about what the colleagues are talking about. Therefore, let´s take a look at the basics of databases in general and more specifically at one of the most popular types, “SQL”.
SQL vs. NoSQL
As the name “database” suggests, it provides a central place to store any type of data. The data is then made available for users to be created, read, updated or deleted (CRUD principle). For a beginner the databank landscape can be overwhelming because there are plenty of differnt options. In order to make some sense of the landscape, it´s helpful to have a taxonomy handy. The landscape can be divided into two main classes: SQL (relational databases) and NoSQL (everthing else) databases. The term NoSQL simply includes all types of database which don´t support SQL. To make things more complicated, NoSQL transformed into “Not only SQL” over the years, which gives a hint of how many differnt database types are out there. Of course both have their Pros and Cons and each database expert needs to know when to use the right type by analyising their application data.
For a beginner I recommand to first concentrate on SQL databases. That said, I still want to give a short overview of what a NoSQL database is and when to use it.
The idea for a NoSQL database arose from the lack of horizontal scalability and rigidity of table design of traditional SQL databases. To put this into simple words:
In SQL you need to define a fixed structure for your database which is difficult to change and requires your data to fit this structure.
To avoid this hustle a NoSQL database provides you with the ability to store data in a so-called key-value pair. This means that you place all your data in the database, add a unique id to it and store it away. Therefore a variety of datatypes can be stored in the same database or table, which are also quickly accessable through the unique id.
There is way more about NoSQL and its niche implementations but this gives you a rough idea on how it works. Let´s come back to SQL.
SQL stands for “Structured Query Language” and is a domain-specific language used in programming. It is designed for managing data held in a relational database management system (RDBMS). It´s the best choice for working with structed data and therefore optimal for vertical scaling. Let´s take a look at some demo data.
As you can see each row of data has the same structure and can be placed into this sample table. Each column holds some specific information of the dataset and therefore makes it easier to understand the full contexts of the data and its relations between columns as well as rows.
Time to get your own database up and running. I recommend using PGAdmin as a GUI and PostgreSQL as the underlining database.
Go to the following link to download PostgreSQL which already includes the pgAdmin GUI: https://www.postgresql.org/download/ . Then do the following steps on the website:
- Select your operating system.
- Click on the Button “Download the installer”.
- Download the newest version for your operating system.
Once the download is done, execute the installer and work your way through the installation process. The default options should work fine. Just make sure that the pgAdmin checkbox is checked (should be preselected as default). At one point the installer asks you for a superuser password. The password you provide will be the admin password for your SQL access with full privileges. After finishing the installation process, you can search for pgAdmin and start the programm.
pdAdmin will then start in your browser.
Double click on the PostgreSQL XX server, enter your superuser password and voila, you are connected to the database. You already find an empty database named postgres which does not have any data in it yet. To work with some demo data we will import a database in the next step and continue learning SQL.
Import Test Data
Download the demo database from the following Link: https://www.postgresqltutorial.com/postgresql-sample-database/. Remember the location where you saved the bookstore.backup file and come back to pgAdmin.
To be able to import the backup file we first need to create a new database. Right click on Databases as shown in the following picture.
A pop-up window will appear and you only need to provide the name of the database. Call it whatever you like but I recommend using bookstore to have the same setup as this article. You can save the database with the save button. Next you need to import the backup file into the created database. Right click on the bookstore database and click on Backup. Select the backup file as shown in the following picture.
Good job! Let´s checkout the new data in our database. First refresh the database by right clicking on it and select refresh. Then open the dropdown of the database and go to Schemas — public — Tables. You should see all the imported tables.
Each table has its own structure and consists of multiple columns which are also called fields. By right clicking on the table customers and selecting the View/Edit Data — All Rows option, we can take a look at the table data.
As you can see in the picture above, pgAdmin also provides an overview of the table fields. In customers we have:
- id: [PK | bigint]
- title: [character varying(127)]
- firstname, lastname, street, city, email and age
- order_sum was added by me and will not be in your table
Create your own table
Now we create our own table with the Query Editor and SQL. Select the bookstore database and click on the icon Query Tool.
This opens a session which is connected to the bookstore database and you can start using SQL to interact with the tables.
Now we are ready to create our own table with the following SQL command:
CREATE TABLE owntable(
id SERIAL PRIMARY KEY,
Execute the SQL command with F5 or by clicking on the play button. Next refresh the tables and you will see the newly created “owntable” table. As you have propably assumed owntable defines the name of the table. id, street and description are the fields/columns names of the table.
SELECT * FROM tableName
Next we want to see what is inside of the table. Therefore, use the following SQL command to view the table content:
SELECT * FROM owntable;
As expected you see a table with three columns, named id, street and description, and no data inside of the table. The id column has the datatype integer and was defined as primary key. This is a unique identifier which makes sure that each entry in the table, even with the same data, is listed as a single data element. The street column is of the datatype character varying (=VARCHAR) which represents a string. We defined that the maximum string length is 127 characters. The last column, description, has the value text. All availavle PostgreSQL datatypes can be found in the documentation.
After some thinking, we decide that the table is missing some columns. To store our “fake” data, we also need columns for a firstname and lastname. We can alter the table with the following command:
ALTER TABLE table
ADD COLUMN firstname VARCHAR(127) NOT NULL,
ADD COLUMN lastname VARCHAR(127) NOT NULL;
With this query we add the two needed columns. The “NOT NULL” part makes sure that we can only insert data into the table if we provide at least these two fields. If you execute the same command without this, you can add any data to it and the fields will be set to NULL by default.
Now it is time to insert some data into the new table. Execute the following command:
INSERT INTO owntable (firstname, lastname, street, description)
VALUES (‘Luc’, ‘Brazilian’, ‘MainRoad 67’, ‘Home Adress’),
(‘Max’, ‘Maxman’, ‘SideRoad 68’, ‘Workplace’);
With this command we insert two new data rows. When you inspect the table with “SELECT * FROM owntable”, you find the newly added datasets.
When you closely examine the insert SQL command you recognize that we didn´t have to add the id value. The primary key is automatically set and incremented by PostgreSQL and keeps track of our unique identifier all by itself.
You are now ready for more complex queries and set for a deep dive into the following topics:
- Complex Queries (Selection of partional data, changing existing data, filtering and sorting data, etc…)
- Complex table operations (Deletion, replacing and updating tables)
- User and role management for your database and tables
- Views, transactions, triggers, procedures and constraints
But first, take a break and enjoy the progress you have already made. In the meantime I work on these topics and link them to this article so you can take the next step towards becoming a SQL expert and impress your colleagues with your skills.
Thanks, see you around,