How many rows may be retrieved by the select statement in the following SQL code samples?

 create table t1  (k int unique)
               insert t1 values (rand()*1000)
               insert t1 values (rand()*1000)
               insert t1 values (rand()*1000)
               [...1000 additional inserts...]
               select * from t1 where k = rand()*1000
Ans: 0 or 1

What will be the output of the following query?

Suppose I have a table with following column

E_Id

---------------

BLL12

BRR

BDGT8

NW23L

KO9G0

B___B

___BN

BSNMA

___12

BNG

c

“select E_Id from table where E_Id like ‘___’”

Ans: BRR

% -0 or more

_ - any single chr

[]- any single chr listed in the bracket

How do you find the name of the employees those have missed more than 5 days of work in last 30 days

There are two tables master is Employees and the detail is Attendance. The Employee_ID field is common to both tables. How do you find the name of the employees those have missed more than 5 days of work in last 30 days

Select name from Employees a where a.id in (select b.id from attendance b where a.id=b.id and date between (lastmonth+1 ,lastmonth) presence=0 group by id having count(presence)>4)

What command do we use to rename a database?

Ans: sp_renamedb ‘oldname’ , ‘newname’
Well sometimes sp_renamedb may not work you know because if some one is using the db it will not accept this command so what do you think you can do in such cases? - In such cases we can first bring to db to single user using sp_dboptions and then we can rename that db and then we can rerun the sp_dboptions command to remove the single user mode.

Give me the Global variable names for the description given below

1. Error number reported for last SQL statement ( @@error)
2. Current transaction mode (chained or unchained)(@@tranchained)
3. Status of prevoius fetch statement in a cursor(@@sqlstatus)
4. Transaction nesting level(@@trancount)
5. Current process ID(@@spid)

What’s the difference between DELETE TABLE and TRUNCATE TABLE commands?

Ans: DELETE TABLE is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow.
TRUNCATE TABLE also deletes all the rows in a table, but it won’t log the deletion of each row, instead it logs the deallocation of the data pages of the table, which makes it faster.
Of course, TRUNCATE TABLE can be rolled back.
TRUNCATE TABLE is functionally identical to DELETE statement with no WHERE clause: both remove all rows in the table.
But TRUNCATE TABLE is faster and uses fewer system and transaction log resources than DELETE. The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table’s data, and only the page deallocations are recorded in the transaction log. TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain.
The counter used by an identity for new rows is reset to the seed for the column. If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement.
You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint; instead, use DELETE statement without a WHERE clause. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.
TRUNCATE TABLE may not be used on tables participating in an indexed view

What is default? Is there a column to which a default can’t be bound?

Ans: 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 DEFAULT in books online

Write a query to find the duplicate rows from a table?

Select name, count(*) from tablename group by name having count(*)>1

What is the advantage of stored procedure?

Ans: Faster Execution: after first exec it become memory resident and do not need to be reparsed, recompiled
Reduced network traffic
Modular programming
Reduced operator error-less info to pass

What is the difference between a sub-query and a correlated sub-query?

Ans: A subquery is a query that SQL Server must evaluate before it can process the main query. It doesn’t depend on a value in the outer subquery.
A correlated subquery is the one that depends on a value in a join clause in the outer subquery