Oracle SQL示例数据库 [英] Oracle SQL sample database
问题描述
我正在尝试通过它提供的数据库学习Oracle SQL. 我在某处发现了要完成的任务. 数据库结构由Oracle提供:
I'm trying to learn Oracle SQL by database Supplied by it. I found somewhere tasks to be done. Database structure is supplied by Oracle:
CREATE TABLE EMP
(EMPNO NUMERIC(4) NOT NULL,
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR NUMERIC(4),
HIREDATE DATETIME,
SAL NUMERIC(7, 2),
COMM NUMERIC(7, 2),
DEPTNO NUMERIC(2))
INSERT INTO EMP VALUES
(7369, 'SMITH', 'CLERK', 7902, '17-DEC-1980', 800, NULL, 20)
INSERT INTO EMP VALUES
(7499, 'ALLEN', 'SALESMAN', 7698, '20-FEB-1981', 1600, 300, 30)
INSERT INTO EMP VALUES
(7521, 'WARD', 'SALESMAN', 7698, '22-FEB-1981', 1250, 500, 30)
INSERT INTO EMP VALUES
(7566, 'JONES', 'MANAGER', 7839, '2-APR-1981', 2975, NULL, 20)
INSERT INTO EMP VALUES
(7654, 'MARTIN', 'SALESMAN', 7698, '28-SEP-1981', 1250, 1400, 30)
INSERT INTO EMP VALUES
(7698, 'BLAKE', 'MANAGER', 7839, '1-MAY-1981', 2850, NULL, 30)
INSERT INTO EMP VALUES
(7782, 'CLARK', 'MANAGER', 7839, '9-JUN-1981', 2450, NULL, 10)
INSERT INTO EMP VALUES
(7788, 'SCOTT', 'ANALYST', 7566, '09-DEC-1982', 3000, NULL, 20)
INSERT INTO EMP VALUES
(7839, 'KING', 'PRESIDENT', NULL, '17-NOV-1981', 5000, NULL, 10)
INSERT INTO EMP VALUES
(7844, 'TURNER', 'SALESMAN', 7698, '8-SEP-1981', 1500, 0, 30)
INSERT INTO EMP VALUES
(7876, 'ADAMS', 'CLERK', 7788, '12-JAN-1983', 1100, NULL, 20)
INSERT INTO EMP VALUES
(7900, 'JAMES', 'CLERK', 7698, '3-DEC-1981', 950, NULL, 30)
INSERT INTO EMP VALUES
(7902, 'FORD', 'ANALYST', 7566, '3-DEC-1981', 3000, NULL, 20)
INSERT INTO EMP VALUES
(7934, 'MILLER', 'CLERK', 7782, '23-JAN-1982', 1300, NULL, 10)
CREATE TABLE DEPT
(DEPTNO NUMERIC(2),
DNAME VARCHAR(14),
LOC VARCHAR(13) )
INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK')
INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS')
INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO')
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON')
CREATE TABLE BONUS
(ENAME VARCHAR(10),
JOB VARCHAR(9),
SAL NUMERIC,
COMM NUMERIC)
CREATE TABLE SALGRADE
(GRADE NUMERIC,
LOSAL NUMERIC,
HISAL NUMERIC)
INSERT INTO SALGRADE VALUES (1, 700, 1200)
INSERT INTO SALGRADE VALUES (2, 1201, 1400)
INSERT INTO SALGRADE VALUES (3, 1401, 2000)
INSERT INTO SALGRADE VALUES (4, 2001, 3000)
INSERT INTO SALGRADE VALUES (5, 3001, 9999)
现在，我想选择在本部门和薪水最高的员工.
Now I would like to Select employees that earn most in their department and salgrade.
我写了这样的东西:
select ename, salgrade.grade, dept.dname from emp, salgrade, dept
WHERE emp.sal BETWEEN salgrade.losal AND salgrade.hisal
AND emp.deptno = dept.deptno group by salgrade.grade, dept.dname, emp.ename
但是它不能正常工作. 输出为:
But it's not working properly. The output is:
ENAME GRADE DNAME
SMITH 1 RESEARCH
BLAKE 4 SALES
FORD 4 RESEARCH
KING 5 ACCOUNTING
SCOTT 4 RESEARCH
MILLER 2 ACCOUNTING
TURNER 3 SALES
WARD 2 SALES
MARTIN 2 SALES
ADAMS 1 RESEARCH
JONES 4 RESEARCH
JAMES 1 SALES
CLARK 4 ACCOUNTING
ALLEN 3 SALES
注释行:
WARD 2 SALES
MARTIN 2 SALES
2个来自同一部门和薪水的人.
2 people from same department and salgrade.
你能指出我的错误吗?
推荐答案
您没有过滤查询，这就是为什么要显示所有员工的原因.
You are not filtering your query this is why you have all the employees displayed.
这将过滤收入低于其部门/职等最高工资的员工:
This would filter the employees that earn less than the max for their dept/grade:
SELECT ename, salgrade.grade, dept.dname
FROM emp, salgrade, dept
WHERE emp.sal BETWEEN salgrade.losal AND salgrade.hisal
AND emp.deptno = dept.deptno
AND emp.sal = (SELECT MAX(sal)
FROM emp emp_in, salgrade grade_in
WHERE emp_in.sal BETWEEN grade_in.losal AND grande_in.hisal
AND emp_in.deptno = emp.deptno
AND grade_in.losal = salgrade.losal)
您仍然会发现重复的内容，例如，有两个销售人员获得了2级的最高薪水(马丁和沃德都赚了1250).这是可以接受的，还是您需要一些其他条件来仅选择其中一个.
You will still find duplicates because for instance, two people in sales earn the max salary for grade 2 (both Martin and Ward earn 1250). Either this is acceptable or you need some other criteria to only select one of them.
您可以使用row_number分析函数来确保按年级/部门只返回一行(请注意，如果重复，Oracle将选择任意一行):
You can use the row_number analytic function to ensure that only one row is returned by grade/dept (note that Oracle will select arbitrarily one row when there are duplicates) :
SELECT * FROM (
SELECT ename, salgrade.grade, dept.dname,
row_number() OVER (PARTITION BY dept.deptno, salgrade.grade
ORDER BY emp.sal DESC) rnk
FROM emp, salgrade, dept
WHERE emp.sal BETWEEN salgrade.losal AND salgrade.hisal
AND emp.deptno = dept.deptno
) WHERE rnk = 1;
ENAME GRADE DNAME RNK
---------- ------ -------------- ---
MILLER 2 ACCOUNTING 1
CLARK 4 ACCOUNTING 1
KING 5 ACCOUNTING 1
ADAMS 1 RESEARCH 1
FORD 4 RESEARCH 1
JAMES 1 SALES 1
MARTIN 2 SALES 1
ALLEN 3 SALES 1
BLAKE 4 SALES 1
这篇关于Oracle SQL示例数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
