SQL Commands Tutorial

cortegesmashInternet και Εφαρμογές Web

10 Νοε 2013 (πριν από 3 χρόνια και 11 μήνες)

126 εμφανίσεις


1

SQL
Commands
Tutorial

Source: http://www.quackit.com/sql/tutorial/

Learning SQL can be very rewarding. Once you have a basic understanding of SQL, you can start to develop
more advanced websites, and you can (hopefully) charge more money for doing so!

This

free SQL tutorial covers the SQL (Structure Query Language) syntax and its various SQL commands. It
assumes a basic knowledge of databases and database design.

Although not essential, you will benefit most from practicing the examples on your own computer

-

this would
require a database program such as MySQL, SQL Server, or Oracle to be configured on your machine.

If you don't have a database program installed on your machine, don't despair, you will still learn what SQL is
and its syntax. Then, when you d
o find yourself a database system, you will know just what to do!

Table of Contents

This SQL tutorial consists of the following lessons:

1.

Introduction

2.

SQL Syntax

3.

SQL Select

4.

SQL Where

5.

SQL Order By

6.

SQL Top

7.

SQL Distinct

8.

SQL In

9.

SQL Alias

10.

SQL Join

11.

SQL Inner Join

12.

SQL Outer Join

13.

SQL Insert

14.

SQL Update

15.

SQL Delete

16.

SQL Functions

17.

SQL Count

18.

SQL Create

19.

SQL Create Database

20.

SQL Create Table

21.

SQL Create Index

22.

SQL Alter Table

23.

SQL Summary



1.
SQL Introduction

About SQL

SQL

stands for Structured Query Language.

SQL is a langu
age that enables you to work with a database. Using SQL, you can insert records, update records,
and delete records. You can also create new database objects such as databases and tables. And you can drop
(delete) them.

More advanced features include creat
ing stored procedures (self contained scripts), views (pre
-
made queries),
and setting permissions on database objects (such as tables, stored procedures, and views).

Although SQL is an ANSI (American National Standards Institute) standard, there are many d
ifferent versions of
SQL. Different database vendors have their own variations of the language.

Having said this, to be in compliance with the ANSI standard, they need to at least support the major
commands such as DELETE, INSERT, UPDATE, WHERE etc. Also,
you will find that many vendors have their own
extensions to the language
-

features that are only supported in their database system.

Furthermore, transact
-
SQL is an extension to the ANSI standard and provides extra functionality.


2

Using SQL

If you need to

build a website with a database providing the content, you will generally need the following:



A server side scripting language (i.e. ColdFusion, PHP, ASP/.NET)



A database query language (i.e. SQL)



A client side markup language and style sheets (i.e. HTML/
CSS)

Although SQL can be quite involved, you can achieve a lot with a handful of SQL statements. When using SQL
on a website, you will often find yourself either selecting a record, inserting a record, updating a record, or
deleting a record. Fortunately,
SQL has commands for performing each of these actions.


2.
SQL Syntax

The SQL syntax is quite an easy one to grasp. Most of the actions you need to perform are done with a
SQL
statement
.

Create the following table called
Individual
.

IndividualId

FirstName

LastName

UserName

1

Fred

Flinstone

freddo

2

Homer

Simpson

homey

3

Homer

Brown

notsofamous

4

Ozzy

Ozzbourne

sabbath

5

Homer

Gain

noplacelike

Generally, a SQL statement begins by stating what to
do

(for example,
"SELECT"
), then states which
object

to
do it to (for example, using the
"FROM"

clause).

SELECT * FROM Individual


It may also have a condition added to the end (for example, with a
WHERE

clause).

SELECT * FROM Individual

WHERE FirstName = 'Homer'


SQL is not case sensitive
-

the above examples
could just have easily used all lowercase or all uppercase.
Different programmers have their own preferences. For readability purposes, many SQL programmers prefer to
use uppercase for SQL commands and lowercase for everything else.

The SQL syntax allows
you to include line breaks at logical points without it breaking the statement. For
example, the above example could have been written all on one line
-

or across 4 lines.

Also, some database systems require that you use a semicolon at the end of each SQL
statement (although this
tutorial doesn't use the semicolon).

DML & DDL

SQL is divided into two main categories; Data Manipulation Language (DML), and Data Definition Language
(DDL). An explanation follows.


3

Data Manipulation Language (DML)

DML enables you
to work with the data that goes into the database. DML is used to insert, select, update, and
delete records in the database. Many of your SQL statements will begin with one of the following commands:



SELECT

-

Retrieves data from the database



INSERT

-

Inse
rts new data into the database



UPDATE

-

Updates existing data in the database



DELETE

-

Deletes existing data from the database

Data Definition Language (DDL)

You may also occasionally need to create or drop a table or other
database

object. SQL enables you

to do this
programmatically

using DDL.

Examples of DDL commands:



CREATE DATABASE

-

Creates a new database



ALTER DATABASE

-

Modifies the database



DROP DATABASE

-

Drops (deletes) a database



CREATE TABLE

-

Creates a new table



ALTER TABLE

-

Modifies the table



DROP TABLE

-

Drops (deletes) a table

These are just some of the object classes that can be defined using DDL. As you can probably guess, the syntax
is generally the same for any object, although, each object will have properties specific to that object cl
ass.

As you can see, the SQL syntax is quite simple. It is also very powerful syntax
-

you can do a lot of damage
with one line of code!


3.
SQL Select

The SELECT statement is probably the most commonly used in SQL. It simply retrieves data from the datab
ase.

Let’s

have a look at a simple SELECT statement:

SELECT * FROM Individual


This SQL SELECT statement is attempting to retrieve all columns from a table called
Individual
.

How do we know it is trying to select
all

columns? Because it is using an asteris
k (*). This is a quick way of
selecting all columns
-

it's much easier than writing out the names of all columns (especially if there are a lot of
columns).

Of course, this SQL SELECT statement assumes that there is a table called
Individual
. If there wasn
't,
an error would be generated.

Let’s
have a look at the table the statement is trying to select data from:

IndividualId

FirstName

LastName

UserName

1

Fred

Flinstone

freddo

2

Homer

Simpson

homey

3

Homer

Brown

notsofamous

4

Ozzy

Ozzbourne

sabbath

5

Ho
mer

Gain

noplacelike


4

Because our select statement asks to display all columns and all records, we would see the following:

IndividualId

FirstName

LastName

UserName

1

Fred

Flinstone

freddo

2

Homer

Simpson

homey

3

Homer

Brown

notsofamous

4

Ozzy

Ozzbourn
e

sabbath

5

Homer

Gain

noplacelike

Select from Multiple Tables

You can select from more than one table at a time. To do this, simply separate each table with a comma. You
should also qualify any references to columns by placing the table name in front, s
eparated by a dot.

We have another table called
Occupation
, which contains the individual's occupation.
Now let’s create this table.

OccupationId

IndividualId

JobTitle

1

1

Engineer

2

2

Accountant

3

3

Cleaner

4

4

Attorney

5

5

Sales Executive

SQL state
ment

We will select from both the
Individual

table and the
Occupation

table. We will qualify any column names by
prefixing them with its table's name and a dot.

SELECT * FROM Individual
, Occupation

WHERE
Individual.
FirstName = 'Homer'


Result


IndividualId

FirstName

LastName

UserName

OccupationId

IndividualId

JobTitle

1

Fred

Flinstone

freddo

1

1

Engineer

2

Homer

Simpson

homey

2

2

Accountant

3

Homer

Brown

notsofamous

3

3

Cleaner

4

Ozzy

Ozzbourne

sabbath

4

4

Attorney

5

Homer

Gain

noplacelike

5

5

Sales Ex
ecutive

Displaying Less Columns

If you don't need every column to be displayed you can single out just the columns you're interested in. It's
good programming practice to do this
-

the more columns your program has to return, the more it will impact
its p
erformance.

To only display those columns you're interested in, simply replace the asterisk (*) with a comma separated list
of the column names.

SQL statement

SELECT
IndividualId, LastName, UserName

FROM Individual

WHERE FirstName = 'Homer'



5

Result

Individ
ualId

LastName

UserName

2

Simpson

homey

3

Brown

notsofamous

5

Gain

noplacelike



4.
SQL Where

In the previous lesson, we used a SQL SELECT statement to retrieve all records from a database table. This is
fine if we want to see every record, but what i
f we were only interested in
some

records? For example, what if
we were only interested in individuals whose first name is "Homer"?

We could use the WHERE clause.

Using the WHERE clause, you can filter out only those records that satisfy a given condition.

SQL WHERE Syntax

SELECT * FROM table_name

WHERE column_name = 'criteria'


Example

SQL WHERE Statement

SELECT * FROM Individual

WHERE FirstName = 'Homer'


Source Table


IndividualId

FirstName

LastName

UserName

1

Fred

Flinstone

freddo

2

Homer

Simpson

home
y

3

Homer

Brown

notsofamous

4

Ozzy

Ozzbourne

sabbath

5

Homer

Gain

noplacelike

Result

Given there are 3 people with the first name of "Homer", the results will look like this:

IndividualId

FirstName

LastName

UserName

2

Homer

Simpson

homey

3

Homer

Brow
n

notsofamous

5

Homer

Gain

noplacelike

Multiple Conditions

You can filter records based on more than one condition using operators. Two common operators are the AND
and OR operators.


6

AND Operator

The AND operator filters the query to only those records t
hat satisfy both the first condition
and

the second
condition.

SELECT * FROM Individual

WHERE FirstName = 'Homer'

AND LastName = 'Brown'


Result


IndividualId

FirstName

LastName

UserName

3

Homer

Brown

notsofamous

OR Operator

The OR operator filters the q
uery to only those records that satisfy either one
or

the other condition.

SELECT * FROM Individual

WHERE FirstName = 'Homer'

OR LastName = 'Ozzbourne'


Result


IndividualId

FirstName

LastName

UserName

2

Homer

Simpson

homey

3

Homer

Brown

notsofamous

5

H
omer

Gain

noplacelike

4

Ozzy

Ozzbourne

sabbath


5.
SQL Order By

Using a SQL SELECT statement can
retrieve

many hundreds or even thousands of records. In some cases you
might find it useful to sort the records by a given column. For example, when selecti
ng records from the
Individual table, you might like to sort them by the
LastName

column.

SQL statement

SELECT * FROM Individual

ORDER BY LastName



Source Table


IndividualId

FirstName

LastName

UserName

1

Fred

Flinstone

freddo

2

Homer

Simpson

homey

3

H
omer

Brown

notsofamous

4

Ozzy

Ozzbourne

sabbath

5

Homer

Gain

noplacelike


7


Result


IndividualId

FirstName

LastName

UserName

3

Homer

Brown

notsofamous

1

Fred

Flinstone

freddo

5

Homer

Gain

noplacelike

4

Ozzy

Ozzbourne

sabbath

2

Homer

Simpson

homey


D
escending Order

By default, ORDER BY sorts the column in ascending order
-

that is, from lowest values to highest values. You
could also explicitly state this using the
ASC

keyword, but it's not necessary.

If you want highest values to appear first, you ca
n use the DESC keyword.

SQL statement

SELECT * FROM Individual

ORDER BY LastName
DESC


Result


IndividualId

FirstName

LastName

UserName

2

Homer

Simpson

homey

4

Ozzy

Ozzbourne

sabbath

5

Homer

Gain

noplacelike

1

Fred

Flinstone

freddo

3

Homer

Brown

notso
famous


Sorting By Multiple Columns

You can sort by multiple columns by stating each column in the ORDER BY clause, separating each column
name with a comma. SQL will first order the results by the first column, then the second, and so on for as many
colu
mns that are included in the ORDER BY clause.

SQL statement

SELECT * FROM Individual

ORDER BY FirstName, LastName


Result


IndividualId

FirstName

LastName

UserName

1

Fred

Flinstone

freddo

3

Homer

Brown

notsofamous

5

Homer

Gain

noplacelike

2

Homer

Simps
on

homey

4

Ozzy

Ozzbourne

sabbath







8

6.
SQL Top

In the
preceding

lessons on the SELECT statement, the examples have returned
all

records that have matched
our SELECT criteria. This is great if you want to look at every record, but, what if you only wa
nt to look at the
first few records?

Sounds like you need the SQL TOP clause.

The TOP clause allows us to specify how many rows to return. This can be useful on very large tables when
there are thousands of records. Returning thousands of records can impac
t on performance, and if you are
working with a production database, this could have an adverse impact on the users.

Note:

The SQL TOP clause is Transact
-
SQL, and not part of ANSI SQL. Therefore, depending on your database
system, you may not be able to us
e this clause.

SQL statement

SELECT
TOP 3

* FROM Individual


Source Table


IndividualId

FirstName

LastName

UserName

1

Fred

Flinstone

freddo

2

Homer

Simpson

homey

3

Homer

Brown

notsofamous

4

Ozzy

Ozzbourne

sabbath

5

Homer

Gain

noplacelike

Result


Indi
vidualId

FirstName

LastName

UserName

1

Fred

Flinstone

freddo

2

Homer

Simpson

homey

3

Homer

Brown

notsofamous

Specifying a Percentage

You have the option of specifying a percentage of the result set instead of an absolute value. You do this with
the PER
CENT keyword.

SQL statement

SELECT
TOP 40 PERCENT

* FROM Individual


Result


IndividualId

FirstName

LastName

UserName

1

Fred

Flinstone

freddo

2

Homer

Simpson

homey

SQL TOP and the ORDER BY clause

If you are using the TOP clause along with the ORDER BY c
lause, the TOP clause is applied to the ordered result
set.

Therefore, if we add an ORDER BY to the above query, we end up with something like this:


9

SQL statement

SELECT
TOP 40 PERCENT

* FROM Individual

ORDER BY LastName DESC


Result


IndividualId

FirstNa
me

LastName

UserName

2

Homer

Simpson

homey

4

Ozzy

Ozzbourne

sabbath


7.
SQL Distinct

Once a table starts getting a lot of data in it, some columns will contain duplicate values. For example, many
Individuals share first names and surnames. Most of the
time this isn't a problem. But sometimes you will want
to find out how many unique values there are in a table. To do this you can use the DISTINCT keyword.

SQL statement

SELECT
DISTINCT(FirstName)

FROM Individual


Source Table


IndividualId

FirstName

Last
Name

UserName

1

Fred

Flinstone

freddo

2

Homer

Simpson

homey

3

Homer

Brown

notsofamous

4

Ozzy

Ozzbourne

sabbath

5

Homer

Gain

noplacelike

Result

Using the DISTINCT keyword, all customers with a name of "Homer" are counted as one.

FirstName

Fred

Homer

Ozzy



8.
SQL In

The SQL IN operator assists you in providing multiple values in your WHERE clause. This can provide very
useful when you need to compare your value to a list of values. Often this list could be the result of a query
from another table.

SQL Syntax

SELECT * FROM table_name

WHERE column_name
IN (value1,value2,...)





10


Example

SQL statement

SELECT * FROM Individual

WHERE LastName IN ('Simpson','Ozzbourne','Flinstone')


Source Table


IndividualId

FirstName

LastName

UserName

1

Fred

Flinstone

freddo

2

Homer

Simpson

homey

3

Homer

Brown

notsofamous

4

Ozzy

Ozzbourne

sabbath

5

Homer

Gain

noplacelike

Result


IndividualId

FirstName

LastName

UserName

1

Fred

Flinstone

freddo

2

Homer

Simpson

homey

4

Ozzy

Ozzbourne

sabbath

You might have notice
d that this returns the same result as the following SQL statement:

SELECT * FROM Individual

WHERE LastName = 'Simpson'

OR LastName = 'Ozzbourne'

OR LastName = 'Flinstone'


Yes, we could just have easily used that statement but the SQL IN statement is more

concise.

SQL IN and Subqueries

Now, where the SQL IN operator becomes really useful is when you need to compare a value against the result
of another query.

For example,
let’s

say we have a
nother table called
Publisher
, shown below. Now let’s create the
table.

This table contains users who are allowed to contribute to the website via an administration console. All users in
the Publisher table are also in the Individual table, but not all users in the Individual table are in the Publisher
table.

Source Ta
bles

Individual Table

IndividualId

FirstName

LastName

UserName

1

Fred

Flinstone

freddo

2

Homer

Simpson

homey

3

Homer

Brown

notsofamous

4

Ozzy

Ozzbourne

sabbath

5

Homer

Gain

noplacelike


11

Publisher Table

IndividualId

AccessLevel

1

Administrator

2

Cont
ributor

3

Contributor

4

Contributor

Our task is to return a list of usernames from all publishers who have an access level of "Contributor".

You may notice that the usernames are in the Individual table but the access level is stored in the Publisher
ta
ble. Also, there could potentially be many contributors. This is a good candidate for the SQL IN operator!

SQL statement

SELECT UserName FROM Individual

WHERE IndividualId IN

(SELECT IndividualId

FROM Publisher

WHERE AccessLevel = 'Contributor')


Result


U
serName

homey

notsofamous

sabbath

In this example there aren't many records in the Publisher table, but imagine if there were thousands
-

the IN
statement is great for this sort of thing.


9.
SQL Alias

In SQL, an alias is a name that you give a table.

This can make it easier to work with table names
-

especially
when they are long. You could name the alias anything, but usually you'd make it short.

You may be thinking "a table already has a name, why give it another one?". Well, there are some good
re
asons for creating an alias. The main reasons are:



Queries can sometimes get very long. Aliases can make your query easier to read.



You may find yourself referencing the same table name over and over again
-

this will occur if you're
working with multiple
tables and you need to refer to columns from those tables. It can be annoying to
have to write the whole name all the time
-

especially if it's a long one.



You may need to work with multiple instances of the same table, for example, a self join. If you're
not
familiar with joins, they are covered later in this tutorial.

As mentioned, an alias could be anything. For example, if you have a table called
Individual

you could give it an
alias of
i
. Another table called
IndividualProductPurchase

could have an ali
as of, say,
ipp


Alias Syntax

SELECT * FROM table_name
AS alias_name





12


Example SQL Statement

SELECT
o
.JobTitle FROM Individual
AS i
, Occupation
AS o

WHERE
i.
FirstName = 'Homer'

ORDER BY
o
.JobTitle




10.
SQL Join

The SQL JOIN refers to using the JOIN ke
yword in a SQL statement in order to query data from two tables.

When you perform a SQL join, you specify one column from each table to join on. These two columns contain
data that is shared across both tables.

You can use multiple joins in the same SQL st
atement to query data from as many tables as you like.

Join Types

Depending on your requirements, you can do an "inner" join or an "outer" join. These are different in a subtle
way



INNER JOIN: This will only return rows when there is at least one row in bo
th tables that match the join
condition.



LEFT OUTER JOIN (or LEFT JOIN): This will return rows that have data in the left table (left of the JOIN
keyword), even if there's no matching rows in the right table.



RIGHT OUTER JOIN (or RIGHT JOIN): This will ret
urn rows that have data in the right table (right of the
JOIN keyword), even if there's no matching rows in the left table.



FULL OUTER JOIN (or FULL JOIN): This will return all rows, as long as there's matching data in one of
the tables.

Join Syntax


Inner

Join:


SELECT * FROM table_name1

INNER

JOIN table_name2

ON table_name1.column_name = table_name2.column_name


Left Join:


SELECT * FROM table_name1

LEFT

JOIN table_name2

ON table_name1.column_name = table_name2.column_name


Right Join:


SELECT * FROM tabl
e_name1

RIGHT

JOIN table_name2

ON table_name1.column_name = table_name2.column_name


Full Join:


SELECT * FROM table_name1

FULL

JOIN table_name2

ON table_name1.column_name = table_name2.column_name


Example Inner Join Statement

SELECT * FROM Individual

INN
ER JOIN Publisher

ON Individual.IndividualId = Publisher.IndividualId



13

Note:

We could use table aliases instead of the full table name. This will keep our statement shorter. For
example:

SELECT * FROM Individual
AS Ind

INNER JOIN Publisher
AS Pub

ON
Ind
.In
dividualId =
Pub
.IndividualId


The next few lessons cover each type of join and show examples of usage.


11.
SQL Inner Join

As discussed in the previous lesson, you should use the SQL INNER JOIN when you only want to return records
where there is at least

one row in both tables that match the join condition.

Example SQL statement

SELECT * FROM Individual

INNER JOIN Publisher

ON Individual.IndividualId = Publisher.IndividualId

WHERE Individual.IndividualId = '2'


Source Tables

Left Table

IndividualId

FirstN
ame

LastName

UserName

1

Fred

Flinstone

freddo

2

Homer

Simpson

homey

3

Homer

Brown

notsofamous

4

Ozzy

Ozzbourne

sabbath

5

Homer

Gain

noplacelike

Right Table

IndividualId

AccessLevel

1

Administrator

2

Contributor

3

Contributor

4

Contributor

10

Adm
inistrator

Result


IndividualId

FirstName

LastName

UserName

IndividualId

AccessLevel

2

Homer

Simpson

homey

2

Contributor


12.
SQL Outer Join

This lesson covers both the
left outer join
, the
right outer join
, and the
full outer join
, and explains the
di
fferences between them. There are some occasions where you would need to use a left outer join or a right
outer join, and others where you would need a full outer join. The join type you use will depend on the situation
and what data you need to return.


14

Le
ft Outer Join

Use this when you only want to return rows that have matching data in the left table, even if there's no
matching rows in the right table.

Example SQL statement

SELECT * FROM Individual AS Ind

LEFT JOIN Publisher AS Pub

ON Ind.IndividualId =
Pub.IndividualId


Source Tables

Left Table

IndividualId

FirstName

LastName

UserName

1

Fred

Flinstone

freddo

2

Homer

Simpson

homey

3

Homer

Brown

notsofamous

4

Ozzy

Ozzbourne

sabbath

5

Homer

Gain

noplacelike

Right Table

IndividualId

AccessLevel

1

Admi
nistrator

2

Contributor

3

Contributor

4

Contributor

10

Administrator


Result


IndividualId

FirstName

LastName

UserName

IndividualId

AccessLevel

1

Fred

Flinstone

freddo

1

Administrator

2

Homer

Simpson

homey

2

Contributor

3

Homer

Brown

notsofamous

3

Contributor

4

Ozzy

Osbourne

sabbath

4

Contributor

5

Homer

Gain

noplacelike

NULL

NULL

Right Outer Join

Use this when you only want to return rows that have matching data in the right table, even if there's no
matching rows in the left table.

Example SQL
statement

SELECT * FROM Individual AS Ind

RIGHT JOIN Publisher AS Pub

ON Ind.IndividualId = Pub.IndividualId


Source Tables

Left Table


15

IndividualId

FirstName

LastName

UserName

1

Fred

Flinstone

freddo

2

Homer

Simpson

homey

3

Homer

Brown

notsofamous

4

Oz
zy

Ozzbourne

sabbath

5

Homer

Gain

noplacelike


Right Table

IndividualId

AccessLevel

1

Administrator

2

Contributor

3

Contributor

4

Contributor

10

Administrator



Result


IndividualId

FirstName

LastName

UserName

IndividualId

AccessLevel

1

Fred

Flins
tone

freddo

1

Administrator

2

Homer

Simpson

homey

2

Contributor

3

Homer

Brown

notsofamous

3

Contributor

4

Ozzy

Osbourne

sabbath

4

Contributor

NULL

NULL

NULL

NULL

10

Administrator

Full Outer Join

Use this when you want to all rows, even if there's no m
atching rows in the right table.

Example SQL statement

SELECT * FROM Individual AS Ind

FULL JOIN Publisher AS Pub

ON Ind.IndividualId = Pub.IndividualId



Source Tables

Left Table

IndividualId

FirstName

LastName

UserName

1

Fred

Flinstone

freddo

2

Homer

S
impson

homey

3

Homer

Brown

notsofamous

4

Ozzy

Ozzbourne

sabbath

5

Homer

Gain

noplacelike




16

Right Table

IndividualId

AccessLevel

1

Administrator

2

Contributor

3

Contributor

4

Contributor

10

Administrator

Result


IndividualId

FirstName

LastName

Use
rName

IndividualId

AccessLevel

1

Fred

Flinstone

freddo

1

Administrator

2

Homer

Simpson

homey

2

Contributor

3

Homer

Brown

notsofamous

3

Contributor

4

Ozzy

Osbourne

sabbath

4

Contributor

5

Homer

Gain

noplacelike

NULL

NULL

NULL

NULL

NULL

NULL

10

Adminis
trator


13.
SQL Insert

Up until now, this tutorial has covered the SELECT statement and variations on it. We are now about to learn a
new statement
-

the INSERT statement.

The SQL INSERT command allows you to insert a record into a table in your database
. As with the SELECT
syntax, the INSERT syntax is quite straight forward.

SQL statement

INSERT INTO Individual

VALUES ( '6', 'Benny', 'Hill', 'hillbenny' )


Source Table


IndividualId

FirstName

LastName

UserName

1

Fred

Flinstone

freddo

2

Homer

Simpson

ho
mey

3

Homer

Brown

notsofamous

4

Ozzy

Ozzbourne

sabbath

5

Homer

Gain

noplacelike

Result

Now if we do a SELECT on the Individual table, we can see the new record added to the bottom of the result
.

IndividualId

FirstName

LastName

UserName

1

Fred

Flinston
e

freddo

2

Homer

Simpson

homey

3

Homer

Brown

notsofamous

4

Ozzy

Ozzbourne

sabbath

5

Homer

Gain

noplacelike

6

Benny

Hill

hillbenny

See
-

nothing to it!


17

Now, it's important to note that the INSERT statement is used only when you want to add a
new

recor
d to the
table. If you want to update an existing record, use an UPDATE statement. The UPDATE command is described
in the next lesson.


14.
SQL Update

The SQL UPDATE statement allows you to update an existing record in the database.

The UPDATE command use
s a WHERE clause. If you don't use a WHERE clause, all rows will be updated. In fact,
the syntax for a basic UPDATE statement is very similar to a SELECT statement.

SQL statement

UPDATE Individual

SET UserName = 'funnyman'

WHERE IndividualId = '6'


Source
Table


IndividualId

FirstName

LastName

UserName

1

Fred

Flinstone

freddo

2

Homer

Simpson

homey

3

Homer

Brown

notsofamous

4

Ozzy

Ozzbourne

sabbath

5

Homer

Gain

noplacelike

6

Benny

Hill

hillbenny


Result

Now if we select this record, we can see the upd
ated value.

IndividualId

FirstName

LastName

UserName

6

Benny

Hill

funnyman


Updating Multiple Fields

To update multiple fields, separate each field assignment with a comma.

SQL statement

UPDATE Individual

SET UserName = 'getserious', FirstName = 'Onetree
'

WHERE IndividualId = '6'


Result


IndividualId

FirstName

LastName

UserName

6

Onetree

Hill

getserious

Next lesson covers the DELETE statement.



18

15.
SQL Delete

The SQL DELETE statement allows you to delete a record from the database.

The DELETE command
uses a WHERE clause. If you don't use a WHERE clause, all rows in the table will be
deleted. Again, as with the UPDATE statement, the syntax for a basic DELETE statement is similar to a SELECT
statement.

SQL statement

DELETE

FROM Individual

WHERE Individua
lId = '6'


Source Table


IndividualId

FirstName

LastName

UserName

1

Fred

Flinstone

freddo

2

Homer

Simpson

homey

3

Homer

Brown

notsofamous

4

Ozzy

Ozzbourne

sabbath

5

Homer

Gain

noplacelike

6

Benny

Hill

funnyman

Result

Now if we select all records fro
m the table, we see that record 6 has been deleted.

IndividualId

FirstName

LastName

UserName

1

Fred

Flinstone

freddo

2

Homer

Simpson

homey

3

Homer

Brown

notsofamous

4

Ozzy

Ozzbourne

sabbath

5

Homer

Gain

noplacelike


16.
SQL Functions

SQL has a numbe
r of functions to assist you in your database programming.

Functions are a self contained script/program built for a specific purpose. Generally, the value returned by a
function will depend on the context in which it is being used. Often, a SQL function w
ill be used within a query
and this is what provides it with it's context.

Transact
-
SQL provides 3 different types of functions:

Rowset Functions

These return an object that can be used in place of a table reference in a SQL statement

Aggregate
Functions

Perform a calculation on a set of values and return a single value. Aggregate functions can
be used in the following:



The select list of a SELECT statement



A COMPUTE or COMPUTE BY clause



A HAVING clause


19

Scalar Functions

These return a single value from a

single value. Scalar functions are categorized as follows:



Configuration Functions



Cursor Functions



Date and Time Functions



Mathematical Functions



Metadata Functions



Security Functions



String Functions



System Functions



System Statistical Functions



Text a
nd Image Functions

On top of these functions, different database vendors have their own built
-
in functions for their products. Also,
most products enable programmers to program their own User Defined Functions. For information on
proprietary functions, yo
u should cons
ult the vendor's documentation.


17.
SQL Count

A commonly used aggregate function in SQL is COUNT. COUNT returns the number of rows that match the
given criteria.

COUNT(*)

If we only want to see how many records are in a table (but not actual
ly view those records), we could use
COUNT(*). COUNT(*) returns everything
-

including null values and duplicates.

SQL statement

SELECT
COUNT(*)

FROM Individual


Source Table


IndividualId

FirstName

LastName

UserName

1

Fred

Flinstone

freddo

2

Homer

Simps
on

homey

3

Homer

Brown

notsofamous

4

Ozzy

Ozzbourne

sabbath

5

Homer

Gain

noplacelike

6

Bono



u2

Result


6

COUNT(
column name
)

If we want to see how many non
-
null values are in a given column, we use COUNT(
column name
) where
column name

is the name of

the column we want to test.

SQL statement

SELECT
COUNT(LastName)

FROM Individual



20

Source Table


IndividualId

FirstName

LastName

UserName

1

Fred

Flinstone

freddo

2

Homer

Simpson

homey

3

Homer

Brown

notsofamous

4

Ozzy

Ozzbourne

sabbath

5

Homer

Gain

no
placelike

6

Bono



u2

Result


5


Combining COUNT & DISTINCT

If we only want to see how many unique names are in the table, we could nest the DISTINCT inside a COUNT
function.

SQL statement

SELECT
COUNT(DISTINCT(FirstName))

FROM Individual


Result


4


1
8.
SQL Create

Most database systems provide a way for you to create database objects via a WYSIWYG interface. For
example, Microsoft's SQL Server has Enterprise Manager. The Enterprise Manager gives you a kind of graphical
representation of your database
system. You can browse through your
databases;

view the tables and their
contents etc.

Despite having a tool like Enterprise Manager to make these tasks easier, there can be good reasons for
wanting to perform some of these tasks
programmatically
. Possible

reasons could include:



Your application allows users to create objects on the fly.



You have multiple environments (for example development, staging, production). It's much easier, and
less error prone, to run a script against 3 environments than to open u
p Enterprise Manager and repeat
the steps in 3 environments
-

especially if you have lots of changes.



You don't have access to Enterprise Manager (or your database system's equivalent).

Fortunately, SQL has a number of CREATE commands that enable you to
pr
ogrammatically

create database
objects including the database, its tables and more.

Here are the CREATE commands supported by SQL Server:



CREATE ACTION



CREATE CACHE



CREATE CELL CALCULATION



CREATE CUBE



CREATE DATABASE


21



CREATE DEFAULT



CREATE FUNCTION



CREATE
INDEX



CREATE MEMBER



CREATE MINING MODEL



CREATE PROCEDURE



CREATE RULE



CREATE SCHEMA



CREATE SET



CREATE STATISTICS



CREATE TABLE



CREATE TRIGGER



CREATE UNIQUE CLUSTERED INDEX



CREATE VIEW

We're not going to cover all these here but, over the next few lessons, we
'll cover some of the most common
CREATE commands.


19.
SQL Create Database

You can create a database using the CREATE DATABASE command.

SQL syntax

CREATE DATABASE database_name


Example Code

This statement creates a database called "Payroll". Because no

arguments have been specified, the database
data files and transaction logs will be created automatically in the default location.

CREATE DATABASE Payroll


Adding Arguments

There are a number of optional arguments that you can supply with the CREATE DATAB
ASE command. You
should check your database system's documentation for the specific arguments supported and their usage, but
here's an example of supplying arguments when creating a database using Microsoft's SQL Server.

Example Code

In this example, we ar
e supplying the name and location of the database's data file and transaction log. We are
also specifying the initial size of these files (with the SIZE argument), the maximum size it can grow to (with
the MAXSIZE argument) and the growth increment of each

file (using the FILEGROWTH) argument.

USE master

GO

CREATE DATABASE Payroll

ON

(

NAME = Payroll_dat,


FILENAME = 'c:
\
program files
\
microsoft sql server
\
mssql
\
data
\
payrolldat.mdf',


SIZE = 20MB,


MAXSIZE = 70MB,


FILEGROWTH = 5MB )

LOG ON

(

NAME

= 'Payroll_log',


FILENAME = 'c:
\
program files
\
microsoft sql server
\
mssql
\
data
\
payroll.ldf',


22


SIZE = 10MB,


MAXSIZE = 40MB,


FILEGROWTH = 5MB )

GO




20.
SQL Create Table

You create a table using the CREATE TABLE command.

SQL syntax

CREATE TABLE
table_name

(

column_name_1 datatype,


column_name_2 datatype,

...

)


Example

CREATE TABLE Individual

(IndividualId int,


FirstName Varchar(255),


LastName Varchar(255),


UserName Char(10)

)


Result

This results in an empty table. You can now use an
INSERT

statement to add data to the table.

IndividualId

FirstName

LastName

UserName









Data Types

You'll notice we explicitly stated the data type in our CREATE TABLE statement. This is be
cause, when you
create a column, you need to tell the database what type of data it can hold.

The exact data types and how their expressed differs with each database system and vendor, but you'll find
that generally, there will be support for fixed length

strings (eg
char
), variable length strings (eg
varchar
),
date/time values (eg
datetime
), numbers and integers (eg,
bigint
,
int
,
smallint
,
tinyint
,
numeric
).

The following base data types are available in SQL Server 2000.

bigint

Binary

bit

char

cursor

da
tetime

Decimal

float

image

int

money

Nchar

ntext

nvarchar

real

smalldatetime

Smallint

smallmoney

text

timestamp

tinyint

Varbinary

Varchar

uniqueidentifier



You may need to consult your database system's documentation if you're unsure of which data typ
e to use or
how it is expressed in that system.


23

Next, we learn how to create an index for our table with the
CREATE INDEX

command.


21.
SQL Create Index

Indexes can be created against a table to make searches more efficient. A database index is similar to

an index
of a book
-

a book index allows you to find information without having to read through the whole book. A
database index enables the database application to find data quickly without having to scan the whole table.

Indexes can have a slight impact

on performance so you should only create indexes against tables and columns
that will be frequently searched against. For example, if users of your application often search against the
LastName field then that field is a great candidate for an index.

You
create an index using the CREATE INDEX command.

SQL syntax

CREATE INDEX index_name

ON table_name (column_name)


Example

CREATE INDEX IndividualIndex

ON Individual (LastName)



22.
SQL Alter Table

In an earlier lesson, we created a table with the CREATE TA
BLE command. In this lesson, we will modify the
table using the ALTER TABLE command.

Add a Column

SQL syntax

ALTER TABLE table_name

ADD column_name datatype


Example SQL Statement

ALTER TABLE Individual

ADD age int


Change the Datatype

SQL syntax

ALTER TAB
LE table_name

ALTER COLUMN column_name datatype


Example SQL Statement

ALTER TABLE Individual

ALTER COLUMN age numeric


Drop a Column

'Dropping' a column means removing or deleting that column.


24

SQL syntax

ALTER TABLE table_name

DROP COLUMN column_name


Exa
mple SQL Statement


ALTER TABLE Individual

DROP COLUMN age



23.
SQL Summary

This SQL tutorial has provided you with a quick and easy way to learn SQL.

We began by learning that SQL stands for Structured Query Language, and is an ANSI standard. We then
le
arned the basic SQL syntax, before continuing on to the SELECT statement
-

probably the most commonly
used statement.

We learned that there are various keywords and aggregate functions that can be included into SQL statements
such as WHERE, COUNT, DISTINCT

etc. These are all part of the DML (Data Manipulation Language).

Then, after covering the INSERT, UPDATE, and DELETE statements, we learned that there are various
commands for performing administration tasks against a database. For example, there are comm
ands for
creating database objects (CREATE DATABASE, CREATE TABLE etc), and there are commands for modifying (or
altering) database objects (ALTER DATABASE, ALTER TABLE etc). These commands are part of the DDL (Data
Definition Language).