Anyone who can write following SQL queries?

Question

1.00/5 (3 votes)

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 3-Jun-20 22:02pm

A. B. Dinshaa

Updated 13-Jan-21 23:29pm

Add a Solution

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

4 solutions

Add a Solution

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

CHill60 · Answer 1 · 2020-06-05T04:54:00

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

SQL

WHERE MOVIE.year % 4 = 0

or

SQL

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

SQL

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

SQL

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

SQL

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

OriginalGriff · Answer 2 · 2020-06-03T22:10:00

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.

RickZeeland · Answer 3 · 2020-06-04T00:34:00

Solution 2

Take a look at: Python MySQL[^] to learn how to create a database and then create the tables.

Posted 4-Jun-20 0:34am

RickZeeland

Haris Memon · Answer 4 · 2020-06-18T11:17:00

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)

Anyone who can write following SQL queries?

4 solutions

Solution 4

Solution 1

Solution 2

Solution 5

Add your solution here

Preview 0

Anyone who can write following SQL queries?

4 solutions

Solution 4

Solution 1

Solution 2

Solution 5

Add your solution here

Preview 0

Existing Members

...or Join us