Advanced SQL Queries For Practice With Solution
In the previous article “SQL Queries for Practice with Solution“, we learned how to write basic queries. Now in this article, we will learn more Advanced SQL Queries with practical examples. Before we start learning advanced SQL queries, create the following tables and populate tables with data from Sample Database For Demos.
SELECT rows from tblEmployee and tblGender with Gender name using Correlated Sub Query
In the below query, we will get rows from tblEmployee and tblGender tables with Gender instead of EmpGenderID using Correlated Sub Query.
USE HRDB GO SELECT EmpID, EmpUserName, ( SELECT Gender FROM tblGender WHERE tblGender.GenderID=tblEmployee.EmpGenderID ) AS Gender, EmpAge, EmpSalary, EmpJoiningDate FROM tblEmployee GO
Result

SELECT rows from tblEmployee using Non-Correlated Sub Query Where EmpDeptID exists in tblDepartment table
In the below query, we will get rows from tblEmployee where Department ID exists in tblDepartment table with DeptID equal to 2 using Correlated Sub Query.
USE HRDB GO SELECT * FROM tblEmployee WHERE EmpDeptID IN ( SELECT DeptID FROM tblDepartment WHERE DeptID=2 ) GO
Result

Joins Queries in SQL Server
To understand joins clearly I will insert few more rows in tblEmployee table having EmpDeptID column NULL values.
USE HRDB GO INSERT INTO tblEmployee (EmpFirstName, EmpLastName, EmpUserName, EmpGenderID, EmpAge, EmpSalary, EmpJoiningDate) VALUES ('Callahan','Singh','Callahan Singh',1,25,45000,'2013-02-01'), ('Steven','Margaret','Steven Margaret',1,33,65000,'2018-06-01'), ('Dodsworth','Anne','Dodsworth Anne',1,35,59000,'2019-07-01') GO
SELECT rows using inner join
In inner join, we will get all rows from tblEmployee and tblDepartment table using inner join based on the common columns EmpDeptID from tblEmployee table and DeptID column from tblDerpartment table.
USE HRDB GO SELECT e.EmpID, e.EmpUserName, d.DeptName, e.EmpSalary, e.EmpJoiningDate FROM tblEmployee AS e INNER JOIN tblDepartment AS d ON e.EmpDeptID=d.DeptID GO
Result

SELECT rows using left inner join/left join
In left inner join, we will get all the matching and non-matching rows from the left table tblEmployee and all the matching rows from right table tblDepartment.
USE HRDB GO SELECT e.EmpID, e.EmpUserName, d.DeptName, e.EmpSalary, e.EmpJoiningDate FROM tblEmployee AS e LEFT OUTER JOIN tblDepartment AS d ON e.EmpDeptID=d.DeptID GO
Result

SELECT rows using right inner join/right join
In right outer join, we will get all the matching rows from the left table tblEmployee and all the matching and non-matching rows from the right table tblDepartment.
USE HRDB GO SELECT e.EmpID, e.EmpUserName, d.DeptName, e.EmpSalary, e.EmpJoiningDate FROM tblEmployee AS e RIGHT OUTER JOIN tblDepartment AS d ON e.EmpDeptID=d.DeptID GO
Result

SELECT rows using full join
In full join, we will get all the matching and non-matching rows from the left table tblEmployee as well as all the matching and non-matching rows from the right table tblDepartment.
USE HRDB GO SELECT e.EmpID, e.EmpUserName, d.DeptName, e.EmpSalary, e.EmpJoiningDate FROM tblEmployee AS e FULL JOIN tblDepartment AS d ON e.EmpDeptID=d.DeptID GO
Result

SELECT rows using cross join
In cross join, we will get the result by Cartesian Product. i.e. the numbers of rows of first table tblEmployee multiply by the number of rows of the second table tblDepartment. Since we have 13 rows in tblEmployee table and 4 rows in tblDepartment table So, we will get 52 rows.
USE HRDB GO SELECT e.EmpID, e.EmpUserName, d.DeptName, e.EmpSalary, e.EmpJoiningDate FROM tblEmployee AS e CROSS JOIN tblDepartment AS d GO
Result

Stored Procedures in SQL
Stored Procedure without Parameter
Here is the stored procedure with simple select statement to get DeptID and DeptName from tblDepartment table.
USE HRDB GO CREATE PROCEDURE usp_GetAllDepartments AS BEGIN SELECT DeptID, DeptName FROM tblDepartment END GO
Result

Stored Procedure with one Parameter
Here is the stored procedure with simple select statement to get DeptID and DeptName from tblDepartment table having input parameter @DeptID.
USE HRDB GO CREATE PROCEDURE usp_GetDepartmentByID @DeptID INT AS BEGIN SELECT DeptID, DeptName FROM tblDepartment WHERE DeptID=@DeptID END GO
Result

Stored Procedure with multiple Parameters
Here is the stored procedure with select statement with multiple parameters @DeptID and @DeptName.
USE HRDB GO CREATE PROCEDURE usp_GetEmployeesByDeptAndGender @DeptID INT, @GenderID INT AS BEGIN SELECT EmpID, EmpUserName, (SELECT DeptName FROM tblDepartment AS d WHERE d.DeptID=e.EmpDeptID) AS Department, (SELECT Gender FROM tblGender AS g WHERE g.GenderID=e.EmpGenderID) AS Gender, EmpSalary, EmpJoiningDate FROM tblEmployee AS e WHERE EmpDeptID=@DeptID AND EmpGenderID=@GenderID END GO
Result

Stored Procedure with delete statement
Here is the stored procedure with delete statement with one parameter @EmpID.
USE HRDB GO CREATE PROCEDURE usp_DeleteByEmpID @EmpID INT AS BEGIN DELETE FROM tblEmployee WHERE EmpID=@EmpID END GO
Result

Stored Procedure with update statement for updating single field
Here is the stored procedure with update statement with two parameters where @EmpSalary is used to update the salary and @EmpID is used in where cluase to decide which employee’s salary will be updated.
USE HRDB GO CREATE PROCEDURE usp_DeleteByEmpID @EmpID INT, @EmpSalary INT AS BEGIN UPDATE tblEmployee SET EmpSalary=@EmpSalary WHERE EmpID=@EmpID END GO
Result

Stored Procedure with update statement for updating multiple fields
Here is the stored procedure with update statement with three parameters where we update EmpFirstName and EmpLastName based on the provided EmpID. It will automatically update the third column value with EmpFirstName and EmpLastName and a space between them.
USE HRDB GO CREATE PROCEDURE usp_DeleteByEmpID @EmpID INT, @EmpFirstName VARCHAR(20), @EmpLastName VARCHAR(20) AS BEGIN UPDATE tblEmployee SET EmpFirstName=@EmpFirstName, EmpLastName=@EmpLastName, EmpUserName=@EmpFirstName + ' ' + @EmpLastName WHERE EmpID=@EmpID END GO
Result

Stored Procedure to insert data into a table
In the following stored procedure, we will populate tblEmployee table with data.
USE HRDB GO CREATE PROCEDURE usp_InsertNewEmployee @EmpFirstName VARCHAR(20), @EmpLastName VARCHAR(20), @EmpUserName VARCHAR(40), @EmpGenderID INT, @EmpDeptID INT, @EmpAge INT, @EmpSalary INT, @EmpJoiningDate DATETIME AS BEGIN INSERT INTO tblEmployee VALUES ( @EmpFirstName, @EmpLastName, @EmpUserName, @EmpGenderID, @EmpDeptID, @EmpAge, @EmpSalary, @EmpJoiningDate ) END GO
EXECUTE usp_InsertNewEmployee 'Theo','Jackob','Theo Jackob',1,3,45,90000,'2020-01-01'
Result

Create a simple View
As we know, a VIEW is like a virtual table, so we can get all or some rows based on specific condition.
USE HRDB GO CREATE VIEW VW_GetAllEmployees AS SELECT * FROM tblEmployee GO
In the following query we will get all the data from a view VW_GetAllEmployees.
SELECT * FROM VW_GetAllEmployees
Result

In the following query we will filter the rows where EmpID is between 1 and 5 VW_GetAllEmployees.
SELECT * FROM VW_GetAllEmployees WHERE EMPID BETWEEN 1 AND 5

In the following query we will select the rows from view VW_GetAllEmployees with selected columns.
SELECT EmpID, EmpUserName, EmpAge, EmpSalary, EmpJoiningDate FROM VW_GetAllEmployees

Create a View with selected columns
We can create a view with selected columns as show below.
USE HRDB GO CREATE VIEW VW_GetEmpBySpecificColumns AS SELECT EmpID, EmpUserName, EmpGenderID, EmpDeptID, EmpSalary, EmpAge, EmpJoiningDate FROM tblEmployee GO
In the following query we will get all the data from a view VW_GetEmpBySpecificColumns.
SELECT * FROM VW_GetEmpBySpecificColumns
Result

Create a View with filtered data
We can create a view with filtered rows with a where clause.
USE HRDB GO CREATE VIEW VW_GetMaleEmpolyees AS SELECT EmpID, EmpUserName, EmpGenderID, EmpDeptID, EmpSalary, EmpAge, EmpJoiningDate FROM tblEmployee WHERE EmpGenderID=1 GO
In the following query we will get all the data from a view VW_GetMaleEmpolyees.
SELECT * FROM VW_GetMaleEmpolyees
Result

Create a View with joins
We can create a view using joins.
USE HRDB GO CREATE VIEW VW_GetEmpNameAndDeptUsingJoins AS SELECT e.EmpID, e.EmpUserName, g.Gender, d.DeptName, e.EmpSalary, e.EmpAge, e.EmpJoiningDate FROM tblEmployee e INNER JOIN tblGender g ON e.EmpGenderID = g.GenderID INNER JOIN tblDepartment d ON e.EmpDeptID = d.DeptID GO
In the following query we will get all the data from a view VW_GetEmpNameAndDeptUsingJoins.
SELECT * FROM VW_GetEmpNameAndDeptUsingJoins
Result

ROW_NUMBER() function to assign row number for every record
In the following query we will get a unique row number for every record in the select list using row_number() function. In the below query, first it will sort the rows by EmpUserName in ascending order and assign the unique row number for every record. You can also sort the rows in descending order and assign the row number.
USE HRDB GO SELECT EmpUserName, EmpGenderID, EmpSalary, EmpJoiningDate, ROW_NUMBER() OVER(ORDER BY EmpUserName ASC) AS RowNumber FROM tblEmployee GO
Result

ROW_NUMBER() function with order by and partition by clause
In the following query we will get a unique row number for every partition in the select list. When the partition changes the row number assigned with 1 and increment it by 1.
USE HRDB GO SELECT EmpUserName, EmpGenderID, EmpSalary, EmpJoiningDate, ROW_NUMBER() OVER(PARTITION BY EmpGenderID ORDER BY EmpUserName ASC) AS RowNumberByPartition FROM tblEmployee GO
Result

ROW_NUMBER() function with order by and multiple partition by clause
In the following query we will get a unique row number for every partition in the select list. When the partition changes the row number assigned with 1 and increment it by 1. In this query we used multiple partitions.
USE HRDB GO SELECT EmpUserName, EmpGenderID, EmpDeptID, EmpSalary, EmpJoiningDate, ROW_NUMBER() OVER(PARTITION BY EmpGenderID,EmpDeptID ORDER BY EmpUserName ASC) AS RowNumberByMultiplePartition FROM tblEmployee GO
Result

DENSE_RANK() function with only order by clause
DENSE_RANK() Function is also used to assign rank for every record in the SELECT statement. The only difference between ROW_NUMBER() and DENSE_RANK() function is that ROw_NUMBER() function assign unique number for every record, while in case of DENSE_RANK() function assign the same rank if the order by column has same value. In below the example, DENSE_RANK() function assigned the unique rank from 1 to 4 for EmpSalary 25000, 30000, 35000, and 40000, while for EmpSalary 50000 DENSE_RANK() function will assign rank as 5.
USE HRDB GO SELECT EmpUserName, EmpGenderID, EmpDeptID, EmpSalary, EmpJoiningDate, DENSE_RANK() OVER(ORDER BY EmpSalary ASC) AS DenseRankByEmpSalary FROM tblEmployee GO
Result

DENSE_RANK() with order by and partition by single column
In the below query, DENSE_RANK() function will assign a unique rank according to partition by EmpGenderID and order by EmpSalary column. So, for EmpGenderID 1 and EmpSalary 25000 and 40000 the rank is assigned 1 and 2, EmpGenderID 1 and EmpSalary 50000 the rank is assigned as 3, for EmpGenderID 1 and EmpSalary 75000 the rank is assigned as 4, for EmpGenderID 2 and EmpSalary 30000,35000 the rank is assigned 1 and 2, and for EmpGenderID 2 and EmpSalary 50000 the rank is assigned 3 for both rows.
USE HRDB GO SELECT EmpUserName, EmpGenderID, EmpDeptID, EmpSalary, EmpJoiningDate, DENSE_RANK() OVER(PARTITION BY EmpGenderID ORDER BY EmpSalary ASC) AS DenseRankByGender FROM tblEmployee GO
Result

DENSE_RANK() with order by and partition by multiple columns
USE HRDB GO SELECT EmpUserName, EmpGenderID, EmpDeptID, EmpSalary, EmpJoiningDate, DENSE_RANK() OVER(PARTITION BY EmpGenderID, EmpDeptID ORDER BY EmpSalary ASC) AS DenseRankByGenderAndDeptID FROM tblEmployee GO
Result

Recommended Readings
- Advanced SQL Queries For Practice With Solution
- SQL Queries For Practice With Solution
- SQL Interview Questions and Answers
- STORED PROCEDURE in SQL Server
- How To Join Tables Data in SQL Server
- How to use Transaction in SQL Stored Procedure
- Difference Between IN and NOT IN Operators in SQL
- How To Modify Date in SQL Using DATEADD
- How To Get Year From Date in SQL Server
- How To Get Month From Date in SQL Server
- How To Get Day From Date in SQL Server
- How To Use ROW_NUMBER Function in SQL
- Date and Time Functions in SQL Server
- How To Find Nth Highest Salary in SQL Server
- How to Backup Table Using SELECT INTO Statement
- How To Use HAVING Clause in SQL Server
- Aggregate Functions in SQL Server
- How To Group Data Using Group By in SQL Server
- How To Truncate Table in SQL Server
- How To Delete Data From Table in SQL Server
- How To Update Table Data in SQL Server
- How To Sort Data Using Order By Clause in SQL
- How To Select Distinct Records in SQL Server
- How to Filter Data From Table in SQL Server
- Round Off Values Using Ceiling and Floor in SQL
- How To Find Square Root Of A Number in SQL Server
- How To Select Data From Table in SQL Server
- How To Insert Data in SQL Server Table
- How To Add NOT NULL Constraint in SQL Server
- How To Add Check Constraint on SQL Server Table
- How To Add Default Constraint on SQL Server Table
- Unique Key Constraint in SQL Server
- How to add Foreign Key Constraint in SQL Server
- How To Add Identity To SQL Server Table Column
- How to add Primary Key Constraint in SQL Server
- How To Create Alter and Drop Table in SQL Server
- How To Create Alter and Drop Database in SQL