SQL Interview Questions

Why is a UNION ALL faster than a UNION?

UNION ALL faster than a UNION because for union operation server needs to remove the duplicate values but for union all its not. Thats why the UNOIN ALL is fater than UNION Operation. It is recommended that if you know that the union set operation never returns duplicate values than you must use UNION ALL instead of UNION.

How many types of data models are there?

There are no standards in this area. Authors and theorists make it up as they go. The entity-relationship model (ER) has hundreds of derivitives (bachman, chen, ibm, IDEF1x etc.). the most popular of the OO models is Unified Modeling Language (UML). Actually UML and IDEF1x are closest to becoming a standard that can support software products. Rational already has products and IDEF1x is the language of ERwin.
Don't be fooled by these variations. They all represent the same things, you have to be very careful that you understand all of the non-standard symbols or you will surely make mistakes in interpreting what the pictures mean.

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's the difference between a primary key and a unique key?

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

Define candidate key, alternate key, 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 are defaults? Is there a column to which a default can't be bound?
A default is a value that will be used by a column, if no value is supplied to that column while inserting data. IDENTITY columns and timestamp columns can't have defaults bound to them. See CREATE DEFUALT in books online.

Whar is an index? What are the types of indexes? How many clustered indexes can be created on a table? I create 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 craete a clustered index 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 it's 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 t ime, 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 are cursors? Explain different types of cursors. What are the disadvantages of cursors? How can you avoid cursors?

Cursors allow row-by-row prcessing of the resultsets.

Types of cursors: Static, Dynamic, Forward-only, Keyset-driven. See books online for more information.

Disadvantages of cursors: Each time you fetch a row from the cursor, it results in a network roundtrip, where as a normal SELECT query makes only one rowundtrip, however large the resultset is. Cursors are also costly because they require more resources and temporary storage (results in more IO operations). Furthere, there are restrictions on the SELECT statements that can be used with some types of cursors.

Most of the times, set based operations can be used instead of cursors. Here is an example:

If you have to give a flat hike to your employees using the following criteria:

Salary between 30000 and 40000 -- 5000 hike
Salary between 40000 and 55000 -- 7000 hike
Salary between 55000 and 65000 -- 9000 hike

In this situation many developers tend to use a cursor, determine each employee's salary and update his salary according to the above formula. But the same can be achieved by multiple update statements or can be combined in a single UPDATE statement as shown below:

UPDATE tbl_emp SET salary =
CASE WHEN salary BETWEEN 30000 AND 40000 THEN salary + 5000
WHEN salary BETWEEN 40000 AND 55000 THEN salary + 7000
WHEN salary BETWEEN 55000 AND 65000 THEN salary + 10000
END


Another situation in which developers tend to use cursors: You need to call a stored procedure when a column in a particular row meets certain condition. You don't have to use cursors for this. This can be achieved using WHILE loop, as long as there is a unique key to identify each row. For examples of using WHILE loop for row by row processing,

What is a join and explain different types of joins?

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 JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.

What is a Stored Procedure?

Its nothing but a set of T-SQL statements combined to perform a single task of several tasks. Its basically like a Macro so when you invoke the Stored procedure, you actually run a set of statements.

What is the basic difference between clustered and a non-clustered index?

The difference is that, Clustered index is unique for any given table and we can have only one clustered index on a table. The leaf level of a clustered index is the actual data and the data is resorted in case of clustered index. Whereas in case of non-clustered index the leaf level is actually a pointer to the data in rows so we can have as many non-clustered indexes as we can on the db.

What are cursors?

Well cursors help us to do an operation on a set of data that we retreive by commands such as Select columns from table. For example : If we have duplicate records in a table we can remove it by declaring a cursor which would check the records during retreival one by one and remove rows which have duplicate values.

Which TCP/IP port does SQL Server run on?

SQL Server runs on port 1433 but we can also change it for better security.

Can we use Truncate command on a table which is referenced by FOREIGN KEY?

No. We cannot use Truncate command on a table with Foreign Key because of referential integrity.


What is the use of DBCC commands?


DBCC stands for database consistency checker. We use these commands to check the consistency of the databases, i.e., maintenance, validation task and status checks.

What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?

Having Clause is basically used only with the GROUP BY function in a query. WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.

What is a Linked Server?

Linked Servers is a concept in SQL Server by which we can add other SQL Server to a Group and query both the SQL Server dbs using T-SQL Statements.

Can you link only other SQL Servers or any database servers such as Oracle?

We can link any server provided we have the OLE-DB provider from Microsoft to allow a link. For Oracle we have a OLE-DB provider for oracle that microsoft provides to add it as a linked server to the sql server group.

How do you troubleshoot SQL Server if its running very slow?

First check the processor and memory usage to see that processor is not above 80% utilization and memory not above 40-45% utilization then check the disk utilization using Performance Monitor, Secondly, use SQL Profiler to check for the users and current SQL activities and jobs running which might be a problem. Third would be to run UPDATE_STATISTICS command to update the indexes.

What is log shipping?

Can we do logshipping with SQL Server 7.0 - Logshipping is a new feature of SQL Server 2000. We should have two SQL Server - Enterprise Editions. From Enterprise Manager we can configure the logshipping. In logshipping the transactional log file from one server is automatically updated into the backup database on the other server. If one server fails, the other server will have the same db and we can use this as the DR (disaster recovery) plan.

Let us say the SQL Server crashed and you are rebuilding the databases including the master database what procedure to you follow?

For restoring the master db we have to stop the SQL Server first and then from command line we can type SQLSERVER .m which will basically bring it into the maintenance mode after which we can restore the master db.

What is BCP? When do we use it?

BulkCopy is a tool used to copy huge amount of data from tables and views. But it won’t copy the structures of the same.

What is the difference between oracle,sql and sql server ?

Oracle is based on RDBMS.
SQL is Structured Query Language.
SQL Server is another tool for RDBMS provided by MicroSoft.
why you need indexing ? where that is stroed and what you mean by schema object? For what purpose we are using view?

We cant create an Index on Index.. Index is stoed in user_index table.Every object that has been created on Schema is Schema Object like Table,View etc.If we want to share the particular data to various users we have to use the virtual table for the Base table...So tht is a view.

indexing is used for faster search or to retrieve data faster from various table. Schema containing set of tables, basically schema means logical separation of the database. View is crated for faster retrieval of data. It's customized virtual table. we can create a single view of multiple tables. Only the drawback is..view needs to be get refreshed for retrieving updated data.

Difference between Store Procedure and Trigger?

we can call stored procedure explicitly.
but trigger is automatically invoked when the action defined in trigger is done.
ex: create trigger after Insert on
this trigger invoked after we insert something on that table.
Stored procedure can't be inactive but trigger can be Inactive.
Triggers are used to initiate a particular activity after fulfilling certain condition.It need to define and can be enable and disable according to need.

What is the advantage to use trigger in your PL?

Triggers are fired implicitly on the tables/views on which they are created. There are various advantages of using a trigger. Some of them are:

Suppose we need to validate a DML statement(insert/Update/Delete) that modifies a table then we can write a trigger on the table that gets fired implicitly whenever DML statement is executed on that table.
Another reason of using triggers can be for automatic updation of one or more tables whenever a DML/DDL statement is executed for the table on which the trigger is created.
Triggers can be used to enforce constraints. For eg : Any insert/update/ Delete statements should not be allowed on a particular table after office hours. For enforcing this constraint Triggers should be used.
Triggers can be used to publish information about database events to subscribers. Database event can be a system event like Database startup or shutdown or it can be a user even like User loggin in or user logoff.
What the difference between UNION and UNIONALL?

Union will remove the duplicate rows from the result set while Union all does'nt.

What is the difference between TRUNCATE and DELETE commands?

Both will result in deleting all the rows in the table .TRUNCATE call cannot be rolled back as it is a DDL command and all memory space for that table is released back to the server. TRUNCATE is much faster.Whereas DELETE call is an DML command and can be rolled back.

Which system table contains information on constraints on all the tables created ?
yes,
USER_CONSTRAINTS,
system table contains information on constraints on all the tables created

Explain normalization ?
Normalisation means refining the redundancy and maintain stablisation. there are four types of normalisation :
first normal forms, second normal forms, third normal forms and fourth Normal forms.

How to find out the database name from SQL*PLUS command prompt?
Select * from global_name;
This will give the datbase name which u r currently connected to.....

What is the difference between SQL and SQL Server ?

SQLServer is an RDBMS just like oracle,DB2 from Microsoft
whereas
Structured Query Language (SQL), pronounced "sequel", is a language that provides an interface to relational database systems. It was developed by IBM in the 1970s for use in System R. SQL is a de facto standard, as well as an ISO and ANSI standard. SQL is used to perform various operations on RDBMS.

What is diffrence between Co-related sub query and nested sub query?

Correlated subquery runs once for each row selected by the outer query. It contains a reference to a value from the row selected by the outer query.

Nested subquery runs only once for the entire nesting (outer) query. It does not contain any reference to the outer query row.

For example,

Correlated Subquery:

select e1.empname, e1.basicsal, e1.deptno from emp e1 where e1.basicsal = (select max(basicsal) from emp e2 where e2.deptno = e1.deptno)

Nested Subquery:

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

WHAT OPERATOR PERFORMS PATTERN MATCHING?
Pattern matching operator is LIKE and it has to used with two attributes

1. % and

2. _ ( underscore )

% means matches zero or more characters and under score means mathing exactly one character

1)What is difference between Oracle and MS Access?
2) What are disadvantages in Oracle and MS Access?
3) What are feratures&advantages in Oracle and MS Access?

Oracle's features for distributed transactions, materialized views and replication are not available with MS Access. These features enable Oracle to efficiently store data for multinational companies across the globe. Also these features increase scalability of applications based on Oracle.

What is database?
A database is a collection of data that is organized so that itscontents can easily be accessed, managed and updated. open this url : http://www.webopedia.com/TERM/d/database.html

What is cluster.cluster index and non cluster index ?
Clustered Index:- A Clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table may have only one clustered index.Non-Clustered Index:- A Non-Clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows in the disk. The leaf nodes of a non-clustered index does not consists of the data pages. instead the leaf node contains index rows.

How can i hide a particular table name of our schema?
you can hide the table name by creating synonyms.

e.g) you can create a synonym y for table x

create synonym y for x;

What is difference between DBMS and RDBMS?
The main difference of DBMS & RDBMS is

RDBMS have Normalization. Normalization means to refining the redundant and maintain the stablization.
the DBMS hasn't normalization concept.

What are the advantages and disadvantages of primary key and foreign key in SQL?

Primary key

Advantages

1) It is a unique key on which all the other candidate keys are functionally dependent

Disadvantage

1) There can be more than one keys on which all the other attributes are dependent on.

Foreign Key

Advantage

1)It allows refrencing another table using the primary key for the other table

Which date function is used to find the difference between two dates?
datediff

for Eg: select datediff (dd,'2-06-2007','7-06-2007')

output is 5

No comments: