Complete and full answer in order to get credit Thank u The

Complete and full answer in order to get credit .Thank u

The SQL statements comprising the DDL for the COMPANY Database (employee,department,project,dependent,etc...). Remember..just SQL

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

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

8. Retrieve the names of all employees who work in the department that has the employee with the highest salary among all employees.

9.

[9A] Create a View ProjectInfo that has the project name, controlling department name, number of employees, and total hours worked per week on the project for each project with more than one employee working on it.

[9B] Show the contents of this view.

10. Retrieve the names of employees who make at least $10,000 more than the employee who is paid the least in the Company.

Solution

6. select fname,lname from employee e,project p,works_on w where e.ssn=w.ssn and w.pno=p.pnumber and p.location=\'HOSTON\' and NOT(select dept_name from department d where e.dno=d.deptno and d.location=\'HOSTON\'));

---> This query first executes the sub query which gives the department names of the employees where the department location is hoston.

Then when the execution comes to main query it retrieves fname,lname of the employees whose project location is hoston then it eliminates the employees who is retrieved from the subquery result as we have put the condition not.i.e.

To be clear it first retrieves all the employees information whose project is having location hoston then it eliminates the information of employees whose departnment is in hoston gives the required result.

7. select deptname,count(*) from employee e,department e where e.dno=d.deptno and e.sex=\'M\' and d.dno in(select dno from employee group by dno having avg(sal)>30000) group by d.dname;

8. select fname,lname from employee where dno=(select dno from employee where sal=(select max(sal) from employee));