Practice Arena
[Exp 2] High-Earner Department Analytics (Correlated Subqueries)
UNIT-III Rubric: 15m Code + 5m Viva + 5m Record MEDIUM 15 Marks
Session: 59:59
Bubblewrap Sandbox Active
Problem Statement
Write an SQL query using a correlated subquery to identify all employees whose salary strictly exceeds the average salary of their respective department. Query Requirements: 1. Select columns: `emp_id`, `name`, `salary`, `dept_id`. 2. Filter condition: `salary > (SELECT AVG(salary) FROM employees WHERE dept_id = outer.dept_id)`. 3. Order results by `emp_id` ascending.
Input Format
Relational table `employees` (emp_id, name, salary, dept_id).
Output Format
List of employees earning above departmental average.
Constraints
Standard SQL correlated subquery.
Database Schema & Initial Records
CREATE TABLE departments (dept_id INT PRIMARY KEY, dept_name TEXT); CREATE TABLE employees (emp_id INT PRIMARY KEY, name TEXT, salary REAL, dept_id INT); INSERT INTO departments VALUES (10, 'Computer Science'), (20, 'Data Analytics'); INSERT INTO employees VALUES (101, 'K. Ramesh', 92000, 10), (102, 'P. Sunitha', 84000, 10), (103, 'M. Suresh', 76000, 20), (104, 'T. Lakshmi', 98000, 20);
Status Lang Score Time Submitted
No prior submissions for this problem.
Ready
Click Run Code to evaluate against sample test cases, or Submit Solution for full evaluation.
Instructor Guidance