SQL 课后作业答案 练习2 练习6

Exercise2

1. Find the name and salary of employees in Luton.

SELECT ename,sal
FROM emp e,dept2020290223 d
WHERE loc='Luton' AND e.deptno=d.deptno

 2. Join the DEPT table to the EMP table and show in department number
order.

SELECT *FROM emp e NATURAL JOIN dept2020290223 d
ORDER BY deptno

3. List the names of all salesmen who work in SALES
 

SELECT ename
FROM emp e NATURAL JOIN dept2020290223 d
WHERE job='SALESMAN' AND DNAME='SALES'

 4. List all departments that do not have any employees.

SELECT d.deptno
FROM dept2020290223 d
WHERE deptno NOT IN (SELECT deptno FROM emp NATURAL JOIN
dept2020290223)

 5. For each employee whose salary exceeds his manager's salary, list the
employee's name and salary and the manager's name and salary.

SELECT employee.ename,employee.sal,manager.ename,manager.sal
FROM emp employee,emp manager
WHERE employee.sal>manager.sal AND employee.mgr=manager.empno

6. List the employees who have BLAKE as their manager.

SELECT employee.ename
FROM emp employee,emp manager
WHERE employee.mgr=manager.empno AND manager.ename='BLAKE'

 Exercise 6:

1. List the name and job of employees who have the same job as Jones.

SELECT e1.ename, e1.job
FROM emp e1,emp e2
WHERE e1.job=e2.job AND e2.ename='Jones' AND e1.ename != 'Jones'

2. Find all the employees in Department 10 that have a job that is the
same as anyone in department 30.

SELECT d10.ename, d10.job
FROM emp d10,emp d30WHERE d10.job = d30.job AND d30.deptno=30 AND d10.deptno=10

3. List the name, job, and department of employees who have the same
job as Jones or a salary greater than or equal to Ford.

SELECT ename,job,deptno
FROM emp
WHERE job IN (SELECT job FROM emp WHERE ename='Jones') AND
ename!="Jones" OR
sal>=(SELECT sal FROM emp WHERE ename='Ford') AND ename!="Ford"

4. Find all employees in department 10 that have a job that is the same as
anyone in the Sales department.

SELECT ename,job,deptno
FROM emp
WHERE deptno=10 AND job IN (SELECT job FROM emp WHERE deptno IN
(SELECT deptno FROM dept2020290223 WHERE dname='Sales') )

5. Find the employees located in Liverpool who have the same job as
Allen. Return the results in alphabetical order by employee name.
 

SELECT ename,loc
FROM emp NATURAL JOIN dept2020290223
WHERE loc='Liverpool' AND job IN (SELECT job FROM emp WHEREename='Allen') AND ename!='Allen'
ORDER BY ename

6. Find all the employees that earn more than the average salary of
employees in their department.

SELECT ename,sal,e.deptno
FROM emp e,(SELECT AVG(sal) sal_avg,deptno FROM emp GROUP BY deptno)
WHERE e.deptno=a.deptno AND sal>sal_avg

7. Find all the employees that earn more than JONES, using temporary
labels to abbreviate table names.

SELECT ename,sal
FROM emp e
WHERE sal>(SELECT sal FROM emp WHERE ename='Jones')