SQL Statements:
***************
-->It Is classified into 5 steps.
DDL (Data definition Language)
DML (Data Manipulation Language)
DQL/DRL (Data Query Language or Data Retrieval Language)
TCL (Transacton Control Language)
DCL (Data Control Language)
DDL - CREATE,ALTER,TRUNCATE,DROP,RENAME
DML - INSERT,UPDATE,DELETE,MERGE
DCL - GRANT,REVOKE (ORACLE DBA)
TCL - COMMIT,ROLLBACK,SAVEPOINT
DQL - SELECT (DAY TO DAY WORK)
Database objects:
-----------------
1.Table
2.View
3.Synonym
4.Sequence
5.Index
6.Procedure
7.Function
8.Trigger or Database Trigger
9.package
DDL(Data Definition Language):
*******************************
-->It is used to define the New database objects like Table,View,Synonym,Seuence,Procedure,Function,Trigger etc.
-->These are auto commit commands
-->It is used to modify the structure of objects.
-->DDL commands interact with the database directly.
DDL COMMANDS:
*************
1)CREATE
2)ALTER
3)RENAME
4)DROP
5)TRUNCATE
1)CREATE:
*********
-->It is used to create new database objects.
-->While creating a table we provide the basic information for each column together with their data type and size.
SYNTAX:
*******
CREATE TABLE TABLENAME
(
COLUMNNAME1 DATATYPE(SIZE),
COLUMNNAME2 DATATYPE(SIZE),
COLUMNNAME3 DATATYPE(SIZE),
............................,
.........................,
COLIMNNAMEN DATATYOE(SIZE)
);
Table_Name- Is the name of the table.
Column_Name1,Column Nmame2--Is the name of the columns
Datatype - Is the type of data that column holds.
EX1:
***
CREATE TABLE XX_STUDENT_DETAILS
(
STU_NO NUMBER(10),
STU_NAME VARCHAR2(20),
BRANCH VARCHAR2(20),
MOBILE NUMBER(10),
EMAIL VARCHAR2(40),
ADDREES1 VARCHAR2(20),
ADDRESS2 VARCHAR2(20),
ADDRESS3 VARCHAR2(20),
COUNTRY VARCHAR2(20),
STATE VARCHAR2(20),
CITY VARCHAR2(20),
ZIP VARCHAR2(20),
JDATE DATE
);
/
--BASE TABLES FOR ANY TYPE OF DATABASE OBJECTS
DBA_OBJECTS
ALL_OBJECTS
USER_OBJECTS
/
SELECT *
FROM DBA_OBJECTS;
/
OBJECT_NAME --TABLE NAME
OBJECT_TYPE --TABLE
/
SELECT *
FROM DBA_OBJECTS
WHERE OBJECT_TYPE='TABLE'
AND OBJECT_NAME='XX_STUDENT_DETAILS'; --TABLE NAME
/
SELECT *
FROM ALL_OBJECTS
WHERE OBJECT_TYPE='TABLE'
AND OBJECT_NAME='XX_STUDENT_DETAILS';
/
SELECT *
FROM USER_OBJECTS
WHERE OBJECT_TYPE='TABLE'
AND OBJECT_NAME='XX_STUDENT_DETAILS';
/
--BASE TABLES FOR TABLE NAMES
ALL_TABLES
USER_TABLES
/
SELECT *
FROM ALL_TABLES
WHERE TABLE_NAME='XX_STUDENT_DETAILS';
/
SELECT *
FROM USER_TABLES
WHERE TABLE_NAME='XX_STUDENT_DETAILS';
/
DESC XX_STUDENT_DETAILS;
/
SELECT * FROM XX_STUDENT_DETAILS;
/
CREATE TABLE XX_CUSTOMER_DETAILS
(
CNO NUMBER(10),
CNAME VARCHAR2(20),
MOBILE NUMBER(10),
EMAIL VARCHAR2(30),
ADDRESS VARCHAR2(30),
COUNTRY VARCHAR2(20),
STATE VARCHAR2(20),
CITY VARCHAR2(20),
ZIP VARCHAR2(20)
);
/
SELECT *
FROM DBA_OBJECTS
WHERE OBJECT_TYPE='TABLE'
AND OBJECT_NAME='XX_CUSTOMER_DETAILS';
/
DESC XX_CUSTOMER_DETAILS;
/
CREATE TABLE XX_EMP_DETAILS
(
EMPNO NUMBER(4),
ENAME VARCHAR2(30),
DOJ DATE,
DEPTNO NUMBER(3),
SAL NUMBER(10),
MOBILE NUMBER(10),
EMAIL VARCHAR2(40)
);
/
DBA_OBJECTS
ALL_OBJECTS
USER_OBJECTS
/
SELECT *
FROM DBA_OBJECTS
WHERE OBJECT_TYPE='TABLE'
AND OBJECT_NAME='XX_EMP_DETAILS';
/
ALL_TABLES
USER_TABLES
/
SELECT *
FROM ALL_TABLES
WHERE TABLE_NAME='XX_EMP_DETAILS';
/
DESC XX_EMP_DETAILS;
/
-->Once the table is created we will receive a message "Table Created".
-->If a same table name already exists in database then we will not be allowed to create a table again.
/
EX2:
****
CREATE TABLE XX_STUDENT_DTLS
(
SNO NUMBER(10),
SNAME VARCHAR2(20),
CLASS VARCHAR2(20),
SECTION VARCHAR2(20),
JDATE DATE,
MOBILE NUMBER,
EMAIL VARCHAR2(50)
);
/
EX3:
***
CREATE TABLE DEPTDTLS
(
DEPTNO NUMBER,
DNAME VARCHAR2(20),
LOC VARCHAR2(20)
);
/
TABLE --OBJECT_TYPE
TABLE_NAME --OBJECT_NAME
/
CREATE TABLE XX_PO_HEADERS_ALL
(
PO_HEADER_ID NUMBER(20),
TYPE_LOOKUP_CODE VARCHAR2(20),
SEGMENT1 VARCHAR2(20),
VENDOR_ID NUMBER,
VENDOR_SITE_ID NUMBER,
START_DATE DATE,
END_DATE DATE
);
/
--database base tables
SELECT *
FROM ALL_TABLES; --table information
/
select *
from all_objects; --all database objects
/
select *
from dba_objects; --all database objects
/
select *
from user_objects; --all database objects
/
DESC XX_STD1;
/
select *
from all_objects
where object_name='XX_EMP_DUMMY_TEST'
order by object_type;
/
SELECT *
FROM DBA_OBJECTS
WHERE OBJECT_NAME='XX_EMP_DUMMY_TEST';
/
ALL_TABLES,USER_TABLES
/
SELECT *
FROM ALL_TABLES
WHERE TABLE_NAME='XX_EMP_DUMMY_TEST';
/
DESCRIBE/DESC:
**************
-->It is used to retrieve structure of the table.
SYNTAX:
*******
DESC <TABLENAME>
EX:
***
DESC XX_STUDENT_Details;
DESC DEPTDTLS;
DESC XX_EMP_DUMMY_TEST;
2)ALTER:
*********
-->It is used to alter the structure of the table or it is used to change the structure of existing table.
-->It is used to add,delete or modify columns in existing table.
-->The ALTER command is used to perform the following functions.
A)ADD
B)MODIFY
C)RENAME
D)DROP
ADD:
****
-->It is used to add the new columns to the existing table
SYNTAX:
*******
ALTER TABLE <TABLENAME> ADD <COLUMNNAME> DATATYPE <SIZE>; --SINGLE COLUMN
ALTER TABLE <TABLENAME> ADD (<COLUMNNAME1> DATATYPE <SIZE>,<COLUMNNAME1> DATATYPE <SIZE>); --MULTIPLE COLUMNS
HOW TO ADD A SINGLE COLUMN TO THE EXISTING TABLE:
************************************************
/
ALTER TABLE TABLENAME ADD COLUMNNAME DATATYPE(SIZE);
/
COLLEGE_NAME -- SIGLE COLUMN NAME
/
ALTER TABLE XX_STUDENT_DETAILS ADD COLLEGE_NAME VARCHAR2(50);
/
HOW TO TO ADD MULTIPLE COLUMNS TO THE EXISTING TABLE:
*****************************************************
/
ALTER TABLE TABLENAME ADD(COL1 DATATYPE(SIZE),COL2 DATATYPE(SIZE),.....,COLN DATATYPEN(SIZE);
/
DESC XX_STUDENT_DETAILS;
/
SUB1 VARCHAR2(20) --COLUMN NAME
SUB2 VARCHAR2(20) --COLUMN NAME
/
ALTER TABLE XX_STUDENT_DETAILS ADD(SUB1 VARCHAR2(20),SUB2 VARCHAR2(20));
/
-->Once the table is successfully altered then we will receive a message saying "Table Altered".
Let's check the table names.
/
-->SELECT * FROM XX_STUDENT_DETAILS;;
/
MODIFY:
*******
-->It is used to change or modify the structure of exsting column datatypes and size(datatype and size).
-->It is used to modify the column in a table.
SYNTAX:
*******
ALTER TABLE <TABLENAME> MODIFY <COLUMNNAME> <DATATYPE> <SIZE>; --SINGLE COLUMN
ALTER TABLE <TABLENAME> MODIFY (<COLUMNNAME1> <DATATYPE> <SIZE>,<COLUMNNAME2> <DATATYPE> <SIZE>); --MULTIPLE COLUMNS
EX:
****
STU_NAME VARCHAR2(20) -- VARCHAR2(40)
/
ALTER TABLE TABLENAME MODIFY COLUMNNAME DATATYPE(SIZE); --SINGLE COLUMN
/
ALTER TABLE XX_STUDENT_DETAILS MODIFY STU_NAME VARCHAR2(40);
/
DESC XX_STUDENT_DETAILS;
/
ZIP VARCHAR2(20) --VARCAHR2(10);
/
ALTER TABLE TABLENAME MODIFY COLUMNNAME DATATYPE(SIZE); --SINGLE COLUMN
/
ALTER TABLE XX_STUDENT_DETAILS MODIFY ZIP VARCHAR2(10);
/
STU_NO NUMBER(10) --VARCHAR2(10) ==Change the datatype
JDATE DATE --VARCHAR2(10) --Change the Datatype and size
/
ALTER TABLE TABLENAME MODIFY(COL1 DATATYPE(SIZE),COL2 DATATYPE(SIZE),.....,COLN DATATYPE(SIZE); ---MULTIPLE COLUMNS
/
DESC XX_STUDENT_DETAILS;
/
ALTER TABLE XX_STUDENT_DETAILS MODIFY(STU_NO VARCHAR2(10),JDATE VARCHAR2(10));
/
DESC XX_STUDENT_DETAIAILS;
/
RENAME:
*******
-->It is used to rename a columnname in a table.
-->We can rename only one column at a time.
SYNTAX:
*******
ALTER TABLE <TABLENAME> RENAME COLUMN <OLDCOLUMNNAME> TO <NEWCOLUMNNAME>
/
EX:
****
/
ALTER TABLE TABLENAME RENAME COLUMN OLDCOLUMNNAME TO NEWCOLUMNNAME;
/
DESC XX_STUDENT_DETAILS;
/
STU_NO --SNO
/
ALTER TABLE XX_STUDENT_DETAILS RENAME COLUMN STU_NO TO SNO;
/
DESC XX_STUDENT_DETAILS;
/
--STU_NAME -- SNAME
/
ALTER TABLE XX_STUDENT_DETAILS RENAME COLUMN STU_NAME TO SNAME;
/
DESC XX_STUDENT_DETAILS;
/
DROP:
*****
-->It is used to drop a column in a table.
SYNTAX:
*******
/
ALTER TABLE TABLENAME DROP COLUMN COLUMNNAME; --SINGLE COLUMN
/
ALTER TABLE XX_STUDENT_DETAILS DROP COLUMN COLLEGE_NAME;
/
COUNTRY
MOBILE
/
ALTER TABLE TABLENAME DROP(COL1,COL2,...,COLN); --MULTIPLE COLUMNS
/
DESC XX_STUDENT_DETAILS;
/
ALTER TABLE XX_STUDENT_DETAILS DROP(COUNTRY,EMAIL,MOBILE);
/
DESC XX_STUDENT_DETAILS;
/
3)RENAME:
*********
-->It is used to rename the table name.
SYNTAX:
*******
RENAME OLDTABLENAME TO NEWTABLENAME
EX:
***
RENAME XX_STUDENT_DETAILS TO XX_STUDENT_DETAILS_1;
/
4)DROP:
*******
-->It is used to drop the table permanently in database.
-->
SYNTAX:
*******
DROP TABLE <TABLENAME>
EX:
***
DROP TABLE XX_STUDENT_DETAILS_1;
5)TRUNCATE:(DELETE+COMMIT)
***********
-->It is used to remove all the records from table.
-->data will be removed permanently in database but not the table itself.
-->Automatically Implicit commit will be fire.
-->It can not rollback
-->It works like a delete +commit.
SYNTAX:
*******
TRUNCATE TABLE <TABLENAME>
EX:
***
TRUNCATE TABLE XX_STUDENT;
/
COMMENT:
*********
-->Comment statement to add a comment about a table,view,snapshot, or column into the data dictionary.
SYNTAX:
*******
COMMENT ON TABLE <TABLENAME>/COLUMN <TABLENAME.COLUMNNAME> IS 'TEXT'.
EX:
****
COMMENT ON TABLE XX_EMP1 IS 'IT will STORE EMPLOYEE INFORMATION';
COMMENT ON TABLE XX_STD1 IS 'IT WILL STORE STUDENT INFORMATION';
/
SELECT *
FROM USER_TAB_COMMENTS
WHERE TABLE_NAME='XX_STD1';
/
COMMENT ON COLUMN XX_EMP1.ENAME IS 'EMPLOYEE NAMES';
COMMENT ON COLUMN XX_STD1.SNAME IS 'STUDENT NAMES';
/
SELECT *
FROM ALL_COL_COMMENTS
WHERE TABLE_NAME='XX_STD1';
/
--database base tables
select *
from all_objects;
/
select *
from dba_objects;
/
select *
from user_objects;
/
DESC XX_STD1;
/
select *
from all_objects
where object_name='XX_STD1'
order by object_type;
/
SELECT *
FROM DBA_OBJECTS
WHERE OBJECT_NAME='XX_STD1';
/
ALL_TABLES,USER_TABLES
/
SELECT *
FROM ALL_TABLES
WHERE TABLE_NAME='XX_STD1';
No comments:
Post a Comment