Write a query to return employee  history details like LastName , FirstName , EmployeeID ,  Department and Rate.

Design Rules : 

1. Use Joins

2. Rename the column ‘Name’ from Department table as ‘Department’ in the resultset

3. Return only  employees whose FirstName starts with letter 'P'

Tables: 

Department, EmployeeDepartmentHistory, EmployeePayHistory, Employee, Contact

Note : Each employee can have more  than one department mapped in the EmployeeDepartmentHistory.

Sample Output: 

 

 SELECT 
       c.LastName , c.FirstName , emain.EmployeeID ,  d.name as Department, eph.Rate
FROM   Department d 
       INNER JOIN EmployeeDepartmentHistory edhmain 
         ON d.DepartmentID = edhmain.DepartmentID 
       INNER JOIN EmployeePayHistory eph 
         ON eph.EmployeeID = edhmain.EmployeeID 
       INNER JOIN Employee emain 
         ON edhmain.EmployeeID = emain.EmployeeID 
       INNER JOIN Contact c 
         ON emain.ContactID = c.ContactID
WHERE c.FirstName like 'P%' 

 
The EmployeePayHistory will have the rate history for each year. Write a query to fetch the number of Rate change  happened for the years 1996, 1997 and 1998. Also display the number of unique rate for the mentioned years.



Design Rules : 

1. Display Year using column RateChangeDate in YYYY format for the years 1996, 1997 and 1998.

2. The output will have Year, No of Rates Changed and  Distinct Rates. 

Tables: 

EmployeePayHistory

 

Sample Output: 

 

 SELECT Year(RateChangeDate) AS [Year],
COUNT([Rate]) AS [No of Rates Changed],
COUNT(DISTINCT Rate) AS [DistinctRates]
FROM [EmployeePayHistory]
WHERE Year(RateChangeDate) in ('1996','1997','1998')
GROUP BY Year(RateChangeDate); 

 
Write a stored procedure  to select the details of employees like LastName, FirstName, City, StateProvinceCode

Design Rules : 

1. Use Joins

PROCEDURE NAME: GetEmployeeDetails

INPUT PARAMETERS: @StateProvinceCode

Tables: 

Contact, Employee, EmployeeAddress, Address, StateProvince

Sample Output:

 

 ALTER PROCEDURE [GetEmployeeDetails] 
@StateProvinceCode nvarchar(3)
AS 
SELECT c.LastName, c.FirstName, a.City, s.StateProvinceCode
FROM Contact c JOIN Employee e 
      ON c.ContactID = e.ContactID
INNER JOIN EmployeeAddress ea
      ON e.EmployeeID = ea.EmployeeID
INNER JOIN Address a 
     ON ea.AddressID = a.AddressID
INNER JOIN StateProvince s 
      ON a.StateProvinceID = s.StateProvinceID
WHERE s.StateProvinceCode = @StateProvinceCode; 

 
Write a query to return the details  Employee details like Name, Department, Manager, City, StateProvinceCode, CountryRegionCode



Design Rules : 

1. Retreive output where City is ‘Phoenix’

2. Fetch StateprovinceCode, CountryRegionCode from StateProvince table.

3. Fetch Name from Contact table.

4. For Name and Manager columns, use ‘FirstName’ and 'LastName'  seperated by a single space delimiter. Order should be ‘FirstName’  followed by a space, then the ‘LastName’. (Ex: FirstName LastName).

5. If Manager for a particular employee is null then display as ‘CEO’

6. Rename the column ‘Name’ from Department table as ‘Department’ 

7. Order the output by Department.

Tables: 

Employee, Contact, EmployeeAddress, Address, StateProvince, EmployeeDepartmentHistory, Department

Sample Output:

 

 SELECT    
  [Name]= c.FirstName +' '+ c.LastName
, [Department] = d.Name
, [Manager]= ISNULL(pc.FirstName+' '+ pc.LastName, 'CEO')
, a.City
, sp.StateProvinceCode
, sp.CountryRegionCode
FROM         
            Employee e 
  LEFT JOIN Employee emp ON e.ManagerID = emp.EmployeeID 
  LEFT JOIN Contact pc ON pc.ContactID = emp.ContactID 
  INNER JOIN Contact c ON e.ContactID = c.ContactID 
  INNER JOIN EmployeeAddress ea ON e.EmployeeID = ea.EmployeeID 
  INNER JOIN Address a ON ea.AddressID = a.AddressID 
  INNER JOIN StateProvince sp ON a.StateProvinceID = sp.StateProvinceID 
  INNER JOIN EmployeeDepartmentHistory edh 
             ON e.EmployeeID = edh.EmployeeID 
  INNER JOIN Department d ON edh.DepartmentID = d.DepartmentID 
WHERE a.City = 'Phoenix'
ORDER BY  d.Name  

 
Write a query to return Employeeid,Managerid,Title,Gender using Pagination concept



Design Rules : 

1. Ignore the first 5 rows and select the next 3  rows using pagination concept

2. Order the resultset by EmployeeID

Tables: 

Employee

Sample Output: 

 

 select Employeeid,managerid,title,gender from
Employee 
order by employeeid
offset 5 rows 
fetch next 3 rows only 

 
Write a function to select EmployeeName, Gender, EmployeeID, ManagerID, Title

Design Rules : 

1. For EmployeeName use ‘FirstName’ and 'LastName'  seperated by a single space delimiter. Order should be ‘FirstName’  followed by a space, then the ‘LastName’. (Ex: FirstName LastName)

FUNCTION NAME: CASQL_Fn_EmployeeDetails

INPUT PARAMETERS: @EmployeeID

Tables: 

Employee, Contact

Sample Output: 

 

 ALTER FUNCTION CASQL_Fn_EmployeeDetails
(@EmployeeID  int)
RETURNS TABLE
AS
RETURN
SELECT FirstName + ' ' + LastName AS 'EmployeeName', 
                     E.Gender,
                     EmployeeID, 
                     ManagerID ,
                     E.Title
            FROM   Employee AS E 
                    JOIN Contact AS C 
                      ON C.ContactID = E.ContactID 
             WHERE  EmployeeID = @EmployeeID  

 
Rate incrementation for each ModifiedYear needs to be captured. Write a query to select ModifiedYear,  Rate and RateRanking (Highest rate to be ranked 1 and so on.) 

Design Rules : 

1. Get Modified Year from the column ModifiedDate.

2. For RateRanking use ranking statements to Rank the Rates (Highest Rate to be ranked 1 and so on) for each Modified Year.

3. Modified Year greater than 2000 only. 

4. Order the output by ModifiedDate

5. Refer the sample output for your reference.

Tables: 

EmployeePayHistory

Sample Output:



 
 

 SELECT
 Year(ModifiedDate) as ModifiedYear,
    [Rate],
 RANK() over(partition by Year(ModifiedDate) order by Rate desc) as RateRanking
 
FROM EmployeePayHistory where Year(ModifiedDate) >'2000'
order by Year(ModifiedDate) 

 
Write a query to return ISO Formatted Date, US Currency, UK Currency.

Design Rules : 

1. Retreive ModifiedDate as ISO Formatted Date in yyyy-MM-DD format.

2. Select Rate in US & UK currency format and rename the columns as US Currency and UK Currency respectively.  Refer sample output

3. Retreive the output for ModifiedDate  2004 

Tables: 

EmployeePayHistory

Sample Output:

 

 SELECT
 Year(ModifiedDate) as ModifiedYear,
    [Rate],
 RANK() over(partition by Year(ModifiedDate) order by Rate desc) as RateRanking
 
FROM EmployeePayHistory where Year(ModifiedDate) >'2000'
order by Year(ModifiedDate) 

 
Write a query using MERGE statement and perform delete and update operations in a single query.

Design Rules : 

1. Relationship between CountryRegion and StateProvince is based on CountryRegionCode. 

2. Target table for Update and Delete operation is StateProvince

3.  Perform delete operation in  the merge statement when CountryRegionCode is “ZZ”. 

4. Perform update action in the merge statement when CountryRegionCode is “ZY” , update column Name as 'MERGE'.

5. Please follow the order of operation as mentioned above. First delete, then update.

Tables: 

CountryRegion, StateProvince

Sample Output:

 

 MERGE StateProvince AS statep
USING (SELECT CountryRegionCode FROM StateProvince) AS country
ON country.CountryRegionCode = statep.CountryRegionCode
WHEN MATCHED AND statep.CountryRegionCode = 'ZZ' THEN DELETE
WHEN MATCHED AND statep.CountryRegionCode = 'ZY' THEN UPDATE SET statep.Name = 'MERGE'; 

 


Write a query to select the total sum of Rate for each employee for the given modifed year using PIVOT concept. Select the Employeeid, Rate, ModifiedYear from EmployeePayHistory table.

Design Rules : 

1. For employeeid less than and equal to 4.

2. Get ModifiedYear from the column ModifiedDate.

3. For ModifiedYear  [2004],[2002],[1997] only

Tables: 

EmployeePayHistory 

TempTableName: 

#PayHistoryPivotResult

Sample Output: 

 

 SELECT *
      INTO #PayHistoryPivotResult
FROM
  (SELECT EmployeeID,Rate,
    YEAR(ModifiedDate) AS ModifiedYear
  FROM EmployeePayHistory
  where employeeid<=4) AS PayHistory
  PIVOT(SUM(Rate)
  FOR ModifiedYear IN([2004],[2002],[1997])
  ) AS PivotPayHistory;
  
  select * from #PayHistoryPivotResult