Data Science Hub
  • Data Science Hub
  • STATISTICS
    • Introduction
    • Fundamentals
      • Data Types
      • Central Tendency, Asymmetry, and Variability
      • Sampling
      • Confidence Interval
      • Hypothesis Testing
    • Distributions
      • Exponential Distribution
    • A/B Testing
      • Sample Size Calculation
      • Multiple Testing
  • Database
    • Database Fundamentals
    • Database Management Systems
    • Data Warehouse vs Data Lake
  • SQL
    • SQL Basics
      • Creating and Modifying Tables/Views
      • Data Types
      • Joins
    • SQL Rules
    • SQL Aggregate Functions
    • SQL Window Functions
    • SQL Data Manipulation
      • String Operations
      • Date/Time Operations
    • SQL Descriptive Stats
    • SQL Tips
    • SQL Performance Tuning
    • SQL Customization
    • SQL Practice
      • Designing Databases
        • Spotify Database Design
      • Most Commonly Asked
      • Mixed Queries
      • Popular Websites For SQL Practice
        • SQLZoo
          • World - BBC Tables
            • SUM and COUNT Tutorial
            • SELECT within SELECT Tutorial
            • SELECT from WORLD Tutorial
            • Select Quiz
            • BBC QUIZ
            • Nested SELECT Quiz
            • SUM and COUNT Quiz
          • Nobel Table
            • SELECT from Nobel Tutorial
            • Nobel Quiz
          • Soccer / Football Tables
            • JOIN Tutorial
            • JOIN Quiz
          • Movie / Actor / Casting Tables
            • More JOIN Operations Tutorial
            • JOIN Quiz 2
          • Teacher - Dept Tables
            • Using Null Quiz
          • Edinburgh Buses Table
            • Self join Quiz
        • HackerRank
          • SQL (Basic)
            • Select All
            • Select By ID
            • Japanese Cities' Attributes
            • Revising the Select Query I
            • Revising the Select Query II
            • Revising Aggregations - The Count Function
            • Revising Aggregations - The Sum Function
            • Revising Aggregations - Averages
            • Average Population
            • Japan Population
            • Population Density Difference
            • Population Census
            • African Cities
            • Average Population of Each Continent
            • Weather Observation Station 1
            • Weather Observation Station 2
            • Weather Observation Station 3
            • Weather Observation Station 4
            • Weather Observation Station 6
            • Weather Observation Station 7
            • Weather Observation Station 8
            • Weather Observation Station 9
            • Weather Observation Station 10
            • Weather Observation Station 11
            • Weather Observation Station 12
            • Weather Observation Station 13
            • Weather Observation Station 14
            • Weather Observation Station 15
            • Weather Observation Station 16
            • Weather Observation Station 17
            • Weather Observation Station 18
            • Weather Observation Station 19
            • Higher Than 75 Marks
            • Employee Names
            • Employee Salaries
            • The Blunder
            • Top Earners
            • Type of Triangle
            • The PADS
          • SQL (Intermediate)
            • Weather Observation Station 5
            • Weather Observation Station 20
            • New Companies
            • The Report
            • Top Competitors
            • Ollivander's Inventory
            • Challenges
            • Contest Leaderboard
            • SQL Project Planning
            • Placements
            • Symmetric Pairs
            • Binary Tree Nodes
            • Interviews
            • Occupations
          • SQL (Advanced)
            • Draw The Triangle 1
            • Draw The Triangle 2
            • Print Prime Numbers
            • 15 Days of Learning SQL
          • TABLES
            • City - Country
            • Station
            • Hackers - Submissions
            • Students
            • Employee - Employees
            • Occupations
            • Triangles
        • StrataScratch
          • Netflix
            • Oscar Nominees Table
            • Nominee Filmography Table
            • Nominee Information Table
          • Audible
            • Easy - Audible
          • Spotify
            • Worldwide Daily Song Ranking Table
            • Billboard Top 100 Year End Table
            • Daily Rankings 2017 US
          • Google
            • Easy - Google
            • Medium - Google
            • Hard - Google
        • LeetCode
          • Easy
  • Python
    • Basics
      • Variables and DataTypes
        • Lists
        • Dictionaries
      • Control Flow
      • Functions
    • Object Oriented Programming
      • Restaurant Modeler
    • Pythonic Resources
    • Projects
  • Machine Learning
    • Fundamentals
      • Supervised Learning
        • Classification Algorithms
          • k-Nearest Neighbors
            • kNN Parameters & Attributes
          • Logistic Regression
        • Classification Report
      • UnSupervised Learning
        • Clustering
          • Evaluation
      • Preprocessing
        • Scalers: Standard vs MinMax
        • Feature Selection vs Dimensionality Reduction
        • Encoding
    • Frameworks
    • Machine Learning in Advertising
    • Natural Language Processing
      • Stopwords
      • Name Entity Recognition (NER)
      • Sentiment Analysis
        • Agoda Reviews - Part I - Scraping Reviews, Detecting Languages, and Preprocessing
        • Agoda Reviews - Part II - Sentiment Analysis and WordClouds
    • Recommendation Systems
      • Spotify Recommender System - Artists
  • Geospatial Analysis
    • Geospatial Analysis Basics
    • GSA at Work
      • Web Scraping and Mapping
  • GIT
    • GIT Essentials
    • Connecting to GitHub
  • FAQ
    • Statistics
  • Cloud Computing
    • Introduction to Cloud Computing
    • Google Cloud Platform
  • Docker
    • What is Docker?
Powered by GitBook
On this page
  • 1962 movies
  • When was Citizen Kane released?
  • Star Trek movies
  • id for actor Glenn Close
  • id for Casablanca
  • Cast list for Casablanca
  • Alien cast list
  • Harrison Ford movies
  • Harrison Ford as a supporting actor
  • Lead actors in 1962 movies
  • Busy years for Rock Hudson
  • Lead actor in Julie Andrews movies
  • Actors with 15 leading roles
  • released in the year 1978
  • with 'Art Garfunkel'

Was this helpful?

  1. SQL
  2. SQL Practice
  3. Popular Websites For SQL Practice
  4. SQLZoo
  5. Movie / Actor / Casting Tables

More JOIN Operations Tutorial

Last updated 1 year ago

Was this helpful?

1962 movies

List the films where the yr is 1962 [Show id, title]

SELECT id, title 
FROM movie 
WHERE yr=1962

When was Citizen Kane released?

Give year of 'Citizen Kane'.

SELECT yr 
FROM movie 
WHERE title = 'Citizen Kane'

Star Trek movies

List all of the Star Trek movies, include the id, title and yr (all of these movies include the words Star Trek in the title). Order results by year.

SELECT id, title, yr 
FROM movie 
WHERE LOWER(title) LIKE '%star trek%'
ORDER BY yr

id for actor Glenn Close

What id number does the actor 'Glenn Close' have?

SELECT id
FROM actor 
WHERE name = 'Glenn Close'

id for Casablanca

What is the id of the film 'Casablanca' ?

SELECT id
FROM movie 
WHERE title = 'Casablanca'

Cast list for Casablanca

Obtain the cast list for 'Casablanca'. what is a cast list? (The cast list is the names of the actors who were in the movie.) (Use movieid value you got from the previous question)

SELECT a.name
FROM actor a
JOIN casting c
ON a.id = c.actorid
WHERE c.movieid = 27 

Alien cast list

Obtain the cast list for the film 'Alien'

SELECT a.name
FROM actor a
JOIN casting c
ON a.id = c.actorid
WHERE c.movieid = (SELECT id FROM movie WHERE title = 'Alien')

Harrison Ford movies

List the films in which 'Harrison Ford' has appeared

SELECT m.title
FROM movie m
JOIN casting c
ON m.id = c.movieid
WHERE c.actorid = (SELECT id FROM actor WHERE name = 'Harrison Ford')

Harrison Ford as a supporting actor

List the films where 'Harrison Ford' has appeared - but not in the starring role. [Note: the ord field of casting gives the position of the actor. If ord=1 then this actor is in the starring role]

SELECT m.title
FROM movie m
JOIN casting c
ON m.id = c.movieid
WHERE c.actorid = (SELECT id FROM actor WHERE name = 'Harrison Ford') 
AND c.ord != 1

Lead actors in 1962 movies

List the films together with the leading star for all 1962 films.

SELECT m.title, a.name
FROM movie m
JOIN casting c
ON m.id = c.movieid
JOIN actor a
ON c.actorid = a.id
WHERE m.yr = 1962 AND c.ord = 1

Busy years for Rock Hudson

Which were the busiest years for 'Rock Hudson', show the year and the number of movies he made each year for any year in which he made more than 2 movies.

SELECT m.yr, COUNT(m.id) as movie_cnt
FROM movie m
JOIN casting c
ON m.id = c.movieid
JOIN actor a
ON c.actorid = a.id
WHERE a.name = 'Rock Hudson'
GROUP BY m.yr
HAVING COUNT(m.id) > 2

Lead actor in Julie Andrews movies

List the film title and the leading actor for all of the films 'Julie Andrews' played in. Did you get "Little Miss Marker twice"?

SELECT m.title, a.name
FROM movie m
JOIN casting c ON m.id = c.movieid
JOIN actor a ON a.id = c.actorid 
WHERE c.movieid IN (SELECT movieid 
                FROM casting 
                WHERE actorid = (SELECT id 
                                FROM actor 
                                WHERE name = 'Julie Andrews'))
AND c.ord = 1;

Actors with 15 leading roles

Obtain a list, in alphabetical order, of actors who've had at least 15 starring roles.

SELECT a.name
FROM actor a 
JOIN casting c 
ON a.id = c.actorid 
WHERE c.ord = 1
GROUP BY a.name
HAVING COUNT(c.movieid) >= 15
ORDER BY a.name

released in the year 1978

List the films released in the year 1978 ordered by the number of actors in the cast, then by title.

SELECT m.title, COUNT(c.actorid) AS num_actors
FROM movie m
JOIN casting c ON m.id = c.movieid
JOIN actor a ON c.actorid = a.id
WHERE m.yr = 1978
GROUP BY m.title
ORDER BY COUNT(c.actorid) DESC , m.title

with 'Art Garfunkel'

List all the people who have worked with 'Art Garfunkel'.

SELECT a.name
FROM movie m
JOIN casting c ON m.id = c.movieid
JOIN actor a ON c.actorid = a.id
WHERE m.id IN (SELECT DISTINCT movieid 
                FROM casting 
                WHERE actorid = (SELECT id 
                                FROM actor 
                                WHERE name = 'Art Garfunkel'))
AND a.name != 'Art Garfunkel'
Link