Home » Sample Chapters » Oracle SQL Interview Questions

Oracle SQL Interview Questions

Posted by admin on Feb 28, 2011 in Sample Chapters | Comments Off

Oracle DBA Interview Questions Answers during technical interview – A Sample Chapter

Oracle Interview Questions and Answers during telephonic technical interviews

A Sample Chapter of book Oracle DBA Interview Questions – Topic covered in this sample chapter is “SQL – Essential for Oracle DBA”

Here you can find out some Oracle Interview Questions and Answers for the topic of Oracle SQL and RDBMS. These all interview questions and answers are real time Oracle DBA interview questions and answers. Be careful about Oracle DBA interviews because sometime very strange SQL questions might be raised during interviews. As a Oracle DBA candidate you can’t eliminate the SQL interview questions during Oracle DBA interviews.

Some strange interview questions are really very simple but we found those strange because you are not aware of those questions and answers. Most of cases candidate failed due to not preparing proper interview questions and answers.

Following all Oracle DBA interview questions and answers are sampling from our excellent book “Oracle DBA Interview Questions and Answers”. These are only sample interview questions and answers of Oracle DBA book’s topic “SQL – Essential for Oracle DBA”. Full topic is covered in book with total 50 important and strange SQL interview questions with answers.

Q-1: How to determine the structure of the any table? Give to 2 options?

Use the describe command or use the dbms_metadata.get_ddl package.

Q-2: Give the two types of tables involved in producing a star schema and the type of data they hold.

Fact tables and dimension tables. A fact table contains measurements while dimension tables will contain data that will help describe the fact tables.

Q-3: If I am executing command “select 8 from emp” then what would be happening?

If total number of rows are in EMP table 14 then we would get output 8 , up to 14 times.

Q-4: A table is classified as a parent table and you want to drop and re-create it. How would you do this without affecting the children tables?

Disable the foreign key constraint to the parent, drop the table, re-create the table, enable the foreign key constraint.

Q-5: What is a Cartesian product?

A Cartesian product is the result of an unrestricted join of two or more tables. The result set of a three table Cartesian product will have x * y * z number of rows where x, y, z correspond to the number of rows in each table involved in the join.

Q-6: What is the default ordering of an ORDER BY clause in a SELECT statement?

Ascending.

Q-7: What type of index should you use on a fact table?

A Bitmap index.

Q-8: What is basic difference between view and materialized view?

Simple view is logical and only definition stored in data dictionary view of system schema means it doesn’t occupy any space in database, but materialized view is real table and it occupy space in database.

Q-9: Can a table contain more than 1 primary key?

No we can’t able to create more than 1 primary key on single table.

Q-10: Any trick to create more than one primary key on single table?

Create one primary and create NOT NULL column with Unique Constraints. Using this trick we can able to create more than one primary key in single table.

Q-11: Can Check constraint be used for self referential integrity ? How ?

Yes. In the CHECK condition for a column of a table, we can reference some other column of the same table and thus enforce self referential integrity.

Q-12: Can null keys be entered in cluster index, normal index? OR can I able to insert NULL values in cluster index or normal index?

Yes we can insert NULL keys in cluster index and normal index.

Q-13: What is the use of Data Dictionary?

Used by Oracle to store information about various physical and logical Oracle structures e.g. Tables, Tablespaces, datafiles, etc. Data Dictionary stores Oracle database metadata.

Q-14: Are views automatically updated when base tables are updated?

Yes views are automatically updated if any base table updated.

Q-15: What is the SYSDATE? Is Sysdate a system variable or a system function?

SYSDATE is system function.

Q-16: What is the difference between alias and synonym?

Alias is temporary and used with one query. Synonym is permanent and not used as alias.

Q-17: What is the difference between foreign key and reference key?

Foreign key is the key i.e. attribute which refers to another table primary key. Reference key is the primary key of table referred by another table.

Q-18: Can DUAL table be deleted, dropped or altered or updated or inserted?

Yes we can delete, update, delete or insert in DUAL table.

Q-19: Any effect will be occurring after updating or changing DUAL table?

Output using database functions are becoming unstable and might be wrong or producing errors.

Q-20: What is the difference between SQL*Plus commands and SQL commands?

SQL commands are terminated using / or ; (semi-colon) but SQL*Plus commands are terminated using enter key.

Q-21: Can I execute SQL*Plus command from buffer?

No. SQL*Plus command never saved in buffer only SQL commands are saved in buffer.

Q-22: Is it possible to use LONG columns in WHERE clause or ORDER BY?

No. We can’t use LONG column in where clause or order by clause of query.

Q-23: What is ROWNUM and ROWID in Oracle table?

ROWNUM and ROWID are pseudo columns.

Q-24: Give difference of ROWID and ROWNUM?

ROWID has a physical significance i.e you can read a row if you know rowid. It is complete physical address of a row. While ROWNUM is temporary serial number allocated to each returned row during query execution.

Q-25: Can we insert more than single NULL in unique constraint or unique index?

Yes we can insert more than one NULL value in unique constraint or unique index. Because every NULL is not similar.

Q-26: Can we create unique constraint using non-unique index?

Yes we can create unique constraint or primary key using non-unique index.

This is the sample SQL interview question answers from topic “SQL – Essential for Oracle DBA” of the book Oracle DBA Interview Questions. Kendba provides unique career making services for Oracle DBA from start online Oracle Training, Oracle interview questions books, fresher Oracle jobs assistance services.