Consider a simple database for keeping track of directors an

Consider a simple database for keeping track of directors and their movies. The schema is as follows:

DIRECTOR(DId, Name, DateOfBirth)
MOVIE(MId, Title, ReleaseDate, Budget)
DIRECTED(DId, MId)

1. DId is a numeric identifier given to each director.
2. DateOfBirth is the director’s date of birth.
3. MId is a numeric identifier given to each movie.
4. ReleaseDate is the date when the movie was released.
5. In DIRECTOR, we may store information about directors that have not released a movie yet.
6. All movies in MOVIE have at least one director.
7. Directors can direct multiple movies.
8. A movie may have multiple directors.

Using the below information,

.headers ON
.mode column
PRAGMA foreign_keys = ON;

CREATE Table Director (
DId INT NOT NULL,   
Name TEXT NOT NULL,   
DateOfBirth DATE NOT NULL,   
PRIMARY KEY (DId)
);

CREATE Table Movie (
MId INT NOT NULL,   
Title TEXT NOT NULL,   
ReleaseDate DATE NOT NULL,   
Budget INT NOT NULL,   
PRIMARY KEY (MId)
);

CREATE Table Directed (   
DId INT NOT NULL,   
MId INT NOT NULL,   
FOREIGN KEY (DId) REFERENCES Director (DId),
FOREIGN KEY (MId) REFERENCES Movie (MId),   
PRIMARY KEY (DId, MId)
);

INSERT INTO Director VALUES (1, \'Don Argott\', \'1969-02-16\');
INSERT INTO Director VALUES (2, \'Martin Scorsese\', \'1954-10-30\');
INSERT INTO Director VALUES (3, \'Tesla Ellis\', \'1969-07-14\');
INSERT INTO Director VALUES (4, \'Dana Burkley\', \'1969-12-02\');
INSERT INTO Director VALUES (5, \'Richard Donnely\', \'1914-04-03\');
INSERT INTO Director VALUES (6, \'Ian Brunell\', \'1977-03-09\');
INSERT INTO Director VALUES (7, \'Francis Ford Coppola\', \'1939-04-07\');
INSERT INTO Director VALUES (8, \'Director With No Movies\', \'2013-01-01\');

INSERT INTO Movie VALUES (1, \'The Art of the Steal\', \'2012-02-16\', 450000);
INSERT INTO Movie VALUES (2, \'Brown Paper Bag\', \'1999-10-30\', 4750000);
INSERT INTO Movie VALUES (3, \'Birthday Wish\', \'1991-09-22\', 4000);
INSERT INTO Movie VALUES (4, \'Titanic\', \'1991-02-11\', 500000);
INSERT INTO Movie VALUES (5, \'Barney and the Boys\', \'1989-02-06\', 9450000);
INSERT INTO Movie VALUES (6, \'The Godfather Part II\', \'2012-08-19\', 1333666);

INSERT INTO Directed VALUES (1, 1);
INSERT INTO Directed VALUES (1, 2);
INSERT INTO Directed VALUES (2, 2);
INSERT INTO Directed VALUES (2, 3);
INSERT INTO Directed VALUES (3, 3);
INSERT INTO Directed VALUES (4, 5);
INSERT INTO Directed VALUES (5, 4);
INSERT INTO Directed VALUES (6, 4);
INSERT INTO Directed VALUES (7, 6);

Write a query that finds the directors (and returns DId and Name) that have directed movies, but never by themselves.

Query should return:
2 Martin Scorsese
3 Tesla Ellis
5 Richard Donnely
6 Ian Brunell

But what is the query?

Solution

select Did,nane from Director where DId in(select DId from Directed where count(MId)>1 group by MId)