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.
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 of the table in the database represents the collection of fields that end up making a record of the database.
In a database, a column represents those values which are of the same type. A column is also called as 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. 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-
|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 is satisfying “True” Boolean value is returned.
|=||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|
|OR||Returns true if either operand is true, returns false if both the operands are false.|
|AND||Returns true if both operands are true, 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.|
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
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;
|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|
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
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;
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;
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
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;
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;
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;
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;
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 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.