Oracle

     

DROP – delete the table from db

TRUNCATE – delete contents of table

Rownum, Rowid             

Rownum - Virtual

rowid - Physical (used in sub query)       

Functions available in Orc

Rank() function                

Sysdate                System date      

Joins                     

Group by                            

Order by                             

Top 5                    

Sequence use in oracle                

Sample queries –

To  search word containing %

 SELECT col_name FROM tbl_nam WHERE col_name LIKE '%?%%' ESCAPE '?';

SUBSTR returns a specified portion of a string eg SUBSTR('BCDEF',4) output BCDE

INSTR provides character position in which a pattern is found in a string. eg INSTR('ABC-DC-F','-',2) output 7 (2nd occurence of '-')

HAVING clause is used when you want to specify a condition for a group function and it is written after GROUP BY clause.

Number of days between two days - weekends

select sysdate - creation_dttm - 2 * (to_char(sysdate, 'WW') - to_char(creation_dttm, 'WW')) from the_table                    

Compare and contrast TRUNCATE and DELETE for a table.

Both the truncate and delete command have the desired outcome of getting rid of all the rows in a table. The difference between the two is that the truncate command is a DDL operation and just moves the high water mark and produces few rollback data. The delete command, on the other hand, is a DML operation, which will produce rollback data and thus take longer to complete.

Explain the difference between ARCHIVELOG mode and NOARCHIVELOG mode and the benefits and disadvantages to each.

ARCHIVELOG mode is a mode that you can put the database in for creating a backup of all transactions that have occurred in the database so that you can recover to any point in time. NOARCHIVELOG mode is basically the absence of ARCHIVELOG mode and has the disadvantage of not being able to recover to any point in time. NOARCHIVELOG mode does have the advantage of not having to write transactions to an archive log and thus increases the performance of the database slightly.

Explain the difference between $ORACLE_HOME and $ORACLE_BASE.

ORACLE_BASE is the root directory for oracle. ORACLE_HOME located beneath ORACLE_BASE is where the oracle products reside.

How would you determine the time zone under which a database was operating?

SELECT dbtimezone FROM DUAL;

Describe the use of %ROWTYPE and %TYPE in PL/SQL

 %ROWTYPE allows you to associate a variable with an entire table row. The %TYPE associates a variable with a single column type.

What is the fastest query method for a table

Fetch by rowid