Navigation
Introduces the SQL commands used to define and modify the structure of a database.
Imagine you are building the next viral social media app. How do you tell a computer exactly what a 'user' looks like—their name, birthday, and profile picture—so it never forgets and never makes a mistake?
In the world of databases, Data Definition Language (DDL) is the set of commands used to define the 'schema' or structure. The CREATE TABLE statement is your primary tool. When defining a table, you must specify the Data Type for every column. Common types include `INT` for whole numbers, `VARCHAR(n)` for strings up to characters, and `DECIMAL(p, s)` for precise numbers where is the total digits and is the digits after the decimal. To ensure data integrity, we use Constraints like `PRIMARY KEY` (unique identifier) and `NOT NULL` (prevents empty values).
Let's build a simple table to track students. 1. Define the table name: `Students`. 2. Add an ID: `StudentID INT PRIMARY KEY`. 3. Add a name: `FullName VARCHAR(100) NOT NULL`. 4. Add a GPA: `GPA DECIMAL(3, 2)`.
Full Code: ```sql CREATE TABLE Students ( StudentID INT PRIMARY KEY, FullName VARCHAR(100) NOT NULL, GPA DECIMAL(3, 2) ); ```
Quick Check
If you want to store a price like $19.99, which data type is most appropriate: INT or DECIMAL(4, 2)?
Answer
DECIMAL(4, 2) is correct because INT only stores whole numbers, while DECIMAL(4, 2) allows for 4 total digits with 2 after the decimal point.
Requirements change. Your app might start simple, but eventually, you'll need to add new features. The ALTER TABLE command allows you to modify an existing structure without deleting your data. You can use the `ADD` keyword to insert a new column, `DROP COLUMN` to remove one, or `RENAME` to change identifiers. This is far more efficient than recreating a table, especially when the table contains millions of rows of existing information.
Suppose we need to add an email column to our `Students` table and remove the GPA column. 1. Use `ALTER TABLE Students`. 2. Add the column: `ADD Email VARCHAR(255)`. 3. Remove the column: `DROP COLUMN GPA`.
```sql ALTER TABLE Students ADD Email VARCHAR(255);
ALTER TABLE Students DROP COLUMN GPA; ```
Quick Check
True or False: Using ALTER TABLE to add a column will delete all the existing rows in your table.
Answer
False. ALTER TABLE modifies the structure while preserving the existing data.
When it's time to clean up, you have two main options, but they behave very differently. TRUNCATE TABLE is like emptying a filing cabinet: it removes all the records (rows) but leaves the cabinet (the table structure) standing. DROP TABLE is like throwing the entire filing cabinet into a furnace: it deletes all the data and the table definition itself. Both commands are irreversible in standard SQL, so they must be used with extreme caution.
Imagine you are migrating a legacy system. You have a temporary table called `Old_Logs` containing rows. 1. To clear the logs for a fresh start but keep the table for tomorrow's data: `TRUNCATE TABLE Old_Logs;`. 2. To completely remove the log system because it is being replaced by a new service: `DROP TABLE Old_Logs;`. 3. Note: If you DROP a table, any future `INSERT` commands will fail until you `CREATE` it again.
Which SQL command would you use to add a 'PhoneNumber' column to an existing 'Users' table?
What is the primary difference between DROP and TRUNCATE?
A PRIMARY KEY constraint allows multiple rows to have the same value in that column.
Review Tomorrow
In 24 hours, try to recall the three main DDL keywords and explain the difference between 'dropping' a table and 'truncating' it.
Practice Activity
Try this on your own: Write the SQL code to create a 'Products' table with an ID, a Name, and a Price, then write a command to add a 'StockQuantity' column.