SQL Puzzle: Movies With 2 different Villains

List the movies that have exactly two different villains. check the Sample data and expected output below.

Input Data:

Expected Output:

Data Script:

CREATE TABLE Movies
(
	ID INT,
	MovieName NVARCHAR(150),
	Actor NVARCHAR(100),
	Role NVARCHAR(150)
)

INSERT INTO Movies
VALUES
	(1,'Pushpa','AA','Hero'),
	(2,'Pushpa','Sunil','Villain'),
	(3,'Pushpa','Sunil','Villain'),
	(4,'F10','Rocky','Villain'),
	(5,'F10','Jhon','Villain'),
	(6,'Transformers','Nick','Hero'),
	(7,'Transformers','Rock','Villain'),
	(8,'Transformers','Rockey','Villain'),
	(9,'Transformers','vikki','Villain'),
	(10,'Pushpa-2','Sunil','Villain'),
	(11,'Pushpa-2','Fahad','Villain')

SELECT * FROM Movies

Please try to answer the question, if you still not able to get it click below show button to see the answer.

        
SELECT * FROM Movies
WHERE MovieName IN (
						SELECT MovieName FROM Movies
						WHERE Role = 'Villain'
						GROUP BY MovieName HAVING COUNT(DISTINCT Actor) =2
                   ) 
AND Role = 'Villain'

        

SQL Puzzle: Putting Hyderabad First

How can we ensure that, based on the provided dataset below, 'Hyderabad' always appears in the first row of the 'StateCapital' column, with the remaining column values sorted in ascending order.

Input Data:

Expected Output:

Data Script:

CREATE TABLE StateDetails
(
	State NVARCHAR(150) ,
	StateCapital NVARCHAR(150)
)

INSERT INTO StateDetails
VALUES
	('TamilNadu','Chennai'),
	('AndhraPradesh', 'Amaravathi'),
	('Telangana','Hyderabad'),
	('Karnataka','Bengaluru'),
	('MahaRastra','Mumbai')

SELECT  * FROM StateDetails

Please try to answer the question, if you still not able to get it click below show button to see the answer.

        
SELECT * FROM StateDetails
ORDER BY IIF(StateCapital ='Hyderabad',0,1),StateCapital