1) What is TRUNCATE table ?
TRUNCATE table is a DDL command used to remove all the rows from the specified table or cluster instantly. Eg: TRUNCATE TABLE table_name;
Advantage over DELETING:
a] It is a DDL statement and generates NO ROLLBACK information.
b] Doesn’t fire the tables DELETE TRIGGER.
c] Truncating the master table of a snapshot doesn’t record any changes in the tables snapshot log.
d] It’s more convenient than dropping and recreating the table.
e] D/R invalidates the table’s dependent objects than truncating the object.
f] D/R requires you to RE GRANT the privileges on the table while truncating doesn’t.
g] D/R requires you to RECREATE the INDEXES, INTEGRITY CONSTRAINTS, TRIGGERS and STORAGE PARAMETER while truncating doesn’t.
2)Can we use a function inside an INSERT statement ?
Yes. Eg: INSERT INTO EMP(COMM ) VALUES ( SAL*0.05 ) WHERE DEPTNO = 20;
3). What is the difference between a SYNONYM and a VIEW ?
A SYNONYM is a name assigned to a table or view that may thereafter be used to refer it. If you access to another user’s table, you may create a synonym for it and refer to it by the synonym alone, without entering the user’s name as a qualifier.
A View is a database object that is a logical representation of a table. It is derived from a table but has no storage space of its own and often may be used in the same manner as a table.
4)What is the difference between REPLACE and TRANSLATE ?
Syntax : REPLACE(string,if,then)
REPLACE replaces a character or characters in a string with 0 or more characters, if is a character or characters. Everytime it appears in a string, it is by the contents of then.
Eg: REPLACE(‘ADAH’,’A’,’BLAH’) - BLAHDBLAHH (Result)
Syntax: TRANSLATE(string,if,then)
TRANSLATE looks at each character in string, and then checks if to see if that character is there, if it is, TRANSLATE notes the position in if where it found the character, and then looks the same position in then. Whatever character it finds there it substitutes the character in string
Eg: TRANSLATE(‘RAMESH’,’RAM’,’SUR’) - SURESH(Result)
5)What is a LEVEL ?
LEVEL is a pseudo column, used with CONNECT BY. It is equal to 1 for a root, 2 for a child of root, 3 for a child of a child of a root and so on
