--Getting Max Salary whole Record
SELECT EmpID, EmpName, Salary FROM dbo.Emp WHERE Salary IN (SELECT MAX(Salary) as Salary From dbo.Emp)
--OR
SELECT EmpID, EmpName, MAX(Salary) FROM dbo.Emp
GROUP BY EmpID, EmpName, Salary
HAVING MAX(Salary) NOT IN
(SELECT Salary FROM dbo.Emp WHERE Salary Not IN (SELECT Max(Salary) as MaxSal FROM dbo.Emp) )
-- Getting Max Salary
SELECT Max(Salary) as MaxSal FROM dbo.Emp
-- Getting Second Max Salary using Not IN
SELECT Max(Salary) as SecondMaxSal FROM dbo.Emp
WHERE Salary NOT IN ( SELECT Max(Salary) FROM dbo.Emp )
-- Getting Second Max Salary using operator
SELECT (SELECT Max(Salary) FROM dbo.Emp ) as MaxSal, Max(Salary) as SecondMaxSal FROM dbo.Emp
WHERE Salary < (select max(Salary) from Emp)
--Finding rows that are duplicates:
SELECT Salary, COUNT(Salary) as SalaryCount FROM dbo.Emp group by Salary HAVING COUNT(Salary) > 1
--Finding rows that occur exactly once:
SELECT e1.EmpName, e1.Salary, A.SalaryCount FROM dbo.Emp e1 INNER JOIN
(SELECT B.Salary, COUNT(B.Salary) as SalaryCount FROM dbo.Emp B group by B.Salary HAVING COUNT(B.Salary) = 1) A
ON e1.Salary = A.Salary
Explain different types of cursors.
Answer
Different types of cursors:
Implicit cursors: these cursors are invoked implicitly. User need not create, open, fetch or close the cursor.
Explicit cursors: these cursors are not invoked implicitly. User needs to create, open, fetch or close the cursor.
Static Cursor: Stores a complete copy of the result set. Used mostly where scrolling is required. Static cursors don’t support updates.
Forward – only cursors: This cursor supports updates but not scrolling. It supports only fetching serially. Rows are not retrieved from the database until they are fetched.
Forward – only cursors / Read only cursor: These are the fastest of the cursors and cannot be updated. They cannot be created on query that returns only read only columns.
Key set driven: It is a scrollable cursor that cannot be updated. These cursors are controlled by a set of physical identifiers called as key set. The keyset is built in a temporary table when the cursor is opened.
What are the disadvantages of cursors?
Answer
Disadvantages of cursors
§ Uses more resources because Each time you fetch a row from the cursor, it results in a network roundtrip
§ There are restrictions on the SELECT statements that can be used.
§ Because of the round trips, performance and speed is slow
How can you avoid cursors?
Answer
The main purpose that cursors fulfill is traversing of the row set. Cursors can be best avoided by:
a. Using the SQL while loop: Using a while loop we can insert the result set into the temporary table.
b. User defined functions: Cursors are sometimes used to perform some calculation on the resultant row set. This cam also be achieved by creating a user defined function to suit the needs
Export Gridview data to Excel
ii) Set Gridview Allowpaging property to "False"
Write the below code to export the entire Gridview data to Excel sheet
private void ExportGridView()
{
string style = @"<style> .text { mso-number-format:\@; } </style> ";
string attachment = "attachment; filename=Contacts.xls";
Response.ClearContent();
Response.AddHeader("content-disposition", attachment);
Response.ContentType = "application/ms-excel";
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
// Create a form to contain the grid
HtmlForm frm = new HtmlForm();
VerificationTrackingGridView.Parent.Controls.Add(frm);
frm.Attributes["runat"] = "server";
frm.Controls.Add(VerificationTrackingGridView);
frm.RenderControl(htw);
Response.Write(style);
Response.Write(sw.ToString());
Response.End();
}
A New feature in SQL Server is Time delay to execute a T-SQL Query
SQL Server will wait for perticular time to excute the query.
Ex :
i) SELECT GETDATE() CurrentTime
WAITFOR DELAY '00:00:20' ---- 5 Second Delay
SELECT GETDATE() CurrentTime
Ex:
ii)
DECLARE @delaytime DATETIME
SET @delaytime = DATEADD(s,20,GETDATE())
SELECT GETDATE() CurrentTime
WAITFOR TIME @delaytime
SELECT GETDATE() CurrentTime
Transact-SQL provides the following ranking functions:
-RANK
-NTILE
-DENSE_RANK
-ROW_NUMBER
Rank() - Applies to monotonically increasing number for each value in the set
Row_Number() - simply assigns sequential numbering to the records of a result-set or to the records within groups of a result-set
Rank() and Row_Number differ only if there are ties.
Dense_Rank() - Method the rank is increasing number for each value in the set.
NTILE(n)- Which splits the records into partition based on the Column in the Over() and assigns the Rank for the partition.
OVER clause is required in all the ranking functions and with that you specify the partitioning and ordering of records before the ranking functions are evaluated. If you don't specify it, you will get an error similar to "Incorrect syntax near 'ROW_NUMBER', expected 'OVER'."
ORDER BY clause is mandatory and if you don't specify it you will get an error similar to "The ranking function "ROW_NUMBER" must have an ORDER BY clause.". With this clause you specify a column or a set of columns which will be used to order the records within a result-set or within record groups/partitions of a result-set. Please note you can specify only those columns which are being made available by the FROM clause of the query.
Please find the below Image for the example:
Click to get full Image
Click to get full Image
