Oracle SQL 示例数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8789485/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle SQL sample database
提问by mic-kul
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:
我正在尝试通过它提供的数据库来学习 Oracle SQL。我找到了要完成的任务。数据库结构由 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.
现在我想选择在他们的部门和salgrade中收入最高的员工。
I wrote something like this one:
我写了这样一个：
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
Note rows:
注意行：
WARD 2 SALES
MARTIN 2 SALES
2 people from same department and salgrade.
来自同一部门和 Salgrade 的 2 人。
Could you point me my mistakes?
你能指出我的错误吗？
采纳答案by Vincent Malgrat
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)
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.
您仍然会发现重复项，因为例如，两个销售人员赚取 2 级的最高工资（Martin 和 Ward 均赚取 1250）。这是可以接受的，或者您需要一些其他标准来仅选择其中之一。
You can use the row_numberanalytic function to ensure that only one row is returned by grade/dept (note that Oracle will select arbitrarily one row when there are duplicates) :
可以使用row_number解析函数保证grade/dept只返回一行（注意，有重复时Oracle会任意选择一行）：
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
回答by mic-kul
You are grouping by employee name, as well as department and salary grade. That means you will return a row for every combination of employee name, department and salary grade in your dataset.
您正在按员工姓名以及部门和工资等级分组。这意味着您将为数据集中的员工姓名、部门和工资等级的每个组合返回一行。
To ensure that you only return one row per department and salary grade, you will need to remove the employee name from the group by clause. This will enable you to find the maximum salary per grade and department, but not which employees have that salary - to do that, you need to join the results back to the employee table again, like so:
为确保每个部门和工资等级只返回一行，您需要从 group by 子句中删除员工姓名。这将使您能够找到每个级别和部门的最高薪水，但不能找到哪些员工拥有该薪水 - 要做到这一点，您需要再次将结果连接回员工表，如下所示：
select e.ename, s.grade, d.dname, e.salary
from (select max(emp.salary) max_salary,
salgrade.grade,
emp.deptno
from emp, salgrade
WHERE emp.sal BETWEEN salgrade.losal AND salgrade.hisal
group by salgrade.grade, dept.dname) s
join emp e on e.salary = s.max_salary
join dept d on e.deptno = d.deptno
Note that if multiple employees in the same department are earning the same maximum salary within their grade, then both employees will be returned - this should happen with FORD and SCOTT in RESEARCH.
请注意，如果同一部门的多名员工在其级别内获得相同的最高工资，那么这两名员工都将被退回——这应该发生在 RESEARCH 的 FORD 和 SCOTT 身上。
回答by aF.
With your `select:
使用您的`选择：
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
when you group by salgrade.grade, dept.dname, emp.enamethe results will be grouped into those three values. You also put the results BETWEEN salgrade.losal AND salgrade.hisalso it will give all employeesthat have the salary in that interval. It's not restricting anything to the greater one got it? And that's why you have WARD 2 SALESand MARTIN 2 SALES.
当您group by salgrade.grade, dept.dname, emp.ename将结果分组为这三个值时。你也把结果，BETWEEN salgrade.losal AND salgrade.hisal所以它会给出所有employees在该时间间隔内的工资。它不限制任何东西到更大的人得到它吗？这就是为什么你有WARD 2 SALES和MARTIN 2 SALES。
回答by 07sunburst
SELECT SUB2.ENAME , SUB2.DNAME , S.GRADE ,SUB2.SAL
FROM SALGRADE S,
(SELECT E.ENAME,E.SAL,D.DNAME
FROM EMP E, DEPT D,
(SELECT DEPTNO, MAX(SAL) AS "MAX"
FROM EMP
GROUP BY DEPTNO) SUB1
WHERE E.SAL=SUB1.MAX
AND E.DEPTNO=D.DEPTNO) SUB2
WHERE SUB2.SAL BETWEEN S.LOSAL AND S.HISAL
回答by sachin shejekar
Screen Shot////// Try this out
屏幕截图///// 试试这个
SELECT E.EMPNO, E.ENAME, E.JOB, D.DNAME, E.SAL, E.DEPTNO, S.GRADE
FROM EMP E, SALGRADE S, DEPT D
WHERE E.SAL IN (SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO)
AND E.SAL BETWEEN S.LOSAL AND S.HISAL
AND E.DEPTNO = D.DEPTNO
ORDER BY E.SAL DESC
回答by Robert Curtis
When I created this I used this format to make it easier to read and modify it is for an Oracle format
当我创建它时，我使用了这种格式以使其更易于阅读和修改，适用于 Oracle 格式
CREATE TABLE EMP
(EMP_NO NUMBER(4) NOT NULL PRIMARY KEY,
E_NAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIRE_DATE DATE,
SAL DECIMAL(7, 2),
COMM DECIMAL(7, 2),
DEPT_NO NUMBER(2));
SELECT *
FROM EMP
INSERT INTO EMP
(EMP_NO, E_NAME, JOB, MGR, HIRE_DATE, SAL, COMM, DEPT_NO)
VALUES
(7369, 'SMITH', 'CLERK', 7902, '17-DEC-1980', 800, NULL, 20);
INSERT INTO EMP
(EMP_NO, E_NAME, JOB, MGR, HIRE_DATE, SAL, COMM, DEPT_NO)
VALUES
(7499, 'ALLEN', 'SALESMAN', 7698, '20-FEB-1981', 1600, 300, 30);
INSERT INTO EMP
(EMP_NO, E_NAME, JOB, MGR, HIRE_DATE, SAL, COMM, DEPT_NO)
VALUES
(7521, 'WARD', 'SALESMAN', 7698, '22-FEB-1981', 1250, 500, 30);
INSERT INTO EMP
(EMP_NO, E_NAME, JOB, MGR, HIRE_DATE, SAL, COMM, DEPT_NO)
VALUES
(7566, 'JONES', 'MANAGER', 7839, '02-APR-1981', 2975, NULL, 20);
INSERT INTO EMP
(EMP_NO, E_NAME, JOB, MGR, HIRE_DATE, SAL, COMM, DEPT_NO)
VALUES
(7654, 'MARTIN', 'SALESMAN', 7698, '28-SEP-1981', 1250, 1400, 30);
INSERT INTO EMP
(EMP_NO, E_NAME, JOB, MGR, HIRE_DATE, SAL, COMM, DEPT_NO)
VALUES
(7698, 'BLAKE', 'MANAGER', 7839, '01-MAY-1981', 2850, NULL, 30);
INSERT INTO EMP
(EMP_NO, E_NAME, JOB, MGR, HIRE_DATE, SAL, COMM, DEPT_NO)
VALUES
(7782, 'CLARK', 'MANAGER', 7839, '09-JUN-1981', 2450, NULL, 10);
INSERT INTO EMP
(EMP_NO, E_NAME, JOB, MGR, HIRE_DATE, SAL, COMM, DEPT_NO)
VALUES
(7788, 'SCOTT', 'ANALYST', 7566, '09-DEC-1982', 3000, NULL, 20);
INSERT INTO EMP
(EMP_NO, E_NAME, JOB, MGR, HIRE_DATE, SAL, COMM, DEPT_NO)
VALUES
(7839, 'KING', 'PRESIDENT', NULL, '17-NOV-1981', 5000, NULL, 10);
INSERT INTO EMP
(EMP_NO, E_NAME, JOB, MGR, HIRE_DATE, SAL, COMM, DEPT_NO)
VALUES
(7844, 'TURNER', 'SALESMAN', 7698, '08-SEP-1981', 1500, 0, 30);
INSERT INTO EMP
(EMP_NO, E_NAME, JOB, MGR, HIRE_DATE, SAL, COMM, DEPT_NO)
VALUES
(7876, 'ADAMS', 'CLERK', 7788, '12-JAN-1983', 1100, NULL, 20);
INSERT INTO EMP
(EMP_NO, E_NAME, JOB, MGR, HIRE_DATE, SAL, COMM, DEPT_NO)
VALUES
(7900, 'JAMES', 'CLERK', 7698, '03-DEC-1981', 950, NULL, 30);
INSERT INTO EMP
(EMP_NO, E_NAME, JOB, MGR, HIRE_DATE, SAL, COMM, DEPT_NO)
VALUES
(7902, 'FORD', 'ANALYST', 7566, '03-DEC-1981', 3000, NULL, 20);
INSERT INTO EMP
(EMP_NO, E_NAME, JOB, MGR, HIRE_DATE, SAL, COMM, DEPT_NO)
VALUES
(7934, 'MILLER', 'CLERK', 7782, '23-JAN-1982', 1300, NULL, 10);
CREATE TABLE DEPT
(DEPT_NO NUMERIC(2),
D_NAME VARCHAR(14),
LOC VARCHAR(13) );
INSERT INTO DEPT
(DEPT_NO, D_NAME, LOC)
VALUES
(10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT
(DEPT_NO, D_NAME, LOC)
VALUES
(20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT
(DEPT_NO, D_NAME, LOC)
VALUES
(30, 'SALES', 'CHICAGO');
INSERT INTO DEPT
(DEPT_NO, D_NAME, LOC)
VALUES
(40, 'OPERATIONS', 'BOSTON');
CREATE TABLE BONUS
(E_NAME VARCHAR(10),
JOB VARCHAR(9),
SAL NUMERIC,
COMM NUMERIC);
CREATE TABLE SAL_GRADE
(GRADE NUMERIC,
LO_SAL NUMERIC,
HI_SAL NUMERIC);
INSERT INTO SAL_GRADE
(GRADE, LO_SAL, HI_SAL)
VALUES
(1, 700, 1200);
INSERT INTO SAL_GRADE
(GRADE, LO_SAL, HI_SAL)
VALUES
(2, 1201, 1400);
INSERT INTO SAL_GRADE
(GRADE, LO_SAL, HI_SAL)
VALUES
(3, 1401, 2000);
INSERT INTO SAL_GRADE
(GRADE, LO_SAL, HI_SAL)
VALUES
(4, 2001, 3000);
INSERT INTO SAL_GRADE
(GRADE, LO_SAL, HI_SAL)
VALUES
(5, 3001, 9999);
