SQL: The Basics

Creating Tables

To define your database schema, you will need to create tables. Tables house information within your database in a tabular format, rather like you would see in an Excel spreadsheet.

To create a table, use this command like this in your SQL editor:


    CREATE TABLE ghosts (
      ghost_id INT PRIMARY KEY,
      name_given VARCHAR(45),
      location_seen VARCHAR(25) NOT NULL        
    );

Now, there are a few things to break down in this example. Lets have a look at these:

You create a new table by using the CREATE TABLE command, followed by the name of the table. So the name can be anything you like:


    CREATE TABLE ghouls (     
    );

Remember that you must end every statement with a " ; " symbol!

Next you need to define the columns. In the main example above, my defined columns for the ghosts table is ghost_id, name_given and location_seen. But what do these words and brackets after the column names mean? These are constraints, and they are used to allocate space, and the data type used in each column.

We will cover the different data types in a different section, but for now let us look only at the ones used here:

INT: An integer (or whole number)
VARCHAR: A string data type that will accept both numbers and characters
The bracketed number after VARCHAR: This is the limit you set as to how many characters (letters or numbers) that can be contained in this field
PRIMARY KEY
: This allocates the column as the PRIMARY KEY. You can only have one per table. This is used to add a unique serial number to each data row. More on why you need this later
NOT NULL: Used as a data validation measure to not let a new row be created without this being blank

Once you execute this in your SQL interpreter (I am using SQL Server, so your equivolent of the describe command may be different depending on your program), you may want to check that this has been successful. To do this simply type:

sp_help ghosts;

You should then get a table that looks like this:

This will show that your table has been created successfully!

Deleting Tables

To delete a table, say you created one in error, you use the DROP TABLE command


DROP TABLE ghouls;

Modify a Table

Using the ALTER and ADD commands, you can add a new column to your table:


ALTER TABLE ghosts ADD price_of_visit DECIMAL(5, 2);

lurkingfear.neocities.org SQL Tutorial

SQL: The Basics

Creating Tables

Deleting Tables

Modify a Table