Saturday, 1 August 2015

Top SQL Interview Questions

What is Normalization?
In relational database design, the process of organizing data to minimize redundancy is called normalization. Normalization usually involves dividing a database into two or more tables and defining relationships between the tables.

What is Stored Procedure?
Stored procedure is a named group of SQL statements that have been previously created and kept in the server database. Stored procedures can accept input parameters so that a single procedure can be used over the network by several clients using different input parameters.

What is Trigger?
A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE) occurs. Triggers are stored in and managed by the DBMS. A trigger cannot be called or executed. DBMS automatically fires the trigger as a result of a data modification to the associated table.

What is View?
A view is a virtual table and contains columns from different tables. A View does not contain any data; it is a set of queries that are applied to one or more tables that is stored within the database as an object.

What is Index?
An index is a physical structure containing pointers to the data. Indices are created in an existing table to find data more quickly and efficiently. It is possible to create an index on one or more columns of a table. They are just used to speed up queries.

What are the difference between clustered and a non‐clustered index?
A clustered index reorders the way records in the table are physically stored. Therefore table can have only one clustered index.

A non clustered index is special a type of index in which the logical order of the index does not match the physical stored order of the rows on disk. Table can have multiple clustered index.

What is Cursor?
Cursor is a database object used to manipulate data in a set on a row‐by‐row basis.

What is Difference between UDF (User defined function)
·       UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
·       Inline UDF's can be thought of as views that take parameters and can be used in JOINs and other Rowset operations.
·       UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT
What are built-in functions for performing calculations on data?
  •        SQL Scalar functions

SQL scalar functions return a single value, based on the input value.
ü  UCASE() - Converts a field to upper case
ü  LCASE() - Converts a field to lower case
ü  MID() - Extract characters from a text field
ü  LEN() - Returns the length of a text field
ü  ROUND() - Rounds a numeric field to the number of decimals specified
ü  NOW() - Returns the current system date and time
ü  FORMAT() - Formats how a field is to be displayed

  •       SQL Aggregate Functions

SQL aggregate functions return a single value, calculated from values in a column.
ü  AVG() - Returns the average value
ü  COUNT() - Returns the number of rows
ü  FIRST() - Returns the first value
ü  LAST() - Returns the last value
ü  MAX() - Returns the largest value
ü  MIN() - Returns the smallest value
ü  SUM() - Returns the sum

What are different Types of Join?
An SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them.
List of joins:
  •        Inner Join
  •        Left Join
  •        Right Join
  •        Full Join
  •        Cross Join

What is Inner Join?

Inner join retrieve rows that have a match in both joined tables.

What is Outer Join?
A join that includes rows even if they do not have related rows in the joined table is an Outer Join. There are three types of outer join
  •        Left Outer Join: In Left Outer Join all rows in the first‐named table i.e. "left" table, which appears leftmost in the JOIN clause are included. Unmatched rows in the right table do not appear.

  •        Right Outer Join: In Right Outer Join all rows in the second‐named table i.e. "right" table, which appears rightmost in the JOIN clause are included. Unmatched rows in the left table are not included.

  •        Full Outer Join: In Full Outer Join all rows in all joined tables are included, whether they are matched or not.

What is Self Join?
When one table joins to itself is call self Join. A self join can be of any type, as long as the joined tables are the same. A self join is rather unique in that it involves a relationship with only one table.

What is Cross Join?
A cross join that does not have a WHERE clause and hence produces the Cartesian product of the tables involved in the join. The size of a Cartesian product is the number of rows in the first table multiplied by the number of rows in the second table.

What is the difference between Union and Union All?      
The UNION operator is used to combine the result-set of two or more SELECT statements. Each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.The UNION operator selects only distinct values by default. To allow duplicate values, use the ALL keyword with UNION.

What is difference between DELETE & TRUNCATE commands?
  •        The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. Note that this operation will cause all DELETE triggers on the table to fire.
  •        TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUCATE is faster and doesn't use as much undo space as a DELETE.

Can a stored procedure call itself recursively? How much level SP nesting is possible?
Yes, we can write stored procedures that call themselves. Recursion can be defined as a method of problem solving wherein the solution is arrived at by repetitively applying it to subsets of the problem.. You can nest stored procedures up to 32 levels.

What is the difference between a Local and a Global temporary table?
  •        A local temporary table available only for the duration of a connection And if it defined inside a compound statement, for the duration of the compound statement.
  •        A global temporary table remains in the database permanently, but the rows exist only within a given connection. When connection is closed, the data in the global temporary table disappears. However, the table definition remains with the database for access when database is opened next time.

  •        Primary keys are the unique identifiers for each row.
  •        They must contain unique values and cannot be null.
  •        A table can have only one Primary key.

What is UNIQUE KEY constraint?
  •        The UNIQUE constraint uniquely identifies each record in a database table.
  •        The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns.
  •        A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.
  •        Table can have many UNIQUE constraints.

  •        A FOREIGN KEY in one table points to a PRIMARY KEY in another table.
  •        Foreign keys prevent actions that would leave rows with foreign key values when there are no primary keys with that value.
  •        The foreign key constraints are used to enforce referential integrity.

What is CHECK Constraint?
  •        The CHECK constraint is used to limit the value range that can be placed in a column.
  •       If you define a CHECK constraint on a single column it allows only certain values for this column.
  •        If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.

What is NOT NULL Constraint?
  •        NOT NULL constraint enforces a column to NOT accept NULL values.
  •        NOT NULL constraint enforces a field to always contain a value. This means that you cannot insert a new record, or update a record without adding a value to this field.

What are the advantages of using Stored Procedures (SP)?
·       SP’s help code reuse.
  •        SP’s provide better security to your data.
  •        SP’s can reduced network traffic and latency, boosting application performance.
  •        SP’s execution plans can be reused, staying cached in SQL Server's memory, reducing server overhead.
  •        SP’s can encapsulate logic and can be changed without affecting clients.

If it has neither Cluster nor Noncluster Index, what you call it.
It called unindexed table or Heap.

What is BCP? When does it used?
BulkCopy is a tool used to copy huge amount of data from tables and views. BCP does not copy the structures same as source to destination.

What is the default in SQL server databases? What are there uses?
  •       master -The master database holds information for all databases located on the SQL Server instance and is the glue that holds the engine together. Because SQL Server cannot start without a functioning master database, you must administer this database with care.
  •        msdb -The msdb database stores information regarding database backups, SQL Agent information, DTS packages, SQL Server jobs, and some replication information such as for log shipping.
  •        tempdb -The tempdb holds temporary objects such as global and local temporary tables and stored procedures.
  •        model -The model is essentially a template database used in the creation of any new user database created in the instance

What is Filtered Index?
Filtered Index is used to index a portion of rows in a table that means it applies filter on INDEX which improves query performance, reduce index maintenance costs, and reduce index storage costs compared with full‐table indexes. When we see an Index created with some where clause then that is actually a FILTERED INDEX.

What are the authentication modes in SQL Server?
  •        Windows mode
  •        Mixed Mode -SQL & Windows.