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