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

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

Explain the set commands given below

1) set rowcount (Instruct the server to rturn only the first n rows of data)
2) set nocount on ( stops reporting the number of rows returned)
3) set noexec on ( parse and optimize, but don’t exec the query (used with show plan for looking at the plan without running a query)) showplan gives the final optimization plan for a query)
4) set statistics time on (requests the execution time)
Construct the Query using the Tables album and track of music database
album
Asin
Title
artist
price
release
label
Rank
B00000255F
Born to Run
Bruce Springsteen
13.98
1975-08-25
Sony
521
B000002UAU
Sgt. Pepper's Lonely Hearts Club Band
The Beatles
13.49
1967-06-01
Capitol
218
...
Track
Album
dsk
posn
Song
B00000255F
1
1
Thunder Road
B00000255F
1
2
Tenth Avenue Freeze-Out
B00000255F
1
3
Night
B00000255F
1
4
Backstreets
B00000255F
1
5
Born to Run
...
B000002UAU
1
1
Sgt. Pepper's Lonely Hearts Club Band
B000002UAU
1
2
With a Little Help from My Friends
B000002UAU
1
3
Lucy in the Sky With Diamonds
B000002UAU
1
4
Getting Better
B000002UAU
1
5
She's Leaving Home
...
A "title track" is where the song is the same as the title. Find the title
Tracks
select song "title tracks" from album,track where album.asin=track.album and album.title=track.song
Find the songs that appear on more than 2 albums. Include a count of the number of times each shows up
select song, count (album) from album, track where album.asin=track.album group by song having count(song)>2
A "good value" album is one where the price per track is less than 50 pence. Find the good value album - show the title, the price and the number of tracks
select title,price,count(song) from album,track where album.asin=track.album group by title having (price/count(song))<0.5
Wagner's Ring cycle has an imposing 173 tracks, Bing Crosby clocks up 101 tracks
List albums so that the album with the most tracks is first. Show the title and the number of tracks
select title, count(song) from album,track where album.asin=track.album group by title order by count(song) desc

Find the Top album of each company

select title ,label ,min(rank) from album group by label

Find the title and artist who recorded the song 'Alison'

Ans: select title,artist from album,track where track.song="Alison"

Explain timestamp datatype?

Construct the Query using the Tables album and track of music database

album
Asin	title	artist	price	release	label	Rank
B00000255F	Born to Run	Bruce Springsteen	13.98	1975-08-25	Sony	521
B000002UAU	Sgt. Pepper's Lonely Hearts Club Band	The Beatles	13.49	1967-06-01	Capitol	218
...

Track
Album	dsk	posn	Song
B00000255F	1	1	Thunder Road
B00000255F	1	2	Tenth Avenue Freeze-Out
B00000255F	1	3	Night
B00000255F	1	4	Backstreets
B00000255F	1	5	Born to Run
...
B000002UAU	1	1	Sgt. Pepper's Lonely Hearts Club Band
B000002UAU	1	2	With a Little Help from My Friends
B000002UAU	1	3	Lucy in the Sky With Diamonds
B000002UAU	1	4	Getting Better
B000002UAU	1	5	She's Leaving Home
...

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

1. count() Returns the number of rows found

2. having keyword should always follow the group by

3. A result set based on a query with a proper join condition is called a cartesian product

4. distinct requires an additional sorting step using a worktable

5. subquery always return single column of data

6. once you create a column and declare its datatype, you cannot change that datatype without dropping and recreating the table.

Ans: FTFTF(F)

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