Activity Description

Given following two tables (EMPLOYEE, DEPARTMENT). Note, Dno in table

EMPLOYEE and Dnumber in table DEPARTMENT all refer to the same thing (department number).

CREATE TABLE EMPLOYEE

( Fname VARCHAR(15)

Minit CHAR,

Lname VARCHAR(15),

Ssn CHAR(9),

Bdate DATE,

Address VARCHAR(30),

Sex CHAR,

Salary DECIMAL(10,2),

Super_ssn CHAR(9),

Dno INT,

PRIMARY KEY (Ssn));

CREATE TABLE DEPARTMENT

( Dname VARCHAR(15),

Dnumber INT,

Mgr_ssn CHAR(9),

Mgr_start_date DATE,

PRIMARY KEY (Dnumber));

1. Write a single SQL statement to find the sum of the salaries of all employees of the Engineering department, as well as the maximum salary, the minimum salary, and the average salary in this department.
2. Based on Employee table, write a single SQL statement for each department, retrieve the department number, the number of employees in the department, and their average salary.