Advertisements

SQL Puzzle | Multiple ways to add Date and Time to a DATETIME | SQL Interview Question

Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | Multiple ways to add Date and Time to a DATETIME | SQL Interview Question

This was asked to one of my developer by a Client in a technical interview so I thought I must share it with you guys. In this puzzle you have 2 variables one is DATE and other is TIME and you have to add both to get a DATETIME. Please check out the sample input and the expected output.

Sample Input

Date Time
2018-08-10 07:48:46.4566667

Expected Output

DateTime
2018-08-10 07:48:46.457

Script – DDL and INSERT Sample Data

--

DECLARE @Dt DATE = '2018-08-10' , @Tm Time = '07:48:46.4566667'

--

–SOLUTION 1

--

DECLARE @Dt DATE = '2018-08-10' , @Tm Time = '07:48:46.4566667'
SELECT CAST(@Dt AS DATETIME) + CAST(@Tm AS DATETIME) [DateTime]
GO

--

OUTPUT – 1

--

DateTime
-----------------------
2018-08-10 07:48:46.457

(1 row affected)


--

–SOLUTION 2

--

DECLARE @Dt DATE = '2018-08-10' , @Tm Time = '07:48:46.4566667'
SELECT 
	DATEADD(MILLISECOND, DATEDIFF(MILLISECOND, '00:00:00', @Tm), CONVERT(DATETIME, @Dt))
	[DateTime]
GO

--

OUTPUT – 2

--

DateTime
-----------------------
2018-08-10 07:48:46.457

(1 row affected)


--

–SOLUTION 3

--

DECLARE @Dt DATE = '2018-08-10' , @Tm Time = '07:48:46.4566667'
SELECT DATEADD(DAY,DATEDIFF(DAY, 0, @Dt),CAST(@Tm AS DATETIME))
	 [DateTime]
GO

--

OUTPUT – 3

--

DateTime
-----------------------
2018-08-10 07:48:46.457

(1 row affected)


--

–SOLUTION 4

--

DECLARE @Dt DATE = '2018-08-10' , @Tm Time = '07:48:46.4566667'
SELECT CAST(CONCAT(@Dt,' ',CAST(@Tm AS VARCHAR(12))) AS DATETIME)
	[DateTime]
GO

--

OUTPUT – 4

--


DateTime
-----------------------
2018-08-10 07:48:46.457

(1 row affected)


--

Enjoy 🙂

Please add comment(s) if you have one or multiple solutions in mind. Thank You.

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail URL
☛ SQL Advance Puzzles https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Performance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com

Advertisements

SQL Puzzle | The Grouping Puzzle – A Single SELECT | SQL Interview Question

Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | The Grouping Puzzle – A Single SELECT | SQL Interview Question

In this puzzle you have to have find the sum of sales for each employee. The catch here is that for inactive employees(IsActive=0) group the sum under Inactive name and rest under their respective names. Another catch is that you need to do this in a single select.Please check out the sample input and the expected output.

Sample Input

EmpName SaleDate SaleAmount IsActive
Pawan 2018-08-07 10:18:28.663 2500.00 1
Pawan 2018-08-07 10:18:28.663 3000.00 1
Avtaar 2018-08-07 10:18:28.663 800.00 1
Avtaar 2018-08-07 10:18:28.663 1000.00 1
Kishan 2018-08-07 10:18:28.663 2800.00 1
Kishan 2018-08-07 10:18:28.663 3000.00 1
Nimit 2018-08-07 10:18:28.663 500.00 1
Nimit 2018-08-07 10:18:28.663 800.00 1
K 2018-08-07 10:18:28.663 1000.00 0
L 2018-08-07 10:18:28.663 1000.00 0
M 2018-08-07 10:18:28.663 500.00 0
J 2018-08-07 10:18:28.663 2500.00 0

Expected Output

EmpName SaleAmount
Avtaar 1800.00
Inactive 5000.00
Kishan 5800.00
Nimit 1300.00
Pawan 5500.00

Script – DDL and INSERT Sample Data

--

CREATE TABLE dbo.tbl_sales
(  
    EmpName VARCHAR(256),
    SaleDate DATETIME,
    SaleAmount DECIMAL(18,2),
    IsActive BIT
)
GO

INSERT INTO tbl_sales VALUES 
('Pawan',GETDATE(),2500.00,1),
('Pawan',GETDATE(),3000.00,1),
('Avtaar',GETDATE(),800.00,1),
('Avtaar',GETDATE(),1000.00,1),
('Kishan',GETDATE(),2800.00,1),
('Kishan',GETDATE(),3000.00,1),
('Nimit',GETDATE(),500.00,1),
('Nimit',GETDATE(),800.00,1),
('K',GETDATE(),1000.00,0),
('L',GETDATE(),1000.00,0),
('M',GETDATE(),500.00,0),
('J',GETDATE(),2500.00,0)
GO

SELECT * FROM tbl_sales
GO

--

–SOLUTION 1

--

SELECT DISTINCT IIF(IsActive = 0, 'Inactive',EmpName) EmpName , 
				SUM(SaleAmount) OVER (PARTITION BY  IIF(IsActive = 0, 'Inactive',EmpName))
				[SaleAmount]
FROM tbl_sales
GO

--

OUTPUT – 1

--

EmpName         SaleAmount
--------------- ---------------
Avtaar          1800.00
Inactive        5000.00
Kishan          5800.00
Nimit           1300.00
Pawan           5500.00

(5 rows affected)

--

–SOLUTION 2

--

SELECT IIF(IsActive = 0, 'Inactive',EmpName) EmpName 
	  ,SUM(SaleAmount) [SaleAmount]
FROM tbl_sales
GROUP BY IIF(IsActive = 0, 'Inactive',EmpName)
GO
--

OUTPUT – 2

--

EmpName         SaleAmount
--------------- ---------------
Avtaar          1800.00
Inactive        5000.00
Kishan          5800.00
Nimit           1300.00
Pawan           5500.00

(5 rows affected)

--

Enjoy 🙂

Please add comment(s) if you have one or multiple solutions in mind. Thank You.

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail URL
☛ SQL Advance Puzzles https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Performance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com

SQL Puzzle | The Compare * 100 Puzzle | SQL Interview Question

Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | The Compare * 100 Puzzle | SQL Interview Question

In this puzzle you have to generate a new column called FinalOutput. The logic for the new column is that you have to check if value of Length1 = Length1 * 100 for each value for col1 and col2, If yes then you need to concatenate the small value with the larger value separating by |. If not then show the value directly. Can you do that using a single select?. Please check out the sample input and the expected output.

Sample Input

col1 col2 Length1
A APD 23
A APD 2300
A APD 45
A APD 78
B BPD 45
B BPD 4500
B BPD 450
NULL CPD 98
NULL CPD 9800
NULL CPD 983

Expected Output

col1 col2 Length1 FinalOutput
A APD 23 23 | 2300
A APD 2300 23 | 2300
A APD 45 45
A APD 78 78
B BPD 45 45 | 4500
B BPD 4500 45 | 4500
B BPD 450 450
NULL CPD 98 98 | 9800
NULL CPD 9800 98 | 9800
NULL CPD 983 983

Script – DDL and INSERT Sample Data

--

CREATE Table Combine 
(
    col1 varchar(MAX),
    col2 varchar(MAX),
    Length1 float
)
GO

INSERT INTO Combine VALUES
('A','APD',23),
('A','APD',2300),
('A','APD',45),
('A','APD',78),
('B','BPD',45),
('B','BPD',4500),
('B','BPD',450),
('NULL','CPD',98),
('NULL','CPD',9800),
('NULL','CPD',983)
GO

SELECT * FROM Combine
GO

--

–SOLUTION 1 : THE SUM … OVER() WITH IIF

--

SELECT a.*, 
		IIF
		(
			b.Length1 IS NOT NULL,
			CONCAT(IIF(a.Length1>b.Length1,b.Length1,a.Length1),' | ',IIF(a.Length1>b.Length1,a.Length1,b.Length1)),
			CONCAT(a.Length1,'')
		)FinalOutput 
FROM Combine
a LEFT JOIN Combine b ON a.col1 = b.col1 AND a.col2 = b.col2
AND ( a.Length1 * 100 = b.Length1 OR a.Length1 = b.Length1 * 100 )

--

OUTPUT – 1

--

col1       col2         Length1                FinalOutput 
---------- ------------ ---------------------- ----------- 
A          APD          23                     23 | 2300   
A          APD          2300                   23 | 2300   
A          APD          45                     45          
A          APD          78                     78          
B          BPD          45                     45 | 4500   
B          BPD          4500                   45 | 4500   
B          BPD          450                    450         
NULL       CPD          98                     98 | 9800   
NULL       CPD          9800                   98 | 9800   
NULL       CPD          983                    983         
                                                           
(10 rows affected)                                         

--

Enjoy 🙂

Please add comment(s) if you have one or multiple solutions in mind. Thank You.

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail URL
☛ SQL Advance Puzzles https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Performance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com

SQL Server Interview Questions & Answers – SET 22(10 Questions & Answers)


SQL Server Interview Questions & Answers – SET 22(10 Questions & Answers)

These questions were asked to one of my friend in an recent technical encounter. Well he has got 10 years of experience 🙂

1. Can we use an ORDER BY clause in a CTE?

Ans:-

Yes you can with the help of TOP operator. Without TOP operator it is not possible to use order by inside a CTE. If you try you will get an error saying – Msg 1033, Level 15, State 1, Line 18
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified. Please check out the example below.

Example – 1 | With TOP

--

CREATE TABLE FirstTab
(
	 ID INT	
)
GO

INSERT INTO FirstTab VALUES
(1),
(2),
(4),
(3)
GO

;WITH CTE AS
(
	SELECT TOP 100 * FROM FirstTab
	ORDER BY Id DESC
)
SELECT * FROM CTE
GO

--OUTPUT

ID
-----------
4
3
2
1

(4 rows affected)


--

Example – 2 | Without TOP

--

CREATE TABLE FirstTab
(
	 ID INT	
)
GO

INSERT INTO FirstTab VALUES
(1),
(2),
(4),
(3)
GO

;WITH CTE AS
(
	SELECT * FROM FirstTab
	ORDER BY Id DESC
)
SELECT * FROM CTE
GO

--OUTPUT

Msg 1033, Level 15, State 1, Line 18
The ORDER BY clause is invalid in views, inline functions, derived tables
, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.


--

2. Do you know – Natural join? How you can use Natural JOIN In SQL SERVER?

Ans:-

Natural Joins are not there in SQL SERVER. For the sake of concept I am gonna explain the concept here. I had leaned that while I was working in Oracle in 2009. They are also present in mySQL Server.

Natural Joins are not there in SQL SERVER. For the sake of concept I am gonna explain the concept here. I had leaned that while I was working in Oracle in 2009.

A NATURAL JOIN is a JOIN operation that creates an inner join between common columns in the two tables being joined. Common columns are columns that have the same name in both tables or you can also specify names by USING keyword.

A NATURAL JOIN can be an INNER join, a LEFT OUTER join, or a RIGHT OUTER join. The default is INNER join.

If the SELECT statement in which the NATURAL JOIN operation appears has an asterisk (*) in the select list, the asterisk will be expanded to the following list of columns (in this order):-
All the common columns
Every column in the first (left) table that is not a common column
Every column in the second (right) table that is not a common column
An asterisk qualified by a table name (for example, COUNTRIES.*) will be expanded to every column of that table that is not a common column.

If a common column is referenced without being qualified by a table name, the column reference points to the column in the first (left) table if the join is an INNER JOIN or a LEFT OUTER JOIN. If it is a RIGHT OUTER JOIN, unqualified references to a common column point to the column in the second (right) table.

Syntax

--

TableExpression NATURAL [ { LEFT | RIGHT } 
[ OUTER ] | INNER ] JOIN { TableViewOrFunctionExpression | ( TableExpression ) }

--

Notes –
1. With NATURAL JOINS columns with the same name of associate tables will appear once only in the output.

Refer below for more-

Oracle Natural JOIN
https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljnaturaljoin.html

mySQL Server Natural JOIN
https://dev.mysql.com/doc/refman/8.0/en/join.html

Examples below – From Oracle(http://rextester.com)

--                            

CREATE TABLE Pawant1
(
    Id INT
   ,Name VARCHAR2(20)
)
\\


INSERT INTO Pawant1 VALUES (1,'Pawan')
\\
INSERT INTO Pawant1 VALUES (2,'Avtaar')
\\
INSERT INTO Pawant1 VALUES (3,'Dil')
\\

CREATE TABLE Pawant2
(
    Id INT
   ,Name VARCHAR2(20)
)
\\

INSERT INTO Pawant2 VALUES (3,'Dil')
\\

--Example 1

SELECT * FROM Pawant1
NATURAL JOIN Pawant2

--OUTPUT 

 	ID	NAME
1	3	Dil


--Example 2 | By USING Keyword and removing Natural keyword

SELECT * FROM Pawant1
JOIN Pawant2
USING(Id)

--OUTPUT 

 	ID	NAME
1	3	Dil

--

Note – After executing the above commands you need to stop and start SQL Server.

3. Can you tell me few limitations of an IDENTITY column

Ans:-

You cannot update value of an identity once it is generated and you can use it on Int columns.

4. How you can get the primary key information for a single table using T-SQL query

Ans:-

You can use sp_pkeys or sp_primarykeys procedures. Please refer below for more details-

sp_pkeys – It returns the primary key information for a single table in the current environment.
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-pkeys-transact-sql?view=sql-server-2017

sp_primarykeys – It returns the primary key columns, one row per key column, for the specified remote table.
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-primarykeys-transact-sql?view=sql-server-2017

5. How many types of XML indexes in SQL Server?

Ans:-

XML indexes categories are of two types

1. Primary XML index
2. Secondary XML index

Primary XML index
The primary XML index indexes all tags, values, and paths within the XML instances in an XML column. To create a primary XML index, the table in which the XML column occurs must have a clustered index on the primary key of the table. SQL Server uses this primary key to correlate rows in the primary XML index with rows in the table that contains the XML column.

The primary XML index is a shredded and persisted representation of the XML BLOBs in the xml data type column. For each XML binary large object (BLOB) in the column, the index creates several rows of data. The number of rows in the index is approximately equal to the number of nodes in the XML binary large object. When a query retrieves the full XML instance, SQL Server provides the instance from the XML column. Queries within XML instances use the primary XML index, and can return scalar values or XML subtrees by using the index itself.

Secondary XML index

To enhance search performance, you can create secondary XML indexes.These are the types:
PATH secondary XML index
VALUE secondary XML index
PROPERTY secondary XML index

Read more from – https://docs.microsoft.com/en-us/sql/relational-databases/xml/xml-indexes-sql-server?view=sql-server-2017#secondary-xml-indexes

Note-
A primary XML index must first exist before you can create secondary indexes

Read more about the XML Indexes from below-
https://docs.microsoft.com/en-us/sql/relational-databases/xml/xml-indexes-sql-server?view=sql-server-2017

6. How can you import data into SQL Server using T-SQL without SSIS?

Ans:-

You can you use any one of the below commands. Also depends from where we are fetch the data.

1. Linked Servers
2. OPENQUERY
3. BCP
4. OPENDATASOURCE
5. OpenRowSet
6. Bulk Insert

7. Why we should not use ORDER BY NEWID() for getting the sample data from a large table?

Ans:-

This is because it will scan the entire table and calculate NEWID() for each row and after that sorts. You should use something else. 🙂

8. How to clear Plan Cache in SQL SERVER(on-premise) and On SQL Azure?

ON SQL Premise-
dbcc freeproccache

Read more from-
https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-freeproccache-transact-sql?view=sql-server-2017

ON SQL Azure-
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

Read more from-
https://msdn.microsoft.com/en-us/library/mt629158.aspx

9. Can we create a clustered index on a uniqueidentifier column

Ans:-

Yes you can but you should not

10. What will be the output of SELECT (1/2) * 10

Ans:-

I am leaving this answer for yourself.

Enjoy 🙂

Page Detail URL
☛ SQL Advance Puzzles https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com

SQL OUTPUT PUZZLE | The JOINs – How well do you know them ? | SQL Interview Question[Updated Answers]

Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL OUTPUT PUZZLE | The Joins – How well do you know them ? | SQL Interview Question

In this puzzle you have to go through all the SQL queries and provide us the output with really executing them at the SSMS.

DATA.

Table1 – FirstTab

ID
1
2
NULL

Table2 – SecondTab

ID
1
3
NULL

Script – DDL and INSERT Sample Data

--

CREATE TABLE FirstTab
(
	 ID INT	
)
GO

INSERT INTO FirstTab VALUES
(1),
(2),
(NULL)
GO

SELECT * FROM FirstTab
GO

CREATE TABLE SecondTab
(
	ID INT	
)
GO

INSERT INTO SecondTab VALUES
(1),
(3),
(NULL)
GO

SELECT * FROM SecondTab
GO


--

Q1. What will be the OUTPUT of the following statement?

--

SELECT COUNT(1) Cnt FROM FirstTab a
INNER JOIN SecondTab b ON a.Id  b.ID
--Note  : INNER JOIN with not equal to join

--

SOLUTION

--


So here the output will be 3 as 1  2 , 32 and 31
Cnt
-----------
3

(1 row affected)

Note - NULLs will not participate here in JOIN.

--

Q2. What will be the OUTPUT of the following statement?

--

SELECT COUNT(1) Cnt FROM FirstTab a
LEFT JOIN SecondTab b ON a.Id  b.ID
--Note  : LEFT JOIN with not equal to join

--

SOLUTION

--

The output will be 4 here.

Cnt
-----------
4

(1 row affected)

Note - 3 rows for the Inner join and 1 NULL remaining from the left table.

--

Q3. What will be the OUTPUT of the following statement?

--

SELECT COUNT(1) Cnt FROM FirstTab a
RIGHT JOIN SecondTab b ON a.Id  b.ID
--Note  : RIGHT JOIN with not equal to join

--

SOLUTION

--


The output will be 4 here.

Cnt
-----------
4

(1 row affected)

Note - 3 rows for the Inner join and 1 NULL remaining from the right table.


--

Q4. What will be the OUTPUT of the following statement?

--

SELECT COUNT(1) Cnt FROM FirstTab a
FULL OUTER JOIN SecondTab b ON a.Id  b.ID
--Note  : FULL OUTER JOIN with not equal to join


--

SOLUTION

--


Cnt
-----------
5

The output will be 5. 3 for the inner join + 1 NULL for the left join and 1 for the right join.

--

Q5. What will be the OUTPUT of the following statement?

--

SELECT COUNT(1) Cnt FROM FirstTab a
CROSS JOIN SecondTab b 
WHERE a.Id  b.ID

--

SOLUTION

--

This is similar as INNER JOIN. So the output will be 3.

--

Enjoy 🙂

Please add comment(s) if you have one or multiple solutions in mind. Thank You.

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail URL
☛ SQL Advance Puzzles https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com