SQL Trivia | SQL with Manoj

SQL Trivia – What are Niladic functions in SQL Server?

March 30, 2016 1 comment

Niladic functions are functions that do not accept any parameters, are specified without parentheses, and return only one result.

Following is the list of Niladic functions:

Niladic function (SQL-92)	Value returned
CURRENT_TIMESTAMP	Current date and time.
CURRENT_USER	Name of user performing an insert.
SESSION_USER	Name of user performing an insert.
SYSTEM_USER	Name of user performing an insert.
USER	Name of user performing an insert.

Execution of below query with these functions gives following results:

SELECT 
	 CURRENT_TIMESTAMP AS 'CURRENT TIMESTAMP'
	,CURRENT_USER AS 'CURRENT USER'
	,SESSION_USER AS 'SESSION USER'
	,SYSTEM_USER AS 'SYSTEM USER'
	,USER AS 'USER'

Niladic functions

SQL Trivia – Find second (or nth) Highest Salary or Marks

January 5, 2014 Leave a comment

At a beginner level this is the most basic and widely asked SQL Interview Question, and I’ve seen many people talk about this when you ask them to ask a basic SQL question.

Or, I could say it’s a tollgate to judge if a candidate knows basic SQL or not 🙂

So, here on demand of lot of people who ping me or email me for this, I’ve drafted this post on various ways or Queries to get the desired results.

–> I’ll create a dummy dbo.Employee table and populate with some dummy records (all these are my school/college friends):

USE tempdb
GO

CREATE TABLE dbo.Employee (
	EmpID INT PRIMARY KEY NOT NULL,
	EmpName VARCHAR(100),
	Salary Money
)
GO

INSERT INTO dbo.Employee
SELECT 1,  'Manoj',   20000
UNION ALL
SELECT 2,  'Saurabh', 50000
UNION ALL
SELECT 3,  'Kanchan', 30000
UNION ALL
SELECT 4,  'Hema',    10000
UNION ALL
SELECT 5,  'Bhanu',   60000
UNION ALL
SELECT 6,  'Prakash', 80000
UNION ALL
SELECT 7,  'Vivek',   40000
UNION ALL
SELECT 8,  'Ramesh',  55000
UNION ALL
SELECT 9,  'Kapil',   65000
UNION ALL
SELECT 10, 'Dev',     90000
GO

select * from dbo.Employee order by Salary DESC
GO

–> We will check for these conditions, 2nd highest & nth highest salary records from the above query, as shown below:

2nd highest is: 6, Prakash, 80000.00
5th highest is: 8, Ramesh,  55000.00
7th highest is: 7, Vivek,   40000.00

–> 2nd Highest Salary:

1. For SQL Server 2005 and +

;WITH CTE AS (
SELECT 
	EmpID, EmpName, Salary, 
	ROW_NUMBER() OVER(ORDER BY Salary DESC) as RN
FROM dbo.Employee
)
SELECT EmpID, EmpName, Salary
FROM CTE
WHERE RN = 2
GO

2. For SQL Server 2000

SELECT TOP 1 EmpID, EmpName, Salary
FROM (SELECT TOP 2 EmpID, EmpName, Salary 
	  FROM dbo.Employees 
	  ORDER BY Salary DESC
) X
ORDER BY Salary ASC
GO

–> nth Highest Salary: Just parametrize the numbers

1. For SQL Server 2005 +

DECLARE @n INT = 5

;WITH CTE AS (
SELECT 
	EmpID, EmpName, Salary, 
	ROW_NUMBER() OVER(ORDER BY Salary DESC) as RN
FROM dbo.Employee
)
SELECT EmpID, EmpName, Salary
FROM CTE
WHERE RN = @n
GO

2. For SQL Server 2000

DECLARE @n INT = 5

SELECT TOP 1 EmpID, EmpName, Salary
FROM (SELECT TOP (@n) EmpID, EmpName, Salary 
	  FROM dbo.Employees 
	  ORDER BY Salary DESC
) X
ORDER BY Salary ASC
GO

--// Final Cleanup
DROP TABLE dbo.Employee
GO

There are lot other ways also to get the same results and you can come up with different Queries with more optimizations.

Please do let me know if you have any comments and suggestions, thanks!

Categories: SQL Trivia Tags: 2nd highest salary, nth highest salary, second highest salary

SQL Trivia – Identify & Delete Duplicate records from a table

October 14, 2011 6 comments

I see a lot of questions/posts about dealing with duplicate records in many SQL Server forums. Many of these questions are asked in SQL Server Interviews and many developers starting their carrier in database programming find it challenging to deal with. Here we will see how we can deal with such records.

Duplicate data is one of the biggest pain points in our IT industry, which various projects have to deal with. Whatever state-of-art technology and best practices followed, the big ERP, CRM, SCM and other inventory based database management projects ends up in having duplicate & redundant data. Duplicate data keeps on increasing by manual entries and automated data loads. Various data leads getting pumped into system’s databases without proper deduping & data cleansing leads to redundant data and thus duplicated record-sets.

Data cleansing requires regular exercise of identifying duplicates, validating and removing them. To minimize these type of scenarios various checks and filters should also be applied before loading new leads into the system.

–> Lets check this by a simple exercise how we can identify & remove duplicate data from a table:

1. Insert some sample records from Person.Contact table of [AdventureWorks] database:

USE [AdventureWorks]
GO

SELECT TOP 10 ContactID, FirstName, LastName, EmailAddress, Phone
INTO DupContacts
FROM Person.Contact

SELECT * FROM DupContacts

2. Insert some duplicate records from the same list inserted above:

INSERT INTO DupContacts
SELECT TOP 50 PERCENT FirstName, LastName, EmailAddress, Phone
from DupContacts

SELECT * FROM DupContacts

3. Insert some more duplicate records from the same list inserted above.

INSERT INTO DupContacts
SELECT TOP 20 PERCENT FirstName, LastName, EmailAddress, Phone
from DupContacts

SELECT * FROM DupContacts

–> Identify Duplicate records & delete them

Method #1: by using ROW_NUMBER() function:

;WITH dup as (
SELECT ContactID, FirstName, LastName, EmailAddress, Phone,
ROW_NUMBER() OVER(PARTITION BY FirstName, LastName ORDER BY ContactID) AS NumOfDups
FROM DupContacts)
SELECT * FROM dup
WHERE NumOfDups > 1
ORDER BY ContactID

-- Remove/Delete duplicate records:
;WITH dup as (
SELECT ContactID, FirstName, LastName, EmailAddress, Phone,
ROW_NUMBER() OVER(PARTITION BY FirstName, LastName ORDER BY ContactID) AS NumOfDups
FROM DupContacts)
DELETE FROM dup
WHERE NumOfDups > 1

SELECT * FROM DupContacts

Method #2: by using SELF-JOIN:

SELECT DISTINCT a.ContactID, a.FirstName, a.LastName, a.EmailAddress, a.Phone
FROM DupContacts a
JOIN DupContacts b
ON a.FirstName = b.FirstName
AND a.LastName = b.LastName
AND a.ContactID > b.ContactID

-- Remove/Delete duplicate records:
DELETE a
FROM DupContacts a
JOIN DupContacts b
ON a.FirstName = b.FirstName
AND a.LastName = b.LastName
AND a.ContactID > b.ContactID

SELECT * FROM DupContacts

Method #3: by using AGGREGATES & Sub-QUERY:

SELECT * FROM DupContacts
WHERE ContactID NOT IN (SELECT MIN(ContactID)
FROM DupContacts
GROUP BY FirstName, LastName)

-- Remove/Delete duplicate records:
DELETE FROM DupContacts
WHERE ContactID NOT IN (SELECT MIN(ContactID)
FROM DupContacts
GROUP BY FirstName, LastName)

SELECT * FROM DupContacts

–> Final Cleanup

DROP TABLE DupContacts

Check the same demo here:

Categories: SQL Tips, SQL Trivia Tags: Delete Duplicate, ROW_NUMBER, SELF JOIN

SQL Trivia – Reverse a string without using T-SQL REVERSE() function

August 18, 2011 9 comments

Everyone knows that the REVERSE() function (as its name suggests) reverses a string’s value, MSDN link.

This post’s title talks about going the other way, but why? If we already have this function then why to reinvent the wheel.

But at times an Interviewer may trick you and asks this question randomly: “How will you reverse a string without using REVERSE() function? No loop, should be a single query.” People who have actually worked on CTEs and know about recursive CTEs can guess the answer and create the query on-spot. Actually he wants to know if you have really worked on CTEs & recursive CTEs, or just learned the concept, or do not know about this at all.

Let’s check this by a simple example:

DECLARE @StringToReverse VARCHAR(55)
SET @StringToReverse = 'Reverse a string with out using REVERSE() function'

;WITH cte AS (
      SELECT @StringToReverse AS string, CAST('' AS VARCHAR(55)) AS revStr, LEN(@StringToReverse) AS ln
      UNION ALL
      SELECT SUBSTRING(string,0,ln) AS string, CAST(revStr + SUBSTRING(string,ln,1) AS VARCHAR(55)) AS revStr, ln-1 AS ln
      FROM cte
      WHERE ln >= 1)
SELECT @StringToReverse AS String, revStr
FROM cte
WHERE ln = 0

Output:-

Note: In the code above check line numbers 5 & 7 containing CAST function, “CAST(” AS VARCHAR(55)) AS revStr” & “CAST(revStr + SUBSTRING(string,ln,1) AS VARCHAR(55))”.

Applying CAST or CONVERT function is necessary here as datatype & size of both the columns in the anchor & recursive part should be same. Otherwise SQL server will throw the following error:

Msg 240, Level 16, State 1, Line 4
Types don’t match between the anchor and the recursive part in column “revStr” of recursive query “cte”.

>> Check & Subscribe my [YouTube videos] on SQL Server.