Session 1

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

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

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

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

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

6. Write a query to find the duplicate rows from a table?
Select name, count(*) from tablename group by name having count(*)>1

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

8. 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)

9. Write a query to access a particular number of rows in a table?

set rowcount 10
go
set rowcount 0