sql from online --10!29!11

8/3/2019 SQL From Online --10!29!11

1/32

About SQL

SQL stands for Structured Query Language.

SQL is a language that enables you to work with a database. UsingSQL, you can insert records, update records, and delete records.

You can also create new database objects such as databases andtables. And you can drop (delete) them.

More advanced features include creating stored procedures (selfcontained scripts), views (pre-made queries), and settingpermissions on database objects (such as tables, storedprocedures, and views).

Although SQL is an ANSI (American National Standards Institute)

standard, there are many different versions of SQL. Differentdatabase vendors have their own variations of the language.

Having said this, to be in compliance with the ANSI standard, theyneed to at least support the major commands such as DELETE,INSERT, UPDATE, WHERE etc. Also, you will find that many vendorshave their own extensions to the language - features that are onlysupported in their database system.

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

Using SQL

If you need to build a website with a database providing thecontent, 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 ahandful of SQL statements. When using SQL on a website, you willoften find yourself either selecting a record, inserting a record,updating a record, or deleting a record. Fortunately, SQL hascommands for performing each of these actions.

8/3/2019 SQL From Online --10!29!11

2/32

8/3/2019 SQL From Online --10!29!11

3/32

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

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 usingDDL. As you can probably guess, the syntax is generally the samefor any object, although, each object will have properties specific tothat object class.

As you can see, the SQL syntax is quite simple. It is also verypowerful syntax - you can do a lot of damage with one line of code!

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 columnsfrom a table called Individual.

How do we know it is trying to select all columns? Because it isusing 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).

8/3/2019 SQL From Online --10!29!11

4/32

8/3/2019 SQL From Online --10!29!11

5/32

Executive

SQL statement

We will select from both the Individualtable and the Occupationtable. We will qualify any column names by prefixing them with itstable's name and a dot.

SELECT * FROM Individual, OccupationWHERE Individual.FirstName = 'Homer'

Result

IndividualId

FirstName

LastName

UserName

OccupationId

IndividualId

JobTitle

1 Homer Simpso

n

homey 2 2 Account

ant2 Homer Brown notsofam

ous3 3 Cleaner

3 Homer Gain noplacelike

5 5 SalesExecutive

Displaying Less Columns

If you don't need every column to be displayed you can single outjust the columns you're interested in. It's good programmingpractice 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 replacethe asterisk (*) with a comma separated list of the column names.

SQL statement

SELECT IndividualId, LastName, UserName FROM IndividualWHERE FirstName = 'Homer'

Result

IndividualId LastName UserName

2 Simpson homey

3 Brown notsofamous

5 Gain noplacelike

8/3/2019 SQL From Online --10!29!11

6/32

In the previous lesson, we used a SQL SELECT statement toretrieve all records from a database table. This is fine if we want tosee every record, but what if we were only interested in somerecords? For example, what if we were only interested in individualswhose first name is "Homer"?

We could use the WHERE clause.

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

Actually, in the previous lesson we did use a WHERE clause whenwe selected records from multiple tables. Here's a closer look at theWHERE clause.

SQL WHERE SyntaxSELECT * FROM table_name

WHERE column_name = 'criteria'

Example

SQL WHERE Statement

SELECT * FROM IndividualWHERE FirstName = 'Homer'

Source Table

IndividualIdFirstNameLastName UserName

1 Fred Flinstone freddo

2 Homer Simpson homey

3 Homer Brown notsofamous

4 Ozzy Ozzbourne sabbath


Result

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

IndividualIdFirstNameLastNameUserName



8/3/2019 SQL From Online --10!29!11

7/32


Multiple Conditions

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

AND Operator

The AND operator filters the query to only those records that satisfyboth the first condition and the second condition.

SELECT * FROM IndividualWHERE FirstName = 'Homer'

AND LastName = 'Brown'

ResultIndividualIdFirstNameLastNameUserName


OR Operator

The OR operator filters the query to only those records that satisfyeither one or the other condition.

SELECT * FROM IndividualWHERE FirstName = 'Homer'OR LastName = 'Ozzbourne'

Result






Using a SQL SELECT statement can retreive many hundreds or eventhousands of records. In some cases you might find it useful to sortthe records by a given column. For example, when selectingrecords from the Individual table, you might like to sort them bythe LastName column.

8/3/2019 SQL From Online --10!29!11

8/32

SQL statement

SELECT * FROM IndividualORDER BY LastName

Source TableIndividualIdFirstNameLastName UserName






Result







Descending Order

By default, ORDER BY sorts the column in ascending order - that is,from lowest values to highest values. You could also explicitly statethis using theASCkeyword, but it's not necessary.

If you want highest values to appear first, you can use the DESCkeyword.

SQL statement

SELECT * FROM IndividualORDER BY LastName DESC

8/3/2019 SQL From Online --10!29!11

9/32

Result





1 Fred Flinstone freddo3 Homer Brown notsofamous

Sorting By Multiple Columns

You can sort by multiple columns by stating each column in theORDER 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 IndividualORDER BY FirstName, LastName

Result



3 Homer Brown notsofamous5 Homer Gain noplacelike



In the preceeding lessons on the SELECT statement, the exampleshave 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. Thiscan be useful on very large tables when there are thousands ofrecords. Returning thousands of records can impact onperformance, and if you are working with a production database,this could have an adverse impact on the users.

8/3/2019 SQL From Online --10!29!11

10/32

Note: The SQL TOP clause is Transact-SQL, and not part of ANSISQL. Therefore, depending on your database system, you may notbe able to use this clause.

SQL statement

SELECT TOP 3 * FROM Individual

Source Table







Result





Specifying a Percentage

You have the option of specifying a percentage of the result setinstead of an absolute value. You do this with the PERCENTkeyword.

SQL statement

SELECT TOP 40 PERCENT * FROM Individual

Result

IndividualIdFirstNameLastNameUserName1 Fred Flinstone freddo


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.

8/3/2019 SQL From Online --10!29!11

11/32

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

SQL statement

SELECT TOP 40 PERCENT * FROM IndividualORDER BY LastName DESC

Result




Once a table starts getting a lot of data in it, some columns willcontain duplicate values. For example, many Individuals share first

names and surnames. Most of the time this isn't a problem. Butsometimes you will want to find out how many unique values thereare in a table. To do this you can use the DISTINCT keyword.

SQL statement

SELECT DISTINCT(FirstName) FROM Individual

Source Table







Result

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

FirstName

Fred

Homer

Ozzy

8/3/2019 SQL From Online --10!29!11

12/32

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

SQL Syntax

SELECT * FROM table_nameWHERE column_name IN (value1,value2,...)

Example

SQL statement

SELECT * FROM IndividualWHERE LastName IN ('Simpson','Ozzbourne','Flinstone')

Source TableId FirstName LastName UserName






Result





You might have noticed that this returns the same result as thefollowing SQL statement:

SELECT * FROM IndividualWHERE LastName = 'Simpson'OR LastName = 'Ozzbourne'OR LastName = 'Flinstone'

Yes, we could just have easily used that statement but the SQL INstatement is more concise.

8/3/2019 SQL From Online --10!29!11

13/32

SQL IN and Subqueries

Now, where the SQL IN operator becomes really useful is when youneed 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 websitevia an administration console. All users in the Publisher table arealso in the Individual table, but not all users in the Individual tableare in the Publisher table.

Source Tables

Individual Table

IndividualIdFirstNameLastName UserName1 Fred Flinstone freddo





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

You may notice that the usernames are in the Individual table butthe access level is stored in the Publisher table. Also, there couldpotentially be many contributors. This is a good candidate for theSQL IN operator!

8/3/2019 SQL From Online --10!29!11

14/32

SQL statement

SELECT UserName FROM IndividualWHERE IndividualId IN(SELECT IndividualIdFROM 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 forthis sort of thing.

In SQL, an alias is a name that you give a table. This can make iteasier 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 itanother one?". Well, there are some good reasons for creating analias. The main reasons are:

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

You may find yourself referencing the same table name overand over again - this will occur if you're working with multipletables and you need to refer to columns from those tables. Itcan 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 havea table called Individualyou could give it an alias ofi. Another tablecalled IndividualProductPurchase could have an alias of, say, ipp

8/3/2019 SQL From Online --10!29!11

15/32

Alias SyntaxSELECT * FROM table_nameAS alias_name

Example SQL StatementSELECT o.JobTitle FROM IndividualAS i, OccupationAS oWHERE i.FirstName = 'Homer'

ORDER BY o.JobTitle

The SQL JOIN refers to using the JOIN keyword in a SQL statementin order to query data from two tables.

When you perform a SQL join, you specify one column from eachtable to join on. These two columns contain data that is sharedacross both tables.

You can use multiple joins in the same SQL statement to query datafrom 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 both 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 ifthere's no matching rows in the right table.

RIGHT OUTER JOIN (or RIGHT JOIN): This will return rowsthat 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, aslong as there's matching data in one of the tables.

Join Syntax

Inner Join:SELECT * FROM table_name1INNERJOIN table_name2ON table_name1.column_name = table_name2.column_name

Left Join:SELECT * FROM table_name1LEFT JOIN table_name2

8/3/2019 SQL From Online --10!29!11

16/32

ON table_name1.column_name = table_name2.column_name

Right Join:SELECT * FROM table_name1RIGHT JOIN table_name2ON table_name1.column_name = table_name2.column_name

Full Join:

SELECT * FROM table_name1FULL JOIN table_name2ON table_name1.column_name = table_name2.column_name

Example Inner Join StatementSELECT * FROM IndividualINNER JOIN PublisherON Individual.IndividualId = Publisher.IndividualId

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

SELECT * FROM IndividualAS IndINNER JOIN PublisherAS PubON Ind.IndividualId = Pub.IndividualId

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

As discussed in the previous lesson, you should use the SQL INNERJOIN when you only want to return records where there is at leastone row in both tables that match the join condition.

Example SQL statement

SELECT * FROM IndividualINNER JOIN PublisherON Individual.IndividualId = Publisher.IndividualIdWHERE Individual.IndividualId = '2'

8/3/2019 SQL From Online --10!29!11

17/32

Source Tables

Left Table

Id FirstName LastName UserName

1 Fred Flinstone freddo2 Homer Simpson homey




Right Table

IndividualId AccessLevel1 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.

This lesson covers both the left outer join, the right outer join, andthe 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, and others where you would need a fullouter join. The join type you use will depend on the situation andwhat data you need to return.

Left Outer Join

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

8/3/2019 SQL From Online --10!29!11

18/32


SELECT * FROM Individual AS IndLEFT JOIN Publisher AS PubON Ind.IndividualId = Pub.IndividualId

Source Tables

Left Table







Right Table


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 Contributor3 Homer Brown notsofamo

us3 Contributor

4 Ozzy Osbourne sabbath 4 Contributor

5 Homer Gain noplacelike NULL NULL

8/3/2019 SQL From Online --10!29!11

19/32

Right Outer Join

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

Example SQL statementSELECT * FROM Individual AS IndRIGHT JOIN Publisher AS PubON Ind.IndividualId = Pub.IndividualId

Source Tables

Left Table

Id FirstName LastName UserName1 Fred Flinstone freddo





Right Table


1 Administrator

2 Contributor

3 Contributor

4 Contributor

10 Administrator

ResultIndividualIdFirstNameLastNameUserName IndividualIdAccessLevel



3 Homer Brown notsofamous3 Contributor


NULL NULL NULL NULL 10 Administrator

8/3/2019 SQL From Online --10!29!11

20/32

Full Outer Join

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


SELECT * FROM Individual AS IndFULL JOIN Publisher AS PubON Ind.IndividualId = Pub.IndividualId

Source Tables

Left Table


1 Fred Flinstone freddo2 Homer Simpson homey




Right Table

IndividualId AccessLevel1 Administrator

2 Contributor

3 Contributor

4 Contributor

10 Administrator

Result

IndividualIdFirstNameLastNameUserName IndividualIdAccessLevel



3 Homer Brown notsofamous3 Contributor


5 Homer Gain noplacelike NULL NULL

NULL NULL NULL NULL 10 Administrator

8/3/2019 SQL From Online --10!29!11

21/32

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

The SQL INSERT command allows you to insert a record into a tablein your database. As with the SELECT syntax, the INSERT syntax isquite straight forward.

SQL statement

INSERT INTO IndividualVALUES ( '6', 'Benny', 'Hill', 'hillbenny' )

Source Table







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.







6 Benny Hill hillbenny

See - nothing to it!

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

8/3/2019 SQL From Online --10!29!11

22/32

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

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

SQL statement

UPDATE IndividualSET UserName = 'funnyman'WHERE IndividualId = '6'

Source Table







6 Benny Hill hillbenny

Result

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


6 Benny Hill funnyman

Updating Multiple Fields

To update multiple fields, separate each field assignment with acomma.

SQL statement

UPDATE IndividualSET UserName = 'getserious', FirstName = 'Onetree'WHERE IndividualId = '6'

Result


6 Onetree Hill getserious

8/3/2019 SQL From Online --10!29!11

23/32

Next lesson covers the DELETE statement.

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

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 withthe UPDATE statement, the syntax for a basic DELETE statement issimilar to a SELECT statement.

SQL statement

DELETEFROM IndividualWHERE IndividualId = '6'

Source TableIndividualIdFirstNameLastName UserName






6 Benny Hill funnyman

Result

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







SQL has a number of functions to assist you in your databaseprogramming.

8/3/2019 SQL From Online --10!29!11

24/32

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

Transact-SQL provides 3 different types of functions:

RowsetFunctions

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

AggregateFunctions

Perform a calculation on a set of values and return asingle value. Aggregate functions can be used in thefollowing:

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

A HAVING clause

ScalarFunctions

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 and Image Functions

On top of these functions, different database vendors have their

own built-in functions for their products. Also, most products enableprogrammers to program their own User Defined Functions. Forinformation on proprietary functions, you should consult thevendor's documentation.

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

8/3/2019 SQL From Online --10!29!11

25/32

A commonly used aggregate function in SQL is COUNT. COUNTreturns 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

actually view those records), we could use COUNT(*). COUNT(*)returns everything - including null values and duplicates.

SQL statement

SELECT COUNT(*) FROM Individual

Source Table







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 ofthe column we want to test.

SQL statement

SELECT COUNT(LastName) FROM Individual

Source Table







8/3/2019 SQL From Online --10!29!11

26/32

6 Bono u2

Result

5

Combining COUNT & DISTINCT

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

SQL statement

SELECT COUNT(DISTINCT(FirstName)) FROM Individual

Result

4

Most database systems provide a way for you to create databaseobjects via a WYSIWYG interface. For example, Microsoft's SQLServer has Enterprise Manager. The Enterprise Manager gives you akind of graphical representation of your database system. You canbrowse through your databases, view the tables and their contentsetc.

Despite having a tool like Enterprise Manager to make these taskseasier, there can be good reasons for wanting to perform some ofthese 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, torun a script against 3 environments than to open up Enterprise

Manager and repeat the steps in 3 environments - especially ifyou 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 enableyou to programatically create database objects including thedatabase, its tables and more.

8/3/2019 SQL From Online --10!29!11

27/32

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 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 fewlessons, we'll cover some of the most common CREATE commands.

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 noarguments have been specified, the database data files andtransaction 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 theCREATE DATABASE command. You should check your database system's

documentation for the specific arguments supported and their usage, but

8/3/2019 SQL From Online --10!29!11

28/32

here's an example of supplying arguments when creating a database using

Microsoft's SQL Server.

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 theinitial size of these files (with the SIZE argument), the maximumsize it can grow to (with the MAXSIZE argument) and the growthincrement of each file (using the FILEGROWTH) argument.

USE masterGOCREATE DATABASE PayrollON( NAME = Payroll_dat,

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

SIZE = 20MB,MAXSIZE = 70MB,FILEGROWTH = 5MB )

LOG ON( NAME = 'Payroll_log',

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

SIZE = 10MB,MAXSIZE = 40MB,FILEGROWTH = 5MB )

GO

Next up is the CREATE TABLE command.

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),
http://www.quackit.com/sql/tutorial/sql_create_table.cfmhttp://www.quackit.com/sql/tutorial/sql_create_table.cfm

8/3/2019 SQL From Online --10!29!11

29/32

LastName Varchar(255),UserName Char(10))

Result

This results in an empty table. You can now use an INSERTstatement to add data to the table.


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 totell the database what type of data it can hold.

The exact data types and how they are expressed differs with eachdatabase system and vendor, but you'll find that generally, therewill be support for fixed length strings (eg char), variable lengthstrings (eg varchar), date/time values (eg datetime), numbers andintegers (eg, bigint, int, smallint, tinyint, numeric).

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

bigint Binary bit char cursor

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 ifyou're unsure of which data type to use or how it is expressed inthat system.

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

Indexes can be created against a table to make searches moreefficient. A database index is similar to an index of a book - a book
http://www.quackit.com/sql/tutorial/sql_insert.cfmhttp://www.quackit.com/sql/tutorial/sql_create_index.cfmhttp://www.quackit.com/sql/tutorial/sql_insert.cfmhttp://www.quackit.com/sql/tutorial/sql_create_index.cfm

8/3/2019 SQL From Online --10!29!11

30/32

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

Indexes can have a slight impact on performance so you shouldonly create indexes against tables and columns that will befrequently searched against. For example, if users of yourapplication often search against the LastName field then that field isa great candidate for an index.

You create an index using the CREATE INDEX command.

SQL syntax

CREATE INDEX index_nameON table_name (column_name)

Example

CREATE INDEX IndividualIndexON Individual (LastName)

SQL Alter Table

In an earlier lesson, we created a table with the CREATE TABLEcommand. In this lesson, we will modify the table using the ALTER

TABLE command.

Add a Column

SQL syntax

ALTER TABLE table_nameADD column_name datatype

Example SQL Statement

ALTER TABLE IndividualADD age int

Change the Datatype

SQL syntax

ALTER TABLE table_nameALTER COLUMN column_name datatype

8/3/2019 SQL From Online --10!29!11

31/32


ALTER TABLE IndividualALTER COLUMN age numeric

Drop a Column

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

SQL syntax

ALTER TABLE table_nameDROP COLUMN column_name


ALTER TABLE Individual

DROP COLUMN age

Congratulations - you've reached the end!

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

We began by learning that SQL stands for Structured QueryLanguage, and is an ANSI standard. We then learned the basic SQL

syntax, before continuing on to the SELECT statement - probablythe most commonly used statement.

We learned that there are various keywords and aggregatefunctions that can be included into SQL statements such as WHERE,COUNT, DISTINCT etc. These are all part of the DML (DataManipulation Language).

Then, after covering the INSERT, UPDATE, and DELETE statements,

we learned that there are various commands for performingadministration tasks against a database. For example, there arecommands for creating database objects (CREATE DATABASE,CREATE TABLE etc), and there are commands for modifying (oraltering) database objects (ALTER DATABASE, ALTER TABLE etc).These commands are part of the DDL (Data Definition Language).

Where to Next?

8/3/2019 SQL From Online --10!29!11

32/32

If you're keen to get into SQL programming, you should download adatabase management system (if you haven't already) and practiceSQL statements against it. You should also read your databasesystem's documentation for any proprietary commands, functionsor features. For example, does your database system have anautomatic scheduler? Are you able to link your database server to adatabase on another server? How do you perform backups on yourdatabase system?

And lastly, if you're new to web development, check out one of theother tutorials on Quackit - for example, the HTML Tutorial, the CSSTutorial, or the JavaScript Tutorial.
http://www.quackit.com/html/tutorialhttp://www.quackit.com/css/tutorialhttp://www.quackit.com/css/tutorialhttp://www.quackit.com/javascript/tutorialhttp://www.quackit.com/html/tutorialhttp://www.quackit.com/css/tutorialhttp://www.quackit.com/css/tutorialhttp://www.quackit.com/javascript/tutorial

sql from online --10!29!11

Documents