For the following query draw the initial query tree and show

For the following query, draw the initial query tree and show how the query tree is optimized (one rule at a time).

List the firstname and lastname of employees who has a work assignment of at least 12 hours in any project that is controlled by the department where s/he is working for and earns salary of $15,000.

SELECT FName, LName FROM works_on w, project p, department d, employee e WHERE w.hours >= 12.0 and w.pno = p.pnumber and p.dnum =d.dnumber and d.dnumber = e.dno and e.ssn = w.essn and e.salary = 15000;

--Query

--Retrieve the name and address of all employees who work for the Research

--departement.

select fname, lname, address

from employee,deoartment

where dnumber=dno and

dname=\'Research\';

--Query

--For every project located stafford, list the project number, the

--controlling department mumber, and the department manager\'s last name,

--address, and birthdate.

select pnumber,dnum,address,bdate

from project,deparement,employee

where dnum=dnumber and mgrss=ssn and plocation=\'stafford\';

--Query

--Retrive the names of each employee who works in all projects controlled

--by department 5

select lname,fname

from employee

where not esists

selec*

from works_on b

where (b.pno in (select pnumber from project where dnum=5) and not exists (

select*

from works_on c

where ssn = c,essn and b.pno = c.pno));

--Query

--Make a list of all project number for projects that involve an employee whose

--last name is Smith either as a worker or as a manager of the department that

--controls the project.

(select distinct pnumber

from project,deparement,employee

where dnum=dnumber and mgrssn=ssn and lname=\'smith\')

union

(select pnumber

from project,works_on,employee

where pnumber=pno and essn=ssn and lname-\'smith\');

--Query

--Retrive the names of all employees who have two or more dependents.

select lname,fnamr

from employee

where (select count(*) from dependent where ssn=essn) >=2;

--Query

--Retrive the names of employees who have no dependents.

select fname,lname

from employee

where not exists(select * from dependent where ssn=essn);

-Query

List the names of managers who have at least one dependent.

select fname, lname

from employee

wher exists (Select * from department where ssn=mgrssn) and

exists (select * from dependent where ssn=essn);

Query

For each employee, retrive the employee\'s first and last name and the fist and last name of his or her immediate supervisor

select e.fname, e. lname, s. fname, s.lname

from employee as e, employee as a where e. superssn=s.ssn;

Query

retrive the salary of every employee

select all salary

from employee;

Query

Retrive the distinct salary values

Select distinct salary

from employee;

Query

Retrive all employees whose address is in houston, Texas

Select fname, lname

from employee\'

where address like \'%Houston, TX%\';

Query

Show the resulting salaries if every employee working on the product x project is given a 10% raise.

select fname, lname, 1.1*salar as increased_sal from employee, works_on,project

where ssn=essn and pno=pnumber and pname=\'productzX\';

Query

Retrive a list of employees and the projcts they are working on, ordered by department and, within each department, ordered alphabetically by last name, first name

select dname, lname, fname,pname

from department, employee,works_on , project

where dnumber=dno and ssn=essn and pno=pnumber order by dname, Lname, fname;

Query

Find the sum of the salaries of all employees, the maximum salary, the minimum salary, and the average salary.

select sum(salary), max(salary),min(salary), avg(salary) from employee

Query

Find the sum of the salaries of all employees of the research development, as well as the maximum salary, minimum salary, and the average salary in this department.

select sum(salary), max(salary), min(salary), avg(salary) from employee, department

where dno=dnumber and dname=\'Research\';

Query

Retrive the total number of employees in the company

seect count(*) from employee;

Query

Retrive the total number of employees in the research department.

Select Count(*)

from employee,department wher dno=dnumber and dname=\'Research\';

Query

COunt the number of distinct salary values in the database.

Select count(distinct salary) from employee;