SQL> --------NUMBER FUNCTIONS------------
SQL> SELECT ROUND(45,923) FROM DUAL;
ROUND(45,923)
-------------
45
SQL> ED
Wrote file afiedt.buf
1* SELECT ROUND(44.569) FROM DUAL
SQL> /
ROUND(44.569)
-------------
45
SQL> ED
Wrote file afiedt.buf
1* SELECT ROUND(44.569,2) FROM DUAL
SQL> /
ROUND(44.569,2)
---------------
44.57
SQL> ED
Wrote file afiedt.buf
1 SELECT ROUND(33.987),
2 ROUND(23.569,3),
3* ROUND(94.34,2) FROM DUAL
SQL> /
ROUND(33.987) ROUND(23.569,3) ROUND(94.34,2)
------------- --------------- --------------
34 23.569 94.34
SQL> ED
Wrote file afiedt.buf
1 SELECT ROUND(33.987),
2 ROUND(23.569,1),
3* ROUND(94.34,2) FROM DUAL
SQL> /
ROUND(33.987) ROUND(23.569,1) ROUND(94.34,2)
------------- --------------- --------------
34 23.6 94.34
SQL> ED
Wrote file afiedt.buf
1* SELECT ROUND(23.569) FROM DUAL
SQL> /
ROUND(23.569)
-------------
24
SQL> ED
Wrote file afiedt.buf
1* SELECT ROUND(23.569,2) FROM DUAL
SQL> /
ROUND(23.569,2)
---------------
23.57
SQL> ED
Wrote file afiedt.buf
1* SELECT ROUND(23.569,-1), ROUND(44.569,0) FROM DUAL
SQL> /
ROUND(23.569,-1) ROUND(44.569,0)
---------------- ---------------
20 45
SQL> ED
Wrote file afiedt.buf
1* SELECT ROUND(230.569,-2), ROUND(44.569,0) FROM DUAL
SQL> /
ROUND(230.569,-2) ROUND(44.569,0)
----------------- ---------------
200 45
SQL> ED
Wrote file afiedt.buf
1* SELECT TRUNC(230.569,1), ROUND(44.569,-1) FROM DUAL
SQL> /
TRUNC(230.569,1) ROUND(44.569,-1)
---------------- ----------------
230.5 40
SQL> ED
Wrote file afiedt.buf
1* SELECT TRUNC(230.769) FROM DUAL
SQL> /
TRUNC(230.769)
--------------
230
SQL> -- TRUNC USED FOR AS IT IS PRINTING WHILE ROUND FUNCTION SIGNIFICANTS THE NUMBER------------
SQL> ED
Wrote file afiedt.buf
1* SELECT TRUNC(230.769,3) FROM DUAL
SQL> /
TRUNC(230.769,3)
----------------
230.769
SQL> SELECT MOD (8,2) FROM DUAL;
MOD(8,2)
----------
0
SQL> ED
Wrote file afiedt.buf
1* SELECT MOD (9,2) FROM DUAL
SQL> /
MOD(9,2)
----------
1
SQL> ED
Wrote file afiedt.buf
1* SELECT MOD (14,3) FROM DUAL
SQL> /
MOD(14,3)
----------
2
SQL> -- MOD FUNCTION RETURNS THE REMINDER------
SQL> --CHECK LEAP YEAR OR NOT----
SQL> SELECT MOD (2012,4) FROM DUAL;
MOD(2012,4)
-----------
0
SQL> ED
Wrote file afiedt.buf
1* SELECT MOD (2011,4) FROM DUAL
SQL> /
MOD(2011,4)
-----------
3
SQL> -- RETURNS '0' INDICATE THAT ITS A LEAP YEAR--
SQL> --- DATE FUNCTIONS -----------
SQL> SELECT SYSDATE FROM DUAL;
SYSDATE
---------
01-FEB-11
SQL> ED
Wrote file afiedt.buf
1* SELECT SYSDATE, SYSDATE + 5 FROM DUAL
SQL> /
SYSDATE SYSDATE+5
--------- ---------
01-FEB-11 06-FEB-11
SQL> ED
Wrote file afiedt.buf
1* SELECT SYSDATE, SYSDATE + 5 "AFTER 5 DAYS" FROM DUAL
SQL> /
SYSDATE AFTER 5 D
--------- ---------
01-FEB-11 06-FEB-11
SQL> ED
Wrote file afiedt.buf
1* SELECT SYSDATE, SYSDATE + 5 "AFT_5DAY" SYSDATE-'365' FROM DUAL
SQL> /
SELECT SYSDATE, SYSDATE + 5 "AFT_5DAY" SYSDATE-'365' FROM DUAL
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
SQL> ED
Wrote file afiedt.buf
1 SELECT SYSDATE,
2 SYSDATE + 5 "AFT_5DAY",
3* SYSDATE-'365' FROM DUAL
SQL> /
SYSDATE AFT_5DAY SYSDATE-'
--------- --------- ---------
01-FEB-11 06-FEB-11 01-FEB-10
SQL> ED
Wrote file afiedt.buf
1 SELECT SYSDATE,
2 SYSDATE + 5 "AFT_5DAY",
3* SYSDATE-365 FROM DUAL
SQL> /
SYSDATE AFT_5DAY SYSDATE-3
--------- --------- ---------
01-FEB-11 06-FEB-11 01-FEB-10
SQL> ED
Wrote file afiedt.buf
1 SELECT SYSDATE,
2 SYSDATE + 5 "AFT_5DAY",
3* SYSDATE-365 "BEF_365DAY" FROM DUAL
SQL> /
SYSDATE AFT_5DAY BEF_365DA
--------- --------- ---------
01-FEB-11 06-FEB-11 01-FEB-10
SQL> ED
Wrote file afiedt.buf
1 SELECT SYSDATE,
2 SYSDATE + 5 "AFT_5DAY",
3* SYSDATE-365 "BEF_365DAY" FROM DUAL
SQL> SELECT ENAME, ENAME, SYSDATE, HIREDATE, SYSDATE-HIREDATE "EMP_DAYS TOTAL" FROM EMP;
ENAME ENAME SYSDATE HIREDATE EMP_DAYS TOTAL
---------- ---------- --------- --------- --------------
01-FEB-11
SMITH SMITH 01-FEB-11 17-DEC-80 11003.4951
ALLEN ALLEN 01-FEB-11 20-FEB-81 10938.4951
WARD WARD 01-FEB-11 22-FEB-81 10936.4951
JONES JONES 01-FEB-11 02-APR-81 10897.4951
MARTIN MARTIN 01-FEB-11 28-SEP-81 10718.4951
BLAKE BLAKE 01-FEB-11 01-MAY-81 10868.4951
CLARK CLARK 01-FEB-11 09-JUN-81 10829.4951
SCOTT SCOTT 01-FEB-11 19-APR-87 8689.49508
KING KING 01-FEB-11 17-NOV-81 10668.4951
TURNER TURNER 01-FEB-11 08-SEP-81 10738.4951
ADAMS ADAMS 01-FEB-11 23-MAY-87 8655.49508
JAMES JAMES 01-FEB-11 03-DEC-81 10652.4951
FORD FORD 01-FEB-11 03-DEC-81 10652.4951
MILLER MILLER 01-FEB-11 23-JAN-82 10601.4951
15 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT ENAME,
2 ENAME,
3 HIREDATE,
4* SYSDATE-HIREDATE "EMP_DAYS TOTAL" FROM EMP
SQL> /
ENAME ENAME HIREDATE EMP_DAYS TOTAL
---------- ---------- --------- --------------
SMITH SMITH 17-DEC-80 11003.4954
ALLEN ALLEN 20-FEB-81 10938.4954
WARD WARD 22-FEB-81 10936.4954
JONES JONES 02-APR-81 10897.4954
MARTIN MARTIN 28-SEP-81 10718.4954
BLAKE BLAKE 01-MAY-81 10868.4954
CLARK CLARK 09-JUN-81 10829.4954
SCOTT SCOTT 19-APR-87 8689.49544
KING KING 17-NOV-81 10668.4954
TURNER TURNER 08-SEP-81 10738.4954
ADAMS ADAMS 23-MAY-87 8655.49544
JAMES JAMES 03-DEC-81 10652.4954
FORD FORD 03-DEC-81 10652.4954
MILLER MILLER 23-JAN-82 10601.4954
15 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT ENAME,
2 ENAME,
3 HIREDATE,
4* ROUND(SYSDATE-HIREDATE) "EMP_DAYS TOTAL" FROM EMP
SQL> /
ENAME ENAME HIREDATE EMP_DAYS TOTAL
---------- ---------- --------- --------------
SMITH SMITH 17-DEC-80 11003
ALLEN ALLEN 20-FEB-81 10938
WARD WARD 22-FEB-81 10936
JONES JONES 02-APR-81 10897
MARTIN MARTIN 28-SEP-81 10718
BLAKE BLAKE 01-MAY-81 10868
CLARK CLARK 09-JUN-81 10829
SCOTT SCOTT 19-APR-87 8689
KING KING 17-NOV-81 10668
TURNER TURNER 08-SEP-81 10738
ADAMS ADAMS 23-MAY-87 8655
JAMES JAMES 03-DEC-81 10652
FORD FORD 03-DEC-81 10652
MILLER MILLER 23-JAN-82 10601
15 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT ENAME,
2 ENAME,
3 HIREDATE,
4* TRUNC(SYSDATE-HIREDATE) "EMP_DAYS TOTAL" FROM EMP
SQL> /
ENAME ENAME HIREDATE EMP_DAYS TOTAL
---------- ---------- --------- --------------
SMITH SMITH 17-DEC-80 11003
ALLEN ALLEN 20-FEB-81 10938
WARD WARD 22-FEB-81 10936
JONES JONES 02-APR-81 10897
MARTIN MARTIN 28-SEP-81 10718
BLAKE BLAKE 01-MAY-81 10868
CLARK CLARK 09-JUN-81 10829
SCOTT SCOTT 19-APR-87 8689
KING KING 17-NOV-81 10668
TURNER TURNER 08-SEP-81 10738
ADAMS ADAMS 23-MAY-87 8655
JAMES JAMES 03-DEC-81 10652
FORD FORD 03-DEC-81 10652
MILLER MILLER 23-JAN-82 10601
15 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT ENAME,
2 ENAME,
3 DEPTNO,
4 HIREDATE,
5 TRUNC(SYSDATE-HIREDATE) "EMP_DAYS TOTAL" FROM EMP
6* WHERE DEPTNO = 10
SQL> /
ENAME ENAME DEPTNO HIREDATE EMP_DAYS TOTAL
---------- ---------- ---------- --------- --------------
CLARK CLARK 10 09-JUN-81 10829
KING KING 10 17-NOV-81 10668
MILLER MILLER 10 23-JAN-82 10601
SQL> ED
Wrote file afiedt.buf
1 SELECT EMPNO,
2 ENAME,
3 JOB,
4 DEPTNO,
5 HIREDATE,
6 TRUNC(SYSDATE-HIREDATE) "EMP_DAYS TOTAL" FROM EMP
7* WHERE DEPTNO = 10
SQL> /
EMPNO ENAME JOB DEPTNO HIREDATE EMP_DAYS TOTAL
---------- ---------- --------- ---------- --------- --------------
7782 CLARK MANAGER 10 09-JUN-81 10829
7839 KING PRESIDENT 10 17-NOV-81 10668
7934 MILLER CLERK 10 23-JAN-82 10601
SQL> ED
Wrote file afiedt.buf
1 SELECT EMPNO,
2 ENAME,
3 JOB,
4 DEPTNO,
5 HIREDATE,
6 TRUNC(SYSDATE-HIREDATE) "EMP_DAYS TOTAL",
7 (SYSDATE-HIREDATE)/30
8 FROM EMP
9* WHERE DEPTNO = 10
SQL> /
EMPNO ENAME JOB DEPTNO HIREDATE EMP_DAYS TOTAL (SYSDATE-HIREDATE)/30
---------- ---------- --------- ---------- --------- -------------- ---------------------
7782 CLARK MANAGER 10 09-JUN-81 10829 360.983443
7839 KING PRESIDENT 10 17-NOV-81 10668 355.616777
7934 MILLER CLERK 10 23-JAN-82 10601 353.383443
SQL> ed
Wrote file afiedt.buf
1 SELECT EMPNO,
2 ENAME,
3 JOB,
4 DEPTNO,
5 HIREDATE,
6 TRUNC(SYSDATE-HIREDATE) "EMP_DAYS TOTAL",
7 TRUNC((SYSDATE-HIREDATE)/30) "NO_MONTHS"
8 FROM EMP
9* WHERE DEPTNO = 10
SQL> /
EMPNO ENAME JOB DEPTNO HIREDATE EMP_DAYS TOTAL NO_MONTHS
---------- ---------- --------- ---------- --------- -------------- ----------
7782 CLARK MANAGER 10 09-JUN-81 10829 360
7839 KING PRESIDENT 10 17-NOV-81 10668 355
7934 MILLER CLERK 10 23-JAN-82 10601 353
SQL> ED
Wrote file afiedt.buf
1 SELECT EMPNO,
2 ENAME,
3 JOB,
4 DEPTNO,
5 HIREDATE,
6 TRUNC(SYSDATE-HIREDATE) "EMP_DAYS TOTAL",
7 TRUNC((SYSDATE-HIREDATE)/12) "NO_YEARS",
8 TRUNC((SYSDATE-HIREDATE)/30) "NO_MONTHS"
9 FROM EMP
10* WHERE DEPTNO = 10
SQL> /
EMPNO ENAME JOB DEPTNO HIREDATE EMP_DAYS TOTAL NO_YEARS NO_MONTHS
---------- ---------- --------- ---------- --------- -------------- ---------- ----------
7782 CLARK MANAGER 10 09-JUN-81 10829 902 360
7839 KING PRESIDENT 10 17-NOV-81 10668 889 355
7934 MILLER CLERK 10 23-JAN-82 10601 883 353
SQL> ED
Wrote file afiedt.buf
1 SELECT EMPNO,
2 ENAME,
3 JOB,
4 DEPTNO,
5 HIREDATE,
6 TRUNC(SYSDATE-HIREDATE) "EMP_DAYS TOTAL",
7 TRUNC((SYSDATE-HIREDATE)/365) "NO_YEARS",
8 TRUNC((SYSDATE-HIREDATE)/30) "NO_MONTHS"
9 FROM EMP
10* WHERE DEPTNO = 10
SQL> /
EMPNO ENAME JOB DEPTNO HIREDATE EMP_DAYS TOTAL NO_YEARS NO_MONTHS
---------- ---------- --------- ---------- --------- -------------- ---------- ----------
7782 CLARK MANAGER 10 09-JUN-81 10829 29 360
7839 KING PRESIDENT 10 17-NOV-81 10668 29 355
7934 MILLER CLERK 10 23-JAN-82 10601 29 353
SQL> ED
Wrote file afiedt.buf
1 SELECT EMPNO,
2 ENAME,
3 JOB,
4 DEPTNO,
5 HIREDATE,
6 TRUNC(SYSDATE-HIREDATE) "EMP_DAYS TOTAL",
7 TRUNC((SYSDATE-HIREDATE)/365) "NO_YEARS",
8 TRUNC((SYSDATE-HIREDATE)/30) "NO_MONTHS"
9 FROM EMP
10* WHERE DEPTNO = 10
SQL> SELECT * FROM EMP;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
111 BB 122
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
15 rows selected.
SQL> DELETE empno = 111
2 ;
DELETE empno = 111
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
SQL> DELETE EMP WHERE EMPNO = 111;
1 row deleted.
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 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 EMPNO,
2 ENAME,
3 JOB,
4 DEPTNO,
5 HIREDATE,
6 TRUNC(SYSDATE-HIREDATE) "EMP_DAYS TOTAL",
7 TRUNC((SYSDATE-HIREDATE)/365) "NO_YEARS",
8 TRUNC((SYSDATE-HIREDATE)/30) "NO_MONTHS"
9 FROM EMP
10 WHERE DEPTNO = 10
11 /
EMPNO ENAME JOB DEPTNO HIREDATE EMP_DAYS TOTAL NO_YEARS NO_MONTHS
---------- ---------- --------- ---------- --------- -------------- ---------- ----------
7782 CLARK MANAGER 10 09-JUN-81 10829 29 360
7839 KING PRESIDENT 10 17-NOV-81 10668 29 355
7934 MILLER CLERK 10 23-JAN-82 10601 29 353
Saturday, February 5, 2011
Saturday, January 22, 2011
ORACLE Part 6
WE CAN SAVE ALL OUR WORK BY THE FOLLOWING WAY
SPOOL DIRECTORY_PATH\SHARIQ.TXT;
CLOSE THE SPOOL THIS WAY
SPOOL OFF
WE CAN SAVE LAST QUERY BY THE FOLLOWING WAY
SAVE SHARIQ -- SAVE THIS QUERY IN THE BIN DIRECTORY OF THE ORACLE HOME
TO RETRIEVE THAT QUERY
@SHARIQ
SPOOL DIRECTORY_PATH\SHARIQ.TXT;
CLOSE THE SPOOL THIS WAY
SPOOL OFF
WE CAN SAVE LAST QUERY BY THE FOLLOWING WAY
SAVE SHARIQ -- SAVE THIS QUERY IN THE BIN DIRECTORY OF THE ORACLE HOME
TO RETRIEVE THAT QUERY
@SHARIQ
ORACLE Part 5
Defining Columns Alias
A Column Alias is basically nickname of the column heading, It is useful in Calculations.
You can use Column Alias by following ways.
1. Immediately After the Column Name
2. Also use it in Double quotations
3. Never used keywords in Aliases but we can used in double quotations
SELECT ENAME "SELECT" FROM EMP;
--CONCATENATION
/* CONCATENATES COLUMNS OR CHARACTER STRING TO OTHER COLUMNS, REPRESENTED BY TWO PIPE SIGNS (||) */
SELECT ENAME || 'WORK AS' || JOB || 'SINCE' || HIREDATE || 'IN DEPARTMENT' || DEPTNNO FROM EMP;
FOR AVOIDING DUPLICATION IN RETRIEVING DATA
-- DISTINCT KEYWORD USED
-- DISTINCT KEYWORD ALWAYS APPEAR AFTER THE SELECT STATEMENT
SELECT DISTINCT DEPTNO FROM EMP;
A Column Alias is basically nickname of the column heading, It is useful in Calculations.
You can use Column Alias by following ways.
1. Immediately After the Column Name
2. Also use it in Double quotations
3. Never used keywords in Aliases but we can used in double quotations
SELECT ENAME "SELECT" FROM EMP;
--CONCATENATION
/* CONCATENATES COLUMNS OR CHARACTER STRING TO OTHER COLUMNS, REPRESENTED BY TWO PIPE SIGNS (||) */
SELECT ENAME || 'WORK AS' || JOB || 'SINCE' || HIREDATE || 'IN DEPARTMENT' || DEPTNNO FROM EMP;
FOR AVOIDING DUPLICATION IN RETRIEVING DATA
-- DISTINCT KEYWORD USED
-- DISTINCT KEYWORD ALWAYS APPEAR AFTER THE SELECT STATEMENT
SELECT DISTINCT DEPTNO FROM EMP;
ORACLE Part 4
Many Times, NULL appears in our result.
To Avoid NULL, we have to use the following function
If NVL Function used with Character then NVL (Char_Column,'ABC')
If NVL Function used with Number then NVL (Numb_Column,0)
Select ename, sal, comm, sal+comm from emp;
Select ename, sal, comm, sal+comm, sal+nvl(comm,0) from emp;
Select ename, sal, comm, sal+comm, sal+nvl(comm,1000) from emp;
To Avoid NULL, we have to use the following function
If NVL Function used with Character then NVL (Char_Column,'ABC')
If NVL Function used with Number then NVL (Numb_Column,0)
Select ename, sal, comm, sal+comm from emp;
Select ename, sal, comm, sal+comm, sal+nvl(comm,0) from emp;
Select ename, sal, comm, sal+comm, sal+nvl(comm,1000) from emp;
ORACLE Part 3
GUIDELINES FOR WRITING SQL STATEMENTS
1. SQL STATEMENTS ARE NOT CASE SENSATIVE
2. SQL STATEMENTS CAN BE SPLIT INTO MANY LINES
FOR DOING ARITHMETIC EXPRESSION
SELECT 2+3 FROM DEPT;
SELECT 3+3/3*3-3 FROM DUAL;
SELECT ENAME, JOB, SAL , SAL+1000 FROM EMP;
SELECT SAL, SAL/30 FROM EMP;
DUAL IS THE DUMMY TABLE IN THE DATABASE.
1. SQL STATEMENTS ARE NOT CASE SENSATIVE
2. SQL STATEMENTS CAN BE SPLIT INTO MANY LINES
FOR DOING ARITHMETIC EXPRESSION
SELECT 2+3 FROM DEPT;
SELECT 3+3/3*3-3 FROM DUAL;
SELECT ENAME, JOB, SAL , SAL+1000 FROM EMP;
SELECT SAL, SAL/30 FROM EMP;
DUAL IS THE DUMMY TABLE IN THE DATABASE.
ORACLE Part 2
-------------- SQL STATEMENTS ----------------------
Data Retrieval
Select
Data Manipulation Language (DML)
Insert, Update, Delete, Merge
Data Definition Language (DDL)
Create, Alter, Drop, Truncate, Rename
Data Control Language (DCL)
Grant, Revoke
Transaction Control
Commit, Rollback, Savepoint
Data Retrieval (Select Statement) -- Capabilities
1. Projection : Select All or Particular Columns From the table
2. Selection : Select All or Particular Columns From the table including Where Clause
3. Joining : Retrieve Data from more than One Table
To Retrieve all the Data from the Table
Select * from Emp;
To Retrieve particular Data from the Table
Select empno, ename, sal from Emp;
To Retrieve particular Data from the Table including Where Clause
Select empno, ename, sal from Emp where deptno = 10;
You can Edit your previous queries through Oracle SQL* Plus, there you have to use Notepad Functionality for it, For this you have to type.
ED
Data Retrieval
Select
Data Manipulation Language (DML)
Insert, Update, Delete, Merge
Data Definition Language (DDL)
Create, Alter, Drop, Truncate, Rename
Data Control Language (DCL)
Grant, Revoke
Transaction Control
Commit, Rollback, Savepoint
Data Retrieval (Select Statement) -- Capabilities
1. Projection : Select All or Particular Columns From the table
2. Selection : Select All or Particular Columns From the table including Where Clause
3. Joining : Retrieve Data from more than One Table
To Retrieve all the Data from the Table
Select * from Emp;
To Retrieve particular Data from the Table
Select empno, ename, sal from Emp;
To Retrieve particular Data from the Table including Where Clause
Select empno, ename, sal from Emp where deptno = 10;
You can Edit your previous queries through Oracle SQL* Plus, there you have to use Notepad Functionality for it, For this you have to type.
ED
ORACLE Part 1
SQL STANDS FOR STRUCTURED QUERY LANGUAGE.
There are two ways to start Oracle through RUN.
Run -> sqlplus... You can do all your work through Dos prompt
Run -> sqlplusw... You can do all your work through Oracle Interface
To Describe any Table..
Desc;
To Check the Current Date of the System.
Select sysdate from Dual;
Dual is the temporary table in the Database by which you can do all your rough working.
To check the User from which you are connecting.
Show User;
or
Select user from Dual;
For Single Line Comments, Use -- This is Single Line Comments
For Multi Line Comments, Use /* This is Multi Line Comments */
To Check how many Objects are Lies in the Schema..
Select * from Tab; -- Tables
Select * from Cat; -- Catalogue
Show Structure of the Table
Desc Emp;
Desc Dept;
There are two ways to start Oracle through RUN.
Run -> sqlplus... You can do all your work through Dos prompt
Run -> sqlplusw... You can do all your work through Oracle Interface
To Describe any Table..
Desc
To Check the Current Date of the System.
Select sysdate from Dual;
Dual is the temporary table in the Database by which you can do all your rough working.
To check the User from which you are connecting.
Show User;
or
Select user from Dual;
For Single Line Comments, Use -- This is Single Line Comments
For Multi Line Comments, Use /* This is Multi Line Comments */
To Check how many Objects are Lies in the Schema..
Select * from Tab; -- Tables
Select * from Cat; -- Catalogue
Show Structure of the Table
Desc Emp;
Desc Dept;
Subscribe to:
Posts (Atom)