Saturday, June 18, 2011

SQL Basics - Interview Questions Part 1



1. Difference between DCL and DML  commands?
DCL  commands  will   commit  implicitly   where  in  DML  commands  we  have   to commit explicitly.

2. Table, Rowid, Rownum
Table is a database object, which is having more than one column associated with its data type.
Rowid is the unique binary address of the row in a table
Rownum it  is a temporary  number  in a memory  and  was  assigned  to each  row selected by the statement.

3. Pseudo-Columns
Columns  that are not created explicitly by  the user and can be used  explicitly in queries.  The pseudo-Columns  are rowid,  rownum, currval,  nextval,  sysdate, and level

4. What is a View?
View is Virtual Table, which hold the data at runtime

5. Difference between ordinary view and complex view?
Simple  views  can be  modified  easily  and these  cant hold  the data where   as complex views  cant be modified  directly  and  complex view can  hold  the  data  to modify a complex view we have to use INSTEAD OF TRIGGERS.

6. Forced view
Creating a view on a table, which is not there  in the database.

7. Inline view
Inline view is basically  a subquery  with  an alias  that  you  can  use  like a  view inside a SQL statement.

8. What is an INDEX and what are the types?
INDEX is a database object used in oracle to provide quick access to  rows.

 9. Synonym
Synonym  is an  alias  name for any database  object like tables,  views,  functions, procedures.

10. SELECT statement syntax?

SELECT    From
Where
Group by  
Having
Connect prior

11. What is Constraint? Different Constraints?
Constraints   are  representators  of   the  columns   to  enforce  data  entity   and consistency. UNIQUE, NOT NULL, Primary key, foreign key, Check.

12. Difference between Primary key and Unique + Not Null constraints?
Unique + Not Null is a combination  of  two constraints and we can use more  than one Unique + Not Null in any table. Primary Key is a single constraint we can use only one time for a table. It can be a referential key for any column in  any table.

13. What is NULL?
Default Value.

14. Dual Table
It is a one row, one column table with value X.

15. Difference between Truncate and Delete?
Truncate  will  delete  all the  rows  from  the  table  with  out any condition.  It  will commit automatically  when it  fires  Where delete  will  delete  all or  specified  rows based upon the condition here we have to commit explicitly.



0 Responses to “SQL Basics - Interview Questions Part 1”

Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.