List Of SQL(Oracle) Syntax

Data Definition Language (DDL)

1. Create Table Syntax

Create table emp(empno number(3), empname varchar2(50));



2. Alter table

Alter table emp add (dob date);

Alter table emp add (sal number(3));

Alter table emp modify (sal number(5));



3. DROP table

Drop table emp;

[Note : If you are running each query as shown skip this query and move on. Execute these query at last of this tutorial.]


Constrains:

1. Primary key

CREATE  TABLE  Persons

(

P_Id int NOT NULL PRIMARY KEY, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255)

)


2. Not Null

CREATE  TABLE  Persons

(

P_Id  int  NOT  NULL,

LastName varchar(255) NOT NULL, FirstName varchar(255),

Address varchar(255), City varchar(255)

)


3. Foreign Key

CREATE  TABLE  Orders

(

O_Id int NOT NULL PRIMARY KEY, OrderNo int NOT NULL,

P_Id  int  FOREIGN  KEY  REFERENCES  Persons(P_Id)

)


4. Unique

CREATE  TABLE  Persons

(

P_Id int NOT NULL UNIQUE, LastName varchar(255) NOT NULL, FirstName varchar(255),

Address varchar(255), City varchar(255)

)


5. Default

CREATE  TABLE  Orders

(

O_Id int NOT NULL, OrderNo int NOT NULL, P_Id int,

OrderDate  date  DEFAULT  GETDATE()

)

Data Manipulation Language (DML)

1. Insert into Syntax

Insert into emp values(3,’A’,’1-jan-90’,5000);

Insert into emp values(4,’share bca’, ’1-jan-90’,3000); Insert into emp values(4,’B’, ’1-jan-90’,2000);

Insert into emp values(3,’C’, ’1-jan-90’,5000); Insert into emp values(2,’D’, ’1-jan-90’,3000);

OR

Insert into emp values(&a,’&b’);

[ NOTE :

1) put ‘’ for string data type and for date data type

2) date is always in format 1-jan-10

3) &a and &b are substitution variables

4) use ed to edit the previous query

5) use \ to run the last successful query

]


2. Select Command

Select * from emp;


3. Update Command

Update emp set dob=’1-jan-10’;


4. Commit

Commit;


5. Delete Command

Delete from emp where empno=2;

OR

Delete from emp;

This will delete all the rows.


Clauses:


1.) Where Clause

Select * from <tablename> where <condition>;

Some Eg :

  • Select * from emp where empno=10;
  • Select * from emp where dob=’1-jan-10’;

2.) Like Clause

Select * from <tablename> like <value>;

Some Eg :

• Select * from emp where empname like ‘share bca’;


3.) Between Clause

Select * from <tablename> between <value> And <value>;

Some Eg :

• Select * from emp where empno between 10 and 20;


4.) Distinct Clause

Select distinct <column name> from <tablename>;

Some Eg :

• Select distinct empno from emp;


5.) Order by Clause

Select * from <tablename> orderby <column name >;

Some Eg :

  • Select * from emp order by empno;
  • Select * from emp order by empno desc;


6.) And

Select * from <tablename> where <condition > and <condition>;

Some Eg :

• Select * from emp where empno=3 and empname=’share bca’;


7.) OR

Select * from <tablename> where <condition > or <condition>;

Some Eg :

• Select * from emp where empno=3 or empname=’xyz’;


8.) In

SELECT Column1, Column2, Column3, … FROM Table1

WHERE Column1 IN (Valu1, Value2, …) ;

Some Eg :

• Select * from emp where empno in (3,5,10);

•

9) Alias

SELECT sum(<column name>) as sum of the column name from <table name>;

Some Eg :

• Select sum(sal) as “sum of the empno” from emp;


10.) Wild Card Characters

% A substitute for zero or more characters

_

[charlist] Any single character in charlist [^charlist] Any single character not in charlist


11.) Group By Clause:

SELECT <aggregate function>(<column name>) from <table name> group by <column name>;

Some Eg :

• Select empno,sum(sal) from emp group by empno;


12.) Having Clause:

SELECT <aggregate function>(<column name>)  from <table name> group

by <column name> having <condition>;

Some Eg :

select empno, sum(sal) from emp group by empno having sum(sal)<7000;


Thanks  To Aadil Keshwani (SHARE BCA) For This Post.. .

Share

About the Author

Akash Padhiyar

Visit Website

There are no comments yet, add one below.

Leave a Comment

Your email address will not be published. Required fields are marked *

*

Time limit is exhausted. Please reload CAPTCHA.