You are currently viewing Advanced SQL Queries For Practice With Solution

Advanced SQL Queries For Practice With Solution

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 WITH Correlated Sub Query

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

SELECT WITH NON CORRELATED QUERY

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

inner join in sql server

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

Left Outer Join in SQL Server

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

Right Outer Join in SQL Server

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

Full Join in SQL Server

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

Cross Joins in SQL Server

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

Procedure without parameter

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

Procedure with one parameter

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

Procedure with multiple parameters

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

Procedure with delete statement

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

Procedure with update statement

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

Procedure with update statement 2

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

Procedure to populate data

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
view with where

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
view with selected rows

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

view with specific columns

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

view with where clause

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

view with joins

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

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 by over and partition by

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

Row Number function with multiple partition by clause

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 Function

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 by gender

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

DENSE_RANK 7

Recommended Readings

Tags: , ,