SQL Interview Questions 2


Back to Notes on .NET

C# General Interview Questions

C# Questions on properties, events etc

Questions on XML, ADO.NET etc

C# Rapid Fire Questions 1

C# Rapid Fire Questions 2

SQL Interview Questions 1

SQL Interview Questions 2

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

26. Solve the Query: Customer Table

Name  Phoneno
 Abc       123
 Pqr        234
 Rst        235
 Xyz        997


a.Write a query to find out customers and count of phno
with more than one phnumber

Ans.Select  name, Count(Phoneno) from customer having
       count(Phoneno)>1

b.Add a row pqr  phno 234. Now write a query to get the
count of phno and name with no duplicate values

Ans. Select [Name],phoneno,count(phoneno) as countofPNo
        from customer group by [Name],phoneno

27.Explain about DTS.
Ans. Data Transformation Services (DTS) provides the
functionality to import, export, and transform data
between SQL Server and any OLE DB, ODBC, or text file
format. Using DTS, it is possible to:

Build data warehouses and data marts in Microsoft SQL
Server by importing and transferring data from multiple
heterogeneous sources interactively or automatically on
a regularly scheduled basis.

Create custom transformation objects that can be
integrated into third-party products.

Access applications using third-party OLE DB providers.
This allows applications, for which an OLE DB provider exists,
to be used as sources and destinations of data.
DTS also provides support for:
•High-speed nonlogged inserts (bcp) into SQL Server version
7.0.
•Creating customized transformations.
•Transferring complete database objects between source and
destination SQL Server 7.0 data sources. The Transfer SQL
Server Objects task can be used to transfer all of the
metadata and data for some or all of the objects in one SQL
Server 7.0 database to another SQL Server 7.0 database.

For example, the Transfer SQL Server Objects task can be used to move a table with all of its associated index, constraint, rule, default, and trigger definitions and the existing rows in the table. The Transfer SQL Server Objects task also can be used to transfer the definitions of objects such as views and stored procedures.

28.What is OpenRowset?
Ans.It includes all connection information necessary to
access remote data from an OLE DB data source.

This method is an alternative to accessing tables in a linked server and is a one-time, ad hoc method of connecting and accessing remote data using OLE DB. The OPENROWSET function can be referenced in the FROM clause of a query as though it is a table name. The OPENROWSET function can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENROWSET returns only the first one.
Ex:
SELECT a.*  FROM OPENROWSET   ('SQLOLEDB','seattle1';'sa';'MyPass',
'SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname')
 AS a

29.What are stored procedures?
Ans.When you use Transact-SQL programs, two methods are
available for storing and executing the programs. You can
store the programs locally and create applications that send
the commands to SQL Server and process the results, or you can store the programs as stored procedures in SQL Server and create applications that execute the stored procedures and process the results. Stored procedures in SQL Server are similar to procedures in other programming languages in that they can:

Accept input parameters and return multiple values in the form of output parameters to the calling procedure or batch.

Contain programming statements that perform operations in the database, including calling other procedures.

Return a status value to a calling procedure or batch to
indicate success or failure (and the reason for failure).
 
You can use the Transact-SQL EXECUTE statement to run a stored procedure. Stored procedures are different from functions in that they do not return values in place of their names and they cannot be used directly in an expression.The benefits of using stored procedures in SQL Server rather than Transact-SQL programs stored locally on client computers are:

They allow modular programming.
 You can create the procedure once, store it in the
database, and call it any number of times in your program.
Stored procedures can be created by a person who specializes
in database programming, and they can be modified independently of the program source code.

They allow faster execution. If the operation requires a large
amount of Transact-SQL code or is performed repetitively,
stored procedures can be faster than batches of Transact-SQL code. They are parsed and optimized when they are created, and an in-memory version of the procedure can be used after the procedure is executed the first time. Transact-SQL statements repeatedly sent from the client each time they run are compiled and optimized every time they are executed by SQL Server.

They can reduce network traffic. An operation requiring hundreds of lines of Transact-SQL code can be performed through a single statement that executes the code in a procedure, rather than by sending hundreds of lines of code over the network.

They can be used as a security mechanism. Users can be granted permission to execute a stored procedure even if they do not have permission to execute the procedure’s statements directly.

A SQL Server stored procedure is created with the Transact-SQL CREATE PROCEDURE statement and can be modified with the ALTER PROCEDURE statement. The stored procedure definition contains two primary components: the specification of the procedure name and its parameters, and the body of the procedure, which contains Transact-SQL statements that perform the procedure’s operations.
 
Extended Stored Procedures
 Extended stored procedures allow you to create your
own external routines in a programming language such as C.
The extended stored procedures appear to users as normal stored procedures and are executed in the same way. Parameters can be passed to extended stored procedures, and they can return results and return status. Extended stored procedures can be used to extend the capabilities of Microsoft® SQL Server™. Extended stored procedures are dynamic link libraries (DLLs) that SQL Server can dynamically load and execute.
Extended stored procedures run directly in the address space of SQL Server and are programmed using the SQL Server Open Data Services API.
After an extended stored procedure has been written,
members of the sysadmin fixed server role can register the
extended stored procedure with SQL Server and then grant
permission to other users to execute the procedure. Extended stored procedures can be added only to the master database

Remote Stored Procedure
A collection of SQL statements and optional control-of-flow
statements stored under a name on a remote server. Remote
stored procedures can be called by clients or SQL Server

Distributed Queries
Distributed queries access data from multiple heterogeneous
data sources, which can be stored in either the same or different computers. SQL Server supports distributed queries
by using OLE DB, the Microsoft specification of an appli
cation programming interface (API) for universal data access.

Distributed queries provide SQL Server users with access to:
•Distributed data stored in multiple computers that are running SQL Server.
•Heterogeneous data stored in various relational and
non-relational data sources that can be accessed using an
OLE DB provider. OLE DB providers expose their data in tabular objects called rowsets. SQL Server version 7.0 allows rowsets from OLE DB providers to be referenced in Transact-SQL statements as if they were a SQL Server table.

30. Solve the Query

       Name         Tel
        ABC          100
        DEF           200
        HIG            300
        ABC           100
        DEF            400
        PQR            500
Write a query to list all the customers and the no. of telephones they have , only if they have more than one unique telephone no   

Ans. Select Name,count(phoneno) as noofPhoneno from        customers group by Name having noofPhoneno >      1

31. Solve Query. Consider the following table:
     Sernum          Day  Temp.
        1                Mon      10
        2                Tue       12
        3                Wed        9
        4                Thurs     15
Write a query to list a new column with the difference in
temp of the days Mon and Tue,Tue and Wed and soon.
(Don't use cursors)

Ans.
 Select a.srno,b.srno,a.[day],b.[day],a.[temp],b.[temp],
 (a.[temp]-b.[temp])AS DiffinTemp from diff a
 inner join diff b on  a.srno=b.srno-1

32. Consider the tables
Team Table
TeamId    TeamName
     1       Team1
     2        Team2

PlayerTable       

 PlayId   Name
      1   Name1
      2   Name2

TeamPlayer

TeamId   PlayerId
 1               1
 2               1
 1               2
Write a query to get Team name and Player name

Ans.
 Select c.teamname,d.playername from
  (Select a.teamid,a.teamname,b.playerid from
    team a inner join teamplayer b on     a.teamid=b.teamid)
     as c inner join player d on
      c.playerid=d.playerid

33.Different ways of getting count of rows from a table
in queryanalyser *******
Ans. Select distinct count(*)

34. What is sp_addlinkedserver
Ans. Creates a linked server, which allows access to
distributed, heterogeneous queries against OLE DB data
sources. After creating a linked server with
sp_addlinkedserver, this server can then execute distributed
queries. If the linked server is defined as SQL Server, remote
stored procedures can be executed.

 A linked server configuration allows SQL Server to
execute commands against OLE DB data sources on different
servers.

Linked servers offer these advantages:
Remote server access.
The ability to issue distributed queries, updates, commands,
and transactions on heterogeneous data sources across the
enterprise. Freedom from the need to address diverse data
sources differently.

35. What are the different types of Locks?
Ans. There are three main types of locks that SQL Server
6.5 uses:
       
 Shared locks
 Update locks
 Exclusive locks

Shared locks are used for operations that do not change
or update data, such as a SELECT statement.

Update locks are used when SQL Server intends to modify a
page, and later promotes the update page lock to an exclusive
page lock before actually making the changes.

Exclusive locks are used for the data modification operations,  such as UPDATE, INSERT, or DELETE.

Shared locks are compatible with other Shared locks or Update locks.

Update locks are compatible with Shared locks only.

Exclusive locks are not compatible with other lock types.

Let me to describe it on the real example. There are four
processes, which attempt to lock the same page of the same
table. These processes start one after another, so Process1 is the first process, Process2 is the second process and so on.

Process1 : SELECT
Process2 : SELECT
Process3 : UPDATE
Process4 : SELECT

Process1 sets the Shared lock on the page, because there
are no another locks on this page.
Process2 sets the Shared lock on the page, because Shared
locks are compatible with other Shared locks.
Process3 wants to modify data and wants to set Exclusive
lock, but it cannot make it before Process1 and Process2
will be finished, because Exclusive lock is not compatible
with other lock types. So, Process3 sets Update lock.
Process4 cannot set Shared lock on the page before Process3 will be finished. So, there is no Lock starvation. Lock starvation occurs when read transactions can monopolize a table or page, forcing a write transaction to wait indefinitely.
So, Process4 waits before Process3 will be finished.
After Process1 and Process2 were finished, Process3 transfer
Update lock into Exclusive lock to modify data. After Process3
was finished, Process4 sets the Shared lock on the page to
select data.

Locking optimizer hints
There are six Locking optimizer hints in SQL Server 7.0:

NOLOCK
HOLDLOCK
UPDLOCK
TABLOCK
PAGLOCK
TABLOCKX
READCOMMITTED
READUNCOMMITTED
REPEATABLEREAD
SERIALIZABLE
READPAST
ROWLOCK

NOLOCK is also known as "dirty reads". This option directs SQL Server not to issue shared locks and not to honor exclusive locks. So, if this option is specified, it is possible to read an uncommitted transaction. This results in higher concurrency and in lower consistency.

HOLDLOCK directs SQL Server to hold a shared lock until
completion of the transaction in which HOLDLOCK is used.
You cannot use HOLDLOCK in a SELECT statement that includes the FOR BROWSE option. HOLDLOCK is equivalent to SERIALIZABLE.

UPDLOCK instructs SQL Server to use update locks instead of shared locks while reading a table and holds them until the
end of the command or transaction.

TABLOCK takes a shared lock on the table that is held until
the end of the command. If you also specify HOLDLOCK, the lock is held until the end of the transaction.

PAGELOCK is used by default. Directs SQL Server to use shared page locks.

TABLOCKX takes an exclusive lock on the table that is held
until the end of the command or transaction.

READCOMMITTED
Perform a scan with the same locking semantics as a transaction running at the READ COMMITTED isolation level. By default, SQL Server operates at this isolation level.

READUNCOMMITTED
Equivalent to NOLOCK.

REPEATABLEREAD
Perform a scan with the same locking semantics as a transaction running at the REPEATABLE READ isolation level. 

SERIALIZABLE
Perform a scan with the same locking semantics as a transaction running at the SERIALIZABLE isolation level. Equivalent to HOLDLOCK.

READPAST
 Skip locked rows. This option causes a transaction to
skip over rows locked by other transactions that would ordinarily appear in the result set, rather than block the transaction waiting for the other transactions to release their locks on these rows.
The READPAST lock hint applies only to transactions operating at READ COMMITTED isolation and will read only past row-level locks.
Applies only to the SELECT statement.
 You can only specify the READPAST lock in the READ
COMMITTED or REPEATABLE READ isolation levels.

ROWLOCK
Use row-level locks rather than use the coarser-grained page
and table-level locks.

You can specify one of these locking options in a SELECT statement.

This is the example:
SELECT au_fname FROM pubs..authors (holdlock)
 

Lock Escalation
You can customize locking by setting Lock Escalation level. The Lock Escalation level determines, when SQL Server applies table locks instead of page locks, and it affects all users of
SQL Server. So it's escalation from the page to the table level locking.

There are three Lock Escalation options:
LE threshold maximum
LE threshold minimum
LE threshold percent

LE threshold maximum is the maximum number of page locks to hold before escalating to a table lock. The default value is 200.

LE threshold minimum is the minimum number of page locks required before escalating to a table lock. The default value is 20.

LE threshold percent is the percentage of page locks needed on a table before escalating to a table lock. The default value is 0, it means that a table lock will be occur only when the LE threshold maximum will be exceeded.

You can configure Lock Escalation levels by using the
sp_configure system stored procedure.
This is the example to set LE threshold maximum to 250:

EXEC sp_configure 'LE threshold maximum', 250
RECONFIGURE WITH OVERRIDE


Deadlocks
Deadlock occurs when two users have locks on separate objects and each user wants a lock on the other's object.
 For example, User1 has a lock on object "A" and wants a
lock on object "B" and User2 has a lock on object "B" and wants a lock on object "A".

 You can decide which connection will be the candidate
for deadlock victim by using SET DEADLOCK_PRIORITY. In other case, SQL Server selects the deadlock victim by choosing the process that completes the circular chain of locks.
So, in a multiuser situation, your application should check the
message 1205 to indicate that the transaction was rolled back, and if it's so, restart the transaction.

Note. To reduce the chance of a deadlock, you should minimize the size of transactions and transaction times.

36. What's a Primary Key?
Ans.The column or combination of columns that uniquely identifies one row from any other row in a table. A primary key (PK) must be nonnull and must have a unique index.
A primary key is commonly used for joins with foreign keys
(matching nonprimary keys) in other tables.
Primary Key creates Cluster Index.