Archive
SQL Trivia – What are Niladic functions in SQL Server?
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'
SQL Trivia – Find second (or nth) Highest Salary or Marks
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!
SQL Trivia – Identify & Delete Duplicate records from a table
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:
SQL Trivia – Reverse a string without using T-SQL REVERSE() function
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.