Create a Department Summary View

Mediumsql

Write a CREATE VIEW statement named department_summary with columns department, headcount (employee count), and avg_salary (integer average salary, using SUM(salary) DIV COUNT(*)), one row per department. The checker will query your view afterward.

sql
CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  department TEXT NOT NULL,
  salary INTEGER NOT NULL
);

Sample data:

sql
INSERT INTO employees (id, name, department, salary) VALUES
(1, 'Asha', 'Engineering', 90000),
(2, 'Rohan', 'Engineering', 60000),
(3, 'Neha', 'Sales', 70000),
(4, 'Vikram', 'Sales', 50000);

Example 1

Input

(none)

Output

department	headcount	avg_salary
Engineering	2	75000
Sales	2	60000

A complex view can involve GROUP BY and aggregate functions, unlike a simple single-table view. Reference: CREATE VIEW department_summary AS SELECT department, COUNT(*) AS headcount, SUM(salary) DIV COUNT(*) AS avg_salary FROM employees GROUP BY department;

Create a Department Summary View

Related Problems

Input (stdin)

Output