Friday, 23 September 2016

How to drop or delete all tables from database with one SQL query

How to drop all tables from database with one SQL query?

You can write query like this:

USE Databasename

SELECT  'DROP TABLE ' + name + ';'
FROM    sys.tables
You can copy and paste into a new SSMS window to run it.

Tuesday, 6 September 2016

How to Find Nth/Second Highest and Lowest Salary in SQL


We will explore the answer with a series of scenarios and questions asked in relation to finding the highest, lowest, and nth highest salary.

+----+---------+-----------+
| Id | Amount  |  Name     |
+----+---------+-----------+
| 1  | 1100    |   Peter   |
| 2  | 1200    |   Sachin  |
| 3  | 1300    |   Roberts |
| 4 | 1400   |   Shyam  |
+----+---------+-----------+

suppose table name is tbl_TeacherPay

(A) Find the Employee with the Highest Salary

SELECT Name, Amount
FROM
(
Select Name, Amount, ROW_NUMBER() OVER(Order by Amount Desc) as Salary_Order
from   tbl_TeacherPay
) DT


WHERE DT. Salary_Order = 1 ;

(B)Finding the Employee with the Nth Highest Salary

So Query is

SELECT Name, Amount
FROM
(
Select Name, Amount, ROW_NUMBER() OVER(Order by Amount Desc) as Salary_Order
from   tbl_TeacherPay
) DT
WHERE DT. Salary_Order = 3 ;


Result

+----+---------+-----------+
| Id | Amount  |  Name     |
+----+---------+-----------+
| 1  | 1200    |  Sachin   |
+----+---------+-----------+



(C)Finding the Employee with the Lowest Salary

SELECT Name, Amount
FROM
(
Select Name, Amount, ROW_NUMBER() OVER(Order by Amount asc) as Salary_Order
from   tbl_TeacherPay
) DT
WHERE DT. Salary_Order = 1 ;


(D)Find the Employee with the Highest Salary When There Is a Tie (Two employees both have the highest salary and the number is the same)

SELECT Name, Amount
FROM
(
Select Name, Amount, ROW_NUMBER() OVER(Order by Amount desc) as Salary_Order
from   tbl_TeacherPay
) DT
WHERE DT. Salary_Order = 1 ;


Second Highest Salary in MySQL and SQL Server

Write a SQL query to get the second highest salary from the table.
+----+---------+
| Id | Amount  |
+----+---------+
| 1  | 1100    |
| 2  | 1200    |
| 3  | 1300    |
+----+---------+

For example, given the above a table , the second highest salary is 1200. If there is no second highest salary, then the query should return NULL.
suppose table name is tbl_TeacherPay


So Query is


SELECT TOPAmount FROM
( SELECT TOP (2) Amount FROM tbl_TeacherPay
ORDER BY AmountDESC)
AS tbl_TeacherPay order by Amount asc


Result

+----+---------+
| Id | Amount  |
+----+---------+
| 1  | 1200    |
+----+---------+