SQL for Data Analytics · CREATE, ALTER, DROP
CREATE, ALTER, DROP
In this unit's videos I work in MySQL Workbench, on a database server on my computer. I create a database, use it, and build tables in it. They stay there until I drop them.
The boxes in this unit's lessons work the same way. They run on a MySQL server of your own, in your browser, which starts with no databases. CREATE DATABASE makes one, and USE picks the database your statements run in.
Everything you run stays, even after you reload the page. Every lesson box in this unit runs on that one server, so a database you build on one lesson page is still there on the others. It's kept in this browser on this computer.
The Your databases panel works like the schema list in Workbench. It lists your databases, and the one in use is open to show its tables and their columns. Clicking another database uses it, the same as running USE. Workbench needs a refresh to show a new table. The panel updates by itself after every run. It doesn't show the rows in a table. To see those, run a SELECT.
Run runs everything in the box. If you select part of the script first, the button changes to Run selection and runs only the part you selected. ⌘/Ctrl + / comments out the selected lines, or puts them back. Under the box, each statement gets a line saying what happened to it. A script stops at its first error, the way it does in Workbench.
CREATE, ALTER, DROP15 min
A note on this video
When I run DROP TABLE IF EXISTS on a table that isn't there, Workbench shows a warning. The boxes here don't show warnings. The statement runs and does nothing.
Run queries here while you watch. When I tell you in the video to open Workbench and try something, use this box. It runs on your own MySQL server, the same one as every box in this unit's lessons, and whatever you run stays. This is for your own practice. It isn't recorded or part of your unit submission.
In the video, running the whole script a second time failed with Table 'students' already exists. Where is the students table at that point, and why can't the script make it again?
How I'd answer it
The table is in the college database. It stays there, with whatever is in it, until something drops it. Closing the script or deleting the CREATE TABLE from it doesn't touch the table.
The script is a set of instructions for making the table. Running it again asks the database to make another table named students, and a database can't have two tables with the same name. To change a table that's already there, you ALTER it, or DROP it and run the CREATE TABLE again.
A CREATE TABLE names the table, then lists its columns between parentheses. Each column gets a name and a data type. A comma goes after every column except the last one.
CREATE TABLE students (
ID INT,
FirstName VARCHAR(255),
LastName VARCHAR(255),
Age INT,
Email VARCHAR(255)
);
The data type says what a column is allowed to hold. These are the common ones from the CREATE, ALTER, DROP video:
| Type | Holds | Example value |
|---|---|---|
INT |
whole numbers | 25 |
VARCHAR(n) |
text, up to n characters | 'Jane' |
TEXT |
long text, like a paragraph or an article | |
DECIMAL(p, s) |
numbers with a decimal part | 12.99 |
DATE |
a day | '1996-04-04' |
DATETIME |
a day and a time | '2024-01-02 19:00:00' |
In DECIMAL(10, 2), the 10 is how many digits the number can have in all, and the 2 is how many of them come after the decimal point. That fits prices up to 99,999,999.99.
For VARCHAR, 255 is a common choice when you don't know how long the text will get. When you do know, like the eight characters in an abbreviation such as SDEV 201, you can make it smaller. The database refuses any text longer than the limit.
The college wants to store each student's phone number, written like (253) 555 0142. Which type fits a Phone column?
In the CREATE, ALTER, DROP video I asked you to pause and write the CREATE TABLE for the classes table. If you didn't make it while you watched, write it here.
In your college database, create a table named classes with these columns.
| Column | Holds |
|---|---|
| ID | a whole number |
| ClassName | text |
| Abbreviation | text, up to 8 characters |
| Days | text, up to 5 characters, like Mo/We |
If you already made classes in a box on this page, run SELECT * FROM classes; here and it gets checked.
Once a table exists, running its CREATE TABLE again fails. To change the table, you either ALTER it, or DROP it and create it again with a new CREATE TABLE. Both get you the new column. The difference is what happens to the rows that are already in the table.
Try both on the same three students.
Adding a column to students
This students table has three columns. The college wants a middle initial for each student. Pick a way to add the column.
students
ALTER TABLE changes the table in place:
ALTER TABLE students
ADD MiddleInitial VARCHAR(1);
The new column is empty, NULL, for every row that was already there. ALTER TABLE can do more than add a column. DROP COLUMN takes one out, along with everything stored in it, and RENAME COLUMN gives one a new name.
ALTER TABLE students
RENAME COLUMN MiddleInitial TO Middle;
The college wants to record how many credits each class is worth, as a whole number. Add a column named Credits to classes in college.
A script that builds a database often starts by dropping the tables it's about to create. That way the CREATE TABLE statements never run into a table that's already there.
DROP TABLE IF EXISTS students;
CREATE TABLE students (
ID INT,
FirstName VARCHAR(255),
LastName VARCHAR(255),
Age INT,
Email VARCHAR(255)
);
IF EXISTS is there for the first time the script runs, when there's no students table yet. Without it, the DROP fails, and the script stops before it gets to the CREATE TABLE. With it, the DROP does nothing and the script goes on.
The script above runs twice in a row, on a database that has no students table yet. What does each statement do the first time, and what does each one do the second time?
How I'd answer it
The first time, the DROP finds no students table and does nothing, and the CREATE TABLE makes one.
The second time, the DROP finds the table from the first run and drops it, along with any rows in it. Then the CREATE TABLE makes a new, empty one. However often the script runs, it ends with an empty students table.
That's why a script that starts with DROP TABLE is for building tables from nothing, like setting up a database to practice on. Run against a database that people are using, it throws away their data. To change a table that has data you need, use ALTER TABLE.