CREATE TABLE Title (

Title_Code varchar(255) not null Primary Key,

Title_Discription varchar(255),

Salary Number(11));

insert into Title (Title_Code, Title_Discription, Salary) values

('T1' , 'Accountant' , '10000');

insert into Title (Title_Code, Title_Discription, Salary) values

('T2' , 'Analyst' , '20000');

insert into Title (Title_Code, Title_Discription, Salary) values

('T3' , 'Programmer' , '30000'),

insert into Title (Title_Code, Title_Discription, Salary) values

('T4' , 'DBA' , '40000');

insert into Title (Title_Code, Title_Discription, Salary) values

('T5' , 'Manager' , '50000');

CREATE TABLE Department (

dep_Code number(11) not null Primary Key,

dept_Name varchar(255),

location varchar(255),

Dept_Type varchar(255));

insert into Department(dep_Code,dept_name,location,Dept_Type) values

( '001' , 'Computer Center' , 'Boston' , 'IT');

insert into Department(dep_Code,dept_name,location,Dept_Type) values

( '002' , 'Budget' , 'New York' , 'Business');

insert into Department(dep_Code,dept_name,location,Dept_Type) values

( '003' , 'Marketing' , 'Boston' , 'Marketing');

insert into Department(dep_Code,dept_name,location,Dept_Type) values

( '004' , 'Database Support' , 'Cleveland' , 'IT');

insert into Department(dep_Code,dept_name,location,Dept_Type) values

( '005' , 'Purchasing' , 'New York' , 'Business');

CREATE TABLE Employee (

ID number(11) not null Primary Key,

emp_Name varchar(255),

empl_Type varchar(255),

dept_Code number(11) not null,

title_Code varchar(255) not null,

constraint fk_dep_Code

foreign key (dep_Code) references Department(dep_Code),

COnstraint fk_title_Code

foreign key (title_Code) references Title(title_Code));

insert into Employee(ID, emp_name,empl_Type,dept_Code,title_Code) values

('1' , 'John' , 'Full-time' , '002', 'T1');

insert into Employee(ID, emp_name,empl_Type,dept_Code,title_Code) values

('2' , 'Adam' , 'Consultant' , '001', 'T3');

insert into Employee(ID, emp_name,empl_Type,dept_Code,title_Code) values

('3' , 'Mary' , 'Part-time' , '004', 'T4');

insert into Employee(ID, emp_name,empl_Type,dept_Code,title_Code) values

('4' , 'Peter' , 'Full-time' , '003', 'T2');

insert into Employee(ID, emp_name,empl_Type,dept_Code,title_Code) values

('5' , 'Scott' , 'Consultant' , '002', 'T1');

insert into Employee(ID, emp_name,empl_Type,dept_Code,title_Code) values

('6' , 'Susan' , 'Full-time' , '005', 'T5');

insert into Employee(ID, emp_name,empl_Type,dept_Code,title_Code) values

('7' , 'Alex' , 'Part-time' , '004', 'T2');

Review of Subqueries 1. List names of employees whose salary is higher than the average salary in their department: a) use correlated subquery; b) subquery that returns department codes and average salaries in the departments 2. List names of employees who work in departments that have more than X employees