--Create a table called PQ..

CREATE TABLE PQ
(
ID INT
,Name VARCHAR(10)
,Typed VARCHAR(10)
)

--Insert data
INSERT INTO PQ(ID,Name,Typed) VALUES  (1,'P',NULL) , (1,NULL,'Q')

--Verify data
SELECT * FROM  PQ

--

/************   SOLUTION 1    | Pawan Kumar Khowal     ****************/


SELECT p.Id,p.Name, (SELECT r.Typed from PQ r WHERE p.ID = r.Id AND r.Typed IS NOT NULL) Typed FROM PQ p WHERE p.Name IS NOT NULL



/************   SOLUTION 2    | Pawan Kumar Khowal     ****************/

SELECT Id, MIN(CASE WHEN name IS NOT NULL THEN name ELSE NULL END) Name
, MIN(CASE WHEN typed IS NOT NULL THEN typed END) Typed 
FROM pq 
GROUP BY Id


/************   SOLUTION 3   | Pawan Kumar Khowal     ****************/

;WITH CTE1 AS
(
	SELECT ID, Name , ROW_NUMBER() OVER (ORDER BY (SELECT 1)) rnk FROM pq WHERE Name IS NOT NULL
)
,CTE2 AS
(
	SELECT ID, Typed , ROW_NUMBER() OVER (ORDER BY (SELECT 1)) rnk FROM pq WHERE Typed IS NOT NULL
)
SELECT c1.ID, Name, Typed FROM CTE1 c1 INNER JOIN CTE2 c2 ON c1.rnk = c2.rnk


--

-Create a sample movie table
CREATE TABLE [Movie]
(

[MName] [varchar] (10) NULL,
[AName] [varchar] (10) NULL,
[Roles] [varchar] (10) NULL
)

GO

--Insert data in the table

INSERT INTO Movie(MName,AName,Roles)
SELECT 'A','Amitabh','Actor'
UNION ALL
SELECT 'A','Vinod','Villan'
UNION ALL
SELECT 'B','Amitabh','Actor'
UNION ALL
SELECT 'B','Vinod','Actor'
UNION ALL
SELECT 'D','Amitabh','Actor'
UNION ALL
SELECT 'E','Vinod','Actor'

--Check your data
SELECT MName , AName , Roles FROM Movie

—

/*
** Solution 1
*/
select * from Movie where MName in
(select MName from Movie where Roles = ‘Actor’ and (AName = ‘Amitabh’ or AName = ‘Vinod’)
group by MName having count(MName)>1)

/*
** Solution 2
*/
SELECT m1.* FROM Movie m1 INNER JOIN Movie m2 ON m1.MName = m2.MName
WHERE
( m1.AName =’amitabh’ AND m2.AName =’vinod’ OR m2.AName =’amitabh’ AND m1.AName =’vinod’)
AND(m1.Roles =’Actor’)
AND(m2.Roles =’Actor’)

/*
** Solution 3
*/
SELECT p.* FROM Movie p
CROSS APPLY
(
SELECT COUNT(*) w FROM Movie t
WHERE ( t.AName = ‘Amitabh’ OR t.AName = ‘Vinod’ ) AND t.roles = ‘Actor’ AND t.MName = p.MName
)r
WHERE ( p.AName = ‘Amitabh’ OR p.AName = ‘Vinod’ ) AND p.roles = ‘Actor’ AND r.w > 1

—

--Create table

CREATE TABLEEmpBirth
(
 EmpId INT  IDENTITY(1,1) 
,EmpName VARCHAR(50) 
,BirthDate DATETIME 
)

--Insert Data
INSERT INTO EmpBirth(EmpName,BirthDate)
SELECT 'Pawan' , '12/04/1983'
UNION ALL
SELECT 'Zuzu' , '11/28/1986'
UNION ALL
SELECT 'Parveen', '05/07/1977'
UNION ALL
SELECT 'Mahesh', '01/13/1983'
UNION ALL
SELECT'Ramesh', '05/09/1983'

--Verify data
SELECT EmpId,EmpName,BirthDate FROM EmpBirth

--

/************   SOLUTION 1    | Pawan Kumar Khowal     ****************/

;WITH CTE AS 
(  
		SELECT EmpId , EmpName,BirthDate , MONTH(BirthDate) AS Mont, DAY(BirthDate) AS days
        FROM EmpBirth
)
SELECT EmpId , EmpName,BirthDate FROM CTE WHERE Mont = 5 AND days >= 7 AND days <= 15


/************   SOLUTION 2    | Pawan Kumar Khowal     ****************/

;WITH CTE  
AS ( 
	SELECT EmpId , EmpName,BirthDate,
	CASE 
      WHEN  MONTH(BirthDate) < 10 THEN  '0' + CAST(MONTH(BirthDate) AS VARCHAR(1))
			ELSE 
				CAST(MONTH(BirthDate) AS VARCHAR(2))
      END 
	+ CASE WHEN DAY(BirthDate) < 10 THEN '0' + CAST(DAY(BirthDate) AS VARCHAR(1))
            ELSE
                  CAST(DAY(BirthDate) AS VARCHAR(2))
            END FormattedDOB
FROM EmpBirth
)
SELECT EmpId , EmpName,BirthDate FROM CTE WHERE FormattedDOB BETWEEN '0507' AND '0515'



--