A. Write a query to delete duplicate records in SQL SERVER
B. Write a query to find 3rd Highest Salary in EMP Table
-- Create Table Emp (EmpID Int Identity Primary Key
,Salary Decimal(15,4), DeptID char(4))
/*
Insert Into Emp(Salary, DeptID) Values(3500, 'DEP1')
Insert Into Emp(Salary, DeptID) Values(4700, 'DEP1')
Insert Into Emp(Salary, DeptID) Values(5100, 'DEP1')
Insert Into Emp(Salary, DeptID) Values(7050, 'DEP1')
Insert Into Emp(Salary, DeptID) Values(4900, 'DEP2')
Insert Into Emp(Salary, DeptID) Values(7350, 'DEP2')
Insert Into Emp(Salary, DeptID) Values(3700, 'DEP2')
Insert Into Emp(Salary, DeptID) Values(2900, 'DEP3')
Insert Into Emp(Salary, DeptID) Values(9600, 'DEP3')
*/
--Sample Values in the EMP Table /*
EmpPID Salary DeptID
1 3500.0000 DEP1
2 4700.0000 DEP1
3 5100.0000 DEP1
4 7050.0000 DEP1
5 4900.0000 DEP2
6 7350.0000 DEP2
7 3700.0000 DEP2
8 2900.0000 DEP3
9 9600.0000 DEP3
*/
-- *******************************************************************************
-- DELETE DUPLICATE RECORDS ON BASIS OF THE DEPARTMENT(DeptID)
-- *******************************************************************************WITH CTE_EMP AS(
SELECT ROW_NUMBER() OVER (PARTITION BY DeptID ORDER BY DeptID) RowNum
,EmpId, Salary, DeptID
FROM Emp)
DELETE FROM CTE_EMP WHERE RowNum > 1
-- *******************************************************************************
-- GET THIRD HIGHEST SALARY FROM EMPLOYEE TABLE
-- *******************************************************************************
Select Salary From(
SELECT ROW_NUMBER() OVER (ORDER BY Salary Desc) As SrNo
,EmpID, Salary, DeptID
FROM Emp)RS Where SrNo = 3
You have following two tables
Write a query to return the Customer details and the details of the most recent order placed by the customer. The result should also include the details of all the customers who have not placed any orders at all.
| CUSTOMERMASTER | | SALESORDERMASTER |
| CustomerID | | SalesOrderID |
| Name | | SalesOrderDate |
| Address | | CustomerID |
| Telephone | | ProductID |
| Email | | Quantity |
Create Table CUSTOMERMASTER(CustomerID Int Identity Primary Key
,Name Varchar(50)
,Address Varchar(50)
,Telephone Varchar(10)
,Email Varchar(40))
Create Table SALESORDERMASTER(SalesOrderID Int Identity Primary Key
,SalesOrderDate DateTime
,CustomerID Int REFERENCES CUSTOMERMASTER(CustomerID)
,ProductID Int
,Quantity Int)
*/
/*
Insert Into CUSTOMERMASTER(Name) Values('Subrata')
Insert Into CUSTOMERMASTER(Name) Values('Arindam')
Insert Into CUSTOMERMASTER(Name) Values('Debabrata')
Insert Into CUSTOMERMASTER(Name) Values('Jayanta')
Select * From CUSTOMERMASTER
Insert Into SALESORDERMASTER(SalesOrderDate, CustomerID, Quantity) Values('2011-03-13', 1, 35)
Insert Into SALESORDERMASTER(SalesOrderDate, CustomerID, Quantity) Values('2011-01-09', 1, 221)
Insert Into SALESORDERMASTER(SalesOrderDate, CustomerID, Quantity) Values('2011-03-16', 1, 99)
Insert Into SALESORDERMASTER(SalesOrderDate, CustomerID, Quantity) Values('2011-03-15', 4, 364)
Select * From SALESORDERMASTER
*/
Select CustomerID, Name, Address, Telephone, EMail, SalesOrderDate, Quantity From(
Select Row_Number() Over (Partition By Cust.CustomerID Order By Sales.SalesOrderDate Desc) Serial
,Cust.CustomerID, Cust.Name, Cust.Address, Cust.Telephone, Cust.Email, Sales.SalesOrderDate,Sales.Quantity
From SALESORDERMASTER Sales
Right Outer Join CUSTOMERMASTER Cust ON Sales.CustomerID = Cust.CustomerID
)ResultSet Where Serial = 1
Order By SalesOrderDate Desc
