SQL PUZZLE | The OUTPUT Puzzles – SQL Interview QUESTION

In this post you have to go through the sample data given below and answer the questions below. There are two tables called Employee and Department. Please check their definitions with data.

Sample Data for Puzzles

--

CREATE TABLE Employee
(
	 EmpID INT
	,EmpName VARCHAR(10)
	,DeptId INT
)
GO

INSERT INTO Employee VALUES
(1,'Pawan',1),
(2,'Avtaar',1)
GO

CREATE TABLE Department
(
	 DeptId INT
	,DeptName VARCHAR(20)
)
GO

INSERT INTO Department VALUES
(1,'IT'),
(2,'HR')
GO

--

Puzzles

Puzzle 1 : What is the OUTPUT of below query?

--

SELECT * FROM Employee Department

--

Answer – Here we will get all the rows from Employee table. The word department present there just acts as alias 🙂

--

EmpID       EmpName    DeptId
----------- ---------- -----------
1           Pawan      1
2           Avtaar     1

(2 rows affected)

--

Puzzle 2 : What is the OUTPUT of below query?

--

SELECT * FROM Employee,Department

--

Answer – Here we will get all the rows from the cartesian product of Employee table department table 🙂

--

EmpID       EmpName    DeptId      DeptId      DeptName
----------- ---------- ----------- ----------- --------------------
1           Pawan      1           1           IT
2           Avtaar     1           1           IT
1           Pawan      1           2           HR
2           Avtaar     1           2           HR

(4 rows affected)

--

Puzzle 3 : What is the OUTPUT of below query?

--

SELECT * FROM Employee.Department

--

Answer – Here we will get error. Here SQL Server considers Employee as the schema name and department table as table inside that schema:)

--

Msg 208, Level 16, State 1, Line 1
Invalid object name 'Employee.Department'.

--

Puzzle 4 : What is the OUTPUT of below query?

--

SELECT * FROM Employee[Department]

--

Answer – Here we will get all the rows from Employee table. The word department present there just acts as alias 🙂

--

EmpID       EmpName    DeptId
----------- ---------- -----------
1           Pawan      1
2           Avtaar     1

(2 rows affected)

--

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.

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