Multiple column Queries:
Syntax:
SELECT COLUMN1, COL2,......
FROM TABLE
WHERE (COLUMN1, COL2, ...) IN
(SELECT COLUMN1, COL2,....
FROM TABLE
WHERE );
Example: Find the department number, name, job title and salary of those people who have the similar job title and salary as those are in department 10.
SELECT DEPTNO,ENAME, JOB, SAL
FROM EMP
WHERE (JOB, SAL) IN ( SELECT JOB, SAL
FROM EMP
WHERE DEPTNO=10);
OUTPUT:
|
DEPTNO
|
ENAME
|
JOB
|
SAL
|
|
10
|
Nirmal
|
MANAGER
|
2975
|
|
10
|
Kailash
|
ANALYST
|
3000
|
|
20
|
Ashwin
|
ANALYST
|
3000
|