Practice Arena
[Exp 1] Department & Employee Relational Analytics (Multi-Table JOINs)
UNIT-II Rubric: 10m Code + 5m Viva + 5m Record EASY 10 Marks
Session: 59:59
Bubblewrap Sandbox Active
Problem Statement
Write an SQL query using an `INNER JOIN` (or `LEFT JOIN`) between `departments` and `employees` to retrieve each department's name, total employee count, and the maximum salary earned in that department. Query Requirements: 1. Select columns: `dept_name`, `total_employees`, `max_salary`. 2. Group records by department name. 3. Filter for departments that have at least 1 employee. 4. Order results by `max_salary` descending.
Input Format
Relational schema `departments` (dept_id, dept_name, location) and `employees` (emp_id, name, salary, dept_id).
Output Format
Tabular result set containing dept_name, total_employees, and max_salary ordered by max_salary DESC.
Constraints
Standard SQL (ANSI-92/MySQL compatible), execution inside isolated sandbox schema.
Database Schema & Initial Records
CREATE TABLE departments (dept_id INT PRIMARY KEY, dept_name TEXT, location TEXT); CREATE TABLE employees (emp_id INT PRIMARY KEY, name TEXT, salary REAL, dept_id INT); INSERT INTO departments VALUES (10, 'Computer Science', 'Visakhapatnam'), (20, 'Data Analytics', 'Vizianagaram'), (30, 'Information Tech', 'Kakinada'); INSERT INTO employees VALUES (101, 'K. Ramesh', 92000, 10), (102, 'P. Sunitha', 84000, 10), (103, 'M. Suresh', 76000, 20), (104, 'T. Lakshmi', 98000, 20), (105, 'V. Anitha', 65000, 30);
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