DOT NET SOLUTIONS: SQL Interview Questions

1. What is DBMS?

Database management system is a collection of programs that enables user to store, retrieve, update and delete information from a database.

2. What is RDBMS?

Relational Database Management system (RDBMS) is a database management system (DBMS) that is based on the relational model. Data from relational database can be accessed or reassembled in many different ways without having to reorganize the database tables . Data from relational database can be accessed using a API , Structured Query Language (SQL)

3. What is SQL?

Structured Query Language(SQL) is a language designed specifically for communicating with databases. SQL is an ANSI (American National Standards Institute) standard.

4. What is the different type of Sql's?

Frequently asked SQL Interview Questions

1. DDL – Data Definition Language

DDL is used to define the structure that holds the data. For example: table

2. DML – Data Manipulation Language

DML is used for manipulation of the data itself. Typical operations are Insert, Delete, Update and retrieving the data from the table

3. DCL – Data Control Language

DCL is used to control the visibility of data like granting database access and set privileges to create table etc.

5. What are the Advantages of SQL?

1. SQL is not a proprietary language used by specific database vendors. Almost every major DBMS supports SQL, so learning this one language will enable programmer to interact with any database like ORACLE, SQL, MYSQL etc.

2. SQL is easy to learn. The statements are all made up of descriptive English words, and there aren't that many of them.

3. SQL is actually a very powerful language and by using its language elements you can perform very complex and sophisticated database operations.

6. What is a field in a database?

A field is an area within a record reserved for a specific piece of data. Examples: Employee Name, Employee ID etc

7. What is a Record in a database?

A record is the collection of values / fields of a specific entity: i.e. a Employee , Salary etc

8. What is a Table in a database?

A table is a collection of records of a specific type. For example, employee table, salary table etc.

SQL Interview Questions and answers on Database Transactions

9. What is a database transaction?

Frequently asked SQL Interview Questions,

Database transaction take DB from one consistent state to another. At the end of the transaction the system must be in the prior state if transaction fails or the status of the system should reflect the successful completion if the transaction goes through.

10. What are properties of a transaction?

Frequently asked SQL Interview Questions
Properties of the transaction can be summarized as ACID Properties.

1. Atomicity

In this, a transaction consists of many steps. When all the steps in the transaction go completed it get reflected in DB or if any step fails, all the transactions are rolled back.

2. Consistency

The database will move from one consistent state to another if the transaction succeeds and remain in the original state if the transaction fails.

3. Isolation

Every transaction should operate as if it is the only transaction in the system

4. Durability

Once a transaction has completed successfully, the updated rows/records must be available for all other transactions on a permanent basis

11. What is a Database Lock?

Database lock tell a transaction if the data item in questions is currently being used by other transactions.

12. What are the type of locks?

1. Shared Lock

When a shared lock is applied on data item, other transactions can only read the item, but can’t write into it.

2. Exclusive Lock

When a exclusive lock is applied on data item, other transactions can’t read or write into the data item.

SQL Interview Questions and answers on Database Normalization

13. What are the different type of normalization?

Frequently asked SQL Interview Questions

In database design , we start with one single table, with all possible columns. Lot of redundant data would be present since it’s a single table. The process of removing the redundant data, by splitting up the table in a well defined fashion is called normalization.

1. First Normal Form (1NF)

A relation is said to be in first normal form if and only if all underlying domains contain atomic values only. After 1NF, we can still have redundant data

2. Second Normal Form (2NF)

A relation is said to be in 2NF if and only if it is in 1NF and every non key attribute is fully dependent on the primary key. After 2NF, we can still have redundant data

3. Third Normal Form (3NF)

A relation is said to be in 3NF if and only if it is in 2NF and every non key attribute is non-transitively dependent on the primary key

SQL Interview Questions and answers on Database Keys and Constraints

14. What is a primary key?

Frequently asked SQL Interview Questions

A primary key is a column whose values uniquely identify every row in a table. Primary key values can never be reused. If a row is deleted from the table, its primary key may not be assigned to any new rows in the future. In the examples given below “Employee_ID” field is the primary key.

1. To define a field as primary key, following conditions had to be met:

2. No two rows can have the same primary key value.

3. Every row must have a primary key value

4. Primary key field cannot be null

5. Values in primary key columns can never be modified or updated

15. What is a SQL Composite Primary Key

A Composite primary key is a set of columns whose values uniquely identify every row in a table. For example, in the table given above , if "Employee_ID" and "Employee Name" together uniquely identifies a row it’s called a Composite Primary Key . In this case, both the columns will be represented as primary key.

16. What is a Composite Primary Key ?

A Composite primary key is a set of columns whose values uniquely identify every row in a table. For example , in the table given above , if "Employee_ID" and "Employee Name" together uniquely identifies a row its called a Composite Primary Key . In this case , both the columns will be represented as primary key .

17. What is a Foreign Key?

Frequently asked SQL Interview Questions

When a "one" table's primary key field is added to a related "many" table in order to create the common field which relates the two tables, it is called a foreign key in the "many" table. In the example given below, salary of an employee is stored in salary table. Relation is established via foreign key column “Employee_ID_Ref” which refers “Employee_ID” field in Employee table.

18. What is a Unique Key?

Unique key is same as primary with difference being existence of null. Unique key field allows one value as NULL value

SQL Interview Questions and answers on SQL Commands

19. Define SQL Insert Statement?

Frequently asked SQL Interview Questions

SQL INSERT statement is used to add rows to a table. For a full row insert , SQL Query should start with “insert into “ statement followed by table name and values command followed by the values that need to be inserted into the table. Insert can be used in several ways:

1. To insert a single complete row

2. To insert a single partial row

20. Define SQL Update Statement?

Frequently asked SQL Interview Questions

SQL Update is used to update data in a row or set of rows specified in the filter condition.

The basic format of an SQL UPDATE statement is, Update command followed by table to be updated and SET command followed by column names and their new values followed by filter condition that determines which rows should be updated

21. Define SQL Delete Statement?

SQL Delete is used to delete a row or set of rows specified in the filter condition .The basic format of an SQL DELETE statement is,DELETE FROM command followed by table name followed by filter condition that determines which rows should be updated

22. What are wild cards used in database for Pattern Matching?

Frequently asked SQL Interview Questions

SQL 'Like' search takes more time to process. So before using like operator following tips should be considered.
Don't overuse wild cards. If another search operator will do, use it instead.
When you do use wild cards, try to not use them at the beginning of the search pattern unless absolutely necessary. Search patterns that begin with wild cards are the slowest to process.
Pay careful attention to the placement of the wild card symbols. If they are misplaced, you might not return the data you intended

SQL Interview Questions and answers on SQL Joins

23. Define Join and explain different type of joins?

Frequently asked SQL Interview Questions

In order to avoid data duplication, data is stored in related tables. Join keyword is used to fetch data from related table. Join return rows when there is at least one match in both tables. Type of joins are

Right Join

Return all rows from the right table, even if there are no matches in the left table.

Outer Join

Left Join

Return all rows from the left table, even if there are no matches in the right table .

Full Join

Return rows when there is a match in one of the tables.

24. What is Self-Join?

Self-join is query used to join a table to itself. Aliases should be used for the same table comparison.

25. What is Cross Join?

Cross Join will return all records where each row from the first table is combined with each row from the second table.

SQL Interview Questions and answers on Database Views

26. What is a view?

Frequently asked SQL Interview Questions

Views are virtual tables. Unlike tables that contain data, views simply contain queries that dynamically retrieve data when used.

27. What is a materialized view?

Materialized views are also a view but are disk based. Materialized views get updated on specific duration, base upon the interval specified in the query definition. We can index materialized view.

28. What are the advantages and disadvantages of views in a database?

Advantages:

1. Views doesn't store data in a physical location.

2. View can be use to hide some of the columns from the table

3. Views can provide Access Restriction, since data insertion, update and deletion is not possible on the view.

Disadvantages:

1. When a table is dropped, associated view become irrelevant.

2. Since view are created when a query requesting data from view is triggered, its bit slow

3. When views are created for large tables, it occupies more memory.

SQL Interview Questions and answers on Stored Procedures and Triggers

29. What is a stored procedure?

Frequently asked SQL Interview Questions

Stored Procedure is a function which contain collection of SQL Queries. Procedure can take inputs, process them and send back output.

30. What are the advantages a stored procedure?

Stored Procedures are precompiled and stored in database. This enable the database to execute the queries much faster. Since many queries can be included in a stored procedure, round trip time to execute multiple queries from source code to database and back is avoided.

31. What is a trigger?

Database are set of commands that get executed when an event(Before Insert, AfterInsert, OnUpdate, On delete of a row) occurs on a table, views.

32. Explain the difference between DELETE, TRUNCATE and DROP commands?

Once delete operation is performed Commit and Rollback can be performed to retrieve data. But after truncate statement, Commit and Rollback statement can’t be performed. Where condition can be used along with delete statement but it can’t be used with truncate statement. Drop command is used to drop the table or keys like primary, foreign from a table.

33. What is the difference between Cluster and Non cluster Index?

A clustered index reorders the way records in the table are physically stored. There can be only one clustered index per table. It make data retrieval faster. A non clustered index does not alter the way it was stored but creates a complete separate object within the table. As a result insert and update command will be faster.

34. What is Union, minus and INTERSECT commands?

MINUS operator is used to return rows from the first query but not from the second query. INTERSECT operator is used to return rows returned by both the queries.

What are DMVs?

Dynamic management views (DMVs) and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance; that is, they let you see what is going on inside SQL Server. They were introduced in SQL Server 2005 as an alternative to system tables. One example is viewing operating system wait statistics via this query:

SELECT * FROM sys.dm_os_wait_stats;

Another example is examining current sessions, much like the sp_who2 command:

SELECT * FROM sys.dm_exec_sessions;

What are temp tables? What is the difference between global and local temp tables?

Temporary tables are temporary storage structures. You may use temporary tables as buckets to store data that you will manipulate before arriving at a final format. The hash (#) character is used to declare a temporary table as it is prepended to the table name. A single hash (#) specifies a local temporary table.

CREATE TABLE #tempLocal( nameidint, fnamevarchar(50), lnamevarchar(50) )

Local temporary tables are available to the current connection for the user, so they disappear when the user disconnects.

Global temporary tables may be created with double hashes (##). These are available to all users via all connections, and they are deleted only when all connections are closed.

CREATE TABLE ##tempGlobal( nameidint, fnamevarchar(50), lnamevarchar(50) )

Once created, these tables are used just like permanent tables; they should be deleted when you are finished with them. Within SQL Server, temporary tables are stored in the Temporary Tables folder of the tempdb database.

How are transactions used?

Transactions allow you to group SQL commands into a single unit. The transaction begins with a certain task and ends when all tasks within it are complete. The transaction completes successfully only if all commands within it complete successfully. The whole thing fails if one command fails. The BEGIN TRANSACTION, ROLLBACK TRANSACTION, and COMMIT TRANSACTION statements are used to work with transactions. A group of tasks starts with the begin statement. If any problems occur, the rollback command is executed to abort. If everything goes well, all commands are permanently executed via the commit statement.

What is the difference between a clustered and a nonclustered index?

A nonclustered index does not affect the way data is physically stored; it creates a new object for the index and stores the column(s) designated for indexing with a pointer back to the row containing the indexed values.

You can think of a clustered index as a dictionary in alphabetical order, and a nonclustered index as a book's index.

What are DBCC commands?

DBCC CHECKALLOC -- Check disk allocation consistency.

DBCC OPENTRAN -- Display information about recent transactions.

DBCC HELP -- Display Help for DBCC commands.

What is the difference between truncate and delete?

Truncate is a quick way to empty a table. It removes everything without logging each row. Truncate will fail if there are foreign key relationships on the table. Conversely, the delete command removes rows from a table, while logging each deletion and triggering any delete triggers that may be present.

What does the NOLOCK query hint do?

Table hints allow you to override the default behaviour of the query optimizer for statements. They are specified in the FROM clause of the statement. While overriding the query optimizer is not always suggested, it can be useful when many users or processes are touching data. The NOLOCK query hint is a good example because it allows you to read data regardless of who else is working with the data; that is, it allows a dirty read of data -- you read data no matter if other users are manipulating it. A hint like NOLOCK increases concurrency with large data stores.

SELECT * FROM table_name (NOLOCK)

Microsoft advises against using NOLOCK, as it is being replaced by the READUNCOMMITTED query hint. There are lots more query hints with plenty of information online.

What is a CTE?

A common table expression (CTE) is a temporary named result set that can be used within other statements like SELECT, INSERT, UPDATE, and DELETE. It is not stored as an object and its lifetime is limited to the query. It is defined using the WITH statement as the following example shows:

WITH ExampleCTE (id, fname, lname)

(

SELECT id, firstname, lastname FROM table

)

SELECT * FROM ExampleCTE

A CTE can be used in place of a view in some instances.

What is a view? What is the WITH CHECK OPTION clause for a view?

A view is a virtual table that consists of fields from one or more real tables. Views are often used to join multiple tables or to control access to the underlying tables.

The WITH CHECK OPTION for a view prevents data modifications (to the data) that do not confirm to the WHERE clause of the view definition. This allows data to be updated via the view, but only if it belongs in the view.

What is a query execution plan?

SQL Server has an optimizer that usually does a great job of optimizing code for the most effective execution. A query execution plan is the breakdown of how the optimizer will run (or ran) a query. There are several ways to view a query execution plan. This includes using the Show Execution Plan option within Query Analyzer; Display Estimated Execution Plan on the query dropdown menu; or use the SET SHOWPLAN_TEXT ON command before running a query and capturing the execution plan event in a SQL Server Profiler trace.

What does the SQL Server Agent Windows service do?

SQL Server Agent is a Windows service that handles scheduled tasks within the SQL Server environment (aka jobs). The jobs are stored/defined within SQL Server, and they contain one or more steps that define what happens when the job runs. These jobs may run on demand, as well as via a trigger or predefined schedule. This service is very important when determining why a certain job did not run as planned -- often it is as simple as the SQL Server Agent service not running.

What is the default port number for SQL Server?

If enabled, the default instance of Microsoft SQL Server listens on TCP port 1433. Named instances are configured for dynamic ports, so an available port is chosen when SQL Server starts. When connecting to a named instance through a firewall, configure the Database Engine to listen on a specific port, so that the appropriate port can be opened in the firewall.

1.What is a "trigger"?

Microsoft SQL Server includes support for a special type of stored procedure called a trigger. A trigger is a stored procedure that executes whenever an update, delete or insert statement is executed against a table or a view. Triggers are created in order to enforce integrity rules in a database. In other words, you can associate a trigger with a table in such a way that it fires whenever a change is made to the contents of the table. Basically, trigger is a set of SQL statements that execute in response to a data modification/retrieval event on a table. A trigger is a solution to the restrictions of a constraint. For instance :

· A database column cannot carry PSEUDO columns as criteria where a trigger can.

· A database constraint cannot refer old and new values for a row where a trigger can.

Also, other than table triggers, there are also schema and database triggers. These can be made to fire when new objects are created, when a user logs in, when the database shutdown etc. Table level triggers can be classified into row and statement level triggers and those can be further broken down into before and after triggers.

· In SQL Server 6.5 you could define only 3 triggers per table, one for INSERT, one for UPDATE and one for DELETE.

· From SQL Server 7.0 onwards, this restriction is gone, and you could create multiple triggers per each action. But in 7.0 there’s no way to control the order in which the triggers fire.

· In SQL Server 2000 you could specify which trigger fires first or fires last using sp_settriggerorder. Triggers can’t be invoked on demand. They get triggered only when an associated action (INSERT, UPDATE, and DELETE) happens on the table on which they are defined.

Triggers are generally used to implement business rules, auditing. Triggers can also be used to extend the referential integrity checks, but wherever possible, use constraints for this purpose, instead of triggers, as constraints are much faster. Till SQL Server 7.0, triggers fire only after the data modification operation happens. So in a way, they are called post triggers. But in SQL Server 2000 you could create pre triggers also.

What is "index covering" of a query?

A non-clustered index that includes (or covers) all columns used in a query is called a covering index. When SQL server can use a non-clustered index to resolve the query, it will prefer to scan the index rather than the table, which typically takes fewer data pages. If your query uses only columns included in the index, then SQL server may scan this index to produce the desired output.

What types of join algorithms can you have?

You can dynamically relate different tables by applying what's known as a join. Technically, a join is the operation used when selecting data to create these relationships at retrieval time. What that means to you is that a join determines which records are selected and which aren't. In this article, we'll introduce you to several types of joins supported by SQL and show you how to correctly apply them to get the data you need.

What that means is that a join is conditional—similar to a WHERE clause or criteria expression—in that the join specifies which records (rows) are selected in both tables. Certainly, the type of join significantly impacts which records are retrieved or acted upon. For the most part, a join can be specified in a FROM or WHERE clause, so be careful where you specify them. Because the data engine executes the clauses in a specific sequence, placement can affect the results.

Generally, the data engine fulfills the FROM clause before applying any other clauses. Adding the join here eliminates records before they're evaluated by any other clauses. As a rule, you'll find this is the recommended method.

Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table. Types of joins: INNER JOIN, OUTER JOIN, and CROSS JOIN. OUTER JOIN’s are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.

What is a SQL View?

A view can be thought of as either a virtual table or a stored query. The data accessible through a view is not stored in the database as a distinct object. What is stored in the database is a SELECT statement. The result set of the SELECT statement forms the virtual table returned by the view. A user can use this virtual table by referencing the view name in Transact-SQL statements the same way a table is referenced. A view is used to do any or all of these functions:

· Restrict a user to specific rows in a table. For example, allow an employee to see only the rows recording his or her work in a labor-tracking table.

· Restrict a user to specific columns. For example, allow employees who do not work in payroll to see the name, office, work phone, and department columns in an employee table, but do not allow them to see any columns with salary information or personal information.

· Join columns from multiple tables so that they look like a single table.

· Aggregate information instead of supplying details. For example, present the sum of a column, or the maximum or minimum value from a column.

Views are created by defining the SELECT statement that retrieves the data to be presented by the view. The data tables referenced by the SELECT statement are known as the base tables for the view. In this example, titleview in the pubs database is a view that selects data from three base tables to present a virtual table of commonly needed data:

CREATE VIEW titleview

SELECT title, au_ord, au_lname, price, ytd_sales, pub_id

FROM authors AS a

JOIN titleauthor AS ta ON (a.au_id = ta.au_id)

JOIN titles AS t ON (t.title_id = ta.title_id)

You can then reference titleview in statements in the same way you would reference a table:

SELECT *

FROM titleview

A view can reference another view. For example, titleview presents information that is useful for managers, but a company typically discloses year-to-date figures only in quarterly or annual financial statements. A view can be built that selects all the titleview columns except au_ord and ytd_sales. This new view can be used by customers to get lists of available books without seeing the financial information:

CREATE VIEW Cust_titleview

SELECT title, au_lname, price, pub_id

FROM titleview

Views in all versions of SQL Server are updatable (can be the target of UPDATE, DELETE, or INSERT statements), as long as the modification affects only one of the base tables referenced by the view, for example:

-- Increase the prices for publisher '0736' by 10%.

UPDATE titleview

SET price = price * 1.10

WHERE pub_id = '0736'

SQL Server 2000 supports more complex types of INSERT, UPDATE, and DELETE statements that reference views. INSTEAD OF triggers can be defined on a view to specify the individual updates that must be performed against the base tables to support the INSERT, UPDATE, or DELETE statement. Also, partitioned views support INSERT, UPDATE, and DELETE statements that modify multiple member tables referenced by the view.

Indexed views are a SQL Server 2000 feature that greatly improves the performance of complex views of the type usually found in data warehouses or other decision support systems.

Views are called virtual tables because the result set of a view is not usually saved in the database. The result set for a view is dynamically incorporated into the logic of the statement and the result set is built dynamically at run time.

What is a Primary Key?

Primary Key is a unique column in the table that identifies each row. There cannot be more than 1 row in the table with the same primary key. The primary key contains a unique identifier to maintain each record's unique identity. Primary keys field can include an employee ID, part number, or customer number. Typically, you specify which column contains the primary key when you create a database table.

Define candidate key, alternate key and composite key.

A candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys. A key formed by combining at least two or more columns is called composite key.

What’s the difference between a primary key and a unique key?

Both primary key and unique key enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, whereas unique key creates a non-clustered index by default. Another difference is that, primary key doesn’t allow NULL, but unique key allows one NULL only.

What is a "constraint"?

A constraint allows you to apply simple referential integrity checks to a table. There are four primary types of constraints that are currently supported by SQL Server:

· PRIMARY/UNIQUE - enforces uniqueness of a particular table column.

· DEFAULT - specifies a default value for a column in case an insert operation does not provide one.

· FOREIGN KEY - validates that every value in a column exists in a column of another table.

· CHECK - checks that every value stored in a column is in some specified list

Each type of constraint performs a specific type of action. There are five kinds of constraints in all, Primary key, Foreign key, Unique, Check and Default.

What action do you have to perform before retrieving data from the next result set of a

stored procedure?

Move the cursor down one row from its current position. A Result Set cursor is initially positioned before the first row. Before you can get to the first row, you would need to Move the cursor down by one row.

With certain database systems, a stored procedure can return multiple result sets, multiple update counts, or some combination of both. Also, if you are providing a user with the ability to enter any SQL statement, you don't know if you are going to get a ResultSet or an update count back from each statement, without analyzing the contents. The Statement.execute() method helps in these cases. Method Statement.execute() returns a Boolean to tell you the type of response:

· true indicates next result is a ResultSet. Use Statement.getResultSet to get the ResultSet

· false indicates next result is an update count. Use Statement.getUpdateCount to get the update count

· false also indicates no more results. Update count is -1 when no more results (usually 0 or positive)

After processing each response, you use Statement.getMoreResults to check for more results, again returning a Boolean. The following demonstrates the processing of multiple result sets:

boolean result = stmt.execute(" ... ");

intupdateCount = stmt.getUpdateCount();

while (result || (updateCount != -1)) {

if(result) {

ResultSet r = stmt.getResultSet();

// process result set

} else if(updateCount != -1) {

// process update count

}

result = stmt.getMoreResults();

updateCount = stmt.getUpdateCount(); }

Briefly explain about Database.

A collection of information organized in such a way that a computer program can quickly select desired pieces of data. You can think of a database as an electronic filing system. Traditional databases are organized by fields, records, and files. A field is a single piece of information; a record is one complete set of fields; and a file is a collection of records. For example, a telephone book is analogous to a file. It contains a list of records, each of which consists of three fields: name, address, and telephone number.

An alternative concept in database design is known as Hypertext. In a Hypertext database, any object, whether it be a piece of text, a picture, or a film, can be linked to any other object. Hypertext databases are particularly useful for organizing large amounts of disparate information, but they are not designed for numerical analysis.

To access information from a database, you need a database management system (DBMS). This is a collection of programs that enables you to enter, organize, and select data in a database.

Briefly explain about DBMS.

A collection of programs that enables you to store, modify, and extract information from a database. There are many different types of DBMSs, ranging from small systems that run on personal computers to huge systems that run on mainframes. The following are examples of database applications:

· computerized library systems

· automated teller machines

· flight reservation systems

· computerized parts inventory systems

From a technical standpoint, DBMSs can differ widely. The terms relational, network, flat, and hierarchical all refer to the way a DBMS organizes information internally. The internal organization can affect how quickly and flexibly you can extract information.

Requests for information from a database are made in the form of a query, which is a stylized question. For example, the query

SELECT ALL WHERE NAME = "SMITH" AND AGE > 35

requests all records in which the NAME field is SMITH and the AGE field is greater than 35. The set of rules for constructing queries is known as a query language. Different DBMSs support different query languages, although there is a semi-standardized query language called SQL(structured query language). Sophisticated languages for managing database systems are called fourth-generation languages, or 4GLs for short.

The information from a database can be presented in a variety of formats. Most DBMSs include a report writerprogram that enables you to output data in the form of a report. Many DBMSs also include a graphics component that enables you to output information in the form of graphs and charts.

Briefly explain about RDBMS.

Short for relational database management system and pronounced as separate letters, a type of database management system (DBMS) that stores data in the form of related tables. Relational databases are powerful because they require few assumptions about how data is related or how it will be extracted from the database. As a result, the same database can be viewed in many different ways.

An important feature of relational systems is that a single database can be spread across several tables. This differs from flat-file databases, in which each database is self-contained in a single table. Almost all full-scale database systems are RDBMS's. Small database systems, however, use other designs that provide less flexibility in posing queries.

What is normalization? Explain different levels of normalization?

In a relational database design, the process of organizing data to minimize redundancy is termed as Normalization. Normalization usually involves dividing a database into two or more tables and defining relationships between the tables. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.

Normalization can be viewed as a series of steps (i.e., levels) designed, one after another, to deal with ways in which tables can be "too complicated for their own good". The purpose of normalization is to reduce the chances for anomalies to occur in a database. The definitions of the various levels of normalization illustrate complications to be eliminated in order to reduce the chances of anomalies. At all levels and in every case of a table with a complication, the resolution of the problem turns out to be the establishment of two or more simpler tables which, as a group, contain the same information as the original table but which, because of their simpler individual structures, lack the complication.

There are three main normal forms, each with increasing levels of normalization:

· First Normal Form (1NF): We must eliminate repeating groups. That means any subgroups of data that appear within the record should be spilt into separate tables. Each field in a table contains different information. For example, in an employee list, each table would contain only one birthdate field.

· Second Normal Form (2NF): By eliminating partial dependencies. Partial dependencies means that data does not depend on the primary key of the table to uniquely identify it. Each field in a table that is not a determiner of the contents of another field must itself be a function of the other fields in the table.

· Third Normal Form (3NF): Eliminate all transitive (i.e. hidden) dependencies. In other words every column that isn’t part of the key must depend on the key for its information al value. No duplicate information is permitted. So, for example, if two tables both require a birthdate field, the birthdate information would be separated into a separate table, and the two other tables would then access the birthdate information via an index field in the birthdate table. Any change to a birthdate would automatically be reflecting in all tables that link to the birthdate table.

There are additional normalization levels, such as Boyce Codd Normal Form (BCNF), fourth normal form (4NF) and fifth normal form (5NF). While normalization makes databases more efficient to maintain, they can also make them more complex because data is separated into so many different tables.

· Boyce-Codd Normal Form (BCNF): A table is in BCNF if it is in 3NF and if every determinant is a candidate key.

· 4th Normal Form (4NF): A table is in 4NF if it is in BCNF and if it has no multi-valued dependencies.

· 5th Normal Form (5NF): A table is in 5NF, also called "Projection-Join Normal Form" (PJNF), if it is in 4NF and if every join dependency in the table is a consequence of the candidate keys of the table.

· Domain-Key Normal Form (DKNF): A table is in DKNF if every constraint on the table is a logical consequence of the definition of keys and domains.

What is denormalization and when would you go for it?

As the name indicates, denormalization is the reverse process of normalization. It's the controlled introduction of redundancy in to the database design. It helps improve the query performance as the number of joins could be reduced.

What is an index? What are the types of indexes? How many clustered indexes can be

created on a table? I created a separate index on each column of a table.

What are the advantages and disadvantages of this approach?

Indexes in SQL Server are similar to the indexes in books. They help SQL Server retrieve the data quicker. Indexes are of two types. Clustered indexes and non-clustered indexes. When you create a 11index on a table, all the rows in the table are stored in the order of the clustered index key. So, there can be only one clustered index per table. Non-clustered indexes have their own storage separate from the table data storage. Non-clustered indexes are stored as B-tree structures (so do clustered indexes), with the leaf level nodes having the index key and its row locater. The row located could be the RID or the Clustered index key, depending up on the absence or presence of clustered index on the table.

If you create an index on each column of a table, it improves the query performance, as the query optimizer can choose from all the existing indexes to come up with an efficient execution plan. At the same time, data modification operations (such as INSERT, UPDATE, DELETE) will become slow, as every time data changes in the table, all the indexes need to be updated. Another disadvantage is that, indexes need disk space, the more indexes you have, more disk space is used.

What is RAID and what are different types of RAID configurations?

Note: RAID is available only on Microsoft Windows NT 4.0 and Microsoft Windows 2000.

RAID (redundant array of independent disks) is a disk system that comprises multiple disk drives (an array) to provide higher performance, reliability, storage capacity, and lower cost. Fault-tolerant arrays are categorized in six RAID levels, 0 through 5. Each level uses a different algorithm to implement fault tolerance.

Although RAID is not a part of Microsoft® SQL Server™ 2000, its implementation can directly affect the way SQL Server performs. RAID levels 0, 1, and 5 are typically used with SQL Server.

A hardware disk array improves I/O performance because I/O functions, such as striping and mirroring, are handled efficiently in firmware. Conversely, an operating system–based RAID offers lower cost but consumes processor cycles. When cost is a consideration and redundancy and high performance are required, Microsoft Windows® NT® stripe sets with parity or Windows 2000 RAID-5 volumes are a good solution.

Data striping (RAID 0) is the RAID configuration with the highest performance, but if one disk fails, all the data on the stripe set becomes inaccessible. A common installation technique for relational database management systems is to configure the database on a RAID 0 drive and then place the transaction log on a mirrored drive (RAID 1). You can get the best disk I/O performance for the database and maintain data recoverability (assuming you perform regular database backups) through a mirrored transaction log.

If data must be quickly recoverable, consider mirroring the transaction log and placing the database on a RAID 5 disk. RAID 5 provides redundancy of all data on the array, allowing a single disk to fail and be replaced in most cases without system downtime. RAID 5 offers lower performance than RAID 0 or RAID 1 but higher reliability and faster recovery.

What are the steps you will take to improve performance of a poor performing query?

There could be a lot of reasons behind the poor performance of a query.

But some general issues to talk about would be:

No indexes, table scans, missing or out of date statistics, blocking, excess recompilations of stored procedures, procedures and triggers without SET NOCOUNT ON, poorly written query with unnecessarily complicated joins, too much normalization, excess usage of cursors and temporary tables.

Some of the tools/ways that help you troubleshooting performance problems are:

SET SHOWPLAN_ALL ON, SET SHOWPLAN_TEXT ON, SET STATISTICS IO ON, SQL Server Profiler, Windows NT /2000 Performance monitor, Graphical execution plan in Query Analyzer.

Write down the general syntax for a SELECT statement covering all the options.

SELECT select_list

[INTO new_table_]

FROM table_source

[WHERE search_condition]

[GROUP BY group_by_expression]

[HAVING search_condition]

[ORDER BY order_expression [ASC | DESC] ]

What is a self join? Explain it with an example.

Self-joins are useful in SQL select statements and are often used to identify duplicate entries or entries linked in other ways such as customers who share the same telephone number or patients who share the same address. A self-join is an internal table (or view) join. The self-join is between fields in a table and fields within a virtual copy of the table.

Self join is just like any other join, except that two instances of the same table will be joined in the query. Here is an example: Employees table which contains rows for normal employees as well as managers. So, to find out the managers of all the employees, you need a self join.

CREATE TABLE emp

(

empidint,

mgridint,

empname char(10)

)

INSERT emp SELECT 1,2,'Vyas'

INSERT emp SELECT 2,3,'Mohan'

INSERT emp SELECT 3,NULL,'Shobha'

INSERT emp SELECT 4,2,'Shridhar'

INSERT emp SELECT 5,2,'Sourabh'

SELECT t1.empname [Employee], t2.empname [Manager]

FROM emp t1, emp t2

WHERE t1.mgrid = t2.empid

What is the difference between GROUP BY and DISTINCT?

GROUP BY divides a table into groups. Groups can consist of column names or results or computed columns. GROUP BY was added to SQL because aggregate functions (like SUM) return the aggregate of all column values every time they are called, and without the GROUP BY function it was impossible to find the sum for each individual group of column values.

This "Sales" Table:

Company	Amount
W3Schools	5500
IBM	4500
W3Schools	7100

And This SQL: SELECT Company, SUM (Amount) FROM Sales

Returns this result:

Company	SUM(Amount)
W3Schools	17100
IBM	17100
W3Schools	17100

The above code is invalid because the column returned is not part of an aggregate. A GROUP BY clause will solve this problem:

SELECT Company, SUM (Amount) FROM Sales

GROUP BY Company

Returns this result:

Company	SUM(Amount)
W3Schools	12600
IBM	4500

DISTINCT: The SELECT keyword allows us to grab all information from a column (or columns) on a table. This, of course, necessarily means that there will be redundancies. What if we only want to select each DISTINCT element? This is easy to accomplish in SQL. All we need to do is to add DISTINCT after SELECT.

Example: Select Companies from Order Table:Simple Table of Purchase Orders:

Company	OrderNumber
Sega	3412
DeveloperIQ	2312
Trio	4678
DeveloperIQ	6798

This SQL statement: SELECT Company FROM Orders

Will return this result:

Company

Sega

DeveloperIQ

Trio

DeveloperIQ

Note that the company DeveloperIQ is listed twice in the result. Sometimes we don't want that.

Example: Select Distinct Companies from Orders

This SQL statement: SELECT DISTINCT Company FROM Orders

Will return this result:

Company

Sega

DeveloperIQ

Trio

In SQL query, what is @@ERROR used for?

It is used to track the error in Stored Procedure and Triggers. For example if @@ERROR = NULL, it means there is no error in Stored Procedure and Triggers.

When an error is encountered within a stored procedure, the best you can do (assuming it’s a non-fatal error) is halt the sequential processing of the code and either branch to another code segment in the procedure or return processing to the calling application. Notice that the previous sentence is specific to non-fatal errors. There are two types of errors in SQL Server: fatal and non-fatal. Fatal errors cause a procedure to abort processing and terminate the connection with the client application. Non-fatal errors do not abort processing a procedure or affect the connection with the client application. When a non-fatal error occurs within a procedure, processing continues on the line of code that follows the one that caused the error.

You might be wondering what actions cause fatal errors. Unfortunately, the actions that cause a fatal error are not well documented. Each error has an associated severity level that is a value between 0–25. The errors with a severity level of 20 or above are all fatal, but once you get below this value there is no well-defined rule as to which errors are fatal. In truth, though, worrying about which errors are fatal is a bit useless because there is no code you can implement that will allow you to handle them gracefully. Of course, you can use pro-active coding to make sure fatal-errors do not occur. For example, if your application allows users to type in the name of the table on which a query is based you can verify its existence before referencing it with dynamic SQL.

@@ERROR

The @@ERROR system function is used to implement error handling code. It contains the error ID produced by the last SQL statement executed during a client’s connection. When a statement executes successfully, @@ERROR contains 0. To determine if a statement executes successfully, an IF statement is used to check the value of the function immediately after the target statement executes. It is imperative that @@ERROR be checked immediately after the target statement, because its value is reset when the next statement executes successfully.

RAISERROR

The RAISERROR statement is used to produce an ad hoc error message or to retrieve a custom message that is stored in the sysmessages table. You can use this statement with the error handling code presented in the previous section to implement custom error messages in your applications.

Adding a Permanent Custom Message

If you have a message that is going to be used frequently, it is more efficient to add it to the sysmessages table and reference it by its unique ID. The system stored procedure sp_addmessages adds an error message to sysmessages.

1. What are the basic functions for master, msdb, model, tempdb and resource databases?

The master database holds information for all databases located on the SQL Server instance and is theglue that holds the engine together. Because SQL Server cannot start without a functioning masterdatabase, you must administer this database with care.
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.
The tempdb holds temporary objects such as global and local temporary tables and stored procedures.
The model is essentially a template database used in the creation of any new user database created in the instance.
The resoure Database is a read-only database that contains all the system objects that are included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata.

2. What is Service Broker?

Service Broker is a message-queuing technology in SQL Server that allows developers to integrate SQL Server fully into distributed applications. Service Broker is feature which provides facility to SQL Server to send an asynchronous, transactional message. it allows a database to send a message to another database without waiting for the response, so the application will continue to function if the remote database is temporarily unavailable.

3. Where SQL server user names and passwords are stored in SQL server?

They get stored in System Catalog Views sys.server_principals and sys.sql_logins.

4. What is Policy Management?

Policy Management in SQL SERVER 2008 allows you to define and enforce policies for configuring and managing SQL Server across the enterprise. Policy-Based Management is configured in SQL Server Management Studio (SSMS). Navigate to the Object Explorer and expand the Management node and the Policy Management node; you will see the Policies, Conditions, and Facets nodes.

5. What is Replication and Database Mirroring?

Database mirroring can be used with replication to provide availability for the publication database. Database mirroring involves two copies of a single database that typically reside on different computers. At any given time, only one copy of the database is currently available to clients which are known as the principal database. Updates made by clients to the principal database are applied on the other copy of the database, known as the mirror database. Mirroring involves applying the transaction log from every insertion, update, or deletion made on the principal database onto the mirror database.

6. What are Sparse Columns?

A sparse column is another tool used to reduce the amount of physical storage used in a database. They are the ordinary columns that have an optimized storage for null values. Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve nonnull values.

7. What does TOP Operator Do?

The TOP operator is used to specify the number of rows to be returned by a query. The TOP operator has new addition in SQL SERVER 2008 that it accepts variables as well as literal values and can be used with INSERT, UPDATE, and DELETES statements.

8. What is CTE?

CTE is an abbreviation Common Table Expression. A Common Table Expression (CTE) is an expression that can be thought of as a temporary result set which is defined within the execution of a single SQL statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query.

9. What is MERGE Statement?

MERGE is a new feature that provides an efficient way to perform multiple DML operations. In previous versions of SQL Server, we had to write separate statements to INSERT, UPDATE, or DELETE data based on certain conditions, but now, using MERGE statement we can include the logic of such data modifications in one statement that even checks when the data is matched then just update it and when unmatched then insert it. One of the most important advantages of MERGE statement is all the data is read and processed only once.

10. 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.

11. Which are new data types introduced in SQL SERVER 2008?

The GEOMETRY Type: The GEOMETRY data type is a system .NET common language runtime (CLR) data type in SQL Server. This type represents data in a two-dimensional Euclidean coordinate system.
The GEOGRAPHY Type: The GEOGRAPHY datatype’s functions are the same as with GEOMETRY. The difference between the two is that when you specify GEOGRAPHY, you are usually specifying points in terms of latitude and longitude.
New Date and Time Datatypes: SQL Server 2008 introduces four new datatypes related to date and time: DATE, TIME, DATETIMEOFFSET, and DATETIME2.

DATE: The new DATE type just stores the date itself. It is based on the Gregorian calendar and handles years from 1 to 9999.
TIME: The new TIME (n) type stores time with a range of 00:00:00.0000000 through 23:59:59.9999999. The precision is allowed with this type. TIME supports seconds down to 100 nanoseconds. The n in TIME (n) defines this level of fractional second precision, from 0 to 7 digits of precision.
The DATETIMEOFFSET Type: DATETIMEOFFSET (n) is the time-zone-aware version of a datetimedatatype. The name will appear less odd when you consider what it really is: a date + a time + a time-zone offset. The offset is based on how far behind or ahead you are from Coordinated Universal Time (UTC) time.
The DATETIME2 Type: It is an extension of the datetime type in earlier versions of SQL Server. This new datatype has a date range covering dates from January 1 of year 1 through December 31 of year 9999. This is a definite improvement over the 1753 lower boundary of the datetimedatatype. DATETIME2 not only includes the larger date range, but also has a timestamp and the same fractional precision that TIME type provides

12. What are the Advantages of using CTE?

Using CTE improves the readability and makes maintenance of complex queries easy.
The query can be divided into separate, simple, logical building blocks which can be then used to build more complex CTEs until final result set is generated.
CTE can be defined in functions, stored procedures, triggers or even views.
After a CTE is defined, it can be used as a Table or a View and can SELECT, INSERT, UPDATE or DELETE Data.

13. What is CLR?

In SQL Server 2008, SQL Server objects such as user-defined functions can be created using such CLR languages. This CLR language support extends not only to user-defined functions, but also to stored procedures and triggers. You can develop such CLR add-ons to SQL Server using Visual Studio 2008.

14. What are synonyms?

Synonyms give you the ability to provide alternate names for database objects. You can alias object names; for example, using the Employee table as Emp. You can also shorten names. This is especially useful when dealing with three and four part names; for example, shortening server.database.owner.object to object.

15. What is LINQ?

Language Integrated Query (LINQ) adds the ability to query objects using .NET languages. The LINQ to SQL object/relational mapping (O/RM) framework provides the following basic features:

Tools to create classes (usually called entities) mapped to database tables
Compatibility with LINQ's standard query operations
The DataContext class, with features such as entity record monitoring, automatic SQL statement generation, record concurrency detection, and much more

16. What is Isolation Levels?

Transactions specify an isolation level that defines the degree to which one transaction must be isolated from resource or data modifications made by other transactions. Isolation levels are described in terms of which concurrency side-effects, such as dirty reads or phantom reads, are allowed.

Transaction isolation levels control:

Whether locks are taken when data is read, and what type of locks are requested.
How long the read locks are held.
Whether a read operation referencing rows modified by another transaction:

Blocks until the exclusive lock on the row is freed.
Retrieves the committed version of the row that existed at the time the statement or transaction started.
Reads the uncommitted data modification.

17. What is use of EXCEPT Clause?

EXCEPT clause is similar to MINUS operation in Oracle. The EXCEPT query and MINUS query returns all rows in the first query that are not returned in the second query. Each SQL statement within the EXCEPT query and MINUS query must have the same number of fields in the result sets with similar data types.

18. How would you handle error in SQL SERVER 2008?

SQL Server now supports the use of TRY...CATCH con handling. TRY...CATCH lets us build error handling at the level we need, in the way w to, by setting a region where if any error occurs, it will break out of the region and head to an error handler.

The basic structure is as follows:
BEGIN TRY
stmts..
END TRY
BEGIN CATCH
stmts..
END CATCH

19. What is RAISEERROR?

RaiseError generates an error message and initiates error processing for the session. RAISERROR can either reference a user-defined message stored in the sys.messagescatalog view or build a message dynamically. The message is returned as a server error message to the calling application or to an associated CATCH block of a TRY | CATCH construct.

20. How to rebuild Master Database?

Master database is system database and it contains information about running server's configuration. When SQL Server 2005 is installed it usually creates master, model, msdb, tempdb resource and distribution system database by default. Only Master database is th one which is absolutely must have database. Without Master database SQL Server cannot be started. This is the reason it is extremely important to backup Master database.

To rebuild the Master database, Run Setup.exe, verify, and repair a SQL Server instance, and rebuild the system databases. This procedure is most often used to rebuild the master database for a corrupted installation of SQL Server.

21. What is XML Datatype?

The xml data type lets you store XML documents and fragments in a SQL Server database. An XML fragment is an XML instance that is missing a single top-level element. You can create columns and variables of the xml type and store XML instances in them. The xml data type and associated methods help integrate XML into the relational framework of S Server.

22. What is Data Compression?

In SQL SERVE 2008 Data Compression comes in two flavors:

Row Compression: Row compression changes the format of physical storage of data. It minimize the metadata (column information, length, offsets etc) associated with each record. Numeric data types and fixed length strings are stored in variable-length storage format, just like Varchar.
Page Compression: Page compression allows common data to be shared between rows for a given page. Its uses the following techniques to compress data:

Row compression.
Prefix Compression. For every column in a page duplicate prefixes are identified. These prefixes are saved in compression information headers (CI) which resides after page header. A reference number is assigned to these prefixes and that reference number is replaced where ever those prefixes are being used.

Dictionary Compression: Dictionary compression searches for duplicate values throughout the page and stores them in CI. The main difference between prefix and dictionary compression is that prefix is only restricted to one column while dictionary is applicable to the complete page.

23. What is Catalog Views?

Catalog views return information that is used by the SQL Server Database Engine. Catalog Views are the most general interface to the catalog metadata and provide the most efficient way to obtain, transform, and present customized forms of this information. All user- available catalog metadata is exposed through catalog views.

24. What is PIVOT and UNPIVOT?

A Pivot Table can automatically sort, count, and total the data stored in one table or spreadsheet and create a second table displaying the summarized data. The PIVOT operator turns the values of a specified column into column names, effectively rotating a table.

UNPIVOT table is reverse of PIVOT Table.

25. What is Dirty Read ?

A dirty read occurs when two operations say, read and write occurs together giving the incorrect or unedited data. Suppose, A has changed a row, but has not committed the changes. B reads the uncommitted data but his view of the data may be wrong so that is Dirty Read.

26. What is Aggregate Functions?

Aggregate functions perform a calculation on a set of values and return a single value. Aggregate functions ignore NULL values except COUNT function. HAVING clause is used, along with GROUP BY, for filtering query using aggregate values.

Following functions are aggregate functions.

AVG, MIN CHECKSUM_AGG, SUM, COUNT, STDEV, COUNT_BIG, STDEVP, GROUPING, VAR, MAX. VARP

27. What do you mean by Table Sample?

TABLESAMPLE allows you to extract a sampling of rows from a table in the FROM clause. The rows retrieved are random and they are not in any order. This sampling can be based on a percentage of number of rows. You can use TABLESAMPLE when only a sampling of rows is necessary for the application instead of a full result set.

28. What is the difference between UNION and UNION ALL?

UNIONThe UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type. With UNION, only distinct values are selected.
UNION ALL The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values.

The difference between Union and Union all is that Union all will not eliminate duplicate rows, instead it just pulls all rows from all tables fitting your query specifics and combines them into a table.

29. What is B-Tree?

The database server uses a B-tree structure to organize index information. B-Tree generally has following types of index pages or nodes:

root node: A root node contains node pointers to branch nodes which can be only one.
branch node: A branch node contains pointers to leaf nodes or other branch nodes which can be two or more.
leaf nodes: A leaf node contains index items and orizantal pointers to other leaf nodes which can be many.

1. When is the UPDATE_STATISTICS command used?

- When the processing of large data is done, this command is used.
- Whenever large number of deletions, modification or copy takes place into the tables, the indexes need to be updated to take care of these changes. UPDATE_STATISTICS performs this job.

2. Differentiate between a HAVING CLAUSE and a WHERE CLAUSE.

HAVING CLAUSE
- HAVING CLAUSE is used only with the SELECT statement.
- It is generally used in a GROUP BY clause in a query.
- If GROUP BY is not used, HAVING works like a WHERE clause.
WHERE Clause
- It is applied to each row before they become a part of the GROUP BY function in a query.
Download SQL Server interview questions and answers pdf

3. What do you understand by a view? What does the WITH CHECK OPTION clause for a view do?

- A view is a virtual table that consists of fields from one or more real tables.
- It is usually used to join multiple tables and get the data.
- The WITH CHECK OPTION for a view prevents any modification to the data that does not confirm to the WHERE clause of the view definition.
- This allows the data belonging to the view to be updated through the view.

4. Explain query execution plan?

- The optimizer available in SQL Server optimizes the code to be effectively executed.
- A query execution plan shows how this optimizer would run the query.
- Query execution plan can be viewed by :
- Using the Show Execution Plan option available in Query Analyzer,
- Displaying Estimated Execution Plan on the query dropdown menu,
- Use the SET SHOWPLAN_TEXT ON command before running a query and capturing the execution plan event in a SQL Server Profiler trace.

5. What is the function of SQL Server Agent Windows service?

- It is a Windows service which handles the tasks scheduled within the SQL Server environment. These tasks are also called as job and are stored with in SQL server. The jobs may run through a trigger, a predefined schedule or on demand.
- This service is very useful in determining why a particular job did not run as intended.

6. Comment on Transactions.

- Using transactions we can group all SQL commands into a single unit.
- The transaction begins with some task and finishes only when all tasks within it are over.
- The transaction gets over successfully only when all commands in it are successfully over. Even if one command fails, the whole transaction fails.
- The BEGIN TRANSACTION, ROLLBACK TRANSACTION, and COMMIT TRANSACTION statements are used to work with transactions.
- A group of tasks starts with the begin statement.
- In case of any problem, the rollback command is executed to abort the transaction.
- If all the tasks run successfully, all commands are executed through commit statement.

7. Differentiate between a primary key and a unique key.

- By default, clustered index on the column are created by the primary key whereas nonclustered index are created by unique key.
- Primary key doesn't allow NULLs, but unique key allows one NULL.

8. What is recursion? Is it possible for a stored procedure to call itself or recursive stored procedure? How many levels of SP nesting is possible?

Recursion is method of problem solving where the solution is arrived at by repetitively applying the logic and solution to the subsets of the problem.
Transact-SQL supports recursion. So, yes it is possible for a stored procedure to call itself.
Stored procedures and managed code references can be nested up to 32 levels.

9. What are the advantages of using Stored Procedures?

- They help in reducing the network traffic and latency which in turn boosts application performance.
- They help in promoting code reuse.
- They provide better security to data.
- It is possible to encapsulate the logic using stored procedures. This allows to change stored procedure code without affecting clients.
- It is possible to reuse stored procedure execution plans, which are cached in SQL Server's memory. This reduces server overhead.

10. a.) What do you mean by an execution plan? Why is it used? How would you view it?

a.) An execution plan can be called as a road map that graphically or textually shows the data retrieval methods which have been chosen by the SQL
Server query optimizer, for a stored procedure or ad- hoc query.

b.) It is used because it is a very useful tool for a developer to understand the performance characteristics of a query or stored procedure.

c.) There exists an option called "Show Execution Plan" in Query Analyzer. If this option is turned on, it will display query execution plan in separate window when the query is run again.

11. You want to implement the following relationships while designing tables. How would you do it?
a.) One-to-one
b.) One-to-many
c.) Many-to-many

a.) One-to-One relationship - can be implemented as a single table and rarely as two tables with primary and foreign key relationships.

b.) One-to-Many relationships - by splitting the data into two tables with primary key and foreign key relationships.

c.) Many-to-Many - by using a junction table with the keys from both the tables forming the composite primary key of the junction table.

12. Differentiate between DELETE and TRUNCATE.

- Truncate can not be rolled back while Delete can be.
- Truncate keeps the lock on table while Delete keeps the lock on each row.
- Truncate resets the counter of the Identity column while Delete doesn't do so.
- Trigger is not fired in Truncate while it happens in Delete.

13. What are the properties of the Relational tables?

Relational tables have six properties:
1. Values are atomic.
2. Column values are of the same kind.
3. Each row is unique.
4. The sequence of columns is insignificant.
5. The sequence of rows is insignificant.
6. Each column must have a unique name.

14. Explain the following.

a.) COLLATION.

Collation is a type of sort order. There are mainly three types of sort orders, namely:
i.) Dictionary case sensitive
ii.)Dictionary - case insensitive
iii.)Binary.

b.) Stored Procedure

- It is a set of T-SQL statements combined together to perform a single task formed by combining many small tasks.
- When you actually run a Stored procedure, a set of statements is run.

15. What do you mean by ACID?

- ACID (Atomicity Consistency Isolation Durability) is a quality sought after in a reliable database. Here's the relevance of each quality:
- Atomicity is an all-or-none proposition.
- Consistency - it guarantees that your database is never left by a transaction in a half-finished state.
- Isolation - it keeps transactions separated from each other until they’re finished.
- Durability - it ensures that the database keeps a track of pending changes in a way that the server can recover from an abnormal termination.

16. Explain the following:

a.) Dirty pages.
These are the buffer pages that contain modifications which have not been written to disk.

b.) ETL - Extraction, Transformation, and Loading.
- It is the process of copying and cleaning data from heterogeneous sources.
- It is an important part of development projects for data warehousing and business intelligence.

17. Differentiate between a Local and a Global temporary table?

- A local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement.

- Global temporary tables (created with a double “##”) are visible to all sessions.
- Global temporary tables are dropped when the session that created it ends, and all other sessions have stopped referencing it.

18. Explain different types of Locks in SQL Server.

There are 3 kinds of locks in SQL Server

i.) Shared locks - they are used for operations which do not allow any change or update of data. For e.g. SELECT.

ii.) Update locks - they are used when SQL Server wants to modify a page. The update page lock is then promoted to an exclusive page lock before actually making the changes.

iii.) Exclusive locks - they are used for the data modification operations. For e.g. UPDATE, INSERT, or DELETE.

What are DMVs?

SELECT * FROM sys.dm_os_wait_stats;

Another example is examining current sessions, much like the sp_who2 command:

SELECT * FROM sys.dm_exec_sessions;

What are temp tables? What is the difference between global and local temp tables?

CREATE TABLE #tempLocal( nameidint, fnamevarchar(50), lnamevarchar(50) )

Local temporary tables are available to the current connection for the user, so they disappear when the user disconnects.
Global temporary tables may be created with double hashes (##). These are available to all users via all connections, and they are deleted only when all connections are closed.

CREATE TABLE ##tempGlobal( nameidint, fnamevarchar(50), lnamevarchar(50) )

How are transactions used?

What is the difference between a clustered and a nonclustered index?

A clustered index affects the way the rows of data in a table are stored on disk. When a clustered index is used, rows are stored in sequential order according to the index column value; for this reason, a table can contain only one clustered index, which is usually used on the primary index value.
A nonclustered index does not affect the way data is physically stored; it creates a new object for the index and stores the column(s) designated for indexing with a pointer back to the row containing the indexed values.
You can think of a clustered index as a dictionary in alphabetical order, and a nonclustered index as a book's index.

What are DBCC commands?

Basically, the Database Consistency Checker (DBCC) provides a set of commands (many of which are undocumented) to maintain databases -- maintenance, validation, and status checks. The syntax is DBCC followed by the command name. Here are three examples:
DBCC CHECKALLOC -- Check disk allocation consistency.
DBCC OPENTRAN -- Display information about recent transactions.
DBCC HELP -- Display Help for DBCC commands.

What is the difference between truncate and delete?

What does the NOLOCK query hint do?

Table hints allow you to override the default behavior of the query optimizer for statements. They are specified in the FROM clause of the statement. While overriding the query optimizer is not always suggested, it can be useful when many users or processes are touching data. The NOLOCK query hint is a good example because it allows you to read data regardless of who else is working with the data; that is, it allows a dirty read of data -- you read data no matter if other users are manipulating it. A hint like NOLOCK increases concurrency with large data stores.

SELECT * FROM table_name (NOLOCK)

Microsoft advises against using NOLOCK, as it is being replaced by the READUNCOMMITTED query hint. There are lots more query hints with plenty of information online.

What is a CTE?

WITH ExampleCTE (id, fname, lname)

(

SELECT id, firstname, lastname FROM table

)

SELECT * FROM ExampleCTE

A CTE can be used in place of a view in some instances.

What is a view? What is the WITH CHECK OPTION clause for a view?

A view is a virtual table that consists of fields from one or more real tables. Views are often used to join multiple tables or to control access to the underlying tables.

What is a query execution plan?

What does the SQL Server Agent Windows service do?

What is the default port number for SQL Server?

What are triggers? How many triggers you can have on a table? How to invoke a trigger on demand?

Triggers are constructs in PL/SQL that need to be just created and associated with a table. Once they are created, when the table associated with it gets updated due to an UPDATE, INSERT or a DELETE, the triggers get implicitly fired depending upon the instructions passed to them.
A table can have up to 12 triggers defined on it.
Triggers can't be invoked on demand. They get triggered when the associated INSERT, DELETE or UPDATE is performed.

Triggers - October 24, 2008 at 18:10 pm by RajmeetGhai

Describe triggers features and limitations.

Trigger features:-
1. Can execute a batch of SQL code for an insert, update or delete command is executed
2. Business rules can be enforced on modification of data
Trigger Limitations:-
1. Does not accept arguments or parameters
2. Cannot perform commit or rollback
3. Can cause table errors if poorly written

Syntax for viewing, dropping and disabling triggers

View trigger:
A trigger can be viewed by using sp_helptrigger syntax. This returns all the triggers defined in a table.
Sp_helptriggertable_name
Drop a trigger
Syntax: DROP TRIGGER Trigger_name
Disable a trigger:-
Syntax: DISABLE TRIGGER [schema name] trigger name
ON [object, database or ALL server ]

Determine how to use the inserted and deleted pseudo tables.

Inserted and deleted pseudo tables contain the New and Old values of the data that initiating the Trigger. These tables can be used for database maintenance and dynamic modification to data. These tables can be examined by the trigger itself. The tables themselves cannot be altered.

Explain how to apply cascading referential integrity in place of triggers.

Cascading referential integrity constraints are automatic updates and deletes on dependant objects. They define a set of actions that SQL server may need to take. The References clause of the CREATE TABLE and ALTER TABLE statements support ON DELETE and ON UPDATE clauses:

· [ON DELETE {NO ACTION} ]: If an attempt to delete a row is made, with a key referenced by foreign keys in existing rows in other tables, an error is raised and DELETE is rolled back.

· [ON UPDATE {NO ACTION } ]: If an attempt to update a row is made, with a key referenced by foreign keys in existing rows in other tables, an error is raised and UPDATE is rolled back.

· [ ON DELETE { CASCADE } ]: If an attempt to delete a row is made, with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are also deleted

· [ ON UPDATE { CASCADE } ]: If an attempt to update a row is made, with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are also Updated.

NO ACTION is default.

Explain trigger classes i.e. instead of and after trigger.

Answer
INSTEAD OF: Cause the trigger to fire instead of executing the triggering event or action. It prevents unnecessary changes to be made.
Example: Causes the trigger to fire instead of the update (action)
CREATE TRIGGER Employee_update ON Employee INSTEAD OF UPDATE AS { TRIGGER Definition }
AFTER: execute following the triggering action, such as an insert, update, or delete. These triggers are fired a little late in the process.
Example: Causes the trigger to fire instead of the update (action)
CREATE TRIGGER Employee_update ON Employee AFTER UPDATE AS { TRIGGER Definition }

What are the instances when triggers are appropriate?

Answer

· When security is the top most priority. i.e. to allow unauthorized access

· When backups are essential

· When Maintenance is desired. Triggers can be fired when any error message is logged

· Keeping the database consistent.

· Table Name : Employee

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	SALARY	JOINING_DATE	DEPARTMENT
1	John	Abraham	1000000	01-JAN-13 12.00.00 AM	Banking
2	Michael	Clarke	800000	01-JAN-13 12.00.00 AM	Insurance
3	Roy	Thomas	700000	01-FEB-13 12.00.00 AM	Banking
4	Tom	Jose	600000	01-FEB-13 12.00.00 AM	Insurance
5	Jerry	Pinto	650000	01-FEB-13 12.00.00 AM	Insurance
6	Philip	Mathew	750000	01-JAN-13 12.00.00 AM	Services
7	TestName1	123	650000	01-JAN-13 12.00.00 AM	Services
8	TestName2	Lname%	600000	01-FEB-13 12.00.00 AM	Insurance

·
Table Name : Incentives

EMPLOYEE_REF_ID	INCENTIVE_DATE	INCENTIVE_AMOUNT
1	01-FEB-13	5000
2	01-FEB-13	3000
3	01-FEB-13	4000
1	01-JAN-13	4500
2	01-JAN-13	3500

· SQL Queries Interview Questions and Answers on "SQL Select" - Examples

· 1. Get all employee details from the employee table

·          Select * from employee

· 2. Get First_Name,Last_Name from employee table

·          Select first_name, Last_Name from employee

· 3. Get First_Name from employee table using alias name “Employee Name”

·          Select first_name Employee Name from employee

· 4. Get First_Name from employee table in upper case

·          Select upper(FIRST_NAME) from EMPLOYEE

· 5. Get First_Name from employee table in lower case

·         Select lower(FIRST_NAME) from EMPLOYEE

· 6. Get unique DEPARTMENT from employee table

·         select distinct DEPARTMENT from EMPLOYEE

· 7. Select first 3 characters of FIRST_NAME from EMPLOYEE

·         Oracle Equivalent of SQL Server SUBSTRING is SUBSTR, Query : select substr(FIRST_NAME,0,3) from employee

·         SQL Server Equivalent of Oracle SUBSTR is SUBSTRING, Query : select substring(FIRST_NAME,0,3) from employee

·         MySQL Server Equivalent of Oracle SUBSTR is SUBSTRING. In MySQL start position is 1, Query : select substring(FIRST_NAME,1,3) from employee

· 8. Get position of 'o' in name 'John' from employee table

·         Oracle Equivalent of SQL Server CHARINDEX is INSTR, Query : Select instr(FIRST_NAME,'o') from employee where first_name = 'John'

·         SQL Server Equivalent of Oracle INSTR is CHARINDEX, Query: Select CHARINDEX('o',FIRST_NAME,0) from employee where first_name = 'John'

·         MySQL Server Equivalent of Oracle INSTR is LOCATE, Query: Select LOCATE('o',FIRST_NAME) from employee where first_name = 'John'

· 9. Get FIRST_NAME from employee table after removing white spaces from right side

·         select RTRIM(FIRST_NAME) from employee

· 10. Get FIRST_NAME from employee table after removing white spaces from left side

·         select LTRIM(FIRST_NAME) from employee

· 11. Get length of FIRST_NAME from employee table

·         Oracle,MYSQL Equivalent of SQL Server Len is Length , Query :select length(FIRST_NAME) from employee

·         SQL Server Equivalent of Oracle,MYSQL Length is Len, Query :select len(FIRST_NAME) from employee

· 12. Get First_Name from employee table after replacing 'o' with '$'

·         select REPLACE(FIRST_NAME,'o','$') from employee

· 13. Get First_Name and Last_Name as single column from employee table separated by a '_'

·         Oracle Equivalent of MySQL concat is '||', Query : Select FIRST_NAME|| '_' ||LAST_NAME from EMPLOYEE

·         SQL Server Equivalent of MySQL concat is '+', Query : Select FIRST_NAME + '_' +LAST_NAME from EMPLOYEE

·         MySQL Equivalent of Oracle '||' is concat, Query : Select concat(FIRST_NAME,'_',LAST_NAME) from EMPLOYEE

· 14. Get FIRST_NAME ,Joining year,Joining Month and Joining Date from employee table

·         SQL Queries in Oracle, Select FIRST_NAME, to_char(joining_date,'YYYY') JoinYear , to_char(joining_date,'Mon'), to_char(joining_date,'dd') from EMPLOYEE

·         SQL Queries in SQL Server, select SUBSTRING (convert(varchar,joining_date,103),7,4) , SUBSTRING (convert(varchar,joining_date,100),1,3) , SUBSTRING (convert(varchar,joining_date,100),5,2) from EMPLOYEE

·         SQL Queries in MySQL, select year(joining_date),month(joining_date), DAY(joining_date) from EMPLOYEE

· Database SQL Queries Interview Questions and answers on "SQL Order By"

· 15. Get all employee details from the employee table order by First_Name Ascending

· Select * from employee order by FIRST_NAME asc

· 16. Get all employee details from the employee table order by First_Name descending

· Select * from employee order by FIRST_NAME desc

· 17. Get all employee details from the employee table order by First_Name Ascending and Salary descending

·         Select * from employee order by FIRST_NAME asc,SALARYdesc

· SQL Queries Interview Questions and Answers on "SQL Where Condition" - Examples

· 18. Get employee details from employee table whose employee name is “John”

·         Select * from EMPLOYEE where FIRST_NAME = 'John'

· 19. Get employee details from employee table whose employee name are “John” and “Roy”

·         Select * from EMPLOYEE where FIRST_NAME in ('John','Roy')

· 20. Get employee details from employee table whose employee name are not “John” and “Roy”

·         Select * from EMPLOYEE where FIRST_NAME not in ('John','Roy')

· SQL Queries Interview Questions and Answers on "SQL Wild Card Search" - Examples

· 21. Get employee details from employee table whose first name starts with 'J'

·         Select * from EMPLOYEE where FIRST_NAME like 'J%'

· 22. Get employee details from employee table whose first name contains 'o'

·         Select * from EMPLOYEE where FIRST_NAME like '%o%'

· 23. Get employee details from employee table whose first name ends with 'n'

·         Select * from EMPLOYEE where FIRST_NAME like '%n'

· SQL Queries Interview Questions and Answers on "SQL Pattern Matching" - Examples

· 24. Get employee details from employee table whose first name ends with 'n' and name contains 4 letters

·         Select * from EMPLOYEE where FIRST_NAME like '___n' (Underscores)

· 25. Get employee details from employee table whose first name starts with 'J' and name contains 4 letters

·         Select * from EMPLOYEE where FIRST_NAME like 'J___' (Underscores)

· 26. Get employee details from employee table whose Salary greater than 600000

·         Select * from EMPLOYEE where Salary > 600000

· 27. Get employee details from employee table whose Salary less than 800000

·         Select * from EMPLOYEE where Salary < 800000

· 28. Get employee details from employee table whose Salary between 500000 and 800000

·         Select * from EMPLOYEE where Salary between 500000 and 800000

· 29. Get employee details from employee table whose name is 'John' and 'Michael'

·         Select * from EMPLOYEE where FIRST_NAME in ('John','Michael')

· SQL Queries Interview Questions and Answers on "SQL DATE Functions" - Examples

· 30. Get employee details from employee table whose joining year is “2013”

· SQL Queries in Oracle, Select * from EMPLOYEE where to_char(joining_date,'YYYY') = '2013'

SQL Queries in SQL Server, Select * from EMPLOYEE where SUBSTRING(convert(varchar,joining_date,103),7,4) = '2013'

SQL Queries in MySQL, Select * from EMPLOYEE where year(joining_date) = '2013'

· 31. Get employee details from employee table whose joining month is “January”

·         SQL Queries in Oracle, Select * from EMPLOYEE where to_char(joining_date,'MM') = '01' or Select * from EMPLOYEE where to_char(joining_date,'Mon') = 'Jan'

·

·         SQL Queries in SQL Server, Select * from EMPLOYEE where SUBSTRING(convert(varchar,joining_date,100),1,3) = 'Jan'

·

·         SQL Queries in MySQL, Select * from EMPLOYEE where month(joining_date) = '01'

· 32. Get employee details from employee table who joined before January 1st 2013

·         SQL Queries in Oracle, Select * from EMPLOYEE where JOINING_DATE <to_date('01/01/2013','dd/mm/yyyy')

·         SQL Queries in SQL Server (Format - “MM/DD/YYYY”), Select * from EMPLOYEE where joining_date< '01/01/2013'

·         SQL Queries in MySQL (Format - “YYYY-DD-MM”), Select * from EMPLOYEE where joining_date< '2013-01-01'

· 33. Get employee details from employee table who joined after January 31st

·         SQL Queries in Oracle, Select * from EMPLOYEE where JOINING_DATE >to_date('31/01/2013','dd/mm/yyyy')

·         SQL Queries in SQL Server and MySQL (Format - “MM/DD/YYYY”), Select * from EMPLOYEE where joining_date>'01/31/2013'

·         SQL Queries in MySQL (Format - “YYYY-DD-MM”), Select * from EMPLOYEE where joining_date> '2013-01-31'

· 35. Get Joining Date and Time from employee table

·         SQL Queries in Oracle, select to_char(JOINING_DATE,'dd/mm/yyyyhh:mi:ss') from EMPLOYEE

·         SQL Queries in SQL Server, Select convert(varchar(19),joining_date,121) from EMPLOYEE

·         SQL Queries in MySQL, Select CONVERT(DATE_FORMAT(joining_date,'%Y-%m-%d-%H:%i:00'),DATETIME) from EMPLOYEE

· 36. Get Joining Date,Time including milliseconds from employee table

·         SQL Queries in Oracle, select to_char(JOINING_DATE,'dd/mm/yyyyHH:mi:ss.ff') from EMPLOYEE . Column Data Type should be “TimeStamp”

·         SQL Queries in SQL Server, select convert(varchar,joining_date,121) from EMPLOYEE

·         SQL Queries in MySQL, Select MICROSECOND(joining_date) from EMPLOYEE

· 37. Get difference between JOINING_DATE and INCENTIVE_DATE from employee and incentives table

·         Select FIRST_NAME,INCENTIVE_DATE - JOINING_DATE from employee a inner join incentives B on A.EMPLOYEE_ID = B.EMPLOYEE_REF_ID

· 38. Get database date

·         SQL Queries in Oracle, select sysdate from dual

·         SQL Queries in SQL Server, select getdate()

·         SQL Query in MySQL, select now()

· SQL Queries Interview Questions and Answers on "SQL Escape Characters" - Examples

· 39. Get names of employees from employee table who has '%' in Last_Name. Tip : Escape character for special characters in a query.

· SQLQueries in Oracle, Select FIRST_NAME from employee where Last_Name like '%?%%'

· SQLQueries in SQL Server, Select FIRST_NAME from employee where Last_Name like '%[%]%'

· SQLQueries in MySQL,Select FIRST_NAME from employee where Last_Name like '%\%%'

· 40. Get Last Name from employee table after replacing special character with white space

·         SQL Queries in Oracle, Select translate(LAST_NAME,'%',' ') from employee

·

·         SQL Queries in SQL Server and MySQL, Select REPLACE(LAST_NAME,'%',' ') from employee

· SQL Queries Interview Questions and Answers on "SQL Group By Functions" - Examples

· 41. Get department,total salary with respect to a department from employee table.

·         Select DEPARTMENT,sum(SALARY) Total_Salary from employee group by department

· 42. Get department,total salary with respect to a department from employee table order by total salary descending

·         Select DEPARTMENT,sum(SALARY) Total_Salary from employee group by DEPARTMENT order by Total_Salary descending

· SQL Queries Interview Questions and Answers on "SQL Mathematical Operations using Group By" - Examples

· 43. Get department,no of employees in a department,total salary with respect to a department from employee table order by total salary descending

·         Select DEPARTMENT,count(FIRST_NAME),sum(SALARY) Total_Salary from employee group by DEPARTMENT order by Total_Salary descending

· 44. Get department wise average salary from employee table order by salary ascending

·         select DEPARTMENT,avg(SALARY) AvgSalary from employee group by DEPARTMENT order by AvgSalaryasc

· 45. Get department wise maximum salary from employee table order by salary ascending

·         select DEPARTMENT,max(SALARY) MaxSalary from employee group by DEPARTMENT order by MaxSalaryasc

· 46. Get department wise minimum salary from employee table order by salary ascending

·         select DEPARTMENT,min(SALARY) MinSalary from employee group by DEPARTMENT order by MinSalaryasc

· 47. Select no of employees joined with respect to year and month from employee table

·         SQL Queries in Oracle, select to_char (JOINING_DATE,'YYYY') Join_Year,to_char (JOINING_DATE,'MM') Join_Month,count(*) Total_Emp from employee group by to_char (JOINING_DATE,'YYYY'),to_char(JOINING_DATE,'MM')

·         SQL Queries in SQL Server, select datepart (YYYY,JOINING_DATE) Join_Year,datepart (MM,JOINING_DATE) Join_Month,count(*) Total_Emp from employee group by datepart(YYYY,JOINING_DATE), datepart(MM,JOINING_DATE)

·         SQL Queries in MySQL, select year (JOINING_DATE) Join_Year,month (JOINING_DATE) Join_Month,count(*) Total_Emp from employee group by year(JOINING_DATE), month(JOINING_DATE)

· 48. Select department,total salary with respect to a department from employee table where total salary greater than 800000 order by Total_Salary descending

·         Select DEPARTMENT,sum(SALARY) Total_Salary from employee group by DEPARTMENT having sum(SALARY) > 800000 order by Total_Salarydesc

· SQL Queries Interview Questions and Answers on "SQL Joins" - Examples

· 49. Select first_name, incentive amount from employee and incentives table for those employees who have incentives

· Select FIRST_NAME,INCENTIVE_AMOUNT from employee a inner join incentives B on A.EMPLOYEE_ID = B.EMPLOYEE_REF_ID

· 50. Select first_name, incentive amount from employee and incentives table for those employees who have incentives and incentive amount greater than 3000

·         Select FIRST_NAME,INCENTIVE_AMOUNT from employee a inner join incentives B on A.EMPLOYEE_ID = B.EMPLOYEE_REF_ID and INCENTIVE_AMOUNT > 3000

· 51. Select first_name, incentive amount from employee and incentives table for all employes even if they didn't get incentives

·         Select FIRST_NAME,INCENTIVE_AMOUNT from employee a left join incentives B on A.EMPLOYEE_ID = B.EMPLOYEE_REF_ID

· 52. Select first_name, incentive amount from employee and incentives table for all employees even if they didn't get incentives and set incentive amount as 0 for those employees who didn't get incentives.

·         SQL Queries in Oracle, Select FIRST_NAME,nvl(INCENTIVE_AMOUNT,0) from employee a left join incentives B on A.EMPLOYEE_ID = B.EMPLOYEE_REF_ID

·         SQL Queries in SQL Server, Select FIRST_NAME, ISNULL(INCENTIVE_AMOUNT,0) from employee a left join incentives B on A.EMPLOYEE_ID = B.EMPLOYEE_REF_ID

·         SQL Queries in MySQL, Select FIRST_NAME, IFNULL(INCENTIVE_AMOUNT,0) from employee a left join incentives B on A.EMPLOYEE_ID = B.EMPLOYEE_REF_ID

· 53. Select first_name, incentive amount from employee and incentives table for all employees who got incentives using left join

·         SQL Queries in Oracle, Select FIRST_NAME,nvl(INCENTIVE_AMOUNT,0) from employee a right join incentives B on A.EMPLOYEE_ID = B.EMPLOYEE_REF_ID

·         SQL Queries in SQL Server, Select FIRST_NAME, isnull(INCENTIVE_AMOUNT,0) from employee a right join incentives B on A.EMPLOYEE_ID = B.EMPLOYEE_REF_ID

·         SQL Queries in MySQL, Select FIRST_NAME, IFNULL(INCENTIVE_AMOUNT,0) from employee a right join incentives B on A.EMPLOYEE_ID = B.EMPLOYEE_REF_ID

· 54. Select max incentive with respect to employee from employee and incentives table using sub query

·         SQL Queries in Oracle, select DEPARTMENT,(select nvl(max(INCENTIVE_AMOUNT),0) from INCENTIVES where EMPLOYEE_REF_ID = EMPLOYEE_ID) Max_incentive from EMPLOYEE

·         SQL Queries in SQL Server, select DEPARTMENT,(select ISNULL(max(INCENTIVE_AMOUNT),0) from INCENTIVES where EMPLOYEE_REF_ID = EMPLOYEE_ID) Max_incentive from EMPLOYEE

·         SQL Queries in SQL Server, select DEPARTMENT,(select IFNULL (max(INCENTIVE_AMOUNT),0) from INCENTIVES where EMPLOYEE_REF_ID = EMPLOYEE_ID) Max_incentive from EMPLOYEE

· Advanced SQL Queries Interview Questions and Answers on "Top N Salary" - Examples

· 55. Select TOP 2 salary from employee table

·         SQL Queries in Oracle, select * from (select * from employee order by SALARY desc) where rownum< 3

·         SQL Queries in SQL Server, select top 2 * from employee order by salary desc

·         SQL Queries in MySQL, select * from employee order by salary desc limit 2

· 56. Select TOP N salary from employee table

·         SQL Queries in Oracle, select * from (select * from employee order by SALARY desc) where rownum< N + 1

·         SQL Queries in SQL Server, select top N * from employee

·         SQL Queries in MySQL, select * from employee order by salary desc limit N

· 57. Select 2nd Highest salary from employee table

·         SQL Queries in Oracle, select min(salary) from (select * from (select * from employee order by SALARY desc) where rownum< 3)

·         SQL Queries in SQL Server, select min(SALARY) from (select top 2 * from employee) a

·         SQL Queries in MySQL, select min(SALARY) from (select * from employee order by salary desc limit 2) a

· 58. Select Nth Highest salary from employee table

·         SQL Queries in Oracle, select min(salary) from (select * from (select * from employee order by SALARY desc) where rownum< N + 1)

·         SQL Queries in SQL Server, select min(SALARY) from (select top N * from employee) a

·         SQL Queries in MySQL, select min(SALARY) from (select * from employee order by salary desc limit N) a

· SQL Queries Interview Questions and Answers on "SQL Union" - Examples

· 59. Select First_Name,LAST_NAME from employee table as separate rows

·         select FIRST_NAME from EMPLOYEE union select LAST_NAME from EMPLOYEE

· 60. What is the difference between UNION and UNION ALL ?

·         Both UNION and UNION ALL is used to select information from structurally similar tables. That means corresponding columns specified in the union should have same data type. For example, in the above query, if FIRST_NAME is DOUBLE and LAST_NAME is STRING above query wont work. Since the data type of both the columns are VARCHAR, union is made possible. Difference between UNION and UNION ALL is that , UNION query return only distinct values.

· "Advanced SQL Queries Interview Questions and Answers"

· 61. Select employee details from employee table if data exists in incentive table ?

· select * from EMPLOYEE where exists (select * from INCENTIVES)

· Explanation : Here exists statement helps us to do the job of If statement. Main query will get executed if the sub query returns at least one row. So we can consider the sub query as "If condition" and the main query as "code block" inside the If condition. We can use any SQL commands (Joins, Group By , having etc) in sub query. This command will be useful in queries which need to detect an event and do some activity.

· 62. How to fetch data that are common in two query results ?

·         select * from EMPLOYEE where EMPLOYEE_ID INTERSECT select * from EMPLOYEE where EMPLOYEE_ID < 4

·         Explanation : Here INTERSECT command is used to fetch data that are common in 2 queries. In this example, we had taken EMPLOYEE table in both the queries.We can apply INTERSECT command on different tables. The result of the above query will return employee details of "ROY" because, employee id of ROY is 3, and both query results have the information about ROY.

· 63. Get Employee ID's of those employees who didn't receive incentives without using sub query ?

·         select EMPLOYEE_ID from EMPLOYEE

·         MINUS

·         select EMPLOYEE_REF_ID from INCENTIVES

·

·         Explanation : To filter out certain information we use MINUS command. What MINUS Command odes is that, it returns all the results from the first query, that are not part of the second query. In our example, first three employees received the incentives. So query will return employee id's 4 to 8.

· 64. Select 20 % of salary from John , 10% of Salary for Roy and for other 15 % of salary from employee table

·         SELECT FIRST_NAME, CASE FIRST_NAME WHEN 'John' THEN SALARY * .2 WHEN 'Roy' THEN SALARY * .10 ELSE SALARY * .15 END "Deduced_Amount" FROM EMPLOYEE

·

·         Explanation : Here we are using SQL CASE statement to achieve the desired results. After case statement, we had to specify the column on which filtering is applied. In our case it is "FIRST_NAME". And in then condition, specify the name of filter like John, Roy etc. To handle conditions outside our filter, use else block where every one other than John and Roy enters.

· 65. Select Banking as 'Bank Dept', Insurance as 'Insurance Dept' and Services as 'Services Dept' from employee table

·         SQL Queries in Oracle, SELECT distinct DECODE (DEPARTMENT, 'Banking', 'Bank Dept', 'Insurance', 'Insurance Dept', 'Services', 'Services Dept') FROM EMPLOYEE

·         SQL Queries in SQL Server and MySQL, SELECT case DEPARTMENT when 'Banking' then 'Bank Dept' when 'Insurance' then 'Insurance Dept' when 'Services' then 'Services Dept' end FROM EMPLOYEE



Explanation : Here DECODE keyword is used to specify the alias name. In oracle we had specify, Column Name followed by Actual Name and Alias Name as arguments. In SQL Server and MySQL, we can use the earlier switch case statements for alias names.

· 66. Delete employee data from employee table who got incentives in incentive table

·         delete from EMPLOYEE where EMPLOYEE_ID in (select EMPLOYEE_REF_ID from INCENTIVES)



Explanation : Trick about this question is that we can't delete data from a table based on some condition in another table by joining them. Here to delete multiple entries from EMPLOYEE table, we need to use Subquery. Entries will get deleted based on the result of Subquery.

· 67. Insert into employee table Last Name with " ' " (Single Quote - Special Character)

·         Tip - Use another single quote before special character

·         Insert into employee (LAST_NAME) values ('Test''')

· 68. Select Last Name from employee table which contain only numbers

·         Select * from EMPLOYEE where lower(LAST_NAME) = upper(LAST_NAME)

·         

Explanation : Here in order to achieve the desired result, we use ASCII property of the database. If we get results for a column using Lower and Upper commands, ASCII of both results will be same for numbers. If there is any alphabets in the column, results will differ.

· 69. Write a query to rank employees based on their incentives for a month

·         select FIRST_NAME,INCENTIVE_AMOUNT,DENSE_RANK() OVER (PARTITION BY INCENTIVE_DATE ORDER BY  INCENTIVE_AMOUNT DESC) AS Rank from EMPLOYEE a, INCENTIVES b where a.EMPLOYEE_ID = b.EMPLOYEE_REF_ID

·         

Explanation : Here in order to rank employees based on their rank for a month, DENSE_RANK keyword is used. Here partition by keyword helps us to sort the column with which filtering is done. Rank is provided to the column specified in the order by statement. The above query ranks employees with respect to their incentives for a given month.

· 70. Update incentive table where employee name is 'John'

· Explanation : Here we need to join Employee and Incentive Table for updating the incentive amount. But for update statement joining query wont work. We need to use sub query to update the data in the incentive table. SQL Query is as shown below.

· update INCENTIVES set INCENTIVE_AMOUNT = '9000' where EMPLOYEE_REF_ID =(select EMPLOYEE_ID from EMPLOYEE where FIRST_NAME = 'John' )

· SQL Queries Interview Questions and Answers on "SQL Table Scripts" - Examples

· 71. Write create table syntax for employee table

·         Oracle -

·

·         CREATE TABLE EMPLOYEE (

·         EMPLOYEE_ID NUMBER,

·         FIRST_NAME VARCHAR2(20 BYTE),

·         LAST_NAME VARCHAR2(20 BYTE),

·         SALARY FLOAT(126),

·         JOINING_DATE TIMESTAMP (6) DEFAULT sysdate,

·         DEPARTMENT VARCHAR2(30 BYTE) )

·

·         SQL Server -

·

·         CREATE TABLE EMPLOYEE(

·         EMPLOYEE_ID int NOT NULL,

·         FIRST_NAME varchar(50) NULL,

·         LAST_NAME varchar(50) NULL,

·         SALARY decimal(18, 0) NULL,

·         JOINING_DATE datetime2(7) default getdate(),

·         DEPARTMENT varchar(50) NULL)

· 72. Write syntax to delete table employee

·         DROP table employee;

· 73. Write syntax to set EMPLOYEE_ID as primary key in employee table

·         ALTER TABLE EMPLOYEE add CONSTRAINT EMPLOYEE_PK PRIMARY KEY(EMPLOYEE_ID)

· 74. Write syntax to set 2 fields(EMPLOYEE_ID,FIRST_NAME) as primary key in employee table

·         ALTER TABLE EMPLOYEE add CONSTRAINT EMPLOYEE_PK PRIMARY KEY(EMPLOYEE_ID,FIRST_NAME)

· 75. Write syntax to drop primary key on employee table

·         Alter TABLE EMPLOYEE drop CONSTRAINT EMPLOYEE_PK;

· 76. Write Sql Syntax to create EMPLOYEE_REF_ID in INCENTIVES table as foreign key with respect to EMPLOYEE_ID in employee table

·         ALTER TABLE INCENTIVES ADD CONSTRAINT INCENTIVES_FK FOREIGN KEY (EMPLOYEE_REF_ID) REFERENCES EMPLOYEE(EMPLOYEE_ID)

· 77. Write SQL to drop foreign key on employee table

·         ALTER TABLE INCENTIVES drop CONSTRAINT INCENTIVES_FK;

· 78. Write SQL to create Orcale Sequence

·         CREATE SEQUENCE EMPLOYEE_ID_SEQ START WITH 0 NOMAXVALUE MINVALUE 0 NOCYCLE NOCACHE NOORDER;

· 79. Write Sql syntax to create Oracle Trigger before insert of each row in employee table

·         CREATE OR REPLACE TRIGGER EMPLOYEE_ROW_ID_TRIGGER

·         BEFORE INSERT ON EMPLOYEE FOR EACH ROW

·         DECLARE

·         seq_no number(12);

·         BEGIN

·         select EMPLOYEE_ID_SEQ.nextval into seq_no from dual ;

·         :new EMPLOYEE_ID := seq_no;

·         END;

·         SHOW ERRORS;

· 80. Oracle Procedure81. Oracle View

·         An example oracle view script is given below

·         create view Employee_Incentive as select FIRST_NAME,max(INCENTIVE_AMOUNT) INCENTIVE_AMOUNT from EMPLOYEE a, INCENTIVES b where a.EMPLOYEE_ID = b.EMPLOYEE_REF_ID group by FIRST_NAME

· 82. Oracle materialized view - Daily Auto Refresh

·         CREATE MATERIALIZED VIEW Employee_Incentive

·         REFRESH COMPLETE

·         START WITH SYSDATE

·         NEXT SYSDATE + 1 AS

·         select FIRST_NAME,INCENTIVE_DATE,INCENTIVE_AMOUNT from EMPLOYEE a, INCENTIVES b

·         where a.EMPLOYEE_ID = b.EMPLOYEE_REF_ID

· 83. Oracle materialized view - Fast Refresh on Commit

·         Create materialized view log for fast refresh. Following materialized view script wont get executed if materialized view log doesn't exists

·         CREATE MATERIALIZED VIEW MAT_Employee_Incentive_Refresh

·         BUILD IMMEDIATE

·         REFRESH FAST ON COMMIT AS

·         select FIRST_NAME,max(INCENTIVE_AMOUNT) from EMPLOYEE a, INCENTIVES b

·         where a.EMPLOYEE_ID = b.EMPLOYEE_REF_ID group by FIRST_NAME

· 84. What is SQL Injection ?

·         SQL Injection is one of the the techniques uses by hackers to hack a website by injecting SQL commands in data fields.

Q: When I delete any data from a table, does SQL Server reduce the size of that table?

A: When data are deleted from any table, the SQL Server does not reduce the size of the table right away, but marks those pages as free pages, showing that they belong to the table. When new data are inserted, they are put into those pages first. Once those pages are filled up, SQL Server will allocate new pages. If you wait for sometime background process de-allocates the pages and finally reducing the page size. Follow the example below.

Q: When I delete any data from a table, does SQL Server reduce the size of the B-Tree or change the level of the B-Tree since there are lesser data?

A: No. It does very different behavior. Follow the example.

USE tempdb
GO
-- Create Table FragTable
CREATE TABLE FragTable(ID CHAR(800),
FirstNameCHAR(2000),
LastNameCHAR(3000),
City CHAR(2200))
GO
-- Create Clustered Index
CREATE CLUSTERED INDEX [IX_FragTable_ID] ON FragTable
(
[ID] ASC
) ON [PRIMARY]
GO
-- Insert one Million Records
INSERT INTO FragTable(ID,FirstName,LastName,City)
SELECT TOP 100 ROW_NUMBER() OVER (ORDER BY a.name) RowID,
'Bob',
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Smith'
ELSE 'Brown' END,
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 1 THEN 'New York'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 5 THEN 'San Marino'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 3 THEN 'Los Angeles'
ELSE 'Houston' END
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
-- Check the spaces
sp_spaceused'FragTable'
GO
-- Check the percentages
SELECT avg_page_space_used_in_percent
,avg_fragmentation_in_percent
,index_level
,record_count
,page_count
,fragment_count
,avg_record_size_in_bytes
FROM sys.dm_db_index_physical_stats(DB_ID('TempDb'),OBJECT_ID('FragTable'),NULL,NULL,'DETAILED')
GO
-- Delete all from table
DELETE
FROM FragTable
GO
-- Check the spaces
sp_spaceused'FragTable'
GO
-- Check the percentages
SELECT avg_page_space_used_in_percent
,avg_fragmentation_in_percent
,index_level
,record_count
,page_count
,fragment_count
,avg_record_size_in_bytes
FROM sys.dm_db_index_physical_stats(DB_ID('TempDb'),OBJECT_ID('FragTable'),NULL,NULL,'DETAILED')
GO
-- Clean up
DROP TABLE FragTable
GO

Now let us check the details. Here, we check the index details before and after the delete statement.

Click on Image to See Larger Image

It is very clear from the example that after deleting data, the size of the table is not reduced; same goes with the levels of the indexes- they are not resized by SQL Server. What really changes is the leaf level pages where data are stored.

However if you wait for some time, and run once again fragmentation script you will noticed that levels are still the same but the page_count has been reduced to 1. This is clearly explained by Hrvojein his comment correcting the blog post over . The part of technical explanation is reproduced here.

“The behavior differs for Heaps and tables with a clustered index. (The following applies to SQL Server 2008 SP1 and it might differ on other versions)

Heaps:
BOL: “When rows are deleted from a heap the Database Engine may use row or page locking for the operation. As a result, the pages made empty by the delete operation remain allocated to the heap. When empty pages are not deallocated, the associated space cannot be reused by other objects in the database.”
While it may appear that the pages are not deallocated due to you example set size, try increasing the number of inserted rows and you will see that the table size gets reduced to around 25MB. The number of index pages reduces accordingly. (Levels do not apply to heaps)
If on the other hand you put a TABLOCK hint in the delete statement all pages will get deallocated.

Tables with a Clustered Index:
Deleting from a table with a clustered index will deallocate deleted pages regardless of the TABLOCK hint. The difference is that without the hint it will happen by a background clean up process and therefore might not be visible immediately after the delete. Try waiting for a few seconds and check space used. TABLOCK will make this operation synchronous.
Number index levels are kept the same, BUT the size of ALL levels gets reduced and not just the leaf levels.

Reducing the size of the heap or the levels of indexes in the B-Tree can be achieved in several ways: if the table is empty (and other constraints satisfied) TRUNCATE TABLE will do the work. Other methods are rebuilding the table/clustered index.” Read original comment.

Monday, 14 July 2014

SQL Interview Questions

1. When is the UPDATE_STATISTICS command used?

2. Differentiate between a HAVING CLAUSE and a WHERE CLAUSE.

3. What do you understand by a view? What does the WITH CHECK OPTION clause for a view do?

4. Explain query execution plan?

5. What is the function of SQL Server Agent Windows service?

6. Comment on Transactions.

7. Differentiate between a primary key and a unique key.

8. What is recursion? Is it possible for a stored procedure to call itself or recursive stored procedure? How many levels of SP nesting is possible?

9. What are the advantages of using Stored Procedures?

10. a.) What do you mean by an execution plan? Why is it used? How would you view it?

11. You want to implement the following relationships while designing tables. How would you do it? a.) One-to-one b.) One-to-many c.) Many-to-many

12. Differentiate between DELETE and TRUNCATE.

13. What are the properties of the Relational tables?

14. Explain the following.

15. What do you mean by ACID?

16. Explain the following:

17. Differentiate between a Local and a Global temporary table?

18. Explain different types of Locks in SQL Server.

What are DMVs?

What are temp tables? What is the difference between global and local temp tables?

How are transactions used?

What is the difference between a clustered and a nonclustered index?

What are DBCC commands?

What is the difference between truncate and delete?

What does the NOLOCK query hint do?

What is a CTE?

What is a view? What is the WITH CHECK OPTION clause for a view?

What is a query execution plan?

What does the SQL Server Agent Windows service do?

What is the default port number for SQL Server?

What are triggers? How many triggers you can have on a table? How to invoke a trigger on demand?

Triggers - October 24, 2008 at 18:10 pm by RajmeetGhai

Describe triggers features and limitations.

Syntax for viewing, dropping and disabling triggers

Determine how to use the inserted and deleted pseudo tables.

Explain how to apply cascading referential integrity in place of triggers.

· [ON DELETE {NO ACTION} ]: If an attempt to delete a row is made, with a key referenced by foreign keys in existing rows in other tables, an error is raised and DELETE is rolled back.

· [ON UPDATE {NO ACTION } ]: If an attempt to update a row is made, with a key referenced by foreign keys in existing rows in other tables, an error is raised and UPDATE is rolled back.

· [ ON DELETE { CASCADE } ]: If an attempt to delete a row is made, with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are also deleted

· [ ON UPDATE { CASCADE } ]: If an attempt to update a row is made, with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are also Updated.

Explain trigger classes i.e. instead of and after trigger.

What are the instances when triggers are appropriate?

· When security is the top most priority. i.e. to allow unauthorized access

· When backups are essential

· When Maintenance is desired. Triggers can be fired when any error message is logged

· Keeping the database consistent.

· SQL Queries Interview Questions and Answers on "SQL Select" - Examples

· Database SQL Queries Interview Questions and answers on "SQL Order By"

· SQL Queries Interview Questions and Answers on "SQL Where Condition" - Examples

· SQL Queries Interview Questions and Answers on "SQL Wild Card Search" - Examples

· SQL Queries Interview Questions and Answers on "SQL Pattern Matching" - Examples

· SQL Queries Interview Questions and Answers on "SQL DATE Functions" - Examples

· SQL Queries Interview Questions and Answers on "SQL Escape Characters" - Examples

· SQL Queries Interview Questions and Answers on "SQL Group By Functions" - Examples

· SQL Queries Interview Questions and Answers on "SQL Mathematical Operations using Group By" - Examples

· SQL Queries Interview Questions and Answers on "SQL Joins" - Examples

· Advanced SQL Queries Interview Questions and Answers on "Top N Salary" - Examples

· SQL Queries Interview Questions and Answers on "SQL Union" - Examples

· "Advanced SQL Queries Interview Questions and Answers"

· SQL Queries Interview Questions and Answers on "SQL Table Scripts" - Examples

No comments:

Post a Comment

11. You want to implement the following relationships while designing tables. How would you do it?
a.) One-to-one
b.) One-to-many
c.) Many-to-many