Scribd Logo
Upload

Welcome to Scribd!

  • 1K views

    Assignment-2 On Operator

    Uploaded by

    Vikramajeet Chauhan
    The document contains 30 SQL queries that select employee data from an EMP database table. The queries filter employees based on various criteria like department, job, salary, hire date and more. They also perform operations like finding null values, using logical operators, checking for string patterns in names, and calculating annual salary.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    Assignment-2 On Operator

    Uploaded by

    Vikramajeet Chauhan
    1K views3 pages
    The document contains 30 SQL queries that select employee data from an EMP database table. The queries filter employees based on various criteria like department, job, salary, hire date and more. They also perform operations like finding null values, using logical operators, checking for string patterns in names, and calculating annual salary.

    Original Title

    ASSIGNMENT-2 ON OPERATOR

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document contains 30 SQL queries that select employee data from an EMP database table. The queries filter employees based on various criteria like department, job, salary, hire date and more. They also perform operations like finding null values, using logical operators, checking for string patterns in names, and calculating annual salary.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    1K views3 pages

    Assignment-2 On Operator

    Uploaded by

    Vikramajeet Chauhan
    The document contains 30 SQL queries that select employee data from an EMP database table. The queries filter employees based on various criteria like department, job, salary, hire date and more. They also perform operations like finding null values, using logical operators, checking for string patterns in names, and calculating annual salary.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    You are on page 1of 3

    ASSIGNMENT-2 ON OPERATORS

    1] LIST ALL THE EMPLOYEES WHOSE COMMISSION IS NULL


    SELECT *
    FROM EMP
    WHERE COMM IS NULL;

    2] LIST ALL THE EMPLOYEES WHO DON’T HAVE A REPORTING MANAGER.


    SELECT *
    FROM EMP
    WHERE MGR IS NULL;

    3] LIST ALL THE SALESMEN IN DEPT 30.


    SELECT *
    FROM EMP
    WHERE DEPTNO=30;

    4] LIST ALL THE SALESMEN IN DEPT NUMBER 30 AND HAVING SALARY GREATER THAN 1500.
    SELECT *
    FROM EMP
    WHERE JOB='SALESMAN' AND DEPTNO=30 AND SAL>1500;

    5] LIST ALL THE EMPLOYEES WHOSE NAME STARTS WITH ‘S’ OR ‘A’.
    SELECT *
    FROM EMP
    WHERE ENAME LIKE 'S%' OR ENAME LIKE 'A%';

    6] LIST ALL THE EMPLOYEES EXCEPT THOSE WHO ARE WORKING IN DEPT 10 & 20.
    SELECT *
    FROM EMP
    WHERE DEPTNO NOT IN (10,20);

    7] LIST THE EMPLOYEES WHOSE NAME DOES NOT START WITH ‘S’.
    SELECT *
    FROM EMP
    WHERE ENAME NOT LIKE 'S%';

    8] LIST ALL THE EMPLOYEES WHO ARE HAVING REPORTING MANAGERS IN DEPT 10.
    SELECT *
    FROM EMP
    WHERE MGR IS NOT NULL AND DEPTNO=10;

    9] LIST ALL THE EMPLOYEES WHOSE COMMISSION IS NULL AND WORKING AS CLERK.
    SELECT *
    FROM EMP
    WHERE COMM IS NULL AND JOB='CLERK';

    10] LIST ALL THE EMPLOYEES WHO DON’T HAVE A REPORTING MANAGER IN DEPTNO 10 OR 30.
    SELECT *
    FROM EMP
    WHERE MGR IS NULL AND DEPTNO IN (10,20);

    11] LIST ALL THE SALESMEN IN DEPT 30 WITH SAL MORE THAN 2450.
    SELECT *
    FROM EMP
    WHERE JOB='SALESMAN' AND DEPTNO=30 AND SAL>2450; (NO ROW SELECTED BECAUSE OF IN TABLE THERE IS NO SALESMAN GETTING
    SALARY MORE THAN 2450)

    12] LIST ALL THE ANALYST IN DEPT NUMBER 20 AND HAVING SALARY GREATER THAN 2500.
    SELECT *
    FROM EMP
    WHERE JOB='ANALYST' AND DEPTNO=20 AND SAL>2500;
    13] LIST ALL THE EMPLOYEES WHOSE NAME STARTS WITH ‘M’ OR ‘J’.
    SELECT *
    FROM EMP
    WHERE ENAME LIKE 'M%' OR ENAME LIKE 'J%';

    14] LIST ALL THE EMPLOYEES WITH ANNUAL SALARY EXCEPT THOSE WHO ARE WORKING IN DEPT 30.
    SELECT EMP.*,SAL*12 "ANNUAL SALARY"
    FROM EMP
    WHERE DEPTNO !=30;

    15] LIST THE EMPLOYEES WHOSE NAME DOES NOT END WITH ‘ES’ OR ‘R’.
    SELECT *
    FROM EMP
    WHERE ENAME NOT LIKE '%ES' AND ENAME NOT LIKE '%R';

    16] LIST ALL THE EMPLOYEES WHO ARE HAVING REPORTING MANAGERS IN DEPT 10 ALONG WITH 10% HIKE IN SALARY.
    SELECT EMP.*, SAL+SAL*0.1 "HIKE SALARY"
    FROM EMP
    WHERE MGR IS NOT NULL AND DEPTNO=10;

    18] DISPLAY ALL THE EMPLOYEE WHO ARE JOINED AFTER YEAR 81.
    SELECT *
    FROM EMP
    WHERE HIREDATE>'31-DEC-81';

    19] DISPLAY ALL THE EMPLOYEE WHO ARE JOINED IN FEB.


    SELECT *
    FROM EMP
    WHERE HIREDATE LIKE '%FEB%';

    20] LIST THE EMPLOYEES WHO ARE NOT WORKING AS MANAGERS AND CLERKS IN DEPT 10 AND 20 WITH A SALARY IN THE RANGE OF 1000
    TO 3000.
    SELECT *
    FROM EMP
    WHERE JOB IN ('MANAGER','CLERK') AND DEPTNO NOT IN (10,20) AND SAL BETWEEN 1000 AND 3000;

    21] LIST THE EMPLOYEES WHOSE SALARY NOT IN THE RANGE OF 1000 TO 2000 AND WORKING IN DEPT 10,20 OR 30 EXCEPT ALL SALESMEN.
    SELECT *
    FROM EMP
    WHERE SAL NOT BETWEEN 1000 AND 2000 AND DEPTNO IN (10,20,30) AND JOB!='SALESMAN';

    22] LIST THE DEPARTMENT NAMES WHICH ARE HAVING LETTER ‘O’ IN THEIR LOCATIONS AS WELL AS THEIR DEPARTMENT NAMES.
    SELECT DNAME
    FROM DEPT
    WHERE LOC LIKE '%O%' AND DNAME LIKE '%O%';

    23] DISPLAY ALL THE EMPLOYEES WHOSE JOB HAS STRING ‘MAN’ IN IT.
    SELECT *
    FROM EMP
    WHERE JOB LIKE '%MAN%';

    24] LIST THE EMPLOYEES WHO ARE HIRED AFTER 82 AND BEFORE 87.
    SELECT *
    FROM EMP
    WHERE HIREDATE BETWEEN '01-JAN-83' AND '31-DEC-86';

    25] WAQTD ALL THE DETAILS OF EMPLOYEES HIRED IN NOVEMBER AND DECEMBER.
    SELECT *
    FROM EMP
    WHERE HIREDATE LIKE '%NOV%' OR HIREDATE LIKE '%DEC%';
    26] LIST ALL THE EMPLOYEE NAMES AND COMISSION FOR THOSE EMPLOYEES WHO EARN COMISSION MORE THAN THEIR SALARY.
    SELECT ENAME, COMM
    FROM EMP
    WHERE COMM>SAL;
    27] WAQTD NAME AND DESIGNATION FOR ALL THE EMPLOYEES HAVING REPORTING MANAGERS AND ALSO THRIE NAMES STARTING WITH
    ‘S’.
    SELECT ENAME, JOB
    FROM EMP
    WHERE MGR IS NOT NULL AND ENAME LIKE 'S%';

    28] WAQTD NAME AND SALARY OF ALL THE EMPLOYEES IF THEIR ANNUAL SALARY ENDS WITH ‘0’.
    SELECT ENAME, SAL
    FROM EMP
    WHERE SAL*12 LIKE '%0';

    29] WAQTD NAME OF THE EMPLOYEE HAVING ATLEAST 2L’s IN HIS NAME.
    SELECT ENAME
    FROM EMP
    WHERE ENAME LIKE '%L%L%';

    30] WAQTD NAME OF THE EMPLOYEES WHOSE NAME STARTS WITH A ‘VOWEL’.
    SELECT ENAME
    FROM EMP
    WHERE ENAME LIKE 'A%' OR ENAME LIKE 'E%' OR ENAME LIKE 'I%' OR ENAME LIKE 'O%' OR ENAME LIKE 'U%’;

    17] DISPLAY ALL THE EMPLOYEE WHO ARE ‘SALESMAN’S HAVING ‘E’ AS THE LAST BUT ONE CHARACTER IN ENAME BUT SALARY HAVING
    EXACTLY 4 CHARACTER.
    SELECT *
    FROM EMP
    WHERE ENAME LIKE '%E_' AND JOB='SALESMAN' AND SAL LIKE '____';

    You might also like