SQL Server Handson solutions cognizant

SQL Server Handson Programs Cognizant

Bymadwithcode

In this post, we are going to cover all SQL queries that are asked under SQL Server Topic in Cognizant Solutions.

Microsoft SQL Server

1. Create table with Foreign Key constraint

CREATE TABLE [orders]
(
[ORD_NO] NUMERIC(5) PRIMARY KEY,
[PURCH_AMT] DECIMAL(8, 2),
[ORD_DATE] DATE,
[CUSTOMER_ID] NUMERIC(5),
[SALESMAN_ID] NUMERIC(5),
FOREIGN KEY (SALESMAN_ID) REFERENCES salesman (SALESMAN_ID)
)

GO

2. Alter Supplier table with Check Constraint

ALTER TABLE Supplier
ADD CONSTRAINT Suplier_Contact_Length
CHECK ( LEN(conact)=10 )
GO

Subqueries and Joins

1. Worker and Admin Departments

SELECT
D.deptname [Worker Department],
D.Location,
MD.deptname [Manager Department]
FROM
Department D
JOIN Department MD
ON D.admrdept = MD.deptno AND D.deptno != D.admrdept
ORDER BY
[Worker Department]
GO

2. Employee with reporting manager

SELECT
CONCAT(E.lastname, " works for ", M.lastname) [Hierarchy]
FROM
Employee E
JOIN Department D
ON E.workdept = D.deptno
JOIN Employee M
ON D.mgrno = M.empno AND E.empno != M.empno
ORDER BY
E.lastname
GO

Functions and Stored Procedures

1. Procedure to display the Employees of a specific Department

CREATE PROC EmployeesDept(@DeptNo NVARCHAR(3))
AS
BEGIN
SELECT
lastname [Name]
FROM
Employee
WHERE
workdept=@DeptNo
END
GO

2. Procedure to display all the Departments

CREATE PROCEDURE AvailableDepartments
AS
BEGIN
SELECT
deptname [Name]
FROM
Department
END
GO

3. Employees working in New York

SELECT
E.firstname,
E.lastname,
E.salary
FROM
Employee E,
Department D
WHERE
E.workdept = D.deptno
AND D.location="New York"
ORDER BY
firstname
GO

4. Employee Count per Department

CREATE PROC EmployeeCount
@deptno NVARCHAR(20),
@total_employees INT OUT
AS
BEGIN
SELECT
@total_employees = count(*)
FROM
employee
WHERE
workdept=@deptno
END
GO

Cursors and Triggers

1. Trigger – claims

CREATE trigger claim_audits
ON claims
AFTER INSERT
AS
BEGIN
INSERT INTO claim_audit
SELECT CT.first_name, SUM(C.amount_of_claim), 'Updated customer claimed amount'
FROM claims C
JOIN customer_policy CP
ON C.customer_policy_id=CP.id
JOIN customer CT
ON CP.customer_id=CT.id
WHERE
C.customer_policy_id IN (SELECT customer_policy_id
FROM inserted)
GROUP BY CT.first_name
END
GO

2. Department records using cursors

-------- Creating Cursor-------------
DECLARE dbcursor CURSOR
FOR SELECT
deptname, COUNT(*)
FROM
Employee E,
Department D
WHERE
E.workdept=D.deptno
GROUP BY
deptname
HAVING
COUNT(workdept) > 1
ORDER BY
deptname
-------- Cursor Created-------------

DECLARE @name NVARCHAR(36)
DECLARE @count INT

OPEN dbcursor

FETCH NEXT FROM dbcursor INTO @name, @count

WHILE @@FETCH_STATUS=0
BEGIN
PRINT CONCAT(@name, ' department has ', @count, ' employees')
FETCH NEXT FROM dbcursor INTO @name, @count
END

CLOSE dbcursor
DEALLOCATE dbcursor
GO

Implementing Error handling – Exceptions / try-catch

1. Create a Procedure delete_status

CREATE PROCEDURE delete_status
@status_id INT
AS
BEGIN
IF NOT EXISTS(SELECT *
FROM
claims
WHERE
status_id=@status_id)
BEGIN
DELETE FROM [status]
WHERE id=@status_id
END
ELSE
BEGIN
INSERT INTO [status_error_log]
VALUES
('child records existing in claims table')
END
END
GO

Similar Posts