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?
- What is SQL?
- Before we start: basic concepts
- SQL Fiddle
- SQL guide: the most important commands
- SQL cheat sheet
- Conclusion
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:
- Navigate to SQL Fiddle.
- Select MySQL version in the top left corner, as shown in the picture below 👇
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:
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:
- Add a new column “salary” as varchar(200).
- Modify salary column type to be float(10,2). 10 is the column size and 2 is the number of decimal digits.
- 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:
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:
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:
- Using column names filling in all values.
- Using column names filling in some values (lastName is not provided).
- 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:
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:
After running the query, you will get this result informing the data into the users table.
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:
As expected, it returned only one register:
In the next example, we are filtering users with job = QAE and age > 30:
In the result, we only have one register that fits the condition:
Next example filters users with age < 30 or job = QAM:
In the result we have two registers that fit the condition:
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:
In the result, it is possible to verify that the registers are now in an alphabetical order:
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:
The results are shown in the correct order.
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:
In the result, we got two columns (job and quantity) and two rows (one for QAE and one for QAM):
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:
In the result, we got two rows (one for QAE and one for QAM):
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:
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:
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 🔥
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 🤗