SQL Test Paper for Database Developers
Uploaded by
varshacpatilfctSQL Test Paper for Database Developers
Uploaded by
varshacpatilfctCommon questions
Powered by AITo update the status of employees who have not worked on any projects and joined before 2020, an SQL query should utilize a subquery or a LEFT JOIN. This query can check for employees without matching entries in EmployeeProjects by using a LEFT JOIN on the EmployeeProjects table and filtering for NULL in ProjID. Additionally, include a condition that filters JoinDate to before 2020, thereby targeting the correct group of employees .
A stored procedure for assigning projects would accept DeptID and ProjID as inputs and employ a cursor or straightforward INSERT INTO ... SELECT query to add entries in the EmployeeProjects table. Integrating concepts of transaction control ensures all related operations are committed or rolled back together if an error occurs, ensuring data integrity. Conditional logic in the procedure ensures only active employees or those matching specific criteria are selected for assignment .
A CTE is significant in calculating cumulative bonuses as it allows for incremental calculations while improving query readability and structure. A CTE can be implemented using the WITH clause, calculating the running total of bonuses ordered by JoinDate through the use of window functions like ROW_NUMBER() and SUM() OVER(ORDER BY JoinDate). This approach efficiently handles cumulative aggregations and improves performance over complex subquery alternatives .
DELETE removes rows from a table based on a WHERE clause, allowing for conditional row deletion. It's transactional and can be rolled back. TRUNCATE removes all rows from a table, resets any auto-increment counters, and usually doesn't log individual row deletions, making it faster but non-transactional. DROP removes the table structure and data, making it irreversible. Use DELETE for removing specific records, TRUNCATE for a complete and fast cleanup while keeping table structure, and DROP for complete deletion of the table including its structure .
The Employees table can be structured with a primary key constraint on EmpID, ensuring each employee has a unique identifier. A foreign key constraint on DeptID links each employee to a valid department, which must exist in the Departments table. For the Status column, a check constraint should ensure the status is either 'Active' or 'Resigned', providing data integrity for employment status. Additionally, a unique constraint on EmpName ensures no two employees have the same name, which could be necessary for business logic depending on database requirements .
Identifying the top 3 highest-paid employees in each department involves using window functions. The ROW_NUMBER() function can be applied over a partitioned result set, with partitioning based on DeptID and ordering by Salary in descending order, to rank employees. A subquery or CTE can then filter for rows where rank is less than or equal to 3, extracting the top compensation packages by department .
A trigger that prevents the insertion of an employee with a salary less than 3000 needs to effectively manage rollback operations automatically if an insertion occurs without requiring explicit control flow manageable in an application. The challenge lies in ensuring that the trigger operates efficiently without significantly impacting database performance, particularly under high insert loads, and does not interfere with other processes if data correction or exceptions are needed for specific business scenarios .
To find employees who have not participated in any project, a LEFT JOIN between the Employees and EmployeeProjects tables can be performed, linking on EmpID. By selecting records with NULL entries in the ProjID from the joined table, only employees without any projects are listed, indicating those who have not worked on any projects .
Creating a View such as ActiveEmployeesView involves writing a SELECT query that joins Employees and Departments tables on DeptID, filtering for employees with a Status of 'Active'. Using a view simplifies query reuse, enhances security by abstracting complex joins and conditions, and presents a consistent interface that's easier for end-users or applications to query directly for reporting or operational needs .
A trigger set to execute after an UPDATE event on the Employees table can monitor salary changes. It checks if the new salary exceeds the old salary by more than 20% using OLD and NEW references in trigger logic. If true, it updates the Status to 'Active'. Implications include maintaining data consistency and automatically enforcing business rules but require careful management to prevent unintended multiple trigger firings during batch updates .