Themestream Contributor
SQL Tutorial
by Mohammed Saleh
January 8, 2001

 

This article is the first of several articles aimed to provide a tutorial on SQL (Structured Query Language). SQL is the language that allows us to talk to deferent relational database management systems RDBMS, such as Oracle, Microsoft SQL Server, Access, and others, and so we need SQL to retrieve data from deferent RDBMS, define the data in the database and manipulate the data. 

The subjects that we will talk about in our tutorial include:

1. SELECT statement and SQL Queries.

2.       Conditional Selection.

3.       Adding Data.

4.       Updating Data. 

5.       Deleting Data.

6. Some advanced topics in SQL.

And every part of our discussion will have some examples to demonstrate the case.  We will start with the SELECT statement, we use the SELECT statement to query and retrieve data from our database. In any relational database data is stored as records in tables, we will work on a table called employees, that will hold some information about the employees of company X, and it will look just like this.

SSN	Name	Age	Job	Salary
12343457	Mike	26	Sales	2000
35636788	Rose	23	Programmer	3200
34536788	John	27	Manager	5000

The basic syntax of the SELECT statement looks like this:

SELECT Column1 Name, Column2 Name, Column3 Name... FROM Table Name

Column name resembles SSN, Name, Age, etc… And the table name will be the name of the table in your database in our case we will call it Employee. Now let's say that we want to see the names of all employees and their salaries, for that we will use a SELECT statement like this.

SELECT Name, Salary From Employee

The result from our SQL query will be stored in a Record set. This Record set can be thought of as a table. You can navigate through the Record set with some programming functions; we will talk about these functions in other tutorials when we talk about things like ADO.

The following is the results of our your query of the database:

Name	Salary
Mike	2000
Rose	3200
John	5000

To get all columns of a table without typing all column names use: 

SELECT * FROM Table Name

Conditional Selection

In a lot of cases we need to retrieve data under some conditions, to do so we add a WHERE clause the SELECT statement with the following syntax:

SELECT column FROM table WHERE column condition value

With the WHERE clause, you can use many conditions Like = for equal, <> for not equal, > for greater than, < for less than and much more.

Lets say that we want to select employees that have salaries more than 3000, we add a WHERE clause to the SELECT statement like this: 

SELECT * FROM Employee WHERE Salary > 3000

We will have the following result:

Name	Salary
Rose	3200
John	5000

If you are working with text you need to put single quotes around the conditional values in the SELECT statement. 

For Example:

SELECT * FROM Employee WHERE Name='Rose'

In our last article (SQL Tutorial Part I) we talked about SELECT statement and the conditional select, and we have done some example to show how we retrieve data from a database, in this article we are going to continue with SQL, we will talk about using LIKE in our SELECT statements then we are going to talk about adding new records to our database.

First we will explain the use of LIKE with our SQL Statements, you remember the table Employee in our pervious article it contained several information about the employees in company X, like the SSN, Name, Age, Job and Salary.

SSN	Name	Age	Job	Salary
65786889	Mike	25	Sales	2000
37823789	Rose	24	Programmer	3200
435376899	John	28	Manager	5000
563089000	Mona	22	Programmer	2300

Lets say that we want to see all the information about employees that have names starting with ‘M', to do so we use the key word LIKE with the percent sign ‘%' to represent any possible set of characters that might appear after, before or around the ‘M', if you want to replace % with characters after the ‘M' you should write it like this ‘M%', and write this ‘%M' if you want to replace the characters before the ‘M', and if you want the ‘M' to appear at the middle of the name use ‘%M%', that's for conditional value and we have to write LIKE key word as the condition operator like this:

SELECT * FROM Employee WHERE Name LIKE ‘M%' 

The previous SELECT statement will return the following Record Set:

SSN	Name	Age	Job	Salary
65786889	Mike	25	Sales	2000
563089000	Mona	22	Programmer	2300

 

Adding Data

To insert rows into a table we use the INSERT INTO statement like this:

INSERT INTO table name (column1, column2...)

VALUES (value1, value2...)

You write the INSERT INTO followed by the table name, columns names, and the key word VALUES and after that the new values you want to add or you may skip the columns names part and just write it like this.

INSERT INTO table name

VALUES (value1, value2...)

To add anew Employee information to our database we just do the following:

INSERT INTO Employee (SSN, Name, Age, Job, Salary)

VALUES(43562753, ‘Sarah', 21, ‘Programmer', 2000)

 

Or you write the values without the columns names like this:

INSERT INTO Employee VALUES (43562753, ‘Sarah', 21, ‘Programmer', 2000)

The result record set is:

SSN	Name	Age	Job	Salary
65786889	Mike	25	Sales	2000
37823789	Rose	24	Programmer	3200
435376899	John	28	Manager	5000
563089000	Mona	22	Programmer	2300
43562753	Sarah	21	Programmer	2000

At this part of our tutorial we are going to show how you can delete or update old records of data in your database, and as always we will work on the Employee table:

SSN	Name	Age	Job	Salary
65786889	Mike	25	Sales	2000
37823789	Rose	24	Programmer	3200
435376899	John	28	Manager	5000
563089000	Mona	22	Programmer	2300

First we are going to talk about Deleting Data, to Delete records or rows from table in a database we use the DELETE statement.

DELETE FROM table name WHERE condition

All the data in the table (specified by the table name) that meets the condition at the end of the statement will be deleted, the condition will look something like Column name = value
Let's try to delete the data about Mona from our database the statement will look something like this:

DELETE FROM Employee WHERE Name = 'Mona'

The result after executing the above statement is:

SSN	Name	Age	Job	Salary
65786889	Mike	25	Sales	2000
37823789	Rose	24	Programmer	3200
435376899	John	28	Manager	5000

The second part of our article will be about how to Update Data using SQL, to do so we use the UPDATE statement:

UPDATE table name SET column1=new value1, column2=new value2…

WHERE column name = value

The above statement will look in the specified table for the record that meets the condition and change some or all the data stored according to what we write in the SET part of our statement, lets try to update the data about Rose in our table we want to change her job to Designer, the Salary from3200 to 3500 and the age to 25, to do so we write the following:

UPDATE Employee SET Age =25, Job='Designer', Salary=3500

WHERE Name = 'Rose'

The result for executing the update statement will look like this:

SSN	Name	Age	Job	Salary
65786889	Mike	25	Sales	2000
37823789	Rose	25	Designer	3500
435376899	John	28	Manager	5000

When we try to retrieve data from any database using SQL SELECT statement the result will be displayed with no particular order or sequence, to see the results in ascending order depending on the values of specific field we have to use the ORDER BY clause with our SELECT statement, so the syntax of the SELECT statement will look like this:

SELECT field name FROM table name ORDER BY field name 

and to see some life example we will use our old Employee table:

SSN	Name	Age	Job	Salary
3253637	Mike	25	Sales	2000
9675894	Rose	24	Programmer	2300
4575778	John	28	Manager	5000
4547589	Adam	30	Project Manager	4000
7657474	Lona	22	Programmer	2100

first of all we will retrieve employees names and salaries depending on their salaries, the SQL will look like this:

SELECT name, salary FROM Employee ORDER BY Salary

The Result:

Name	Salary
Mike	2000
Lona	2100
Rose	2300
Adam	4000
John	5000

You see the result fields is ordered from the lowest salary to the highest one, this is for numbers ordering for character ordering it will be ordered from A to Z, but what if I need to order the result in descending sequence, in this case you need to add (DESC) word after the field name in the ORDER BY clause like this:

SELECT field name FROM table name ORDER BY field name DESC

If we add DESC to or previous SELECT statement the following result will be displayed:

Name	Salary
John	5000
Adam	4000
Rose	2300
Lona	2100
Mike	2000

 

give it a try and good luck.