Hey guys, in this tutorial I’ll explain to you how to set a local SQL server and restore a database. Afterwards, you can easily practice SQL and learn queries. If you’re new to SQL or If you would like to polish your skills, you are at the right place :). Shortly I’ll explain what is SQL for the newbies and afterwards, we will install PGAdmin 4 and PostgreSQL to set our local environment.
Most of the modern applications use databases to keep their data. Therefore it is important for software testers to have a basic understanding of SQL and query language. Imagine you are testing a mobile application where users can enter their “Name – Surname – Birthday – Nationality” In this case, our application will save this data to its database. By using SQL queries you can reach user information in the database, you can insert new users for testing, you can update or even directly delete the users.
To be able to use SQL on our computers, firstly, we need SQL Server running on the same or remote device. Firstly let’s start the PGAdmin 4 installation.
PgAdmin is a Graphical interface we will use to make queries. To download it please direct to https://www.pgadmin.org/download/ and choose the link depends on your OS.
After you download, click on the executable file and complete the installation. You don’t need to change any settings. Just follow the instruction. After you finish it will automatically open in your browser and It will ask for the master password.
You should set up your new password (or enter if you already have one). You have to remember this password to be able to access your servers later on. If you forget it you can reset but you’ll lose your server accesses. After you set your password, PGAdmin 4 is ready to use. We only need a server to restore our database and start querying.
Now it’s time to install PostgreSQL. Please direct to https://www.postgresql.org/download/ and choose your laptop OS version, it can be Windows, Mac or Linux.
On the next screen, click on “Download the installer” link;
And download the latest version which is suitable for your device
On the installation screen, you only need to uncheck the pgAdmin 4. We installed pgAdmin before PostgreSQL since it’s much more reliable and hassle-free. Sometimes it doesn’t install it properly if you try to bulk install. After you uncheck, just click the next button and finish the installation. You don’t need to change any other settings.
That’s it. Please reopen pgAdmin 4 and type your master password which you defined previously. Afterwards, you’ll be able to see PostgreSQL listed under your servers:
If you install the previous version of PostgreSQL, it will also appear in the list. To practice, you can either create your database or restore an existing one. To restore a database, firstly download the dvdrental.tar file and save it to your local disk. Afterwards right click database > create > database and define your database name.
Once you have an empty database, right-click on it > restore and choose your dvdrental.tar file. It will restore it automatically.
Now we have a local server, database and UI where we can write queries. To write a query right-click on your database and select “ Query Tool…”
Query tool is the place where we write our scripts to access the database. Let’s write our first query.
select * from actor
This query will select everything from the actor table. To run your query you should click (F5) button or click the button with a thunder icon on it.
As you see we run our first query and received all the data from the actor table. The SELECT statement is used to select data from a database. SELECT syntax should be :
SELECT column1, column2, …
FROM table_name;
If you would like to add a condition, you can use the WHERE clause. The WHERE clause is used to filter records and extract only those records that fulfil a specified condition.
The syntax should look like this:
SELECT column1, column2, …
FROM table_name
WHERE condition;
Let’s write a small query again to find data.
Select * from actor WHERE last_name = ‘Guiness’;
We select all the data from the actor table whose surname is ‘Guiness’
As you can see from the screenshot, in the actor table there are 3 record matches to our condition.
That’s all. If you would like to learn more about Software Testing you can have a look at my course on Udemy: https://www.udemy.com/software-testing-masterclass-from-novice-to-expert/?couponCode=WEBSITEPROMO .With this link, you will receive the best price possible. Also If you want to learn more about SQL commands, simply follow the w3school guide:
https://www.w3schools.com/sql/default.asp
You can also practice easily via w3school:
https://www.w3schools.com/sql/exercise.asp?filename=exercise_select1
Thanks for reading!