In-Class Assessment
Basic SQL Queries: SELECT, WHERE, ORDER BY, LIMIT
In-Class Assessment: Basic SQL Queries
Assessment Overview
This timed assessment tests your understanding of fundamental SQL operations. You'll work with a real IMDB movies database to demonstrate your skills in data retrieval, filtering, sorting, and limiting results.
Database Schema: IMDB Movies
You'll be working with the movies table from the IMDB Top 1000 dataset. This table contains comprehensive information about popular movies including ratings, cast, and production details.
| Column Name | Data Type | Description | Example Values |
|---|---|---|---|
| id | INTEGER | Primary key, auto-increment | 1, 2, 3, 4 |
| poster | TEXT | URL link to movie poster image | 'https://m.media-amazon.com/...' |
| title | TEXT | Movie title/name | 'The Shawshank Redemption', 'The Godfather' |
| year | INTEGER | Year the movie was released | 1994, 2008, 1972 |
| rating_cert | TEXT | Movie rating certificate | 'A', 'UA', 'PG-13', 'R' |
| runtime | INTEGER | Movie duration in minutes | 142, 175, 152 |
| genre | TEXT | Movie genre(s), comma-separated | 'Drama', 'Action, Crime, Drama' |
| imdb_score | REAL | IMDB rating (0.0 to 10.0) | 9.3, 9.2, 9.0 |
| overview | TEXT | Movie plot summary/description | 'Two imprisoned men bond over...' |
| meta_score | INTEGER | Metacritic score (0-100) | 80, 100, 84 |
| director | TEXT | Director's full name | 'Frank Darabont', 'Christopher Nolan' |
| star1 | TEXT | First lead actor/actress | 'Tim Robbins', 'Marlon Brando' |
| star2 | TEXT | Second lead actor/actress | 'Morgan Freeman', 'Al Pacino' |
| star3 | TEXT | Third lead actor/actress | 'Bob Gunton', 'James Caan' |
| star4 | TEXT | Fourth lead actor/actress | 'William Sadler', 'Diane Keaton' |
| votes | INTEGER | Number of IMDB user votes | 2343110, 1620367 |
| gross | INTEGER | Box office gross earnings (USD) | 28341469, 134966411 |
Quick Reference: SQL Operators
Comparison Operators:
=Equal to!=or<>Not equal>Greater than<Less than>=Greater than or equal<=Less than or equal
Pattern Matching & Logic:
LIKE '%pattern%'Contains textBETWEEN x AND yRange (inclusive)ANDBoth conditions trueOREither condition trueORDER BY col ASC/DESCSort resultsLIMIT nRestrict row count
Assessment Instructions
Complete all 12 questions below using the movies table. Each question tests specific SQL skills and is worth different point values. You'll receive immediate feedback and can see your progress in real-time.
Table: movies
Execute a query to see results
In-Class Assessment: Basic SQL Queries — Questions
- Q18ptsSelect the title and imdb_score of all movies
- Q210ptsFind all movies where the imdb_score is greater than 8.5
- Q310ptsSelect movies released in the year 2019
- Q412ptsFind the top 10 highest-rated movies, ordered by imdb_score in descending order
- Q512ptsSelect only the title and director of movies with runtime greater than 150 minutes
- Q610ptsFind all movies directed by 'Christopher Nolan'
- Q712ptsGet the 5 most recent movies, ordered by year in descending order
- Q815ptsFind movies with imdb_score between 7.0 and 8.0 (inclusive)
- Q915ptsSelect the title, genre, and imdb_score of Action movies, ordered by score from highest to lowest
- Q1012ptsFind the bottom 3 lowest-rated movies (lowest imdb_score first)
- Q1115ptsSelect movies where the title contains the word 'The' (case-sensitive)
- Q1218ptsGet the title and year of movies released before 2000, ordered by year from oldest to newest, limit to 8 results
Click any question above to navigate. Complete all questions to finish the assessment.