T-SQL Output Query | [ The Single SELECT Puzzle List – Part 3 ]

06 Monday Apr 2015

Posted by Pawan Kumar Khowal in SQL SERVER, T SQL Puzzles

Tags

Complex SQL Challenges, Complex TSQL Challenge, Interesting Interview Questions, Interview Qs.SQL SERVER Questions, Interview questions on Joins, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Joins, Joins Interview questions, Joins Puzzle, Learn complex SQL, Learn SQL, Learn T-SQL, Objective Puzzle, PL/SQL Challenges, puzzle sql developer, Puzzles, Queries for SQL Interview, SELECT Puzzle, SQL, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Challenge, SQL Challenges, SQL Interview Questions, SQL Joins, SQL pl/sql puzzles, SQL Puzzles, SQL Queries, SQL Quiz, SQL Server Database, SQL SERVER Interview questions, SQL Skills, SQL Sudoku, SQL Top clause, SQL Trikcy question, sql/database interview for puzzle sql developer, SQLSERVER, T SQL Puzzles, T-SQL Challenge, TOP Clause, Tough SQL Challenges, Tough SQL Puzzles, Tricky Questions, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Queries

T-SQL Output Query | [ The Single SELECT Puzzle List – Part 3 ]

Please go through the below objective type queries and provide output..


--

--Query 1
SELECT SPACE(1) + 1

--Query 2
SELECT LEN(SPACE(1) + CAST(1 AS VARCHAR(1)))

--Query 3
SELECT LEN(SPACE(1) + 1)

--Query 4
SELECT LEN(' AB ')

--Query 5
SELECT LEN('AB ')

--Query 6
SELECT DATALENGTH(' A B ')

--Query 7
SELECT CONCAT('Pawan',1)

--Query 8
SELECT 'PAWAN' + 1

--Query 9
SELECT LEN(SPACE(1) + 'PAWAN')

--Query 10
SELECT DATALENGTH(SPACE(1) + 1)

--

Keep Learning

T-SQL Query | [ AIRPLANES AND PILOTS Puzzle ]

06 Monday Apr 2015

Posted by Pawan Kumar Khowal in SQL SERVER, T SQL Puzzles

≈ 5 Comments

Tags

T-SQL Query | [AIRPLANES AND PILOTS Puzzle ]

NOTE – This puzzle is from Joe Celko’s SQL Puzzles and Answers book.

The puzzle is very simple. We have a table of pilots and the planes they can fly and a table of planes in the hangar. We want the names of the pilots who can fly every plane in the hangar. Please check out the sample input and expected output for details.

Sample Input

PilotSkills

pilot	plane
Celko	Piper Cub
Higgins	B-52 Bomber
Higgins	F-14 Fighter
Higgins	Piper Cub
Jones	B-52 Bomber
Jones	F-14 Fighter
Smith	B-1 Bomber
Smith	B-52 Bomber
Smith	F-14 Fighter
Wilson	B-1 Bomber
Wilson	B-52 Bomber
Wilson	F-14 Fighter
Wilson	F-17 Fighter

Hangar

plane

B-1 Bomber

B-52 Bomber

F-14 Fighter

Expected output

Pilot

Smith

Wilson

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.
Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script

Use the below script to generate the source table and fill them up with the sample data.

--

CREATE TABLE PilotSkills
(
pilot CHAR(15) NOT NULL,
plane CHAR(15) NOT NULL,
PRIMARY KEY (pilot, plane)
);

INSERT INTO PilotSkills
VALUES ('Celko', 'Piper Cub'),
('Higgins', 'B-52 Bomber'),
('Higgins', 'F-14 Fighter'),
('Higgins', 'Piper Cub'),
('Jones', 'B-52 Bomber'),
('Jones', 'F-14 Fighter'),
('Smith', 'B-1 Bomber'),
('Smith', 'B-52 Bomber'),
('Smith', 'F-14 Fighter'),
('Wilson', 'B-1 Bomber'),
('Wilson', 'B-52 Bomber'),
('Wilson', 'F-14 Fighter'),
('Wilson', 'F-17 Fighter');

CREATE TABLE Hangar
(
plane CHAR(15) PRIMARY KEY
);

INSERT INTO Hangar
VALUES
('B-1 Bomber'),
('B-52 Bomber'),
('F-14 Fighter');

--

UPDATE – 10-Apr-2015 – Solution 1


-- Solution 1 --

SELECT p.Pilot 
FROM PilotSkills p 
	 INNER JOIN Hangar h on h.plane = p.plane
GROUP BY p.pilot
HAVING COUNT(*) = ( SELECT COUNT(*) FROM Hangar h )

--

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

http://MSBISkills.com

T-SQL Query | [ Swap Values of a Column Puzzle ]

06 Monday Apr 2015

Posted by Pawan Kumar Khowal in SQL SERVER, T SQL Puzzles

≈ 3 Comments

Tags

Complex SQL Challenges, Complex TSQL Challenge, Interesting Interview Questions, Interview Qs.SQL SERVER Questions, Interview questions on Joins, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Joins, Joins Interview questions, Joins Puzzle, Learn complex SQL, Learn SQL, Learn T-SQL, Objective Puzzle, PL/SQL Challenges, puzzle sql developer, Puzzles, Queries for SQL Interview, SELECT Puzzle, SQL, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Challenge, SQL Challenges, SQL Interview Questions, SQL Joins, SQL pl/sql puzzles, SQL Puzzles, SQL Queries, SQL Quiz, SQL Server Database, SQL SERVER Interview questions, SQL Skills, SQL Sudoku, SQL Top clause, SQL Trikcy question, sql/database interview for puzzle sql developer, SQLSERVER, Swap Values of a Column Puzzle, Swap Values of a Column without case, T SQL Puzzles, T-SQL Challenge, TOP Clause, Tough SQL Challenges, Tough SQL Puzzles, Tricky Questions, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Queries

T-SQL Query | [ Swap Value of Column with another Without Case Statement ]

The puzzle is very simple. Here you have to swap values of column. In sample input case you have only column called Val. In this column only 2 values are present ‘A’ & ‘B’. We have to update value of A with B and B with A. Please check out the sample input and expected output for details.

Sample Input

Val

Expected output

Val

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.
Please do not use CASE, REPLACE, IIF and CHOOSE.
Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script

Use the below script to generate the source table and fill them up with the sample data.


--

CREATE TABLE Swap
(
Val VARCHAR(1)
)
GO

INSERT INTO Swap(Val) VALUES ('A'),('B'),('B'),('A'),('B'),('B')

--

UPDATE – 10-Apr-2015 – Solution 1


-- Solution 1 --

UPDATE s
SET s.Val = s1.Val
FROM Swap S
INNER JOIN Swap S1
ON S.Val <> S1.Val

--

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

http://MSBISkills.com

T-SQL Query | [ Find 2nd highest Salary using Top Clause ]

06 Monday Apr 2015

Posted by Pawan Kumar Khowal in SQL SERVER, T SQL Puzzles

≈ 3 Comments

Tags

Complex SQL Challenges, Complex TSQL Challenge, Interview Qs.SQL SERVER Questions, Interview questions on Joins, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Joins, Joins Interview questions, Joins Puzzle, Learn complex SQL, Learn SQL, Learn T-SQL, Objective Puzzle, PL/SQL Challenges, Puzzles, Queries for SQL Interview, SELECT Puzzle, SQL, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Challenge, SQL Challenges, SQL Interview Questions, SQL Joins, SQL pl/sql puzzles, SQL Puzzles, SQL Queries, SQL SERVER Interview questions, SQL Skills, SQL Sudoku, SQL Top clause, SQLSERVER, T SQL Puzzles, T-SQL Challenge, TOP Clause, Tough SQL Challenges, Tough SQL Puzzles, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Queries

T-SQL Query | [ Find 2^nd highest Salary using Top Clause ]

The puzzle is very simple. Here you have to find 2^nd highest salary details of employees using Top clause. Please note that Top clause is a must in this case and if there are multiple values for 2^nd highest details then the query should return all the duplicate values. Please check out the sample input and expected output for details.

Sample Input

Name	Salary
e5	45000
e3	30000
e2	49000
e4	36600
e1	58000
E10	49000
E11	49000

Expected output

Name	Salary
E10	49000
E11	49000
e2	49000

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.
Top clause is a must in this case.
Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script

Use the below script to generate the source table and fill them up with the sample data.


--Create table
CREATE TABLE NthHighest
(
Name varchar(5) NOT NULL,
Salary   int   NOT NULL
)

--Insert the values
INSERT INTO  NthHighest(Name, Salary)
VALUES

('e5', 45000),
('e3', 30000),
('e2', 49000),
('e4', 36600),
('e1', 58000),
('E10',49000),
('E11',49000)

--Check data
SELECT Name,Salary FROM NthHighest
--
&nbsp;

UPDATE – 10-Apr-2015 – Solution 1


-- Solution 1 --

SELECT TOP 1 WITH TIES Name,Salary
FROM 
(
       SELECT TOP 2 WITH TIES Name,Salary FROM NthHighest ORDER BY Salary DESC 
) a
ORDER BY Salary

--

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

http://MSBISkills.com

T-SQL Query |The Student and the Marks Puzzle

06 Monday Apr 2015

Posted by Pawan Kumar Khowal in SQL SERVER, T SQL Puzzles

≈ 1 Comment

Tags

Complex SQL Challenges, Complex TSQL Challenge, Interview Qs.SQL SERVER Questions, Interview questions on Joins, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Joins, Joins Interview questions, Joins Puzzle, Learn complex SQL, Learn SQL, Learn T-SQL, Objective Puzzle, PL/SQL Challenges, Puzzles, Queries for SQL Interview, SELECT Puzzle, SQL, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Challenge, SQL Challenges, SQL Joins, SQL pl/sql puzzles, SQL Puzzles, SQL Queries, SQL SERVER Interview questions, SQL Skills, SQL Sudoku, SQLSERVER, T SQL Puzzles, T-SQL Challenge, Tough SQL Challenges, Tough SQL Puzzles, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Queries

T-SQL Query | The Student and the Marks Puzzle

The Puzzle is simple. There are 3 tables , lets say BM – book marks , SM – Sports marks , EM – Education marks. All these tables contains stud ID, Name and their respective marks. Write a query which will satisfy below conditions

/*
if any of the two marks are below 40 then the student is fail
if any of the two marks are between 40 and 80 then the student is Pass
if any of the two marks are greater than 80 then the student is Excellent
*/

Sample Input

StuID	StuName	BMarks
1	A	90
2	B	60
3	C	50
4	D	20

StuID	StuName	EMarks
1	A	90
2	B	60
3	C	10
4	D	40

StuID	StuName	SMarks
1	A	90
2	B	60
3	C	40
4	D	20

Expected Output

StuID	StuName	Performance
1	A	Excellent
2	B	Pass
3	C	Pass
4	D	Fail

Rules/Restrictions
◾The solution should be should use “SELECT” statement or “CTE”.
◾Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script

Use the below script to generate the source table and fill them up with the sample data.


--

CREATE TABLE BM
(
StuID INT
,StuName VARCHAR(1)
,BMarks INT
)
GO

INSERT INTO BM(StuID, StuName, BMarks) VALUES
(1,'A',90),
(2,'B',60),
(3,'C',50),
(4,'D',20)

GO

CREATE TABLE SM
(
StuID INT
,StuName VARCHAR(1)
,SMarks INT
)
GO

INSERT INTO SM(StuID, StuName, SMarks) VALUES
(1,'A',90),
(2,'B',60),
(3,'C',40),
(4,'D',20)

GO

CREATE TABLE EM
(
StuID INT
,StuName VARCHAR(1)
,EMarks INT
)
GO

INSERT INTO EM(StuID, StuName, EMarks) VALUES
(1,'A',90),
(2,'B',60),
(3,'C',10),
(4,'D',40)

GO
--

UPDATE – 10-Apr-2015 – Solution 1


-- Solution 1 --

SELECT EM.StuID , EM.StuName ,
CASE  WHEN  (BMarks > 40 AND EMarks <= 60 ) 
           OR (EMarks > 40 AND SMarks <= 60 ) 
           OR (SMarks > 40 AND BMarks <= 60 ) THEN 'Pass' 
       WHEN  (BMarks < 40 ) 
           OR (EMarks < 40 ) 
           OR (SMarks < 40 ) THEN 'Fail'        
       WHEN  (BMarks > 80 ) 
           OR (EMarks > 80 ) 
           OR (SMarks > 80 ) THEN 'Excellent' END 'Performance'
FROM BM INNER JOIN EM on EM.StuId = BM.StuID INNER JOIN SM on SM.StuID = BM.StuID

--

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

http://MSBISkills.com

M	T	W	T	F	S	S
		1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30

Improving my SQL BI Skills

Daily Archives: April 6, 2015

T-SQL Output Query | [ The Single SELECT Puzzle List – Part 3 ]

T-SQL Query | [ AIRPLANES AND PILOTS Puzzle ]

T-SQL Query | [ Swap Values of a Column Puzzle ]

T-SQL Query | [ Find 2nd highest Salary using Top Clause ]

T-SQL Query |The Student and the Marks Puzzle

Share this

Share this

Share this

Share this

Share this