SQL Fundamentals

Database Tables

A database most often contains one or more tables. Each table is identified by a name (e.g. “Customers” or “Orders”). Tables contain records (rows) with data.

Below is an example of a table called “Persons”:

P_Id First_Name Last_Name Address City
1 Jill McGuire Oakley Hay 9 Corby
2 Bob Builder The Main Road 23 Corby
3 Peter Pan Neverland Ohio

The table above contains three records (one for each person) and five columns (P_Id, First_Name, Last_Name, Address, and City).


SQL Statements

Most of the actions you need to perform on a database are done with SQL statements.

The following SQL statement will select all the records in the “Persons” table:

SELECT * FROM Persons

In this tutorial we will teach you all about the different SQL statements.


Keep in Mind That…

  • SQL is not case sensitive

Semicolon after SQL Statements?

Some database systems require a semicolon at the end of each SQL statement.

Semicolon is the standard way to separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to the server.

We are using MS Access and SQL Server 2000 and we do not have to put a semicolon after each SQL statement, but some database programs force you to use it.


SQL DML and DDL

SQL can be divided into two parts: The Data Manipulation Language (DML) and the Data Definition Language (DDL).

The query and update commands form the DML part of SQL:

  • SELECT – extracts data from a database
  • UPDATE – updates data in a database
  • DELETE – deletes data from a database
  • INSERT INTO – inserts new data into a database

The DDL part of SQL permits database tables to be created or deleted. It also define indexes (keys), specify links between tables, and impose constraints between tables.
The most important DDL statements in SQL are:

  • CREATE DATABASE – creates a new database
  • ALTER DATABASE – modifies a database
  • CREATE TABLE – creates a new table
  • ALTER TABLE – modifies a table
  • DROP TABLE – deletes a table
  • CREATE INDEX – creates an index (search key)
  • DROP INDEX – deletes an index

SELECT

If we want to select the content of the columns named “First_Name” and “Last_Name” from the table above, we would use the following SELECT statement:

SELECT First_Name,Last_Name FROM Persons

If we want to select all the columns from the “Persons” table, we would use the following SELECT statement:

SELECT * FROM Persons

SELECT DISTINCT Statement

In a table, some of the columns may contain duplicate values. This is not a problem, however, sometimes you will want to list only the different (distinct) values in a table.

The DISTINCT keyword can be used to return only distinct (different) values.

SELECT DISTINCT column_name(s)
 FROM table_name

For example, if we would like to see the distinct values from the column named “City” from the table above, we would use the following SELECT statement:

SELECT DISTINCT City FROM Persons

The result-set will look like this:

City
Corby
Ohio

The WHERE clause is used to extract only those records that fulfill a specified criterion.

For example, the

SELECT * FROM Persons
 WHERE City='Corby'

will return a result-set looking like this:

P_Id First_Name Last_Name Address City
1 Jill McGuire Oakley Hay 9 Corby
2 Bob Builder The Main Road 23 Corby

Numeric values do not need to be included in quotes, but string values do. Use a single quote or a double quote.

Double quote require an extra setting in SQL to be turned on:

When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks. When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers

Operators Allowed in the WHERE Clause

With the WHERE clause, the following operators can be used:

Operator Description
= Equal
<> Not equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
BETWEEN Between an inclusive range
LIKE Search for a pattern
IN If you know the exact value you want to return for at least one of the columns

If you would like to combine multiple conditions, you can use the logical operators AND and OR. You would use them separately or together, but remember, enclose conditions into open brackets.

SELECT * FROM Persons WHERE
 First_Name='Bob'
 AND (Last_Name='Builder' OR Last_Name='McGuire')

The result-set will look like this:

P_Id First_Name Last_Name Address City
2 Bob Builder The Main Road 23 Corby

The ORDER BY Keyword

The ORDER BY keyword is used to sort the result-set by a specified column.

The ORDER BY keyword sort the records in ascending order (ASC) by default.

If you want to sort the records in a descending order, you can use the DESC keyword.

SELECT column_name(s)
FROM table_name
ORDER BY column_name(s) ASC|DESC

For example, if we want to select all the persons from the table above, sorted by their last name, we use the following SELECT statement:

SELECT * FROM Persons
ORDER BY First_Name

The result-set will look like this:

P_Id First_Name Last_Name Address City
1 Jill McGuire Oakley Hay 9 Corby
4 Nilsen Tom Vingvn 23 Ohio
3 Peter Pan Neverland Ohio
2 Bob Builder The Main Road 23 Corby

INSERT STATEMENT

The INSERT INTO statement is used to insert new records in a table.

It is possible to write the INSERT INTO statement in two forms.

The first form doesn’t specify the column names where the data will be inserted, only their values:

INSERT INTO table_name

VALUES (value1, value2, value3,…)

The second form specifies both the column names and the values to be inserted:

INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)

We have the following “Persons” table:

P_Id First_Name Last_Name Address City
1 Jill McGuire Oakley Hay 9 Corby
2 Bob Builder The Main Road 23 Corby
3 Peter Pan Neverland Ohio

Now we want to insert a new row in the “Persons” table.

We use the following SQL statement:

INSERT INTO Persons
VALUES (4,'John', 'Doe', 'Bat Street 2', 'Desborough')

The “Persons” table will now look like this:

P_Id First_Name Last_Name Address City
1 Jill McGuire Oakley Hay 9 Corby
2 Bob Builder The Main Road 23 Corby
3 Peter Pan Neverland Ohio
4 John Doe Bat Street 2 Desborough

Insert Data Only in Specified Columns

It is also possible to only add data in specific columns.

The following SQL statement will add a new row, but only add data in the “P_Id”, “First_Name” and the “Last_Name” columns:

INSERT INTO Persons (P_Id, First_Name, Last_Name)
VALUES (5, 'Jacob', 'Powell')

The “Persons” table will now look like this:

P_Id First_Name Last_Name Address City
1 Jill McGuire Oakley Hay 9 Corby
2 Bob Builder The Main Road 23 Corby
3 Peter Pan Neverland Ohio
4 John Doe Bat Street 2 Desborough
5 Jacob Powell

The UPDATE Statement

The UPDATE statement is used to update existing records in a table.

UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value

Note: Notice the WHERE clause in the UPDATE syntax. The WHERE clause
specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated!

Now we want to update the person “Tjessem, Jakob” in the “Persons” table.

We use the following SQL statement:

UPDATE Persons
SET Address='Bat Street 67', City='Corby'
WHERE First_Name='Jacob' AND Last_Name='Powell'

The “Persons” table will now look like this:

P_Id First_Name Last_Name Address City
1 Jill McGuire Oakley Hay 9 Corby
2 Bob Builder The Main Road 23 Corby
3 Peter Pan Neverland Ohio
4 John Doe Bat Street 2 Desborough
5 Jacob Powell Bat Street 67 Corby

SQL UPDATE Warning

Be careful when updating records. If we had omitted the WHERE clause in the example above, like this:

UPDATE Persons
SET Address='The bad turn', City='Corby'

The “Persons” table would have looked like this:

P_Id First_Name Last_Name Address City
1 Jill McGuire The bad turn Corby
2 Bob Builder The bad turn Corby
3 Peter Pan The bad turn Corby
4 John Doe The bad turn Corby
5 Jacob Powell The bad turn Corby

The DELETE Statement

The DELETE statement is used to delete rows in a table.

SQL DELETE Syntax

DELETE FROM table_name

WHERE some_column=some_value

Note: Notice the WHERE clause in the DELETE syntax. The WHERE clause specifies which record or records that should be deleted.
If you omit the WHERE clause, all records will be deleted!


SQL DELETE Example

The “Persons” table:

P_Id First_Name Last_Name Address City
1 Jill McGuire Oakley Hay 9 Corby
2 Bob Builder The Main Road 23 Corby
3 Peter Pan Neverland Ohio
4 John Doe Bat Street 2 Desborough
5 Jacob Powell Bat Street 67 Corby

Now we want to delete the person “Jacob Powell” in the “Persons” table.

We use the following SQL statement:

DELETE FROM Persons
WHERE First_Name='Jacob' AND Last_Name='Powell'

The “Persons” table will now look like this:

P_Id First_Name Last_Name Address City
1 Jill McGuire Oakley Hay 9 Corby
2 Bob Builder The Main Road 23 Corby
3 Peter Pan Neverland Ohio
4 John Doe Bat Street 2 Desborough

Delete All Rows

It is possible to delete all rows in a table without deleting the table. This means that the table structure, attributes, and indexes will be intact:

DELETE FROM table_name
or
DELETE * FROM table_name
or
TRUNCATE TABLE table_name

Note: Be very careful when deleting records. You cannot undo this statement!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s