Advertisements

SQL Server Question | Can we declare a variable/ define a column as INTEGER or DEC?

Tags


SQL Server Question | Can we declare a variable/ define a column as INTEGER or DEC?

Well yes we can. We can declare a variable as INTEGER. INTEGER is a synonym for INT and DEC is a synonym for DECIMAL. They are called as data type synonyms.

This was asked to me by a client developer. The actual question was – If you define a column as INTERGER and then you see the table properties, What will you see in the properties – INT or INTEGER.?

Be honest – Have you heard of data type synonyms? 🙂

Data type synonyms are included in SQL Server for ISO compatibility. The following table lists the synonyms and the SQL Server system data types that they map to.

Well the Data type synonyms can be used instead of the corresponding base data type name in the following places-
1. Data definition language (DDL) statements, such as CREATE TABLE, CREATE PROCEDURE
2. DECLARE @variable.

Below if the complete table of data type synonyms-

Synonym SQL Server system data type
Binary varying varbinary
char varying varchar
character char
character char(1)
character( n ) char(n)
character varying( n ) varchar(n)
Dec decimal
Double precision float
float[(n)] for n = 1-7 real
float[(n)] for n = 8-15 float
integer int
national character( n ) nchar(n)
national char( n ) nchar(n)
national character varying( n ) nvarchar(n)
national char varying( n ) nvarchar(n)
national text ntext
timestamp rowversion

Note once the the object is created, the synonyms have no visibility. For example if you define a column with INTEGER, once the table is created you will see Int when you check the table properties. When the object is created, the object is assigned the base data type that is associated with the synonym. There is no record that the synonym was specified in the statement that created the object.

Example.

--

CREATE TABLE TestDataSyn
(
	 Id INTEGER
)
GO

--


Screen shot showing that there is no record that the synonym was specified in the statement that created the object

You can read more from below.
https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-type-synonyms-transact-sql?view=sql-server-2017

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

Updated SQL Puzzle | The INNER JOIN – Two Rows PUZZLE | Solution without UNION/UNION All


Updated SQL Puzzle | The INNER JOIN – Two Rows PUZZLE | Solution without UNION/UNION All

This is something really interesting. Here you have to join these two tables and provide the resulted data but the matching rows into Multiple Rows. The Challenge here is to do this without using UNION/UNION ALL. Please check out the sample input and the expected output.

Sample Input

Table 1 –

Id Vals
1 Pawan
2 Sharlee
3 Harry

Table 2 –

Id Vals
1 Kumar
2 Diwan

Expected Output

Id Vals
1 Pawan
1 Kumar
2 Sharlee
2 Diwan

Script – DDL and INSERT Sample Data

--

CREATE TABLE T1N1
(
	 Id INT
	,Vals Varchar(10)
)
GO

INSERT INTO T1N1 VALUES
(1,'Pawan'),
(2,'Sharlee'),
(3,'Harry')
GO

CREATE TABLE T1N2
(
	 Id INT
	,Vals Varchar(10)
)
GO

INSERT INTO T1N2 VALUES
(1,'Kumar'),
(2,'Diwan')
GO

SELECT * FROM T1N1
GO
SELECT * FROM T1N2
GO


--

SOLUTION 1

--

SELECT Id,Vals FROM 
(
	SELECT ISNULL(a.Id,B.Id) Id, ISNULL(a.Vals,B.Vals) Vals , COUNT(*) OVER (PARTITION BY ISNULL(a.Id,B.Id)) cnt
	FROM T1N1 a
	FULL OUTER JOIN T1N2 b ON a.Id = B.Id and a.Vals = b.Vals
)z WHERE cnt > 1

--

OUTPUT – 1

--

Id          Vals
----------- ----------
1           Pawan
1           Kumar
2           Diwan
2           Sharlee

(4 row(s) 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 NOT in Range Puzzle – SINGLE SELECT | SQL Interview Question

Tags

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


SQL Puzzle | The NOT in Range Puzzle – SINGLE SELECT | SQL Interview Question

In this puzzle you have to JOIN the two tables based on Gr column and figure out whether the val colum’s value is present between the startlimit and the endlimit columns. If not then we need that rows in the Output. Please check out the sample input and the expected output.

Sample Input

Table 1 –

Id Gr Val
1 a 10
2 b 15
3 c 45

Table 2 –

Gr StartLimit EndLimit
a 0 9
a 20 100
b 10 14
b 20 100
c 10 50

Expected Output

Id Gr Val
1 a 10
1 a 10
2 b 15
2 b 15

Script – DDL and INSERT Sample Data

--

CREATE TABLE Prima
(
	 Id INT
	,Gr VARCHAR(10)
	,Val INT
)
GO

INSERT INTO Prima VALUES 
(1,'a',10),
(2,'b',15),
(3,'c',45)

CREATE TABLE Limits
(
	 Gr VARCHAR(10)
	,StartLimit INT
	,EndLimit INT
)
GO

INSERT INTO Limits VALUES 
('a',0,9),
('a',20,100),
('b',10,14),
('b',20,100),
('c',10,50)
GO

--

SOLUTION 1

--

SELECT P.* FROM Prima P
JOIN Limits L ON NOT ( P.Val BETWEEN L.StartLimit AND L.EndLimit )
AND P.Gr = L.Gr

--

OUTPUT – 1

--

Id          Gr         Val
----------- ---------- -----------
1           a          10
1           a          10
2           b          15
2           b          15

(4 rows affected)

--

SOLUTION 2

--

SELECT P.* FROM Prima P
JOIN Limits L ON P.Val NOT BETWEEN L.StartLimit AND L.EndLimit
AND P.Gr = L.Gr


--

OUTPUT – 2

--

Id          Gr         Val
----------- ---------- -----------
1           a          10
1           a          10
2           b          15
2           b          15

(4 rows affected)


--

SOLUTION 3

--

SELECT * 
FROM Prima v
WHERE NOT EXISTS (SELECT NULL FROM Limits r
                  WHERE v.Gr = r.Gr AND
                  v.Val BETWEEN r.StartLimit AND r.EndLimit)

--

OUTPUT – 3

--

Id          Gr         Val
----------- ---------- -----------
1           a          10
1           a          10
2           b          15
2           b          15

(4 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 INNER JOIN – Two Rows PUZZLE | SQL Interview Question

Tags

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


SQL Puzzle | The INNER JOIN – Two Rows PUZZLE | SQL Interview Question

This is something really interesting. Here you have to join these two tables and provide the resultated data but the matching rows into Multiple Rows. Please check out the sample input and the expected output.

Sample Input

Table 1 –

Id Vals
1 Pawan
2 Sharlee
3 Harry

Table 2 –

Id Vals
1 Kumar
2 Diwan

Expected Output

Id Vals
1 Pawan
1 Kumar
2 Sharlee
2 Diwan

Script – DDL and INSERT Sample Data

--

CREATE TABLE T1N1
(
	 Id INT
	,Vals Varchar(10)
)
GO

INSERT INTO T1N1 VALUES
(1,'Pawan'),
(2,'Sharlee'),
(3,'Harry')
GO

CREATE TABLE T1N2
(
	 Id INT
	,Vals Varchar(10)
)
GO

INSERT INTO T1N2 VALUES
(1,'Kumar'),
(2,'Diwan')
GO

SELECT * FROM T1N1
GO
SELECT * FROM T1N2
GO


--

SOLUTION 1

--

SELECT Id,Vals
FROM 
(
	SELECT a.* , 1 Ord
	FROM T1N1 a INNER JOIN T1N2 b ON a.Id = b.Id
	UNION ALL
	SELECT b.* , 2 Ord
	FROM T1N1 a INNER JOIN T1N2 b ON a.Id = b.Id
)x
ORDER BY Id, Ord
GO

--

OUTPUT – 1

--

Id          Vals
----------- ----------
1           Pawan
1           Kumar
2           Sharlee
2           Diwan

(4 rows affected)

--

SOLUTION 2

--

SELECT Id,Vals
FROM 
(
	SELECT a.* , 1 Ord
	FROM T1N1 a CROSS APPLY ( SELECT NULL v FROM T1N2 b WHERE a.Id = b.Id )x
	UNION ALL
	SELECT x.* , 2 Ord
	FROM T1N1 a CROSS APPLY ( SELECT * FROM T1N2 b WHERE a.Id = b.Id )x
)x
ORDER BY Id, Ord
GO

--

OUTPUT – 2

--

Id          Vals
----------- ----------
1           Pawan
1           Kumar
2           Sharlee
2           Diwan

(4 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 | Have you ever gave length in the Float data type declaration? | SQL Interview Question


SQL Server Question | Have you ever gave length in the Float data type declaration? | SQL Interview Question

Well yes we can provide the length while declaring a Float data type, al though it is not compulsory. If you do not specify the length then SQL Server by default take that as FLOAT(53).

Syntax

--

float [ (n) ]

--

n – Where n is the number of bits that are used to store the mantissa of the float number in scientific notation
and, therefore, dictates the precision and storage size. If n is specified, it must be a value between 1 and 53.

Notes –

1. If you specify any value between 1-24 than SQL Server treats n as 24.
2. If you specify any value between 25-53 than SQL Server treats n as 53.
3. If you don’t specify any value than SQL Server treats n as 53.
4. If you specify n then it must be a value between 1 and 53.

N & the Storage Bytes

n value Precision Storage size
1-24 7 digits 4 bytes
25-53 15 digits 8 bytes

Range

Data type Range
float – 1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308

Note

Conversion of float values that use scientific notation to decimal or numeric is restricted to values of precision 17 digits only. Any value < 5E-18 rounds down to 0.

Sample Example – 1 | Float without N

--

CREATE TABLE TestFloat
(
	 Id INT
	,Vals FLOAT
)
GO

OUTPUT
--
Commands completed successfully.

--

Here we have not specified any value so Prec should be 53

Sample Example – 2 | Float with N between 1 – 24

--

CREATE TABLE TestFloat1
(
	 Id INT
	,Vals FLOAT(10)
)
GO


OUTPUT
--
Commands completed successfully.


--

Here we have specified n value between 1-24 so Prec will be 24.

Sample Example – 3 | Float with N between 25-53

--

CREATE TABLE TestFloat2
(
	 Id INT
	,Vals FLOAT(49)
)
GO

OUTPUT
--
Commands completed successfully.


--

Here we have specified n value between 25-53 so Prec will be 53.

Sample Example – 4 | Float with N > 53

--

CREATE TABLE TestFloat3
(
	 Id INT
	,Vals FLOAT(54)
)
GO

--

In this case we shall get the below Error Message.

Msg 2750, Level 16, State 1, Line 34 Column or parameter #2: Specified column precision 54 is greater than the maximum precision of 53.

You can read more from below.
https://docs.microsoft.com/en-us/sql/t-sql/data-types/float-and-real-transact-sql?view=sql-server-2017

Related Puzzle-
https://msbiskills.com/2018/07/19/sql-puzzle-remove-trailing-zeros-puzzle-the-double-precision-float53-data-type-advanced-sql/

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