KEMBAR78
SQL Test Paper | PDF | Sql | Information Technology Management
Scribd
Upload
11 views3 pages

SQL Test Paper

The document is an SQL test paper for database developers at BrightTech Solutions, containing instructions and a dataset description for managing employees, departments, projects, and salaries. It includes 18 questions that cover various SQL operations such as creating tables, updating records, deleting entries, and using aggregate functions, triggers, stored procedures, and views. Each question is assigned specific marks, indicating its complexity and importance.

Uploaded by

varshacpatilfct
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
11 views3 pages

SQL Test Paper

The document is an SQL test paper for database developers at BrightTech Solutions, containing instructions and a dataset description for managing employees, departments, projects, and salaries. It includes 18 questions that cover various SQL operations such as creating tables, updating records, deleting entries, and using aggregate functions, triggers, stored procedures, and views. Each question is assigned specific marks, indicating its complexity and importance.

Uploaded by

varshacpatilfct
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 3

SQL Test Paper

Instructions:

Attempt all questions.

Use appropriate SQL syntax.

Marks are indicated against each question.

Dataset Description

You are working as a database developer for "BrightTech Solutions", which


maintains a relational database for managing employees, departments, projects, and
salaries. The following tables are used:

1. Employees

EmpID EmpName DeptID ManagerID JoinDate Salary Bonus Status


VARCHAR
INT VARCHAR INT INT DATE INT INT
(Active/Resigned)

2. Departments

DeptID DeptName Location


INT VARCHAR VARCHAR

3. Projects

ProjID ProjName DeptID StartDate EndDate

4. EmployeeProjects

EmpID ProjID HoursWorked


INT INT INT

Q1.
Create the Employees table with appropriate constraints including primary key, foreign
key (for DeptID), check (for Status), and unique constraint on EmpName.
(4 Marks)

Q2.
Write an SQL query to update the Status of employees to 'Resigned' who have not
worked on any project and joined before 2020.
(2 Marks)
Q3.
Write an SQL command to delete all employees whose salary is less than the average
salary of their department.
(2 Marks)

Q4.
List all employees whose bonus is greater than 10% of their salary using appropriate
arithmetic and logical operators.
(2 Marks)

Q5.
Insert a record into the Projects table with NULL EndDate and ensure it violates no
constraints.
(2 Marks)

Q6.
Explain the difference between DELETE, TRUNCATE, and DROP with examples.
(3 Marks)

Q7.
Write an SQL query to display each department’s name, number of employees, and
average salary using aggregate functions and GROUP BY.
(3 Marks)

Q8.
List employees who have worked more than 100 hours in total across all projects
using JOIN and GROUP BY.
(3 Marks)

Q9.
Find employees who worked on all projects assigned to their department using
subqueries or HAVING clause.
(3 Marks)

Q10.
Write a query to fetch the top 3 highest paid employees in each department using a
window function or correlated subquery.
(3 Marks)

Q11.
Display the name of employees who have not worked on any project using LEFT
JOIN.
(3 Marks)

Q12.
Create a Stored Procedure that accepts DeptID as input and returns the list of
employees in that department along with their total hours worked.
(5 Marks)
Q13.
Write a Trigger that prevents inserting an employee with a salary less than 3000.
(4 Marks)

Q14.
Create a CTE to calculate the cumulative bonus of employees ordered by JoinDate,
and display only those whose cumulative bonus exceeds 10000.
(4 Marks)

Q15.
Create a View ActiveEmployeesView that displays only active employees along with
their department name.
(3 Marks)

Q16.
Using a subquery, find the employee(s) whose salary is equal to the second highest
salary in the company.
(3 Marks)

Q17.
Create a Stored Procedure to assign a new project to all employees of a particular
department. The procedure should take DeptID and ProjID as input and insert
appropriate records into EmployeeProjects.
(5 Marks)

Q18.
Create a Trigger that updates the status of an employee to ‘Active’ when their salary
is increased by more than 20%.
(3 Marks)

You might also like