SQL Tutorial – Basics
Pre-Tutorial Requirements
You have knowledge of basic database theory.
Introduction
SQL (Structured Query Language) is the ANSI standard language for accessing databases. It is a powerful, yet relatively easy to learn, language that allows queries to executed against a database causing the data contained to be retrieve, updated, deleted, inserted plus lots more.
The basis of SQL was invented by a gentleman named Dr. Edgar Frank Codd, an IBM researcher, in the 1970’s. Its premise was to allow querying of relational database tables using English like commands. Earlier in June 1970 the same man had released an article entitled “A relational model of data for large shared data banks” that laid down the foundations of much of the theory behind modern relational databases. SQL development was further accelerated by a request from NASA for a technology that allowed querying of a database that contained rock information, brought back from the moon by the American government.
Where SQL can be used
Almost all modern database management systems (DBMS) allow SQL to be used to query and manipulate database tables. Some modern examples of databases that allow SQL to be used include Microsoft Access, SQL Server, Oracle and Sybase.
Although there are many version of SQL in order to be in compliance with ANSI SQL standard they are all required to support a set of commonly used SQL commands. To make things just that little more interesting a lot of the SQL database management systems superimpose their own propriety extensions on top of the SQL standard.
Main SQL Sections
SQL can be broken down into two main sections, the SQL Data Manipulation Language (DML) and the SQL Data Definition Language (DDL).
SQL Data Manipulation Language (DML)
SQL’s main premise is to allow databases to be queried; however the SQL language also contains commands to allow inserting, updating and deleting of records. The main commands that make up the DML part of the SQL language include: -
- SELECT – retrieves data from the database
- WHERE – provides conditions for which first keyword should be applied
- INSERT INTO – inserts data into the database
- UPDATE – updates data in the database
- DELETE – removes records from the database
SQL Data Definition Language (DDL)
The DDL part of the SQL language allows the core construction/destruction of database elements to take place. For example it provides commands that allow new database tables to be created and removed, primary keys to be defined, database tables to be linked and much more.
* Please note that the DDL part of SQL will be covered, along with more DML commands in the next tutorial I plan to release entitled, “SQL Tutorial – Advanced”.
SELECT Command
The SELECT command allows data to be retrieved from a database table. The syntax of the SELECT command is as follows: -
SELECT column(s) FROM table;
This tells the database system that you wish to select a certain column of data from a certain table in the database. For example the following would return you the first_names column of people stored within a “users” table: -
SELECT first_names FROM users;
However what if you wish to retrieve more than one column at a time? Well with the use of a comma (,) you can do just that. The following example retrieves both the first_names column and the passwords column from within a “users” table: -
SELECT first_names,passwords FROM users;
Another keyword, DISTINCT, can be placed after the SELECT keyword in SQL to return only one entry for each unique value in that column. So for example …
SELECT DISTINCT first_names FROM users;
… would return one entry in the results table for each person with a different name. This means even if there were one thousand users named “John”, only one result entry for “John” would shown.
Results that have been returned from a database system come so in the form of a results-set table. Most modern database systems allow this results-set table to be navigated with programming functions provided by technology such as ADO (ActiveX Data Object). For example if you had passed a database system a simple SELECT request for all the first_names in the “users” table you would then able to navigate up and down the results-set table using the functions provided. (e.g. move-to-next, move-to-first) This would, for example, allow you to receive into a program all of the users first names.
Syntax Notes
The semicolon used at the end of the example SQL code is not required in all database systems, but as it is the standard it is advisable to use it. Its main purpose is in separating out different SQL commands from each other. This gets important when providing multiple SQL statements to your database system at the same time.
The * symbol is defined in SQL as meaning “for all”. This means that if you wanted to retrieve every column from the “users” table the following code could be used: -
SELECT * FROM users;
WHERE Command
The SELECT command allows you to retrieve one or more columns of data from a database table; however what if you were just interested in retrieving records that matched a certain condition? (e.g. all users who lived in New York). Thanks to the WHERE command this becomes possible. The syntax of the WHERE command (when applied to SELECT statements) is as follows: -
SELECT column(s) FROM table WHERE column op value;
This instructs the database system to return the column(s) from a certain table as along as the condition defined by the WHERE command is true. For example if the following was passed ...
SELECT * FROM users WHERE age=25;
… then the database system would return all the records (because * was used) for all people located in the “users” table that matched the condition, age=25. This means everyone aged 25 will have their records returned in the results-set.
Many different op(operators) can be used to provide the condition laid down by the WHERE command. Some of these operators include:
- = Equals (e.g. A is equals to B)
- <> Not Equals (e.g. A is not equal to B, sometimes write as !=)
- < Less Than (e.g. A is less than B)
- > Greater Than (e.g. A is greater than B)
- LIKE Matches a Pattern (e.g. A is LIKE a defined pattern)
The LIKE keyword allows pattern matching to occur. For example if you wished to retrieve all the records for users within their 20’s you could use the following code: -
SELECT * FROM users WHERE age LIKE 2%;
The % (sometimes write, *) symbol is used as a wildcard (just like in many command line shells) that define areas of a value that are unknown and can be pattern matched. So the above tells the database system that the condition is that the age column should be equal to 2 and then anything else. As this column contains ages it’s able to match any users within their 20’s. This assumes that there is no one aged over 200 in the database, otherwise they to would also be matched.
The LIKE command does not just have to be restricted to numerical patterns however. It can be used to match character patterns just as well as numerical patterns. For example the following code returns the passwords for all users whose first_names begin with P: -
SELECT passwords FROM users WHERE first_names LIKE ‘P%’;
Syntax Notes
When specifying WHERE conditions if a word or characters are used then they should be enclosed within single quotation marks (e.g. WHERE first_names=’Jack’). On the other hand if numerical values are used they should not be enclosed within quotes (e.g. WHERE age=25).
INSERT INTO Command
Now that you can query your database (conditionally and non-conditionally) using SQL you may be thinking to yourself; but what if I want to add records to a database table, can SQL do that? Well I have news for you buster; it sure can! The INSERT INTO command allows for new records to be entered into a database table.
The syntax of the INSERT INTO command goes as follows: -
INSERT INTO table VALUES (value1, value2, value3, …);
The above tells the database system to insert into the table defined the values provided as a new record. This means that if for example the code …
INSERT INTO users VALUES (‘jack’, ‘smith’, ‘password45’, 40, ‘New York’);
.. was entered then a new record in the “users” table would be created and the values entered into the columns of that record.
The INSERT INTO command also allows new records to be entered without a value for all columns in the record being required. For example you could create a new entry in a “users” table but only supply the first_name, password and location, leaving out the second_name and age data. The syntax of the ‘advanced’ INSERT INTO is as follows: -
INSERT INTO table (column1, column2, column3 …} VALUES {value1, value2, value3 …);
The above code specifies the database table in question along with the columns of that table in which data will be inserted, in the new record. It then provides the data to be inserted into each column in the same sequence as the columns were provided.
As an example, if I wish to create a new record in a “users” table but I only wish to provide the first_name, password and age (leaving out the second_name and location) then I could do so with the following code: -
INSERT INTO users (first_name, password, age} VALUES {‘Tom’, ‘apple’, 28)
Syntax Notes
You may be wondering if the SQL keywords (e.g. SELECT, WHERE, INSERT INTO) need to be capitalized. The answer is no. However many people choose to format their SQL commands in this way to help distinguish between keywords and non keywords.
UPDATE Command
The UPDATE command delivers exactly what it promises, in that it allows data already located in a database table to be updated (changed). This is an extremely common and frequently used requirement for most databases. The syntax of the UPDATE command is as follows:-
UPDATE table SET column = new_value WHERE column = value;
The above code tells the database system that in the table identified I wish to set/change a supplied column’s current value with the new value supplied. It also identifies the record for which I wish the data to be changed using the WHERE keyword.
If I wished to change the password of Jack in my “users” table from “apple” to “orange” I could do so using the following code: -
UPDATE users SET password = ‘apple’ WHERE first_names = ‘Jack’;
The UPDATE command doesn’t just limit you to changing one column of data at a time however. For example, using the syntax presented below it’s possible to update as many columns of data as you wish, within the same statement.
UPDATE users SET first_names = ‘Kenny’, age = 25, location = ‘South Spain’ WHERE second_name = ‘Brown’;
The above code requests that the database system updates the first_names, age and location data with the values provided for a user with a second_name of ‘Brown’.
DELETE Command
The DELETE command allows records from a database table to be removed. This could be used if for example you wished to remove a user from a “users” table. The syntax of the command is as follows: -
DELETE FROM table WHERE column = value;
The above SQL code is telling the database system to remove a record that’s column value matches the column value provided from the specified table. For example if you wished to remove a user with the second name of “Black” from a “users” table you could use the following code:-
DELETE FROM users WHERE second_name = ‘Black’;
If for example you wished to remove all records from a database table but to retain the table itself for reuse the following code would do this: -
DELETE FROM users;
Summary
The above tutorial should have hopefully given you an overview of SQL including its main parts and a little of its history along with a grasp of some of the basics of the SQL DML language. You should now be able to execute queries against databases using the SELECT, WHERE and LIKE commands, insert data into a database using the INSERT INTO command, update values using the UPDATE command and finally delete records using the DELETE command.
