Scribd
Upload
44 views5 pages

SQL

The document contains SQL queries for various subquery operations related to employee data, such as retrieving names, salaries, and hire dates based on specific conditions. It includes examples of using operators like ALL and ANY to filter results based on comparisons with other employee records. Additionally, it addresses maximum and minimum salary queries, as well as conditions based on job roles and departments.

Uploaded by

gomalaj366
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
44 views5 pages

SQL

The document contains SQL queries for various subquery operations related to employee data, such as retrieving names, salaries, and hire dates based on specific conditions. It includes examples of using operators like ALL and ANY to filter results based on comparisons with other employee records. Additionally, it addresses maximum and minimum salary queries, as well as conditions based on job roles and departments.

Uploaded by

gomalaj366
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 5

ASSIGNMENT ON TYPES OF SUB QUERY OPERATORS

51. WAQTD NAME OF THE EMPLOYEES EARNING SALARY MORE THAN THE
SALESMAN

ANS. SELECT ENAME

FROM EMP

WHERE SAL > ALL (SELECT SAL FROM EMP WHERE JOB = 'SALESMAN');

52. WAQTD DETAILS OF THE EMPLOYEES HIRED AFTER ALL THE CLERKS

ANS. SELECT *

FROM EMP

WHERE HIREDATE > ALL (SELECT HIREDATE FROM EMP WHERE JOB = 'CLERK');

53. WAQTD NAME AND SALARY FOR EMPLOYEES IF THEY ARE EARNING LESS
THAN ATLEST A MANAGER

ANS. SELECT ENAME, SAL

FROM EMP

WHERE SAL < ANY (SELECT SAL FROM EMP WHERE JOB = 'MANAGER');

54. WAQTD NAME AND HIREDATE OF EMPLOYEES HIRED BEFORE ALL THE
MANAGERS

ANS. SELECT ENAME, HIREDATE

FROM EMP

WHERE HIREDATE < ALL (SELECT HIREDATE FROM EMP WHERE JOB =
'MANAGER');
55. WAQTD NAMES OF THE EMPLOYEES HIRED AFTER ALL THE MANAGERS AND
EARNING SALARY MORE THAN ALL THE CLERKS

ANS.SELECT ENAME

FROM EMP

WHERE HIREDATE > ALL (SELECT HIREDATE FROM EMP WHERE JOB =
'MANAGER')

AND SAL > ALL (SELECT SAL FROM EMP WHERE JOB = 'CLERK');

56. WAQTD DETAILS OF THE EMPLOYEES WORKING AS CLERK AND HIRED


BEFORE ATLEST A SALESMAN

ANS. SELECT *

FROM EMP

WHERE JOB = 'CLERK'

AND HIREDATE < ANY (SELECT HIREDATE FROM EMP WHERE JOB =
'SALESMAN');

57. WAQTD DETAILS OF EMPLOYEES WORKING IN ACCOUNTING OR SALES DEPT

ANS. SELECT *

FROM EMP

WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE DNAME IN


('ACCOUNTING', 'SALES'));

58. WAQTD DEPARTMENT NAMES OF THE EMPLOYEES WITH NAME SMITH, KING
AND MILLER

ANS. SELECT DNAME

FROM DEPT

WHERE DEPTNO IN (

SELECT DEPTNO

FROM EMP

WHERE ENAME IN ('SMITH', 'KING', 'MILLER') );


59. WAQTD DETAILS OF EMPLOYEES WORKING IN NEWYORK OR CHICAGO

ANS. SELECT *

FROM EMP

WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC IN ('NEWYORK',


'CHICAGO'));

60. WAQTD EMP NAMES IF EMPLOYEES ARE HIRED AFTER ALL THE EMPLOYEES
OF DEPT 10

ANS. SELECT ENAME

FROM EMP

WHERE HIREDATE > ALL (SELECT HIREDATE FROM EMP WHERE DEPTNO = 10);

ASSIGNMENT ON MAX & MIN

41. WAQTD NAME OF THE EMPLOYEE EARNING MAXIMUM SALARY

ANS. SELECT ENAME

FROM EMP

WHERE SAL = (SELECT MAX(SAL) FROM EMP);

42. WAQTD NAME OF THE EMPLOYEE EARNING MINIMUM SALARY

ANS. SELECT ENAME

FROM EMP

WHERE SAL = (SELECT MIN(SAL) FROM EMP);

43. WAQTD NAME AND HIREDATE OF THE EMPLOYEE HIRED BEFORE ALL THE
EMPLOYEES (FIRST EMP)

ANS. SELECT ENAME, HIREDATE

FROM EMP

WHERE HIREDATE = (SELECT MIN(HIREDATE) FROM EMP);


44. WAQTD NAME AND HIREDATE OF THE EMPLOYEES HIRED AT THE LAST

ANS. SELECT ENAME, HIREDATE

FROM EMP

WHERE HIREDATE = (SELECT MAX(HIREDATE) FROM EMP);

45. WAQTD NAME, COMM OF THE EMPLOYEE WHO EARNS MIN COMISSION

ANS. NSELECT ENAME, COMM

FROM EMP

WHERE COMM = (SELECT MIN(COMM) FROM EMP WHERE COMM IS NOT NULL);

46. WAQTD NAME, SAL AND COMM OF THE EMPLOYEE EARNING MAXIMUM
COMISSION

ANS. SELECT ENAME, SAL, COMM

FROM EMP

WHERE COMM = (SELECT MAX(COMM) FROM EMP WHERE COMM IS NOT NULL);

47. WAQTD DETAILS OF THE EMPLOYEE WHO HAS GREATEST EMPNO

ANS. SELECT *

FROM EMP

WHERE EMPNO = (SELECT MAX(EMPNO) FROM EMP);

48. WAQTD DETAILS OF THE EMPLOYEES HAVING THE LEAST HIREDATE

ANS. SELECT *

FROM EMP

WHERE HIREDATE = (SELECT MIN(HIREDATE) FROM EMP);


49. WAQTO DETAILS OF THE EMPLOYEES EARNING LEAST ANNUAL SALARY

ANS. SELECT *

FROM EMP

WHERE (SAL * 12) = (SELECT MIN(SAL * 12) FROM EMP);

50. WAQTD NAME, ANNUAL SALARY OF THE EMPLOYEES IF THEIR ANNUAL


SALARY IS MORE THAN ALL THE SALESMAN

ANS. SELECT ENAME, SAL * 12 AS ANNUAL_SALARY

FROM EMP

WHERE SAL * 12 > ALL (SELECT SAL * 12 FROM EMP WHERE JOB = 'SALESMAN');

You might also like