Blended Assessment Detailed Report

Section Name	Total Questions	Answered	Not Answered	Correct Marks	Partial Marks	Penalty Marks	Score
Certified Developer Advanced - ANSI SQL	10	7	3	50.00	0.00	0.00	50.00

1. HR – Human Resource Management System

In this case study, the Human Resource Management System given below to track and maintain complete employee, department, and pay history details.

As an SQL developer, you are expected to write queries to analyse data, based on the given requirements and business conditions.

Following are the list of tables are already defined

Department

Column	DataTypes	Nullability	Description
DepartmentId	small int	Not Null	Primary key for Department rows.
Name	nvarchar	Not Null	Name of the department.
GroupName	nvarchar(50)	Not Null	Name of the group to which the department belongs.
ModifiedDate	datetime	Not Null	Date and time the row was last updated.

Employee

Column	Data type	Nullability	Description
EmployeeID	int	Not null	Primary key for employee rows.
NationalIDNumber	nvarchar(15)	Not null	Unique national identification number such as a social security number.
ContactID	int	Not null	Identifies the employee in the Contact table. Foreign key to Contact.ContactID.
LoginID	nvarchar(256)	Not null	Network logon.
ManagerID	int	Null	Manager to whom the employee is assigned. Foreign key to Employee.EmployeeID.
Title	nvarchar(50)	Not null	Work title such as Buyer or Sales Representative.
BirthDate	datetime	Not null	Date of birth.
MaritalStatus	nchar(1)	Not null	M = Married S = Single
Gender	nchar(1)	Not null	M = Male F = Female
HireDate	datetime	Not null	Date on which the employee was hired.
SalariedFlag	bit	Not null	Job classification. 0 = Hourly, not exempt from collective bargaining. 1 = Salaried, exempt from collective bargaining.
VacationHours	smallint	Not null	Number of available vacation hours.
SickLeaveHours	smallint	Not null	Number of available sick leave hours.
CurrentFlag	bit	Not null	0 = Inactive 1 = Active
rowguid	uniqueidentifier ROWGUIDCOL	Not null	ROWGUIDCOL number that uniquely identifies the row. Used to support a merge replication sample.
ModifiedDate	datetime	Not null	Date and time the row was last updated.

Employee Address

Column	Data type	Nullability	Description
EmployeeID	int	Not null	Employee identification number. Foreign key to Employee.EmployeeID
AddressID	int	Not null	Employee address identification number. Foreign key to Address.AddressID.
rowguid	uniqueidentifier ROWGUIDCOL	Not null	ROWGUIDCOL number that uniquely identifies the row. Used to support a merge replication sample.
ModifiedDate	datetime	Not null	Date and time the row was last updated.

EmployeeDepartmentHistory

Column	Data type	Nullability	Description
EmployeeID	int	Not null	Employee identification number. Foreign key to Employee.EmployeeID.
DepartmentID	smallint	Not null	Department in which the employee works or worked. Foreign key to Department.DepartmentID
ShiftID	tinyint	Not null	Work shift, such as day, evening, or night, to which the employee is assigned. Foreign key to Shift.ShiftID.
EndDate	datetime	Null	Date the employee left the department. NULL = Current department
StartDate	datetime	Not null	Date the employee started work in the department.
ModifiedDate	datetime	Not null	Date and time the row was last updated.

EmployeePayHistory

Column	Data type	Nullability	Description
EmployeeID	int	Not null	Employee identification number. Foreign key to Employee.EmployeeID.
RateChangeDate	datetime	Not null	Date the change in pay is effective.
Rate	money	Not null	Hourly rate of pay.
PayFrequency	tinyint	Not null	1 = Salary received monthly 2 = Salary received biweekly
ModifiedDate	datetime	Not null	Date and time the row was last updated.

Note: PRIMARY KEY CLUSTERED – EmployeeID, RateChangeDate

Shift

Column	Data type	Nullability	Description
ShiftID	smallint	Not null	Primary key.
Name	nvarchar(50)	Not null	Shift description.
StartTime	datetime	Not null	Shift start time.
EndTime	datetime	Not null	Shift end time.
ModifiedDate	datetime	Not null	Date and time the row was last updated.

Address

Column	Data type	Nullability	Description
AddressID		Not null	Primary key for address rows.
AddressLine1	nvarchar(60)	Not null	First street-address line.
AddressLine2	nvarchar(60)	Null	Second street address line.
City	nvarchar(30)	Not null	Name of the city.
StateProvinceID	int	Not null	Unique identification number for the state or province. Foreign key to StateProvince.StateProvinceID.
PostalCode	nvarchar(15)	Not null	Postal code for the street address.
rowguid	uniqueidentifier ROWGUIDCOL	Not null	ROWGUIDCOL number uniquely identifying the row. Used to support a merge replication sample.
ModifiedDate	datetime	Not null	Date and time the row was last updated.

AddressType

Column	Data type	Nullability	Description
AddressTypeID	int	Not null	Primary key for AddressType rows.
Name	nvarchar(50)	Not null	Address type description. For example, billing, home, or shipping.
rowguid	uniqueidentifier ROWGUIDCOL	Not null	ROWGUIDCOL number uniquely identifying the row. Used to support a merge replication sample.
ModifiedDate	datetime	Not null	Date and time the row was last updated.

Contact

Column	Data type	Nullability	Description
ContactID	int	Not null	Primary key for Contact rows.
NameStyle	bit	Not null	Indicates the column in which the family name is stored. 0 = Family name is stored in LastName and the first name is stored in FirstName. 1 = .Family name is stored in FirstName and the first name is stored in LastName.
Title	nvarchar(8)	Null	A courtesy title. For example, Mr. or Ms.
FirstName	nvarchar(50)	Not null	First name of the person.
MiddleName	nvarchar(50)	Not null	First name of the person.
LastName	nvarchar(50)	Not null	Last name of the person.
Suffix	nvarchar(10)	Null	Surname suffix. For example, Sr. or Jr.
EmailAddress	nvarchar(50)	Null	E-mail address for the person.
EmailPromotion	int	Not null	0 = Contact does not wish to receive e-mail promotions. 1 = Contact does wish to receive e-mail promotions.
Phone	nvarchar(25)	Null	Phone number associated with the person.
PasswordHash	varchar(128)	Not null	Password for the e-mail account.
PasswordSalt	varchar(10)	Not null	Random value concatenated with the password string before the password is hashed.
AdditionalContactInfo	xml	Null	Additional contact information about the person, such as additional phone numbers and addresses or special instructions and notes. Data is stored in XML format. For more information, see About the Contact.AdditionalContactInfo xml Column
rowguid	uniqueidentifier ROWGUIDCOL	Not null	ROWGUIDCOL number uniquely identifying the row. Used to support a merge replication sample.
ModifiedDate	datetime	Not null	Date and time the row was last updated.

ContactType

Column	Data type	Nullability	Description
ContactTypeID	int	Not null	Primary key for ContactType records.
Name	nvarchar(50)	Not null	Contact type description.
ModifiedDate	datetime	Not null	Date and time the row was last updated.

CountryRegion

Column	Data type	Nullability	Description
CountryRegionCode	nvarchar(3)	Not null	Primary key.Standard code identifying countries and regions.
Name	nvarchar(50)	Not null	Country or region name.
ModifiedDate	datetime	Not null	Date and time the row was last updated.

StateProvince

Column	Data type	Nullability	Description
StateProvinceID	int	Not null	Primary key.
StateProvinceCode	nchar(3)	Not null	State or province code.
CountryRegionCode	nvarchar(3)	Not null	Country or region code. Foreign key to CountryRegion.CountryRegionCode.
IsOnlyStateProvinceFlag	bit	Not null	0 = StateProvinceCode exists. 1 = StateProvinceCode unavailable, use CountryRegionCode.
Name	nvarchar(50)	Not null	State or province description.
TerritoryID	int	Not null	ID of the Adventure Works sales territory in which the state or province is located. Foreign key to SalesTerritory.SalesTerritoryID.
rowguid	uniqueidentifier	Not null	ROWGUIDCOL number uniquely identifying the row. Used to support a merge replication sample.
ModifiedDate	datetime	Not null	Date and time the row was last updated.

1.1 To return Employee History details

15 minutes 34 seconds Correct

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

Guidelines:

Click on 'Solve Question' button to get the SQL Editor window
You can try executing your SQL query, using 'Run' button. The SQL Query output will be displayed to you
Use 'Submit' button to submit your answers for auto evaluation and scoring
You can go back to main question using 'Back' button
You can retrieve the previously executed queries from the 'Log' tab
Editor is enabled with 'Auto Save' feature hence your query will be auto saved
Do not attempt to create any new database objects or delete existing database objects
Don’t change the database structure or rename table/column names
You can answer the questions in any order
Please make sure that the editor will contain ONLY the solution query. Please remove other queries which you have tried
The order of the columns in the sample output should match with your query
Sample output rows is just for Associate's reference and this need not match the actual output

Sample Output:

LASTNAME	FIRSTNAME	EMPLOYEEID	DEPARTMENT	RATE
Allen	Phyllis	4	Engineering	8.6200
Allen	Phyllis	4	Tool Design	8.6200

Expected Query

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%';

Submitted Query

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%';

Test Case Execution

Test Case Name	Test Case Description	Primary TestCase	Secondary TestCase
Validate_EmployeeHistory_Details	To validate the Employee History Details

1.2 Retrieve Rate count details

6 minutes 29 seconds Correct

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 :

Display Year using column RateChangeDate as [No of Rates Changed] in YYYY format for the years 1996, 1997 and 1998
Display Distict Rates column as [DistinctRates]
The output will have Year, No of Rates Changed and Distinct Rates

Tables:
EmployeePayHistory

Guidelines:

Click on 'Solve Question' button to get the SQL Editor window
You can try executing your SQL query, using 'Run' button. The SQL Query output will be displayed to you
Use 'Submit' button to submit your answers for auto evaluation and scoring
You can go back to main question using 'Back' button
You can retrieve the previously executed queries from the 'Log' tab
Editor is enabled with 'Auto Save' feature hence your query will be auto saved
Do not attempt to create any new database objects or delete existing database objects
Don’t change the database structure or rename table/column names
You can answer the questions in any order
Please make sure that the editor will contain ONLY the solution query. Please remove other queries which you have tried
The order of the columns in the sample output should match with your query
Sample output rows is just for Associate's reference and this need not match the actual output

Sample Output:

YEAR	NO OF RATES CHANGED	DISTINCTRATES
1997	5	4
1998	4	4

Expected Query

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);

Submitted Query

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);

Test Case Execution

Test Case Name	Test Case Description	Primary TestCase	Secondary TestCase
Validate_Retrieval_of_Rate_Details	To validate the retrieval of rate details

1.3 To return Employee details whose State is AB

9 minutes 52 seconds InCorrect

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

Design Rules :
1. Use Joins
2. Do not submit your query with other keywords like alter procedure or execute procedure. Only the create procedure text should be submitted

PROCEDURE NAME: GetEmployeeDetails

INPUT PARAMETERS: @StateProvinceCode

Tables: Contact,Employee,EmployeeAddress,Address,StateProvince

Guidelines:

Click on 'Solve Question' button to get the SQL Editor window
You can try executing your SQL query, using 'Run' button. The SQL Query output will be displayed to you
Use 'Submit' button to submit your answers for auto evaluation and scoring
You can go back to main question using 'Back' button
You can retrieve the previously executed queries from the 'Log' tab
Editor is enabled with 'Auto Save' feature hence your query will be auto saved
Do not attempt to create any new database objects or delete existing database objects
Do not change the database structure or rename table/column names
You can answer the questions in any order
Please make sure that the editor will contain ONLY the solution query. Please remove other queries which you have tried
The order of the columns in the sample output should match with your query
Sample output rows is just for Associate's reference and this need not match the actual output
Do not change the Structure, Name and input parameter of the Stored Procedure
Don’t attempt hardcoding but rather use appropriate keywords/functions applicable to achieve similar output
Do not submit the solution code with execute statement or alter statement
Do not add END/GO keywords at the end of the stored procedure

Sample Output:

LASTNAME	FIRSTNAME	CITY	STATEPROVINCECODE
Jacobson	Sean	Camrose	AB
Steve	Michael	Calgary	AB
Allison	Cecil	Calgary	AB

Expected Query

CREATE 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;

Submitted Query

CREATE PROCEDURE [GetEmployeeDetails] 
@StateProvinceCode nvarchar(3)
AS 

-- TODO : Query the requirement here

Test Case Execution

Test Case Name	Test Case Description	Primary TestCase	Secondary TestCase
Validate_FilterConditions_Based_on_State	To validate the filter conditions based on State

1.4 To return Employee details based on City.

23 seconds Not Answered

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

Design Rules :
       1. Retreive output where City is ‘Phoenix’
       2. Fetch StateprovinceCode, CountryRegionCode from StateProvince tables
       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

Guidelines:

Click on 'Solve Question' button to get the SQL Editor window
You can try executing your SQL query, using 'Run' button. The SQL Query output will be displayed to you
Use 'Submit' button to submit your answers for auto evaluation and scoring
You can go back to main question using 'Back' button
You can retrieve the previously executed queries from the 'Log' tab
Editor is enabled with 'Auto Save' feature hence your query will be auto saved
Do not attempt to create any new database objects or delete existing database objects
Do not change the database structure or rename table/column names
You can answer the questions in any order
Please make sure that the editor will contain ONLY the solution query. Please remove other queries which you have tried
The order of the columns in the sample output should match with your query
Sample output rows is just for Associate's reference and this need not match the actual output

Sample Output:

NAME	DEPARTMENT	MANAGER	CITY	STATEPROVINCECODE	COUNTRYREGIONCODE
Michelle Peter	Marketing	CEO	Phoenix	AZ	US
Sandra Altamirano	Marketing	Phyllis Allen	Phoenix	AZ	US
Marvin Mark	Tool Design	Sean Jacobson	Phoenix	AZ	US

1.5 Using Pagination concept

2 minutes 13 seconds Correct

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

Guidelines:

Click on 'Solve Question' button to get the SQL Editor window
You can try executing your SQL query, using 'Run' button. The SQL Query output will be displayed to you
Use 'Submit' button to submit your answers for auto evaluation and scoring
You can go back to main question using 'Back' button
You can retrieve the previously executed queries from the 'Log' tab
Editor is enabled with 'Auto Save' feature hence your query will be auto saved
Do not attempt to create any new database objects or delete existing database objects
Do not change the database structure or rename table/column names
You can answer the questions in any order
Please make sure that the editor will contain ONLY the solution query. Please remove other queries which you have tried
The order of the columns in the sample output should match with your query
Sample output rows is just for Associate's reference and this need not match the actual output

Sample Output:

EMPLOYEEID	MANAGERID	TITLE	GENDER
6	1	Maintenance Supervisor	M
7	4	Production Technician - WC30	F
8	2	Production Supervisor - WC40	M

Expected Query

SELECT   employeeid, managerid, title, gender 
FROM     employee 
ORDER BY employeeid offset 5 rows
FETCH next 3 rows only

Submitted Query

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

Test Case Execution

Test Case Name	Test Case Description	Primary TestCase	Secondary TestCase
Validate_Pagination_Concept	To validate the pagination concept

1.6 User Functions

24 seconds Not Answered

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

Design Rules :

To display Employee name use ‘FirstName’ and 'LastName' separated by a single space delimiter and use alias name 'EmployeeName'
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

Guidelines:

Click on 'Solve Question' button to get the SQL Editor window
You can try executing your SQL query, using 'Run' button. The SQL Query output will be displayed to you
Use 'Submit' button to submit your answers for auto evaluation and scoring
You can go back to main question using 'Back' button
You can retrieve the previously executed queries from the 'Log' tab
Editor is enabled with 'Auto Save' feature hence your query will be auto saved
Do not attempt to create any new database objects or delete existing database objects
Do not change the database structure or rename table/column names
You can answer the questions in any order
Please make sure that the editor will contain ONLY the solution query. Please remove other queries which you have tried
The order of the columns in the sample output should match with your query
Sample output rows is just for Associate's reference and this need not match the actual output
Do not change the Structure,Name and input parameter of the Stored Procedure
Don’t attempt hardcoding but rather use appropriate keywords/functions applicable to achieve similar output
Do not submit the solution code with execute statement or alter statement
Do not add END/GO keywords at the end of the stored procedure

Sample Output:

EMPLOYEENAME	GENDER	EMPLOYEEID	MANAGERID	TITLE
Sean Jacobson	M	3	4	Marketing Specialist

1.7 Using Ranking Statements

3 minutes 45 seconds Correct

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 :

Get Modified Year from the column ModifiedDate and use alias name 'ModifiedYear'
For RateRanking use ranking statements to Rank the Rates (Highest Rate to be ranked 1 and so on) for each Modified Year. Use alias name 'RateRanking'
Modified Year greater than 2000 only
Order the output by ModifiedDate
Refer the sample output for your reference

Tables:
EmployeePayHistory

Guidelines:

Click on 'Solve Question' button to get the SQL Editor window
You can try executing your SQL query, using 'Run' button. The SQL Query output will be displayed to you
Use 'Submit' button to submit your answers for auto evaluation and scoring
You can go back to main question using 'Back' button
You can retrieve the previously executed queries from the 'Log' tab
Editor is enabled with 'Auto Save' feature hence your query will be auto saved
Do not attempt to create any new database objects or delete existing database objects
Do not change the database structure or rename table/column names
You can answer the questions in any order
Please make sure that the editor will contain ONLY the solution query. Please remove other queries which you have tried
The order of the columns in the sample output should match with your query
Sample output rows is just for Associate's reference and this need not match the actual output

Sample Output:

MODIFIEDYEAR	RATE	RATERANKING
2002	29.8462	1
2002	25.0000	2
2002	12.0000	3
2004	29.8462	1
2004	13.4615	2
2004	12.6990	3

Expected Query

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)

Submitted Query

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);

Test Case Execution

Test Case Name	Test Case Description	Primary TestCase	Secondary TestCase
Validate_Ranking Statements	To validate the ranking statements

1.8 Using Format function

11 minutes 25 seconds Correct

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

Guidelines:

Click on 'Solve Question' button to get the SQL Editor window
You can try executing your SQL query, using 'Run' button. The SQL Query output will be displayed to you
Use 'Submit' button to submit your answers for auto evaluation and scoring
You can go back to main question using 'Back' button
You can retrieve the previously executed queries from the 'Log' tab
Editor is enabled with 'Auto Save' feature hence your query will be auto saved
Do not attempt to create any new database objects or delete existing database objects
Do not change the database structure or rename table/column names
You can answer the questions in any order
Please make sure that the editor will contain ONLY the solution query. Please remove other queries which you have tried
The order of the columns in the sample output should match with your query
Sample output rows is just for Associate's reference and this need not match the actual output
Don’t attempt hardcoding but rather use appropriate keywords/functions applicable to achieve similar output

Sample Output:

ISO FORMATTED DATE	US CURRENCY	UK CURRENCY
2004-07-31	$12.70	£12.70
2004-07-31	$13.46	£13.46
2004-07-31	$29.85	£29.85

Expected Query

SELECT Format(modifieddate, 'yyyy-MM-dd') AS [ISO Formatted Date],Format(rate, 'C', 'en-US')         AS [US Currency], 
       Format(rate, 'C', 'en-GB')         AS [UK Currency] 
FROM   employeepayhistory 
WHERE  Year(modifieddate) = '2004';

Submitted Query

select FORMAT(ModifiedDate,'yyyy-MM-dd') AS [ISO Formatted Date],
FORMAT(Rate,'C','en-US') AS [US Currency],
FORMAT(Rate,'C', 'en-GB') AS [UK Currency]
from EmployeePayHistory
where YEAR(ModifiedDate)='2004';

Test Case Execution

Test Case Name	Test Case Description	Primary TestCase	Secondary TestCase
Validate_Analytic_Functions	To validate the analytic functions

1.9 Using Merge statement

5 minutes 55 seconds InCorrect

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

Guidelines:

Click on 'Solve Question' button to get the SQL Editor window
You can try executing your SQL query, using 'Run' button. The SQL Query output will be displayed to you
Use 'Submit' button to submit your answers for auto evaluation and scoring
You can go back to main question using 'Back' button
You can retrieve the previously executed queries from the 'Log' tab
Editor is enabled with 'Auto Save' feature hence your query will be auto saved
Do not attempt to create any new database objects or delete existing database objects
Do not change the database structure or rename table/column names
You can answer the questions in any order
Please make sure that the editor will contain ONLY the solution query. Please remove other queries which you have tried
The order of the columns in the sample output should match with your query
Sample output rows is just for Associate's reference and this need not match the actual output

Sample Output:

STATEPROVINCEID	STATEPROVINCECODE	COUNTRYREGIONCODE	NAME	TERRITORYID
12	97	ZY	MERGE	1

Expected Query

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';

Submitted Query

update employee SET Title='STD' where EmployeeID=1;

Test Case Execution

Test Case Name	Test Case Description	Primary TestCase	Secondary TestCase
Validate_Merge_Statements	To validate the Merge statements

1.10 Using Pivot function

1 seconds Not Answered

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

Guidelines:

Click on 'Solve Question' button to get the SQL Editor window
You can try executing your SQL query, using 'Run' button. The SQL Query output will be displayed to you
Use 'Submit' button to submit your answers for auto evaluation and scoring
You can go back to main question using 'Back' button
You can retrieve the previously executed queries from the 'Log' tab
Editor is enabled with 'Auto Save' feature hence your query will be auto saved
Do not attempt to create any new database objects or delete existing database objects
Do not change the database structure or rename table/column names
You can answer the questions in any order
Please make sure that the editor will contain ONLY the solution query. Please remove other queries which you have tried
The order of the columns in the sample output should match with your query
Sample output rows is just for Associate's reference and this need not match the actual output
Do not change the Structure, Name and input parameter of the PIVOT Function

Sample Output:

EMPLOYEEID	2004	2002	1997
1	12.6990	NULL	NULL
2	13.4615	NULL	NULL
3	29.8462	NULL	NULL
4	NULL	NULL	8.6200