1.

Create table is used to create the table in database(DDL Command)

Create Table <Table_Name>(Column1 Datatype,Column2 Datatype,Column3

Datatype);

Create Table DBMS_EMP_TEST(Empid char(6),Empname Varchar2(50),Salary

number(7,2),Address Varchar2(1000),DOJ date);

Describe DBMS_EMP_TEST;
Desc DBMS_EMP_TEST;
=========================================================================
=======
2.Alter table is used to add or drop or modify columns in existing
table.(DDL Command)

adding the column

ALTER Table <Table_Name> ADD Column1 datatype;

ALTER Table DBMS_EMP_TEST ADD Department Varchar2(20);

=========================================================================
=======
Modifying the column

ALTER Table <Table_Name> Modify Column1 new_datatype;

ALTER Table DBMS_EMP_TEST Modify Department Varchar2(25);

=========================================================================
=======
Dropping the column

ALTER Table <Table_Name> DROP Column1;

ALTER Table DBMS_EMP_TEST DROP (Department);

=========================================================================
=======

3.Truncate table is used to delete whole rows of the table.The structure

remains as it is.You can add new data to the table again.(DDL Command)

Truncate Table <Table_Name>

Truncate Table DEPT0;
=========================================================================
=======

4.DROP table is used to delete the table with rows.The structure does not
remains.You need to create the table again.(DDL Command)
DROP Table <Table_Name>
DROP Table DEPT0;
=========================================================================
=======
5.Rename table is used to rename the old table name to new table
name.(DDL Command)

Rename old_table_name to new_table_name;

Rename DBMS_EMP_TEST to DBMS_EMP_TEST1;
=========================================================================
=======

6.Insert the new rows in table:(DML Command)

Insert into DBMS_EMP_TEST values('101','Ramesh',35000.80,'Pune','10-OCT-
1995');
Insert into DBMS_EMP_TEST values('102','Sukesh',45000.80,'Pune','10-Aug-
1995');
Insert into DBMS_EMP_TEST values('103','Rajesh',35000.80,'Pune','16-Nov-
1995');
Insert into DBMS_EMP_TEST values('104','Nimesh',65000.80,'Pune','19-OCT-
1994');
Insert into DBMS_EMP_TEST values('105','Rakesh',30000.80,'Pune','23-JAN-
1996');
Insert into DBMS_EMP_TEST values('106','Rohit',25000.80,'Pune','10-DEC-
1997');
commit;
=========================================================================
=======
7.Update or modify the old rows in table:(DML Command)

Update <Table_Name> SET Column_name =new_value

Where Column_name=Some_value;

Update DBMS_EMP_TEST SET Empname ='Amit'

Where Empid=102;

=========================================================================
=======
8.delete the rows in table:(DML Command)

DELETE FROM Table_Name where Column_name= Some_value;

DELETE FROM DBMS_EMP_TEST where Empid='106';

=========================================================================
=======
9.Different Options of data retrieve statements:(DQL Command)

SELECT * FROM DBMS_EMP_TEST;

SELECT Empid,Empname,Address FROM DBMS_EMP_TEST;

SELECT DISTINCT Address FROM DBMS_EMP_TEST;

=========================================================================
=======
10.Commit(DCL Command)
It is transanctional command used to save changes invoked by transanction
of database

Insert into DBMS_EMP_TEST values('106','Rohit',25000.80,'Pune','10-DEC-

1997');
commit;

=========================================================================
=========
11.Entity Integrity Constraints:

1)Primary Key
Create Table DBMS_EMP_TEST9 (Empid char(6) CONSTRAINT empid_pk PRIMARY
KEY,Empname Varchar2(50),Salary number(7,2),Address Varchar2(1000),DOJ
date);
ALTER Table DBMS_EMP_TEST10 add CONSTRAINT empid_pk101 PRIMARY
KEY(Empid);

2)Foreign KEY
Create Table DBMS_Product(Empid char(6) references
DBMS_EMP_TEST9(Empid),ProdcutName Varchar2(25));

=========================================================================
=========
12.Domain Integrity Constraints:

1) Null and Not Null CONSTRAINT

Create table DBMS_EMP_TEST11(Empname Varchar2(15) NOT NULL,phone_number

number(10));

2) Check constraint
Create table EMP909(Empid number(6),Empname varchar2(25) NOT NULL,salary
number(11,2) constraint chk_emp check(salary is NOT NULL and
salary>20000),phoneno number(10));
=========================================================================
=========
13.Rollback:It used to undo transanction that have not already been saved
to the database and undo transanction since last commit and rollback.
ROLLBACK;

delete from DBMS_EMP_TEST10 where Empid='105';

ROLLBACK;
=========================================================================
=========
14:SAVEPOINT is a point in transaction when you undo the transaction back
to certain point without rolling back the entire transaction.
SAVEPOINT SAVEPOINT_NAME;

Rollback To SAVEPOINT_NAME;

e.g SAVEPOINT S1;

delete from DBMS_EMP_TEST10 where Empid='103';
SAVEPOINT S2;
delete from DBMS_EMP_TEST10 where Empid='104';
SAVEPOINT S3;
delete from DBMS_EMP_TEST10 where Empid='105';
ROLLBACK To S1;
=========================================================================
=========
15:GRANT: The objects created by one user are not accessible by another
user unless the owner of those objects gives such permissions to other
users.object such as tables,views,sequences.

GRANT {privileges}
ON object_name
To username
with grant option.

1)Grant all privileges on DBMS_EMP_TEST10 table user Kundan

GRANT ALL
ON DBMS_EMP_TEST10
To Kundan;

2)Grant select and update privileges on DBMS_EMP_TEST10 table user Kundan

GRANT SELECT,UPDATE
ON DBMS_EMP_TEST10
To Kundan;

3)Grant all privileges on DBMS_EMP_TEST10 table user Kundan with grant

option(It Allows the grantee to grant object privileges to other users)
GRANT ALL
ON DBMS_EMP_TEST10
To Kundan
WITH GRANT OPTION;
=========================================================================
=========
16.Revoke:remove grant permission of object
REVOKE {privileges}
ON object_name
FROM Username;

1)REVOKE delete privileges on DBMS_EMP_TEST10 table from kundan

REVOKE DELETE
on DBMS_EMP_TEST10
from kundan;

2)Revoke all permission

REVOKE ALL
on DBMS_EMP_TEST10
from kundan;
=========================================================================
=========
Arithmatic operators:
1)Addition:display the salary of employee by adding 6000 to it.
Select Empid,salary + 6000 from DBMS_EMP_TEST10;

2)Substraction:display the salary of employee by substarcting 6000 to it.

Select Empid,salary - 6000 from DBMS_EMP_TEST10;

3)Multiplication:display the salary of employee by giving 5% raise in

salary
Select Empid,salary + 0.05*Salary from DBMS_EMP_TEST10;

4)Divide:Display salary by dividing the salary by 2

Select Empid,salary/2 from DBMS_EMP_TEST10;
=========================================================================
=========
Comparison Operator

1)Equal operator (=):check two operand are equal or not if equal

SELECT * FROM DBMS_EMP_TEST where salary='35000.8';

SELECT * FROM DBMS_EMP_TEST where EMPNAME = 'Nimesh'

2)Not Equal operator (!=) :check two operand are equal or not if equal

SELECT * FROM DBMS_EMP_TEST where SALARY != '35000.8'

SELECT * FROM DBMS_EMP_TEST where EMPNAME != 'Nimesh'

3)Less-than operator(<):check if left operand is less than right operand

SELECT * FROM DBMS_EMP_TEST where salary < '35000'

4)Greater-than operator(>):check if left operand is greater than right
operand

SELECT * FROM DBMS_EMP_TEST where salary > '30000'

5)Greater-than equal operator(>=):check if left operand is greater than

or equal to right operand

SELECT * FROM DBMS_EMP_TEST where salary >= '35000.8'

6)Less-than equal operator(<=):check if left operand is less than or

equal to right operand

SELECT * FROM DBMS_EMP_TEST where salary <= '35000.8'

7)Not equal to <>:Checks if the values of two operands are equal or not,
if values are not equal then condition becomes true.
not equal

SELECT * FROM DBMS_EMP_TEST where salary <> '35000.8'

=========================================================================
=========

Logical Operator

AND Operator

SELECT * FROM DBMS_EMP_TEST where EMPNAME = 'Nimesh' AND ADDRESS = 'Pune'

OR Operator

SELECT * FROM DBMS_EMP_TEST where EMPNAME = 'Nimesh' OR ADDRESS =

'Nagpur'

=========================================================================
=========

Range Searching Operator

1)IN Operator:If you know the exact value you want to return for at least
one of the columns.

SELECT * FROM DBMS_EMP_TEST where EMPNAME IN ('Nimesh','Rakesh');

2)NOT IN Operator: not present

SELECT * FROM DBMS_EMP_TEST where EMPNAME NOT IN ('Nimesh','Rakesh');

3)Between and not between

Between:search for values that are within set of values given min and max
value

SELECT * FROM DBMS_EMP_TEST where salary BETWEEN '5000.8' AND '40000.8';

NOT Between:search for values that are not within set of values given min
and max value
SELECT * FROM DBMS_EMP_TEST where salary NOT BETWEEN '5000.8' AND
'40000.8';

=========================================================================
=========

Pattern Matching

Like is used to specify a search for pattern in column.

%:matches any string

_:matches any character

SELECT * FROM DBMS_EMP_TEST where EMPNAME like '%sh' end with h

SELECT * FROM DBMS_EMP_TEST where EMPNAME like 'R%' Start with R

SELECT * FROM DBMS_EMP_TEST where EMPNAME like 'R%h' Start with R and
end with h

SELECT * FROM DBMS_EMP_TEST where EMPNAME like '___e%' _ _ _ e% fourth

letter e

Is NULL: check if data exist in row or not.It will dispaly empty row

SELECT * FROM DBMS_EMP_TEST where Address IS NULL;

Is NOT NULL: check if data exist in row or not.It will not dispaly empty
row

SELECT * FROM DBMS_EMP_TEST where Address IS NOT NULL;

=========================================================================
=========

Alias(nickname)

SELECT EMPID,EMPNAME AS "NAME" FROM DBMS_EMP_TEST

SELECT empid,empname,salary*12 AS annual_salary FROM DBMS_EMP_TEST

SELECT * FROM DBMS_EMP_TEST AS "DBMS"

=========================================================================
=========
Set Operator

Union Operator:All distinct rows selected by either query.It is used to

select related information from two tables that is like JOIN commond.Only
DISTINCT value selected

SELECT EMPNAME,SALARY FROM DBMS_EMP_TEST

UNION
SELECT EMPNAME,SALARY FROM DBMS_EMP_TEST1

Union all operator:All rows selected by either query, including all

duplicates.It is used to select related information from two tables that
is like JOIN commond.All repeated value selected

SELECT EMPNAME,SALARY FROM DBMS_EMP_TEST

UNION ALL
SELECT EMPNAME,SALARY FROM DBMS_EMP_TEST1

Intersect Operator:All distinct rows selected by both queries.It used to

select related information from two tables.All selected columns need to
be of the same datatype.
only distinct values selected.

SELECT EMPID FROM DBMS_EMP_TEST

INTERSECT
SELECT EMPID FROM DBMS_EMP_TEST1

MINUS:All distinct rows selected by the first query but not the second

SELECT EMPID FROM DBMS_EMP_TEST

MINUS
SELECT EMPID FROM DBMS_EMP_TEST1

=========================================================================
=========