SQL Date functions and Formats

SQL Functions: 

In this article, we will explore various SQL functions and Convert Date formats to use in writing SQL queries.

SYSDATETIME() - to get current system date

GETUTCDATE() - to get current UTC date

GETDATE() - to get current system date

DATEDIFF() - Diff between two dates

DATEPART() - to get some part of date for example - if you want to retrieve year part (2020) from date 12/09/2020

SQL Convert Date Formats:

We can use the SQL CONVERT() function in SQL Server to format DateTime in various formats.

Syntax for the SQL CONVERT() function is as follows.

SELECT CONVERT (data_type(length)),Date, DateFormatCode)

Data_Type: We need to define data type along with length. In the date function, we use Varchar(length) data types

Date: We need to specify the date that we want to convert

DateFormatCode: We need to specify DateFormatCode to convert a date in an appropriate form. We will explore more on this in the upcoming section

Employee Detail table:

Select * from EmployeeDetail 


Exercise Queries:

--1. Show "JoiningDate" in "dd mmm yyyy" format, ex- "15 Mar 2020"
SELECT CONVERT(VARCHAR(20),JoiningDate,106) AS ResultDateFormat FROM EmployeeDetail

--2. Show "JoiningDate" in "yyyy/mm/dd" format, ex- "2013/02/21" 
SELECT CONVERT(VARCHAR(20),JoiningDate,111) AS ResultDateFormat FROM EmployeeDetail

--3. Show only time part of the "JoiningDate". ex- hh:mm: ss
SELECT CONVERT(VARCHAR(20),JoiningDate,108) AS ResultDateFormat FROM EmployeeDetail

--4. Get only Year part of "JoiningDate".
SELECT DATEPART(YEAR,JoiningDate) AS ResultDateFormat FROM EmployeeDetail

--5. Get only Month part of "JoiningDate".
SELECT DATEPART(MM,JoiningDate) AS ResultDateFormat FROM EmployeeDetail

--6. Get only Day part of "JoiningDate".
SELECT DATEPART(DD,JoiningDate) AS ResultDateFormat FROM EmployeeDetail

--7. Get system date.
SELECT SYSDATETIME() 
SELECT GETDATE()

--8. Get UTC date. 
SELECT GETUTCDATE()

/*--9. Get the first name, current date, joiningdate and diff between 
current date and joining date in months. */

SELECT FirstName,GETDATE() AS [Current Date], JoiningDate
,DATEDIFF(MM,JoiningDate,GETDATE()) AS TotalMonths FROM EmployeeDetail

/*--10.Get the first name, current date, joiningdate and diff between 
current date and joining date in days.*/

SELECT FirstName,GETDATE() AS [Current Date], JoiningDate
,DATEDIFF(DD,JoiningDate,GETDATE()) AS TotalDays FROM EmployeeDetail

--11. Get all employee details from EmployeeDetail table whose joining year is 2008.
SELECT * FROM EmployeeDetail WHERE DATEPART(YYYY,JoiningDate)='2008'

--12. Get all employee details from EmployeeDetail table whose joining month is Jan(1).
 SELECT * FROM EmployeeDetail WHERE DATEPART(MM,JoiningDate)='1'

/*--13.Get all employee details from EmployeeDetail table whose joining date between 
"2008-01-01" and "2010-12-01".*/
 
 SELECT * FROM EmployeeDetail WHERE JoiningDate BETWEEN '2008-01-01'
 AND '2010-12-01' 

Please refer below YouTube video for more detail explanation:



How to find 2nd Lowest Salary | Find Nth Lowest Salary | 4 ways to get it

 EmployeeDetail table:

Four ways to get Nth lowest salary in EmployeeDetail table:

Select * from EmployeeDetail order by Salary 
---------------------1st Method----------------------------
--Using sub-query (With > Operator):
--2nd Highest Salary
Select min(Salary) As Salary from EmployeeDetail where 
Salary > (Select min(Salary) from EmployeeDetail)

--3rd Highest Salary
Select min(Salary) As Salary from EmployeeDetail where 
Salary > (Select min(Salary) from EmployeeDetail Where
Salary > (Select min(Salary) from EmployeeDetail))

---------------------2nd Method----------------------------
--Using sub-query (With Not In Operator)
Select min(Salary) As Salary from EmployeeDetail where 
Salary NOT IN (Select min(Salary) from EmployeeDetail)

---------------------3rd Method----------------------------
--Using correlated sub-query
Select distinct Salary from EmployeeDetail A
Where 2=(Select count(distinct salary) from EmployeeDetail B 
where A.Salary >= b.Salary)

---------------------4th Method-----------------------------
--Using TOP Clause keyword 
Select top 1 Salary From
(Select distinct top 2 Salary from 
EmployeeDetail Order by Salary)
As Temp Order by Salary DESC

/*NOTE: First three method won't consider NULL however 4th method 
will consider NULL values*/

Please find the below Youtube video:

SQL HAVING Clause

What is having Clause?

The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions.

A HAVING clause in SQL specifies that an SQL SELECT statement must only return rows where aggregate values meet the specified conditions. After the aggregating operation, HAVING is applied, filtering out the rows that don't match the specified conditions.

Why we cannot use where with aggregate function?

We cannot use the WHERE clause with aggregate functions because it works for filtering individual rows. In contrast, HAVING can works with aggregate functions because it is used to filter groups.

Basic Syntax:

SELECT column_names

FROM tableName

WHERE condition

GROUP BY column_names

HAVING condition

ORDER BY column_names

EmployeeDetail Table:

ProjectDetail Table:

Below Sample Queries using Having Clause:

/*Write Down the query to fetch the number of employees in each department.
Only include department with more than 2 employees */

SELECT Department, count(EmpID) As NoOfEmploees 
FROM EmployeeDetail
GROUP BY Department
HAVING count(EmpID)>2
ORDER BY count(EmpID) DESC

--Write Down the query to fetch Project name assign more than one employee

SELECT ProjectName, count(*) As NoOfEmployees 
FROM ProjectDetail
GROUP BY ProjectName
HAVING count(*)>1

--Write Down the query to fetch Employee name(FirstName) assign more than one Project.

SELECT ed.FirstName,Count(ProjectName) NoOfProjects FROM EmployeeDetail ed
INNER JOIN ProjectDetail pd
ON ed.EmpId=pd.EmpId
GROUP BY FirstName
HAVING count(*)>1

/*Write Down the query to fetch if the Employees 'Frank' or 'Tom' have assigned with 
more than 2 Projects*/

SELECT ed.FirstName,Count(ProjectName) NoOfProjects FROM EmployeeDetail ed
INNER JOIN ProjectDetail pd
ON ed.EmpId=pd.EmpId
WHERE ed.FirstName='Frank' OR ed.FirstName='Tom'
GROUP BY FirstName
HAVING count(*)>1

Please refer below YouTube video on HAVING Clause:

How to find 2nd Highest Salary | Find Nth Salary | 4 ways to get it

 In This tutorial we will discuss about how to get 2nd highest salary in employee table. Below are the different ways:

EmployeeDetail Table:


Using sub-query (With < Operator):

First, we find the employee with highest salary. To do this we run this query:

Select max(Salary) from EmployeeDetail

This will give us the Maximum Salary; we can further nest this query to a subquery to find the Second Highest Salary. This query will work on MYSQL, ORACLE as well as SQL Server :

Using sub-query (With Not In Operator): 
We can also use the NOT IN Clause instead of comparing the salary between two sets.

Using correlated sub-query:

The distinct keyword is there to deal with duplicate salaries in the table. In order to find the Nth highest salary, we are only considering unique salaries. The highest salary means no salary is higher than it, the Second highest means only one salary is higher than it, 3rd highest means two salaries are higher than it, similarly Nth highest salary means N-1 salaries are higher than it.

For each record processed by outer query, inner query will be executed and will return how many records has records has salary less than the current salary. If you are looking for second highest salary then your query will stop as soon as inner query will return 2.

Using TOP Clause keyword:

We can use TOP Command in SQL Server. The sub-query produces a result set containing the salary of employees arranged in decreasing order and fetches TOP 2 records from the set; we use DISTINCT (can be ignored) to segregate duplicate values (if any). Then from RESULT of sub-query, we order the two rows in Ascending order the get the Topmost row which gives us the 2nd Highest Salary.


Please find below all the queries as per above method:

Using sub-query (With < Operator):

2nd Highest Salary
Select max(Salary) As Salary from EmployeeDetail where 
Salary < (Select max(Salary) from EmployeeDetail

3rd Highest Salary
Select max(Salary) As Salary from EmployeeDetail where 
Salary < (Select max(Salary) from EmployeeDetail Where
Salary < (Select max(Salary) from EmployeeDetail))

Using sub-query (With Not In Operator)
Select max(Salary) As Salary from EmployeeDetail where 
Salary NOT IN (Select max(Salary) from EmployeeDetail)


Using correlated sub-query
Select distinct Salary from EmployeeDetail A
Where 2=(Select count(distinct salary) from EmployeeDetail B where
A.Salary <= b.Salary)


Using TOP Clause keyword
Select top 1 Salary From
(Select distinct top 2 Salary from 
EmployeeDetail Order by Salary Desc)
As Temp Order by Salary 

For detail explanation please watch below YouTube video:

SQL Aggregate Functions and Group By Clause

 In this post we will discuss below points:

  • SQL Aggregate Functions
  • Explanation of each with Example
  • Group By Clause
  • 10 SQL Queries

Aggregate Function

SQL aggregation function is used to perform the calculations on multiple rows of a single column of a table. It returns a single value. 

A
Count()

COUNT function is used to Count the number of rows in a database table. It can work on both numeric and non-numeric data types.

Count(*): Returns total number of records . Count(salary): Return number of Non Null values over the column salary.

Sum()

Sum function is used to calculate the sum of all selected columns. It works on numeric fields only.
Example: Select SUM(Salary) from EmployeeDetail.

Avg()

The AVG function is used to calculate the average value of the numeric type. AVG function returns the average of all non-Null values.
Example: Select AVG(Salary) from EmployeeDetail.

Max()

MAX function is used to find the maximum value of a certain column. This function determines the largest value of all selected values of a column.
Example: Select MAX(Salary) from EmployeeDetail.

Max()

MAX function is used to find the maximum value of a certain column. This function determines the largest value of all selected values of a column.
Example: Select MAX(Salary) from EmployeeDetail.

Min()

MIN function is used to find the minimum value of a certain column. This function determines the smallest value of all selected values of a column.
Example: Select MIN(Salary) from EmployeeDetail.

Group By Keyword

The GROUP BY clause is a SQL command that is used to group rows that have the same values. The GROUP BY clause is used in the SELECT statement.  
The GROUP BY statement is often used with aggregate functions ( COUNT() , MAX() , MIN() , SUM() , AVG() ) to group the result-set by one or more columns.
Example: SELECT column1, column2
      FROM table_name
      WHERE [ conditions ]
      GROUP BY column1, column2
      ORDER BY column1, column2

Sample Queries:
Get the highest "Salary" from EmployeeDetail table. 
SELECT MAX(Salary) As MaxSalary FROM [EmployeeDetail]

Get the lowest "Salary" from EmployeeDetail table.
SELECT MIN(Salary) As MinSalary FROM [EmployeeDetail]
 
Get how many employee exist in "EmployeeDetail" table.
SELECT COUNT(*) FROM [EmployeeDetail]

Write the query to get the department and department wise total(sum) salary from "EmployeeDetail" table. 
SELECT Department, SUM(Salary) AS [Total Salary] FROM [EmployeeDetail] GROUP BY Department

Write the query to get the department and department wise total(sum) salary, display it in ascending order according to salary.
SELECT Department, SUM(Salary) AS [Total Salary] FROM [EmployeeDetail] GROUP BY Department ORDER BY SUM(Salary) ASC

Write the query to get the department and department wise total(sum) salary, display it in descending order according to salary.
SELECT Department, SUM(Salary) AS [Total Salary] FROM [EmployeeDetail] GROUP BY Department ORDER BY SUM(Salary) DESC
 
Write the query to get the department, total no. of emp in each department, total(sum) salary with respect to department from "EmployeeDetail" table. 
SELECT Department, COUNT(*) AS [Dept Counts], SUM(Salary) AS [Total Salary] FROM [EmployeeDetail] GROUP BY Department

Get department wise average salary from "EmployeeDetail" table order by salary ascending.
SELECT Department, AVG(Salary) AS [Average Salary] FROM [EmployeeDetail] GROUP BY Department ORDER BY AVG(Salary) ASC

Get department wise maximum salary from "EmployeeDetail" table order by salary ascending.
SELECT Department, MAX(Salary) AS [Average Salary] FROM [EmployeeDetail] GROUP BY Department ORDER BY MAX(Salary) ASC
   
Get department wise minimum salary from "EmployeeDetail" table order by salary ascending.
SELECT Department, MIN(Salary) AS [Average Salary] FROM [EmployeeDetail] GROUP BY Department ORDER BY MIN(Salary) ASC

Please refer below YouTube video for more detail explanation:

SQL Queries on SQL Like, SQL Wildcards, RTRIM() and LTRIM() functions

  • Select all employee detail with First name "Tom" and "Lucy"
Answer: Select * from EmployeeDetail Where FirstName IN ('Tom','Lucy') 
  • Select all employee detail with First name Not  "Frank" and "Jane” 
Answer: Select * from EmployeeDetail Where FirstName NOT IN ('Frank','Jane')
  • Select first name from "EmployeeDetail" table after removing white spaces from right side 
Answer: Select RTRIM(FirstName) AS [First Name] From EmployeeDetail 
  • Select first name from "EmployeeDetail" table after removing white spaces from left side 
Answer: Select LTRIM(FirstName) AS [First Name] From EmployeeDetail
Where EmpId=13 
  • Select first name from "EmployeeDetail" table prefixed with "Hi
Answer: Select 'Hi '+FirstName from EmployeeDetail 
  • Get employee details from "EmployeeDetail" table whose Salary greater than 70000 
Answer: Select * from EmployeeDetail where Salary>70000
  • Get employee details from "EmployeeDetail" table whose Salary less than 70000 
Answer: Select * from EmployeeDetail where Salary<70000
  • Get employee details from "EmployeeDetail" table whose Salary between 50000 than 60000
Answer: Select * from EmployeeDetail Where Salary Between 40000 AND 60000

Please refer below YouTube video for more detail explanation:

Some More SQL Queries on Wildcard, Distinct etc

  •  Get all employee detail from Employee table whose "FirstName" not start with any single character between 'a-p' 
Answer:  SELECT * FROM [Employee] WHERE FirstName like '[^a-p]%'
  • Get all employee detail from Employee table whose "Gender" end with 'le' and contain 4 letters. 

Answer:  --The Underscore(_) Wildcard Character represents any single character. 

SELECT * FROM [EmployeeDetail] WHERE Gender like '__le' --there are two "_"

  • Get all employee detail from Employee table whose "FirstName" start with 'F' and contain 5 letters. 
Answer: SELECT * FROM [EmployeeDetail] WHERE FirstName like 'F____' --there are four “_”
  • Get all employee detail from Employee table whose "FirstName" containing '%'. ex:-"Fra%nk".
Answer: SELECT * FROM [EmployeeDetail] WHERE FirstName like '%[%]%' 
--According to our table it would return 0 rows, because no name containg '%

DISTINCT keyword - The SELECT DISTINCT statement is used to return only distinct (different) values.
The SQL DISTINCT keyword is used in conjunction with the SELECT statement to eliminate all the duplicate records and fetching only unique records.

  • Get all unique "Department" from Employee table. 

Answer: SELECT DISTINCT(Department) FROM EmployeeDetail

Please refer below YouTube video for more detail explanation:

SQL Order By, Where Clause, Like Operator and SQL Wildcards

 In this post we will discuss about:

  • SQL Order By
  • Where Clause
  • SQL Like
  • SQL Wildcards
  • Problems and write SQL queries

Order By 

Order By - The ORDER BY keyword is used to sort the result-set in ascending or descending order. The ORDER BY keyword sorts the records in ascending order by default.

Where Clause

The WHERE clause is used to filter records. It is used to extract only those records that fulfill a specified condition. SQL requires single quotes around text values. However, numeric fields should not be enclosed in quotes.
The following operators can be used in the WHERE clause: =, >, <, >=, <=, <>, BETWEEN, LIKE, IN 

SQL Wildcards

A wildcard character is used to substitute one or more characters in a string.
Wildcard characters are used with the LIKE operator. 

SQL Like 

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
%  - Represents zero or more characters
Example: Hi% finds Hitendra, Hit, Hi
_   -Represents a single character

Example: V_rma find Verma, varma

[] - Represents any single character within the brackets

Example: h[oa]t finds hot and hat, but not hit
^  - Represents any character not in the brackets

h[^oa]t finds hit, but not hot and hat
- Represents a range of characters

Example: c[a-b]t finds cat and cbt

Queries to Solve
  • List the employees in the asc order of their Salaries? 
Answer: Select * from EmployeeDetail Order By Salary;
  • List the employees in the desc order of their Salaries? 
AnswerSelect * from EmployeeDetail Order By Salary DESC;
  • List the details of the emps in asc order of the FirstName and desc of Department? 
Answer: SELECT * from EmployeeDetail Order BY Firstname ASC, Department DESC;
  • Select employee detail whose name is "Jane" 
Answer: Select * from EmployeeDetail where FirstName='Jane'
  • Get all employee detail from Employee table whose "FirstName" start with letter 'r'. 
Answer: Select * from EmployeeDetail where FirstName like 'R%'
  • Get all employee details from Employee table whose "FirstName" contains 'a' 
Answer: Select * from EmployeeDetail where FirstName like '%a%'
  • Get all employee details from EmployeeDetail table whose "FirstName" end with 'm' 
Answer: Select * from EmployeeDetail where FirstName like '%m'
  • Get all employee detail from EmployeeDetail table whose "FirstName" start with any single character between 'a-p' 
Answer: Select * from EmployeeDetail where FirstName like '[a-p]%'

Please refer below YouTube video for more detail explanation:

SQL Part 3: Create new table, Alias, Merge columns

Query to Create EmployeeDetail table:

CREATE TABLE EmployeeDetail

(

EmpId integer PRIMARY KEY, 

FirstName varchar(255),

LastName varchar(255),

Salary integer,

JoiningDate date, 

Department varchar(255),

Gender varchar(255) 

);

INSERT INTO EmployeeDetail VALUES(1,'Tom','Garcia',60000.00,'2008-11-10 13:23:44','IT','Male');

INSERT INTO EmployeeDetail VALUES(2,'Lucy','Martin',70000.00,'2009-11-09 13:23:44','HR','FeMale');

INSERT INTO EmployeeDetail VALUES(3,'Frank','Clark',75000.00,'2012-01-24 13:23:44','Admin','Male');

INSERT INTO EmployeeDetail VALUES(4,'Jane','Joseph',78000.00,'2017-01-28 13:23:44','ICT','FeMale');

INSERT INTO EmployeeDetail VALUES(5,'Robert','Hansen',100000.00,'2012-01-29 13:23:44','HR','Male');

Query to Create ProjectDetail table:

CREATE TABLE ProjectDetail

(

ProjectDetailId integer PRIMARY KEY, 

EmpId integer REFERENCES EmployeeDetail(EmpId), 

ProjectName varchar(255) 

);

INSERT INTO ProjectDetail VALUES(1,1,'ABC');

INSERT INTO ProjectDetail VALUES(2,1,'DDC');

INSERT INTO ProjectDetail VALUES(3,1,'GHI');

INSERT INTO ProjectDetail VALUES(4,2,'HGT');

INSERT INTO ProjectDetail VALUES(5,3,'ABC');

INSERT INTO ProjectDetail VALUES(6,4,'GHI');

INSERT INTO ProjectDetail VALUES(7,3,'XYZ');

INSERT INTO ProjectDetail VALUES(8,5,'DDC');

Alias: Aliases are often used to make column names more readable

Basic SQL Queries for Practice:

1. Write query to get all employee detail from "EmployeeDetail" table:

ANS: SELECT * FROM EmployeeDetail

2. Write query to get only "FirstName" column from " EmployeeDetail " table

ANS: SELECT FirstName FROM EmployeeDetail

3. Write query to get FirstName in upper case as "First Name".
ANS: SELECT UPPER(FirstName) AS [First Name] FROM EmployeeDetail

4. Write query to get FirstName in lower case as "First Name".
ANS: SELECT LOWER(FirstName) AS [First Name] FROM EmployeeDetail

5. Write query for combine FirstName and LastName and display it as "Name" (also include white space between first name & last name).
ANS: 
1st Way - SELECT CONCAT(FirstName+' ',LastName) AS Name FROM EmployeeDetail
2nd Way - SELECT FirstName+' '+LastName AS Name FROM EmployeeDetail

Below are some very good websites to Practice SQL Queries online:

SQL Part 2: Data types, Operators and Constraints

 SQL Datatypes:

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 0 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 = CHAR is fixed lengt. Maximum length of 8,000 characters

varchar = VARCHAR is variable length. Maximum of 8,000 characters

varchar(max) = Maximum length of 231 characters

char vs varchar

CHAR takes up 1 byte per character. So, a CHAR(100) field (or variable) takes up 100 bytes on disk, regardless of the string it holds. VARCHAR is a variable length string data type, so it holds only the characters you assign to it. VARCHAR takes up 1 byte per character, + 2 bytes to hold length information.

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:

There are are three types of Operators.

  • Arithmetic Operators:
  • Comparison Operators
  • Logical Operators

Arithmetic operators

Operator Description

+ 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 gets satisfied then “True” Boolean value is returned.

Operator Description

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

Logical Operators

Operator Description

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.

SQL Constraints:

  • Constraints are the rules enforced on data columns on table. 
  • These are used to limit the type of data that can go into a table. 
  • This ensures the accuracy and reliability of the data in the database. 
  • Constraints could be column level or table level. 
  • Column level constraints are applied only to one column, 
  • whereas table level constraints are applied to the whole table. 

Following are commonly used constraints available in SQL: 

1. NOT NULL Constraint: Ensures that a column cannot have NULL value. 

Example:

CREATE TABLE recipes (

  recipe_id INT NOT NULL,

  recipe_name VARCHAR(30) NOT NULL,

  PRIMARY KEY (recipe_id),

  UNIQUE (recipe_name)

);

2. DEFAULT Constraint: Provides a default value for a column when none is specified. 

3. UNIQUE Constraint: Ensures that all values in a column are different. 

4. PRIMARY Key: Uniquely identified each rows/records in a database table. 

UNIQUE KEY vs PRIMARY KEY

  • The UNIQUE constraint ensures that all values in a column are different.
  • Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns.
  • A PRIMARY KEY constraint automatically has a UNIQUE constraint.
  • However, you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.
  • The primary key column cannot have null values while the Unique Key column can have one null value.

5. FOREIGN Key: Uniquely identified a rows/records in any another database table. 

  • A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table. The table with the foreign key is called the child table, and the table with the primary key is called the referenced or parent table.
  • Foreign key is used to maintain relationship between two tables.
  • Foreign Key in a table doesn't have to be unique in said table. BUT, as it is the Primary Key of another table, it must be unique in this table.
  • Foreign Key can be NULL or duplicate
Example:

Table1 (Parent Table):

CREATE TABLE EmployeeDetail(EmpId integer PRIMARY KEY, FirstName varchar(255),LastName varchar(255),Salary integer,JoiningDate date, Department varchar(255), Gender varchar(255) );

Table 2 (Child Table):

CREATE TABLE ProjectDetail(ProjectDetailId integer PRIMARY KEY, EmpId integer REFERENCES EmployeeDetail(EmpId), ProjectName varchar(255) );

6. CHECK Constraint: The CHECK constraint ensures that all values in a column satisfy certain conditions. 

SQL constraints are used to specify rules for the data in a table. Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the table. If there is any violation between the constraint and the data action, the action is aborted.

Syntax: ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK (column_name condition);

7. INDEX: Use to create and retrieve data from the database very quickly.

A SQL index is used to retrieve data from a database very fast. Indexing a table or view is, without a doubt, one of the best ways to improve the performance of queries and applications. 

Syntax: CREATE INDEX <indexName>

ON <TableName>(LastName);

Please refer below YouTube video for more detail explanation:

SQL Part 1: DataBase Concept

 In This section we are going to cover below topics:

  • SQL For Testers
  • What is Database?
  • What is DBMS?
  • What is RDBMS?
  • What is SQL?
  • SQL Command Types
  • Most Important SQL Commands

Why SQL needed for Testers
  • The ability to recognize the different types of databases, The ability to connect to the database using different SQL connection clients, Understanding of the relationship between database tables, keys, and indices. Ability to write a simple select or SQL statement along with more complex join queries.
  • 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. 
  • We will start with the database fundamentals. At the same time, we will move to the SQL concepts. Finally, we’ll check some of the widely used SQL commands
What is a Database?
  • A database is an organized and systematic collection of data that is stored and accessed in/from a computer system. A database is usually controlled by a database management system (DBMS).
  • For example, a company database may include tables for products, employees, and financial records. Each of these tables would have different fields that are relevant to the information stored in the table.
What is DBMS?
  • DBMS (Database Management System) 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.
  • Some DBMS examples include MySQL, PostgreSQL, Microsoft Access, SQL Server, FileMaker, Oracle, RDBMS, dBASE, Clipper, and FoxPro
What is RDBMS?
RDBMS (Relational Database Management System) is an advanced version of the basic DBMS.
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.
  • Top Relational Databases Software
  • MS SQL
  • Oracle Database
  • MySQL
  • Db2
  • PostgreSQL
  • A RDBMS database uses tables for storing the data. A table is nothing but a collection of data that is related to one another.
  • 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.
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 keywords are not case sensitive.
SQL commands are segregated into following types:
  • DDL – Data Definition Language – 
SQL commands come under DDL are as follows: CREATE, ALTER, DROP, TRUNCATE
  • DML – Data Manipulation Language
SQL Commands come under DML are as follows: INSERT, UPDATE, DELETE
  • DQL – Data Query Language
SQL Commands come under DQL are as follows: SELECT
  • DCL – Data Control Language
SQL Commands come under DCL are as follows: GRANT, REVOKE, DENY
  • TCL – Transaction Control Language
SQL Commands come under TCL are as follows: COMMIT, ROLEBACK, SAVE

Some frequently used keywords/commands:
  • SELECT – It extracts data from a DataBase
  • INSERT INTO – It inserts new data into a DataBase
  • CREATE – It creates a new DataBase/Table/Index
  • UPDATE – It updates data in a DataBase
  • ALTER – It modifies a DataBase/Table
The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.
The ALTER TABLE statement is also used to add and drop various constraints on an existing table.

Syntax: ALTER TABLE table_name
ADD column_name datatype;
  • TRUNCATE -  Truncate can be used to delete the entire data of the table. Table structure remain same.
Syntax: TRUNCATE TABLE table_name
  • DELETE – Delete statement can be used for deleting the specific data.
Syntax/Example:
Delete from  <table>;
Delete from  <table> where ID=3;
  • DROP – It deletes a table/Index.
Syntax: DROP TABLE table_name

Please refer below YouTube videos in detail:

Data Driven Testing in Cucumber

What is Cucumber data driven testing?

In very simple terms, cucumber data driven testing means that you can pass data from cucumber feature files to your test cases. For example, let us consider that you are automating the login flow of some application. In this case, you can pass user name and password as test data from your feature file to selenium code.

Data-Driven Testing in Cucumber

1. Parameterization without Example Keyword:
Parameterization without Example Keyword
Using Regular Expression: 
("^user enters \"(.*)\" and \"(.*)\"$")
("^user enters \"([^\"]*)\" and \"([^\"]*)\"$")

2. Data-Driven Testing in Cucumber using Scenario Outline
Parameterization with Example Keyword

Parameterization with Examples Keyword
       Using Regular Expression: 
(“user enters (.*) and (.*)$")

Scenario Outline – This is used to run the same scenario for 2 or more different sets of test data. E.g. In our scenario, if you want to register another user you can data drive the same scenario twice.

Examples – All scenario outlines have to be followed with the Examples section. This contains the data that has to be passed on to the scenario.

Using Scenario Outline
Cucumber inherently supports data driven testing using Scenario Outline. Consider the following feature file using Scenario to define the test steps-

 Feature: Check addition in Google calculator
   In order to verify that google calculator work correctly
   As a user of google
   I should be able to get correct addition result

   Scenario: Addition
   Given I open google
   When I enter "2+2" in search textbox
   Then I should get result as "4"
In order to make it data driven we just have to use Scenario Outline along with Examples. Write the following code in feature file-

   Feature: Check addition in Google calculator
   In order to verify that google calculator work correctly
   As a user of google
   I should be able to get correct addition result

   Scenario Outline: Addition
   Given I open google
   When I enter "<calculation>" in search textbox
   Then I should get result as "<result>"
   
   Examples:
| calculation |result|
| 3+3      | 6 |
| 2+5      | 9 |
Please refer below Youtube video for detail explanation:


What is Data table in Cucumber?

Data Tables are handy for passing a list of values to a step definition. Cucumber provides a rich API for manipulating tables from within step definitions.
Data Tables are also used to handle large amount of data. Tables in Cucumber feature files are represented by using the pipeline “|” sign.
Please refer below Youtube video for detail explanation:



Behaviour Driven Development (BDD) Framework Setup

 Steps to be followed to setup BDD Framework:

1. Create a Maven Project

2. Add dependencies - Selenium java, webdrivermanager, cucumber-java, junit, cucumber-junit 

3. Create Features folder in src/test/resources

4. Create a new feature file. extension is .feature

5. Install Cucumber Eclipse plug-in or natural plug-in and Restart the eclipse.

6. Create a new feature file to get all the options

7. Write Scenarios in feature file

8. Run the feature file

9. Create step definition class or glue code under src/test/java package

10. Create a runner package/class

@RunWith(Cucumber.class) 

@CucumberOptions(features="src/test/resources/Features",glue={"com.StepDefinition"}

Please refer below YouTube video:


What is Cucumber Options? 
@CucumberOptions are like property files or settings for your test. Basically @CucumberOptions enables us to do all the things that we could have done if we have used cucumber command line.

Different Cucumber Options:


Please refer below YouTube video: