This is a simple test or assignment to see how comfortable someone is using SQL at the beginner to advanced level. Exercises of this nature will undoubtedly aid in skill improvement. The simplest method of learning is through practice and exercise problems.
This is unrelated to Microsoft examinations or their format. Simply put, I’m testing your SQL knowledge. The five sections of the test are all focused on database development. You must use SQL built-in /system functions, arithmetic operators, various clauses, etc. to pass the test.
Download the sample dataset (SQL file for the tables and data) for the exam from the link below.
The schema is as follows:
Questions:
1) How many people work in each department and location, respectively
The output should contain, Department Name, Location Name, The number of employees
2) Determine which locations and departments don’t have any staff members. You can choose to offer single results or individual result sets.
The output should contain, Department Number, Department Name, Location Id, and Location Name
3) List the departments, their locations, and the number of employees that work there.
The output should contain, Department Number, Department Name, Location Id, Location Name and Number of Employees
4) List the departments, their locations, and the number of staff in each. Include the department’s top and bottom salaries as well. Include the total amount paid to each department, including any commissions.
The output should contain, Department Number, Department Name, Location Id, Location Name and Number of Employees
5) Calculate the number of years each employee has been with the company and make a list of them.
The output should include the employee’s name, employee number, and the number of years he has been employed by the company. To ensure a readable format, provide the employee’s full name in a single column with appropriate spaces. Also, provide the proper column names for the columns that are generated by expressions.
6) There is a suggestion to raise the pay for employees who have served their two-year time frame by 1000 dollars. Give a list of the employees together with their proposed and existing salaries.
The output needs to contain the employee’s ID number, full name, the number of years they’ve worked for the company, their current pay, and the recommended pay. Give the columns that expressions create, the appropriate column names. The salary means the complete salary including the commissions if any.
7) Identify the employees who are drawing the second highest salary in the organization.
The output needs to contain the employee’s ID number, full name in a single column, role, department, and location. Give appropriate names to the columns that expressions create.
8) Create an employee report that includes an increase in commission of 10% for each employee. If there is a null, disregard it.
The output should contain the employee’s ID number, current salary, current commission, and the commission with a 10% increase. Give appropriate names to the columns that expressions create.
For answers, click here.
Big Data and SQL 