Question

Using the Company database in Oracle, construct SQL queries for the following (note: I will make the files to create and populate the Company database available on Isadore shortly):

1. List the last name and address of managers who have a dependent with the same first name as themselves.

2. Retrieve the names of all employees who are directly supervised by ‘Franklin Wong’.

3. Retrieve the names of employees in the Research department who work more than 20 hours per week on the 'Computerization' project.

4. List the last names of all supervisors who have no dependents.

5. For each department, retrieve the department name and the minimum salary for all employees working in that department.

6. 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.

7. Retrieve the average salary of female employees who work more than 20 hours on projects.

8. Retrieve the names of employees who work on every project that John Smith works on.

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

10. Create a view that has the employee name, supervisor name, and employee salary for each employee who works in the ‘Research’ department.

11. Create a view that has project name, controlling department number, number of employees, and total hours worked per week on the project (group by project name).

Answer 1

1.For an employee database that looks as follows, P,ease find all the queries mentioned below.

Select lname, address 
from Employee as e
Inner join dependent as d
Using (emp_id)
where e.last_name=d.dependent_name and e.designation like 'Manager'

2.

Select fname, lname 
from employee as e
where e.superssn =(Select ssn from employee where fname like 'Franklin' and lname like 'Wong');

3.

select fname,lname from employee as e

inner join department as d

on 

e.dno=d.number

inner join works_on as w

on 
e.ssn=w.essn

inner join project as p
w.pno=p.pnumber

where d.dname like 'Research' and w.hours>20 and p.pname="Computerization";

4.

Select fname,lname 
from employee as e

where e.superssn is not null and e.ssn not in (select essn from dependent);

5.

select dname, min(salary) as Min_Salary
from department as d
inner join employee as e
on e.dno=d.dnumber
group by dname;

6.

select dname, count(ssn) as Count_Emp
from department as d
inner join employee as e
on e.dno=d.dnumber
where e.salary>30000
group by dname;

7.

select avg(salary) as avg_salary 
from employee as e
inner join works_on as w
on e.ssn=w.essn
where e.sex= 'Female' ;

8.

select fname,lname 
from employee as e
inner join works_on as w
on e.ssn=w.essn
where w.pno in (select pno from works_on as w
                    inner join employee as e 
                     on e.ssn=w.essn 
                     where e.fname='John' and lname='Smith');

9.

select fname,lname , adress from employee as e
inner join works_on as w
on e.ssn=w.essn
inner join project as p
on w.pno=p.pnumber
inner join dept_location as dl 
on e.dno=dl.dnumber
where
p.plocation like 'Houston' and dl.dlocation not like 'Houston' ;

10.

select fname,lname, sname, salary 
from employee as e 
inner join department as d
on e.dno=d.dnumber
where d.dname ='Research' ;

11.

Select pname,dnumber,count(ssn) as No_of_emp,hours 
from employees as e
inner join department as d
on d.dnumber=e.dno
inner join works_on as w
on w.essn=e.ssn
inner join project as p
on w.pno =p.pnumber
group by pname;

Please mention any doubts in comments.Thanks