Actors and Directors Who Cooperated At Least Three Times

Write a SQL query for a report that provides the pairs (actor_id, director_id) where the actor has cooperated with the director at least three times. Return the result with the director at least three times.

table name: ActorDirector

Solution:

select actor_id,director_id from ActorDirector group by actor_id,director_id
having count(actor_id)>=3

Output:

SQL Script:

CREATE TABLE [dbo].[ActorDirector](
       [actor_id] [int] NULL,
       [director_id] [int] NULL,
       [timestamp] [int] NOT NULL,
 CONSTRAINT
[PK_ActorDirector] PRIMARY KEY CLUSTERED
(
       [timestamp] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE
=
OFF, IGNORE_DUP_KEY
=
OFF, ALLOW_ROW_LOCKS
=
ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[ActorDirector] ([actor_id], [director_id], [timestamp]) VALUES (1, 1, 0)
GO
INSERT [dbo].[ActorDirector] ([actor_id], [director_id], [timestamp]) VALUES (1, 1, 1)
GO
INSERT [dbo].[ActorDirector] ([actor_id], [director_id], [timestamp]) VALUES (1, 1, 2)
GO
INSERT [dbo].[ActorDirector] ([actor_id], [director_id], [timestamp]) VALUES (1, 2, 3)
GO
INSERT [dbo].[ActorDirector] ([actor_id], [director_id], [timestamp]) VALUES (1, 2, 4)
GO
INSERT [dbo].[ActorDirector] ([actor_id], [director_id], [timestamp]) VALUES (2, 1, 5)
GO
INSERT [dbo].[ActorDirector] ([actor_id], [director_id], [timestamp]) VALUES (2, 1, 6)
GO