StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POOracle SQL sample database
text
Body
copied!<p>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:</p> <pre><code>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) </code></pre> <p>Now I would like to Select employees that earn most in their department and salgrade.</p> <p>I wrote something like this one:</p> <pre><code>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 </code></pre> <p>But it's not working properly. The output is:</p> <pre><code>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 </code></pre> <p>Note rows:</p> <pre><code>WARD 2 SALES MARTIN 2 SALES </code></pre> <p>2 people from same department and salgrade.</p> <p>Could you point me my mistakes?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload