Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • Problems in writing sql queries anyone who can write following queries?

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 3
    Answer it

    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 

     

     

    Below are the details of the tables:

    ·         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

                                                  ); """

     

 0 Answer(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: