IA Report

Profile Report

Candidate Details

Lerner (GRTLN1000)

learner@maantt.com

Group Name : ADMIN

Overall competency

Pass

Pass Percentage : 50

90%

Assessment Details

Assessment Name : Testing pivot, function, procedure, sub query etc.,

Test Marks : 90.00

Total Questions : 1

Penalty : No

Partial Marks : Yes

Attempt Details

Attempt No : 1

Started At : 14-05-2019 2:44PM

Submitted At : 14-05-2019 2:54PM

Time Taken : {TakenMinutes}

Candidate Score

Questions Answered : 90.00

Questions Not Answered : 0

Marks for Correct Answer : 0.00

Penalty : 0.00

Your Score : 90.00

Answer Summary

To return Employee History details

 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:

Test case execution

Test Case Name	Test Case Description	Primary TestCase	Secondary TestCase
To return Employee History details	To return Employee History details	True	True

Retrieve Rate count details

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:

Test case execution

Test Case Name	Test Case Description	Primary TestCase	Secondary TestCase
Retrieve Rate count details	Retrieve Rate count details	True	True

To return Employee details whose State is AB

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:

Test case execution

Test Case Name	Test Case Description	Primary TestCase	Secondary TestCase
To return Employee details whose State is AB	To return Employee details whose State is AB	True

To return Employee details based on City.

 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:

Test case execution

Test Case Name	Test Case Description	Primary TestCase	Secondary TestCase
To return Employee details based on City.	To return Employee details based on City.	True

Using Pagination concept

 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:

Test case execution

Test Case Name	Test Case Description	Primary TestCase	Secondary TestCase
Using Pagination concept	Using Pagination concept	True

User Functions

 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:

Test case execution

Test Case Name	Test Case Description	Primary TestCase	Secondary TestCase
User Functions	User Functions	True

Using Ranking Statements

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:

Test case execution

Test Case Name	Test Case Description	Primary TestCase	Secondary TestCase
Using Ranking Statements	Using Ranking Statements	True	True

Using Format function

 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:

Test case execution

Test Case Name	Test Case Description	Primary TestCase	Secondary TestCase
Using Format function	Using Format function	False	False

Using Merge statement

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:

Test case execution

Test Case Name	Test Case Description	Primary TestCase	Secondary TestCase
Using Merge statement	Using Merge statement	True

Using Pivot function

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:

Test case execution

Test Case Name	Test Case Description	Primary TestCase	Secondary TestCase
Using Pivot function	Using Pivot function	True	True

Report generated time : 20-05-2019 06:21:27