Answer Posted / rakesh kumar jaiswal

CREATE TABLE Names
(
NameID INT IDENTITY(1, 1)
PRIMARY KEY CLUSTERED,
FName VARCHAR(32)
)
GO

SET NOCOUNT ON
INSERT Names(FName) VALUES('Aaron')
INSERT Names(FName) VALUES('Greg')
INSERT Names(FName) VALUES('Alex')
INSERT Names(FName) VALUES('Luan')
INSERT Names(FName) VALUES('John')
INSERT Names(FName) VALUES('Todd')
INSERT Names(FName) VALUES('Scott')
INSERT Names(FName) VALUES('Jess')
INSERT Names(FName) VALUES('Drew')
INSERT Names(FName) VALUES('Katherine')
INSERT Names(FName) VALUES('Paul')
GO

-- solution #1: nested top

SELECT TOP 1 FName
FROM
(
SELECT TOP 10 FName
FROM Names
ORDER BY FName
) sub
ORDER BY FName DESC

-- solution #2: NOT IN

SELECT TOP 1 FName
FROM Names WHERE FName NOT IN
(
SELECT TOP 9 FName
FROM Names
ORDER BY FName
)
ORDER BY FName

-- solution #3: derived count
-- this assumes FName is unique

SELECT FName
FROM Names
WHERE
(
SELECT COUNT(*)
FROM Names n2
WHERE n2.FName <= Names.FName
) = 10

-- solution #4: MAX

SELECT FName = MAX(FName) FROM
(
SELECT TOP 10 FName
FROM Names
ORDER BY FName
) sub

-- solution #5: relative fetch from cursor

-- yes, cursors are generally evil, but
-- sometimes you might be surprised

DECLARE FNames CURSOR
LOCAL STATIC READ_ONLY FOR
SELECT FName
FROM Names
ORDER BY FName

DECLARE @FName VARCHAR(32)

OPEN FNames

FETCH RELATIVE 10 FROM FNames INTO @FName

CLOSE FNames
DEALLOCATE FNames

SELECT FName = @FName

DROP TABLE Names
GO

What is proper subset of candidate key?

948

---

Write a SQL query to delete a table?

1080

---

What are cursors and when they are useful?

1017

---

What is query optimizer in sql server?

960

---

What is difference between stored procedure and user defined function?

1033

---

How do you rename a table in sql server?

940

---

What is the difference between online clustering and Offline clustering?

2357

---

What does this statement do @@rowcount?

927

---

How to connect ms access to sql servers through odbc?

971

---

Can sql servers link to other servers like oracle?

795

---

What is BCNF? How is it better than 2NF & 3NF?

964

---

What is service broker? : sql server database administration

942

---

Do you know what is user defined datatypes and when you should go for them?

909

---

What is normalization of database? What are its benefits?

963

---

What is table valued function and scalar valued functions?

990

---