SQLite Forum

Problems related to writing following queries can someone write all these queries?
Login

Problems related to writing following queries can someone write all these queries?

(1) By anonymous on 2020-06-04 11:46:55 [link] [source]

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
3. Find all actors who acted only in films before 1960 using SELECT 
        statement 
4. Find the films with more women actors than men 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
                                              ); """

(2) By Larry Brasfield (LarryBrasfield) on 2020-06-04 13:23:27 in reply to 1 [link] [source]

You will have better luck getting some help here with your own, genuine attempt to do your own homework. I, along with others, do not see a productive use of time in doing homework for somebody who cannot be bothered to make a reasonable effort on their own. So, my advice is: Study the materials you were assigned for this part of the course; write some queries that have some chance of working according to your understanding; if they work, you are done; otherwise, show your not-quite-right queries and ask specific questions about why the results you get differ from the results you wanted.

If it does not become obvious during your assigned study, do some research on the meaning and uses of the JOIN keyword in SQL.

(3) By Keith Medcalf (kmedcalf) on 2020-06-04 14:13:27 in reply to 1 [source]

To answer the headline, yes.