logo logo

SQL 101– The Ultimate Guide For QAs

SQL 101– The Ultimate Guide For QAs

As a QA, it is always important to be willing to learn more about things that could help your daily activities. SQL is must-have knowledge for QAs and can be differential in your career 👩‍💻 SQL will give you the power to deal with data inside databases, sometimes creating test data and checking its accuracy during test activities.

Data is a core part of many kinds of applications, as a database system is connected with the application. SQL is a programming language that enables us to work with this data.

This guide will explain what database and SQL are, and what are the basic concepts you should learn before starting. You will also learn the main SQL commands and practices using the SQL Fiddle tool. Finally, you will have access to a SQL cheat sheet, where all learned commands are summarized easily.

SQL Guide For QAs – Table of Content

What is a database?

Companies have several pieces of information that need to be organized and available within their business, for later queries by the team and management.

A database is a tool that promotes a grouping of data that deals with the same subject, and that needs to be stored for security and for a future conference. It allows the manipulation of this information and makes the company’s routine much simpler ✅

There are several types of databases, each one suited to a customer’s need. The most common and well-known databases are Oracle, MySQL, SQL Server, and PostgreSQL.

What is SQL?

SQL stands for “Structured Query Language”, and it is the standard language for dealing with relational databases. In short, SQL is a language that allows communication with databases to manage all the data they contain.

In general, SQL can be used to create databases- that means to create, update, and delete tables inside a database, and to insert, search, update, and delete database records. SQL can do lots of other operations, including optimizing and maintenance of databases.

SQL is considered a programming language, but instead of being a programming language for building applications, it is a language that enables all these database manipulations.

Before we start: basic concepts

For a better understanding of SQL, there are some important concepts that need to be clarified before we start. In this section, you will better understand which data types exist, and what DDL and DML are 📊

Data types

There are a lot of data types that can be stored in a database. The most popular ones used in SQL commands are text, integer numbers, float numbers, and date & time.

DDL x DML

DDL stands for “Data Definition Language” and DML stands for “Data Manipulation Language”. DDL is used to define data structures, and DML is used to manipulate data itself.

SQL Fiddle

SQL Fiddle is an online application for testing SQL commands. We will use this tool in order to practice most of the commands we are going to learn throughout the article.

How to use

For practicing in this article, you must:

  1. Navigate to SQL Fiddle.
  2. Select MySQL version in the top left corner, as shown in the picture below 👇

MySQL Fiddle version

SQL guide: the most important commands

This section will show you which are the main DDL and DML commands that you can use to create a database, its structure, and manipulate & query data. You will learn how to run most of these commands using SQL Fiddle.

Some optional information could be sent in some commands, but it is not the goal of this article to explore all possible optional command attributes.

Create database

The CREATE DATABASE command is used to create a new database:

CREATE DATABASE <database_name>

Alter database

The ALTER DATABASE command is used to update a characteristic of a database. For example, to change the name of a database, you could send the command:

ALTER DATABASE <database_name>
MODIFY NAME = <new_database_name>

Create table

The CREATE TABLE command is used to create a new table inside the database:

CREATE TABLE <table_name> (
    <column_1> data_type(size),
    <column_2> data_type(size),
    …
)

In the below example, we are going to create a user’s table, with name, lastName, job, and age columns. Each column has its own data type like int and varchar (that is used for text/string values) and followed by its size (to the varchar data type).

In SQL Fiddle, you can use the left side to insert the bellow command and click on the Build Schema button:

Create SQL table

You will see a green message “Schema Ready”. The table was created, and we will use it in the future.

Alter table

The ALTER TABLE command is used to update an existent table inside the database:

ALTER TABLE <table_name>
ADD/MODIFY/DROP COLUMN <column_name> <data_type>

In the following examples, we are going to:

  1. Add a new column “salary” as varchar(200).
  2. Modify salary column type to be float(10,2). 10 is the column size and 2 is the number of decimal digits.
  3. Drop the salary column.

In SQL Fiddle, you can use the left side to insert the bellow command and click on the Build Schema button:

Alter table

You will see a green message “Schema Ready”. The updates were done.

Drop table

The DROP TABLE command is used to delete an existent table inside the database:

DROP TABLE <table_name>

In SQL Fiddle, you can use the left side to insert the bellow command and click on the Build Schema button:

Drop table

You will see a green message “Schema Ready”. The table was deleted.

Insert into

The INSERT command is used to insert data in a database table:

INSERT INTO <table_name> (<column_1>, <column_2>, …)
VALUES (<value_1>, <value_2>, …)

Alternatively, you can use it without informing the column names, if you are going to fill in all columns of a table:

INSERT INTO <table_name>
VALUES (<value_1>, <value_2>, …)

In the below examples, we are going to insert three registers. Before running these commands, run again the CREATE TABLE command, given that we deleted the table in the last command. Examples:

  1. Using column names filling in all values.
  2. Using column names filling in some values (lastName is not provided).
  3. Without using column names (you should inform all values in the correct order).

In SQL Fiddle, you can use the left side to insert the bellow command and click on the Build Schema button:

SQL guide: inserting values into a table

You will see a green message “Schema Ready”. The registers were inserted into the table.

Select

The SELECT command is used to query data inside the database. The basic structure of the select command is:

SELECT <column_1>, <column_2>, …
FROM <table_name>

If you want to get all columns, you can alternatively use * instead of column names. In the below example, we will retrieve all data from the table.

To run the SELECT commands you can use the right side of SQL Fiddle. Just insert the command and click on the RUN SQL button. In the bellow part, you will see the results:

Run SQL in Fiddle

After running the query, you will get this result informing the data into the users table.

Query result

Where Clause

The SELECT command is commonly used with a WHERE clause, but it is optional. “WHERE” allows you to filter data through one or more conditions. You can add the WHERE clause after the FROM clause:

SELECT <column_1>, <column_2>, …
FROM <table_name>
WHERE <condition>

In this first example, we are filtering users with the name = Juliana:

Where clause in SQL

As expected, it returned only one register:

Results

In the next example, we are filtering users with job = QAE and age > 30:

SQL guide: multiple conditions in the WHERE clause

In the result, we only have one register that fits the condition:

Query result

Next example filters users with age < 30 or job = QAM:

Query using OR

In the result we have two registers that fit the condition:

Query results

Order by clause

It is possible to sort the results of a query using the ORDER BY clause (optional clause). You can add the ORDER BY clause, after the FROM clause or after the WHERE clause, given that the WHERE clause is also optional:

SELECT <column_1>, <column_2>, …
FROM <table_name>
WHERE <condition>
ORDER BY <column_name>

In this first example, we want to order the result in an alphabetical order:

Order by name in alphabetical order

In the result, it is possible to verify that the registers are now in an alphabetical order:

Ordered results

By default, it used the ascending order. But by using the DESC option after column name, you can use the descending order as shown in the next example, where we select all the registers in descending order by age:

Order by age, descending

The results are shown in the correct order.

Results ordered descending

Group By clause

It is possible to group the results of a query that have the same value into summary rows, using the GROUP BY clause (optional clause). You can add the GROUP BY clause at the end of your SELECT command:

SELECT [COUNT/AVG/SUM](<column_1>), <column_2>, …
FROM <table_name>
GROUP BY <column_name>

Complementarily, we need to use the COUNT/AVG/SUM function to be able to count/get average value/sum, when using the GROUP BY clause. In the below example, we want to get the number of users grouped by job. We can use “as” followed by a string value, to name a column with a friendly name:

SQL guide: Group by

In the result, we got two columns (job and quantity) and two rows (one for QAE and one for QAM):

Grouped results

The results showed that in our user table, we have two users that have QAE as a job, and one that has QAM as a job. In the next example, we want to get the average age of users grouped by job:

Calculate average age for group

In the result, we got two rows (one for QAE and one for QAM):

Results with average age

The results showed that the average age of the QAEs is 28 years (remember that we have two QAE users, one with 31 and other with 25 years), and the average age of QAM is 42, given that we have only one QAM user.

Update

The UPDATE command is used to update the values of a register:

UPDATE <table_name>
SET <column_1> = <value_1>, <column_2> = <value_2>
WHERE <conditions>

In the below example, we are going to update lastName of the user with name = Juliana. In SQL Fiddle, you can use the left side to insert the bellow command and click on the Build Schema button:

SQL guide: Update

You will see a green message “Schema Ready”. The registers were updated.

Delete

The DELETE command is used to delete registers from a specific table:

DELETE FROM <table_name>
WHERE <conditions>

In the below example, we are going to delete the user with name = Marcos. In SQL Fiddle, you can use the left side to insert the bellow command and click in the Build Schema button:

Delete command

You will see a green message “Schema Ready”. The register was removed from the table.

SQL cheat sheet

In this section, you will get access to the SQL cheat sheet, a great summary of all the SQL commands that have been explored throughout this guide. It is a quick-access resource that you can use every day, whenever you need to remember any SQL command 🔥

SQL guide: SQL Cheat Sheet

What is next?

There is some advanced SQL knowledge that you should research and read, like procedures, primary and foreign keys, or join statements. The goal of the article is to provide a basic guide you should have to be a better QA.

Conclusion

In this article, you learned what databases and SQL are, the basic data types, and the difference between DDL and DML languages. You also learned the most used SQL commands with examples using the SQL Fiddle tool. Finally, you had access to a SQL cheat sheet with all the commands presented in this guide, for an easy access whenever you need it.

SQL knowledge is a must-have to any IT professional, as it’s an important skill to use. Hope this guide helped you! Happy testing 🤗

Avatar

About the author

Paulo Oliveira

I’m a proactive, cooperative, and responsible Quality Assurance Engineer with more than 14 years of experience in software testing. I love to automate tests for all kind of applications (both backend and frontend) in order to improve the team’s workflow, product quality, and customer satisfaction. Even though my main roles were hands-on testing applications, I’ve worked as QA Lead, planning and coordinating activities, as well as coaching and contributing to team member’s development. Mentoring people to achieve their goals is also something that makes my eyes shine.

Website- https://paulocoliveira.github.io/qa/

LinkedIn- https://www.linkedin.com/in/pcesar/

Join TestProject Community

Get full access to the world's first cloud-based, open source friendly testing community. Enjoy TestProject's end-to-end test automation Platform, Forum, Blog and Docs - All for FREE.

Join Us Now  

Leave a Reply

popup image

A new world for test automation

Join 150,000 testing & dev teams taking their web & mobile testing to new heights, using #1 FREE test automation platform, designed to help deliver quality at speed.
Get Started
FacebookLinkedInTwitterEmail