Select

SQL also has a standard.
In this section, we will practice SELECT statements with standard SQL.
The SELECT has the following order.

SELECT column, column ...
FROM table name
WHERE condition
GROUP BY statement
UNION/UNION ALL/INTERSECT/MINUS
ORDER BY statement

For Oracle, you must write up to FROM table_name.

All examples below use tables from the SCOTT account.
If you do not have a SCOTT account, refer to the following to create tables.

Oracle
CREATE TABLE DEPT (
	DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
	DNAME VARCHAR2(14),
	LOC VARCHAR2(13) 
);
CREATE TABLE EMP (
	EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
	ENAME VARCHAR2(10),
	JOB VARCHAR2(9),
	MGR NUMBER(4),
	HIREDATE DATE,
	SAL NUMBER(7,2),
	COMM NUMBER(7,2),
	DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT
);
CREATE TABLE SALGRADE ( 
	GRADE NUMBER,
	LOSAL NUMBER,
	HISAL NUMBER 
);
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');
INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-7-1987','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,to_date('13-7-1987', 'dd-mm-yyyy'),1100,NULL,20);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
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);
COMMIT;
MySQL
CREATE TABLE DEPT (
	DEPTNO DECIMAL(2),
	DNAME VARCHAR(14),
	LOC VARCHAR(13),
	CONSTRAINT PK_DEPT PRIMARY KEY (DEPTNO) 
);
CREATE TABLE EMP (
	EMPNO DECIMAL(4),
	ENAME VARCHAR(10),
	JOB VARCHAR(9),
	MGR DECIMAL(4),
	HIREDATE DATE,
	SAL DECIMAL(7,2),
	COMM DECIMAL(7,2),
	DEPTNO DECIMAL(2),
	CONSTRAINT PK_EMP PRIMARY KEY (EMPNO),
	CONSTRAINT FK_DEPTNO FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO)
);
CREATE TABLE SALGRADE ( 
	GRADE TINYINT,
	LOSAL SMALLINT,
	HISAL SMALLINT 
);
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');
INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,STR_TO_DATE('17-12-1980','%d-%m-%Y'),800,NULL,20);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,STR_TO_DATE('20-2-1981','%d-%m-%Y'),1600,300,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,STR_TO_DATE('22-2-1981','%d-%m-%Y'),1250,500,30);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,STR_TO_DATE('2-4-1981','%d-%m-%Y'),2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,STR_TO_DATE('28-9-1981','%d-%m-%Y'),1250,1400,30);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,STR_TO_DATE('1-5-1981','%d-%m-%Y'),2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,STR_TO_DATE('9-6-1981','%d-%m-%Y'),2450,NULL,10);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,STR_TO_DATE('13-7-1987','%d-%m-%Y')-85,3000,NULL,20);
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,STR_TO_DATE('17-11-1981','%d-%m-%Y'),5000,NULL,10);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,STR_TO_DATE('8-9-1981','%d-%m-%Y'),1500,0,30);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,STR_TO_DATE('13-7-1987', '%d-%m-%Y'),1100,NULL,20);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,STR_TO_DATE('3-12-1981','%d-%m-%Y'),950,NULL,30);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,STR_TO_DATE('3-12-1981','%d-%m-%Y'),3000,NULL,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,STR_TO_DATE('23-1-1982','%d-%m-%Y'),1300,NULL,10);
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);
COMMIT;

All the records in the employee table

SELECT * 
FROM EMP
SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO 
FROM EMP

The above two SQL statements fetch the same records. After select, * means all the columns in order.

     EMPNO ENAME      JOB	       MGR HIREDATE	   SAL	     COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7369 SMITH      CLERK	      7902 80/12/17	   800			  20
      7499 ALLEN      SALESMAN	      7698 81/02/20	  1600	      300	  30
      7521 WARD       SALESMAN	      7698 81/02/22	  1250	      500	  30
      7566 JONES      MANAGER	      7839 81/04/02	  2975			  20
      7654 MARTIN     SALESMAN	      7698 81/09/28	  1250	     1400	  30
      7698 BLAKE      MANAGER	      7839 81/05/01	  2850			  30
      7782 CLARK      MANAGER	      7839 81/06/09	  2450			  10
      7788 SCOTT      ANALYST	      7566 87/07/13	  3000			  20
      7839 KING       PRESIDENT 	   81/11/17	  5000			  10
      7844 TURNER     SALESMAN	      7698 81/09/08	  1500		0	  30
      7876 ADAMS      CLERK	      7788 87/07/13	  1100			  20
      7900 JAMES      CLERK	      7698 81/12/03	   950			  30
      7902 FORD       ANALYST	      7566 81/12/03	  3000			  20
      7934 MILLER     CLERK	      7782 82/01/23	  1300			  10

employee's name and hire date

SELECT ENAME,HIREDATE
FROM EMP;
ENAME	   HIREDATE
---------- --------
SMITH	   80/12/17
ALLEN	   81/02/20
WARD	   81/02/22
JONES	   81/04/02
MARTIN	   81/09/28
BLAKE	   81/05/01
CLARK	   81/06/09
SCOTT	   87/07/13
KING	   81/11/17
TURNER	   81/09/08
ADAMS	   87/07/13
JAMES	   81/12/03
FORD	   81/12/03
MILLER	   82/01/23

Department number and employee's name

SELECT DEPTNO,ENAME 
FROM EMP
    DEPTNO ENAME
---------- ----------
	20 SMITH
	30 ALLEN
	30 WARD
	20 JONES
	30 MARTIN
	30 BLAKE
	10 CLARK
	20 SCOTT
	10 KING
	30 TURNER
	20 ADAMS
	30 JAMES
	20 FORD
	10 MILLER

Jobs List

SELECT DISTINCT JOB
FROM
EMP
JOB
---------
CLERK
SALESMAN
PRESIDENT
MANAGER
ANALYST

DISTINCT let duplicate records to be shown only once in query results.

Total number of employees

SELECT COUNT(EMPNO)
FROM
EMP
COUNT(EMPNO)
------------
	  14

COUNT returns the number of records retrieved as one of the group functions. COUNT (column name) returns the number of non-NULL records for which that column is not NULL, and COUNT (*) returns the total number of them.

WHERE conditions

Employees working in a department with department number 10

SELECT * 
FROM EMP
WHERE DEPTNO = 10;
     EMPNO ENAME      JOB	       MGR HIREDATE	   SAL	     COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7782 CLARK      MANAGER	      7839 81/06/09	  2450			  10
      7839 KING       PRESIDENT 	   81/11/17	  5000			  10
      7934 MILLER     CLERK	      7782 82/01/23	  1300			  10

Employees receiving more than 2500

SELECT *
FROM EMP
WHERE SAL >= 2500;
     EMPNO ENAME      JOB	       MGR HIREDATE	   SAL	     COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7566 JONES      MANAGER	      7839 81/04/02	  2975			  20
      7698 BLAKE      MANAGER	      7839 81/05/01	  2850			  30
      7788 SCOTT      ANALYST	      7566 87/07/13	  3000			  20
      7839 KING       PRESIDENT 	   81/11/17	  5000			  10
      7902 FORD       ANALYST	      7566 81/12/03	  3000			  20

When you add the WEREE conditions to the query, you can fetch records matching the WHERE conditions. You can use =, >, >=, <=, < in the WHERE conditions.

Employee whose name is KING

SELECT *
FROM EMP
WHERE ENAME = 'KING';
     EMPNO ENAME      JOB	       MGR HIREDATE	   SAL	     COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7839 KING       PRESIDENT 	   81/11/17	  5000			  10

SQL statements are case-insensitive. However, the data in the column is, of course, case sensitive. It is stored as KING, but is not searched by king.

MySQL is case sensitive
Unlike Oracle, table and column names in MySQL are case sensitive.

Employee number and name of employees whose name begins with S

SELECT EMPNO,ENAME
FROM EMP
WHERE ENAME LIKE 'S%';
     EMPNO ENAME
---------- ----------
      7369 SMITH
      7788 SCOTT

Employee number and name of the employees whose name includes T

SELECT EMPNO,ENAME
FROM EMP
WHERE ENAME LIKE '%T%';
     EMPNO ENAME
---------- ----------
      7369 SMITH
      7654 MARTIN
      7788 SCOTT
      7844 TURNER

LIKE is used in searches with % and _.

SQL*PLUS Tips
Here's how to edit the SQL statement in the command buffer.
This is useful when you run an incorrect SQL statement.
ed
When you run ed in SQL*PLUS, the system's default editor runs and displays the buffer. In Windows, Notepad runs. After modifying the contents and closing it in Notepad, execute / to run the SQL statement in the buffer.
/

Employee number, name, and commission of the employees who earn 300, 500, or 1400 commissions

SELECT EMPNO,ENAME,COMM
FROM EMP
WHERE COMM = 300 OR COMM = 500 OR COMM = 1400;
     EMPNO ENAME	    COMM
---------- ---------- ----------
      7499 ALLEN	     300
      7521 WARD 	     500
      7654 MARTIN	    1400
SELECT EMPNO,ENAME,COMM
FROM EMP
WHERE COMM IN (300,500,1400);
     EMPNO ENAME	    COMM
---------- ---------- ----------
      7499 ALLEN	     300
      7521 WARD 	     500
      7654 MARTIN	    1400

Both are SQL statements that show the same result. The first SQL statement used the logical operator, OR, and the second used IN.

Employee number, name, and salary of employees whose salary is between 1200 and 3500

SELECT EMPNO,ENAME,SAL
FROM EMP
WHERE SAL BETWEEN 1200 AND 3500;
     EMPNO ENAME	     SAL
---------- ---------- ----------
      7499 ALLEN	    1600
      7521 WARD 	    1250
      7566 JONES	    2975
      7654 MARTIN	    1250
      7698 BLAKE	    2850
      7782 CLARK	    2450
      7788 SCOTT	    3000
      7844 TURNER	    1500
      7902 FORD 	    3000
      7934 MILLER	    1300

SAL BETWEEN 1200 AND 3500 is mathematically 1200 < = SAL < = 3500.

Employee name, employee number, job, department number of the employees whose job title is manager and department number is 30

SELECT ENAME,EMPNO,JOB,DEPTNO
FROM EMP
WHERE DEPTNO = 30 AND JOB = 'MANAGER';
ENAME		EMPNO JOB	    DEPTNO
---------- ---------- --------- ----------
BLAKE		 7698 MANAGER		30

Employee number, name, and department number of an employees whose department number is not 30

SELECT EMPNO,ENAME,DEPTNO
FROM EMP
WHERE NOT DEPTNO = 30;
     EMPNO ENAME	  DEPTNO
---------- ---------- ----------
      7369 SMITH	      20
      7566 JONES	      20
      7782 CLARK	      10
      7788 SCOTT	      20
      7839 KING 	      10
      7876 ADAMS	      20
      7902 FORD 	      20
      7934 MILLER	      10

Employee number, name, and commission of the employees whose commission are not 300, 500, or 1400

SELECT EMPNO,ENAME,COMM
FROM EMP
WHERE COMM NOT IN (300,500,1400);
     EMPNO ENAME	    COMM
---------- ---------- ----------
      7844 TURNER	       0

Employee number and name of the employees whose name does not contain an S

SELECT EMPNO,ENAME
FROM EMP
WHERE ENAME NOT LIKE '%S%';
     EMPNO ENAME
---------- ----------
      7499 ALLEN
      7521 WARD
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7839 KING
      7844 TURNER
      7902 FORD
      7934 MILLER

Employee number, name, and salary of employees whose salary is less than 1200 or exceeds 3700

SELECT EMPNO,ENAME,SAL
FROM EMP
WHERE SAL NOT BETWEEN 1200 AND 3700;
     EMPNO ENAME	     SAL
---------- ---------- ----------
      7369 SMITH	     800
      7839 KING 	    5000
      7876 ADAMS	    1100
      7900 JAMES	     950

Until now, we have seen how to use AND, OR, and NOT.

Employee name and job of the employees without his/her manager

To check if a column is NULL, use the IS NULL and IS NOT NULL statements.

SELECT ENAME,JOB
FROM EMP
WHERE MGR IS NULL;
ENAME	   JOB
---------- ---------
KING	   PRESIDENT

GROUP BY

SELECT DEPTNO,AVG(SAL)
FROM EMP
SELECT DEPTNO,AVG(SAL)
       *
ERROR at line 1:
ORA-00937: not a single-group group function

The above SQL statement intents to find the salary average by DEPTNO.
At this time, DEPTNO is a column for grouping.
A group function is used together with a column for grouping.
You should specify that column after GROUP BY.

Average salary by department number

SELECT DEPTNO,AVG(SAL)
FROM EMP
GROUP BY DEPTNO;
    DEPTNO   AVG(SAL)
---------- ----------
	30 1566.66667
	20	 2175
	10 2916.66667

Total number of employees and employees receiving commissions by department number

SELECT DEPTNO,COUNT(*),COUNT(COMM)
FROM EMP
GROUP BY DEPTNO;
    DEPTNO   COUNT(*) COUNT(COMM)
---------- ---------- -----------
	30	    6		4
	20	    5		0
	10	    3		0

Maximum salary and minimum salary by department number

SELECT DEPTNO,MAX(SAL),MIN(SAL)
FROM EMP
GROUP BY DEPTNO;
    DEPTNO   MAX(SAL)	MIN(SAL)
---------- ---------- ----------
	30	 2850	     950
	20	 3000	     800
	10	 5000	    1300

HAVING is used to extract data from the GROUP BY clause.

Average salary by department number for those with a salary of 2000 or higher

SELECT DEPTNO,AVG(SAL)
FROM EMP
WHERE AVG(SAL) >= 2000;
GROUP BY DEPTNO;
GROUP BY DEPTNO;  2    3  WHERE AVG(SAL) >= 2000
      *
ERROR at line 3:
ORA-00934: group function is not allowed here

You can not use a WHERE conditional statement when you are giving a condition using the GROUP BY clause. Therefore, the above query generates an error. Use the HAVING clause instead of the WHERE clause. Note that in the HAVING clause only has the group functions and the columns for grouping.

SELECT DEPTNO,AVG(SAL)
FROM EMP
GROUP BY DEPTNO
HAVING AVG(SAL) >= 2000;
    DEPTNO   AVG(SAL)
---------- ----------
	20	 2175
	10 2916.66667

Salary averages by department number only for employees whose salary is 1000 or higher (only records with an average of 2000 or higher)

SELECT DEPTNO,AVG(SAL)
FROM EMP
WHERE SAL >= 1000
GROUP BY DEPTNO
HAVING AVG(SAL) >= 2000;
    DEPTNO   AVG(SAL)
---------- ----------
	20    2518.75
	10 2916.66667

The WHERE clause retrieves records matching specific conditions from a table. The HAVING clause retrieves records matching specific conditions from the query result of the GROUP BY clause.

ORDER BY

An ORDER BY clause sorts by any column in a SELECT statement.
The usage is as follows.
ORDER BY ColumnName ASC; or ORDER BY ColumnName DESC;
ASC: Ascending order (You can omit it)
DESC: Descending order

Fetch employee number, name, and salary in descending order of his/her salary (For same salary employees, sort by name in ascending order)

SELECT EMPNO,ENAME,SAL
FROM EMP
ORDER BY SAL DESC,ENAME ASC;
     EMPNO ENAME	     SAL
---------- ---------- ----------
      7839 KING 	    5000
      7902 FORD 	    3000
      7788 SCOTT	    3000
      7566 JONES	    2975
      7698 BLAKE	    2850
      7782 CLARK	    2450
      7499 ALLEN	    1600
      7844 TURNER	    1500
      7934 MILLER	    1300
      7654 MARTIN	    1250
      7521 WARD 	    1250
      7876 ADAMS	    1100
      7900 JAMES	     950
      7369 SMITH	     800

Join

Joins means query data from two or more tables.
Join requires N-1 join conditions when joining N tables.
Join without any join condition is called Cartesian product.

Cartesian product

SELECT EMP.ENAME,DEPT.DNAME
FROM EMP,DEPT
ENAME	   DNAME
---------- --------------
SMITH	   ACCOUNTING
ALLEN	   ACCOUNTING
WARD	   ACCOUNTING
JONES	   ACCOUNTING
MARTIN	   ACCOUNTING
BLAKE	   ACCOUNTING
CLARK	   ACCOUNTING
SCOTT	   ACCOUNTING
KING	   ACCOUNTING
TURNER	   ACCOUNTING
ADAMS	   ACCOUNTING
JAMES	   ACCOUNTING
FORD	   ACCOUNTING
MILLER	   ACCOUNTING
SMITH	   RESEARCH
ALLEN	   RESEARCH
WARD	   RESEARCH
JONES	   RESEARCH
MARTIN	   RESEARCH
BLAKE	   RESEARCH
CLARK	   RESEARCH
SCOTT	   RESEARCH
KING	   RESEARCH
TURNER	   RESEARCH
ADAMS	   RESEARCH
JAMES	   RESEARCH
FORD	   RESEARCH
MILLER	   RESEARCH
SMITH	   SALES
ALLEN	   SALES
WARD	   SALES
JONES	   SALES
MARTIN	   SALES
BLAKE	   SALES
CLARK	   SALES
SCOTT	   SALES
KING	   SALES
TURNER	   SALES
ADAMS	   SALES
JAMES	   SALES
FORD	   SALES
MILLER	   SALES
SMITH	   OPERATIONS
ALLEN	   OPERATIONS
WARD	   OPERATIONS
JONES	   OPERATIONS
MARTIN	   OPERATIONS
BLAKE	   OPERATIONS
CLARK	   OPERATIONS
SCOTT	   OPERATIONS
KING	   OPERATIONS
TURNER	   OPERATIONS
ADAMS	   OPERATIONS
JAMES	   OPERATIONS
FORD	   OPERATIONS
MILLER	   OPERATIONS

The example of joining in the future should be practiced while considering the Cartesian product above. A total of 48 rows are generated by multiplying 12 records existing in the EMP table and 4 records existing in the DEPT table. If you are certain that a column is a specific table column, you can omit the table name like ENAME for EMP.ENAME.

Employee name and department name

SELECT ENAME,DNAME
FROM EMP,DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO;
ENAME	   DNAME
---------- --------------
SMITH	   RESEARCH
ALLEN	   SALES
WARD	   SALES
JONES	   RESEARCH
MARTIN	   SALES
BLAKE	   SALES
CLARK	   ACCOUNTING
SCOTT	   RESEARCH
KING	   ACCOUNTING
TURNER	   SALES
ADAMS	   RESEARCH
JAMES	   SALES
FORD	   RESEARCH
MILLER	   ACCOUNTING

You can give the table name alias in the FROM clause.

SELECT E.ENAME,D.DNAME
FROM EMP E,DEPT D
WHERE E.DEPTNO = D.DEPTNO;
ENAME	   DNAME
---------- --------------
SMITH	   RESEARCH
ALLEN	   SALES
WARD	   SALES
JONES	   RESEARCH
MARTIN	   SALES
BLAKE	   SALES
CLARK	   ACCOUNTING
SCOTT	   RESEARCH
KING	   ACCOUNTING
TURNER	   SALES
ADAMS	   RESEARCH
JAMES	   SALES
FORD	   RESEARCH
MILLER	   ACCOUNTING

Employee name and his/her manager's name

You can join the same table.

SELECT E.ENAME, M.ENAME 
FROM EMP E INNER JOIN EMP M ON E.MGR = M.EMPNO;
ENAME	   ENAME
---------- ----------
FORD	   JONES
SCOTT	   JONES
JAMES	   BLAKE
TURNER	   BLAKE
MARTIN	   BLAKE
WARD	   BLAKE
ALLEN	   BLAKE
MILLER	   CLARK
ADAMS	   SCOTT
CLARK	   KING
BLAKE	   KING
JONES	   KING
SMITH	   FORD

Employee name, salary, and salary grade

SELECT E.ENAME,E.SAL,S.GRADE
FROM EMP E,SALGRADE S
WHERE E.SAL >= S.LOSAL AND E.SAL <= S.HISAL;
ENAME		  SAL	   GRADE
---------- ---------- ----------
SMITH		  800	       1
ADAMS		 1100	       1
JAMES		  950	       1
WARD		 1250	       2
MARTIN		 1250	       2
MILLER		 1300	       2
ALLEN		 1600	       3
TURNER		 1500	       3
JONES		 2975	       4
BLAKE		 2850	       4
CLARK		 2450	       4
SCOTT		 3000	       4
FORD		 3000	       4
KING		 5000	       5
SELECT E.ENAME,E.SAL,S.GRADE
FROM EMP E,SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;
ENAME		  SAL	   GRADE
---------- ---------- ----------
SMITH		  800	       1
ADAMS		 1100	       1
JAMES		  950	       1
WARD		 1250	       2
MARTIN		 1250	       2
MILLER		 1300	       2
ALLEN		 1600	       3
TURNER		 1500	       3
JONES		 2975	       4
BLAKE		 2850	       4
CLARK		 2450	       4
SCOTT		 3000	       4
FORD		 3000	       4
KING		 5000	       5

Employee name, department name, and salary grade

SELECT E.ENAME,D.DNAME,S.GRADE
FROM EMP E,DEPT D,SALGRADE S
WHERE E.DEPTNO = D.DEPTNO
AND E.SAL BETWEEN S.LOSAL AND S.HISAL;
ENAME	   DNAME	       GRADE
---------- -------------- ----------
SMITH	   RESEARCH		   1
ALLEN	   SALES		   3
WARD	   SALES		   2
JONES	   RESEARCH		   4
MARTIN	   SALES		   2
BLAKE	   SALES		   4
CLARK	   ACCOUNTING		   4
SCOTT	   RESEARCH		   4
KING	   ACCOUNTING		   5
TURNER	   SALES		   3
ADAMS	   RESEARCH		   1
JAMES	   SALES		   1
FORD	   RESEARCH		   4
MILLER	   ACCOUNTING		   2

Employee name and his/her manager's name

SELECT E.ENAME,M.ENAME
FROM EMP E,EMP M
WHERE E.MGR = M.EMPNO;
ENAME	   ENAME
---------- ----------
FORD	   JONES
SCOTT	   JONES
JAMES	   BLAKE
TURNER	   BLAKE
MARTIN	   BLAKE
WARD	   BLAKE
ALLEN	   BLAKE
MILLER	   CLARK
ADAMS	   SCOTT
CLARK	   KING
BLAKE	   KING
JONES	   KING
SMITH	   FORD

The above results are missing the president's record. There is no employee whose employee number is NULL, so the president record does not match the join condition. Nevertheless, if you want to see the president's records, you have to query as follows.

SELECT E.ENAME,M.ENAME
FROM EMP E LEFT JOIN EMP M ON E.MGR = M.EMPNO;
ENAME	   ENAME
---------- ----------
FORD	   JONES
SCOTT	   JONES
JAMES	   BLAKE
TURNER	   BLAKE
MARTIN	   BLAKE
WARD	   BLAKE
ALLEN	   BLAKE
MILLER	   CLARK
ADAMS	   SCOTT
CLARK	   KING
BLAKE	   KING
JONES	   KING
SMITH	   FORD
KING

This join is called an OUTER join.

A LEFT JOIN B

Even if the rows do not satisfy the join condition, it includes those rows of the left table, A.

Employee name, department name (Even if there are no employees in the department with department number 40, the result must include department 40)

SELECT E.ENAME,D.DNAME
FROM DEPT D LEFT JOIN EMP E ON D.DEPTNO = E.DEPTNO;
ENAME	   DNAME
---------- --------------
SMITH	   RESEARCH
ALLEN	   SALES
WARD	   SALES
JONES	   RESEARCH
MARTIN	   SALES
BLAKE	   SALES
CLARK	   ACCOUNTING
SCOTT	   RESEARCH
KING	   ACCOUNTING
TURNER	   SALES
ADAMS	   RESEARCH
JAMES	   SALES
FORD	   RESEARCH
MILLER	   ACCOUNTING
	   OPERATIONS

Employee name, department number, department name

SELECT ENAME,E.DEPTNO,DNAME
FROM EMP E,DEPT D
WHERE E.DEPTNO = D.DEPTNO;
ENAME	       DEPTNO DNAME
---------- ---------- --------------
SMITH		   20 RESEARCH
ALLEN		   30 SALES
WARD		   30 SALES
JONES		   20 RESEARCH
MARTIN		   30 SALES
BLAKE		   30 SALES
CLARK		   10 ACCOUNTING
SCOTT		   20 RESEARCH
KING		   10 ACCOUNTING
TURNER		   30 SALES
ADAMS		   20 RESEARCH
JAMES		   30 SALES
FORD		   20 RESEARCH
MILLER		   10 ACCOUNTING
SELECT ENAME,E.DEPTNO,DNAME
FROM EMP E INNER JOIN DEPT D ON E.DEPTNO = D.DEPTNO;
ENAME	       DEPTNO DNAME
---------- ---------- --------------
SMITH		   20 RESEARCH
ALLEN		   30 SALES
WARD		   30 SALES
JONES		   20 RESEARCH
MARTIN		   30 SALES
BLAKE		   30 SALES
CLARK		   10 ACCOUNTING
SCOTT		   20 RESEARCH
KING		   10 ACCOUNTING
TURNER		   30 SALES
ADAMS		   20 RESEARCH
JAMES		   30 SALES
FORD		   20 RESEARCH
MILLER		   10 ACCOUNTING

The above two queries retrieve the same result.

Employee name, job, department number, and department location of the employees with department number 30

SELECT ENAME,JOB,E.DEPTNO,LOC
FROM EMP E,DEPT D
WHERE E.DEPTNO = D.DEPTNO AND D.DEPTNO = 30;
ENAME	   JOB		 DEPTNO LOC
---------- --------- ---------- -------------
ALLEN	   SALESMAN	     30 CHICAGO
WARD	   SALESMAN	     30 CHICAGO
MARTIN	   SALESMAN	     30 CHICAGO
BLAKE	   MANAGER	     30 CHICAGO
TURNER	   SALESMAN	     30 CHICAGO
JAMES	   CLERK	     30 CHICAGO
SELECT ENAME,JOB,E.DEPTNO,LOC
FROM EMP E INNER JOIN DEPT D ON E.DEPTNO = D.DEPTNO
WHERE D.DEPTNO = 30;
ENAME	   JOB		 DEPTNO LOC
---------- --------- ---------- -------------
ALLEN	   SALESMAN	     30 CHICAGO
WARD	   SALESMAN	     30 CHICAGO
MARTIN	   SALESMAN	     30 CHICAGO
BLAKE	   MANAGER	     30 CHICAGO
TURNER	   SALESMAN	     30 CHICAGO
JAMES	   CLERK	     30 CHICAGO

Employee name, commission, department name, and department location of the employees receiving the commission

SELECT ENAME,COMM,DNAME,LOC
FROM EMP,DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO 
AND EMP.COMM IS NOT NULL AND EMP.COMM <> 0;
ENAME		 COMM DNAME	     LOC
---------- ---------- -------------- -------------
ALLEN		  300 SALES	     CHICAGO
WARD		  500 SALES	     CHICAGO
MARTIN		 1400 SALES	     CHICAGO
SELECT ENAME,COMM,DNAME,LOC
FROM EMP,DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO 
AND EMP.COMM IS NOT NULL AND EMP.COMM != 0;
ENAME		 COMM DNAME	     LOC
---------- ---------- -------------- -------------
ALLEN		  300 SALES	     CHICAGO
WARD		  500 SALES	     CHICAGO
MARTIN		 1400 SALES	     CHICAGO
SELECT ENAME,COMM,DNAME,LOC
FROM EMP,DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO 
AND EMP.COMM IS NOT NULL AND EMP.COMM NOT IN(0);
ENAME		 COMM DNAME	     LOC
---------- ---------- -------------- -------------
ALLEN		  300 SALES	     CHICAGO
WARD		  500 SALES	     CHICAGO
MARTIN		 1400 SALES	     CHICAGO
SELECT ENAME,COMM,DNAME,LOC
FROM EMP INNER JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO
WHERE EMP.COMM IS NOT NULL AND EMP.COMM <> 0;
ENAME		 COMM DNAME	     LOC
---------- ---------- -------------- -------------
ALLEN		  300 SALES	     CHICAGO
WARD		  500 SALES	     CHICAGO
MARTIN		 1400 SALES	     CHICAGO

Employee name, job, department number, department name of the employees who work at DALLAS

SELECT E.ENAME,E.JOB,D.DEPTNO,D.DNAME
FROM EMP E,DEPT D
WHERE E.DEPTNO = D.DEPTNO
AND D.LOC = 'DALLAS';
ENAME	   JOB		 DEPTNO DNAME
---------- --------- ---------- --------------
SMITH	   CLERK	     20 RESEARCH
JONES	   MANAGER	     20 RESEARCH
SCOTT	   ANALYST	     20 RESEARCH
ADAMS	   CLERK	     20 RESEARCH
FORD	   ANALYST	     20 RESEARCH
SELECT E.ENAME,E.JOB,D.DEPTNO,D.DNAME
FROM EMP E INNER JOIN DEPT D ON E.DEPTNO = D.DEPTNO
WHERE D.LOC = 'DALLAS';
ENAME	   JOB		 DEPTNO DNAME
---------- --------- ---------- --------------
SMITH	   CLERK	     20 RESEARCH
JONES	   MANAGER	     20 RESEARCH
SCOTT	   ANALYST	     20 RESEARCH
ADAMS	   CLERK	     20 RESEARCH
FORD	   ANALYST	     20 RESEARCH

Employee name and department name of the employee whose name contains A

SELECT E.ENAME,D.DNAME
FROM EMP E,DEPT D
WHERE E.DEPTNO = D.DEPTNO
AND E.ENAME LIKE '%A%';
ENAME	   DNAME
---------- --------------
ALLEN	   SALES
WARD	   SALES
MARTIN	   SALES
BLAKE	   SALES
CLARK	   ACCOUNTING
ADAMS	   RESEARCH
JAMES	   SALES
SELECT E.ENAME,D.DNAME
FROM EMP E INNER JOIN DEPT D ON E.DEPTNO = D.DEPTNO
WHERE E.ENAME LIKE '%A%';
ENAME	   DNAME
---------- --------------
ALLEN	   SALES
WARD	   SALES
MARTIN	   SALES
BLAKE	   SALES
CLARK	   ACCOUNTING
ADAMS	   RESEARCH
JAMES	   SALES

Employee name, job, salary, salary grade

SELECT E.ENAME,E.JOB,E.SAL,S.GRADE
FROM EMP E,SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;
ENAME	   JOB		    SAL      GRADE
---------- --------- ---------- ----------
SMITH	   CLERK	    800 	 1
ADAMS	   CLERK	   1100 	 1
JAMES	   CLERK	    950 	 1
WARD	   SALESMAN	   1250 	 2
MARTIN	   SALESMAN	   1250 	 2
MILLER	   CLERK	   1300 	 2
ALLEN	   SALESMAN	   1600 	 3
TURNER	   SALESMAN	   1500 	 3
JONES	   MANAGER	   2975 	 4
BLAKE	   MANAGER	   2850 	 4
CLARK	   MANAGER	   2450 	 4
SCOTT	   ANALYST	   3000 	 4
FORD	   ANALYST	   3000 	 4
KING	   PRESIDENT	   5000 	 5

Employee name and department number of the employee who works with ALLEN

SELECT C.ENAME,C.DEPTNO
FROM EMP E,EMP C
WHERE E.EMPNO <> C.EMPNO
AND E.DEPTNO = C.DEPTNO
AND E.ENAME = 'ALLEN'
ORDER BY C.ENAME;
ENAME	       DEPTNO
---------- ----------
BLAKE		   30
JAMES		   30
MARTIN		   30
TURNER		   30
WARD		   30
SELECT C.ENAME,C.DEPTNO
FROM EMP E INNER JOIN EMP C ON E.DEPTNO = C.DEPTNO 
WHERE E.EMPNO <> C.EMPNO
AND E.ENAME = 'ALLEN'
ORDER BY C.ENAME;
ENAME	       DEPTNO
---------- ----------
BLAKE		   30
JAMES		   30
MARTIN		   30
TURNER		   30
WARD		   30

Subqueries

A subquery is another SELECT enclosed in parentheses() in a SELECT and pass the result of a subquery to the main query.

Name of the department to which JONES belongs

SELECT DNAME 
FROM DEPT
WHERE DEPTNO = (SELECT DEPTNO FROM EMP WHERE ENAME = 'JONES');
DNAME
--------------
RESEARCH

The query that asks for the department number is a subquery. This subquery is called a single-row subquery because it obtains only one result.

Employee name and department name of employees in a department with department number 10

SELECT E.ENAME,D.DNAME
FROM EMP E,DEPT D
WHERE E.DEPTNO = D.DEPTNO
AND D.DEPTNO = 10;
ENAME	   DNAME
---------- --------------
CLARK	   ACCOUNTING
KING	   ACCOUNTING
MILLER	   ACCOUNTING
SELECT E.ENAME,D.DNAME
FROM EMP E,
(
	SELECT DEPTNO,DNAME
	FROM DEPT
	WHERE DEPTNO = 10
) D
WHERE E.DEPTNO = D.DEPTNO;
ENAME	   DNAME
---------- --------------
CLARK	   ACCOUNTING
KING	   ACCOUNTING
MILLER	   ACCOUNTING

Employee number, name, and salary of employees who receive more than average salary

SELECT EMPNO,ENAME,SAL
FROM EMP
WHERE SAL > (SELECT AVG(SAL)
	      FROM EMP)
ORDER BY SAL DESC;
     EMPNO ENAME	     SAL
---------- ---------- ----------
      7839 KING 	    5000
      7902 FORD 	    3000
      7788 SCOTT	    3000
      7566 JONES	    2975
      7698 BLAKE	    2850
      7782 CLARK	    2450

Employee number and employee name of the employee with the highest salary among the employees to which belong department 10

SELECT EMPNO,ENAME
FROM EMP
WHERE SAL = (SELECT MAX(SAL) 
	     FROM EMP 
	     WHERE DEPTNO = 10);
     EMPNO ENAME
---------- ----------
      7839 KING