Finding Missing Sequence Dates using SQL

   Part - 28

Link for video answer:

Queries:

CREATE TABLE tblEmployee_HireDates (HireDate DATE)

INSERT tblEmployee_HireDates values ('2020-01-01'),('2020-01-03'),('2020-01-05'),('2020-01-07'),('2020-01-10'),('2020-01-12')

SELECT * FROM tblEmployee_HireDates

DECLARE @StartDate Date, @EndDate Date;

SELECT @StartDate = MIN(HireDate) FROM tblEmployee_HireDates

SELECT @EndDate = MAX(HireDate) FROM tblEmployee_HireDates

;WITH Emp_CTE (DateSequence) AS 

(

SELECT @StartDate --> Anchor Query

UNION ALL

SELECT DATEADD(DD,1,DateSequence) FROM Emp_CTE WHERE DateSequence < @EndDate --> Recusive Query

)

--SELECT * FROM Emp_CTE

SELECT EC.DateSequence AS Missing_Sequence FROM Emp_CTE EC 

LEFT JOIN tblEmployee_HireDates E

ON EC.DateSequence = E.HireDate 

WHERE E.HireDate IS NULL

Option (MAXRECURSION 0)

You tube Palylists:

SQL Interview Questions: 

https://www.youtube.com/watch?v=YZB8p2Anhr4&list=PLgGPoIouio2B3sXJQd_pQwy6UWo68vp6j

SQL Tutorial Series for Beginners:

https://www.youtube.com/watch?v=Tfu4EQ1E8cs&list=PLgGPoIouio2DaI1lC1Eb0nMmYnk-28dft

SQL Tips and Tricks:

https://www.youtube.com/watch?v=zq6IEqJKnGk&list=PLgGPoIouio2A12x8PhqUcOIO1EyuMpPCc

SSIS Video Series:

https://www.youtube.com/watch?v=1HkSyYl7Pr0&list=PLgGPoIouio2Amr9zjBmZQre64HgkwMXjk

SQL In 2 Minutes:

https://www.youtube.com/watch?v=xDqueXgL7r8&list=PLgGPoIouio2AXEk2WixZFJEcDKWEeZFYU

 

Finding tables with identity column using SQL

   Part - 27

Link for video answer:

SQL Scripts:

--**************************************************************************************

-- Q27 -- Query to find tables with Identity columns in SQL

--**************************************************************************************

--> Identity Column

--> IDENTITY [ (seed , increment) ]

CREATE TABLE new_employees   

(  

 Empid int IDENTITY(1,1),  --> Identity column

 EmpName varchar (20),  

 Salary int,  

 EmpLocation varchar(30)  

);  

sp_help new_employees

insert new_employees values ('Richard',10000,'US'),('John',20000,'UK'),('George',25000,'India')

insert new_employees values (4,'Richard',10000,'US')

-- IDENTITY_INSERT is ON

SELECT * FROM new_employees

--> Method 1:

--> To Find Table and column names with identity columns 

select object_name(object_id) AS TABLE_NAME, Name AS COLUMN_NAME, * from sys.identity_columns 

select object_name(object_id)AS TABLE_NAME, Name AS COLUMN_NAME,  seed_value, increment_value from sys.identity_columns 

--> Method 2:

select object_name(object_id) AS TABLE_NAME, name AS COLUMN_NAME,* from sys.columns where is_identity = 1

--> Method 3:

--> To fect the Table Name and Column Name of Identity Tables:

select TABLE_NAME, COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS

where COLUMNPROPERTY(object_id(TABLE_SCHEMA+'.'+TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1

order by TABLE_NAME 

select * from INFORMATION_SCHEMA.COLUMNS

--> To fetch Table_name, identity column name, seed, increment, max identity value

select TABLE_NAME, COLUMN_NAME, IDENT_SEED(TABLE_SCHEMA + '.' + TABLE_NAME) AS Seed, 

IDENT_INCR(TABLE_SCHEMA + '.' + TABLE_NAME) AS Increment,

IDENT_CURRENT(TABLE_SCHEMA + '.' + TABLE_NAME) AS Current_Identity_Value

from INFORMATION_SCHEMA.COLUMNS

where COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA+'.'+TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1

order by TABLE_NAME 

-- COLUMNPROPERTY( id , column , property )   

SELECT DISTINCT OBJECT_ID('tblCountry')

--> Method 4:

SELECT 

tables.TABLE_NAME,

IDENT_SEED(tables.TABLE_SCHEMA + '.' + tables.TABLE_NAME) AS Seed,

IDENT_INCR(tables.TABLE_SCHEMA + '.' + tables.TABLE_NAME) AS Increment,

IDENT_CURRENT(tables.TABLE_SCHEMA + '.' + tables.TABLE_NAME) AS Current_Identity_Value

FROM INFORMATION_SCHEMA.TABLES tables 

WHERE OBJECTPROPERTY(OBJECT_ID(tables.TABLE_SCHEMA + '.' + tables.TABLE_NAME), 'TableHasIdentity') = 1

AND tables.TABLE_TYPE = 'BASE TABLE'

GO

SELECT * FROM INFORMATION_SCHEMA.TABLES

--> OBJECT PROPERTIES

TableHasIdentity

TableHasPrimaryKey

TableHasForeignKey

TableHasIndex

TableHasClustIndex

TableHasNonclustIndex

TableIsMemoryOptimized

TableInsertTrigger

TableTemporalType

 

Printing Star (*) Pattern using SQL

   Part - 26

--*********************************************************

-- 26 - Printing Star (*) Pattern using SQL

--*********************************************************

Click below for you tube answer:

-- REPLICATE --> 

SELECT REPLICATE('String ',6) AS Derived_String

SELECT REPLICATE('String' + SPACE(3),6) AS Derived_String

Printing 5*5 Pattern

-- 5 * 5 

DECLARE @rows int = 5,@columns int = 5         

WHILE @rows > 0                 

BEGIN                          

PRINT REPLICATE('*'+SPACE(2), @columns)    

SET @rows = @rows - 1            

END

Printing 5*7 Pattern

-- 5 * 7 

DECLARE @rows int = 5, @columns int = 7              

WHILE @rows > 0                 

BEGIN                          

PRINT REPLICATE('*'+SPACE(2), @columns)    

SET @rows = @rows - 1            

END

Printing Diamond Pattern:

DECLARE @counter INT = 0,@maxid INT = 9,@space INT = 1

WHILE (@counter<@maxid)

BEGIN

PRINT SPACE((@maxid-@space)/2)+ REPLICATE('*',@space)

SET @counter = @counter + 1

IF(@counter<=(@maxid/2))

SET @space = @space+2

ELSE

SET @space = @space-2

END

Iterations for Diamond Pattern:

Iteration 1: 

@counter = 0 @space = 1 --> Prints 4 spaces and 1 * -->@counter = 1 <= 4  @space = 1 + 2 = 3 

Iteration 2:

@counter = 1 @space = 3 --> Prints 3 spaces and 3 * --> @counter = 2 <= 4  @space = 3 + 2 = 5

Iteration 3:

@counter = 2 @space = 5 --> Prints 2 spaces and 5 * --> @counter = 3 <= 4  @space = 5 + 2 = 7

Iteration 4:

@counter = 3 @space = 7 --> Prints 1 spaces and 7 * --> @counter = 4 <= 4  @space = 7 + 2 = 9

Iteration 5:

@counter = 4 @space = 9 --> Prints 0 spaces and 9 * --> @counter = 5 <= 4  @space = 9 - 2 = 7

Iteration 6:

@counter = 5 @space = 7 --> Prints 1 spaces and 7 * --> @counter = 6 <= 4  @space = 7 - 2 = 5

Iteration 7:

@counter = 6 @space = 5 --> Prints 2 spaces and 5 * --> @counter = 7 <= 4  @space = 5 - 2 = 3

Iteration 8:

@counter = 7 @space = 3 --> Prints 3 spaces and 3 * --> @counter = 8 <= 4  @space = 3 - 2 =1

Iteration 9:

@counter = 8 @space = 1 --> Prints 4 spaces and 1 * --> @counter = 9 <= 4  @space = 1-2 = -2