Write SQL statement for the following using table structure:

- List the last names of all department managers who have no dependents.

-For each department whose average employee salary is more than $30,000, retrieve the department name and the number of employees working for that department.

- Find the names and addresses of all employees who work on at least one project located in Houston but whose department has no location in Houston.

DROP TABLE employee CASCADE CONSTRAINTS;

CREATE TABLE employee (

fname varchar2(15) not null,

minit varchar2(1),

lname varchar2(15) not null,

ssn char(9),

bdate date,

address varchar2(30),

sex char,

salary number(10,2),

superssn char(9),

dno number(4),

primary key (ssn)

--, foreign key (superssn) references employee(ssn)

--, foreign key (dno) references department(dnumber)

);

DROP TABLE department CASCADE CONSTRAINTS;

CREATE TABLE department (

dname varchar2(15) not null,

dnumber number(4),

mgrssn char(9) not null,

mgrstartdate date,

primary key (dnumber),

unique (dname)

-- ,foreign key (mgrssn) references employee(ssn)

);

DROP TABLE dept_locations CASCADE CONSTRAINTS;

CREATE TABLE dept_locations (

dnumber number(4),

dlocation varchar2(15),

primary key (dnumber,dlocation)

-- , foreign key (dnumber) references department(dnumber)

);

DROP TABLE project CASCADE CONSTRAINTS;

CREATE TABLE project (

pname varchar2(15) not null,

pnumber number(4),

plocation varchar2(15),

dnum number(4) not null,

primary key (pnumber),

unique (pname)

-- ,foreign key (dnum) references department(dnumber)

);

DROP TABLE works_on CASCADE CONSTRAINTS;

CREATE TABLE works_on (

essn char(9),

pno number(4),

hours number(4,1),

primary key (essn,pno)

-- ,foreign key (essn) references employee(ssn)

-- ,foreign key (pno) references project(pnumber)

);

DROP TABLE dependent CASCADE CONSTRAINTS;

CREATE TABLE dependent (

essn char(9),

dependent_name varchar2(15),

sex char,

bdate date,

relationship varchar2(8),

primary key (essn,dependent_name)

-- ,foreign key (essn) references employee(ssn)

);