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 along with the ability to write SQL queries to retrieve, update and insert data in the databases. So, 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 and then move to the SQL concepts and 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 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 of the table in the database represents the collection of fields that end up making a record of the database.

Column

In a database, a column represents those values which are of the same type. A column is also called as 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. With this language, users can perform actions like insertion, deletion, selection, etc on the database.

SQL programming is widely used as using this language we can describe, 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-

Data typeDescription
VARCHAR(size)Variable length character data
CHAR (size)Fixed-length character data
BINARY (size)Binary byte strings
NUMBER(a,b)Variable-length numeric data
DATEDate and time values
LONGVariable-length character data up to 2 gigabytes
CLOBCharacter data up to 4 gigabytes
RAWRaw binary data
BLOBBinary data up to 4 gigabytes
BFILEBinary data stored that is stores in external file up to 4 gigabytes
ROWID64 base number system for the unique address of a particular row in the table


SQL Operators

Arithmetic operators

OperatorDescription
+Add values of operands
Subtract values of operands
*Multiply operand’s values
/Divide values of operands
%Modulus operation on operands


Comparison operators

In the table below if condition is satisfying “True” Boolean value is returned.

OperatorDescription
=Determine if values of operands are equal
!=Determine if values of operands are not equal
>Determine if left operand is more than right operand
<Determine if right operand is more than left operand
>=Determine if left operand is more than or equal to right operand
<=Determine if right operand is more than or equal to left operand
!>Determine if right operand is not more than left operand
!<Determine if left operand is not more than right operand


Logical Operators

OperatorDescription
ORReturns true if either operand is true, returns false if both the operands are false.
ANDReturns true if both operands are true, returns false if either or both the operands are false 
NOTReturns true if condition is false and returns false if the condition is true.

SQL Commands

SQL CREATE DATABASE

The CREATE DATABASE statement is used to create a fresh new SQL database. Syntax-

CREATE DATABASE database_name;



SQL DROP DATABASE

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

DROP DATABASE database_name;



SQL RENAME DATABASE

The RENAME DATABASE query is used to rename an existing database. Syntax-

RENAME DATABASE old_database_name TO new_database_name;



SQL SELECT DATABASE

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

SELECT * FROM database_name;



SQL CREATE TABLE

The CREATE TABLE query is used to create a new table. Syntax-

CREATE TABLE <table_name> (<Field><Datatype><(Width)><constraint>,..);



SQL DROP TABLE

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

DROP TABLE <table_name>;



SQL RENAME TABLE

The RENAME query is used to change the name of an existing table. Syntax-

RENAME old_table_name TO new_table_name;



SQL TRUNCATE TABLE

The TRUNCATE TABLE query is used to delete all rows from a table, retaining its structure. Syntax-

TRUNCATE TABLE <table_name>;


 
SELECT * INTO

This query is used to copy a SQL table into another SQL table in the same database. Syntax-

SELECT * INTO <required_table> FROM <source_table>;



SQL ALTER TABLE

ALTER command can be used to perform various manipulations on our database. These are as listed below-

a. To add a column to the table. Syntax- 

ALTER TABLE <table_name> ADD <field_name> <Type> <(width)>; 

ALTER TABLE department ADD dep_name varchar (20);


b. To modify the attributes of data in a field of the table. Syntax- 

ALTER TABLE <table_name> MODIFY (<column_name > < new_data_type>); 

ALTER TABLE department MODIFY dep_name varchar (15);


c. To drop or delete a field of the table. Syntax- 

ALTER TABLE <table_name> DROP COLUMN < column_name>; 

ALTER TABLE department DROP column address;


d. To rename a column. Syntax- 

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

DROP TABLE <Table_name>; 

DROP TABLE department;


f. Truncate a table – Deletes all rows from a table but it retains its structure. Syntax-

TRUNCATE TABLE <table_name> 

TRUNCATE TABLE department;


g. To rename a table. Syntax- 

RENAME <old_tablename> TO <new_table_name> 

RENAME dep_name TO department_name;

SQL Constraints

ConstraintDescription
NOT NULLIt specifies that a column must have some value
UNIQUEIt specifies that columns must have unique values
PRIMARY KEYIt specifies a column or a set of columns that uniquely identify a row, it does not allow null values
FOREIGN KEYIt is a column(s) that references a column(s) of a table
CHECKIt specifies a condition that must be satisfied by all the rows in a table


SQL 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. 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. 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. 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;


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


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


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


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


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


SQL 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


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

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


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


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


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


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


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


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


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

Leave a Comment