SQL: Table Creation and Basic Queries

SQL (pronounced Sequel, or sometimes just S-Q-L) stands for Structured Query Language. SQL is a language for interacting with databases, which it does by ‘querying’ a database and asking for certain features or columns, often with various conditions to narrow down the results. SQL can be used with different languages, like Python, Java and others, but it always uses the same basic syntax for its queries, so once you learn how to write SQL queries they will work with whatever language you’re working in. There are actually several versions of the SQL language, but they all support the major commands that we’ll be discussing here.

Queries are written in a rather straight-forward manner, with the form ‘select - from - where’. As we go on that will make more sense, but for now we’re going to start with database and table creation, since we’ll need to have a table before we can query it. Like a normal query, creating a database is also rather straight-forward. For example, we’ll create a database for a store:

CREATE DATABASE storeDB;

Very simple. If you make a mistake and need to drop your table for any reason, you do just that:

DROP DATABASE storeDB;

Now that we have a database, we still need at least one table, so we’ll create a table to store customers. This time, however, we need to specify the features or columns the table is going to hold, with a name and data type for each column:

CREATE TABLE Customers(
    CustID int,
    LastName String,
    FirstName String,
    Phone String);

You’ll notice that we’re only using primitive types(and strings), since most databases can only handle simple types. As before if you need to remove the table completely the syntax is very similar to dropping a database:

DROP TABLE Customers;

Now that we have a table, we need to be able to modify it. We decide that we no longer need to store customer phone numbers, so we can remove that column. The way to do this is by starting with ALTER TABLE followed by the table name, and then DROP COLUMN and the column name:

ALTER TABLE Customers
DROP COLUMN Phone;

But we also want to store customers e-mail addresses, so we need to add that in a similar fashion:

ALTER TABLE Customers
ADD COLUMN Email;

There is also a way to use ALTER TABLE to alter the datatype of a column, but this varies depending on which version of SQL you are using. Okay, we have a database with a table, let’s start filling it with information. To add a row to the table we need to use the INSERT INTO statement. A simple INSERT INTO statement uses the name of the table you want to add to, followed by VALUES, and then the values you want to add. When writing the statement this way it is important to remember to write the values in the order the columns appear in the table:

INSERT INTO Customers
VALUES (1, Smith, John, [e-mail]);

We can get more complicated and exact if we want however. What if our next Customer does not have an email specified? In that case we need to specify which columns the values should be placed in. We do that by following the table name with the column names we want to the values to go in, in the same order that we write the values:

INSERT INTO Customers (custID, FirstName, LastName)
VALUES (2, Jones, Ben)

Now that our table has data, we can start talking about retrieving it. The basic form of this is the SELECT statement, which can have many modifiers but starts out simple. This type of statement is written SELECT and then the name or names of the columns you’re interested in, and then FROM, followed by the name of the table you’re selecting the data from. If we want to select all fields we just use * instead of specifying any column names:

SELECT * FROM Customers;

There, we have now created a database, a table, filled the table, and then retrieved data from the table. Next time we’ll talk about more complex and advanced SELECT statements.