SQL Puzzle 1

Q1. How to select Random record from a Table

select top 1 * from Employee order by newid()

Q2. You have a table Employee with a column EName which contains Records Employee Name as A,B,A,A,B,D,C,M,A . Write a query to swap EName A to B and B to A.

UPDATE Employee

SET EName = (CASE WHEN EName='A' THEN 'B'

                  WHEN EName='B' THEN 'A'

                  ELSE EName

           END)

Q3. Write a query to calculate number of A in string AUSTRALIAA.

SELECT LEN('AUSTRALIAA')-LEN(REPLACE('AUSTRALIAA','A',''))    

Q4. What will be the output of below query?

SELECT * FROM (SELECT 1 UNION ALL SELECT 2) M

Msg 8155, Level 16, State 2, Line 1

No column name was specified for column 1 of 'M'.

Correct way :

SELECT * FROM (SELECT 1 as Col1 UNION ALL SELECT 2) M

Col1

1

2

Q5. What will be the output of below query?

SELECT SUM(A) as Total FROM (SELECT 1 A UNION ALL SELECT NULL A) M

Total

1

Q6. For 5/2, I want result as 2.5

SELECT CAST(5 AS float)/CAST(2 AS float)

Q7. What will be the output of below query?

SELECT CASE WHEN 1=1 THEN 'Hello'

            WHEN 2=2 THEN 'Hii'

            ELSE 'Bye'

       END AS Name

Output: Hello

Q8. What will be the output of below query?

SELECT 'Binod' + NULL + 'Kumar' AS [Full Name]

Output: NULL

Details of Employee who is having highest salary from each Department

Details of Employee who is having highest salary from each Department

CREATE TABLE Employee (EmpId int, Name Varchar(20), Department Varchar(20), Salary Int)

INSERT INTO Employee values (1,'Binod','Dot Net', 1000),

(2,'Anil','Dot Net', 2000),

(3,'Rohit','Dot Net', 1500),

(4,'Ankit','Design', 3000),

(5,'Mandeep','Design', 2000),

(6,'Bharat','Design', 3040),

(7,'Harinder','PHP', 4000),

(8,'Neha','PHP', 2000),

(9,'Airi','PHP', 4300),

(10,'Rajesh','PHP', 4300)

EmpId	Name	Department	Salary
1	Binod	Dot Net	1000
2	Anil	Dot Net	2000
3	Rohit	Dot Net	1500
4	Ankit	Design	3000
5	Mandeep	Design	2000
6	Bharat	Design	3040
7	Harinder	PHP	4000
8	Neha	PHP	2000
9	Airi	PHP	4300
10	Rajesh	PHP	4300

Expected Output :

Name	Department	Salary
Bharat	Design	3040
Anil	Dot Net	2000
Airi	PHP	4300
Rajesh	PHP	4300

Query:

WITH CTE

AS

(

SELECT Name, Department,Salary,DENSE_RANK() OVER(PARTITION BY Department ORDER BY Salary DESC) AS RowNum FROM Employee

)

SELECT Name, Department,Salary FROM CTE WHERE RowNum=1

NOTE: In above query we have used DENSE_RANK() function and we can't use ROW_NUMBER() function because it will not generate same row number for same salary from same department but DENSE_RANK() will generate same row number.

Question 1

Given these contents of the Customers table:

 


  

  

Id

  

  

Name

  

  

ReferredBy

 

  

  

1

  

  

John Doe

  

  

NULL

 

  

  

2

  

  

Jane Smith

  

  

NULL

 

  

  

3

  

  

Anne Jenkins

  

  

2

 

  

  

4

  

  

Eric Branford

  

  

NULL

 

  

  

5

  

  

Pat Richards

  

  

1

 

  

  

6

  

  

Alice Barnes

  

  

2

 

Here is a query written to return the list of customers not referred by Jane Smith:

SELECT Name FROM Customers WHERE ReferredBy <> 2;

What will be the result of the query? Why? What would be a better way to write it?

Although there are 4 customers not referred by Jane Smith (including Jane Smith herself), the query will only return one: Pat Richards. All the customers who were referred by nobody at all (and therefore have NULL in their ReferredBy column) don’t show up. But certainly those customers weren’t referred by Jane Smith, and certainly NULL is not equal to 2, so why didn’t they show up?
SQL Server uses three-valued logic, which can be troublesome for programmers accustomed to the more satisfying two-valued logic (TRUE or FALSE) most programming languages use. In most languages, if you were presented with two predicates: ReferredBy = 2 and ReferredBy <> 2, you would expect one of them to be true and one of them to be false, given the same value of ReferredBy. In SQL Server, however, if ReferredBy is NULL, neither of them are true and neither of them are false. Anything compared to NULL evaluates to the third value in three-valued logic: UNKNOWN.
The query should be written:

SELECT Name FROM Customers WHERE ReferredBy IS NULL OR ReferredBy <> 2

Watch out for the following, though!

SELECT Name FROM Customers WHERE ReferredBy = NULL OR ReferredBy <> 2

This will return the same faulty set as the original. Why? We already covered that: Anything compared to NULL evaluates to the third value in the three-valued logic: UNKNOWN. That “anything” includes NULL itself! That’s why SQL Server provides the IS NULL and IS NOT NULL operators to specifically check for NULL. Those particular operators will always evaluate to true or false.
Even if a candidate doesn’t have a great amount of experience with SQL Server, diving into the intricacies of three-valued logic in general can give a good indication of whether they have the ability learn it quickly or whether they will struggle with it.

Calculate the sum at Row and column level.

Calculate the sum at Row and column level.

Given Table:

Sal1	Sal2
10	15
20	30
15	30
30	40

Expected Output:

Sal1	Sal2	Sal3
10	15	25
20	30	50
15	30	45
30	40	70
75	115	190

 Table Creation:

CREATE TABLE SalaryDetails

(

Sal1 INT,

Sal2 INT

)

Data Insertion:

INSERT INTO SalaryDetails VALUES(10,15)

INSERT INTO SalaryDetails VALUES(20,30)

INSERT INTO SalaryDetails VALUES(15,30)

INSERT INTO SalaryDetails VALUES(30,40)

Final Query:

WITH CTE

AS

(

SELECT Sal1, Sal2,Sal1+Sal2 as Sal3 FROM SalaryDetails

)

SELECT Sal1, Sal2, Sal3 FROM CTE

UNION ALL

SELECT SUM(Sal1),SUM(Sal2),SUM(Sal3) FROM CTE

Converting Comma separated values to Rows and Vice Versa in SQL Server(Method 2).

Converting Comma separated values to Rows and Vice Versa in SQL Server(Method 2).



Introduction:

While doing ETL Testing or while Reporting you may encounter a situation where you will have comma separated (or separated with some other character) values and you want to report them in rows of a table or vice-versa.

In this article, I am going to explain how to write SQL query in SQL Server to handle this scenario.

Scenario: 1(Converting comma separated values in a cell to different rows)

CREATE TABLE Fruit

(

NAME VARCHAR(100)

)


INSERT Fruit(NAME) VALUES('Apple,Banana,Orange')

   

We can use STRING_SPLIT function which takes a string that should be divided and the separator that will be used to divide string. It returns a single-column table with substrings.

SELECT STRING_SPLIT(NAME,',') FROM Fruit

   

NOTE: The STRING_SPLIT function is available only under compatibility level 130. If your database compatibility level is lower than 130, SQL Server will not be able to find and execute STRING_SPLIT function. You can change a compatibility level of database using the following command:
ALTER DATABASE DatabaseName SET COMPATIBILITY_LEVEL = 130

Scenario: 2(Converting Rows to Comma separated values)

Before explaining how to achieve this let me create the table and insert some values

/*Create a table containing values in rows*/

 
CREATE TABLE Animals
(  Name VARCHAR(50))GO

/*Insert Values in the rows*/INSERT Animals(Name) VALUES ('Dog')

INSERT Animals(Name) VALUES ('Cat')

INSERT Animals(Name) VALUES ('Cow') GO

 

SELECT STUFF((SELECT ',' + Name FROM Fruits FOR XML PATH ('')),1,1,'') as Fruits