Introduction to SQL
Published on the 30th of January 2005
Skip to the Table of ContentsNow that you know the theory behind the relational model, we can go into what SQL actually is. This lesson will teach you how to query the database.
To communicate with a database, no matter if it is relational or not, you will need a language. SQL is that language. SQL means Structured Query Language. Many database management systems use SQL. Often those programs also offer features which are not available in SQL, or which are difficult in SQL. Something which is not available in SQL is the ability to create forms, which are available in MS Access, or you can create your own if you know how to use MySQL and PHP. In some cases, like MS Access, a little bit altered form of SQL is implemented to provide for the extra functions. However, if you ever plan to switch from one DBMS to another, it would be unwise to depend much on those proprietary functions.
Getting Started with Queries
Let us start with one of the easiest queries possible. We want a list of all available voting topics.
SQL
SELECT vote_title FROM vote_questions;
We see the result of this query. After SELECT we have the columns which will be shown. If you want to select multiple columns, they need to be separated by commas. After FROM we put the name of the table (or tables) from which these columns should be selected. A SQL query is always being closed by the semi-colon (;). A SQL query is usually simply referred to as query.
If we would want to have a list of all the information, we could of course list all the available columns, like this.
SQL
SELECT vote_id, vote_title, vote_desc, vote_close_date, vote_votes FROM vote_questions;
However, that can be written much shorter, like this.
SQL
SELECT * FROM vote_questions;
The asterix is a so-called wildcard which will output every available column. If you do not need all columns, it is better for performance reasons to only select those you need.
Making It More Selective
You have now seen how you can select a whole table or a couple of columns from a table. But it is realistic to presume that you do not want all information from a column to be selected. You do this by using the WHERE clause. The WHERE clause offers a lot of possibilities, but those will be explained in the next tutorial. For now, I will stick to the very basics of the where clause.
It is possible that you want a list of every voting topic on which I voted, and for what I voted. You do this by using the WHERE clause like this.
SQL
SELECT * FROM vote_voters WHERE vote_user_id = 5;
After WHERE you say that only if the user id from the one who voted is 5, it should be selected. My user id is 5.
SQL knows several different data types, like text, numeric and some other things, a bit depending on the implementation. Both MySQL and Access have a date type, but the format is different. To create a table you need to specify the correct data type for every column. For text you need to put the string between apostrophes (WHERE username = `Frenzie`), for numerical data you don't have to do this.
The basic structure of a SQL query looks like this.
SQL
SELECT one_or_more_columns FROM one_or_more_tables WHERE one_or_more_clauses
The query does not influence the order in which the rows are displayed. You can order the result of your query by using ORDER BY. To sort the results of the previous query of voting topics I voted for by the actual voting topics you could use this.
SQL
SELECT * FROM vote_voters WHERE vote_user_id = 5 ORDER BY vote_id;
If you want to order is by the option I voted for you can specify it further with commas, just like with the other components of a query.
SQL
SELECT * FROM vote_voters WHERE vote_user_id = 5 ORDER BY vote_id, vote_option_id;
The default way of ordering is to order ascending. Of course you will also want to order descending now and then. This can be easily achieved by putting DESC behind the thing you wish to be ordered descending. In this example, the vote_option_id will be sorted ascending, where the vote_id will be ordered descending.
SQL
SELECT * FROM vote_voters WHERE vote_user_id = 5 ORDER BY vote_id DESC, vote_option_id;
Now let's presume we have another voting table, used for backup purposes. We want to select those cases where the information is still the same. Therefore, we can use DISTINCT.
SQL
SELECT vote_id, vote_backup_id FROM vote_voters, vote_backup_voters;
This query would return a complete list of every entry. Now, we use DISTINCT.
SQL
SELECT DISTINCT vote_id, vote_backup_id FROM vote_voters, vote_backup_voters;
Now only the cases where those two are similar will be selected.
