TechiWarehouse.Com


Top 3 Products & Services

1.
2.
3.

Dated: Aug. 12, 2004

Related Categories

SQL

INTRODUCTION:

SQL (pronounced "ess-que-el" ) - Structured Query Language is a powerful but nonprocedural language that is used for retrieving or modifying data from the database. Unlike other languages like C, Pascal, etc., SQL does not have any flow-of-control constructs such as if-then-else, do-while, function definitions and so forth. Furthermore, SQL only has a fixed set of datatypes, in other words, you cannot create your own datatypes as you would with other programming languages.

It is used to communicate with a database. According to ANSI (American National Standards Institute), it is the standard language for relational database management systems. SQL statements are used to perform tasks such as update data on a database, or retrieve data from a database. Some common relational database management systems that use SQL are: Oracle, Sybase, Microsoft SQL Server, Access, Ingres, etc. Although most database systems use SQL, most of them also have their own additional proprietary extensions that are usually only used on their system. However, the standard SQL commands such as "Select", "Insert", "Update", "Delete", "Create", and "Drop" can be used to accomplish almost everything that one needs to do with a database.

Tables

A table is a set of columns and rows. Each column is referred to as a field. Each value in a fieldSQL Tutorial represents a single type of data. For example, a table might have three fields: name, city, and state. The table will consist of three columns: one for name, one for city, and one for state. For every row in the table, the name field contains the name, the city field contains the city, and the state field contains the state.

Relational Database Management Systems

The sole purpose of a DBMS is to manage data. If we measure quantity of DBMSs by the number of installations, then by far the most common kind of DBMS is a relational DBMS. Some commercial relational DBMSs are Oracle, Informix and Microsoft Access. Postgres is a research prototype DBMS which supports relational and extended relational data models.

A relational DBMS manages data which are modeled as tables, such as the two tables illustrated below.

The Employees table below describes the employees of a company, listing for each employee their ID within the company, their name, and the ID of the department where they work. The Departments table describes the company’s departments by listing each department’s ID, the department’s name, and the building where the department is located. We have included a small amount of sample data.

Employees
EmpID Name DeptID
123 Sergio CS
234 Lara Comm
135 Elayne Comm
124 Dick CS

 

Departments
DeptID Name Bldg
Comm Communications Lincoln
CS Computer Science PCAT

Typically the DBMS stores its data on disk. The user can write an SQL statement and submit it to a DBMS, which then will retrieve the appropriate data from disk and return it to the user.

SQL has many more features than data retrieval, but we will cover only retrieval here.

Files, Databases and Database Management Systems

Introduction

Data and its storage may be considered to be the heart of any information system. Data has to be up-to-date, accurate, accessible in the required form and available to one or perhaps many users at the same time.

For data to be of value it must be presented in a form which supports the various operational, financial, managerial, decision making, administrative and clerical activities within an organisation.

To meet these objectives data needs to be stored efficiently - to avoid lengthy access times - and with minimal duplication - to avoid lengthy update times and the possibility of inconsistency and inaccuracy. For the data stored by a given organisation to have any value at all its integrity (consistency and accuracy) must always be assured.

In this section we are going to consider what is know as the conceptual view (user view) of stored data. As such we do not need to be concerned with how data is physically stored on specific types of storage media; neither do we need to consider the various storage structures and access methods applicable to retrieval of data from such media.

Data Storage

The data of an organisation takes the form of an abstract representation of objects and events which occur within the organisation's environment. Within the context of an airline company for example, such objects might include Aircraft, Passengers and Airports, and include events such as Flights and the issuing of Tickets.

There are two principle approaches to the storage of data in a computer-based information system. Data may be stored in separate files belonging to isolated information systems operating within individual departments, or data may be stored in a database which may serve as a resource available across all departments and functional areas. The following sections consider these two approaches and their relative merits.

File Based Systems

Before we consider the issues arising from file based storage let us firstly, establish some general concepts and definitions relating to the storage of data in files.

Data Files.

A file is a complete, named collection of information and the basic unit of storage that enables a computer to distinguish one set of information from another. For example a file named "Aircraft" might contain information about the different types of aircraft used by a particular airline company and a file named "Airport" might hold details of all the airports from which the airline operates.

Records.

The data held within a file are organised into structured groups of related elements called records. For example, a record describing an individual aircraft might be composed of the data elements: "identifying number", "name of manufacturer"; "description", "classification" (turbo-prop, jet, etc) "seating capacity" and so on. The aircraft file then contains zero, one, or many such records; where each record describes an individual aircraft.

SQL Tutorial

Fields.

The individual elements of a record are referred to as fields. Hence form the example above, "identifying number", "name of manufacturer", "description", "classification" and "seating capacity", each represent an individual field (element) of the aircraft record.

Data Types.

The data to be held within each field of a given record will possess certain characteristics in terms of size (length measured in characters or digits) and type (numeric, alphabetic, dates, etc). Each field of a record is allocated a particular data type which describes the allowed characteristics of the data to be held by the field and further indicates the range of operations which can be carried out on the field; for example, arithmetic operations would be valid on fields containing numeric data but not on fields containing an address or a narrative description.

Keys.

A key is a field or combination of fields used to identify a record. When a key uniquely identifies a record it is referred to as the primary key.

Continuing with the example of the Aircraft record, if a given value of the field "identifying number" identifies an individual aircraft then it could serve as the primary key. Other fields such as "manufacturer" for example, could also serve as alternative keys (secondary keys) by which a set of records (eg all aircraft from a particular manufacturer) could be identified.

Issues Arising From File Based Systems

Data Duplication.

The use of individual data files each serving separate information system tends to lead to situations in which an organisation maintains many copies of the same basic information.

For example let us consider a sales department which calculates bonuses payable to individual sales personnel on the value of their sales; with actual bonus payments being made only after confirmation that the sales invoices on which bonuses have been calculated have been fully paid.

If the sales department was to operate its bonus scheme based on its own sales and receipts files then the sales department would undoubtedly be holding copies of data such as employee name, payroll number, invoice numbers and amounts received, which would already be recorded elsewhere in the organisation by the personnel department and by the accounts department for example.

A change in marital status resulting in a change of surname would therefore result in the need to update several files and queries against invoices resulting in debit or credit notes being raised would also require the updating of several files.

Data Inconsistency and Integrity.

Where several discrete files exist serving the immediate requirements of individual departments there is a strong likelihood that the common or duplicated data held by these files will get out of step with each other resulting in different versions of data being held by the organisation. If the accounts receivable system in the example above issues a credit note against an incorrectly totalled invoice and fails to advise the sales department of this then the integrity of the sales department's files would be lost and as a result incorrect bonus payments would be made.

File Design.

Not with standing the above consequences of file based information systems, taken from the view point of an individual information system, it is a relatively easy matter to ensure that the required files are designed to "perfectly" suite user/application needs. As such, taken in isolation, such information systems are capable of presenting information in exactly the form required by their users and also of providing highly efficient usage of storage and rapid retrieval times.

Databases and Database Management Systems

For a generalised consideration of databases we may continue to apply the data storage concepts (files, records, keys, etc) previously introduced for file based systems. However, databases are not just a collection of files; through specific access controls provided by the Database Management System (DBMS) databases are able to provide a central resource of data which can be shared between users on an organisation wide basis.

Database Design.

To satisfy the information needs of users across an organisation the database has to be designed (in terms of "files" and "record layouts") in a generalised manner.

Considering the personnel and sales departments views of personnel records for example. The sales department is only interested in a specific type of personnel record; those records for sales men and women. The sales department might also only be interested in fields such as payroll number, name and bonus. Whilst the personnel department on the other hand would be interested in all personnel records irrespective of job description ( a field in the personnel record) and would want record fields in addition to those of interest to the sales department, such as home-address, marital status, date-of-appointment, department, salary-scale and scale-point, national insurance number and so on.

User Views.

It is the role of the DBMS to provide facilities which enable data ( from a generalised definition ) to be presented in the form required by specific users. So for example the DBMS should provide the sales department with just those record fields which they require about sales personnel whilst at the same time providing the personnel department with their requirements.

Database Security and Integrity.

There are two major consequences arising from the shared usage of data, namely, security and integrity.

Firstly, the DBMS must ensure that users are only allowed access to data which they are authorised to access. In addition, access authorisation must also restrict the type of access; limiting some users to read-only access for certain instances of data for example. So, if only the accounts receivable users are permitted to adjust the value of invoices, by the issuing of credit notes for instance, then the sales department, whilst interested to see when full payment of an invoices has been received, should not be permitted to change the invoice records in any way.

Secondly, the DBMS must also ensure that conflicting updates do not occur. In a stock control systems for example, a user updating the database for a customer order, must be given exclusive access to the records of the ordered items so their "quantity on hand" fields may be updated. Such exclusive access should prevent anyone from looking at out of date quantity on hand figures and should also prevent two or more users from trying to update the same quantity on hand values at the same time. This serialisation of record updating is controlled through the DBMS, which in the case of the above example, would issue locks on the records required by each customer order transaction.

Database Performance.

Because databases maintain data in a generalised form, converting this generalised form into a series of user views as required, they are generally less efficient in terms of data storage usage and in terms of access times compared with their individual counterpart file based systems.

Relational Databases

The Relational Model

The relational model was developed by E F Codd at the IBM San Jose Research Laboratory in the late 1960s. This work being published in 1970 under the title:

"A Relational Model of Data For Large Shared Data Banks".

Relational Query Languages.

The relational database model as defined by Codd included a number of alternative relational query languages.

The Ingres project developed a query language called Quel which broadly complies with Codd's definition of a tuple relational calculus query language. Quel is still a part of the Ingres DBMS available today; although in view of current trends SQL is generally chosen.

The System R project developed a series of query languages; the first of these called SQUARE, was later developed into a more convenient form called SEQUEL. SEQUEL was itself further developed into the form of today's SQL.

In 1986 the American National Standards Institute ANSI published an SQL standard the:

"Systems Application Architecture Database Interface (SAA SQL)".

Query languages have two main components:

Data Manipulation Language (DML), and

Data Definition Language (DDL);

where the DML part of the language is used to retrieve, delete and amend instances of data in the database and where the DDL part of the language is used to describe the type of data to be held by the database.

Relational Database Terminology

In a Relational Database all data may be viewed in the form of simple two-dimensional tables and to distinguish this representation of data from that of other representations we use a separate terminology to describe the data held in a Relational Database.

There are in fact alternative terms used to describe the data in a relational database. The first is taken from the formal definition of the relational model and the second is based on the ability to view data in the form of simple tables

SQL Data Manipulation Language

SQL is a non-procedural language that is, it allows the user to concentrate on specifying what data is required rather than concentrating on the how to get it.

The non-procedural nature of SQL is one of the principle characteristics of all 4GLs - Fourth Generation Languages - and contrasts with 3GLs (eg, C, Pascal, Modula-2, COBOL, etc) in which the user has to give particular attention to how data is to be accessed in terms of storage method, primary/secondary indices, end-of-file conditions, error conditions (eg ,Record NOT Found), and so on.

The DML component of SQL comprises four basic statements:

SELECT to retrieve rows from tables

UPDATE to modify the rows of tables

DELETE to remove rows from tables

INSERT to add new rows to tables.

SQL Data Definition Language

Database Administration

All of the statements considered in previous sections belong to the data manipulation part of the SQL language and allow users to interrogate and change the data of selected database tables.

In this section we will consider two statements which form the data definition part of the INGRES/SQL language, namely:

CREATE used to create table definitions

DROP used to remove unwanted tables from the database.

In a live database, ie a database supporting some aspect of an organisation's operation, these statements (and others beside) would be used by the database administrator (DBA) to establish and maintain the definition of the database tables which support the organisations information needs.

SQL Create Database and Table

Create a Database

To create a database:

CREATE DATABASE database_name

Create a Table

To create a table in a database:

CREATE TABLE table_name (
column_name1 data_type, column_name2 data_type, .......
)


Example

This example demonstrates how you can create a table named "Person", with four columns. The column names will be "LastName", "FirstName", "Address", and "Age":

CREATE TABLE Person 
(
LastName varchar,
FirstName varchar,
Address varchar,
Age int
)

This example demonstrates how you can specify a maximum length for some columns:

CREATE TABLE Person 
(
LastName varchar(30),
FirstName varchar,
Address varchar,
Age int(3) 
)

The data type specifies what type of data the column can hold. The table below contains the most common data types in SQL:

Data Type Description
integer(size)
int(size)
smallint(size)
tinyint(size)
Hold integers only. The maximum number of digits are specified in parenthesis.
decimal(size,d)
numeric(size,d)
Hold numbers with fractions. The maximum number of digits are specified in "size". The maximum number of digits to the right of the decimal is specified in "d".
char(size) Holds a fixed length string (can contain letters, numbers, and special characters). The fixed size is specified in parenthesis.
varchar(size) Holds a variable length string (can contain letters, numbers, and special characters). The maximum size is specified in parenthesis.
date(yyyymmdd) Holds a date

INDEX: Create Index

Indices are created in an existing table to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of a table, and each index is given a name. The users can not see the indices, they are just used to speed up queries. Note: Updating a table containing indices takes more time than updating a table without, this is because the indices also need an update. So, it is a good idea to create indices only on columns that are often used for a search.

A Unique Index

Creates an unique index on a table. A unique index means that two rows cannot have the same index value.

CREATE UNIQUE INDEX index_name
ON table_name (column_name)

The "column_name" specifies the column you want indexed.

A Simple Index

Creates a simple index on a table. When the UNIQUE keyword is omitted, duplicate values are allowed.

CREATE INDEX index_name
ON table_name (column_name)

The "column_name" specifies the column you want indexed. Example

This example creates a simple index, named "PersonIndex", on the LastName field of the Person table:

CREATE INDEX PersonIndex
ON Person (LastName)

If you want to index the values in a column in descending order, you can add the reserved word DESC after the column name:

CREATE INDEX PersonIndex
ON Person (LastName DESC)

If you want to index more than one column you can list the column names within the parentheses, separated by commas:

CREATE INDEX PersonIndex
ON Person (LastName, FirstName)

Drop Index

You can delete an existing index in a table with the DROP statement.

DROP INDEX table_name.index_name

Delete a Database or Table

To delete a database:

drop. database database_name

To delete a table:

DROP TABLE table_name

To delete the data in a table without deleting the table:

DELETE TABLE table_name

SQL Select Statement

The SELECT statement selects columns of data from a database. The tabular result is stored in a result table (called the result set). The SELECT Statement

The SELECT statement selects columns of data from a database. Use it to SELECT information FROM a table like this:

SELECT column_name(s) FROM table_name
Example: Select Columns from a Table

To select the columns named "LastName" and "FirstName", use a SELECT statement like this:

SELECT LastName,FirstName FROM Persons

The "Persons" table:

LastName FirstName Address City
Hansen Ola Timoteivn 10 Sandnes
Svendson Tove Borgvn 23 Sandnes
Pettersen Kari Storgt 20 Stavanger

The result:

LastName FirstName
Hansen Ola
Svendson Tove
Pettersen Kari

Example: Select all Columns

To select all columns from the "Persons" table, use a * symbol instead of column name like this:

SELECT * FROM Persons

The result:

LastName FirstName Address City
Hansen Ola Timoteivn 10 Sandnes
Svendson Tove Borgvn 23 Sandnes
Pettersen Kari Storgt 20 Stavanger

The Result Table

The result from a SQL query is stored in a result set. The result set can be thought of as a result table. Most database software allow navigation of the result set with programming functions like: Move-To-First-Record, Get-Record-Content, Move-To-Next-Record...

Semicolon after SQL Statements?

Many SQL tutorials insist that you should always end an SQL statement with a semicolon. That is not correct. But semicolon is a standard way to end an SQL statement (and to separate different SQL statements) in systems that allow more than one SQL statement to be executed in the same call to a database server.

SQL Where Clause

The WHERE clause is used to specify a selection criteria.

The WHERE Clause

To conditionally select data from a table, a WHERE clause can be added to the SELECT statement with the following syntax:

SELECT column FROM table WHERE column condition value

With the WHERE clause, these conditions can be used:

Operator Condition
= Equal
<> Not equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
BETWEEN Between an inclusive range
LIKE Explained below

Note: In some versions of SQL the not equal operator <> can be written as != Example: Select Persons from a City

To select only the people that live in Sandnes, add a WHERE clause to the SELECT statement like this:

SELECT * FROM Persons WHERE City='Sandnes'

The "Persons" Table:

LastName FirstName Address City Year
Hansen Ola Timoteivn 10 Sandnes 1951
Svendson Tove Borgvn 23 Sandnes 1978
Svendson Stale Kaivn 18 Sandnes 1980
Pettersen Kari Storgt 20 Stavanger 1960

The Result:

LastName FirstName Address City Year
Hansen Ola Timoteivn 10 Sandnes 1951
Svendson Tove Borgvn 23 Sandnes 1978
Svendson Stale Kaivn 18 Sandnes 1980
Using Quotes

Note that we have used single quotes around the conditional values in the examples. SQL uses single quotes around text values. Most database systems will also accept double quotes. Numeric values should not be enclosed in quotes.

For Text values:

This is correct:
SELECT * FROM Persons WHERE FirstName='Tove'
This is not correct:
SELECT * FROM Persons WHERE FirstName=Tove

For Numeric values:

This is correct:
SELECT * FROM Persons WHERE Year>1965
This is not correct:
SELECT * FROM Persons WHERE Year>'1965'

The LIKE Condition

The LIKE condition is used to specify a search for a pattern in a column.

The syntax is like this:

SELECT column FROM table WHERE column LIKE pattern

A "%" sign can be used to define wildcards (missing letters in the pattern) both before and after the pattern. Example: Select Persons with a Name Pattern

This SQL statement will return persons with a first name that start with an 'O'.

SELECT * FROM Persons WHERE FirstName LIKE 'O%'

This SQL statement will return persons with a first name that end with an 'a'.

SELECT * FROM Persons WHERE FirstName LIKE '%a'

This SQL statement will return persons with a first name that contains the pattern 'la'.

SELECT * FROM Persons WHERE FirstName LIKE '%la%'

All the examples above will return the following result:

LastName FirstName Address City Year
Hansen Ola Timoteivn 10 Sandnes 1951

SQL And & Or

AND and OR join two or more conditions in a WHERE clause.The AND operator displays a row if ALL conditions listed are true. The OR operator displays a row if ANY of the conditions listed are true.

Original Table (used in the examples)

LastName FirstName Address City
Hansen Ola Timoteivn 10 Sandnes
Svendson Tove Borgvn 23 Sandnes
Svendson Stephen Kaivn 18 Sandnes

Example

Use AND to display each person with the first name equal to "Tove", and the last name equal to "Svendson":

SELECT * FROM Persons
WHERE FirstName='Tove'
AND LastName='Svendson'

Result:

LastName FirstName Address City
Svendson Tove Borgvn 23 Sandnes

Example

Use OR to display each person with the first name equal to "Tove", or the last name equal to "Svendson":

SELECT * FROM Persons
WHERE firstname='Tove'
OR lastname='Svendson'

Result:

LastName FirstName Address City
Svendson Tove Borgvn 23 Sandnes
Svendson Stephen Kaivn 18 Sandnes

Example

You can also combine AND and OR (use parentheses to form complex expressions):

SELECT * FROM Persons WHERE
(FirstName='Tove' OR FirstName='Stephen')
AND LastName='Svendson'

Result:

LastName FirstName Address City
Svendson Tove Borgvn 23 Sandnes
Svendson Stephen Kaivn 18 Sandnes
S

QL Between / And

The BETWEEN ... AND operator selects an inclusive range of data between two values. These values can be numbers, text, or dates.

SELECT column_name FROM table_name WHERE column_name BETWEEN value1 AND value2

Original Table (used in the examples)

LastName FirstName Address City
Hansen Ola Timoteivn 10 Sandnes
Nordmann Anna Neset 18 Sandnes
Pettersen Kari Storgt 20 Stavanger
Svendson Tove Borgvn 23 Sandnes

Example 1

To display the persons alphabetically between (and including) "Hansen" and "Pettersen", use the following SQL:

SELECT * FROM Persons WHERE LastName
BETWEEN 'Hansen' AND 'Pettersen'

Result:

LastName FirstName Address City
Hansen Ola Timoteivn 10 Sandnes
Nordmann Anna Neset 18 Sandnes
Pettersen Kari Storgt 20 Stavanger

Example 2

To display the persons outside the range used in the previous example, use the NOT operator:

SELECT * FROM Persons WHERE LastName
NOT BETWEEN 'Hansen' AND 'Pettersen'

Result:

LastName FirstName Address City
Svendson Tove Borgvn 23 Sandnes

SQL INSERT INTO

The INSERT INTO statement inserts new rows into a table:

INSERT INTO table_name VALUES (value1, value2,....)

You can also specify the columns for which you want to insert data:

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

Insert a New Row

This "Persons" table:

LastName FirstName Address City
Pettersen Kari Storgt 20 Stavanger

And this SQL statement:

INSERT INTO Persons 
VALUES ('Hetland', 'Camilla', 'Hagabakka 24', 'Sandnes')

Will give this result:

LastName FirstName Address City
Pettersen Kari Storgt 20 Stavanger
Hetland Camilla Hagabakka 24 Sandnes

Insert Data in Specified Columns

This "Persons" table:

LastName FirstName Address City
Pettersen Kari Storgt 20 Stavanger
Hetland Camilla Hagabakka 24 Sandnes

And This SQL statement:

INSERT INTO Persons (LastName, Address)
VALUES ('Rasmussen', 'Storgt 67')

Will give this result:

LastName FirstName Address City
Pettersen Kari Storgt 20 Stavanger
Hetland Camilla Hagabakka 24 Sandnes
Rasmussen   Storgt 67  

SQL Update

Update Rows

The UPDATE statement updates or changes rows:

UPDATE table_name SET column_name = new_value WHERE column_name = some_value

Person:

LastName FirstName Address City
Nilsen Fred Kirkegt 56 Stavanger
Rasmussen   Storgt 67  

Update one Column in a Row

We want to add a first name to the person with a last name of "Rasmussen":

UPDATE Person SET FirstName = 'Nina'
WHERE LastName = 'Rasmussen'

Result:

LastName FirstName Address City
Nilsen Fred Kirkegt 56 Stavanger
Rasmussen Nina Storgt 67  

Update several Columns in a Row

We want to change the address and add the name of the city:

UPDATE Person
SET Address = 'Stien 12', City = 'Stavanger'
WHERE LastName = 'Rasmussen'

Result:

LastName FirstName Address City
Nilsen Fred Kirkegt 56 Stavanger
Rasmussen Nina Stien 12 Stavanger

SQL Delete

Delete Rows

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

DELETE FROM table_name WHERE column_name = some_value

Person:

LastName FirstName Address City
Nilsen Fred Kirkegt 56 Stavanger
Rasmussen Nina Stien 12 Stavanger

Delete a Row

"Nina Rasmussen" is going to be deleted:

DELETE FROM Person WHERE LastName = 'Rasmussen'

Result

LastName FirstName Address City
Nilsen Fred Kirkegt 56 Stavanger

Now that you've gotten free know-how on this topic, try to grow your skills even faster with online video training. Then finally, put these skills to the test and make a name for yourself by offering these skills to others by becoming a freelancer. There are literally 2000+ new projects that are posted every single freakin' day, no lie!


Previous Article

Next Article


cheapguccibelts's Comment
Cheap Designer Belts http://www.operakidsamsterdam.com/
27 Wed May 2015
Admin's Reply:



Mine's Comment
The SQL standard names are LOWER and UPPER, not LCASE and UCASE. Some prducots like MySQL alias LCASE and UCASE to the LOWER and UPPER functions for increased compatibility with other non-standard prducots and some prducots that are not databases. MS Access uses LCASE and UCASE as does the non-database prducots Excel and OOCalc. There are some programming languages which use LCASE and UCASE. There may be other DB prducots that do not use the SQL standard LOWER/UPPER names for these functions. Oracle does use LOWER/UPPER. DB2 supports both. PostgreSQL uses LOWER/UPPER.
02 Sat Jun 2012
Admin's Reply:

Agreed!




Lara's Comment
How do you delete (or can you) a record from a detail file based on the value in the matching header file. That is, I want to delete records from the detail where the customer number and po number in the detail match those in the header.
03 Mon Oct 2011
Admin's Reply:

As far as I know, NO. But, you can export a list based on your criteria, and delete the records with corresponding data.




shreyas joshi's Comment
this gives very good details within less time thanx this covers everything
02 Thu Jun 2011
Admin's Reply:

 Thanks! You can expect the same quality on the rest of our articles. Please keep coming back and suggest us to your friends.