Making New Tables in SQL

Table With Columns Instead of Legs?

Structured Query Language or SQL is the language of databases. With SQL we have already covered how to write basic queries in order to find columns and/or rows matching specific conditions generated in our SQL statements. In previous exercises we had tables already made with which to pull our data from so let us take a step back and practice creating our database and tables.

Creating the Database

There are no tables to hold any data without first making the database. The SQL command for this is:

CREATE DATABASE awesomedatabase

This will create a new database called “awesomedatabase”.

WARNING!! Make sure you have admin rights before creating a database. You won’t be able to do anything with your awesome database otherwise.

To check if the database was created you can use the command:

SHOW DATABASE

which will return a list of available databases.

Backing Up Your Database

A good practice when dealing with databases is to back them up. This means saving a copy of the selected database to another location for use as a “back up”. SQL provides a fast way to do this:

BACKUP DATABASE awesomedatabase
TO DISK = 'D:\somewhere\awesomedatabase.bak'

It is important to always save the back up to a different drive than the original.

There is another clause, WITH DIFFERENTIAL that can be used at the end of this statement. A differential backup only makes a copy of files changed since the last full back up. This saves time since you aren’t copying the whole database every time.

Now we have a literal “awesome database” to mess around with. Let’s get to work making some tables.

Creating a Table

Suppose we have a movie database and we want to chronicle each movie in our personal DVD library (yeah thats right we are still rocking dvd’s). There are several key fields each movie would have so lets create a table:

CREATE TABLE movie (
  movieID int,
  title varChar(255),
  mov_year int,
  genre varChar(255),
  runtime varChar(255),
);

We start by naming the table (movie) then declaring each column name along with what datatype it is.

Datatypes

You will notice declarations like “int” and “varChar()” in our CREATE TABLE statement. These are setting up what kind of datatype the column can expect to store. There are many different datatypes in databases and it is important to note what they are.

For a great list of datatypes please check out this link.

Changing a Table

Darn it! Looks like we declared mov_year as “int” but we actually want the datatype “year”. YEAR will be more specific since it accepts the format as YYYY from 1901 to 2155. This fits our needs much better than an int declaration. We can alter a column in a table with:

ALTER TABLE movie 
MODIFY COLUMN mov_year year

Now our datatype for mov_year will be “year” instead of “int”.

Blast it! Looks like we’ve forgotten an important column for any movie: the director. No problem. With the ADD clause we can add a column to any table. Lets add a director column to the movie table:

ALTER TABLE movie
ADD director varChar(255);

Now we have an additional column in our movie table called director.

Oops! I just realized we don’t need to have a genre column in our movie table after all. We can delete the column with:

ALTER TABLE movie 
DROP COLUMN genre;

With just a few easy statements, we have complete control over our table and its columns.

Dropping Shtuff

After setting up the movie table I realize I actually want to quit the whole thing and start a vinyl record database. Lets get rid of the movie table so we can start over:

DROP TABLE movie;

Please be careful when dropping tables. All of the information will be deleted and lost so make sure it is something you absolutely want to do.

As a matter of fact, I don’t think I like this awesome database anymore. I think I’d like to scratch the ENTIRE thing and start over. If I was so inclined to delete the entire database I could do so with:

DROP DATABASE awesomedatabase;

AGAIN, please be very careful before you go deleting your database. I shouldn’t have to say it but I will anyways: Do not go deleting an entire database without being absolutely sure it is what you want and need to do!

Get CREATE ing

As you get to practicing how to create databases and tables you’ll notice how common sense it is. SQL is a great language to know precisely because it is intuitive in its structure and commands. In our next blog we will be looking at constraints and ways reference tables in other tables. As always Happy Coding!