SQL Introduction

Document Sample
SQL Introduction Powered By Docstoc
					                                                                                        Source: http://www.quackit.com/sql




              DATABASE DEVELOPMENT
                                            USING SQL

                                                                                 Table of Contents

SQL Introduction ...................................................................................................... 2
SQL Syntax ................................................................................................................... 3
SQL Select ..................................................................................................................... 5
SQL Where .................................................................................................................... 8
SQL Order By............................................................................................................. 10
SQL Top ........................................................................................................................ 12
SQL Distinct ............................................................................................................... 14
SQL In ........................................................................................................................... 15
SQL Alias ..................................................................................................................... 18
SQL Inner Join ......................................................................................................... 19
SQL Outer Join ......................................................................................................... 20
SQL Insert .................................................................................................................. 24
SQL Update ................................................................................................................ 26
SQL Delete .................................................................................................................. 27
SQL Functions........................................................................................................... 28
SQL Count ................................................................................................................... 29
SQL Create .................................................................................................................. 31
SQL Create Database ........................................................................................... 33
SQL Create Table .................................................................................................... 34
SQL Create Index ................................................................................................... 36
SQL Alter Table ........................................................................................................ 37
SQL Summary ........................................................................................................... 38
Microsoft Access Versus SQL Server .......................................................... 39




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




Compiled By: Belay S. --------                                                                                                     1
                                                     Source: http://www.quackit.com/sql




SQL Introduction

About SQL

SQL stands for Structured Query Language.


SQL is a language 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 creating 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 different 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.


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)



Compiled By: Belay S. --------                                                       2
                                                     Source: http://www.quackit.com/sql


       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.


The next lesson covers the SQL syntax basics.


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.


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.




Compiled By: Belay S. --------                                                       3
                                                     Source: http://www.quackit.com/sql


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.


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 - Inserts 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 datbase
object. SQL enables you to do this programatically 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



Compiled By: Belay S. --------                                                       4
                                                      Source: http://www.quackit.com/sql


       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 class.


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!


SQL Select


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


Lets 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 individuals.


How do we know it is trying to select all columns? Because it is using an
asterisk (*). 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
individuals. If there wasn't, an error would be generated.


Lets have a look at the table the statement is trying to select data from:


    IndividualId FirstName LastName UserName




Compiled By: Belay S. --------                                                        5
                                                            Source: http://www.quackit.com/sql



    1                  Fred         Flinstone    freddo

    2                  Homer        Simpson      homey

    3                  Homer        Brown        notsofamous

    4                  Ozzy         Ozzbourne sabbath

    5                  Homer        Gain         noplacelike


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           Ozzbourne 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, separated by a dot.


We have another table called "Occupation", which contains the individual's
occupation.


    OccupationId          IndividualId       JobTitle

    1                     1                  Engineer

    2                     2                  Accountant

    3                     3                  Cleaner

    4                     4                  Attorney

    5                     5                  Sales Executive



Compiled By: Belay S. --------                                                              6
                                                             Source: http://www.quackit.com/sql



SQL statement

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
                                                                                                  Executive


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
performance.


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'




Compiled By: Belay S. --------                                                               7
                                                        Source: http://www.quackit.com/sql



Result
IndividualId            LastName       UserName
2                       Simpson        homey
3                       Brown          notsofamous
5                       Gain           noplacelike


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 if 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



Compiled By: Belay S. --------                                                          8
                                                            Source: http://www.quackit.com/sql



    2                  Homer        Simpson      homey

    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          Brown        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.


AND Operator

The AND operator filters the query to only those records that 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




Compiled By: Belay S. --------                                                              9
                                                            Source: http://www.quackit.com/sql



OR Operator

The OR operator filters the query 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                 Homer          Gain         noplacelike
4                 Ozzy           Ozzbourne sabbath


SQL Order By


Using a SQL SELECT statement can retreive 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 selecting 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




Compiled By: Belay S. --------                                                             10
                                                           Source: http://www.quackit.com/sql



    3                  Homer        Brown       notsofamous

    4                  Ozzy         Ozzbourne sabbath

    5                  Homer        Gain        noplacelike


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


Descending 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 can 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       notsofamous



Compiled By: Belay S. --------                                                            11
                                                           Source: http://www.quackit.com/sql



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 columns
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          Simpson     homey
4                 Ozzy           Ozzbourne sabbath


SQL Top


In the preceeding 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 want 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 impact on performance, and if you are working with
a production database, this could have an adverse impact on the users.


Compiled By: Belay S. --------                                                            12
                                                          Source: http://www.quackit.com/sql


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 use
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
IndividualId 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 PERCENT keyword.


SQL statement
SELECT TOP 40 PERCENT * FROM Individual




Compiled By: Belay S. --------                                                           13
                                                      Source: http://www.quackit.com/sql



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 clause, 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:


SQL statement
SELECT TOP 40 PERCENT * FROM Individual
ORDER BY LastName DESC



Result
IndividualId FirstName LastName UserName
2                  Homer         Simpson     homey
4                  Ozzy          Ozzbourne sabbath


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.




Compiled By: Belay S. --------                                                       14
                                                      Source: http://www.quackit.com/sql



SQL statement
SELECT DISTINCT(FirstName) 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

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


FirstName
Fred
Homer
Ozzy


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,...)



Compiled By: Belay S. --------                                                       15
                                                              Source: http://www.quackit.com/sql




Example

SQL statement
SELECT * FROM Individual
WHERE LastName IN ('Simpson','Ozzbourne','Flinstone')



Source Table

    Id 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 noticed 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.




Compiled By: Belay S. --------                                                               16
                                                        Source: http://www.quackit.com/sql



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, lets say we have another table called "Publisher". 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 Tables

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


Publisher Table


    IndividualId                 AccessLevel

    1                            Administrator

    2                            Contributor

    3                            Contributor

    4                            Contributor


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



Compiled By: Belay S. --------                                                         17
                                                     Source: http://www.quackit.com/sql


You may notice that the usernames are in the Individual table but the access
level is stored in the Publisher table. 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
UserName
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.


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 reasons for creating an alias. The main reasons
are:


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




Compiled By: Belay S. --------                                                      18
                                                       Source: http://www.quackit.com/sql


       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 alias of, say, ipp


Alias Syntax
SELECT * FROM table_name AS alias_name



Example SQL Statement
SELECT o.JobTitle FROM Individual AS i, Occupation AS o
WHERE i.FirstName = 'Homer'
ORDER BY o.JobTitle



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'




Compiled By: Belay S. --------                                                        19
                                                           Source: http://www.quackit.com/sql



Source Tables

Left Table


    Id 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                                Administrator

    2                                Contributor

    3                                Contributor

    4                                Contributor

    10                               Administrator


Result
IndividualId FirstName LastName UserName IndividualId AccessLevel
2                Homer           Simpson   homey       2                 Contributor


Next lesson covers the SQL OUTER JOIN.


SQL Outer Join


This lesson covers both the left outer join, the right outer join, and the full
outer join, and explains the differences between them. There are some
occasions where you would need to use a left outer join or a right outer join,


Compiled By: Belay S. --------                                                            20
                                                           Source: http://www.quackit.com/sql


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.


Left 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


    Id 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                                Administrator

    2                                Contributor

    3                                Contributor

    4                                Contributor

    10                               Administrator



Compiled By: Belay S. --------                                                            21
                                                             Source: http://www.quackit.com/sql



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


    Id 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



Compiled By: Belay S. --------                                                              22
                                                             Source: http://www.quackit.com/sql



    1                                 Administrator

    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
NULL             NULL            NULL        NULL       10                   Administrator


Full Outer Join

Use this when you want to all rows, even if there's no matching 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


    Id FirstName             LastName          UserName

    1    Fred                Flinstone         freddo

    2    Homer               Simpson           homey




Compiled By: Belay S. --------                                                              23
                                                              Source: http://www.quackit.com/sql



    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                                  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
NULL             NULL            NULL         NULL        10                  Administrator


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.



Compiled By: Belay S. --------                                                               24
                                                                Source: http://www.quackit.com/sql



SQL statement
INSERT INTO Individual
VALUES ( '6', 'Benny', 'Hill', 'hillbenny' )



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

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


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


See - nothing to it!


Now, it's important to note that the INSERT statement is used only when you
want to add a new record to the table. If you want to update an existing
record, use an UPDATE statement. The UPDATE command is described in the
next lesson.




Compiled By: Belay S. --------                                                                 25
                                                           Source: http://www.quackit.com/sql



SQL Update


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


The UPDATE command uses 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          funnyman


Result

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


IndividualId FirstName LastName UserName
6                  Benny         Hill           funnyman


Updating Multiple Fields


Compiled By: Belay S. --------                                                            26
                                                             Source: http://www.quackit.com/sql


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.


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 IndividualId = '6'



Source Table

    IndividualId FirstName LastName UserName

    1                  Fred         Flinstone     freddo

    2                  Homer        Simpson       homey

    3                  Homer        Brown         notsofamous



Compiled By: Belay S. --------                                                              27
                                                            Source: http://www.quackit.com/sql



    4                  Ozzy         Ozzbourne sabbath

    5                  Homer        Gain         noplacelike

    6                  Benny        Hill         funnyman


Result

Now if we select all records from 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


SQL Functions


SQL has a number 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 will be used within a query and
this is what provides it with it's context.


Transact-SQL provides 3 different types of functions:


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

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




Compiled By: Belay S. --------                                                             28
                                                           Source: http://www.quackit.com/sql




                           The select list of a SELECT statement
                           A COMPUTE or COMPUTE BY clause
                           A HAVING clause


Scalar              These return a single value from a single value. Scalar
Functions           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 and 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, you should consult the vendor's documentation.


In the next lesson you will learn about one of the more commonly used SQL
functions - the COUNT function.


SQL Count


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




Compiled By: Belay S. --------                                                            29
                                                      Source: http://www.quackit.com/sql



COUNT(*)

If we only want to see how many records are in a table (but not actually 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     Simpson     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




Compiled By: Belay S. --------                                                       30
                                                       Source: http://www.quackit.com/sql



Source Table

    Id FirstName             LastName    UserName

    1    Fred                Flinstone   freddo

    2    Homer               Simpson     homey

    3    Homer               Brown       notsofamous

    4    Ozzy                Ozzbourne   sabbath

    5    Homer               Gain        noplacelike

    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


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.


Compiled By: Belay S. --------                                                        31
                                                      Source: http://www.quackit.com/sql


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
programatically. 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 up 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
programatically 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
       CREATE DEFAULT
       CREATE FUNCTION
       CREATE INDEX
       CREATE MEMBER
       CREATE MINING MODEL
       CREATE PROCEDURE
       CREATE RULE
       CREATE SCHEMA
       CREATE SET



Compiled By: Belay S. --------                                                       32
                                                    Source: http://www.quackit.com/sql


       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.


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 DATABASE 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.




Compiled By: Belay S. --------                                                     33
                                                      Source: http://www.quackit.com/sql



Example Code

In this example, we are 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',
   SIZE = 10MB,
   MAXSIZE = 40MB,
   FILEGROWTH = 5MB )
GO


Next up is the CREATE TABLE command.


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,



Compiled By: Belay S. --------                                                       34
                                                     Source: http://www.quackit.com/sql


...
)



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 because, 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




Compiled By: Belay S. --------                                                      35
                                                       Source: http://www.quackit.com/sql



    datetime            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 type to use or how it is expressed in that system.


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


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



Compiled By: Belay S. --------                                                        36
                                                 Source: http://www.quackit.com/sql


ON Individual (LastName)



SQL Alter Table


In an earlier lesson, we created a table with the CREATE TABLE 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 TABLE 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.




Compiled By: Belay S. --------                                                  37
                                                           Source: http://www.quackit.com/sql



SQL syntax
ALTER TABLE table_name
DROP COLUMN column_name



Example SQL Statement

ALTER TABLE Individual
DROP COLUMN age



SQL Summary

Congratulations - you've reached the end!


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 learned 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 commands 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).




Compiled By: Belay S. --------                                                            38
                                                     Source: http://www.quackit.com/sql



Microsoft Access Versus SQL Server


As we near the end of this tutorial, you may be thinking "Microsoft Access
seems to have some great features, why would I want to use any other
database package?". In particular, if you've heard about SQL Server, you
may be wondering what the difference is between Microsoft Access and SQL
Server.


Now that you've learned how to use MS Access, there are some very good
reasons why you wouldn't use it in some cases. Here's a rundown on
Microsoft Access versus SQL Server.


MS Access

Access is more suited for desktop use with a small number of users accessing
it simultaneously. One reason you might choose to use Access over SQL
Server is for compatibility/sharing. You might need to email someone a copy
of your database. People are more likely to have Access on their desktop
computer than SQL Server. You'll generally only find SQL Server on
developers' computers/servers or on production server machines.


Another reason you might use Access instead of SQL Server is money. You
might already have installed Access as part of the Microsoft Office suite.
Purchasing SQL Server would be an extra expense that may not be necessary
- depending on your situation. SQL Server can also be quite expensive.


MS SQL Server

SQL Server is a more robust database management system. SQL Server was
designed to have many hundreds, or even thousands of users accessing it at
any point in time. Microsoft Access on the other hand, doesn't handle this
type of load very well.



Compiled By: Belay S. --------                                                      39
                                                    Source: http://www.quackit.com/sql


This makes SQL Server perfectly suited for database driven websites. You
should never use Access for a database driven website - unless it has a very
small amount of traffic (like you and a few of your friends). Even then, you
may find yourself getting errors due to multiple users trying to access the
database at the same time.


SQL Server also contains some advanced database administration tools that
enable organisations to schedule tasks, receive alerts, optimize databases,
configure security accounts/roles, transfer data between other disparate
sources, and much more.


So Which One - Microsoft Access or SQL Server?

You need to make the decision. It may already be made for you. If you've
just finished this whole tutorial, then there's a good chance you already have
MS Access. This probably means that you've got good reason to use Access.


If you do think you need to upgrade to SQL Server, don't panic - it's not a
scary as you might think! And you didn't just waste your time learning
Access. You will find that SQL Server is similar in many ways to Access. Now
that you know your way around Access, you should be able to relate to SQL
Server much better.


In any case, I recommend




Compiled By: Belay S. --------                                                     40

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:5
posted:10/3/2011
language:English
pages:40