Write a query  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)
    2. Return only  employees whose FirstName starts with letter 'S' 
    3. Order the output by FirstName 

Tables: Employee, Contact

Guidelines:

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

Sample Output:

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  firstname LIKE 'S%'
ORDER  BY firstname

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

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
    3. Order the output by Year

Tables: EmployeePayHistory 

Guidelines:

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

Sample Output:

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

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 
      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: 

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

Sample Output: 

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: 

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

Sample Output: 

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

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

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 and use alias name 'ModifiedYear'
    2. 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'
    3. Modified Year greater than 2000 only
    4. Order the output by ModifiedDate 
    5. Refer the sample output for your reference

Tables: 
    EmployeePayHistory 

Guidelines:

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

Sample Output: 

Write a query that returns the orders which was shipped with minimum duration from the date of the order placed. 

Design rules:

1. The output must contain the below columns ORDER_ID, MINIMUM_DURATION_IN_DAYS 
2. The column MINIMUM_DURATION_IN_DAYS can be acheived by calculating the number of days between the date of order and shipment date and it must pick the least duration days out of all orders. 
3. Sort the query using ascending order of the order_id.

Guidelines:

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

Sample Output:

SELECT order_id,(shipment_date - order_date)AS MINIMUM_DURATION_IN_DAYS
FROM t_order_placement
WHERE (shipment_date - order_date)= ( SELECT MIN(shipment_date-order_date) FROM t_order_placement)
ORDER BY ORDER_ID;

select * from T_order_details;

Write a query that updates the discount percentage based on the below constraints : 

Design Rules:

1. If the total cost is greater than 2000, then discount percentage must be set as 50% 
2. Else set the discount percentage as 10%

Guidelines:

1. click on 'Solve Question' button to get the SQL Editor window
2. You can try executing your SQL query, using 'Run' button. The SQL Query output will be displayed to you
3. Use 'Submit' button to submit your answers for auto evaluation and scoring
4. You can go back to main question using 'Back' button
5. You can retrieve the previously executed queries from the 'Log' tab
6. Editor is enabled with 'Auto Save' feature hence your query will be auto saved
7. Do not attempt to create any new database objects or delete existing database objects
8. Do not change the database structure or rename table/column names 
9. Please make sure that the editor will contain ONLY the solution query on your last save. Once you are done, please remove other queries which you have tried out and have ONLY the solution query. Then click on 'Submit' button

Write a query that retrives the customer details along with the order and stock details. (Retrieve only the columns which is mentioned in the design rules)

Use Joins to achieve this. 

Design rules:

1. The output must contain the following columns Cust_id, order_Date,order_id, stock_id. 
2. Sort the output based on the ascending order of the cust_id, order_date, order_id, stock_id.

Guidelines:

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

Sample Output:

Write a Pl / Sql function that takes customer id as input and retrieves the shipment status of the customer. 

         Input parameter :Customer id

        Output parameter : SHIPMENT_STATUS

Exception scenario : 

          If there is no customer with the given input id, then raise application error expcetion with the error message as 'ERROR'  

Guidelines:

1. Click on 'Solve Question' button to get the SQL Editor window
2. You can try executing your SQL query, using 'Run' button. The SQL Query output will be displayed to you
3. Use 'Submit' button to submit your answers for auto evaluation and scoring
4. You can go back to main question using 'Back' button
5. You can retrieve the previously executed queries from the 'Log' tab
6. Editor is enabled with 'Auto Save' feature hence your query will be auto saved
7. Do not attempt to create any new database objects or delete existing database objects
8. Do not change the database structure or rename table/column names
9. Please make sure that the editor will contain ONLY the solution query on your last save. Once you are done, please remove other queries which you have tried out and have ONLY the solution query. Then click on 'Submit' button
10. Do not alter the provided skeleton's method signature. Please proceed with the same procedure name and same out parameter