Welcome to this tutorial on SQL for Testers. As a software tester, you are required to perform database testing that requires the knowledge of different SQL and database concepts. In addition, you are required to write SQL queries to retrieve, update and insert data in the databases. Therefore, just like the DBAs and SQL developers, learning this skill is equally important for software testing professionals as well.
I have designed this tutorial for complete beginners with no or minimal prior knowledge of SQL. We will start with the database fundamentals. After that, we will move to the SQL concepts. Finally, we’ll check some of the widely used SQL commands.
Once, you complete this tutorial, you can test your knowledge with our comprehensive list of frequently asked – DBMS interview questions and SQL query interview questions.
So, let’s start this SQL for testers article with the database fundamentals first.
What is a Database?
A database is an organized and systematic collection of data that is stored and accessed in/from a computer system. Organizations use the databases as a method to store, manage, and retrieve information in real-time.
DBMS – Database Management System
DBMS is a software system that is designed to maintain and access the database. It allows the user to access, create, delete, and update data in a database.
DBMS defines the rules for manipulation and validation of this data. We use a database when there is a huge amount of data, the security of the data is important, or when multiple users have to have access to the data concurrently.
RDBMS – Relational Database Management System
RDBMS is an advanced version of the basic DBMS. This databases management system uses the relational model for its databases i.e. RDBMS enables the user to create relational databases.
A relational database is a database that allows the user to store related data in the form of multiple tables, which are linked by establishing a relationship between the different tables, hence providing an efficient way to access the database.
A RDBMS database uses tables for storing the data. A table is nothing but a collection of data that is related to one another, in the form of rows and columns.
A Row represents a collection of fields that end up making a record in the database.
In a database, a column represents those values that are of the same type. A column is also called an attribute.
What is SQL?
SQL stands for Structured Query Language. It is a programming language that is used to request information from a database. SQL can be used to manage and share data in a relational database management system. Moreover, users can perform actions like insertion, deletion, selection, etc on the database.
SQL programming is widely used as using this language we can create, store as well as manipulate the data inside the database. Moreover, SQL can be embedded within other languages through SQL libraries and modules.
SQL Data Types
Various data types in SQL are-
|VARCHAR(size)||Variable length character data|
|CHAR (size)||Fixed-length character data|
|BINARY (size)||Binary byte strings|
|NUMBER(a,b)||Variable-length numeric data|
|DATE||Date and time values|
|LONG||Variable-length character data up to 2 gigabytes|
|CLOB||Character data up to 4 gigabytes|
|RAW||Raw binary data|
|BLOB||Binary data up to 4 gigabytes|
|BFILE||Binary data stored that is stores in external file up to 4 gigabytes|
|ROWID||64 base number system for the unique address of a particular row in the table|
|+||Add values of operands|
|–||Subtract values of operands|
|*||Multiply operand’s values|
|/||Divide values of operands|
|%||Modulus operation on operands|
In the table below, if condition gets satisfied then “True” Boolean value is returned.
|=||Determine if the values of operands are equal.|
|!=||Check if the values of operands are not equal.|
|>||Determine if the left operand is more than the right operand.|
|<||Check if the right operand is more than the left operand.|
|>=||Determine if the left operand is more than or equal to the right operand.|
|<=||Check if the right operand is more than or equal to the left operand.|
|!>||Determine if the right operand is not more than the left operand.|
|!<||Check if the left operand is not more than the right operand.|
|OR||Returns true if either operand is true. Else it returns false if both the operands are false.|
|AND||Returns true if both operands are true. Else it returns false if either or both the operands are false|
|NOT||Returns true if condition is false and returns false if the condition is true.|
The CREATE DATABASE statement is used to create a fresh new SQL database. Following is the syntax-
CREATE DATABASE database_name;
The DROP DATABASE query is used to drop or delete an existing SQL database. Syntax-
DROP DATABASE database_name;
The RENAME DATABASE query is used to rename an existing database. Following is the syntax-
RENAME DATABASE old_database_name TO new_database_name;
The SELECT DATABASE query is used to select all the fields inside an existing database. Syntax-
SELECT * FROM database_name;
The CREATE TABLE query is used to create a new table. Following is the syntax for SQL create table usage-
CREATE TABLE <table_name> (<Field><Datatype><(Width)><constraint>,..);
The DROP TABLE query is used to drop an existing table. Syntax-
DROP TABLE <table_name>;
The RENAME query is used to change the name of an existing table. Following is the syntax-
RENAME old_table_name TO new_table_name;
The TRUNCATE TABLE query is used to delete all rows from a table, retaining its structure. Following is the syntax-
TRUNCATE TABLE <table_name>;
Select * Into
This query is used to copy a table into another table in the same database. Syntax-
SELECT * INTO <required_table> FROM <source_table>;
ALTER command can be used to perform various manipulations on our database. These are as listed below-
a. Following is the syntax to add a column to the table.
ALTER TABLE <table_name> ADD <field_name> <Type> <(width)>;
ALTER TABLE department ADD dep_name varchar (20);
b. Following is the syntax to modify the attributes of data in a field of the table.
ALTER TABLE <table_name> MODIFY (<column_name > < new_data_type>);
ALTER TABLE department MODIFY dep_name varchar (15);
c. Following is the syntax to drop or delete a field of the table.
ALTER TABLE <table_name> DROP COLUMN < column_name>;
ALTER TABLE department DROP column address;
d. Following is the syntax to rename a column-
ALTER TABLE <table_name> RENAME COLUMN <old_column_name> TO
ALTER TABLE department RENAME COLUMN dep_id to department_id;
e. Drop a table – Deletes a Table and along with it all its contents. Following is the syntax-
DROP TABLE <Table_name>;
DROP TABLE department;
f. Truncate a table – Deletes all rows from a table but it retains its structure. Following is the syntax for truncate operation-
TRUNCATE TABLE <table_name>
TRUNCATE TABLE department;
g. Following is the syntax to rename a table.
RENAME <old_tablename> TO <new_table_name>
RENAME dep_name TO department_name;
|NOT NULL||It specifies that a column must have some value|
|UNIQUE||It specifies that columns must have unique values|
|PRIMARY KEY||It specifies a column or a set of columns that uniquely identify a row, it does not allow null values|
|FOREIGN KEY||It is a column(s) that references a column(s) of a table|
|CHECK||It specifies a condition that must be satisfied by all the rows in a table|
The SQL insert commands are used to insert data into the database. This can be achieved by the following-
a. Inserting values given by the user – This is used to insert certain values given by the user into the table. Following is the syntax-
INSERT INTO <table_name> VALUES ( val1,val2 …);
INSERT INTO department VALUES(20, 'HR', 'DELHI');
b. Inserting values into a specific column of the table – This query is used to insert values into some specific column inside the table. Following is the syntax-
INSERT INTO <Table_Name> (column_name1, column_name2, column_name3,..) VALUES (val1, val2, val3,…..);
INSERT INTO department (dept_id , dep_name) VALUES(20, 'finance');
SQL Select Statements
The SELECT statement in SQL is used to select some specific data from the tables in the database and show them to the user. Following is the syntax to fetch all fields-
SELECT * FROM table_name;
This SQL query is used to select all the unique values stored in the table in a given database. Syntax-
SELECT DISTINCT column_name FROM table_name;
The select count gives the number of rows in the specified table that satisfies the condition given by the user. Condition for a query can be specified in the WHERE clause. It returns 0 if there are no rows that satisfy the specified condition.
SELECT COUNT (column_name) FROM table_name WHERE condition;
SELECT COUNT (dep_sal) FROM department WHERE dep_sal>10000;
The sql select top query is used to select the top specified number of records from the table.
SELECT TOP number_of_records FROM table_name WHERE condition;
SELECT TOP 4 FROM department WHERE dep_sal>10000;
The AS command is used to change the name of a column or the table with an alias. This given name only exists for the duration of the query being executed.
SELECT column_name AS new_column_name FROM table_name;
SELECT dep_id AS department_id FROM department;
The sql IN command is used to specify multiple values in the WHERE part of the query.
SELECT column_name FROM table_name WHERE column_name IN (val1, val2, …);
SELECT dep_name FROM department WHERE dep_id IN (1020, 1044);
The GETDATE () query is used to return the database’s current date and time, format for which is YYYY-MM-DD hh:mm:ss.mmm
The sum() query is used to return the total sum of the specified numeric column.
SELECT SUM column_name FROM table_name WHERE condition;
SELECT SUM dep_sal FROM department WHERE dep_sal>10000;
The select null query is used to select all the records having null values.
SELECT column_name FROM table_name WHERE condition IS NULL;
SELECT dep_sal FROM department WHERE dep_sal>10000 IS NULL;
The sql ‘where’ clause is used to select only those records which satisfy the specified condition
SELECT column_name FROM table_name WHERE condition;
SELECT dep_sal FROM department WHERE dep_sal>10000;
The sql AND is an operator that is used to select records on more than one condition. The AND operator returns values if both the conditions specified are TRUE.
SELECT column_name FROM table_name WHERE condition1 AND condition2 …;
SELECT dep_sal FROM department WHERE dep_sal>10000 AND dep_name=HR;
The SQL OR is an operator that is used to select records on more than one condition. The OR operator returns values if either condition specified is TRUE.
SELECT column_name FROM table_name WHERE condition1 OR condition2 …;
SELECT dep_sal FROM department WHERE dep_sal>10000 OR dep_name=HR;
The SQL GROUP BY query is used to group rows according to the specified condition or the specified column name. This GROUP BY query is generally used with functions like MAX, IN, AVG, SUM, COUNT.
SELECT column_name FROM table_name WHERE condition GROUP BY column_name;
SELECT COUNT(dep_sal) FROM department GROUP BY dep_name;
The SQL HAVING clause is used when we want to specify the condition for the selection of records with functions like MAX, IN, AVG, SUM, COUNT as with these functions WHERE clause cannot be used.
SELECT column_name FROM table_name GROUP BY column_name HAVING condition;
SELECT COUNT (dep_id) FROM department GROUP BY dep_name HAVING dep_sal>10000;
The SQL ORDER BY function is used to sort our selection in either ascending or descending order.
SELECT column_name FROM table_name ORDER BY column_name ASC/DESC;
SELECT dep_name FROM department ORDER BY dep_id ASC;
The SQL UPDATE command is used to change or modify any existing fields/records in the table.
UPDATE table_name SET column_name1 = val1, column_name2 = val2… WHERE condition;
UPDATE department SET dep_name="travel" WHERE dep_id=1020;
The SQL DELETE command is used to remove any existing fields/records from the table.
DELETE FROM table_name WHERE condition;
DELETE FROM department WHERE dep_name="Travel";
The SQL UNION operator is used to combine the resulting collection of data of two or more SELECT statements.
SELECT column_name FROM table_name1 UNION SELECT column_name FROM table_name2;
SELECT dep_name FROM department UNION SELECT emp_name FROM employee;
The SQL intersect operator is used to return those values which common to both the datasets. This operator will only work if both the datasets being intersected have similar data types.
SELECT column_name FROM table_name1 INTERSECT SELECT column_name FROM table_name2;
SELECT dep_name FROM department INTERSECT SELECT emp_dep FROM employee;
The SQL EXCEPT operator used between two SELECT statements. This operator selects those rows which are present in the SELECT statement and not in the second SELECT statement.
SELECT column_name FROM table_name EXCEPT SELECT column_name FROM table_name;
SELECT dep_name FROM department EXCEPT SELECT emp_dep FROM employee;
SQL Join Command
Very simply, the SQL join command is used to join or combine either rows or an entire table, on the basis of a common column between them. There are various types joins in SQL are-
SQL Inner Join
The SQL inner join is used to combine the common records shared by the two datasets.
SELECT column_name FROM table_name1 INNER JOIN table_name2 ON table_name1.column_name = table_name2.column_name ;
SELECT dep_name FROM department INNER JOIN employee ON department.dep_name = employee.emp_dep;
SQL Left Join
The SQL left join is used to combine records from the left table and the common records of both the tables.
SELECT column_name FROM table_name1 LEFT JOIN table_name2 ON table_name1.column_name = table_name2.column_name;
SELECT dep_name FROM department LEFT JOIN employee ON department.dep_name = employee.emp_dep;
SQL Right Join
The SQL right join is used to combine records from table 2(right) and the common records of both the tables.
SELECT column_name FROM table_name2 RIGHT JOIN table_name1 ON table_name2.column_name = table_name1.column_name;
SELECT emp_name FROM employee RIGHT JOIN department ON department.dep_name = employee.emp_dep;
FULL Outer Join
The full outer join is used to combine all the records from both the tables when there are some common records between the tables.
SELECT column_name FROM table_name1 FULL OUTER JOIN table_name2 ON table_name1.column_name = table_name2.column_name;
SELECT dep_name FROM department FULL OUTER JOIN employee ON department.dep_name = employee.emp_dep;
SQL Self Join
The SQL self join is used to combine a table with itself by creating a temporary table of itself and joining it with itself.
SELECT column_name FROM table1 table_alias1, table2 table_alias2 WHERE condition;
SQL Cross join
The SQL cross join is used to combine two tables wherein the result set produced will be a product of the number of rows in the first table and the number of rows in the second table. This result set is generally called the Cartesian product.
SELECT * FROM table_name1 CROSS JOIN table_name2;
SELECT * FROM department CROSS JOIN employee;
Recommended – SQL Joins Interview Questions.
An SQL subquery is a query nested within another query. These subqueries are placed in SQL clauses like WHERE, HAVING, WITH, FROM etc.
The subqueries are executed first and the output obtained after executing this query is used to execute the outer or the main query.
SELECT column_name FROM table_name WHERE condition OPERATOR (SELECT column_name FROM table_name WHERE condition);
In the syntax given above the query present inside of the WHERE clause is called the subquery.
SELECT dep_name FROM department WHERE dep_name in (SELECT dep_name FROM department WHERE dep_sal>1000);
With this, we have come to the end of this tutorial on SQL for Testers. I hope this fast-paced SQL tutorial will help you in quickly grasping the SQL concepts. If you have any questions or suggestions related to this topic, you can let us know in the comment section.
Kuldeep is the founder and lead author of ArtOfTesting. He is skilled in test automation, performance testing, big data, and CI-CD. He brings his decade of experience to his current role where he is dedicated to educating the QA professionals. You can connect with him on LinkedIn.
5 thoughts on “SQL For Testers”
Thank you very much, I liked the tutorial! With all due respect I just wanted to comment that I think you have made a mistake in the part of SQL Cross join “The SQL cross join is used used to combine two…” Greetings
Thanks, typo corrected.
Is these concepts is enough for manual testing role?
These concepts will provide you with a solid base for a QA role.
hi, thanks for the explanations, is there any tutorial video you can recommend for hands-on practice of the sql queries above? that will be handy.
thanks once again.