Transcript SQL Basic

SQL
From:
http://www.w3schools.com/sql
SQL
• It is a standard language for accessing and
manipulating databases
– MySQL, SQL Server, Access, Oracle, Sybase, DB2, and
others
• SQL stands for Structured Query Language
• SQL is an ANSI (American National Standards
Institute) standard
– There are many variations and different systems have
their own extensions
– But the major commands are same
SQL
•
SQL include
– Data Manipulation Language (DML)
– Data Definition Language (DDL)
•
•
The query and update commands for DML
– SELECT: extracts data from a database
– UPDATE: updates data in a database
– DELETE: deletes data from a database
– INSERT INTO: insert new data into a database
DDL: permits database tables to be created or deleted, define indexes (keys), impose
constraints between tables. Some important statements:
–
–
–
–
–
–
–
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
DROP INDEX: deletes an index
Create Persons Table
Persons Table
P_Id
LastName
FirstName
Address
City
1
Hansen
Ola
Timoteivn 10
Sandnes
2
Svendson
Tove
Borgvn 23
Sandnes
3
Pettersen
Kari
Storgt 20
Stavanger
CREATE DATABASE test;
CREATE TABLE persons (
P_Id
INT,
LastName VARCHAR(25),
FirstName VARCHAR(25),
Address
VARCHAR(25),
City
VARCHAR(15),
PRIMARY KEY (P_Id)
);
INSERT INTO persons VALUES (1, 'Hansen', 'Ola', 'Timoteivn10', 'Sandnes');
INSERT INTO persons VALUES (2, 'Svendson', 'Tove', 'Borgvn23','Sandnes');
INSERT INTO persons VALUES (3, 'Pettersen', 'Kari', 'Storgt20', 'Stavanger');
Aqua Data Studio
• You can use either “go” or “/” to replace the “;” so that it
can process multiple SQLs at the same time.
• More info:
http://www.aquafold.com/support_faq.html#commands
select * from t1
select * from t2
select * from t3
select * from t1
/
select * from t2
/
select * from t3
/
select * from t1
go
select * from t2
go
select * from t3
go
SQL BASIC
SELECT
•
•
•
•
SELECT is used to select data from a database
The result is stored in a result table, called the result-set
SQL is not case sensitive
SELECT syntax
SELECT column_name(s)
FROM table_name;
SELECT * FROM table_name;
SELECT
Persons Table
P_Id
LastName
FirstName
Address
City
1
Hansen
Ola
Timoteivn 10
Sandnes
2
Svendson
Tove
Borgvn 23
Sandnes
3
Pettersen
Kari
Storgt 20
Stavanger
SELECT LastName, FirstName FROM Persons;
SELECT *FROM Persons;
SELECT DISTINCT
SELECT DISTINCT column_name(s)
FROM table_name;
SELECT DISTINCT city FROM persons;
WHERE clause
SELECT column_name(s)
FROM table_name
WHERE column_name operator value;
SELECT * FROM persons
WHERE city=‘Sandnes’;
WHERE Clause
• Text values should be quoted by single quotes or
double quotes
• Numeric values do not need to be enclosed in quotes
SELECT * FROM persons
WHERE city=‘Sandnes’;
Or
SELECT * FROM persons
WHERE city=“Sandnes”;
Or
SELECT * FROM persons
WHERE P_Id=1;
WHERE Clause
Operator
Description
=
Equal
<>
Not equal
>
Greater than
<
Less than
>=
Greater than or equal
<=
Less than or equal
BETWEEN
Between an inclusive range
LIKE
Search for a pattern
IN
If you know the exact value you want to return for at least one of
the columns
AND or OR
• AND, OR operators are used to filter records based on more
than one condition
• AND=both the first and the second conditions is true
• OR=either the first or the second condition is true
AND or OR
SELECT * FROM persons
WHERE firstname=‘Tove’ AND lastname=‘Svendson’;
SELECT * FROM persons
WHERE firstname=‘Tove’ OR firstname=‘Ola’;
SELECT * FROM persons
WHERE lastname=‘Svendson’ AND (firstname=‘Tove’ OR firstname=‘Ola’);
ORDER BY
• The ORDER BY keyword is used to sort the result-set by a specified column
• It sorts the records in ascending order by default
• Use DESC for a descending order
SELECT column_name(s)
FROM table_name
ORDER BY column_name(s) ASC|DESC;
ORDER BY
INSERT INTO persons VALUES (4, ‘Nilsen’, ‘Tom’, ‘Vingvn23', 'Stavanger');
SELECT * FROM Persons
ORDER BY lastname;
SELECT * FROM Persons
ORDER BY lastname DESC;
INSERT INTO
• Use to insert new records in a table
INSERT INTO table_name
VALUES (value1, value2, value3,…);
INSERT INTO table_name (column1, column2, column3, …
VALUES (value1, value2, value3,…);
INSERT INTO persons VALUES (4, ‘Nilsen’, ‘Tom’, ‘Vingvn23', 'Stavanger');
INSERT INTO persons (P_Id, lastname, firstname)
VALUES (5, ‘Tjessem’, ‘Jakob’);
UPDATE
• Update records in a table
UPDATE table_name
SET column=value, column2=value2,…
WHERE some_column=some_value;
UPDATE Persons
SET Address=‘Nissestien 67’, city=‘Sandnes’
WHERE lastname=‘Tjessem’ AND firstname=‘Jakob’;
Warning: if you forget to add WHERE clause, all the address and city will be set to
‘Nissestien 67’ and ‘Sandnes’.
DELETE statement
• Used to delete records in a table
DELETE FROM table_name
WHERE some_column=some_value;
DELETE FROM persons
WHERE lastname=‘Tjessem’ AND firstname=‘Jakob’;
DELETE FROM table_name;
Or
DELETE * FROM table_name;
Test
CompanyName
Alfreds Futterkiste
Berglunds snabbköp
Centro comercial Moctezuma
Ernst Handel
FISSA Fabrica Inter. Salchichas S.A.
Galería del gastrónomo
Island Trading
Königlich Essen
Laughing Bacchus Wine Cellars
Magazzini Alimentari Riuniti
North/South
Paris spécialités
Rattlesnake Canyon Grocery
Simons bistro
The Big Cheese
Vaffeljernet
Wolski Zajazd
ContactName
Maria Anders
Christina Berglund
Francisco Chang
Roland Mendel
Diego Roel
Eduardo Saavedra
Helen Bennett
Philip Cramer
Yoshi Tannamuri
Giovanni Rovelli
Simon Crowther
Marie Bertrand
Paula Wilson
Jytte Petersen
Liz Nixon
Palle Ibsen
Zbyszek
Piestrzeniewicz
Address
Obere Str. 57
Berguvsvägen 8
Sierras de Granada 9993
Kirchgasse 6
C/ Moralzarzal, 86
Rambla de Cataluña, 23
Garden House Crowther Way
Maubelstr. 90
1900 Oak St.
Via Ludovico il Moro 22
South House 300 Queensbridge
265, boulevard Charonne
2817 Milton Dr.
Vinbæltet 34
89 Jefferson Way Suite 2
Smagsløget 45
ul. Filtrowa 68
City
Berlin
Luleå
México D.F.
Graz
Madrid
Barcelona
Cowes
Brandenburg
Vancouver
Bergamo
London
Paris
Albuquerque
København
Portland
Århus
Warszawa
Create this Customers table and do the following SQL queries
TEST
CREATE TABLE customers (
CompanyName
VARCHAR(100),
ContactName VARCHAR(100),
Address
VARCHAR(100),
City
VARCHAR(50)
);
INSERT INTO customers VALUES ('Alfreds Futterkiste', 'Maria Anders', 'Obere Str. 57', 'Berlin');
INSERT INTO customers VALUES ('Berglunds snabbköp', 'Christina Berglund', 'Berguvsvägen 8','Luleå');
INSERT INTO customers VALUES ('Centro comercial Moctezuma', 'Francisco Chang', 'Sierras de Granada 9993', 'México D.F.');
INSERT INTO customers VALUES ('Ernst Handel', 'Roland Mendel', 'Kirchgasse 6', 'Graz');
INSERT INTO customers VALUES ('FISSA Fabrica Inter. Salchichas S.A.', 'Diego Roel', 'C/Moralzarzal, 86 ', 'Madrid');
INSERT INTO customers VALUES ('Galería del gastrónomo', 'Eduardo Saavedra', 'Rambla de Cataluña, 23', 'Barcelona');
INSERT INTO customers VALUES ('Island Trading', 'Helen Bennett', 'Garden House Crowther Way', 'Cowes');
INSERT INTO customers VALUES ('Königlich Essen', 'Philip Cramer', 'Maubelstr. 90', 'Brandenburg');
INSERT INTO customers VALUES ('Laughing Bacchus Wine Cellars', 'Yoshi Tannamuri', '1900 Oak St.', 'Vancouver');
INSERT INTO customers VALUES ('Magazzini Alimentari Riuniti', 'Giovanni Rovelli', 'Via Ludovico il Moro 22', 'Bergamo');
INSERT INTO customers VALUES ('North/South', 'Simon Crowther', 'South House 300 Queensbridge', 'London');
INSERT INTO customers VALUES ('Paris spécialités', 'Marie Bertrand', '265, boulevard Charonne', 'Paris');
INSERT INTO customers VALUES ('Rattlesnake Canyon Grocery', 'Paula Wilson', '2817 Milton Dr.', 'Albuquerque');
INSERT INTO customers VALUES ('Simons bistro', 'Jytte Petersen', 'Vinbæltet 34', 'København');
INSERT INTO customers VALUES ('The Big Cheese', 'Liz Nixon', '89 Jefferson Way Suite 2', 'Portland');
INSERT INTO customers VALUES ('Vaffeljernet', 'Palle Ibsen', 'Smagsløget 45', 'Århus');
INSERT INTO customers VALUES ('Wolski Zajazd', 'Zbyszek Piestrzeniewicz', 'ul. Filtrowa 68', 'Warszawa');
TEST
SELEC * FROM customers;
SELECT CompanyName, ContactName FROM customers;
SELECT * FROM customers WHERE companyname LIKE ‘a%’;
SELECT * FROM customers WHERE companyname LIKE ‘A%’;
SELECT companyname, contactname
FROM customers
WHERE companyname > ‘A’;
SELECT companyname, contactname
FROM customers
WHERE companyname > ‘G’
AND contactname > ‘G’;
SQL ADVANCED
LIMIT clause
• Used to specify the number of records to return
SELECT column_name(s)
FROM table_name
LIMIT number;
SELECT * FROM persons LIMIT 2;
Oracle, SQL Server: Top number|percent
(e.g., Top 2, or Top 50 PERCENT
Oracle: ROWNUM<=number (e.g.,
ROWNUM<=5)
LIKE operator
• Used in a WHERE clause to search for a specified pattern in a column
SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;
SELECT * FROM persons WHERE city LIKE ‘S%’;
SELECT * FROM persons WHERE city LIKE ‘%s’;
%: define wildcards
(missing letters)
both before and
after the pattern
SQL Wildcards
• SQL Wildcards can substitute for one or more characters when searching
for data in a database
• SQL wildcards must be used with the SQL LIKE operator
Wildcard
Description
%
A substitute for zero or more characters
_
A substitute for exactly one character
SQL Wildcards
SELECT * FROM persons WHERE city LIKE ‘Sa%’;
SELECT * FROM persons WHERE city LIKE ‘%nes%’;
SELECT * FROM persons WHERE firstname LIKE ‘_la’;
SELECT * FROM persons WHERE lastname LIKE ‘S_end_on’;
IN operator
• To specify multiple values in a WHERE clause
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2,…)
SELECT * FROM Persons
WHERE lastname IN (‘Hansen’, ‘Pettersen’)
SELECT * FROM Persons
WHERE lastname IN (SELECT lastname from Persons where
city=‘Sandnes’)
BETWEEN operator
• Used in a WHERE clause to select a range of data between two values
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value 2;
SELECT * FROM persons
WHERE lastname BETWEEN ‘Hansen’ AND ‘Pettersen’;
SELECT * FROM persons
WHERE lastname NOT BETWEEN ‘Hansen’ AND ‘Pettersen’;
Warning: different
database systems
have different ways
of processing
BETWEEN operator
Alias
• An alias name can be given to a table or a column
SELECT column_name(s)
FROM table_name AS alias_name;
Or
SELECT column_name AS alias_name
FROM table_name;
SELECT po.O_ID, p.LastName, p.FirstName
FROM Persons AS p, Orders AS po
WHERE p.LastName='Hansen' AND p.FirstName='Ola';
Without alias
SELECT Orders.OrderID, Persons.LastName, Persons.FirstName
FROM Persons, Orders
WHERE Persons.LastName=‘Hansen’ AND Persons.FirstName=‘Ola’;
JOIN
• Used to query data from two or more tables
– JOIN: return rows when there is at least one match in both tables
– LEFT JOIN: return all rows from the left table, even if there are no
matches in the right table
– RIGHT JOIN: return all rows from the right table, even if there are no
matches in the left table
– FULL JOIN: return rows where there is a match in one of the tables
JOIN
O_Id
OrderNo
P_Id
1
77895
3
2
44678
3
3
22456
1
4
24562
1
5
34764
15
Same as INNER JOIN
CREATE DATABASE test;
CREATE TABLE orders (
O_Id
INT,
OrderNO INT,
P_Id
INT,
PRIMARY KEY (O_Id)
);
INSERT INTO orders VALUES (1, 77895, 3);
INSERT INTO orders VALUES (2, 44678, 3);
INSERT INTO orders VALUES (3, 22456, 1);
INSERT INTO orders VALUES (4, 24562, 1);
INSERT INTO orders VALUES (5, 34764, 15);
INNER JOIN
SELECT column_name(s)
FROM table_name1 INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name;
SELECT persons.lastname, persons.firstname, orders.orderNo
From Persons INNER JOIN Orders
ON persons.P_Id=orders.P_Id
ORDER BY persons.Lastname;
The INNER JOIN keyword returns rows
where there is at least one match in both
tables. If there are rows in Persons that do
not have matches in Orders, those rows
will NOT be listed.
LEFT JOIN
•
Returns all rows from the left table, even if there are no matches in the right table
SELECT column_name(s)
FROM table_name1 LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name;
SELECT persons.lastname, persons.firstname, orders.orderno
FROM persons LEFT JOIN orders
ON persons.P_Id=orders.P_Id
ORDER BY persons.lastname;
In some
databases,
LEFT JOIN is
called LEFT
OUTER JOIN
RIGHT JOIN
•
Returns all rows from the right table (table 2), even if there are no matches in the left table (table 1)
SELECT column_name(s)
FROM table_name1 RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name;
SELECT persons.lastname, persons.firstname, orders.orderno
FROM persons RIGHT JOIN orders
ON persons.P_Id=orders.P_Id
ORDER BY persons.lastname;
In some
databases,
RIGHT JOIN is
called RIGHT
OUTER JOIN
FULL JOIN
•
Return rows when there is a match in one of the tables
SELECT column_name(s)
FROM table_name1 FULL JOIN table_name2
ON
table_name1.column_name=table_name2.column_name;
FULL JOIN is
not
supported
by MySQL.
SELECT persons.lastname, persons.firstname, orders.orderno
FROM persons FULL JOIN orders
ON persons.P_Id=orders.P_Id
ORDER BY persons.lastname;
FULL JOIN =
LEFT JOIN
UNION
RIGHT JOIN
SELECT persons.lastname, persons.firstname, orders.orderno
FROM persons LEFT JOIN orders
ON persons.P_Id=orders.P_Id
UNION
SELECT persons.lastname, persons.firstname, orders.orderno
FROM persons RIGHT JOIN orders
ON persons.P_Id=orders.P_Id
UNION operator
• Combines two or more SELECT statements
• Each SELECT statement must have the same columns (same name, same
data types, in the same order)
• UNION selects only distinct values by default. To allow duplicate values,
use UNION ALL.
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2;
SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2;
SELECT P_Id FROM persons
UNION
SELECT P_Id FROM orders;
CREATE DATABASE
• Used to create a database
CREATE DATABASE database_name;
CREATE DATABASE my_db;
CREATE TABLE
• Used to create a table within a database
CREATE TABLE table_name
(column_name1 data_type,
column_name2 data_type,
column_name3 data_type,
….);
CREATE TABLE persons (
P_Id
INT,
LastName
VARCHAR(25),
FirstName
VARCHAR(25),
Address
VARCHAR(25),
City
VARCHAR(15),
PRIMARY KEY (P_Id)
);
Constraints
• Used to limit the type of data that can go into a table
• Can be specified when a table is created (with the CREATE TABLE
statement) or after the table is created (with the ALTER TABLE
statement)
• Type of constraints:
–
–
–
–
–
–
NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK
DEFAULT
NOT NULL
• Enforce a column to not accept NULL values
CREATE TABLE Persons
(
P_Id
INT NOT NULL,
LastName
varchar(255) NOT NULL,
FirstName
varchar(255),
Address
varchar(255),
City
varchar(255)
);
UNIQUE
• Uniquely identifies each record in a database
table
• UNIQUE and PRIMARY KEY both provide a
guarantee for uniqueness for a column or set
of columns
• A PRIMARY KEY constraint automatically has a
UNIQUE constraint defined on it.
UNIQUE
CREATE TABLE Persons
(
P_Id
INT NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City
varchar(255)
MySQL
UNIQUE (P_Id)
);
CREATE TABLE Persons
(
P_Id
INT NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City
varchar(255)
);
SQL Server/Oracle/MS Access
CREATE TABLE Persons
(
MySQL/SQL Server/Oracle/MS Access
P_Id
INT NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City
varchar(255)
CONSTRAINT uc_PersonID UNIQUE (P_Id, LastName)
);
UNIQUE
When Persons table has already been created,
use Alter to add new constraints.
ALTER TABLE Persons
ADD UNIQUE (P_Id)
ALTER TABLE Persons
ADD CONSTRAINT un_PersonID UNIQUE (P_Id, LastName)
To drop a UNIQUE constraint
ALTER TABLE Persons
DROP INDEX un_PersonID
MySQL
ALTER TABLE Persons
SQL Server/Oracle/MS Access
DROP CONSTRAINT un_PersonID
PRIMARY KEY
• Each table should have one and only one primary key
• Primary key should be unique and does not contain NULL values
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)
MySQL
);
CREATE TABLE Persons
(
P_Id
INT NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City
varchar(255)
);
SQL Server/Oracle/MS Access
CREATE TABLE Persons
( P_Id
INT NOT NULL,
MySQL/SQL Server/Oracle/MS Access
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City
varchar(255)
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id, LastName) );
PRIMARY KEY
When Persons table has already been created,
use Alter to add new constraints.
ALTER TABLE Persons
ADD PRIMARY KEY (P_Id)
ALTER TABLE Persons
ADD CONSTRAINT un_PersonID PRIMARY KEY (P_Id, LastName)
To drop a constraint
ALTER TABLE Persons
DROP PRIMARY KEY
MySQL
ALTER TABLE Persons
SQL Server/Oracle/MS Access
DROP CONSTRAINT pk_PersonID
FOREIGN KEY
•
•
A foreign key in one table points to a primary key in another table
The foreign key constraint prevents invalid data from being inserted into the foreign key
column because it has to be one of the values contained in the table it points to.
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)
MySQL
);
CREATE TABLE Orders
(
O_Id
INT NOT NULL PRIMARY KEY,
OrderNo INT NOT NULL,
P_Id
INT FOREIGN KEY
REFERENCES Persons(P_Id)
);
SQL Server/Oracle/MS Access
CREATE TABLE Orders
( O_Id
INT NOT NULL,
MySQL/SQL Server/Oracle/MS Access
OrderNo INT NOT NULL,
P_Id
INT,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id) REFERENCES Persons (P_Id));
FOREIGN KEY
When Orders table has already been created, use
Alter to add new constraints.
ALTER TABLE Orders
ADD FOREIGN KEY (P_Id)
REFERENCES Persons (P_Id)
ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
To drop a constraint
MySQL
ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders
ALTER TABLE Orders
SQL Server/Oracle/MS Access
DROP CONSTRAINT fk_PerOrders
CHECK Constraint
• Used to limit the value range of a column
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CHECK (P_Id>0)
MySQL
)
CREATE TABLE Persons
(
P_Id int NOT NULL CHECK (P_Id>0),
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
SQL Server/Oracle/MS Access
CREATE TABLE Persons
(P_Id int NOT NULL,
MySQL/SQL Server/Oracle/MS Access
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes‘));
CHECK Constraint
When a table has already been created, use Alter
to add new constraints.
ALTER TABLE Persons
ADD CHECK (P_Id>0)
ALTER TABLE Persons
ADD CONSTRAINT chk_Person
CHECK (P_Id>0 AND City=‘Sandnes’)
To drop a constraint
ALTER TABLE Persons
DROP CONSTRAINT chk_Person
SQL Server/Oracle/MS Access
DEFAULT Constraint
• Used to insert a default value into a column
CREATE TABLE Persons
CREATE TABLE Orders
(
(
P_Id int NOT NULL,
O_Id int NOT NULL,
LastName varchar(255) NOT NULL,
OrderNo int NOT NULL,
FirstName varchar(255),
P_Id int,
Address varchar(255),
OrderDate date DEFAULT GETDATE()
City varchar(255) DEFAULT 'Sandnes'
)
MySQL/SQL Server/Oracle/MS Access
)
MySQL/SQL Server/Oracle/MS Access
DEFAULT Constrain
When a table has already been created, use Alter
to add new constraints.
ALTER TABLE Persons
ALTER City SET DEFAULT ‘Sandnes’
MySQL
SQL Server/Oracle/MS Access
To drop a constraint
ALTER TABLE Persons
ALTER City DROP DEFAULT
ALTER TABLE Persons
ALTER COLUMN City SET DEFAULT
‘Sandnes’
MySQL
ALTER TABLE Persons
SQL Server/Oracle/MS Access
ALTER COLUMN City DROP DEFAULT
CREATE INDEX statement
• An index can be created in a table to find data
more quickly and efficiently.
• The users cannot see the indexes.
• Indexes can speed up searches/queries
• Updating a table with indexes takes more time
than updating a table without indexes.
Because indexes also need to update.
• So please create indexes on columns (and
tables) that will be frequently searched.
CREATE INDEX statement
CREATE INDEX index_name
ON table_name (column_name)
CREATE UNIQUE INDEX index_name
ON table_name (column_name)
CREATE INDEX PIndex
ON Persons (lastname)
CREATE INDEX PIndex
ON Persons (LastName, FirstName)
Duplicate values are allowed
Duplicate values are not allowed
SQL DROP INDEX, DROP TABLE,
DROP DATABASE
MS Access
DROP INDEX index_name ON table_name
DROP INDEX table_name.index_name
DROP INDEX index_name
MS SQL Server
DB2/Oracle
ALTER TABLE table_name DROP INDEX index_name
MySQL
DROP TABLE table_name
DROP DATABASE database_name
TRUNCATE TABLE table_name
Delete data inside the table,
not the table itself
SQL ALTER TABLE statement
• Alter table statement is used to add, delete, or modify columns in an
existing table
ALTER TABLE table_name
ADD column_name datatype
ALTER TABLE table_name
DROP COLUMN column_name
To add a column in a table
To delete a column in a table (some database
systems don’t allow deleting a column)
ALTER TABLE table_name
ALTER COLUMN column_name datatype
To change the data type of a column
in a table
SQL ALTER TABLE statement
ALTER TABLE Persons
ADD DateOfBirth date
ALTER TABLE Persons
DROP COLUMN DateOfBirth
SQL AUTO INCREMENT
• Auto-increment allows a unique number to be generated
when a new record is inserted into a table
MySQL
CREATE TABLE Persons
(
P_Id int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)
ALTER TABLE Persons
AUTO_INCREMENT=100
To start the auto_increment
with another value
INSERT INTO Persons (FirstName,
LastName) VALUES (‘Lars’,
‘Monsen’)
SQL AUTO INCREMENT
• Auto-increment allows a unique number to be generated
when a new record is inserted into a table
SQL Server
CREATE TABLE Persons
(
P_Id int PRIMARY KEY IDENTITY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
To start at 10 and increment by 5
IDENTITY(10,5)
ALTER TABLE Persons
AUTO_INCREMENT=100
To start the auto_increment
with another value
INSERT INTO Persons (FirstName,
LastName) VALUES (‘Lars’,
‘Monsen’)
SQL AUTO INCREMENT
• Auto-increment allows a unique number to be generated
when a new record is inserted into a table
Access
CREATE TABLE Persons
(
P_Id PRIMARY KEY AUTOINCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
To start at 10 and increment by 5
AUTOINCREMENT(10,5)
INSERT INTO Persons (FirstName,
LastName) VALUES (‘Lars’,
‘Monsen’)
SQL AUTO INCREMENT
• Auto-increment allows a unique number to be generated
when a new record is inserted into a table
Oracle
CREATE SEQUENCE seq_person
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10
INSERT INTO Persons (P_Id,FirstName,LastName)
VALUES (seq_person.nextval,'Lars','Monsen')
To start at 10 and increment by 5
AUTOINCREMENT(10,5)
SQL Views
• A view is a virtual table based on the result-set of an SQL statement
• A view just looks like a real table with fields and records from one
or more real tables in the database
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
CREATE VIEW Sandnes AS
SELECT LastName, FirstName
FROM Persons
WHERE city=‘Sandnes’
CREATE VIEW SandnesLastName AS
SELECT LastName
FROM Sandnes
SELECT * FROM SandnesLastName
SELECT * FROM Sandnes
SQL Views
• A view can be updated or dropped
CREATE OR REPLACE OR ALTER VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
ALTER VIEW Sandnes AS
SELECT LastName, FirstName, Address
FROM Persons
WHERE city=‘Sandnes’
SELECT * FROM Sandnes
DROP VIEW view_name
DROP VIEW Sandnes
SQL FUNCTIONS
SQL Functions
• SQL Aggregate functions: return a single value
calculated from values in a column
– AVG() – Returns the average value
– COUNT() – Returns the number of rows
– FIRST() – Returns the first value
– LAST() – Returns the last value
– MAX() – Returns the largest value
– MIN() – Returns the smallest value
– SUM() – Returns the sum
SQL Functions
• SQL Scalar functions: returns a single value based
on the input value
–
–
–
–
–
UCASE() – Converts a field to upper case
LCASE() – Converts a field to lower case
MID() – Extracts characters from a text field
LEN() – Returns the length of a text field
ROUND() – Rounds a numeric field to the number of
decimals specified
– NOW() – Returns the current system date and time
– FORMAT() – Formats how a field is to be displayed
Create Products Table
Persons Table
P_Id
ProductName UnitPrice
UnitsInStock
UnitsOnOrder
1
Jarlsberg
10.45
16
15
2
Mascarpone
32.56
23
3
Gorgonzola
15.67
9
CREATE TABLE products (
P_Id
ProductName
UnitPrice
UnitsInStock
UnitsOnOrder
PRIMARY KEY (P_Id)
);
INT,
VARCHAR(25),
decimal,
INT,
INT,
INSERT INTO products VALUES (1, ‘Jarlsberg', 10.45, 16, 15);
INSERT INTO products VALUES (2, ‘Mascarpone', 32.56, 23,NULL);
INSERT INTO products VALUES (3, ‘Gorgonzola', 15.67, 9, 20);
20
SQL AVG() Function
• AVG() returns the average value of a numeric column
SELECT AVG(column_name) FROM table_name
SELECT AVG(UnitPrice) AS UnitAverage FROM Products
SELECT ProductName FROM products
WHERE UnitPrice>( SELECT AVG(UnitPrice) FROM Products)
SQL COUNT() Function
• COUNT() returns the number of rows that matches a specified criteria
SELECT COUNT(column_name) FROM table_name
SELECT COUNT(*) FROM Products
SELECT COUNT(DISTINCT column_name) FROM table_name
SELECT COUNT(DISTINCT city) As CityName From persons
SQL MAX() Function
• MAX() returns the largest value of the selected column
SELECT MAX(column_name) FROM table_name
SELECT MAX(UnitPrice) AS LargestUnitPrice FROM Products
SQL SUM() Function
• SUM() returns the total sum of the selected column
SELECT SUM(column_name) FROM table_name
SELECT SUM(UnitPrice) AS TotalUnitPrice FROM Products
Create CumOrders Table
O_Id
OrderDate
OrderPrice
Customer
1
2008/11/12
1000
Hansen
2
2008/10/23
1600
Nilsen
3
2008/09/02
700
Hansen
4
2008/09/03
300
Hansen
5
2008/08/30
2000
Jensen
6
2008/10/04
100
Nilsen
CREATE TABLE CumOrders (
O_Id
INT,
OrderDate
Date,
OrderPrice
INT,
Customer
Varchar(30),
PRIMARY KEY (O_Id) );
INSERT INTO cumorders VALUES (1, ‘2011-08-06’, 1000, ‘Hansen’);
INSERT INTO cumorders VALUES (2, ‘2011-08-07’, 1600, ‘Nilsen’);
INSERT INTO cumorders VALUES (3, ‘2011-08-08’, 700, ‘Hansen’);
INSERT INTO cumorders VALUES (4, ‘2011-08-09’, 300, ‘Hansen’);
INSERT INTO cumorders VALUES (5, ‘2011-08-10’, 2000, ‘Jensen’);
INSERT INTO cumorders VALUES (6, ‘2011-08-11’, 100, ‘Nilsen’);
SQL GROUP BY Statement
• Aggregate functions often need an added GROUP BY
statement to group the result-set by one or more columns
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
SELECT Customer, SUM(OrderPrice) FROM CumOrders
GROUP BY Customer
SELECT Customer, OrderDate, SUM(OrderPrice) FROM CumOrders
GROUP BY Customer, OrderDate
SQL HAVING Clause
• HAVING was added to SQL because the WHERE keyword could
not be used wit the aggregate functions
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value
SELECT Customer, SUM(OrderPrice) FROM CumOrders
GROUP BY Customer
HAVING SUM(OrderPrice)<2000
SQL HAVING Clause
SELECT Customer, SUM(OrderPrice) FROM CumOrders
WHERE Customer=‘Hansen’ OR Customer=‘Jensen’
GROUP BY Customer
HAVING SUM(OrderPrice)>1500
SQL UCASE() Function
• UCASE() converts the value of a field to uppercase.
SELECT UCASE(column_name) FROM table_name
SELECT UCASE(LastName) as LastName From Persons
SQL LCASE() Function
• LCASE() converts the value of a field to lowercase.
SELECT LCASE(column_name) FROM table_name
SELECT LCASE(LastName) as LastName From Persons
SQL MID() Function
• MID() extracts characters from a text field
SELECT MID(column_name, start[, length]) FROM table_name
Parameter
Description
column_name Required. The field to extract characters from
start
Required. Specifies the starting position (starts at 1)
length
Optional. The number of characters to return. If omitted, the
MID() function returns the rest of the text
SELECT MID(City, 1, 4) as SmallCity From Persons
MySQL
SELECT substr(City, 1, 4) as SmallCity From Persons
PostgreSQL
PostgreSQL: http://www.postgresql.org/docs/9.1/static/functions-string.html
SQL LENGTH() Function
• LENGTH() returns the length of the value in a text field.
SELECT LENGTH(column_name) FROM table_name
SELECT LENGTH(Address) as LengthOfAddress From Persons
SQL ROUND() Function
• ROUND() rounds a numeric field to the number of decimals specified
SELECT ROUND(column_name, decimals) FROM table_name
Parameter
Description
column_name Required. The field to round.
decimals
Required. Specifies the number of decimals to be returned.
UPDATE Products
SET UnitPrice=10.49
WHERE P_Id=1
SELECT ROUND(UnitPrice, 0) as Price From Products
SQL Date Functions
• Introduces some build-in functions to deal with dates
Function
Description
NOW()
Returns the current date and time
CURDATE()
Returns the current date
CURTIME()
Returns the current time
DATE()
Extracts the date part of a date or date/time expression
EXTRACT()
Returns a single part of a date/time
DATE_ADD()
Adds a specified time interval to a date
DATE_SUB()
Subtracts a specified time interval from a date
DATEDIFF()
Returns the number of days between two dates
DATE_FORMAT()
Displays date/time data in different formats
MySQL Date
Functions
PostgreSQL: http://www.postgresql.org/docs/8.2/static/functions-datetime.html
MySQL NOW() Function
• NOW() returns the current date and time
SELECT NOW(), CURDATE(), CURTIME()
MySQL
SELECT NOW()
CREATE TABLE Orders
(
OrderId int NOT NULL,
ProductName varchar(50) NOT NULL,
OrderDate datetime NOT NULL,
PRIMARY KEY (OrderId)
)
INSERT INTO orders VALUES (1, ‘Jarlsberg Cheese’, NOW())
SELECT ProductName, UnitPrice, NOW() as PerDate FROM Products
PostgreSQL
MySQL CURDATE() Function
• CURDATE() returns the current date
SELECT NOW(), CURDATE(), CURTIME()
CREATE TABLE Orders
(
OrderId int NOT NULL,
ProductName varchar(50) NOT NULL,
OrderDate datetime NOT NULL,
PRIMARY KEY (OrderId)
)
INSERT INTO orders VALUES (2, ‘Jarlsberg Cheese’, CURDATE())
MySQL DATE() Function
• DATE() function extracts the date part of a date or date/time expression
DATE(date)
SELECT ProductName, DATE(OrderDate) as OrderDate
FROM Orders
WHERE OrderId=1
MySQL EXTRACT() Function
• The EXTRACT() function returns a single part of a date/time, such as year,
month, day, hour, minute, etc.
• Unit value can be: SECOND, MINUTE, HOUR, DAY, WEEK, MONTH,
QUARTER, YEAR, or any combination of them
EXTRACT(unit FROM date)
SELECT EXTRACT(YEAR FROM OrderDate) as OrderYear,
EXTRACT(Month FROM OrderDate) as OrderMonth,
EXTRACT(DAY FROM OrderDate) as OrderDay
FROM Orders
WHERE OrderID=1
MySQL DATE_ADD() Function
• The DATE_ADD() function adds a specified time interval to a date.
DATE_ADD(date, INTERVAL expr type)
SELECT OrderID, DATE_ADD(OrderDate, INTERVAL 45 DAY) AS OrderPayDate
FROM Orders
MySQL DATE_SUB() Function
• The DATE_SUB() function subtracts a specified time interval from a date.
DATE_SUB(date, INTERVAL expr type)
SELECT OrderID, DATE_SUB(OrderDate, INTERVAL 5 DAY) AS SubtractDate
FROM Orders
MySQL DATEIFF() Function
• The DATEDIFF() function returns the time between two dates.
DATEDIFF(date1, date2)
SELECT DATEDIFF ( ‘2010-11-30’, ‘2010-11-29’) AS DiffDate
MySQL DATE_FORMAT() Function
• The DATE_FORMAT() function displays the date/time in different formats.
DATE_FORMAT(date, format)
SELECT DATE_FORMAT(NOW(), ‘%m-%d-%y’)
SQL Date Functions
• Introduces some build-in functions to deal with dates
SQL Server Date
Functions
Function
Description
GETDATE()
Returns the current date and time
DATEPART()
Returns a single part of a date/time
DATEADD()
Adds or subtracts a specified time interval from a date
DATEDIFF()
Returns the time between two dates
CONVERT()
Displays date/time data in different formats
SQL Date Datatypes
• MySQL
– Date: YYYY-MM-DD
– Datetime: YYYY-MM-DD HH:MM:SS
– Timestamp: YYYY-MM-DD HH:MM:SS
– Year: YYYY or YY
SELECT * FROM Orders WHERE OrderDate=‘2011-08-06’
SQL NULL Values
• NULL values represent missing unknown data.
• NULL is used as a placeholder for unknown or inapplicable
values
• NULL vs. 0
INSERT INTO persons VALUES (5, 'Ding', 'Ying', NULL, NULL);
SELECT * FROM Persons
WHERE Address IS NULL
SELECT * FROM Persons
WHERE Address IS NOT NULL
DELETE FROM Persons WHERE P_ID=5
SQL NULL Functions
SELECT ProdcutName, UnitPrice*(UnitsInStock+UnitsOnOrder)
FROM Products
ISNULL(), NVL(), IFNULL(), COALESCE() are used to treat NULL values.
Below all makes nulls to be zero
SELECT ProductName, UnitPrice*(UnitsInStock+ISNULL(UnitsOnOrder,0))
FROM Products
SQL Server/Access
SELECT ProductName, UnitPrice*(UnitsInStock+NVL(UnitsOnOrder,0))
FROM Products
Oracle
SELECT ProductName, UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0)) AS Price
FROM Products
MySQL
SELECT ProductName, UnitPrice*(UnitsInStock+COALESCE(UnitsOnOrder,0))AS Price
FROM Products
SQL DATATYPES
SQL Datatype
Access
Data type
Text
Memo
Byte
Integer
Long
Single
Double
Currency
AutoNumber
Date/Time
Yes/No
OLE Object
Hyperlink
Lookup Wizard
Description
Use for text or combinations of text and numbers. 255 characters maximum
Memo is used for larger amounts of text. Stores up to 65,536 characters.
Note: You cannot sort a memo field. However, they are searchable
Allows whole numbers from 0 to 255
Allows whole numbers between -32,768 and 32,767
Allows whole numbers between -2,147,483,648 and 2,147,483,647
Single precision floating-point. Will handle most decimals
Double precision floating-point. Will handle most decimals
Use for currency. Holds up to 15 digits of whole dollars, plus 4 decimal
places. Tip: You can choose which country's currency to use
AutoNumber fields automatically give each record its own number, usually
starting at 1
Use for dates and times
A logical field can be displayed as Yes/No, True/False, or On/Off. In code, use
the constants True and False (equivalent to -1 and 0). Note: Null values are
not allowed in Yes/No fields
Can store pictures, audio, video, or other BLOBs (Binary Large OBjects)
Contain links to other files, including web pages
Let you type a list of options, which can then be chosen from a drop-down
list
Storage
1 byte
2 bytes
4 bytes
4 bytes
8 bytes
8 bytes
4 bytes
8 bytes
1 bit
up to 1GB
4 bytes
SQL Datatype
MySQL: Text
Data type
CHAR(size)
Description
Holds a fixed length string (can contain letters, numbers, and special characters).
The fixed size is specified in parenthesis. Can store up to 255 characters
VARCHAR(size)
TINYTEXT
Holds a variable length string (can contain letters, numbers, and special
characters). The maximum size is specified in parenthesis. Can store up to 255
characters. Note: If you put a greater value than 255 it will be converted to a
TEXT type
Holds a string with a maximum length of 255 characters
TEXT
Holds a string with a maximum length of 65,535 characters
BLOB
For BLOBs (Binary Large OBjects). Holds up to 65,535 bytes of data
MEDIUMTEXT
Holds a string with a maximum length of 16,777,215 characters
MEDIUMBLOB
For BLOBs (Binary Large OBjects). Holds up to 16,777,215 bytes of data
LONGTEXT
Holds a string with a maximum length of 4,294,967,295 characters
LONGBLOB
For BLOBs (Binary Large OBjects). Holds up to 4,294,967,295 bytes of data
ENUM(x,y,z,etc.)
Let you enter a list of possible values. You can list up to 65535 values in an ENUM
list. If a value is inserted that is not in the list, a blank value will be inserted.Note:
The values are sorted in the order you enter them.
You enter the possible values in this format: ENUM('X','Y','Z')
Similar to ENUM except that SET may contain up to 64 list items and can store
more than one choice
SET
SQL Datatype
Data type
TINYINT(size)
SMALLINT(size)
MEDIUMINT(size)
INT(size)
BIGINT(size)
FLOAT(size,d)
DOUBLE(size,d)
DECIMAL(size,d)
MySQL: Number
Description
-128 to 127 normal. 0 to 255 UNSIGNED*. The maximum number of digits may be
specified in parenthesis
-32768 to 32767 normal. 0 to 65535 UNSIGNED*. The maximum number of digits may
be specified in parenthesis
-8388608 to 8388607 normal. 0 to 16777215 UNSIGNED*. The maximum number of
digits may be specified in parenthesis
-2147483648 to 2147483647 normal. 0 to 4294967295 UNSIGNED*. The maximum
number of digits may be specified in parenthesis
-9223372036854775808 to 9223372036854775807 normal. 0 to
18446744073709551615 UNSIGNED*. The maximum number of digits may be specified
in parenthesis
A small number with a floating decimal point. The maximum number of digits may be
specified in the size parameter. The maximum number of digits to the right of the
decimal point is specified in the d parameter
A large number with a floating decimal point. The maximum number of digits may be
specified in the size parameter. The maximum number of digits to the right of the
decimal point is specified in the d parameter
A DOUBLE stored as a string , allowing for a fixed decimal point. The maximum number
of digits may be specified in the size parameter. The maximum number of digits to the
right of the decimal point is specified in the d parameter
Unsigned: integer cannot be negative
SQL Datatype
MySQL: Date
Data type
Description
DATE()
A date. Format: YYYY-MM-DDNote: The supported range is from
'1000-01-01' to '9999-12-31'
DATETIME()
*A date and time combination. Format: YYYY-MM-DD
HH:MM:SSNote: The supported range is from '1000-01-01 00:00:00'
to '9999-12-31 23:59:59'
TIMESTAMP()
*A timestamp. TIMESTAMP values are stored as the number of
seconds since the Unix epoch ('1970-01-01 00:00:00' UTC). Format:
YYYY-MM-DD HH:MM:SSNote: The supported range is from '197001-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC
TIME()
A time. Format: HH:MM:SSNote: The supported range is from '838:59:59' to '838:59:59'
YEAR()
A year in two-digit or four-digit format.Note: Values allowed in fourdigit format: 1901 to 2155. Values allowed in two-digit format: 70 to
69, representing years from 1970 to 2069
Homework 5
• Create one database containing several tables
using PostgreSQL
SELECT * FROM Persons
WHERE lastname IN (SELECT lastname
• 10 SQL queries
from Persons
where city=‘Sandnes’)
– Embedded queries
– GroupBy queries
SELECT Customer, SUM(OrderPrice)
– Join queries
FROM CumOrders
GROUP BY Customer
HAVING SUM(OrderPrice)<2000
SELECT persons.lastname, persons.firstname, orders.orderNo
From Persons INNER JOIN Orders
ON persons.P_Id=orders.P_Id
ORDER BY persons.Lastname;
SQL Quiz
• http://www.w3schools.com/sql/sql_quiz.asp