10/06/2025
[Link] database
version:
----------
oracle 1
oracle 2
oracle 3
.
.
.
oracle 7
oracle 8i
oracle 9i
oracle 10g
oracle 11g
oracle 12c
oracle 18c
oracle 19c
oracle 21c
[Link] tool(graphical user interface)
sql developer
plsql developer
toad
sqlplus
oracle 11g<--communicate-->sql developer
orale language:
---------------------
sql(structured query language):
sql types:
-------------
DDL(DATA DEFINITION LANGUAGE)
CREATE
ALTER
RENAME
DROP
TRUNCATE
DML(DATA MANIPULATION LANGUAGE)
INSERT
UPDATE
DELETE
MERGE
DCL(DATA CONTROL LANGUAGE)
GRANT
REVOKE
TCL(TRANSACTIONAL CONTROL LANGUAGE)
COMMIT
ROLLBACK
SAVEPOINT
DQL(DATA QUERY LANGUAGE)
SELECT
* ---> ALL COLUMNS
CLAUSE:
--------
SELECT COLUMNNAMES
FROM TABLENAME
WHERE CONDITIONS;
SELECT * FROM EMPLOYEES;
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY FROM EMPLOYEES;
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY FROM EMPLOYEES WHERE
EMPLOYEE_ID=100;
--DATA DICTIONARY TABLE
SELECT * FROM USER_TABLES WHERE TABLE_NAME='EMPLOYEES';
OPERATORS:
-----------------
ARITHMETIC OPERATOR:
+
-
*
/
SELECT SALARY,SALARY+1000 FROM EMPLOYEES;
SELECT SALARY,SALARY-1000 FROM EMPLOYEES;
SELECT SALARY,SALARY*2 FROM EMPLOYEES;
SELECT SALARY,SALARY/2 FROM EMPLOYEES;
ALIAS NAME
----------------
SELECT SALARY,SALARY+1000 ADDITION FROM EMPLOYEES;
SELECT SALARY,SALARY-1000 AS SUB FROM EMPLOYEES;
SELECT EMPLOYEE_ID EMPID,FIRST_NAME FNAME,SALARY SAL FROM EMPLOYEES;
DESC EMPLOYEES;
LOGICAL OPERATOR:
AND
OR
RELATIONAL OPERATOR
=
<>
!=
<
<=
>
>=
IN
NOT IN
LIKE
NOT LIKE
BETWEEN
NOT BETWEEN
IS NULL
IS NOT NULL
plsql(procedural language)
11/06/25
OPERATORS:
------------
ARITHMETIC OPERATOR
+
-
*
/
LOGICAL OPERATOR:
------------------------
AND --> IT SHOULD SATISFY BOTH THE CONDITION.
OR --->IT SHOULD SATISFY EITHER 1ST OR 2ND CONDITION.
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY FROM EMPLOYEES
WHERE FIRST_NAME='Steven' AND SALARY=26670;
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY FROM EMPLOYEES
WHERE FIRST_NAME='Steven' OR SALARY=10000;
RELATIONAL OPERATOR
----------------------------
=
<>
!=
<
<=
>
>=
IN
NOT IN
BETWEEN
NOT BETWEEN
LIKE
NOT LIKE
is null
is not null
SELECT FIRST_NAME,SALARY FROM EMPLOYEES WHERE SALARY=10000;
SELECT FIRST_NAME,SALARY FROM EMPLOYEES WHERE SALARY<>10000;
SELECT FIRST_NAME,SALARY FROM EMPLOYEES WHERE SALARY!=10000;
SELECT FIRST_NAME,SALARY FROM EMPLOYEES WHERE SALARY>10000;
SELECT FIRST_NAME,SALARY FROM EMPLOYEES WHERE SALARY>=10000;
SELECT FIRST_NAME,SALARY FROM EMPLOYEES WHERE SALARY<10000;
SELECT FIRST_NAME,SALARY FROM EMPLOYEES WHERE SALARY<=10000;
SELECT FIRST_NAME,SALARY FROM EMPLOYEES WHERE SALARY
IN(10000,7500,7600);
SELECT FIRST_NAME,SALARY FROM EMPLOYEES WHERE SALARY NOT IN
(10000,7500,7600);
SELECT FIRST_NAME,SALARY FROM EMPLOYEES WHERE SALARY BETWEEN 10000
AND 15000;
SELECT FIRST_NAME,SALARY FROM EMPLOYEES WHERE SALARY NOT BETWEEN
10000 AND 15000;
SELECT FIRST_NAME FROM EMPLOYEES WHERE FIRST_NAME LIKE 'A%';
SELECT FIRST_NAME FROM EMPLOYEES WHERE FIRST_NAME NOT LIKE 'A%';
SELECT FIRST_NAME FROM EMPLOYEES WHERE FIRST_NAME LIKE '%a';
SELECT FIRST_NAME FROM EMPLOYEES WHERE FIRST_NAME NOT LIKE '%a';
SELECT FIRST_NAME FROM EMPLOYEES WHERE FIRST_NAME LIKE '%a%';
SELECT FIRST_NAME FROM EMPLOYEES WHERE FIRST_NAME not LIKE '%a%';
SELECT FIRST_NAME FROM EMPLOYEES WHERE FIRST_NAME LIKE '_a%';
SELECT FIRST_NAME FROM EMPLOYEES WHERE FIRST_NAME LIKE '__a%';
SELECT FIRST_NAME,COMMISSION_PCT FROM EMPLOYEES
WHERE COMMISSION_PCT is null;
SELECT FIRST_NAME,COMMISSION_PCT FROM EMPLOYEES
WHERE COMMISSION_PCT is not null;
NULL:
-----------
IT IS AN UNASSIGNED VALUE.
NULL IS NOT EQUAL TO ZERO
NULL IS NOT EQUAL TO EMPTY SPACE
NULL IS NOT EQUAL TO NULL.
concatenation operator:
---------------------------
||
select first_name,salary, first_name||salary||hire_date from employees;
select first_name,salary, first_name||' '||salary as full_name from employees;
datatypes:
----------
NUMBER(): 38 BYTES
WE CAN STORE NUMBER TYPE VALUES INTO A COLUMN.
WE CAN STORE UPTO 38 BYTES.
CHAR():2000 BYTES
IT ALLOWS TO STORE THE ALPHANUMERIC VALUES INTO A COLUMN
WE CAN STORE UPTO 2000 BYTES.
VARCHAR():2000 BYTES
IT ALLOWS TO STORE THE ALPHANUMERIC VALUES INTO A COLUMN
WE CAN STORE UPTO 2000 BYTES.
VARCHAR2():
IT ALLOWS TO STORE THE ALPHANUMERIC VALUES INTO A COLUMN
WE CAN STORE UPTO 4000 BYTES.
DATE():
IT ALLOWS STORE THE DATE VALUES INTO A COLUMN.
IT STORES THE DATE AND TIME DEFAULTLY.
TIMESTAMP()
IT STORES THE DATE,TIME,MILLISECONDS INTO A COLUMN.
LONG():2GB
IT ALLOWS TO STORE THE ALPHANUMERIC VALUES INTO A COLUMN
2GB
IN A TABLE WE CAN USE ONLY ONE LONG DATATYPE
CLOB():4GB
CHARACTER LARGE OBJECTS.
BLOB():4GB
BINARY LARGE OBJECTS.
AUDIO FILES,VIDEO FILES,PDF FILES,EXCEL FILES.
XMLTYPE():
WE CAN STORE THE XML TYPE OF DATA.
DDL(DATA DEFINITION LANGUAGE):
-------------------------------
IT IS AUTOCOMMIT.
CREATE:
------------
WE CAN CREATE A NEW OBJECT INTO A DATABASE.
CREATE TABLE CUSTOMERS
(
CUSTOMERID NUMBER(3),
CUSTOMER_NAME VARCHAR2(10),
PHNO NUMBER,
DOB DATE
);
SELECT * FROM USER_TABLES WHERE TABLE_NAME = 'CUSTOMERS';
DESC CUSTOMERS;
SELECT * FROM CUSTOMERS;
ALTER:
----------
IT IS USED TO ALTER THE OBJECT STRUCTURE.
TO ADD A NEW COLUMN IN A EXISTING TABLE:
-----
ALTER TABLE CUSTOMERS ADD GENDER CHAR(1);
TO RENAME A COLUMN IN EXISTING TABLE:
-----
ALTER TABLE CUSTOMERS RENAME COLUMN PHNO TO PHONE_NUMBER;
TO MODIFY THE DATATYPE OF A EXISTING COLUMN:
---------------
ALTER TABLE CUSTOMERS MODIFY GENDER VARCHAR2(10);
TO REMOVE A COLUMN FROM EXISTING TABLE:
----------------
ALTER TABLE CUSTOMERS DROP COLUMN PHONE_NUMBER;
RENAME:
----------
IT IS USED TO CHANGE THE TABLE NAME.
RENAME CUSTOMERS TO CUSTOMER;
TRUNCATE:
------------
IT IS USED TO REMOVE THE ENTIRE RECORDS FROM A TABLE PERMANENTLY.
TRUNCATE TABLE EMP;
DROP:
----------
IT IS USED TO REMOVE THE OBJECT PERMANENETLY FROM A DATABASE.
DROP TABLE SOURCE;
DML(DATA MANIPULATION LANAGUAGE):
-----------
IT IS AN EXPLICIT COMMIT.(MANUAL COMMIT).
INSERT:
--------
IT IS USED TO STORE THE NEW RECORD INTO A TABLE.
INSERT INTO EMP(EMPID,EMPNAME,SALARY)VALUES(100,'STEVEN',1000);
INSERT INTO EMP(EMPID,EMPNAME,SALARY)VALUES(200,'BRUCE',2000);
INSERT INTO EMP VALUES(300,'ALEX',3000);
INSERT INTO EMP(EMPID,EMPNAME)VALUES(400,'DAVID');
INSERT ALL INTO EMP(EMPID,EMPNAME,SALARY) VALUES(500,'WILLIAM',4000)
INTO EMP(EMPID,EMPNAME,SALARY) VALUES(600,'NEENA',5000)
INTO EMP(EMPID,EMPNAME,SALARY) VALUES(700,'WINSTON',6000)
SELECT * FROM DUAL;
UPDATE:
---
IT IS USED TO MODIFY THE EXISTING VALUE.
UPDATE EMP
SET SALARY=10000
WHERE EMPID=100;
UPDATE EMP
SET SALARY=20000
WHERE EMPID=400;
UPDATE EMP
SET EMPNAME='ALEXANDER',SALARY=30000
WHERE EMPID=300;
DELETE:
----------
IT IS USED TO REMOVE THE PARTICULAR RECORDS FROM A TABLE.
DELETE FROM EMP WHERE EMPID=700;
TCL(TRANSACTIONAL CONTROL LANGUAGE):
----------
commit:
---
it is used to save all pending transactions permanently into a database.
commit;
rollback:
----------
it is used to undo the pending transactions before doing commit.
it is used to remove the pending transactions permanently from a session.
savepoint:
----------------
[10:13 am, 20/6/2025] +91 89251 43687: oracle inbuilt table:
-----
dual:
-------
it is a dummy table.
we can perform the temporary transactions int it.
default-->onecolumn
one value-->'X'
select * from dual;
select 'welcome' from dual;
select 100+2000 as addition,100*2 as multiplication from dual;
Pseudo columns:(oracle inbuilt columns)
----------------------------------------
sysdate:
-----------
it is used to display the current system date.
select sysdate from dual;
systimestamp:
----------
it is used to display the current system date,time,zone
select systimestamp from dual;
user:
----------
it is used to display the the current login user.
select user from dual;
uid:
---
it is used to display the current login userid.
select uid from dual;
rownum:
--------
it used to display the running serial number in the result.
select first_name,salary,rownum from employees where rownum=1;
select first_name,salary,rownum from employees where rownum in (1,2);
select first_name,salary,rownum from employees where rownum in (4,5);
select first_name,salary,rownum from employees where rownum <=100;
select first_name,salary,rownum from employees where rownum > 100;
rowid:
----------
it is a 18 digit unique ID assigned to the each row at the time of new rows inserted into a table.
select empname,rowid from emp;
select empname,rowid from emp;
select * from emp;
delete from emp where empname='steven';
delete from emp where rowid='AAAGjZAAEAAAAUkAAC';
level:
----------
it will generate the hierarchical.
select level from dual
connect by level<=10;
currval --->sequence attributes
nextval --->sequence attributes
[10:21 am, 20/6/2025] +91 89251 43687: to take the backup of the table structure and data:
----------------
create table emp
as
select * from employees;
to copy only the table structure:
-----------
create table emp
as
select * from employees where 1=2;