Practice & Assessment
Test your understanding of Data Types and Constraints
Multiple Choice Questions
5Which data type should you use to store a monetary amount like 19.99?
A query SELECT * FROM users WHERE email = NULL returns zero rows even though some rows have NULL email. Why?
When should you use ON DELETE CASCADE on a foreign key?
Which column is the worst candidate for a standalone index?
What is the correct way to ensure no two rows in the users table have the same email address?
Coding Challenges
1Design the Schema for an E-Commerce Product Catalog
Write CREATE TABLE statements for a product catalog with: products (id, name, sku, price, stock, status, created_at), categories (id, name, slug), and a product_categories junction. Requirements: price must be positive; sku must be unique; status must be one of 'active', 'inactive', 'discontinued'; stock defaults to 0 and cannot be negative; category slug must be unique and lowercase enforced by a CHECK. Include all PKs, FKs, constraints, and appropriate data types. Time estimate: 25 minutes.
Mini Project
Constrained Schema for a Hotel Booking System
Design and write full DDL for a hotel booking system covering: hotels, room_types, rooms, guests, bookings, and payments. Every table must have correct data types for all columns, NOT NULL where appropriate, UNIQUE constraints on natural keys, CHECK constraints on price/amount/status columns, appropriate FK ON DELETE behavior for each relationship, indexes on all FK columns, and a TIMESTAMP created_at on every table. Document each constraint choice with an inline SQL comment.