ORACLE(SQL)

SQL Tutorial

SQL is short for Structured Query Language and is a widely used database language, providing means of data manipulation (store, retrieve, update, delete) and database creation.

Almost all modern Relational Database Management Systems like MS SQL Server, Microsoft Access, MSDE, Oracle, DB2, Sybase, MySQL, Postgres and Informix use SQL as standard database language. Now a word of warning here, although all those RDBMS use SQL, they use different SQL dialects. For example MS SQL Server specific version of the SQL is called T-SQL, Oracle version of SQL is called PL/SQL, MS Access version of SQL is called JET SQL, etc.

Our SQL tutorial will teach you how to use commonly used SQL commands and you will be able to apply most of the knowledge gathered from this SQL tutorial to any of the databases above.
SQL Tutorial Table of Contents

SQL Tutorial
Learn what SQL (Structured Query Language) is, and where and how it is used.

SQL Table
SQL Database Tables are the foundation of every RDBMS (Relational Database Management System). Learn more about SQL tables here.

SQL SELECT
Learn how to use the SELECT SQL statement to retrieve data from a SQL database table.

SQL SELECT INTO
Learn how to use the SQL SELECT INTO statement to copy data between database tables.

SQL DISTINCT
Learn how to use the SQL DISTINCT clause together with the SQL SELECT keyword, to return a dataset with unique entries for certain database table column.

SQL WHERE
The SQL WHERE command is used to specify selection criteria, thus restricting the result of a SQL query.

SQL LIKE
The SQL LIKE clause is used along with the SQL WHERE clause and specifies criteria based on a string pattern.

SQL INSERT INTO
Learn how to use the SQL INSERT INTO clause to insert data into a SQL database table.

SQL UPDATE
Learn how to use the SQL UPDATE statement to update data in a SQL database table.

SQL DELETE
Learn how to use the SQL DELETE statement to delete data from a SQL database table.

SQL ORDER BY
Learn how to use the SQL ORDER BY statement to sort the data retrieved in your SQL query.

SQL OR & AND
Learn how to use the SQL OR & AND keywords together with the SQL WHERE clause to add several conditions to your SQL statement.

SQL IN
The SQL IN clause allows you to specify discrete values in your SQL WHERE search criteria.

SQL BETWEEN
The SQL BETWEEN & AND keywords define a range of data between 2 values.

SQL Aliases
SQL aliases can be used with database tables and/or with database table columns, depending on task you are performing.

SQL COUNT
The SQL COUNT aggregate function is used to count the number of rows in a database table.

SQL MAX
The SQL MAX aggregate function allows us to select the highest (maximum) value for a certain column.

SQL MIN
The SQL MIN aggregate function allows us to select the lowest (minimum) value for a certain column.

SQL AVG
The SQL AVG aggregate function selects the average value for a certain table column.

SQL SUM
The SQL SUM aggregate function allows selecting the total for a numeric column.

SQL GROUP BY
The SQL GROUP BY statement is used along with the SQL aggregate functions like SUM to provide means of grouping the result dataset by certain database table column(s).

SQL HAVING
The SQL HAVING clause is used to restrict conditionally the output of a SQL statement, by a SQL aggregate function used in your SELECT list of columns.

SQL JOIN
The SQL JOIN clause is used whenever we have to select data from 2 or more tables.

SQL Database Table

The foundation of every Relational Database Management System is a database object called table. Every database consists of one or more tables, which store the database’s data/information. Each table has its own unique name and consists of columns and rows.

The database table columns (called also table fields) have their own unique names and have a pre-defined data types. Table columns can have various attributes defining the column functionality (the column is a primary key, there is an index defined on the column, the column has certain default value, etc.).

While table columns describe the data types, the table rows contain the actual data for the columns.

Here is an example of a simple database table, containing customers data. The first row, listed in bold, contains the names of the table columns:

Table: Customers

FirstName LastName Email DOB Phone
John Smith John.Smith@yahoo.com 2/4/1968 626 222-2222
Steven Goldfish goldfish@fishhere.net 4/4/1974 323 455-4545
Paula Brown pb@herowndomain.org 5/24/1978 416 323-3232
James Smith jim@supergig.co.uk 20/10/1980 416 323-8888

Now that we’ve learned what is a database table, we can continue with our sql tutorial and learn how to manipulate the data within the database tables.

CREATION TABLE:
create table customers(firstname varchar2(20),lastname varchar2(20),
2 Email varchar2(20),dob date,phone number(10));

SQL SELECT

The SQL SELECT statement is used to select data from a SQL database table. This is usually the very first SQL command every SQL newbie learns and this is because the SELECT SQL statement is one of the most used SQL commands.

Please have a look at the general SQL SELECT syntax:


SELECT Column1, Column2, Column3,
FROM Table1

The list of column names after the SQL SELECT command determines which columns you want to be returned in your result set. If you want to select all columns from a database table, you can use the following SQL statement:


SELECT * FROM Table1;

When the list of columns following the SELECT SQL command is replaced with asterix (*) all table columns are returned. Word of caution here, it’s always better to explicitly specify the columns in the SELECT list, as this will improve your query performance significantly.

The table name following the SQL FROM keyword (in our case Table1) tells the SQL interpreter which table to use to retrieve the data.

SQL SELECT INTO

The SQL SELECT INTO statement is used to select data from a SQL database table and to insert it to a different table at the same time.

The general SQL SELECT INTO syntax looks like this:


SELECT Column1, Column2, Column3,
INTO Table2
FROM Table1

The list of column names after the SQL SELECT command determines which columns will be copied, and the table name after the SQL INTO keyword specifies to which table to copy those rows.

If we want to make an exact copy of the data in our Customers table, we need the following SQL SELECT INTO statement:


SELECT *
INTO Customers_copy
FROM Customers

SQL DISTINCT

The SQL DISTINCT clause is used together with the SQL SELECT keyword, to return a dataset with unique entries for certain database table column.

We will use our Customers database table to illustrate the usage of SQL DISTINCT.

FirstName LastName Email DOB Phone
John Smith John.Smith@yahoo.com 2/4/1968 626 222-2222
Steven Goldfish goldfish@fishhere.net 4/4/1974 323 455-4545
Paula Brown pb@herowndomain.org 5/24/1978 416 323-3232
James Smith jim@supergig.co.uk 20/10/1980 416 323-8888

For example if we want to select all distinct surnames from our Customers table, we will use the following SQL DISTINCT statement:


SELECT DISTINCT LastName
FROM Customers

The result of the SQL DISTINCT expression above will look like this:

LastName
Smith
Goldfish
Brown

SQL WHERE

The SQL WHERE clause is used to select data conditionally, by adding it to already existing SQL SELECT query. We are going to use the Customers table from the previous chapter, to illustrate the use of the SQL WHERE command.

Table: Customers

FirstName LastName Email DOB Phone
John Smith John.Smith@yahoo.com 2/4/1968 626 222-2222
Steven Goldfish goldfish@fishhere.net 4/4/1974 323 455-4545
Paula Brown pb@herowndomain.org 5/24/1978 416 323-3232
James Smith jim@supergig.co.uk 20/10/1980 416 323-8888

If we want to select all customers from our database table, having last name 'Smith' we need to use the following SQL syntax:


SELECT *
FROM Customers
WHERE LastName = 'Smith'

The result of the SQL expression above will be the following:

FirstName LastName Email DOB Phone
John Smith John.Smith@yahoo.com 2/4/1968 626 222-2222
James Smith jim@supergig.co.uk 20/10/1980 416 323-8888

In this simple SQL query we used the "=" (Equal) operator in our WHERE criteria:

LastName = 'Smith'

But we can use any of the following comparison operators in conjunction with the SQL WHERE clause:

<> (Not Equal)


SELECT *
FROM Customers
WHERE LastName <> 'Smith'

> (Greater than)


SELECT *
FROM Customers
WHERE DOB > '1/1/1970'

>= (Greater or Equal)


SELECT *
FROM Customers
WHERE DOB >= '1/1/1970'

< (Less than)


SELECT *
FROM Customers
WHERE DOB < '1/1/1970'

<= (Less or Equal)


SELECT *
FROM Customers
WHERE DOB =< '1/1/1970'

LIKE (similar to)


SELECT *
FROM Customers
WHERE Phone LIKE '626%'

Note the LIKE syntax is different with the different RDBMS (SQL Server syntax used above). Check the SQL LIKE article for more details.

Between (Defines a range)


SELECT *
FROM Customers
WHERE DOB BETWEEN '1/1/1970' AND '1/1/1975'

SQL LIKE

We will use the Customers table to illustrate the SQL LIKE clause usage:

FirstName LastName Email DOB Phone
John Smith John.Smith@yahoo.com 2/4/1968 626 222-2222
Steven Goldfish goldfish@fishhere.net 4/4/1974 323 455-4545
Paula Brown pb@herowndomain.org 5/24/1978 416 323-3232
James Smith jim@supergig.co.uk 20/10/1980 416 323-8888

The SQL LIKE clause is very useful when you want to specify a search condition within your SQL WHERE clause, based on a part of a column contents. For example if you want to select all customers having FirstName starting with 'J' you need to use the following SQL statement:


SELECT *
FROM Customers
WHERE FirstName LIKE 'J%'

Here is the result of the SQL statement above:

FirstName LastName Email DOB Phone
John Smith John.Smith@yahoo.com 2/4/1968 626 222-2222
James Smith jim@supergig.co.uk 20/10/1980 416 323-8888

If you want to select all Customers with phone numbers starting with '416' you will use this SQL expression:


SELECT *
FROM Customers
WHERE Phone LIKE '416%'

The '%' is a so called wildcard character and represents any string in our pattern.
You can put the wildcard anywhere in the string following the SQL LIKE clause and you can put as many wildcards as you like too.

Note that different databases use different characters as wildcard characters, for example '%' is a wildcard character for MS SQL Server representing any string, and '*' is the corresponding wildcard character used in MS Access.

Another wildcard character is '_' representing any single character.

The '[]' specifies a range of characters. Have a look at the following SQL statement:


SELECT *
FROM Customers
WHERE Phone LIKE '[4-6]_6%'

This SQL expression will return all customers satisfying the following conditions:

* The Phone column starts with a digit between 4 and 6 ([4-6])
* Second character in the Phone column can be anything (_)
* The third character in the Phone column is 6 (6)
* The remainder of the Phone column can be any character string (%)

Here is the result of this SQL expression:

FirstName LastName Email DOB Phone
John Smith John.Smith@yahoo.com 2/4/1968 626 222-2222
Paula Brown pb@herowndomain.org 5/24/1978 416 323-3232
James Smith jim@supergig.co.uk 20/10/1980 416 323-8888

SQL INSERT INTO

The SQL INSERT INTO syntax has 2 main forms and the result of either of them is adding a new row into the database table.

The first syntax form of the INSERT INTO SQL clause doesn't specify the column names where the data will be inserted, but just their values:


INSERT INTO Table1
VALUES (value1, value2, value3…)

The second form of the SQL INSERT INTO command, specifies both the columns and the values to be inserted in them:


INSERT INTO Table1 (Column1, Column2, Column3…)
VALUES (Value1, Value2, Value3…)

As you might already have guessed, the number of the columns in the second INSERT INTO syntax form must match the number of values into the SQL statement, otherwise you will get an error.

If we want to insert a new row into our Customers table, we are going to use one of the following 2 SQL statements:


INSERT INTO Customers
VALUES ('Peter', 'Hunt', 'peter.hunt@tgmail.net', '1/1/1974', '626 888-8888')


INSERT INTO Customers (FirstName, LastName, Email, DOB, Phone)
VALUES ('Peter', 'Hunt', 'peter.hunt@tgmail.net', '1/1/1974', '626 888-8888')

The result of the execution of either of the 2 INSERT INTO SQL statements will be a new row added to our Customers database table:

FirstName LastName Email DOB Phone
John Smith John.Smith@yahoo.com 2/4/1968 626 222-2222
Steven Goldfish goldfish@fishhere.net 4/4/1974 323 455-4545
Paula Brown pb@herowndomain.org 5/24/1978 416 323-3232
James Smith jim@supergig.co.uk 20/10/1980 416 323-8888
Peter Hunt peter.hunt@tgmail.net 1/1/1974 626 888-8888

If you want to enter data for just a few of the table columns, you’ll have to use the second syntax form of the SQL INSERT INTO clause, because the first form will produce an error if you haven’t supplied values for all columns.

To insert only the FirstName and LastName columns, execute the following SQL statement:


INSERT INTO Customers (FirstName, LastName)
VALUES ('Peter', 'Hunt')

SQL UPDATE

The SQL UPDATE general syntax looks like this:


UPDATE Table1
SET Column1 = Value1, Column2 = Value2
WHERE Some_Column = Some_Value

The SQL UPDATE clause changes the data in already existing database row(s) and usually we need to add a conditional SQL WHERE clause to our SQL UPDATE statement in order to specify which row(s) we intend to update.

If we want to update the Mr. Steven Goldfish's date of birth to '5/10/1974' in our Customers database table

FirstName LastName Email DOB Phone
John Smith John.Smith@yahoo.com 2/4/1968 626 222-2222
Steven Goldfish goldfish@fishhere.net 4/4/1974 323 455-4545
Paula Brown pb@herowndomain.org 5/24/1978 416 323-3232
James Smith jim@supergig.co.uk 20/10/1980 416 323-8888

we need the following SQL UPDATE statement:


UPDATE Customers
SET DOB = '5/10/1974'
WHERE LastName = 'Goldfish' AND FirstName = 'Steven'

If we don’t specify a WHERE clause in the SQL expression above, all customers' DOB will be updated to '5/10/1974', so be careful with the SQL UPDATE command usage.

We can update several database table rows at once, by using the SQL WHERE clause in our UPDATE statement. For example if we want to change the phone number for all customers with last name Smith (we have 2 in our example Customers table), we need to use the following SQL UPDATE statement:


UPDATE Customers
SET Phone = '626 555-5555'
WHERE LastName = 'Smith'

After the execution of the UPDATE SQL expression above, the Customers table will look as follows:

FirstName LastName Email DOB Phone
John Smith John.Smith@yahoo.com 2/4/1968 626 555-5555
Steven Goldfish goldfish@fishhere.net 4/4/1974 323 455-4545
Paula Brown pb@herowndomain.org 5/24/1978 416 323-3232
James Smith jim@supergig.co.uk 20/10/1980 626 555-5555

SQL DELETE

So far we’ve learnt how to select data from a database table and how to insert and update data into a database table. Now it’s time to learn how to remove data from a database. Here comes the SQL DELETE statement!

The SQL DELETE command has the following generic SQL syntax:


DELETE FROM Table1
WHERE Some_Column = Some_Value

If you skip the SQL WHERE clause when executing SQL DELETE expression, then all the data in the specified table will be deleted. The following SQL statement will delete all the data from our Customers table and we’ll end up with completely empty table:


DELETE FROM Table1

If you specify a WHERE clause in your SQL DELETE statement, only the table rows satisfying the WHERE criteria will be deleted:


DELETE FROM Customers
WHERE LastName = 'Smith'

The SQL query above will delete all database rows having LastName 'Smith' and will leave the Customers table in the following state:

FirstName LastName Email DOB Phone
Steven Goldfish goldfish@fishhere.net 4/4/1974 323 455-4545
Paula Brown pb@herowndomain.org 5/24/1978 416 323-3232

SQL ORDER BY

The SQL ORDER BY clause comes in handy when you want to sort your SQL result sets by some column(s). For example if you want to select all the persons from the already familiar Customers table and order the result by date of birth, you will use the following statement:


SELECT * FROM Customers
ORDER BY DOB

The result of the above SQL expression will be the following:

FirstName LastName Email DOB Phone
John Smith John.Smith@yahoo.com 2/4/1968 626 222-2222
Steven Goldfish goldfish@fishhere.net 4/4/1974 323 455-4545
Paula Brown pb@herowndomain.org 5/24/1978 416 323-3232
James Smith jim@supergig.co.uk 20/10/1980 416 323-8888

As you can see the rows are sorted in ascending order by the DOB column, but what if you want to sort them in descending order? To do that you will have to add the DESC SQL keyword after your SQL ORDER BY clause:


SELECT * FROM Customers
ORDER BY DOB DESC

The result of the SQL query above will look like this:

FirstName LastName Email DOB Phone
James Smith jim@supergig.co.uk 20/10/1980 416 323-8888
Paula Brown pb@herowndomain.org 5/24/1978 416 323-3232
Steven Goldfish goldfish@fishhere.net 4/4/1974 323 455-4545
John Smith John.Smith@yahoo.com 2/4/1968 626 222-2222

If you don't specify how to order your rows, alphabetically or reverse, than the result set is ordered alphabetically, hence the following to SQL expressions produce the same result:


SELECT * FROM Customers
ORDER BY DOB

SELECT * FROM Customers
ORDER BY DOB ASC

You can sort your result set by more than one column by specifying those columns in the SQL ORDER BY list. The following SQL expression will order by DOB and LastName:


SELECT * FROM Customers
ORDER BY DOB, LastName

SQL AND & OR

The SQL AND clause is used when you want to specify more than one condition in your SQL WHERE clause, and at the same time you want all conditions to be true.
For example if you want to select all customers with FirstName "John" and LastName "Smith", you will use the following SQL expression:


SELECT * FROM Customers
WHERE FirstName = 'John' AND LastName = 'Smith'

The result of the SQL query above is:

FirstName LastName Email DOB Phone
John Smith John.Smith@yahoo.com 2/4/1968 626 222-2222

The following row in our Customer table, satisfies the second of the conditions (LastName = 'Smith'), but not the first one (FirstName = 'John'), and that's why it's not returned by our SQL query:

FirstName LastName Email DOB Phone
James Smith jim@supergig.co.uk 20/10/1980 416 323-8888

The SQL OR statement is used in similar fashion and the major difference compared to the SQL AND is that OR clause will return all rows satisfying any of the conditions listed in the WHERE clause.

If we want to select all customers having FirstName 'James' or FirstName 'Paula' we need to use the following SQL statement:


SELECT * FROM Customers
WHERE FirstName = 'James' OR FirstName = 'Paula'

The result of this query will be the following:

FirstName LastName Email DOB Phone
Paula Brown pb@herowndomain.org 5/24/1978 416 323-3232
James Smith jim@supergig.co.uk 20/10/1980 416 323-8888

You can combine AND and OR clauses anyway you want and you can use parentheses to define your logical expressions.
Here is an example of such a SQL query, selecting all customers with LastName 'Brown' and FirstName either 'James' or 'Paula':


SELECT * FROM Customers
WHERE (FirstName = 'James' OR FirstName = 'Paula') AND LastName = 'Brown'

The result of the SQL expression above will be:

FirstName LastName Email DOB Phone
Paula Brown pb@herowndomain.org 5/24/1978 416 323-3232

SQL IN

The SQL IN clause allows you to specify discrete values in your SQL WHERE search criteria.

THE SQL IN syntax looks like this:


SELECT Column1, Column2, Column3, …
FROM Table1
WHERE Column1 IN (Valu1, Value2, …)

Lets use the EmployeeHours table to illustrate how SQL IN works:

Employee Date Hours
John Smith 5/6/2004 8
Allan Babel 5/6/2004 8
Tina Crown 5/6/2004 8
John Smith 5/7/2004 9
Allan Babel 5/7/2004 8
Tina Crown 5/7/2004 10
John Smith 5/8/2004 8
Allan Babel 5/8/2004 8
Tina Crown 5/8/2004 9

Consider the following SQL query using the SQL IN clause:


SELECT *
FROM EmployeeHours
WHERE Date IN ('5/6/2004', '5/7/2004')

This SQL expression will select only the entries where the column Date has value of '5/6/2004' or '5/7/2004', and you can see the result below:

Employee Date Hours
John Smith 5/6/2004 8
Allan Babel 5/6/2004 8
Tina Crown 5/6/2004 8
John Smith 5/7/2004 9
Allan Babel 5/7/2004 8
Tina Crown 5/7/2004 10

We can use the SQL IN statement with another column in our EmployeeHours table:


SELECT *
FROM EmployeeHours
WHERE Hours IN (9, 10)

The result of the SQL query above will be:

Employee Date Hours
John Smith 5/7/2004 9
Tina Crown 5/7/2004 10
Tina Crown 5/8/2004 9

SQL BETWEEN

The SQL BETWEEN & AND keywords define a range of data between 2 values.

The SQL BETWEEN syntax looks like this:


SELECT Column1, Column2, Column3, …
FROM Table1
WHERE Column1 BETWEEN Value1 AND Value2

The 2 values defining the range for SQL BETWEEN clause can be dates, numbers or just text.

In contrast with the SQL IN keyword, which allows you to specify discrete values in your SQL WHERE criteria, the SQL BETWEEN gives you the ability to specify a range in your search criteria.

We are going to use the familiar Customers table to show how SQL BETWEEN works:

FirstName LastName Email DOB Phone
John Smith John.Smith@yahoo.com 2/4/1968 626 222-2222
Steven Goldfish goldfish@fishhere.net 4/4/1974 323 455-4545
Paula Brown pb@herowndomain.org 5/24/1978 416 323-3232
James Smith jim@supergig.co.uk 20/10/1980 416 323-8888

Consider the following SQL BETWEEN statement:


SELECT *
FROM Customers
WHERE DOB BETWEEN '1/1/1975' AND '1/1/2004'

The SQL BETWEEN statement above will select all Customers having DOB column between '1/1/1975' and '1/1/2004' dates. Here is the result of this SQL expression:

FirstName LastName Email DOB Phone
Paula Brown pb@herowndomain.org 5/24/1978 416 323-3232
James Smith jim@supergig.co.uk 20/10/1980 416 323-8888

SQL ALIASES

SQL aliases can be used with database tables and with database table columns, depending on task you are performing.

SQL column aliases are used to make the output of your SQL queries easy to read and more meaningful:


SELECT Employee, SUM(Hours) As SumHoursPerEmployee
FROM EmployeeHours
GROUP BY Employee

In the example above we created SQL alias SumHoursPerEmployee and the result of this SQL query will be the following:

Employee SumHoursPerEmployee
John Smith 25
Allan Babel 24
Tina Crown 27

Consider the following SQL statement, showing how to use SQL table aliases:


SELECT Emp.Employee
FROM EmployeeHours AS Emp

Here is the result of the SQL expression above:

Employee
John Smith
Allan Babel
Tina Crown

The SQL table aliases are very useful when you select data from multiple tables.

SQL COUNT

The SQL COUNT aggregate function is used to count the number of rows in a database table.

The SQL COUNT syntax is simple and looks like this:


SELECT COUNT(Column1)
FROM Table1

If we want to count the number of customers in our Customers table, we will use the following SQL COUNT statement:


SELECT COUNT(LastName) AS NumberOfCustomers
FROM Customers

The result of this SQL COUNT query will be:

NumberOfCustomers
4

SQL MAX

The SQL MAX aggregate function allows us to select the highest (maximum) value for a certain column.

The SQL MAX function syntax is very simple and it looks like this:


SELECT MAX(Column1)
FROM Table1

If we use the Customers table from our previous chapters, we can select the highest date of birth with the following SQL MAX expression:


SELECT MAX(DOB) AS MaxDOB
FROM Customers

SQL MIN

The SQL MIN aggregate function allows us to select the lowest (minimum) value for a certain column.

The SQL MIN function syntax is very simple and it looks like this:


SELECT MIN(Column1)
FROM Table1

If we use the Customers table from our previous chapters, we can select the lowest date of birth with the following SQL MIN expression:


SELECT MIN(DOB) AS MinDOB
FROM Customers

SQL AVG

The SQL AVG aggregate function selects the average value for certain table column.

Have a look at the SQL AVG syntax:


SELECT AVG(Column1)
FROM Table1

If we want to find out what is the average SaleAmount in the Sales table, we will use the following SQL AVG statement:


SELECT AVG(SaleAmount) AS AvgSaleAmount
FROM Sales

which will result in the following dataset:

AvgSaleAmount
$195.73