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:
2. Conditional Selection.
3. Adding Data.
4. Updating Data.
5. Deleting Data.
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
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'
Keep in mind that each database management system (DBMS) has different ways for entering and executing SQL commands; you have to see the manuals to help you get onto the system, so that you can use SQL. With this we finish our first article, in the next article I will talk more about the SELECT statement and about adding data to the database.
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 |
That's all for the using of like and adding to the database, in the next article we are going to talk about updating and deleting old data.
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 |
That's all for now about deleting and updating using SQL, Give it a try and good luck.
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.