Scribd
Upload
4 views8 pages

Advanced SQL Tasks for Employees

The document outlines advanced SQL tasks for Week 2, including creating an Employees table, inserting sample data, and implementing various SQL features such as window functions, stored procedures for inserting and retrieving employee data, and a scalar function to calculate yearly salary. It also includes the creation of a non-clustered index on the Department column. The submission is by Ananya Upadhyay as part of the Cognizant DN4.0 DotNet FSE Track.

Uploaded by

hyperxissei
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
4 views8 pages

Advanced SQL Tasks for Employees

The document outlines advanced SQL tasks for Week 2, including creating an Employees table, inserting sample data, and implementing various SQL features such as window functions, stored procedures for inserting and retrieving employee data, and a scalar function to calculate yearly salary. It also includes the creation of a non-clustered index on the Department column. The submission is by Ananya Upadhyay as part of the Cognizant DN4.0 DotNet FSE Track.

Uploaded by

hyperxissei
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

WEEK 2 – ADVANCED SQL TASKS

0. TABLE CREATION + SAMPLE DATA


-- Week 2: Initial Setup — Create Employees Table

USE DN4_DemoDB;
GO

-- Drop old table if exists


IF OBJECT_ID('Employees', 'U') IS NOT NULL
DROP TABLE Employees;
GO

-- Create table with auto-incremented EmployeeID


CREATE TABLE Employees (
EmployeeID INT IDENTITY(1,1) PRIMARY KEY,
Name VARCHAR(100),
Department VARCHAR(50),
Salary INT
);
GO

-- Insert sample data


INSERT INTO Employees (Name, Department, Salary) VALUES
('Avi', 'HR', 50000),
('Molu', 'HR', 60000),
('Moon Ji', 'Tech', 80000),
('Guni', 'Tech',75000);Go

1. TASK 1 — WINDOW FUNCTIONS


-- Task 1: Use RANK(), DENSE_RANK(), and ROW_NUMBER()

USE DN4_DemoDB;
GO

SELECT
EmployeeID,
Name,
Department,
Salary,
RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS DeptRank,
ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS DeptRow,
DENSE_RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS DeptDense
FROM Employees;
2. TASK 2 — STORED PROCEDURE (INSERT
EMPLOYEE)
-- Task 2: Stored Procedure to insert employee data

USE DN4_DemoDB;
GO

IF OBJECT_ID('InsertEmployee', 'P') IS NOT NULL


DROP PROCEDURE InsertEmployee;
GO

CREATE PROCEDURE InsertEmployee


@Name VARCHAR(100),
@Department VARCHAR(50),
@Salary INT
AS
BEGIN
INSERT INTO Employees (Name, Department, Salary)
VALUES (@Name, @Department, @Salary);
END;
GO

-- Test Insert
EXEC InsertEmployee 'Aishwarya', 'Finance', 68000;
EXEC InsertEmployee 'Kartik', 'Tech', 77000;

-- Check Table
SELECT * FROM Employees;
3. TASK 3 — STORED PROCEDURE (RETURN
DATA BY DEPT)
-- Task 3: Stored Procedure to return employees by department

USE DN4_DemoDB;
GO

IF OBJECT_ID('GetEmployeesByDept', 'P') IS NOT NULL


DROP PROCEDURE GetEmployeesByDept;
GO

CREATE PROCEDURE GetEmployeesByDept


@Department VARCHAR(50)
AS
BEGIN
SELECT * FROM Employees
WHERE Department = @Department;
END;
GO

-- Test Procedure
EXEC GetEmployeesByDept 'Tech';

4. OPTIONAL TASK — SCALAR FUNCTION


-- Scalar Function: Calculate Yearly Salary from Monthly Salary

USE DN4_DemoDB;
GO

IF OBJECT_ID('[Link]', 'FN') IS NOT NULL


DROP FUNCTION [Link];
GO

CREATE FUNCTION [Link] (@MonthlySalary INT)


RETURNS INT
AS
BEGIN
RETURN @MonthlySalary * 12;
END;
GO

-- Test Function
SELECT [Link](50000) AS YearlySalary;

-- Use in Table
SELECT
Name,
Salary AS MonthlySalary,
[Link](Salary) AS AnnualSalary
FROM Employees;
5. OPTIONAL TASK — CREATE INDEX
-- Create non-clustered index on Department column

USE DN4_DemoDB;
GO

IF EXISTS (
SELECT name
FROM [Link]
WHERE name = 'IX_Employees_Department'
)
DROP INDEX IX_Employees_Department ON Employees;
GO

CREATE NONCLUSTERED INDEX IX_Employees_Department


ON Employees (Department);
GO

-- Check Index
EXEC sp_helpindex 'Employees';

Submission Format
Submitted by: Ananya Upadhyay
Week 2 — Advanced SQL
Cognizant DN4.0 DotNet FSE Track

You might also like