We need to learn how to execute SQL commands from a file instead of typing them directly into the SQLite console. This is a crucial skill because in real-world database management, we often need to run complex sequences of SQL commands, and storing them in files makes them reusable, shareable, and version-controllable.
Our specific task involves creating a table of pies with their flavors and prices. Each pie has two attributes:
CREATE TABLE pies (
flavor VARCHAR(255) PRIMARY KEY,
price FLOAT
);
Key requirements to understand:
1. The flavor column is marked as PRIMARY KEY, which means each flavor must be unique
2. We need to handle the case where we might run the same file multiple times
3. We need to verify our data was inserted correctly
-- First, prevent duplicate tables by dropping existing table
DROP TABLE IF EXISTS pies;
-- Create the pies table with a primary key constraint
CREATE TABLE pies (
flavor VARCHAR(255) PRIMARY KEY,
price FLOAT
);
-- Insert pie data
INSERT INTO pies VALUES('Apple', 19.95);
INSERT INTO pies VALUES('Caramel Apple Crumble', 20.53);
-- ... additional INSERT statements ...
INSERT INTO pies VALUES('Milk Bar', 46.00);
In the SQLite console, we use the following command:
.read create-table.sql
-- Check all data is inserted correctly
SELECT * FROM pies ORDER BY flavor;
-- View the table schema
.schema
To handle multiple executions, we add the DROP TABLE statement at the beginning:
-- Reset the database state
DROP TABLE IF EXISTS pies;
-- Rest of the CREATE TABLE and INSERT statements...
Think of SQL files like cooking recipes. Just as a recipe contains a sequence of steps to create a dish, a SQL file contains a sequence of commands to set up your database. And just like recipes, SQL files can be:
1. Saved for later use
2. Shared with others
3. Modified and improved over time
4. Version controlled using tools like Git
In our pies table, the flavor column is a PRIMARY KEY. This is like giving each pie a unique identifier. Just as a pie shop wouldn't have two completely identical pies with different prices, our database won't allow duplicate flavors. This is enforced by the PRIMARY KEY constraint.
Adding DROP TABLE IF EXISTS at the start of our file is like clearing your workbench before starting a new project. It ensures you're starting with a clean slate. This pattern is commonly used in:
1. Development environments where you frequently reset your database
2. Testing scenarios where you need a fresh state
3. Database migration scripts where you need to rebuild tables
Database Migrations: When updating an application's database structure, SQL files are used to track and apply changes.
Data Seeding: Initial data for testing or development is often provided through SQL files.
Database Backups: SQL files can store both structure (CREATE statements) and data (INSERT statements) for backup purposes.
Issue 1: Table Already Exists
Error: "table pies already exists"
Solution: Use DROP TABLE IF EXISTS before CREATE TABLE
Issue 2: Unique Constraint Violations
Error: "UNIQUE constraint failed: pies.flavor"
Solution: Ensure all primary key values are unique or drop and recreate the table
1. Always include comments in your SQL files explaining what each section does
2. Use consistent formatting to make the file readable
3. Group related commands together
4. Include verification queries at the end of important sections
5. Consider adding transactions for data consistency
Try these additional challenges:
1. Create a new SQL file that updates all pie prices by 10%
2. Write a file that creates a new table of seasonal pies
3. Create a file that generates a report of pies grouped by price ranges
4. Write a migration file that adds a new column for pie description