Ques.1>What is database? Define candidate key, alternate key, and composite key.
Ans.>A database is a collection of interrelated data that is organized so that its contents can easily be accessed, managed and updated. It is a repository for stored data.
A candidate key is one that can identify each row of a table uniquely and generally they become the primary key of the table. If there is more than one candidate key then one becomes primary key and the rest are considered to be alternate key.
A composite key is one that is formed by combining at least two or more columns of a table.
Ques.2>What is the difference between DELETE TABLE and TRUNCATE TABLE commands?
Ans.>Both the commands results in deleting all rows in the table. TRUNCATE call cannot be rolled back since it is a DDL command and all memory space for that table is released back to the server. It is also much faster since it won’t log the deletion of each row; instead it logs the reallocation of the data pages of the table. Whereas DELETE call is a DML command and can be rolled back. DELETE TABLE is a logged operation, which makes it slow.
Ques.3>What is the difference between a HAVING clause and a WHERE clause?
Ans.>Both specifies a search condition for a group or an aggregate. The difference is that HAVING can be used only with the SELECT statement and used typically in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause. HAVING Clause is basically used only with the GROUP BY function in a query whereas WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.
E.g. Using WHERE clause: SELECT col1, col2 FROM table_name WHERE condition.
E.g. Using HAVING clause: SELECT col1, col2 FROM table_name GROUP BY field_name HAVING condition.
Ques.4>How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?
Ans.>One-to-One relationship can be implemented as a single table or sometimes as two tables with primary and foreign key relationships.
One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships.
Many-to-Many relationships are implemented using a junction table. Using the keys from both the tables forms the composite primary key of the junction table
Ques.5>What is Normalization?
Ans.>It is a set of rules that have been established to aid in the design of tables that are meant to be connected through relationships. This set of rules is known as Normalization. The benefits of normalizing the database include:
a. Avoiding repetitive entries and reducing required storage space..
b.Preventing the need to restructure existing tables to accommodate new data.
c.Increased speed and flexibility of queries, sorts and summaries.
Ques.6>What is a transaction?
Ans.>A transaction is a sequence of operations performed as a single logical unit of work. A logical unit of work must exhibit four properties, called the ACID (Atomicity, Consistency, Isolation and Durability) properties, to qualify as a transaction. A database transaction is a larger unit that frames multiple SQL statements and ensures that the action of the framed statements is atomic with respect to recovery.
Ques.7> What are ACID properties?
Atomicity-A transaction must be an atomic unit of work; either all of its data modifications are performed or none are performed.
Consistency-It must leave all data in a consistent state.
Isolation-Modifications made by concurrent transactions must be isolated from the ones made by any other concurrent transactions.
Durability-After it has completed, its effects are permanently in place in the system. The changes persist even in the event of a system failure.
Ques.8>What is a join and explain different types of joins.
Ans.>Joins are used in queries to explain how different tables are related. And also let us select data from a table depending upon data from another table. Types of joins are: inner join, left outer join and right outer join.
An INNER JOIN-It show matches only when they exist in both tables.
LEFT OUTER JOIN-It display all records in left table of the sql statement
RIGHT OUTER JOIN-It display all records in right table of the sql statement
Ques.9>What are indexes? What is the difference between clustered and non clustered indexes?
Ans.>SQL indexes are similar to the indexes in books, which help the server to retrieve the data quickly.
A clustered index is one that records the way in which records in the table are physically stored. The leaf nodes of a clustered index contain the data pages.
A nonclustered index is one in which the logical order of the index does not match the physical stored order of the rows on disk .In this case the leaf node contains the index rows.
Ques.10>What are the different types of triggers?
Ans.>There are two types of triggers:
INSTEAD OF triggers, which fire in place of the triggering action.Eg.if an INSTEAD OF UPDATE trigger exists on the Sales table and an UPDATE statement is executed against that table, only the INSTEAD OF UPDATE statement will be executed. They can be created on views.
AFTER triggers executes the sql action such as insert, update or delete.It is executed after the Primary and Foreign key are checked.
Ques.11>What are the differences between Stored Procedure (SP) and User Defined Function (UDF)?
Ans.>The major differences are:
a. We cannot change data using UDF and cannot be used in XML FOR clause while everything can be done using stored procedure.
b.UDF does not return output parameters and if there is an error it stops executing while SP`s can return output parameters and ignores the error and moves to the next statement.
c.UDF cannot make permanent changes to server environments while SP`s can.
Ques.12>What is Denormalization?
Ans.>Denormalization is the process of putting one fact in numerous places. Only one valid reason exists for denormalizing a relational design-that is to enhance performance or if we are doing data warehousing and data mining. The sacrifice to performance is that we can increase redundancy in database.