SQL - Tutorial

SQL is 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 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 - Table of Contents

SQL - Table: Database Tables are the foundation of every RDBMS (Relational Database Management System).

SQL - SELECT: How to use the SELECT statement to retrieve data from a SQL database.

SQL - SELECT INTO: How to use this statement to copy data between database tables.

SQL - DISTINCT: How to use this clause together with the SELECT keyword, to return a dataset with unique entries for certain database table column.

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

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

SQL - INSERT INTO: How to use INSERT INTO clause to insert data into a SQL database.

SQL - UPDATE: How to use the UPDATE statement to update data in a SQL database.

SQL - DELETE: How to use the DELETE statement to delete data from a SQL database table.

SQL - ORDER BY: How to use this statement to sort the data retrieved in your SQL query.

SQL - OR & AND: How to use these keywords together with the WHERE clause to add several conditions to your SQL statement.

SQL - IN: This clause allows you to specify discrete values in your WHERE search criteria.

SQL - BETWEEN: These keywords define a range of data between 2 values.

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

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

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

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

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

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

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

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

SQL - JOIN: The 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 tutorial and learn how to manipulate the data within the database tables.

SQL - SELECT

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

Please have a look at the general SELECT syntax:

SELECT Column1, Column2, Column3, FROM Table1

The list of column names after the 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 command is replaced with asterisk (*) 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 FROM keyword (in our case Table1) tells the SQL interpreter which table to use to retrieve the data.

SQL - SUM

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

The SUM syntax is displayed below:

SELECT SUM(Column1) FROM Table1

We are going to use the Sales table to illustrate the use of SUM clause:

Sales:

CustomerID	Date	SaleAmount
2	5/6/2004	$100.22
1	5/7/2004	$99.95
3	5/7/2004	$122.95
3	5/13/2004	$100.00
4	5/22/2004	$555.55

Consider the following SUM statement:

SELECT SUM(SaleAmount) FROM Sales

This SQL statement will return the sum of all SaleAmount fields and the result of it will be:

SaleAmount

$978.67

Of course you can specify search criteria using the WHERE clause in your SUM statement. If you want to select the total sales for customer with CustomerID = 3, you will use the following SUM statement:

SELECT SUM(SaleAmount) FROM Sales WHERE CustomerID = 3

The result will be:

SaleAmount

$222.95

SQL - HAVING

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

You can't specify criteria in a WHERE clause against a column in the SELECT list for which Aggregate Function is used. For example the following SQL statement will generate an error:

SELECT Employee, SUM (Hours) FROM EmployeeHours WHERE SUM (Hours) > 24 GROUP BY Employee

The HAVING clause is used to do exactly this, to specify a condition for an aggregate function which is used in your query:

SELECT Employee, SUM (Hours) FROM EmployeeHours GROUP BY Employee HAVING SUM (Hours) > 24

The above SQL statement will select all employees and the sum of their respective hours, as long as this sum is greater than 24. The result of the HAVING clause can be seen below:

Employee	Hours
John Smith	25
Tina Crown	27

SQL - SELECT INTO

The 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 SELECT INTO syntax looks like this:

SELECT Column1, Column2, Column3, INTO Table2 FROM Table1

The list of column names after the 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 SELECT INTO statement:

SELECT * INTO Customers_copy FROM Customers