Question of the day

Hi folks, I got a question from my junior in my organization-

Input the name of a person born in Generation X and output the names of all of their descendants born in Generation Z, but only if these descendants are brother and sister (have the same parent)

/*

Create table Test (id int ,name varchar(100),Parent_ID int null,Generation varchar(1),Gender varchar(6))

Insert into test (id,name,Parent_ID,Generation,Gender)

select 1,'John',null,'x','male'

union

select 2,'Jill',1,'y','Female'

union

select 3,'Andy',2,'z','male'

union

select 4,'Ralph',2,'z','male'

union

select 5,'Lisa',null,'x','Female'

union

select 6,'Steve',5,'y','male'

union

select 7,'Sean',6,'y','male'

union

select 8,'Sarah',6,'y','Female'

union

select 9,'Emily',7,'z','Female'

union

select 10,'Matt',7,'z','male'

Drop table test

*/

select * from Test

If input is lisa

Then output should be:

 

-------------

Answer :

Declare @name varchar(50)='Lisa'

;with cte

as

(

Select id,name,Parent_id,generation,gender from test where name =@name and generation='x'

Union All

Select t.id, t.name,t.Parent_id,t.generation,t.gender from test t

join cte c on c. id=t.parent_id

)

,condition

as

(

select Parent_id,COUNT(*) cnt  from cte Where generation='z' Group by parent_id having COUNT(*)>1

)

,sibling

as

(

select parent_id,Count(gender) cntg from (select distinct c.parent_id,c.gender from cte c join condition co on co.parent_id=c.parent_id) a group by parent_id having count(gender)>1

)

select c.name from cte c join sibling s on c.parent_id=s.parent_id

 

Splitting Data Query

DECLARE @STR VARCHAR(100) = 'Hi how are you doing? I am good.'
DECLARE @dev VARCHAR(1) = ' '

SELECT Split.a.value('.', 'VARCHAR(100)') AS E
FROM (
 SELECT Cast('<M> ' + Replace(Replace(@str, @dev, @dev + '</M><M>'), '.', '') + ' </M>' AS XML) AS yo
 ) a
CROSS APPLY yo.nodes('/M') AS SPLIT(a)

Encrypt And Decrypt Data Using Certificate In SQL Server

CREATE DATABASE Dbavimal
GO

USE Dbavimal
GO

--Create MasterKey
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'DBAVimal!';
GO

-- Create Certificate
CREATE CERTIFICATE [EncryptionCert]
 WITH SUBJECT = 'DBAEncryption'
GO

-- Symmetric Key
CREATE SYMMETRIC KEY SymmetricDBAVimalKey
 WITH ALGORITHM = AES_192 ENCRYPTION BY CERTIFICATE [EncryptionCert]
GO

--Use Symmetric Key
OPEN SYMMETRIC KEY SymmetricDBAVimalKey DECRYPTION BY CERTIFICATE [EncryptionCert]
GO

-----------------------------
CREATE FUNCTION [dbo].[fn_Encrypt] (@Data VARCHAR(max))
RETURNS VARBINARY(256)
AS
BEGIN
 DECLARE @Result VARBINARY(256)

 SET @Result = EncryptByKey(Key_GUID('SymmetricDBAVimalKey'), @Data)

 RETURN @Result
END
GO

-------------------------------
CREATE FUNCTION [dbo].[fn_Decrypt] (@Data VARCHAR(max))
RETURNS VARCHAR(max)
AS
BEGIN
 DECLARE @Result VARCHAR(max)

 SET @Result = DecryptByKey(@Data)

 RETURN @Result
END
GO

-----------------------------------
--Test the result (Same way you can store your data on tables)
DECLARE @Str VARCHAR(500) = 'Hello'

PRINT '====Original Data============'
PRINT @Str
PRINT '============================='

DECLARE @EncryptedData VARCHAR(256)
DECLARE @DecryptedData VARCHAR(256)

SET @EncryptedData = [dbo].[fn_Encrypt](@Str)

PRINT '====Encrypted Data==========='
PRINT @EncryptedData
PRINT '============================='

SET @DecryptedData = [dbo].[fn_Decrypt](@EncryptedData)

PRINT '====Data After Decryption===='
PRINT @DecryptedData
PRINT '============================='

USE master
GO

DROP DATABASE Dbavimal