Click here to Skip to main content
14,579,188 members
Rate this:
Please Sign up or sign in to vote.
See more:
1. List all the directors who directed a film in a leap year by using SELECT statement
2. Find the film(s) with the largest cast using SELECT statement)

	ACTOR (aid, fname, lname, gender)
	MOVIE (mid, name, year, rank)
	DIRECTOR (did, fname, lname)
	CAST (aid, mid, role)
	MOVIE_DIRECTOR (did, mid)

sql_create_ACTOR_table = """ CREATE TABLE IF NOT EXISTS ACTOR (
                                        aid integer PRIMARY KEY,
                                        fname text NOT NULL,
                                        lname text NOT NULL,
                                        gender text NOT NULL
                                        ); """

sql_create_MOVIE_table = """ CREATE TABLE IF NOT EXISTS MOVIE (
                                     mid integer PRIMARY KEY,
                                     name text NOT NULL,
                                     year integer NOT NULL,
                                     rank integer NOT NULL
                                     ); """

 sql_create_CAST_table = """ CREATE TABLE IF NOT EXISTS CAST (
                                    aid integer NOT NULL,
                                    mid integer NOT NULL,
                                    role text NOT NULL,
                                    FOREIGN KEY (aid) REFERENCES ACTOR (aid),
                                    FOREIGN KEY (mid) REFERENCES MOVIES (mid)
                                    ); """

sql_create_DIRECTOR_table = """ CREATE TABLE IF NOT EXISTS DIRECTOR (
                                        did integer PRIMARY KEY,
                                        fname text NOT NULL,
                                        lname text NOT NULL
                                        ); """

sql_create_MOVIE_DIRECTOR_table = """ CREATE TABLE IF NOT EXISTS MOVIE_DIRECTOR (
                                              did integer COMPOSITE KEY,
                                              mid integer COMPOSITE KEY
                                              ); """


What I have tried:

I have tried to make these queries but they are not giving any output on jupyter notebook.
Posted
Updated 18-Jun-20 11:16am
Comments
Garth J Lancaster 4-Jun-20 4:10am
   
This looks like homework - we don't do people's homework for them. You say 'not giving any output' .. ok, why don't you update your question (use 'Improve Question') with some data samples for each table, then show the SQL queries you tried - that way 'we' may be able to assist you .. else, we only have your word that you've tried
Rate this:
Please Sign up or sign in to vote.

Solution 4

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
   
v3
Comments
Maciej Los 5-Jun-20 13:09pm
   
5ed!
Richard Deeming 5-Jun-20 13:10pm
   
Even taking the modulus isn't enough to properly identify a leap year:
Leap year - Wikipedia[^]
Alice Norman 6-Jun-20 1:44am
   
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
Alice Norman 6-Jun-20 1:48am
   
The error below is not leaving from the past two three days and where is the repitition?

---------------------------------------------------------------------------
IntegrityError Traceback (most recent call last)
<ipython-input-4-c34c20bb3abd> in <module>
483
484 if __name__ == '__main__':
--> 485 main()

<ipython-input-4-c34c20bb3abd> in main()
353 ACTOR_5 = (5, 'fname5', 'lname5', 'male')
354 # insert_ACTOR
--> 355 insert_ACTOR(conn, ACTOR_1)
356 insert_ACTOR(conn, ACTOR_2)
357 insert_ACTOR(conn, ACTOR_3)

<ipython-input-4-c34c20bb3abd> in insert_ACTOR(conn, ACTOR)
43 VALUES(?,?,?,?) '''
44 cur = conn.cursor()
---> 45 cur.execute(sql, ACTOR)
46 return cur.lastrowid
47

IntegrityError: UNIQUE constraint failed: ACTOR.aid
CHill60 8-Jun-20 8:20am
   
I have updated my solution
Rate this:
Please Sign up or sign in to vote.

Solution 1

We are more than willing to help those that are stuck: but that doesn't mean that we are here to do it all for you! We can't do all the work, you are either getting paid for this, or it's part of your grades and it wouldn't be at all fair for us to do it all for you.

So we need you to do the work, and we will help you when you get stuck. That doesn't mean we will give you a step by step solution you can hand in!
Start by explaining where you are at the moment, and what the next step in the process is. Then tell us what you have tried to get that next step working, and what happened when you did.
   
Rate this:
Please Sign up or sign in to vote.

Solution 2

Take a look at: Python MySQL[^] to learn how to create a database and then create the tables.
   
Rate this:
Please Sign up or sign in to vote.

Solution 5

import sqlite3 as sql

conn = sql.connect('test.db')
c = conn.cursor()


#1. List all the directors who directed a film in a leap year by using SELECT statement
for row in c.execute("""SELECT fname, m.year from director d
                    INNER JOIN movie_director md on md.did = d.did
                    INNER JOIN movie m on m.mid = md.mid
                    WHERE m.year % 4 = 0;
                    """):
    print(row)

#2. Find the film(s) with the largest cast using SELECT statement)
SELECT c.mid,  m.name, maxCast
from cast c,
(
	SELECT cc.mid, count(*) as maxCast
	from cast cc
	group by cc.mid
)
inner join movie m on m.mid = c.mid
where maxCast = 
(
	SELECT max(maxCast)
	from
	(
		select ccc.mid, count(*) as maxCast
		from cast ccc
		GROUP by ccc.mid
	)
)
group by maxCast


c.close()


Also, you've issue in movie_director you wrote it wrong (syntax error)
   
Comments
CHill60 19-Jun-20 6:05am
   
Reasons for my vote of 2:
- for #1 you have essentially just reposted what was in Solution 4
- for #2 the SQL produces syntax errors
- #2 also contains repetition. Instead of writing the same query twice consider using temporary tables or a common table expression e.g.
;with cte AS
(
	select mid, COUNT(*) as CastCount FROM CAST GROUP BY mid
)
SELECT * FROM cte 
INNER JOIN MOVIE M ON cte.mid = M.mid
WHERE CastCount = (SELECT MAX(CastCount) FROM CTE)

- Your comment about an "issue in movie_director" isn't clear. It did make me check my solution however and fix the bug. But again, the problem with that table definition had already been posted.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100