SQL For Testers

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.

Database Fundamentals

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.

Tables

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.

Rows

A Row represents a collection of fields that end up making a record in the database.

Column

In a database, a column represents those values that are of the same type. A column is also called an attribute.

SQL Fundamentals

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-

SQL Operators

Arithmetic operators

Comparison operators

In the table below, if condition gets satisfied then “True” Boolean value is returned.

Logical Operators

SQL Commands

Create Database

The CREATE DATABASE statement is used to create a fresh new SQL database. Following is the syntax-

CREATE DATABASE database_name;

Drop Database

The DROP DATABASE query is used to drop or delete an existing SQL database. Syntax-

DROP DATABASE database_name;

Rename Database

The RENAME DATABASE query is used to rename an existing database. Following is the syntax-

RENAME DATABASE old_database_name TO new_database_name;

Select Database

The SELECT DATABASE query is used to select all the fields inside an existing database. Syntax-

SELECT * FROM database_name;

Create Database

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>,..);

Drop Table

The DROP TABLE query is used to drop an existing table. Syntax-

DROP TABLE <table_name>;

Rename Table

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;

Truncate Table

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 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 

<new_column_name>;

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;

SQL Constraints

Insert Commands

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

SELECT *

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;

Select Distinct

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;

Select Count

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;

Select Top

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;

Select As

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;

Select In

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);

Select Date

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

SELECT GETDATE();

Select Sum

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;

Select Null

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;

SQL Clauses

Where

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;

And

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;

Or

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;

Group By

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;

Having

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;

Order By

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;

Update Command

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;

Delete Command

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";

Union Operator

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;

Intersect Operator

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;

Except Operator

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.

SQL Subquery

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);

Conclusion

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.