Alice Norman wrote in a comment:
For #1: SELECT * from DIRECTOR WHERE did in
(SELECT did from MOVIE_DIRECTOR and MOVIE WHERE MOVIE.year/4 = 0)
As leap year is one that when divided by 4 result is 0
It is not true that a leap year is one where the result after dividing by 4 is 0 - you need to find the MODULUS of dividing the year by 4 - i.e. the remainder must be 0. You can do that with
WHERE MOVIE.year % 4 = 0
or
WHERE MOD(MOVIE.year, 4) = 0
The latter is MySQL specific. I prefer to use % as it can be used on other platforms.
That is not how you join tables in MySql according to the documentation at
MySQL :: MySQL 8.0 Reference Manual :: 13.2.10.2 JOIN Clause[
^]. Perhaps you meant
SELECT * FROM DIRECTOR WHERE did IN
(SELECT did FROM MOVIE_DIRECTOR INNER JOIN MOVIE ON MOVIE_DIRECTOR.mid = MOVIE.mid WHERE MOVIE.year % 4 = 0)
In fact, there is no need for that sub-query. You can get the same effect just by using JOINs like this
SELECT *
FROM DIRECTOR
INNER JOIN MOVIE_DIRECTOR ON DIRECTOR.did = MOVIE_DIRECTOR.did
INNER JOIN MOVIE ON MOVIE_DIRECTOR.mid = MOVIE.mid
WHERE MOVIE.year % 4 = 0
Finally, with your key on the MOVIE_DIRECTOR table - I've personally never seen it done that way. I would have used
CREATE TABLE IF NOT EXISTS MOVIE_DIRECTOR (
did integer,
mid integer,
PRIMARY KEY (mid, did));
EDIT after OP comments:
Alice Norman wrote in a comment:
what about #3? it is: Find all actors who acted only in films before 1960
is it?
SELECT name FROM ACTOR WHERE year>1960
but the years table is is movies not in actor
These questions are all about making you comfortable with tables JOIN
You might find the following links useful
Visual Representation of SQL Joins[
^]
Understanding JOINs in SQL Server[
^]
OP is also getting an error
Quote:
IntegrityError: UNIQUE constraint failed: ACTOR.aid
You have added somewhere a constraint that Actor names or actor Ids must be unique. You have already entered an actors details, and now you are trying to enter them again.
Also see @Richard-Deeming 's comment
Quote:
Even taking the modulus isn't enough to properly identify a leap year:
and the link he provided
Leap year - Wikipedia[
^].At some stage you will probably want to put that into a User-defined function (or filter the results in the UI). It's probably enough just to use modulus 4 at this stage of your learning but don't lose sight of this inaccuracy