Back

Structured Query Language (SQL) - getting started

Introduction
SQL, or Structured Query Language, is a language that is used for gaining access to and manipulating tables of data. It is a ‘standard’ language in that it has been defined by the American National Standards Institute (ANSI) and so will work with many different applications such as Access, Informix and Oracle, for example. Although there are a number of different SQL versions in existence, they all support the basic ANSI standard and so all can carry out certain functions in the same way, such as SELECT and DELETE. We will see examples of these later. 

How to get experience of SQL
There are some online simulators, where you can enter commands in SQL and then see the results. You could try the one here: http://www.w3schools.com/sql/ There are some very good notes on this website as well as others (use Google), and of course some excellent tutorials on YouTube.

A better way to explore would be to open up Access and use that. You need to open up the Queries section and switch into SQL. You should write and save one SQL job, and then run it. for example, write an SQL query to create a table, save it and then run it. When you run any other queries after creating a table, make sure any tables you are using are closed first, or you may get errors. Using SQL in Access does depend very much on what version of Access you are using, but your teacher will help you get started. Once you have written the first view SQL commands and run them, you will be able to experiment with lots of different instructions. The hardest part is just getting going!

A dog club’s database
Here is a design for a dog club. 

We must describe any one E-R relationship with two sentences. In this case, we have:

    • Each owner can own many dogs.
    • Each dog can be owned by only one owner.

Here are the two tables with some record in:

Using Access to do the examples
You must get some actual experience using SQL to really understand it. Use Access or any other relational database package. To try out the following examples in Access 2000, do the following.

    • In the database window (The database window is the screen you get when you first open your database, where you can see a window with a list on the left that says tables, queries, forms reports, pages etc.) click on Queries, New, OK and then don’t ADD any tables. Just CLOSE the pop-up box.
    • Now click the design view icon. It is probably situated under the FILE menu.
    • An SQL box should pop-up and you are ready to type in your queries. If you get stuck getting started, you will need to ask your peers or your teacher for help or refer to the HELP menu in Access or use the Internet.

IMPORTANT: When you do each query, you must be sure that you get the correct results. Just because you get some results does not mean they are correct. Whilst you are learning how to construct queries, you should always:

    • Predict the results of running a query before you actually run it.
    • Run the query.
    • Compare your prediction to the actual results obtained.

Creating a database
You must create a database in SQL before you can start adding tables and then data to it. The syntax for creating a database is:

CREATE DATABASE database_name

To create a database for the Dog Club, we would use:

CREATE DATABASE dog_club

Creating a table
Once the database has been created, you can add tables to it. You can create a table in SQL using the CREATE TABLE command. The syntax for this command is as follows:

CREATE TABLE tableName
(
column1_name data_type(size),
column2_name data_type(size),
column3_name data_type(size)
);

To create the Dogs table, for example, we would type in the following:

CREATE TABLE tblOwners
(
ID int(1),
Title varchar(5),
Surname varchar(20),
Phone_no varchar(20),
Registration date
);

ID will hold an integer, Title, Surname and Phone_no will hold text and Registration will hold a date. We now have an empty table for the owners of dogs that looks like this:

ID Title Surname Phone_no Registration
         

Every table must have a primary key. This is the one field that is unique for each row in the table, for each record. It is typically an ID number or similar. We can add the primary key when we create a table, like this:

CREATE TABLE tblOwners
(
ID int(1),
Title varchar(5),
Surname varchar(20),
Phone_no varchar(20),
Registration date,
PRIMARY KEY (ID)
);

We can also add a primary key after a table has been created, by designing an SQL query and running it with these commands:

ALTER TABLE tblOwners
ADD PRIMARY KEY (ID)

To add the other table in our database, we would use:

CREATE TABLE tblOwners
(
ID int(1),
Name varchar(20),
DofB date,
Type varchar(20),
Wins varchar(20),
Owner_ID int,
PRIMARY KEY (ID)
);

ALTER TABLE
The ALTER TABLE command is useful for modifying the table. For example, if you wanted to delete the Surname field in the Owners' table, you would use:

ALTER TABLE tblOwners
DROP COLUMN Surname

If you wanted to add a new column called Surname in the Owners' table (that didn't exist before, perhaps because you just deleted it), you would use:

ALTER TABLE tblOwners
ADD Surname varchar(20)

Back