SQL Tutorial For Software Testers

In this Free SQL Tutorial, we cover all SQL concepts in detail with easy to understand practical examples. This free tutorial is helpful for beginners to advanced level users.

Who are the targeted audience of this Selenium Tutorial?
Software Testers, QA Engineers

SQL tutorial aims to teach beginners on how to use the SQL language to access and manipulate data. SQL stands for Structured Query Language.

SQL Overview:

SQL Overview: SQL stands for Structured Query Language. It is an American National Standard Institute (ANSI) standard. It is a standard language for accessing and manipulating databases. Using SQL, some of the action we could do are to create databases, tables, stored procedures (SP’s), execute queries, retrieve, insert, update, delete data against a database.

SQL keywords are not case sensitive.

Example: CREATE is same as create.

SQL commands are segregated into following types:

  1. DDL – Data Definition Language
  2. DML – Data Manipulation Language
  3. DQL – Data Query Language
  4. DCL – Data Control Language
  5. TCL – Transaction Control Language

Let’s see each type in detail:

i. DDL(Data Definition Language):  Allows to work with the Structure or Definition of the data

SQL commands come under DDL are as follows: CREATE, ALTER, DROP, TRUNCATE, COMMENT, RENAME

ii. DML(Data Manipulation Language): To deal with the data itself directly

SQL Commands come under DML are as follows: INSERT, UPDATE, DELETE

iii. DQL(Data Query Language): Deals with the data but to retrive the data

SQL Commands come under DQL are as follows: SELECT

iv. DCL(Data Control Language): Controls the data

SQL Commands come under DCL are as follows: GRANT, REVOKE, DENY

v. TCL(Transaction Control Language): Deals with the transaction management

SQL Commands come under TCL are as follows: COMMIT, ROLEBACK, SAVE

Most important SQL Commands are as follows:

SELECT – It extracts data from a DataBase
UPDATE – It updates data in a DataBase
DELETE – It deletes data from a DataBase
INSERT INTO – It inserts new data into a DataBase
CREATE – It creates a new DataBase/Table/Index
ALTER – It modifies a DataBase/Table
DROP – It deletes a table/Index.

 

QL Data Types:

In this post, we see SQL Data Types in detail.

In SQL Server, each column in a database table has a name and a data type. We need to decide what type of data to store inside each and every column of a table while creating a SQL table.

How To Create an SQL Table.

SQL Server supplies a set of system data types that define all types of data that can be used with the SQL Server.

SQL Server offers following categories of data types:

  1. Exact Numeric
  2. Approximate Numeric
  3. Date and Time
  4. Character Strings
  5. Unicode Character Strings
  6. Binary

Let’s see the SQL Data Types in detail.

Exact Numeric SQL Data Types:

bigint = Range from -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)
int = Range from -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)
smallint = Range from -2^15 (-32,768) to 2^15-1 (32,767)
tinyint = Range from to 255
bit = 0 and 1
decimal = Range from –10^38 +1 to 10^38 -1
numeric = Range from -10^38 +1 to 10^38 -1
money = Range from -922,337,203,685,477.5808 to +922,337,203,685,477.5807
small money = Range from -214,748.3648 to +214,748.3647

Approximate Numeric SQL Data Types:

float = Range from -1.79E + 308 to 1.79E + 308
real = Range from -3.40E + 38 to 3.40E + 38

Date and Time SQL Data Types:

datetime = From Jan 1, 1753 to Dec 31, 9999
smalldatetime = From Jan 1, 1900 to Jun 6, 2079
date = To store a date like March 27, 1986
time = To store a time of day like 12:00 A.M.

Character Strings SQL Data Types:

char = Maximum length of 8,000 characters
varchar = Maximum of 8,000 characters
varchar(max) = Maximum length of 231 characters
text = Maximum length of 2,147,483,647 characters.

Unicode Character Strings SQL Data Types:

nchar = Maximum length of 4,000 characters
nvarchar = Maximum length of 4,000 characters
nvarchar(max) = Maximum length of 231 characters
ntext = Maximum length of 1,073,741,823 characters

Binary SQL Data Types:

binary = Maximum length of 8,000 bytes
varbinary  = Maximum length of 8,000 bytes
varbinary(max) = Maximum length of 231 bytes
image = Maximum length of 2,147,483,647 bytes.

 

SQL Operators:

In this post, we see SQL Operators in detail.

SQL Operator is a reserved word used primarily in an SQL statement’s WHERE clause to perform operations, such as arithmetic operations and comparisons. These are used to specify conditions in an SQL statement.

There are are three types of Operators.

  1. Arithmetic Operators
  2. Comparison Operators
  3. Logical Operators

SQL Arithmetic Operators:

Let’s assume two variables “x” and “y”. Here “x” is valued 30 and “y” valued 60.

In SQL, we use Arithmetic operators such as +*/%.

To add two numbers we use the operator +

Based on the above mentioned values of x and y.

Check the below image for the description of each operator.

SQL Arithmetic Operators

SQL Comparison Operators:

Let’s assume two variables “x” and “y”. Here “x” is valued 30 and “y” valued 60.

In SQL, we use Comparison operators such as =!=><>=<=,<>!<!>. Check the below image for the description of each operator.

SQL Comparison Operators

SQL Logical Operators:

In SQL, we use Logical operators such as AND, OR, NOT, IN, BETWEEN, ALL, EXISTS, LIKE. Check the below image for the description of each operator.

SQL Logical Operators

In the next tutorial, we will see How To Create A Database In SQL.

 

SQL Create Database:

The SQL CREATE DATABASE statement is used to create new SQL database

Syntax:

Note: Always database name should be unique within the RDBMS.

Also Read: SQL Create Table

Example:

I am creating a Database say TestDB

Note: Make sure you have admin privilege before creating any database.

To see the list of databases, use the following syntax. We see list of Select Queries in other post in detail.

We have created a Database named ‘TestDB’. In the below image, we could find the list of Databases based on the above query. In the list of Databases, we could find the database ‘TestDB’ which we have created.

In the next tutorial, we will see How To Create A Table In SQL.

 

SQL Create Table:

The SQL CREATE TABLE statement is used to create a new table in a database. Creating a table involves naming the table and defining its columns and data type of each column.

SQL Create Table Syntax:

The column_name_1 parameters specify the names of the columns of the table.

The data_type parameter specifies the data type of the column (e.g. char, varchar, integer, decimal, float, etc.).

Example:

Creating a Table in the Database.

Copy the above Create Table statement and execute it. You could see “Command(s) completed successfully” in the result console.

To verify whether the table is created or not. You need to write Select Query. We will see Select Query in detail in later posts. Click here to view detailed post on Select Query.

Also to view the complete table structure, use the following statement.

 

Now, you have SCOREBOARD table available in the database. We could use this table SCOREBOARD to store required information.

In the next tutorial, we will see How To Retrive the data from the table using SELECT Query.

 

SQL Select Query:

SQL Select Query is used to select data from a Database.

Syntax:

or

where * represents all the columns of the table.

Example:

To get all the records from a table. I have a sample table named ‘SCOREBOARD’. Using the Select query, we could get all the records from the table.

Check this post on how to create a table in SQL.

How To Create Table in SQL

 

To get particular column records from a table using SQL Select Query:

Syntax:

Example:

By using the below code, we could fetch just from one particular column say ‘Playername’ from the table ‘SCOREBOARD’

 

In the next tutorial, we will see How To Use Where Clause In SQL