[go: up one dir, main page]
Scribd
Upload
13 views13 pages

Monday 20 SQL

The document contains SQL queries executed on an EMP table, retrieving employee details based on various conditions such as job titles, hire dates, and department numbers. It showcases the results of these queries, including employees who are managers, clerks, or presidents, along with their salaries and commissions. Additionally, it demonstrates the use of logical operators and filtering criteria in SQL to extract specific employee records.

Uploaded by

rohandingra1997
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
13 views13 pages

Monday 20 SQL

The document contains SQL queries executed on an EMP table, retrieving employee details based on various conditions such as job titles, hire dates, and department numbers. It showcases the results of these queries, including employees who are managers, clerks, or presidents, along with their salaries and commissions. Additionally, it demonstrates the use of logical operators and filtering criteria in SQL to extract specific employee records.

Uploaded by

rohandingra1997
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/ 13

SQL> SELECT *

2 FROM EMP
3 WHERE COMM+2000 AND HIREDATE>'31-DEC-80'AND DEPT=
4
SQL> SELECT *
2 FROM EMP
3 WHERE JOB='MANAGER'OR JOB='CLERK'OR JOB='PRESIDENT';

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

EMPNO ENAME JOB MGR HIREDATE SAL COMM


---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 09-JUN-81 2450
10

7839 KING PRESIDENT 17-NOV-81 5000


10

7876 ADAMS CLERK 7788 23-MAY-87 1100


20

EMPNO ENAME JOB MGR HIREDATE SAL COMM


---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7900 JAMES CLERK 7698 03-DEC-81 950
30

7934 MILLER CLERK 7782 23-JAN-82 1300


10

8 rows selected.

SQL> SET PAGES 100 LINES 100


SQL> /

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
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
7934 MILLER CLERK 7782 23-JAN-82 1300
10

8 rows selected.

SQL> SELECT *
2 FROM EMP
3 WHERE JOB='MANAGER'OR JOB='CLERK'OR JOB='PRESIDENT';

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
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
7934 MILLER CLERK 7782 23-JAN-82 1300
10

8 rows selected.

SQL> SELECT *
2 FROM EMP
3 WHERE JOB='MANAGER'OR JOB='CLERK'OR JOB='PRESIDENT';

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
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
7934 MILLER CLERK 7782 23-JAN-82 1300
10

8 rows selected.

SQL> SELECT *
2 FROM EMP
3 WHERE JOB='MANAGER'OR JOB='CLERK'OR JOB='RESIDENT' AND DEPTNO=20 OR DEPTNO=30;

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
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
7934 MILLER CLERK 7782 23-JAN-82 1300
10

11 rows selected.

SQL> SELECT *
2 FROM EMP
3 WHERE JOB='MANAGER'OR JOB='CLERK'OR JOB='RESIDENT' AND DEPTNO=20 AND
DEPTNO=30;

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
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

7 rows selected.

SQL> SELECT EMP.*


2
SQL> SELE

SQL> SELECT EMP.*,COMM+2000,SAL*12


2 FROM EMP
3 WHERE HIREDATE>'31-DEC-80'
4 AND DEPTNO=20 AND DEPTNO=30;

no rows selected

SQL> SELECT EMP.*,COMM+2000,SAL*12


2 FROM EMP
3 WHERE HIREDATE>'31-DEC-80'
4 AND DEPTNO=20 OR DEPTNO=30;

EMPNO ENAME JOB MGR HIREDATE SAL COMM


DEPTNO COMM+2000
---------- ---------- --------- ---------- --------- ---------- ----------
---------- ----------
SAL*12
----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30 2300
19200

7521 WARD SALESMAN 7698 22-FEB-81 1250 500


30 2500
15000

7566 JONES MANAGER 7839 02-APR-81 2975


20
35700

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400


30 3400
15000

7698 BLAKE MANAGER 7839 01-MAY-81 2850


30
34200

7788 SCOTT ANALYST 7566 19-APR-87 3000


20
36000

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0


30 2000
18000

7876 ADAMS CLERK 7788 23-MAY-87 1100


20
13200

7900 JAMES CLERK 7698 03-DEC-81 950


30
11400

7902 FORD ANALYST 7566 03-DEC-81 3000


20
36000

10 rows selected.

SQL> SELECT *
2 FROM EMP
3 WHERE HIREDATE<'01-JAN-87'
4 AND JOB='PRESIDENT'OR 'MANAGER'
5 AND SAL<5000;
AND SAL<5000
*
ERROR at line 5:
ORA-00920: invalid relational operator

SQL> SELECT *
2 FROM EMP
3 WHERE HIREDATE<'01-JAN-87'
4 AND JOB='PRESIDENT' OR JOB='MANAGER'
5 AND SAL<5000;

EMPNO ENAME JOB MGR HIREDATE SAL COMM


DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------
----------
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
7839 KING PRESIDENT 17-NOV-81 5000
10

SQL> SELECT *
2 FROM EMP
3 WHERE HIREDATE<'01-JAN-87'
4 AND JOB='PRESIDENT' OR JOB='MANAGER'
5 AND SAL< 5000;

EMPNO ENAME JOB MGR HIREDATE SAL COMM


DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------
----------
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
7839 KING PRESIDENT 17-NOV-81 5000
10

SQL> SELECT *
2 FROM EMP
3 WHERE HIREDATE<'01-JAN-87'
4 AND SAL<5000
5 AND JOB NOTIN('PRESIDENT','MANAGER');
AND JOB NOTIN('PRESIDENT','MANAGER')
*
ERROR at line 5:
ORA-00920: invalid relational operator

SQL> SELECT *
2 FROM EMP
3 WHERE HIREDATE<'01-JAN-87'
4 AND SAL<5000
5 AND JOB NOT IN ('PRESIDENT','MANAGER');

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
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30
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

8 rows selected.

SQL> SELECT *
2 FROM EMP;

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> SELE

SQL> SELECT ENAME,JOB,SAL,COMM


2 FROM EMP
3 WHERE JOB=('CLERK','ANALYST')
4 AND SAL>1111;
WHERE JOB=('CLERK','ANALYST')
*
ERROR at line 3:
ORA-01797: this operator must be followed by ANY or ALL

SQL> SELECT ENAME,JOB,SAL,COMM


2 FROM EMP
3 WHERE JOB IN ('CLERK','ANALYST');

ENAME JOB SAL COMM


---------- --------- ---------- ----------
SMITH CLERK 800
SCOTT ANALYST 3000
ADAMS CLERK 1100
JAMES CLERK 950
FORD ANALYST 3000
MILLER CLERK 1300

6 rows selected.

SQL> SELECT ENAME,JOB,SAL,COMM


2 FROM EMP
3 WHERE JOB IN ('CLERK','ANALYST')
4 AND SAL>1111
5 AND HIREDATE NOT IN ('01-JUN-81);
ERROR:
ORA-01756: quoted string not properly terminated

SQL> SELECT ENAME,JOB,SAL,COMM


2 FROM EMP
3 WHERE JOB IN ('CLERK','ANALYST')
4 AND SAL>1111
5 AND HIREDATE NOT IN ('01-JUN-81');

ENAME JOB SAL COMM


---------- --------- ---------- ----------
SCOTT ANALYST 3000
FORD ANALYST 3000
MILLER CLERK 1300

SQL> DESC EMP;


Name Null? Type
----------------------------------------------------- --------
------------------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

SQL> SELECT EMP.*,SAL*1.25


2 FROM EMP
3 WHERE DEPTNO NOT IN
4
SQL> SELECT EMP.*,SAL*1.25
2 FROM EMP
3 WHERE DEPTNO NOT IN (10,20)
4 AND SAL<11111
5 AND MGR NOT IN (7560,7777,7090,7434);

EMPNO ENAME JOB MGR HIREDATE SAL COMM


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

6 rows selected.

SQL> SELCT *
SP2-0042: unknown command "SELCT *" - rest of line ignored.
SQL> FROM TAB
SP2-0042: unknown command "FROM TAB" - rest of line ignored.
SQL> SELECT * FROM TAB
2
SQL> SELECT *
2 FROM TAB;

TNAME TABTYPE CLUSTERID


------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE

SQL> SELECT * FROM DEPT;

DEPTNO DNAME LOC


---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SQL> SELECT LOC


2 FROM DEPT
3 WHERE DNAME IN ('ACCOUNTING','SALES'
4
SQL> SELECT LOC
2 FROM DEPT
3 WHERE DNAME IN ('ACCOUNTING','SALES');

LOC
-------------
NEW YORK
CHICAGO

SQL> SELECT * FROM SALGRADE;

GRADE LOSAL HISAL


---------- ---------- ----------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999

SQL> SELECT GRADE


2 FROM SALGRADE
3 WHERE HISAL IN (2000,3000,9999);

GRADE
----------
3
4
5

SQL> SELECT EMPNO,ENAME,MGR,SAL*12


2 FROM EMP
3 WHERE HIREDATE IN ('01-JAN-80','01-JAN-81')
4 AND DEPTNO IN (10,20,30)
5 AND ENAME NOT IN ('SMITH','JONES','ADAMS')
6 AND JOB NOT IN ('CLERK')
7 AND SAL>9999;

no rows selected

SQL> SELECT EMPNO,ENAME,MGR,SAL*12


2 FROM EMP
3 WHERE HIREDATE>='01-JAN-80'
4 HIRREDATE<='31-JAN-80'
5
SQL>
SQL> SELECT EMPNO,ENAME,MGR,SAL*12
2 FROM EMP
3 WHERE HIREDATE>='01-JAN-80'
4 HIREDATE<='31-JAN-80'
5 HIREDATE>='01-JAN-81'
6 HIREDATE<='31-DEC-81';
HIREDATE<='31-JAN-80'
*
ERROR at line 4:
ORA-00933: SQL command not properly ended

SQL> SELECT EMPNO,ENAME,MGR,SAL*12


2 FROM EMP
3 WHERE HIREDATE>='01-JAN-80'
4 HIREDATE<='31-DEC-80'
5 HIREDATE>='01-JAN-81'
6 HIREDATE<='31-DEC-81';
HIREDATE<='31-DEC-80'
*
ERROR at line 4:
ORA-00933: SQL command not properly ended

SQL> SELECT EMPNO,ENAME,MGR,SAL*12


2 FROM EMP
3 WHERE HIREDATE>='01-JAN-80'
4 HIREDATE<='31-DEC-80';
HIREDATE<='31-DEC-80'
*
ERROR at line 4:
ORA-00933: SQL command not properly ended

SQL> SELECT EMPNO,ENAME,MGR,SAL*12


2 FROM EMP
3 WHERE HIREDATE>='01-JAN-80';

EMPNO ENAME MGR SAL*12


---------- ---------- ---------- ----------
7369 SMITH 7902 9600
7499 ALLEN 7698 19200
7521 WARD 7698 15000
7566 JONES 7839 35700
7654 MARTIN 7698 15000
7698 BLAKE 7839 34200
7782 CLARK 7839 29400
7788 SCOTT 7566 36000
7839 KING 60000
7844 TURNER 7698 18000
7876 ADAMS 7788 13200
7900 JAMES 7698 11400
7902 FORD 7566 36000
7934 MILLER 7782 15600

14 rows selected.

SQL> SELECT *
2 FROM EMP
3 WHERE (HIREDATE>='01-JAN-80';
WHERE (HIREDATE>='01-JAN-80'
*
ERROR at line 3:
ORA-00907: missing right parenthesis

SQL> SELECT *
2 FROM EMP
3 WHERE (HIREDATE>='01-JAN-80'
4 SELECT *
5 FROM EMP
6 WHERE (HIREDATE>='01-JAN-80';
WHERE (HIREDATE>='01-JAN-80'
*
ERROR at line 3:
ORA-00907: missing right parenthesis

SQL> SELECT *
2 FROM EMP
3 WHERE (HIREDATE>='01-JAN-80'
4 HIREDATE<='31-DEC-80'
5 HIREDATE>='01-JAN-81'
6 HIREDATE<='31-DEC-81');
WHERE (HIREDATE>='01-JAN-80'
*
ERROR at line 3:
ORA-00907: missing right parenthesis

SQL> SELECT *
2 FROM EMP
3 WHERE HIREDATE>='01-JAN-80';HIREDATE<='31-DEC-80';
WHERE HIREDATE>='01-JAN-80';HIREDATE<='31-DEC-80'
*
ERROR at line 3:
ORA-00911: invalid character

SQL> SELECT *
2 FROM EMP
3 WHERE HIREDATE>='01-JAN-80'HIREDATE<='31-DEC-80';
WHERE HIREDATE>='01-JAN-80'HIREDATE<='31-DEC-80'
*
ERROR at line 3:
ORA-00933: SQL command not properly ended

SQL> SELECT EMPNO, ENAME, MGR, SAL*12 AS ANNUAL_SALARYFROM EMPWHERE (HIREDATE >=
'01-JAN-80' AND HIR
EDATE <= '31-DEC-80') OR (HIREDATE >= '01-JAN-81' AND HIREDATE <= '31-DEC-81');
SELECT EMPNO, ENAME, MGR, SAL*12 AS ANNUAL_SALARYFROM EMPWHERE (HIREDATE >= '01-
JAN-80' AND HIREDATE
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

SQL> SELECT EMPNO, ENAME, MGR, SAL*12 AS ANNUAL_SALARY


2 FROM EMP
3 WHERE (HIREDATE >= '01-JAN-80' AND HIREDATE <= '31-DEC-80')
4 OR (HIREDATE >= '01-JAN-81' AND HIREDATE <= '31-DEC-81');

EMPNO ENAME MGR ANNUAL_SALARY


---------- ---------- ---------- -------------
7369 SMITH 7902 9600
7499 ALLEN 7698 19200
7521 WARD 7698 15000
7566 JONES 7839 35700
7654 MARTIN 7698 15000
7698 BLAKE 7839 34200
7782 CLARK 7839 29400
7839 KING 60000
7844 TURNER 7698 18000
7900 JAMES 7698 11400
7902 FORD 7566 36000

11 rows selected.

SQL> SELECT EMPNO, ENAME, MGR, SAL*12 AS ANNUAL_SALARY


2 FROM EMP
3 WHERE (HIREDATE >= '01-JAN-80' AND HIREDATE <= '31-DEC-80')
4 OR (HIREDATE >= '01-JAN-81' AND HIREDATE <= '31-DEC-81');

EMPNO ENAME MGR ANNUAL_SALARY


---------- ---------- ---------- -------------
7369 SMITH 7902 9600
7499 ALLEN 7698 19200
7521 WARD 7698 15000
7566 JONES 7839 35700
7654 MARTIN 7698 15000
7698 BLAKE 7839 34200
7782 CLARK 7839 29400
7839 KING 60000
7844 TURNER 7698 18000
7900 JAMES 7698 11400
7902 FORD 7566 36000

11 rows selected.

SQL> SELECT EMPNO, ENAME, MGR, SAL*12 AS ANNUAL_SALARY


2 FROM EMP
3 WHERE (HIREDATE >= '01-JAN-80' AND HIREDATE <= '31-DEC-80')
4 OR (HIREDATE >= '01-JAN-81' AND HIREDATE <= '31-DEC-81')
5 AND DEPTNO IN (10,20,30)
6 AND ENAME NOT IN ('SMITH','JONES','ADAMS')
7 AND JOB NOT IN ('CLERK')
8 AND SAL>9999;

EMPNO ENAME MGR ANNUAL_SALARY


---------- ---------- ---------- -------------
7369 SMITH 7902 9600

SQL> SELECT EMPNO, ENAME, MGR, SAL*12 AS ANNUAL_SALARY


2 FROM EMP
3 WHERE ((HIREDATE >= '01-JAN-80' AND HIREDATE <= '31-DEC-80')
4 OR (HIREDATE >= '01-JAN-81' AND HIREDATE <= '31-DEC-81'))
5 AND DEPTNO IN (10, 20, 30)
6 AND ENAME NOT IN ('SMITH', 'JONES', 'ADAMS')
7 AND JOB NOT IN ('CLERK')
8 AND SAL > 9999;

no rows selected

SQL> SELECT EMPNO, ENAME, MGR, SAL*12 AS ANNUAL_SALARY


2 FROM EMP
3 WHERE ((HIREDATE >= '01-JAN-80' AND HIREDATE <= '31-DEC-80')
4 OR (HIREDATE >= '01-JAN-81' AND HIREDATE <= '31-DEC-81'))
5 AND DEPTNO IN (10, 20, 30)
6 AND ENAME NOT IN ('SMITH', 'JONES', 'ADAMS','CLARK')
7 AND SAL > 9999;

no rows selected

SQL>

You might also like