ORACLE PRIVILEGES

PRIVILEGE
"PRIVILEGE" is a type of authority to execute particular SQL statement or right access another user's objects.

Oracle define two types of privileges

• System Privilege and
• Object Privilege

System Privilege
Each user needs some system privilege to perform particular database operations.
You can see all system privileges by query

select privilege, name from system_privilege_map


Some Commonly used System privileges are:-

System Privilege	Capability
ALTER DATABASE	Make changes to the database
CREATE ANY INDEX	Create an index in any schema
CREATE PROCEDURE	Create a function, procedure, or package in your own schema.
CREATE SESSION	Connect to the database.
CREATE SYNONYM	Create a private synonym in your own schema.
CREATE PUBLIC SYNONYM	Create a public synonym.
CREATE VIEW	Create a view in your Schema
CREATE TABLE	Create a table in your own schema.
CREATE TABLESPACE	Create a new tablespace in the database.
CREATE USER	Create a user account/schema.
ALTER USER	Make changes to a user account/schema.

By Query Examples:-

grant create session, create table, unlimited tablespace to shariq


Here "shariq" is a Table, by above privileges Table "shariq" can connect to Database, creates table with unlimited table space.


However, you can also see how many privileges you have assigned from dba by query.
select * from session_privs;

Object Privilege

Object Privilege allow users to perform certain action on Database objects such as executing DML statements on tables.

Some Commonly used System privileges are:-

Object Privilege Allows a User to SELECT Perform a select INSERT Perform an insert UPDATE Perform an update DELETE Perform a delete EXECUTE Execute a stored procedure

By Query Examples:-

grant select, insert, update on emp to sha

By above query, user "sha" can access "scott's" schema for Table emp selection, insertion as well as updation.

Connect to "sha" user

conn sha/sha

For Selection

select * from scott.emp;

For Updation 

 update scott.emp set ename = 'Sharique' where empno = 7369;

Oracle Data Loader

Oracle Data Loader takes Data from any external file. Here I'll perform Oracle Data Loader from an excel file (CSV Format).
First make an excel file. Check the Image. Kindly save it in CSV format. I have saved the Excel File as 'Emp_Info.csv' as destination 'E:\Ex_Dir\Emp_Info.csv'

After making Excel Sheet, Connect to SysDba to make directory and also to give the privileges to any common user for read and write the directory. Mostly Scott and other Users unable to make directory and read, write it because of less priveleges.

Conn sys/oracle as Sysdba;

Create Directory EXDIR as 'E:\Ex_Dir';

You can give any name as Directory name but make sure the destination would be the same where you have saved the excel sheet like (as 'E:\Ex_Dir')

After that give the rights to any user you want.

Grant All on Directory EXDIR to Scott;

or 

GRANT READ, WRITE ON DIRECTORY EXDIR TO SCOTT;

Now Connect to Scott.

& Write the following Code.

CREATE TABLE EX_ABC

(

ID NUMBER,

FNAME VARCHAR2(20),

JOB VARCHAR2(20)

)

ORGANIZATION EXTERNAL

(

TYPE ORACLE_LOADER DEFAULT DIRECTORY EXDIR

ACCESS PARAMETERS

(

FIELDS TERMINATED BY ','

)

LOCATION ('EMP_INFO.csv'))

The Attributes should be in the same manner/format datatype like your excel sheet. 

Here (FIELDS TERMINATED BY ',')  indicates that you have made comma separated file,

LOCATION ('EMP_INFO.csv')) indicates your excel file name.

 Thank you. . .  !!

Have a Nice Day...!!!! 

SQL Server- Encryption/Decryption (functions, procedures, triggers and views)

I have made some functions, procedures, triggers and views in SQL Server 2008 its level was encrypted by using “WITH ENCRYPTION”. I was looking to secure my functions, procedures, triggers and views secure so that no one would be able to use it.

After having some sort of working, I came to know that if the procedure or user defined function created “WITH ENCRYPTION”, there is no possible ways to decrypt it by SQL Server commands.

Well, if anyone wants to decrypt it then you will have to use the freeware “Optillect SQL Decryptor”. It will decrypt your functions, procedures, triggers and views.

Just download “Optillect SQL Decryptor” , connect it by using your desired connection and decrypt it whatever you want.

Have a nice Day..!

Some Useful Data Dictionary VIEWS

Table spaces


dba_tablespacesuser_tablespaces 

Tablespace Quotas

dba_ts_quotas
user_ts_quotas
Data Files

dba_data_files
v$backup_datafile
v$datafile
v$datafile_copy
v$datafile_header
Free Space

dba_free_space
Segments

dba_segments
v$segment_statistics
Extents

dba_extents
Blocks

v$database_block_corruption

Groups

dba_tablespace_groups
SYSAUX Tablespace

v_$sysaux_occupants
Temp Tablespace

dba_temp_files
Undo Tablespace

dba_rollback_segs
dba_undo_extents
v$rollstat
v$undostat
Transportable Tablespaces

transport_set_violations

Connect Oracle10g Database with DotNet Framework using C#

Namespaces


using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;

Button Click Event to Display Data on DataGridView


private void button1_Click(object sender, EventArgs e)
{
string connectionString = "provider=MSDAORA;data source=ORCL;user id=SCOTT;password=TIGER";
OleDbConnection myOleDbConnection = new OleDbConnection(connectionString);
OleDbDataAdapter da = new OleDbDataAdapter("select * from emp",myOleDbConnection );
DataSet ds = new DataSet();
da.Fill(ds, "emp");
dataGridView1.DataSource = ds.Tables["EMP"].DefaultView;
}

DROP TABLE - PURGE

Oracle Database 10g gives you the extra feature to drop your table and put them in the Recycle bin, This feature was introduced in 10g and it's not in earlier versions. Once you drop the table you can use Flashback query to retrieve that table from the Recycle bin.
If you want to remove the table permanently from the database also from Recycle bin then you have to use PURGE (just like shift+delete key in Windows).
And you cannot roll back Drop Table and Purge Clause.

Syntax given below.

Drop Table Table_name Purge;
It'll permanently remove and clears your storage space.

Drop Table Table_name
It will drop the table and send that table into the Recycle Bin.

Flashback Table Table_name to Before drop;
It will retrieve the Table from the Recycle Bin.

And also you can use this query to retrieve from Recycle Bin.

Select original_name, operation, droptime, from recyclebin;
Where Original_name is the table_name
Operation is drop that you did perform
Droptime, At what time you dropped the table.

To Clear the RecycleBin,
PURGE RECYCLEBIN;

Syed Sharique Imam: Different Types of Joins-

Syed Sharique Imam: Different Types of Joins-: "Joins A range of new join syntax are available that comply with the ANSI/ISO SQL: 1999 standards. CROSS JOIN The CROSS JOIN produces a ca..."

Different Types of Joins-

Joins

A range of new join syntax are available that comply with the ANSI/ISO SQL: 1999 standards.

CROSS JOIN

The CROSS JOIN produces a cartesian product.

ANSI/ISO Syntax

select e.ename, d.dname
from emp e cross join dept d;

Existing Syntax
select e.ename, d.dname
from emp e , dept d ;

NATURAL JOIN

The NATURAL JOIN performs a join for all columns with matching names in the two tables.

ANSI/ISO Syntax

select e.ename, d.dname
from emp e natural join dept d;

Existing Syntax

select e.ename, d.dname
from emp e , dept d
where e.deptno = d.deptno;

JOIN ... USING

The USING clause is used if several columns share the same name, but you do not wish to join using all of these common columns. The columns listed in the USING clause cannot have any qualifiers in the statement, including the WHERE clause.


ANSI/ISO Syntax

select e.ename, e.job, d.dname
from emp e join dept d
using (deptno)

Existing Syntax

select e.ename, e.job, d.dname
from emp e , dept d
where e.deptno = d.deptno

Multiple Joins

Multiple Joins are those where more than two tables are joined

ANSI/ISO Syntax

select first_name, last_name, department_name, city
from employees e join departments d on (e.department_id = d.department_id)
join locations l on (d.location_id = l.location_id)

Existing Syntax

select first_name, last_name, department_name, city
from employees e , departments d , locations l
where e.department_id = d.department_id
and d.location_id = l.location_id

OUTER JOIN

There are three variations on the outer join. The LEFT OUTER JOIN returns all the rows from the table on the left side of the join, along with the values from the right hand side, or NULLs if a matching row doesn't exist. The RIGHT OUTER JOIN does the reverse of this. Finally, the FULL OUTER JOIN returns all rows from both tables, filling in any blanks with nulls.


ANSI/ISO Syntax

select l.city, d.department_name
from locations l
left outer join
departments d
on (l.location_id = d.location_id)


Existing Syntax

select l.city, d.department_name
from locations l , departments d
where l.location_id = d.location_id(+)


ANSI/ISO Syntax

select l.city, d.department_name
from locations l
right outer join
departments d
on (l.location_id = d.location_id)


Existing Syntax

select l.city, d.department_name
from locations l , departments d
where l.location_id(+) = d.location_id


ANSI/ISO Syntax

select l.city, d.department_name
from locations l
full outer join
departments d
on (l.location_id = d.location_id)


Existing Syntax

No Equivalent!

Oracle - Date Time Format

SQL> SELECT TO_CHAR (SYSDATE, 'DD-MON-RRRR HH24:MI:SS') FROM DUAL;

TO_CHAR(SYSDATE,'DD-
--------------------
01-FEB-2011 15:24:12

SQL> ED
Wrote file afiedt.buf

1* SELECT SYSDATE, 'DD-MON-RRRR HH24:MI:SS' FROM DUAL
SQL> /

SYSDATE 'DD-MON-RRRRHH24:MI:SS
--------- ----------------------
01-FEB-11 DD-MON-RRRR HH24:MI:SS


1* SELECT TO_CHAR(SYSDATE,'DD-MON-RR') FROM DUAL
SQL> /

TO_CHAR(S
---------
01-FEB-11

SQL> ED
Wrote file afiedt.buf

1* SELECT TO_CHAR(SYSDATE,'DD-MON-RR') "SYSTEM DATE" FROM DUAL
SQL> /

SYSTEM DA
---------
01-FEB-11

SQL> ED
Wrote file afiedt.buf

1* SELECT TO_CHAR(SYSDATE,'DD-MON-RR') "SYS DATE" FROM DUAL
SQL> /

SYS DATE
---------
01-FEB-11

1* SELECT SYSDATE, HIREDATE, MONTHS_BETWEEN(SYSDATE, HIREDATE) "MONTH-B/W" FROM EMP
SQL> /

SYSDATE HIREDATE MONTH-B/W
--------- --------- ----------
01-FEB-11 17-DEC-80 361.504772
01-FEB-11 20-FEB-81 359.407998
01-FEB-11 22-FEB-81 359.343482
01-FEB-11 02-APR-81 357.988643
01-FEB-11 28-SEP-81 352.149934
01-FEB-11 01-MAY-81 357
01-FEB-11 09-JUN-81 355.762837
01-FEB-11 19-APR-87 285.440256
01-FEB-11 17-NOV-81 350.504772
01-FEB-11 08-SEP-81 352.795095
01-FEB-11 23-MAY-87 284.311224
01-FEB-11 03-DEC-81 349.956385
01-FEB-11 03-DEC-81 349.956385
01-FEB-11 23-JAN-82 348.311224

14 rows selected.

SQL> ED
Wrote file afiedt.buf

1* SELECT SYSDATE, HIREDATE, ROUND(MONTHS_BETWEEN(SYSDATE, HIREDATE),0) "MONTH-B/W" FROM EMP
SQL> /

SYSDATE HIREDATE MONTH-B/W
--------- --------- ----------
01-FEB-11 17-DEC-80 362
01-FEB-11 20-FEB-81 359
01-FEB-11 22-FEB-81 359
01-FEB-11 02-APR-81 358
01-FEB-11 28-SEP-81 352
01-FEB-11 01-MAY-81 357
01-FEB-11 09-JUN-81 356
01-FEB-11 19-APR-87 285
01-FEB-11 17-NOV-81 351
01-FEB-11 08-SEP-81 353
01-FEB-11 23-MAY-87 284
01-FEB-11 03-DEC-81 350
01-FEB-11 03-DEC-81 350
01-FEB-11 23-JAN-82 348

14 rows selected.

SQL> ED
Wrote file afiedt.buf

1 SELECT SYSDATE, HIREDATE, ROUND(MONTHS_BETWEEN(SYSDATE, HIREDATE),0) "MONTH-B/W",
2* (SYSDATE-HIREDATE)/30 "MONTHSSS" FROM EMP
SQL> /

SYSDATE HIREDATE MONTH-B/W MONTHSSS
--------- --------- ---------- ----------
01-FEB-11 17-DEC-80 362 366.788296
01-FEB-11 20-FEB-81 359 364.621629
01-FEB-11 22-FEB-81 359 364.554963
01-FEB-11 02-APR-81 358 363.254963
01-FEB-11 28-SEP-81 352 357.288296
01-FEB-11 01-MAY-81 357 362.288296
01-FEB-11 09-JUN-81 356 360.988296
01-FEB-11 19-APR-87 285 289.654963
01-FEB-11 17-NOV-81 351 355.621629
01-FEB-11 08-SEP-81 353 357.954963
01-FEB-11 23-MAY-87 284 288.521629
01-FEB-11 03-DEC-81 350 355.088296
01-FEB-11 03-DEC-81 350 355.088296
01-FEB-11 23-JAN-82 348 353.388296

14 rows selected.

SQL> ED
Wrote file afiedt.buf

1 SELECT SYSDATE, HIREDATE, ROUND(MONTHS_BETWEEN(SYSDATE, HIREDATE),0) "MONTH-B/W",
2* ROUND((SYSDATE-HIREDATE)/30,0) "MONTHSSS" FROM EMP
SQL> /

SYSDATE HIREDATE MONTH-B/W MONTHSSS
--------- --------- ---------- ----------
01-FEB-11 17-DEC-80 362 367
01-FEB-11 20-FEB-81 359 365
01-FEB-11 22-FEB-81 359 365
01-FEB-11 02-APR-81 358 363
01-FEB-11 28-SEP-81 352 357
01-FEB-11 01-MAY-81 357 362
01-FEB-11 09-JUN-81 356 361
01-FEB-11 19-APR-87 285 290
01-FEB-11 17-NOV-81 351 356
01-FEB-11 08-SEP-81 353 358
01-FEB-11 23-MAY-87 284 289
01-FEB-11 03-DEC-81 350 355
01-FEB-11 03-DEC-81 350 355
01-FEB-11 23-JAN-82 348 353

14 rows selected.

SQL> ED
Wrote file afiedt.buf

1 SELECT SYSDATE, HIREDATE, ROUND(MONTHS_BETWEEN(SYSDATE, HIREDATE),0) "MONTH-B/W",
2* ROUND((SYSDATE-HIREDATE)/30,0) "MONTHSSS" FROM EMP
SQL> /

SYSDATE HIREDATE MONTH-B/W MONTHSSS
--------- --------- ---------- ----------
01-FEB-11 17-DEC-80 362 367
01-FEB-11 20-FEB-81 359 365
01-FEB-11 22-FEB-81 359 365
01-FEB-11 02-APR-81 358 363
01-FEB-11 28-SEP-81 352 357
01-FEB-11 01-MAY-81 357 362
01-FEB-11 09-JUN-81 356 361
01-FEB-11 19-APR-87 285 290
01-FEB-11 17-NOV-81 351 356
01-FEB-11 08-SEP-81 353 358
01-FEB-11 23-MAY-87 284 289
01-FEB-11 03-DEC-81 350 355
01-FEB-11 03-DEC-81 350 355
01-FEB-11 23-JAN-82 348 353

1* SELECT SYSDATE, NEXT_DAY(SYSDATE,'WEDNESDAY') FROM DUAL
SQL> /

SYSDATE NEXT_DAY(
--------- ---------
01-FEB-11 02-FEB-11

1* SELECT SYSDATE, LAST_DAY(SYSDATE) FROM DUAL
SQL> /

SYSDATE LAST_DAY(
--------- ---------
01-FEB-11 28-FEB-11

SQL> -- LAST_DAY,, RETURNS LASTDAY OF THE MONTH ----
SQL> SELECT SYSDATE, ADD_MONTHS(SYSDATE, 2) FROM DUAL;

SYSDATE ADD_MONTH
--------- ---------
01-FEB-11 01-APR-11

1* SELECT SYSDATE, ADD_MONTHS(SYSDATE, -2) FROM DUAL
SQL> /

SYSDATE ADD_MONTH
--------- ---------
01-FEB-11 01-DEC-10

SQL> SELECT '12' * 5, '01-FEB-11' FROM DUAL;

'12'*5 '01-FEB-1
---------- ---------
60 01-FEB-11

SQL> ED
Wrote file afiedt.buf

1* SELECT '12' * 5, '01-FEB-11', '3*5' FROM DUAL
SQL> /

'12'*5 '01-FEB-1 '3*
---------- --------- ---
60 01-FEB-11 3*5

SQL> ED
Wrote file afiedt.buf

1* SELECT '12' * 5, '01-FEB-11', 3*'5' FROM DUAL
SQL> /

'12'*5 '01-FEB-1 3*'5'
---------- --------- ----------
60 01-FEB-11 15

SQL> -- CONVERSION FUNCTION,,, 'IGNORES SINGLE CODE'-----
SQL> -- ALSO CALLED IMPLICIT CONVERSION --

1* select SYSDATE, TO_CHAR(SYSDATE, 'D, DD, DDD, DAY, DY, W, WWW') "FORMATS" FROM DUAL
SQL> /

SYSDATE FORMATS
--------- ----------------------------------
01-FEB-11 3, 01, 032, TUESDAY , TUE, 1, 051

SQL> ED
Wrote file afiedt.buf

1* select SYSDATE, TO_CHAR(SYSDATE, 'D, DD, DDD, DAY, DY, W, WWW') "FORMATS" FROM DUAL
SQL> /

SYSDATE FORMATS
--------- ----------------------------------
01-FEB-11 3, 01, 032, TUESDAY , TUE, 1, 051

SQL> ED
Wrote file afiedt.buf

1* select SYSDATE, TO_CHAR(SYSDATE, 'D, DD, DDD, DAY, DY, W, WWW, MM, MON, MONTH, RM') "FORMATS" FROM DUAL
SQL> /

SYSDATE FORMATS
--------- ------------------------------------------------------------
01-FEB-11 3, 01, 032, TUESDAY , TUE, 1, 051, 02, FEB, FEBRUARY , II

SQL> ed
Wrote file afiedt.buf

1* select SYSDATE, TO_CHAR(SYSDATE, 'DD-MM-RRRR SCC,CC') "FORMATS" FROM DUAL
SQL> /

SYSDATE FORMATS
--------- -----------------
01-FEB-11 01-02-2011 21,21

SQL> ED
Wrote file afiedt.buf

1* select SYSDATE, TO_CHAR(SYSDATE, 'DD-MM-RRRR SCC AD,CC B.C.') "FORMATS" FROM DUAL
SQL> /

SYSDATE FORMATS
--------- -------------------------
01-FEB-11 01-02-2011 21 AD,21 A.D.

SQL> -- TIME FORMAT---

Oracle - Number Date Functions

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

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

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;

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;

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.

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

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;