Tags

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


T-SQL Query | [Movie Puzzle] – Write a query which will gets the movie details where Mr. Amitabh and Mr. Vinod acted together and their role is actor.

Sample Input

MName AName Roles
A Amitabh Actor
A Vinod Villain
B Amitabh Actor
B Vinod Actor
D Amitabh Actor
E Vinod Actor

Expected Output

MName AName Roles
B Vinod Actor
B Amitabh Actor

Rules/Restrictions

  • The solution should be should use “SELECT” statement or “CTE”.
  • Send your solution to pawankkmr@gmail.com
  • Do not post you solution in comments section

Script

Use the below script to generate the source table and fill them up with the sample data.

–


-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

SolutionS

—

/*
** 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

—

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

http://MSBISkills.com