Interview Questions.

Q1. Difference Between Stored Procedure And Trigger?

We can call stored manner explicitly.

But trigger is mechanically invoked while the action described in cause is executed.

Ex: create cause after Insert on

this trigger invoked after we insert something on that desk.

Stored technique can not be inactive but trigger can be Inactive.

Triggers are used to initiate a particular pastime after enjoyable positive circumstance.It need to define and can be permit and disable consistent with need.

Q2. What Is "normalization"? "denormalization"? Why Do You Sometimes Want To Denormalize?

Normalizing information me getting rid of redundant records from a desk and organizing the statistics so that future changes to the desk are simpler.

 Denormalization me permitting redundancy in a desk. The main gain of denormalization is progressed overall performance with simplified data retrieval and manipulation. This is finished by using reduction within the wide variety of joins needed for records processing.

Q3. What's The Maximum Size Of A Row?

8060 bytes.

Q4. What Is A Sql View?

An output of a query may be stored as a view. View acts like small desk which meets our criterion. View is a pre-complied SQL query which is used to pick facts from one or greater tables. A view is like a table but it would not physically take any space. View is a good way to offer statistics in a specific format in case you use that question quite regularly. View also can be used to limit customers from gaining access to the tables immediately.

Q5. Which System Table Contains Information On Constraints On All The Tables Created ?

USER_CONSTRAINTS,

device table consists of statistics on constraints on all of the tables created

Q6. What Is The Advantage To Use Trigger In Your Pl?

A cause is a database item without delay associated with a specific desk. It fires whenever a particular declaration/type of assertion is issued against that desk. The styles of statements are insert,update,delete and question statements. Basically, cause is a set of SQL statements A cause is a approach to the restrictions of a constraint. For instance:

A database column can't convey PSEUDO columns as criteria in which a trigger can. 

A database constraint cannot refer antique and new values for a row where a cause can.

Triggers are fired implicitly at the tables/perspectives on which they may be created. There are numerous blessings of using a cause. Some of them are:

Suppose we want to validate a DML statement(insert/Update/Delete) that modifies a table then we will write a cause on the desk that gets fired implicitly each time DML declaration is finished on that desk.

Another purpose of the usage of triggers can be for automated updation of 1 or extra tables each time a DML/DDL announcement is carried out for the desk on which the trigger is created.

Triggers can be used to implement constraints. For eg : Any insert/update/ Delete statements must not be allowed on a selected desk after workplace hours. For imposing this constraint Triggers ought to be used.

Triggers may be used to put up statistics about database activities to subscribers. Database occasion may be a gadget event like Database startup or shutdown or it may be a person even like User loggin in or consumer logoff.

Q7. Why You Need Indexing? Where That Is Stored And What You Mean By Schema Object? For What Purpose We Are Using View?

We can't create an Index on Index.. Index is stoed in user_index table. Every object that has been created on Schema is Schema Object like Table, View and so on. If we need to share the unique records to numerous customers we must use the digital desk for the Base table. So that could be a view.

Indexing is used for faster search or to retrieve facts faster from numerous desk. Schema containing set of tables, essentially schema me logical separation of the database. View is crated for quicker retrieval of statistics. It's customized digital table. We will create a unmarried view of a couple of tables. Only the disadvantage is..View needs to be get refreshed for retrieving updated facts.

Q8. What Are Defaults? Is There A Column To Which A Default Can't Be Bound?

A default is a fee on the way to be used by a column, if no value is provided to that column whilst putting data. IDENTITY columns and timestamp columns cannot have defaults bound to them.

Explain exceptional isolation ranges:

An isolation degree determines the degree of isolation of information among concurrent tractions. The default SQL Server isolation degree is Read Committed. Here are the opposite isolation degrees (in the ascending order of isolation):

Read Uncommitted,

Read Committed,

Repeatable Read,

Serializable

Q9. What Is Cluster.Cluster Index And Non Cluster Index ?

Clustered Index:- A Clustered index is a special form of index that reorders the way data inside the table are physically stored. Therefore desk may also have only one clustered index.Non-Clustered Index:- A Non-Clustered index is a unique form of index in which the logical order of the index does not healthy the bodily stored order of the rows in the disk. The leaf nodes of a non-clustered index does now not consists of the data pages. Alternatively the leaf node carries index rows.

Q10. Why Can A "institution By" Or "order By" Clause Be Expensive To Process?

Processing of "institution by" or "order through" clause regularly calls for introduction of Temporary tables to process the consequences of the question. Which depending of the end result set may be very pricey.

Q11. What Is A "constraint"?

A constraint lets in you to use simple referential integrity tests to a table.

There are 4 number one varieties of constraints:

PRIMARY/UNIQUE - enforces forte of a selected desk column. But by default number one key creates a clustered index on the column, where are precise creates a non-clustered index with the aid of default. Another most important difference is that, number one key doesn't permit NULLs, but specific key lets in one NULL handiest.

DEFAULT - specifies a default fee for a column in case an insert operation does no longer offer one.

FOREIGN KEY - validates that every fee in a column exists in a column of another desk.

CHECK - tests that every value saved in a column is in a few specific list. Each form of constraint plays a specific type of action. Default isn't a constraint. NOT NULL is one greater constraint which does no longer permit values in the unique column to be null. And also it the best constraint which isn't a desk level constraint.

Q12. What Types Of Index Data Structures Can You Have?

An index allows to faster search values in tables. The three maximum commonly used index-sorts are: -

B-Tree: builds a tree of feasible values with a list of row IDs which have the leaf value. Needs a whole lot of area and is the default index kind for most databases.

Bitmap: string of bits for every feasible price of the column. Each bit string has one bit for every row. Needs most effective few space and may be very fast.(but, area of cost can not be big, e.G. SEX(m,f); degree(BS,MS,PHD).

 Hash: A hashing algorithm is used to assign a hard and fast of characters to represent a textual content string inclusive of a composite of keys or partial keys, and compresses the underlying data. Takes longer to build and is supported by incredibly few databases.

Q13. What Is The Difference Between Oracle, Sql And Sql Server ?

Oracle is based totally on RDBMS.

SQL is Structured Query Language.

SQL Server is some other device for RDBMS provided by means of MicroSoft.

Q14. What Is The Difference Between A "in which" Clause And A "having" Clause?

"Where" is a kind of restriction assertion. You use in which clause to restrict all of the information from DB. Where clause is the usage of earlier than end result retrieving. But Having clause is the use of after retrieving the facts. Having clause is a form of filtering command.

Q15. What Is Difference Between Co-related Sub Query And Nested Sub Query?

Correlated subquery runs once for every row decided on by the outer question. It carries a connection with a value from the row selected with the aid of the outer query.

Nested subquery runs most effective once for the entire nesting (outer) question. It does no longer incorporate any reference to the outer query row.

For example,

Correlated Subquery:

select e1.Empname, e1.Basicsal, e1.Deptno from emp e1 wherein e1.Basicsal = (pick max(basicsal) from emp e2 in which e2.Deptno = e1.Deptno)

Nested Subquery:

select empname, basicsal, deptno from emp where (deptno, basicsal) in (pick out deptno, max(basicsal) from emp group by deptno)

Q16. What Is The Difference Between Sql And Sql Server ?

SQLServer is an RDBMS similar to oracle,DB2 from Microsoft

whereas 

Structured Query Language (SQL), suggested "sequel", is a language that offers an interface to relational database systems. It turned into advanced via IBM in the 1970s for use in System R. SQL is a de facto wellknown, in addition to an ISO and ANSI widespread. SQL is used to carry out numerous operations on RDBMS.

Q17. What Is The Difference Between Truncate And Delete Commands?

Both will result in deleting all the rows inside the desk .TRUNCATE call cannot be rolled lower back as it is a DDL command and all memory area for that table is launched lower back to the server. TRUNCATE is a whole lot faster.Whereas DELETE call is an DML command and can be rolled lower back.

Q18. What Are The Tradeoffs With Having Indexes?

Faster selects, slower updates.

Extra storage space to keep indexes. Updates are slower because similarly to updating the table you have to replace the index.

Q19. What Operator Performs Pattern Matching?

Pattern matching operator is LIKE and it has to used with  attributes:

%  me suits zero or more characters and  

_ ( underscore ) me matching precisely one man or woman

Q20. What Type Of Index Will Get Created After Executing The Below Statement?

CREATE INDEX myIndex ON myTable(myColumn)

Non-clustered index. By default a clustered index receives created on the primary key, unless distinct otherwise.

Q21. How To Find Out The Database Name From Sql*plus Command Prompt?

Select * from global_name;

This will give the database name which u r currently related to.

Q22. What Is Group By?

The GROUP BY key phrases has been brought to SQL due to the fact mixture capabilities (like SUM) return the mixture of all column values every time they may be known as. Without the GROUP BY capability, finding the sum for every man or woman organization of column values become not feasible.

Q23. What The Difference Between Union And Union All?

Union will put off the replica rows from the end result set while Union all does'nt.

Q24. What Structure Can You Implement For The Database To Speed Up Table Reads?

Follow the guidelines of DB tuning we must:

properly use indexes ( specific varieties of indexes)

properly locate special DB gadgets throughout exceptional tablespaces, documents and so forth.

Create a special space (tablespace) to find some of the data with special datatype ( for instance CLOB, LOB and …)

Q25. Explain Normalization ?

Normalisation me refining the redundancy and maintain stabilization. There are Four forms of Normalization :first everyday bureaucracy, 2d ordinary bureaucracy, third ordinary forms and fourth Normal paperwork.