SELECT * 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 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 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 40
SELECT * FROM dept
2 ;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> SELECT ENAME,SAL FROM EMP;
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 AS NAME,SAL AS SALARY FROM EMP;
NAME SALARY
---------- ----------
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> ---COL ALIAS
SQL> SELECT ENAME AS NAME,SAL AS SALARY FROM EMP;
NAME SALARY
---------- ----------
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 NAME,SAL SALARY FROM EMP;
NAME SALARY
---------- ----------
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 name,SAL SALARY FROM EMP;
NAME SALARY
---------- ----------
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 "name",SAL SALARY FROM EMP;
name SALARY
---------- ----------
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 "name",SAL SALARY FROM EMP;
name SALARY
---------- ----------
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 name,SAL SALARY FROM EMP;
NAME SALARY
---------- ----------
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> cl scr
SQL> select ename,sal from emp
2 where sal > 2000;
ENAME SAL
---------- ----------
JONES 2975
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
FORD 3000
6 rows selected.
SQL> select ename,sal from emp
2 where sal < 2000;
ENAME SAL
---------- ----------
SMITH 800
ALLEN 1600
WARD 1250
MARTIN 1250
TURNER 1500
ADAMS 1100
JAMES 950
MILLER 1300
8 rows selected.
SQL> select ename,sal from emp
2 where sal between 2000 and 5000;
ENAME SAL
---------- ----------
JONES 2975
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
FORD 3000
6 rows selected.
SQL> cl scr
SQL> select ename,sal from emp
2 where sal in 3000,5000,2975,2000;
where sal in 3000,5000,2975,2000
*
ERROR at line 2:
ORA-00933: SQL command not properly ended
SQL> select ename,sal from emp
2 where sal in (3000,5000,2975,2000);
ENAME SAL
---------- ----------
JONES 2975
SCOTT 3000
KING 5000
FORD 3000
SQL> cl scr
SQL> --LIKE OPERATOR : STRING
SQL> SELECT * FROM EMP WHERE ENAME LIKE 'A%';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
SQL> SELECT * FROM EMP WHERE ENAME LIKE '_D%';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
SQL> SELECT * FROM EMP WHERE ENAME LIKE '%S';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7566 JONES MANAGER 7839 02-APR-81 2975 20
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
SQL> SELECT * FROM EMP WHERE ENAME LIKE '%A%';
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
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7 rows selected.
SQL> SELECT * FROM EMP WHERE ENAME LIKE '%A%A%';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
SQL> CL SCR
SQL> --- _ : ONE UNKNOWN CHR | % : GROUP OF UNKNOWN CHR
SQL> ---- -----------WILD CARD ------------------------------
SQL> SELECT * FROM EMP WHERE ENAME LIKE '____';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7839 KING PRESIDENT 17-NOV-81 5000 10
7902 FORD ANALYST 7566 03-DEC-81 3000 20
SQL> SELECT * FROM EMP WHERE ENAME LIKE '___D%';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
SQL> CL SR
SP2-0158: unknown CLEAR option "SR"
SQL> CL SCR
SQL> INSERT INTO EMP(EMPNO,ENAME) VALUES(111,'AAA_BBB');
1 row created.
SQL> CL SCR
SQL> COMMIT;
Commit complete.
SQL> CL SCR
SQL> SELECT * 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 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 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 40
111 AAA_BBB
15 rows selected.
SQL> SELECT * FROM EMP WHERE ENAME LIKE '___\_%' ESCAPE '\';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
111 AAA_BBB
SQL> SELECT * FROM EMP WHERE ENAME LIKE '___#_%' ESCAPE '#';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
111 AAA_BBB
SQL> CL SCR
SQL> SELECT ENAME,SAL FROM EMP
2 ORDER BY SAL;
ENAME SAL
---------- ----------
SMITH 800
JAMES 950
ADAMS 1100
WARD 1250
MARTIN 1250
MILLER 1300
TURNER 1500
ALLEN 1600
CLARK 2450
BLAKE 2850
JONES 2975
FORD 3000
SCOTT 3000
KING 5000
AAA_BBB
15 rows selected.
SQL> SELECT ENAME,SAL FROM EMP ORDER BY SAL DESC;
ENAME SAL
---------- ----------
AAA_BBB
KING 5000
FORD 3000
SCOTT 3000
JONES 2975
BLAKE 2850
CLARK 2450
ALLEN 1600
TURNER 1500
MILLER 1300
WARD 1250
MARTIN 1250
ADAMS 1100
JAMES 950
SMITH 800
15 rows selected.
SQL> CL SCR
SQL> ---TO HANDLE NULL
SQL> SELECT ENAME,SAL,COMM FROM EMP WHERE COMM IS NULL;
ENAME SAL COMM
---------- ---------- ----------
SMITH 800
JONES 2975
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
ADAMS 1100
JAMES 950
FORD 3000
MILLER 1300
AAA_BBB
11 rows selected.
SQL> SELECT ENAME,SAL,COMM FROM EMP WHERE COMM IS NOT NULL;
ENAME SAL COMM
---------- ---------- ----------
ALLEN 1600 300
WARD 1250 500
MARTIN 1250 1400
TURNER 1500 0
SQL> CL SCR
SQL> SELECT ENAME,SAL,COMM,SAL+COMM FROM EMP ;
ENAME SAL COMM SAL+COMM
---------- ---------- ---------- ----------
SMITH 800
ALLEN 1600 300 1900
WARD 1250 500 1750
JONES 2975
MARTIN 1250 1400 2650
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 1500 0 1500
ADAMS 1100
JAMES 950
FORD 3000
MILLER 1300
AAA_BBB
15 rows selected.
SQL> SELECT ENAME,SAL,COMM,SAL+COMM FROM EMP ;
ENAME SAL COMM SAL+COMM
---------- ---------- ---------- ----------
SMITH 800
ALLEN 1600 300 1900
WARD 1250 500 1750
JONES 2975
MARTIN 1250 1400 2650
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 1500 0 1500
ADAMS 1100
JAMES 950
FORD 3000
MILLER 1300
AAA_BBB
SQL> SELECT ENAME,SAL,COMM,SAL+NVL(COMM,0) FROM EMP ;
ENAME SAL COMM SAL+NVL(COMM,0)
---------- ---------- ---------- ---------------
SMITH 800 800
ALLEN 1600 300 1900
WARD 1250 500 1750
JONES 2975 2975
MARTIN 1250 1400 2650
BLAKE 2850 2850
CLARK 2450 2450
SCOTT 3000 3000
KING 5000 5000
TURNER 1500 0 1500
ADAMS 1100 1100
JAMES 950 950
FORD 3000 3000
MILLER 1300 1300
AAA_BBB
15 rows selected.
SQL> SELECT ENAME,SAL FROM EMP ORDER BY SAL DESC;
ENAME SAL
---------- ----------
AAA_BBB
KING 5000
FORD 3000
SCOTT 3000
JONES 2975
BLAKE 2850
CLARK 2450
ALLEN 1600
TURNER 1500
MILLER 1300
WARD 1250
MARTIN 1250
ADAMS 1100
JAMES 950
SMITH 800
15 rows selected.
SQL> SELECT ENAME,SAL FROM EMP ORDER BY NVL(SAL,0) DESC;
ENAME SAL
---------- ----------
KING 5000
FORD 3000
SCOTT 3000
JONES 2975
BLAKE 2850
CLARK 2450
ALLEN 1600
TURNER 1500
MILLER 1300
WARD 1250
MARTIN 1250
ADAMS 1100
JAMES 950
SMITH 800
AAA_BBB
15 rows selected.
SQL> CL SCR
SQL> SELECT ENAME,SAL FROM EMP ORDER BY SAL;
ENAME SAL
---------- ----------
SMITH 800
JAMES 950
ADAMS 1100
WARD 1250
MARTIN 1250
MILLER 1300
TURNER 1500
ALLEN 1600
CLARK 2450
BLAKE 2850
JONES 2975
FORD 3000
SCOTT 3000
KING 5000
AAA_BBB
15 rows selected.
SQL> SELECT ENAME,SAL FROM EMP ORDER BY 2;
ENAME SAL
---------- ----------
SMITH 800
JAMES 950
ADAMS 1100
WARD 1250
MARTIN 1250
MILLER 1300
TURNER 1500
ALLEN 1600
CLARK 2450
BLAKE 2850
JONES 2975
FORD 3000
SCOTT 3000
KING 5000
AAA_BBB
15 rows selected.
SQL> SELECT ENAME,SAL AS SALARY FROM EMP ORDER BY SALARY;
ENAME SALARY
---------- ----------
SMITH 800
JAMES 950
ADAMS 1100
WARD 1250
MARTIN 1250
MILLER 1300
TURNER 1500
ALLEN 1600
CLARK 2450
BLAKE 2850
JONES 2975
FORD 3000
SCOTT 3000
KING 5000
AAA_BBB
15 rows selected.
SQL> CL SCR
SQL> ---ORDER BY KE SATH : COL NUMBER | COL NAME | COL ALAIS ALL ALLOWED
SQL> ---WHERE KE SATH : COL NAME ALLOWED
SQL> SELECT ENAME,SAL AS SALARY FROM EMP WHERE SALARY > 2000;
SELECT ENAME,SAL AS SALARY FROM EMP WHERE SALARY > 2000
*
ERROR at line 1:
ORA-00904: "SALARY": invalid identifier
SQL> CL SCR
SQL> ---GROUP FUNCTION : IT WORKS ON GROUP OF DATA AND RETURN SINGLE VALUEN
SQL> SELECT MAX(SAL) FROM EMP;
MAX(SAL)
----------
5000
SQL> SELECT MIN(SAL) FROM EMP;
MIN(SAL)
----------
800
SQL> SELECT SUM(SAL) FROM EMP;
SUM(SAL)
----------
29025
SQL> SELECT AVG(SAL) FROM EMP;
AVG(SAL)
----------
2073.21429
SQL> SELECT COUNT(*) FROM EMP;
COUNT(*)
----------
15
SQL> CL SCR
SQL> --GROUP BY : FOR GROUPING
SQL> ---DEPTWISE NUMBER OF EMPLOYEE
SQL> SELECT DEPTNO,COUNT(*) FROM EMP GROUP BY DEPTNO;
DEPTNO COUNT(*)
---------- ----------
30 6
1
20 5
40 1
10 2
SQL> ---JOB WISE , NUMBER OF EMPLOYEE
SQL> SELECT JOB,COUNT(*) FROM EMP GROUP BY JOB;
JOB COUNT(*)
--------- ----------
1
CLERK 4
SALESMAN 4
PRESIDENT 1
MANAGER 3
ANALYST 2
6 rows selected.
SQL> SELECT JOB,SUM(SAL) FROM EMP GROUP BY JOB;
JOB SUM(SAL)
--------- ----------
CLERK 4150
SALESMAN 5600
PRESIDENT 5000
MANAGER 8275
ANALYST 6000
6 rows selected.
SQL> SELECT DEPTNO,JOB,SUM(SAL) FROM EMP GROUP BY DEPTNO,JOB;
DEPTNO JOB SUM(SAL)
---------- --------- ----------
20 CLERK 1900
30 SALESMAN 5600
20 MANAGER 2975
30 CLERK 950
10 PRESIDENT 5000
30 MANAGER 2850
10 MANAGER 2450
20 ANALYST 6000
40 CLERK 1300
10 rows selected.
SQL> SELECT DEPTNO,JOB,SUM(SAL) FROM EMP GROUP BY DEPTNO,JOB;CL SCR
2 .
SQL> CL SCR
SQL> ---IF WE ARE USING MULTIPLE COLUMN , SOME COL HAVING GROUP GROUP FUNCTION
SQL> --REST COL MUST BE IN GROUP BY
SQL> SELECT DEPTNO,JOB,SUM(SAL) FROM EMP GROUP BY DEPTNO;
SELECT DEPTNO,JOB,SUM(SAL) FROM EMP GROUP BY DEPTNO
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression
SQL> SELECT DEPTNO,JOB,SUM(SAL) FROM EMP GROUP BY DEPTNO,JOB;
DEPTNO JOB SUM(SAL)
---------- --------- ----------
20 CLERK 1900
30 SALESMAN 5600
20 MANAGER 2975
30 CLERK 950
10 PRESIDENT 5000
30 MANAGER 2850
10 MANAGER 2450
20 ANALYST 6000
40 CLERK 1300
10 rows selected.
SQL> SELECT DEPTNO,JOB,SUM(SAL) FROM EMP;
SELECT DEPTNO,JOB,SUM(SAL) FROM EMP
*
ERROR at line 1:
ORA-00937: not a single-group group function
SQL> CL SCR
SQL> SELECT DEPTNO,JOB,SUM(SAL) FROM EMP GROUP BY DEPTNO,JOB
2
SQL> /
DEPTNO JOB SUM(SAL)
---------- --------- ----------
20 CLERK 1900
30 SALESMAN 5600
20 MANAGER 2975
30 CLERK 950
10 PRESIDENT 5000
30 MANAGER 2850
10 MANAGER 2450
20 ANALYST 6000
40 CLERK 1300
10 rows selected.
SQL> SELECT DEPTNO,JOB,SUM(SAL) FROM EMP WHERE SUM(SAL)>2000 GROUP BY DEPTNO,JOB
2 /
SELECT DEPTNO,JOB,SUM(SAL) FROM EMP WHERE SUM(SAL)>2000 GROUP BY DEPTNO,JOB
*
ERROR at line 1:
ORA-00934: group function is not allowed here
SQL> SELECT DEPTNO,JOB,SUM(SAL) FROM EMP GROUP BY DEPTNO,JOB
2 HAVING SUM(SAL) > 2000;
DEPTNO JOB SUM(SAL)
---------- --------- ----------
30 SALESMAN 5600
20 MANAGER 2975
10 PRESIDENT 5000
30 MANAGER 2850
10 MANAGER 2450
20 ANALYST 6000
6 rows selected.
SQL> CL SCR
SQL> SELECT DEPTNO,JOB,SUM(SAL) FROM EMP WHERE SAL>2000 GROUP BY DEPTNO,JOB
2 HAVING SUM(SAL) > 2000;
DEPTNO JOB SUM(SAL)
---------- --------- ----------
20 MANAGER 2975
10 PRESIDENT 5000
30 MANAGER 2850
10 MANAGER 2450
20 ANALYST 6000
SQL> CL SCR
SQL> ED
Wrote file afiedt.buf
1 SELECT DEPTNO,JOB,SUM(SAL) FROM EMP WHERE SAL>2000 GROUP BY DEPTNO,JOB
2* HAVING SUM(SAL) > 2000
SQL> CL SCR
SQL> /
DEPTNO JOB SUM(SAL)
---------- --------- ----------
20 MANAGER 2975
10 PRESIDENT 5000
30 MANAGER 2850
10 MANAGER 2450
20 ANALYST 6000
SQL> RUN
1 SELECT DEPTNO,JOB,SUM(SAL) FROM EMP WHERE SAL>2000 GROUP BY DEPTNO,JOB
2* HAVING SUM(SAL) > 2000
DEPTNO JOB SUM(SAL)
---------- --------- ----------
20 MANAGER 2975
10 PRESIDENT 5000
30 MANAGER 2850
10 MANAGER 2450
20 ANALYST 6000
SQL> SAV D:\T.SQL
Created file D:\T.SQL
SQL> CL SCR
SQL> GET D:\T.SQL
1 SELECT DEPTNO,JOB,SUM(SAL) FROM EMP WHERE SAL>2000 GROUP BY DEPTNO,JOB
2* HAVING SUM(SAL) > 2000
SQL> @D:\T.SQL
DEPTNO JOB SUM(SAL)
---------- --------- ----------
20 MANAGER 2975
10 PRESIDENT 5000
30 MANAGER 2850
10 MANAGER 2450
20 ANALYST 6000
SQL> START D:\T.SQL
DEPTNO JOB SUM(SAL)
---------- --------- ----------
20 MANAGER 2975
10 PRESIDENT 5000
30 MANAGER 2850
10 MANAGER 2450
20 ANALYST 6000
No comments:
Post a Comment