Skip to content

Understanding SQL

 

NOTE: Although very powerful, SQL can damage a database if used incorrectly, so please feel free to practice these SQL scripts but ON THE DEMO DATABASE.

You probably noticed that many databases management systems include the acronym SQL (often pronounced “sequel”). This is a term which arises very often when discussing databases. It stands for Structured Query Language and is a language that all relational databases understand. A “database query” is something that a user asks the database, and SQL is the language that query is written in.

This is how you would create a “clients” table in a database:

CREATE TABLE MYCLIENTS ( IDNUMBER INT PRIMARY KEY, NAME TEXT, CITY TEXT, TELEPHONE TEXT, BUDGET INT);

This creates a table called MYCLIENTS with five columns. The IDNUMBER column is the table’s PRIMARY KEY, which means that it is used to uniquely identify the row and thus has to be unique. In other words, this database will not let you assign two clients the same ID number which makes sense if you think about it. Two different people can't have the same ID number in real life. INT means that the IDNMUBER is an integer (a number without a decimal point) and TEXT means that the name column contains text (you’d probably use something else in a real situation as TEXT is relatively inefficient). SQL keywords have been written in bold. You can't use these words to name things in a database since the SQL interpreter (the program that talks to the database) uses them to issue commands.

Note the semi-colon (;) at the end of the SQL statement. This is like a full stop at the end of an English sentence. It lets the interpreter know that we're finished with the statement. The formatting of the text in the statement isn't important, and can be written in any fashion that seems most clear to you. For example, the same statement could be written like this:

CREATE TABLE MYCLIENTS 

( IDNUMBER INT PRIMARY KEY, NAME TEXT, CITY TEXT, TELEPHONE TEXT, BUDGET INT);

Or even:

CREATE TABLE MYCLIENTS 

( IDNUMBER INT PRIMARY KEY, 

NAME TEXT, 

CITY TEXT, 

TELEPHONE TEXT,

BUDGET INT );

Just remember that you may not be the only person reading the statement, so the neater you keep it, the less the confusion in the long run.

You can then use an SQL command like this to insert data into your new table:

INSERT INTO MYCLIENTS VALUES (100, 'Smiths Records', 'Cape Town', '020 8888 2222', 50000);

This adds a client with a client ID number of 100, some contact details and a budget. Notice that we always use single quotes to indicate a text (also called a string in database lingo). Once you have added some data to your table using similar statements, you can run searches using SQL commands like this:

SELECT IDNUMBER, NAME FROM MYCLIENTS WHERE CITY='Cape Town';

This will retrieve all clients' ID numbers and names located in Cape Town. If you want to get all the fields instead of just the ID number and name you can use an asterix (*) instead of typing all the field names like so:

SELECT * FROM MYCLIENTS WHERE CITY='Cape Town';

Adapted from Introdution to Databases

Further resources:

sqlcourse.com

www.sql-tutorial.net

www.w3schools.com

wikipedia.org