DDL: DATA DEFINITION
LANGUAGE
------------------------------
CREATE, ALTER, DROP, RENAME, TRUNCATE, COMMENT
to create table we should have CREATE TABLE privilege and
also we should
UNLIMITED TABLESPACE privilege.
CREATE ANY TABLE ->privilege require to create table in
any schema.
DROP TABLE PRODUCT;
create table product;
create table product();
CREATE TABLE PRODUCT(PROD_ID NUMBER(3), PROD_NAME
VARCHAR2(25));
DESCRIBE product;
DESC PRODUCT;
DESC DBMS_OUTPUT;
ALTER TABLE statement allows you to add, modify, drop, or
rename
a column from an existing table
1- To add a column to an existing table:
ALTER TABLE <table_name> ADD (<column_name datatype(size) [DEFAULT
<value>]
[CONSTRAINTS],
<COL_NAME2 DATATYPE(SIZE),.....)
DESC PRODUCT;
ALTER TABLE Product ADD description VARCHAR2(50);
DESC product;
2- To add multiple columns to an existing table:
ALTER TABLE <table_name> ADD ( col1 coldef, col2
coldef, ... coln col_def);
ALTER TABLE product ADD(price NUMBER(8,2), location
VARCHAR2(20));
DESC product;
3- To modify a column in an existing table:
syntax:
ALTER TABLE <table_name> MODIFY <col_name
col_type(SIZE)>;
DESC product;
select * from product;
ALTER TABLE product MODIFY location NUMBER(8,2);
DESC product;
INSERT INTO product VALUES(101, 'Monitor', '15 inches
monitor', 3500, 1100);
DESC product;
select * from product;
ALTER TABLE product MODIFY prod_name NUMBER(15);
ALTER TABLE product MODIFY prod_name CHAR(25);
DESC product;
SELECT * FROM product;
ALTER TABLE product MODIFY description VARCHAR2(14);
ALTER TABLE product MODIFY description VARCHAR2(25);
SELECT LENGTH(DNAME) FROM DEPT;
ALTER TABLE DEPT MODIFY DNAME VARCHAR2(9);
DESC DEPT;
4- To modify multiple columns in an existing table:
ALTER TABLE <table_name> MODIFY (col1 type, col2 type,
... coln type)
DESC product;
DELETE FROM product;
ALTER TABLE product MODIFY(location VARCHAR2(20), prod_name
varchar2(50));
DESC product;
5- To drop a column in an existing table:
syntax:
ALTER TABLE <table_name> DROP COLUMN column_name;
syntax:
ALTER TABLE <TABLE_NAME> DROP(COL1, COL2,....);
ALTER TABLE product DROP COLUMN location;
DESC product;
ALTER TABLE product DROP(description, price);
DESC product;
6- To rename a column in an existing table:
ALTER TABLE <table_name> RENAME COLUMN
<old_name> TO <new_name>;
DESC PRODUCT;
ALTER TABLE product RENAME COLUMN prod_name TO
product_name;
DESC product;
7- The basic syntax for renaming a table is:
SYNTAX:
ALTER TABLE <table_name> RENAME TO
<new_table_name>;
DESC product_information;
ALTER TABLE product RENAME TO product_information;
DESC product;
DESC product_information;
SYNTAX:
RENAME <old_table_name> TO
<new_table_name>;
Eg.,
RENAME product_information TO product;
DESC product;
DESC PRODUCT_INFORMATION;
DROP TABLE emp_new;
CREATE TABLE emp_new AS SELECT * FROM emp;
DESC emp_new;
SELECT * FROM emp_new;
SELECT * FROM emp;
DROP TABLE emp_new2;
CREATE TABLE emp_new2 AS SELECT * FROM emp WHERE 1=2;
SELECT * FROM emp_new2;
SYNTAX:
DROP TABLE <tablename>;
DROP TABLE product;
SELECT * FROM PRODUCT;
SELECT * FROM RECYCLEBIN;
FLASHBACK TABLE product TO BEFORE DROP;
SELECT * FROM RECYCLEBIN;
DESC PRODUCT;
SELECT * FROM product;
INSERT INTO PRODUCT VALUES(101, 'LENOVO');
INSERT INTO PRODUCT VALUES(102, 'TOSHIBA');
commit;
SELECT * FROM product;
SELECT * FROM RECYCLEBIN;
FLASHBACK TABLE PRODUCT TO BEFORE DROP RENAME TO
PRODUCT5;
SELECT * FROM recyclebin;
DROP TABLE PRODUCT1;
PURGE RECYCLEBIN;
SELECT * FROM recyclebin;
DROP TABLE PRODUCT5 PURGE;
SELECT * FROM RECYCLEBIN;
SELECT * FROM emp;
select empno,ename,hiredate,to_char(hiredate,'DAY')
from emp where to_char(hiredate, 'D')=5;
select empno,ename,hiredate,to_char(hiredate,'DAY')
from emp where RTRIM(to_char(hiredate, 'DAY'))='THURSDAY';
SELECT hiredate, TO_CHAR(hiredate, 'DAY') "Day" FROM emp;
SYNTAX:
TRUNCATE TABLE <tablename>;
select * from emp_new;
TRUNCATE TABLE emp_new;
SELECT * FROM emp_new;
ROLLBACK;
SELECT * FROM emp_new;
DIFFERENCE BETWEEN TRUNCATE,
DELETE, AND DROP
=============================================
TRUNCATE DELETE DROP
----------------- ----------------------------- ------------------
DDL DML DDL
It will delete all the It will delete all rows It will drop the table
rows permanently and or
it can delete rows and data of the table
unconditionally from conditionally. permanently.
the table
NO ROLLBACK ROLLBACK
CAN BE PERFORMED NO ROLLBACK
NO CONDITION CAN BE SPECIFIED |
CONDITION CAN BE SPECIFIED | NO CONDITION CAN BE SPECIFIED
FAST SLOW FAST
TRIGGER CANNOT BE FIRED | TRIGGER CAN BE FIRED
| TRIGGER CANNOT BE FIRED
TCL NOT REQUIRED |
TCL REQUIRED |
TCL NOT REQUIRED
REDO LOGS NOT GENERATED |
REDO LOGS GENERATED | REDO LOGS NOT GENERATED
REMOVES DATASPACE &
INDEXSPACE | REMOVES ONLY DATASPACE |
REMOVES INDEXES AND
PRIVILEGES
-- Dt.18-Mar-2014 –
SYNTAX:
COMMENT ON TABLE
<TABLE_NAME> IS 'TEXT';
COMMENT ON TABLE EMP IS 'This
table has employee details for HR department';
SELECT * FROM USER_TAB_COMMENTS;
COMMENT ON TABLE EMP IS '';
SELECT * FROM USER_TAB_COMMENTS;
SYNTAX:
COMMENT ON COLUMN
<TABLE.COLUMN_NAME> IS 'TEXT';
COMMENT ON COLUMN emp.sal IS
'This column contains employees salary since 4 yrs';
SELECT * FROM dictionary WHERE
table_name LIKE '%COMMENT%';
SELECT * FROM USER_TAB_COMMENTS;
SELECT * FROM USER_COL_COMMENTS;
DROP TABLE product;
CREATE TABLE PRODUCT(PRODUCT_ID
NUMBER(4), PRODUCT_NAME VARCHAR2(25));
DESC product;
DML/TCL:
DML: SELECT, INSERT, UPDATE,
DELETE, MERGE
TCL: COMMIT, ROLLBACK, SAVEPOINT
SELECT * FROM PRODUCT;
ALTER TABLE PRODUCT ADD(PRICE
NUMBER(8,2));
SYNTAX:
INSERT INTO <TABLE_NAME>
[(COLNAME1, COLNAME2,....)] VALUES(VALUE1, VALUE2,,....);
DESC product;
INSERT INTO PRODUCT VALUES
(101,'PRINTER',49.99);
INSERT INTO PRODUCT VALUES
(101,'PRINTER'); -- gives error
INSERT INTO PRODUCT VALUES
(101,49.99, 'PRINTER'); -- gives error
INSERT INTO
PRODUCT(PRODUCT_ID,PRODUCT_NAME) VALUES (102,'COPIER');
SELECT * FROM product;
INSERT INTO
PRODUCT(PRODUCT_NAME,PRODUCT_ID) VALUES ('SCANNER',103);
SELECT * FROM product;
SET VERIFY ON
INSERT INTO PRODUCT VALUES(&PRODID,
'&PRODNAME', &PRICE);
COMMIT;
SELECT * FROM product;
ROLLBACK;
SELECT * FROM product;
CREATE TABLE emp19 AS SELECT *
FROM emp WHERE 1=2;
SELECT * FROM emp19;
INSERT INTO emp19 SELECT * FROM
emp;
INSERT INTO emp19(empno, ename,
sal) SELECT empno, ename, sal FROM emp;
SELECT * FROM emp19;
SYNTAX:
UPDATE <TABLE_NAME> SET
<COLUMN_NAME> = VALUE, [COLUMN_NAME> = VALUE,......]
[WHERE <CONDTION>];
SELECT * FROM product;
UPDATE PRODUCT SET
PRICE=150;
SELECT * FROM product;
ROLLBACK;
UPDATE PRODUCT SET PRICE=150
WHERE PRODUCT_ID=102;
SELECT * FROM product;
UPDATE PRODUCT SET PRODUCT_ID=100
WHERE PRODUCT_ID=101;
SELECT * FROM product;
INSERT INTO PRODUCT VALUES
(110,'FASCIMILE',250);
SELECT * FROM product;
INSERT INTO PRODUCT VALUES
(111,'ALLINONE',500);
SELECT * FROM product;
COMMIT;
ROLLBACK;
SELECT * FROM PRODUCT;
UPDATE PRODUCT SET PRICE=200,
PRODUCT_NAME='XEROX' WHERE PRODUCT_ID=103;
UPDATE product SET price=(SELECT
price FROM product WHERE product_id=111) WHERE product_id=105
SELECT * FROM PRODUCT;
COMMIT;
SYNTAX:
DELETE [FROM] <TABLE_NAME>
[<WHERE CONDITION>];
SELECT * FROM PRODUCT;
DELETE PRODUCT;
SELECT * FROM product;
ROLLBACK;
SELECT * FROM product;
DELETE FROM product;
DELETE FROM PRODUCT WHERE
PRODUCT_ID = 100;
SELECT * FROM product;
DELETE FROM PRODUCT WHERE
PRODUCT_NAME='COPIER';
SELECT * FROM product;
DELETE FROM product WHERE
price=500;
DELETE FROM product WHERE
product_id=101;
ROLLBACK;
SELECT * FROM product;
COMMIT;
DELETE FROM product WHERE
product_id=111;
INSERT INTO product VALUES(112,
'Nokia', 15000);
DELETE FROM product WHERE
product_id=105;
UPDATE product SET price=1500
WHERE product_id=112;
ROLLBACK;
commit;
SYNTAX:
SAVEPOINT
<SAVEPOINT_NAME>;
INSERT INTO PRODUCT VALUES
(120,'FASCIMILE1',350);
SAVEPOINT S2;
INSERT INTO PRODUCT VALUES
(121,'ALLINONE1',501);
SAVEPOINT S3;
DELETE FROM product WHERE
product_id=100;
SELECT * FROM product;
ROLLBACK;
ROLLBACK TO SAVEPOINT S3;
SELECT * FROM product;
ROLLBACK TO SAVEPOINT S2;
SELECT * FROM PRODUCT;
COMMIT;
SELECT * FROM PRODUCT;
DELETE FROM PRODUCT;
SELECT * FROM product;
ROLLBACK;
SELECT * FROM PRODUCT;
DELETE FROM PRODUCT WHERE PROD_ID
= 101;
SELECT * FROM PRODUCT;
DELETE FROM PRODUCT WHERE
PRODUCT_NAME='COPIER';
TRUNCATE TABLE product;
ROLLBACK;
DESC PRODUCT;
SELECT * FROM PRODUCT;
No comments:
Post a Comment