Practical Application: Building the Movie Table

Your Mission: The Film Archive

Welcome to the final stage of your SQL journey. You are the lead database developer for a new film archive. Your goal is to design a table that is both storage-efficient and prevents data errors.

We need to store:

• Unique IDs (up to 65,000)
• Movie Titles (up to 50 chars)
• Release Years
• Standardized Ratings (exactly 4 chars)

Welcome to the final lesson of this module! You've mastered the theory, and now it's time to put those skills to the test. As the lead developer for a new film archive, you must build a robust Movies table from scratch that balances performance with precision.

• Design for storage efficiency
• Enforce data integrity through constraints
• Apply the CREATE TABLE syntax in a real-world scenario

The Film Archive Challenge

Welcome to the Final Challenge

You've mastered the theory of SQL data types and table syntax. Now, you are the lead database developer for a new film archive. Your goal is to build a Movies table that is fast, efficient, and reliable.

We have four key pieces of data to store, each with its own constraints. Let's examine our requirements before we start coding.

Welcome to the final lesson of this module! You have learned how to select the right data types and apply constraints. Now, it is time to put those skills to the test by building a robust Movies table from scratch for our film archive. First, the Movie ID. We expect up to 65,000 movies. Since IDs are never negative, we need the most efficient 16-bit integer type. Next, the Title. Most are short, but some are long. We need a limit of 50 characters without wasting space for the short ones. Then, the Release Year. This is a standard 4-digit year. Finally, the Rating. These are standardized codes like PG13 or TVMA, always exactly 4 characters long.

• Real-world data often comes with strict storage limits
• Efficient schemas save server costs and improve performance
• Choosing the right type prevents invalid data entries

Mapping Constraints to Types

Before we code, we must choose the optimal data types. Drag the requirements on the left to the correct SQL data type on the right.

Excellent choice! By using SMALLINT UNSIGNED, we shift the range to zero through sixty-five thousand, five hundred and thirty-five, which perfectly fits our sixty-five thousand movie limit. Let's map our requirements to the perfect SQL types. Think about the storage limits we discussed. Correct. VARCHAR(50) is variable-length, meaning a short title like 'Jaws' only uses 4 characters of storage plus a tiny bit of overhead. Spot on. Since ratings like 'PG13' or 'G ' are always exactly 4 characters, CHAR is more performant because the database doesn't need to track changing lengths.

• SMALLINT UNSIGNED doubles the positive range for IDs
• VARCHAR(50) saves space for variable text
• CHAR(4) is faster for fixed-length codes

Mapping Requirements to Types

Choose Your Weapons

Before we write the code, we must map our requirements to the optimal SQL data types. Match each requirement on the left to the correct type on the right.

Let's map our constraints to types. Drag each requirement to its ideal SQL counterpart. Remember: we want maximum efficiency! Excellent work. You've mapped SMALLINT UNSIGNED for the ID, VARCHAR for titles, and CHAR for the fixed-length ratings. You're ready to build. Perfect choice! That type perfectly balances storage and data integrity. Not quite. Think about whether the data length changes or if it's always the same.

• SMALLINT UNSIGNED shifts the range to 0–65,535
• VARCHAR(50) optimizes space for variable text
• CHAR(4) is performant for fixed-length strings

Code Challenge: Build the Movie Table

The Final Build

It's time. Use the CREATE TABLE syntax to define the movies table. Ensure you use the exact types we just mapped to satisfy the archive's requirements.

This is it. Write the full SQL statement to create the movies table from scratch. Use the types we discussed and pay close attention to your commas and parentheses.

• Use proper CREATE TABLE syntax
• Apply SMALLINT UNSIGNED for movie_id
• Apply VARCHAR(50) for title
• Apply CHAR(4) for rating

The Power of Precision

Why bother with SMALLINT instead of a standard INT? It's all about the scale. A standard INT uses 4 bytes, while SMALLINT uses only 2 bytes.

You might wonder: why not just use a regular INT for everything? Look at the difference. In a table with sixty-five thousand rows, choosing SMALLINT cuts the storage requirement for that column in half!

• SMALLINT uses 2 bytes
• INT uses 4 bytes
• Small choices scale significantly in large databases

Efficiency Check: The Power of SMALLINT

Why Efficiency Matters

You might wonder: Why not just use INT for everything? In a database with millions of rows, choosing SMALLINT over INT makes a massive difference.

Let's look at the numbers. A regular INT uses 4 bytes of storage per row. But our SMALLINT uses only 2 bytes. In a table with 65,000 rows, this small choice cuts the storage requirement for that column in half! This means faster backups, faster queries, and lower costs.

• INT uses 4 bytes per row
• SMALLINT uses only 2 bytes per row
• Choosing the smaller type cuts storage needs in half for that column

Final Challenge: Build the Movie Table

Now it's your turn to write the code. Construct a complete CREATE TABLE statement for the movies table using the requirements we've discussed.

This is the moment of truth. Write the full SQL statement to create the movies table. Remember to include movie_id, title, release_year, and rating with their optimal types. Click submit when you're ready for review.

• Correct CREATE TABLE syntax
• Proper column naming and type selection
• Use of commas and parentheses

Module Complete!

Congratulations! You have successfully designed and built a production-ready database table. You've learned to balance storage efficiency with data integrity.

Outstanding work! You've demonstrated that you can analyze business requirements and translate them into efficient SQL code. You're now ready to start building more complex database schemas.

• Matched data types to real-world constraints
• Optimized storage with SMALLINT and UNSIGNED
• Mastered the CREATE TABLE syntax