Scribd
Upload
39 views

Assignment 1

The document contains the output of multiple SQL queries on the EMP table of a database. The queries select records based on various conditions on columns like COMM, MGR, JOB, ENAME, HIREDATE, SAL, DEPTNO.

Uploaded by

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

Assignment 1

The document contains the output of multiple SQL queries on the EMP table of a database. The queries select records based on various conditions on columns like COMM, MGR, JOB, ENAME, HIREDATE, SAL, DEPTNO.

Uploaded by

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

SQL> select * from emp where comm is null;

EMPNO ENAME JOB MGR HIREDATE SAL COMM


DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------
----------
7369 SMITH CLERK 7902 17-DEC-80 800
20
7566 JONES MANAGER 7839 02-APR-81 2975
20
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30
7782 CLARK MANAGER 7839 09-JUN-81 2450
10
7788 SCOTT ANALYST 7566 19-APR-87 3000
20
7839 KING PRESIDENT 17-NOV-81 5000
10
7876 ADAMS CLERK 7788 23-MAY-87 1100
20
7900 JAMES CLERK 7698 03-DEC-81 950
30
7902 FORD ANALYST 7566 03-DEC-81 3000
20
7934 MILLER CLERK 7782 23-JAN-82 1300
10

10 rows selected.

SQL> select * from emp where mgr is null;

EMPNO ENAME JOB MGR HIREDATE SAL COMM


DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------
----------
7839 KING PRESIDENT 17-NOV-81 5000
10

SQL> select * from emp where job in 'SALESMAN' and deptno in 30;

EMPNO ENAME JOB MGR HIREDATE SAL COMM


DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------
----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30

SQL> select * from emp where sal > 1500 and job in 'SALESMAN' and deptno in 30;

EMPNO ENAME JOB MGR HIREDATE SAL COMM


DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------
----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30

SQL> select * from emp where ename like 'A%' or ename like 'S%';

EMPNO ENAME JOB MGR HIREDATE SAL COMM


DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------
----------
7369 SMITH CLERK 7902 17-DEC-80 800
20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30
7788 SCOTT ANALYST 7566 19-APR-87 3000
20
7876 ADAMS CLERK 7788 23-MAY-87 1100
20

SQL> select * from emp where deptno not in (10,20);

EMPNO ENAME JOB MGR HIREDATE SAL COMM


DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------
----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30
7900 JAMES CLERK 7698 03-DEC-81 950
30

6 rows selected.

SQL> select * from emp where ename not like 'S%';

EMPNO ENAME JOB MGR HIREDATE SAL COMM


DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------
----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30
7566 JONES MANAGER 7839 02-APR-81 2975
20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30
7782 CLARK MANAGER 7839 09-JUN-81 2450
10
7839 KING PRESIDENT 17-NOV-81 5000
10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30
7876 ADAMS CLERK 7788 23-MAY-87 1100
20
7900 JAMES CLERK 7698 03-DEC-81 950
30
7902 FORD ANALYST 7566 03-DEC-81 3000
20
7934 MILLER CLERK 7782 23-JAN-82 1300
10

12 rows selected.

SQL> select * from emp where mgr is not null and deptno in 10;

EMPNO ENAME JOB MGR HIREDATE SAL COMM


DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------
----------
7782 CLARK MANAGER 7839 09-JUN-81 2450
10
7934 MILLER CLERK 7782 23-JAN-82 1300
10

SQL> select * from emp where comm is null and job in 'CLERK';

EMPNO ENAME JOB MGR HIREDATE SAL COMM


DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------
----------
7369 SMITH CLERK 7902 17-DEC-80 800
20
7876 ADAMS CLERK 7788 23-MAY-87 1100
20
7900 JAMES CLERK 7698 03-DEC-81 950
30
7934 MILLER CLERK 7782 23-JAN-82 1300
10

SQL> select * from emp where mgr is null and deptno in (10,30);

EMPNO ENAME JOB MGR HIREDATE SAL COMM


DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------
----------
7839 KING PRESIDENT 17-NOV-81 5000
10

SQL> select * from emp where job in 'SALESMAN' and deptno in 30 and sal > 2450;

no rows selected

SQL> select * from emp where job in 'ANALYST' and deptno in 20 and sal > 2500;

EMPNO ENAME JOB MGR HIREDATE SAL COMM


DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------
----------
7788 SCOTT ANALYST 7566 19-APR-87 3000
20
7902 FORD ANALYST 7566 03-DEC-81 3000
20

SQL> select * from emp where ename like 'M%' or ename like 'J%';

EMPNO ENAME JOB MGR HIREDATE SAL COMM


DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------
----------
7566 JONES MANAGER 7839 02-APR-81 2975
20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30
7900 JAMES CLERK 7698 03-DEC-81 950
30
7934 MILLER CLERK 7782 23-JAN-82 1300
10

SQL> select emp.* , sal*12 "annual salary" from emp where deptno not in 30;

EMPNO ENAME JOB MGR HIREDATE SAL COMM


DEPTNO annual salary
---------- ---------- --------- ---------- --------- ---------- ----------
---------- -------------
7369 SMITH CLERK 7902 17-DEC-80 800
20 9600
7566 JONES MANAGER 7839 02-APR-81 2975
20 35700
7782 CLARK MANAGER 7839 09-JUN-81 2450
10 29400
7788 SCOTT ANALYST 7566 19-APR-87 3000
20 36000
7839 KING PRESIDENT 17-NOV-81 5000
10 60000
7876 ADAMS CLERK 7788 23-MAY-87 1100
20 13200
7902 FORD ANALYST 7566 03-DEC-81 3000
20 36000
7934 MILLER CLERK 7782 23-JAN-82 1300
10 15600

8 rows selected.

SQL> select * from emp where ename not like '%ES' or ename not like '%R';

EMPNO ENAME JOB MGR HIREDATE SAL COMM


DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------
----------
7369 SMITH CLERK 7902 17-DEC-80 800
20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30
7566 JONES MANAGER 7839 02-APR-81 2975
20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30
7782 CLARK MANAGER 7839 09-JUN-81 2450
10
7788 SCOTT ANALYST 7566 19-APR-87 3000
20
7839 KING PRESIDENT 17-NOV-81 5000
10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30
7876 ADAMS CLERK 7788 23-MAY-87 1100
20
7900 JAMES CLERK 7698 03-DEC-81 950
30
7902 FORD ANALYST 7566 03-DEC-81 3000
20
7934 MILLER CLERK 7782 23-JAN-82 1300
10

14 rows selected.

SQL> select emp.*,sal*1.1 "hike in salary" from emp where mgr is not null and
deptno in 10;

EMPNO ENAME JOB MGR HIREDATE SAL COMM


DEPTNO hike in salary
---------- ---------- --------- ---------- --------- ---------- ----------
---------- --------------
7782 CLARK MANAGER 7839 09-JUN-81 2450
10 2695
7934 MILLER CLERK 7782 23-JAN-82 1300
10 1430

SQL> select * from emp where job in 'SALESMAN' and ename like '%E' and sal like
'____';

no rows selected

SQL> select * from emp where hiredate > '31-DEC-81';

EMPNO ENAME JOB MGR HIREDATE SAL COMM


DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------
----------
7788 SCOTT ANALYST 7566 19-APR-87 3000
20
7876 ADAMS CLERK 7788 23-MAY-87 1100
20
7934 MILLER CLERK 7782 23-JAN-82 1300
10

SQL> select * from emp where hiredate like '___FEB___';

EMPNO ENAME JOB MGR HIREDATE SAL COMM


DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------
----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30

SQL> select * from emp where job not in ('MANAGER','CLERK') AND DEPTNO NOT IN
(10,20) AND SAL BETWEEN 1000 AND 3000;

EMPNO ENAME JOB MGR HIREDATE SAL COMM


DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------
----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30

SQL> SELECT * FROM EMP WHERE SAL NOT BETWEEN 1000 AND 2000 AND DEPTNO IN (10,20,30)
AND JOB NOT IN 'SALESMAN';

EMPNO ENAME JOB MGR HIREDATE SAL COMM


DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------
----------
7369 SMITH CLERK 7902 17-DEC-80 800
20
7566 JONES MANAGER 7839 02-APR-81 2975
20
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30
7782 CLARK MANAGER 7839 09-JUN-81 2450
10
7788 SCOTT ANALYST 7566 19-APR-87 3000
20
7839 KING PRESIDENT 17-NOV-81 5000
10
7900 JAMES CLERK 7698 03-DEC-81 950
30
7902 FORD ANALYST 7566 03-DEC-81 3000
20

8 rows selected.

SQL> SELECT * FROM DEPT WHERE LOC LIKE '%O%' AND DNAME LIKE '%O%';

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK

40 OPERATIONS BOSTON
SQL> SELECT * FROM EMP WHERE JOB LIKE '%MAN%';

EMPNO ENAME JOB MGR HIREDATE SAL COMM


DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------
----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30
7566 JONES MANAGER 7839 02-APR-81 2975
20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30
7782 CLARK MANAGER 7839 09-JUN-81 2450
10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30

7 rows selected.

SQL> SELECT * FROM EMP WHERE HIREDATE BETWEEN '01-JAN-83' AND '31-DEC-86';

no rows selected

SQL> SELECT * FROM EMP WHERE HIREDATE LIKE '___NOV___' OR HIREDATE LIKE
'___DEC___';

EMPNO ENAME JOB MGR HIREDATE SAL COMM


DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------
----------
7369 SMITH CLERK 7902 17-DEC-80 800
20
7839 KING PRESIDENT 17-NOV-81 5000
10
7900 JAMES CLERK 7698 03-DEC-81 950
30
7902 FORD ANALYST 7566 03-DEC-81 3000
20

SQL> SELECT ENAME,COMM FROM EMP WHERE COMM>SAL;

ENAME COMM

---------- ----------

MARTIN 1400

SQL> SELECT ENAME,JOB FROM EMP WHERE MGR IS NOT NULL AND ENAME LIKE 'S%';

ENAME JOB

---------- ---------
SMITH CLERK

SCOTT ANALYST

SQL> SELECT ENAME,SAL FROM EMP WHERE SAL*12 LIKE '%0';

ENAME SAL

---------- ----------

SMITH 800

ALLEN 1600

WARD 1250

JONES 2975

MARTIN 1250

BLAKE 2850

CLARK 2450

SCOTT 3000

KING 5000

TURNER 1500

ADAMS 1100

JAMES 950

FORD 3000

MILLER 1300

14 rows selected.

SQL> SELECT ENAME FROM EMP WHERE ENAME LIKE '%L%L%';

ENAME

----------

ALLEN

MILLER

SQL> SELECT ENAME FROM EMP WHERE ENAME LIKE 'A%' OR ENAME LIKE 'E%' OR ENAME LIKE
'I%' OR ENAME LIKE 'O%' OR ENAME LIKE 'U%';

ENAME

----------
ALLEN

ADAMS

You might also like