ADVANCED INFORMATION TECHNOLOGY
CAPE IT UNIT 2 MOD 1 OBJECTIVE 12 – STRUCTURED QUERY LANGUAGE
SQL is a standard language for accessing databases.
Our SQL tutorial will teach you how to use SQL to access and manipulate data in:
MySQL, SQL Server, Access, Oracle, Sybase, DB2, and other database systems.
SQL is a standard language for accessing and manipulating databases.
What is SQL?
· SQL stands for Structured Query Language
· SQL lets you access and manipulate databases
· SQL is an ANSI (American National Standards Institute) standard
What Can SQL do?
· SQL can execute queries against a database
· SQL can retrieve data from a database
· SQL can insert records in a database
· SQL can update records in a database
· SQL can delete records from a database
· SQL can create new databases
· SQL can create new tables in a database
· SQL can create stored procedures in a database
· SQL can create views in a database
· SQL can set permissions on tables, procedures, and views
SQL is a Standard - BUT....
Although SQL is an ANSI (American National Standards Institute) standard, there are many different versions of the SQL language.
However, to be compliant with the ANSI standard, they all support at least the major commands (such as SELECT, UPDATE, DELETE, INSERT, WHERE) in a similar manner.
Note: Most of the SQL database programs also have their own proprietary extensions in addition to the SQL standard!
Using SQL in Your Web Site
To build a web site that shows some data from a database, you will need the following:
· An RDBMS database program (i.e. MS Access, SQL Server, MySQL)
· A server-side scripting language, like PHP or ASP
· SQL
· HTML / CSS
RDBMS
RDBMS stands for Relational Database Management System.
RDBMS is the basis for SQL, and for all modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.
The data in RDBMS is stored in database objects called tables.
A table is a collection of related data entries and it consists of columns and rows.
Database Tables
A database most often contains one or more tables. Each table is identified by a name (e.g. "Customers" or "Orders"). Tables contain records (rows) with data.
Below is an example ofa table called "Persons":
P_Id / LastName / FirstName / Address / City1 / Hansen / Ola / Timoteivn 10 / Sandnes
2 / Svendson / Tove / Borgvn 23 / Sandnes
3 / Pettersen / Kari / Storgt 20 / Stavanger
The table above contains three records (one for each person) and five columns (P_Id, LastName, FirstName, Address, and City).
SQL CREATE DATABASE Statement
The CREATE DATABASE Statement
The CREATE DATABASE statement is used to create a database.
SQL CREATE DATABASE Syntax
CREATE DATABASE database_name
CREATE DATABASE Example
Now we want to create a database called "my_db".
We use the following CREATE DATABASE statement:
CREATE DATABASE my_db
Database tables can be added with the CREATE TABLE statement.
SQL CREATE TABLE Statement
The CREATE TABLE Statement
The CREATE TABLE statement is used to create a table in a database.
SQL CREATE TABLE Syntax
CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name3 data_type,
....
)
The data type specifies what type of data the column can hold. For a complete reference of all the data types available in MS Access, MySQL, and SQL Server, go to our complete Data Types reference.
CREATE TABLE Example
Now we want to create a table called "Persons" that contains five columns: P_Id, LastName, FirstName, Address, and City.
We use the following CREATE TABLE statement:
CREATE TABLE Persons
(
P_Id int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
The P_Id column is of type int and will hold a number. The LastName, FirstName, Address, and City columns are of type varchar with a maximum length of 255 characters.
The empty "Persons" table will now look like this:
P_Id / LastName / FirstName / Address / CityThe empty table can be filled with data with the INSERT INTO statement.
SQL PRIMARY KEY Constraint
The PRIMARY KEY constraint uniquely identifies each record in a database table.
Primary keys must contain unique values.
A primary key column cannot contain NULL values.
Each table should have a primary key, and each table can have only ONE primary key.
SQL PRIMARY KEY Constraint on CREATE TABLE
The following SQL creates a PRIMARY KEY on the "P_Id" column when the "Persons" table is created:
MySQL:
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)
SQL Server / Oracle / MS Access:
CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
)
Note: In the example above there is only ONE PRIMARY KEY (pk_PersonID). However, the value of the pk_PersonID is made up of two columns (P_Id and LastName).
SQL PRIMARY KEY Constraint on ALTER TABLE
To create a PRIMARY KEY constraint on the "P_Id" column when the table is already created, use the following SQL:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ADD PRIMARY KEY (P_Id)
To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
Note: If you use the ALTER TABLE statement to add a primary key, the primary key column(s) must already have been declared to not contain NULL values (when the table was first created).
To DROP a PRIMARY KEY Constraint
To drop a PRIMARY KEY constraint, use the following SQL:
MySQL:
ALTER TABLE Persons
DROP PRIMARY KEY
SQL Server / Oracle / MS Access:
ALTER TABLE Persons
DROP CONSTRAINT pk_PersonID
SQL FOREIGN KEY Constraint
SQL FOREIGN KEY Constraint
A FOREIGN KEY in one table points to a PRIMARY KEY in another table.
Let's illustrate the foreign key with an example. Look at the following two tables:
The "Persons" table:
P_Id / LastName / FirstName / Address / City1 / Hansen / Ola / Timoteivn 10 / Sandnes
2 / Svendson / Tove / Borgvn 23 / Sandnes
3 / Pettersen / Kari / Storgt 20 / Stavanger
The "Orders" table:
O_Id / OrderNo / P_Id1 / 77895 / 3
2 / 44678 / 3
3 / 22456 / 2
4 / 24562 / 1
Note that the "P_Id" column in the "Orders" table points to the "P_Id" column in the "Persons" table.
The "P_Id" column in the "Persons" table is the PRIMARY KEY in the "Persons" table.
The "P_Id" column in the "Orders" table is a FOREIGN KEY in the "Orders" table.
The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.
The FOREIGN KEY constraint also prevents that invalid data form being inserted into the foreign key column, because it has to be one of the values contained in the table it points to.
SQL FOREIGN KEY Constraint on CREATE TABLE
The following SQL creates a FOREIGN KEY on the "P_Id" column when the "Orders" table is created:
MySQL:
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)
SQL Server / Oracle / MS Access:
CREATE TABLE Orders
(
O_Id int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
)
To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
)
SQL FOREIGN KEY Constraint on ALTER TABLE
To create a FOREIGN KEY constraint on the "P_Id" column when the "Orders" table is already created, use the following SQL:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Orders
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
To DROP a FOREIGN KEY Constraint
To drop a FOREIGN KEY constraint, use the following SQL:
MySQL:
ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders
SQL Server / Oracle / MS Access:
ALTER TABLE Orders
DROP CONSTRAINT fk_PerOrders
SQL Statements
Most of the actions you need to perform on a database are done with SQL statements.
The following SQL statement will select all the records in the "Persons" table:
SELECT * FROM Persons
In this tutorial we will teach you all about the different SQL statements.
Keep in Mind That...
· SQL is not case sensitive
Semicolon after SQL Statements?
Some database systems require a semicolon at the end of each SQL statement.
Semicolon is the standard way to separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to the server.
We are using MS Access and SQL Server 2000 and we do not have to put a semicolon after each SQL statement, but some database programs force you to use it.
SQL DML and DDL
SQL can be divided into two parts: The Data Manipulation Language (DML) and the Data Definition Language (DDL).
The query and update commands form the DML part of SQL:
· SELECT - extracts data from a database
· UPDATE - updates data in a database
· DELETE - deletes data from a database
· INSERT INTO - inserts new data into a database
The DDL part of SQL permits database tables to be created or deleted. It also defines indexes (keys), specifies links between tables, and imposes constraints between tables. The most important DDL statements in SQL are:
· CREATE DATABASE - creates a new database
· ALTER DATABASE - modifies a database
· CREATE TABLE - creates a new table
· ALTER TABLE - modifies a table
· DROP TABLE - deletes a table
· CREATE INDEX - creates an index (search key)
· DROP INDEX - deletes an index
SQL SELECT Statement
This chapter will explain the SELECT and the SELECT * statements.
The SQL SELECT Statement
The SELECT statement is used to select data from a database.
The result is stored in a result table, called the result-set.
SQL SELECT Syntax
SELECT column_name(s)
FROM table_name
and
SELECT * FROM table_name
Note: SQL is not case sensitive. SELECT is the same as select.
An SQL SELECT Example
The "Persons" table:
P_Id / LastName / FirstName / Address / City1 / Hansen / Ola / Timoteivn 10 / Sandnes
2 / Svendson / Tove / Borgvn 23 / Sandnes
3 / Pettersen / Kari / Storgt 20 / Stavanger
Now we want to select the content of the columns named "LastName" and "FirstName" from the table above.
We use the following SELECT statement:
SELECT LastName,FirstName FROM Persons
The result-set will look like this:
LastName / FirstNameHansen / Ola
Svendson / Tove
Pettersen / Kari
SELECT * Example
Now we want to select all the columns from the "Persons" table.
We use the following SELECT statement:
SELECT * FROM Persons
Tip: The asterisk (*) is a quick way of selecting all columns!
The result-set will look like this:
P_Id / LastName / FirstName / Address / City1 / Hansen / Ola / Timoteivn 10 / Sandnes
2 / Svendson / Tove / Borgvn 23 / Sandnes
3 / Pettersen / Kari / Storgt 20 / Stavanger
Navigation in a Result-set
Most database software systems allow navigation in the result-set with programming functions, like: Move-To-First-Record, Get-Record-Content, Move-To-Next-Record, etc.
Programming functions like these are not a part of this tutorial. To learn about accessing data with function calls, please visit our ADO tutorial or our PHP tutorial.
SQL SELECT DISTINCT Statement
This chapter will explain the SELECT DISTINCT statement.
The SQL SELECT DISTINCT Statement
In a table, some of the columns may contain duplicate values. This is not a problem, however, sometimes you will want to list only the different (distinct) values in a table.
The DISTINCT keyword can be used to return only distinct (different) values.
SQL SELECT DISTINCT Syntax
SELECT DISTINCT column_name(s)
FROM table_name
SELECT DISTINCT Example
The "Persons" table:
P_Id / LastName / FirstName / Address / City1 / Hansen / Ola / Timoteivn 10 / Sandnes
2 / Svendson / Tove / Borgvn 23 / Sandnes
3 / Pettersen / Kari / Storgt 20 / Stavanger
Now we want to select only the distinct values from the column named "City" from the table above.
We use the following SELECT statement:
SELECT DISTINCT City FROM Persons
The result-set will look like this:
CitySandnes
Stavanger
The WHERE clause is used to filter records.
The WHERE Clause
The WHERE clause is used to extract only those records that fulfill a specified criterion.
SQL WHERE Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name operator value
WHERE Clause Example
The "Persons" table:
P_Id / LastName / FirstName / Address / City1 / Hansen / Ola / Timoteivn 10 / Sandnes
2 / Svendson / Tove / Borgvn 23 / Sandnes
3 / Pettersen / Kari / Storgt 20 / Stavanger
Now we want to select only the persons living in the city "Sandnes" from the table above.
We use the following SELECT statement:
SELECT * FROM Persons
WHERE City='Sandnes'