Consider a simple database for keeping track of directors and their movies. The
ID: 3924197 • Letter: C
Question
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?
Explanation / Answer
select Did,nane from Director where DId in(select DId from Directed where count(MId)>1 group by MId)