Structured Query Language (SQL)
Structured Query Language (SQL)
1
SQL
SQL stands for Structured Query Language
It is standard language for querying and manipulating data
2
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
3
Keep in Mind That...
• SQL is NOT case sensitive: select is the same as
SELECT
• In this tutorial we will write all SQL keywords in
upper-case.
• 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 serve 4
Table name Attribute names
Tables in SQL
Product
Tuples or rows 5
Tables Explained
• The schema of a table is the table name and
its attributes:
Product(PName, Price, Category, Manfacturer)
SELECT
SELECT **
FROM
FROM Product
Product
WHERE
WHERE category=‘Gadgets’
category=‘Gadgets’
PName Price Category Manufacturer
Gizmo $19.99 Gadgets GizmoWorks
Powergizmo $29.99 Gadgets GizmoWorks
“selection”
7
Simple SQL Query
Product PName Price Category Manufacturer
Gizmo $19.99 Gadgets GizmoWorks
Powergizmo $29.99 Gadgets GizmoWorks
SingleTouch $149.99 Photography Canon
MultiTouch $203.99 Household Hitachi
SELECT
SELECT PName,
PName,Price,
Price,Manufacturer
Manufacturer
FROM
FROM Product
Product
WHERE
WHERE Price
Price>>100
100
PName Price Manufacturer
“selection” and SingleTouch $149.99 Canon
“projection” MultiTouch $203.99 Hitachi
8
CREATE
CREATE DATABASE dbname;
• The following SQL statement creates a database called "my_db":
CREATE DATABASE my_db;
The SQL CREATE TABLE Statement
The CREATE TABLE statement is used to create a table in a database.
Tables are organized into rows and columns; and each table must have a name.
Syntax
CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
);
9
Drop and Trancate
11
SQL Constraints
• SQL constraints are used to specify rules for the data in a table.
• If there is any violation between the constraint and the data action,
the action is aborted by the constraint.
• Constraints can be specified when the table is created (inside the
CREATE TABLE statement) or after the table is created (inside the
ALTER TABLE statement).
12
• In SQL, we have the following constraints:
• NOT NULL - Indicates that a column cannot store NULL value
• UNIQUE - Ensures that each row for a column must have a unique value
• PRIMARY KEY - A combination of a NOT NULL and UNIQUE.
Ensures that a column (or combination of two or more columns) have a
unique identity which helps to find a particular record in a table more
easily and quickly
• FOREIGN KEY - Ensure the referential integrity of the data in one
table to match values in another table
• CHECK - Ensures that the value in a column meets a specific condition
• DEFAULT - Specifies a default value for a column
13
Unique
• The UNIQUE constraint ensures that all values in a column are different.
• Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for
uniqueness for a column or set of columns.
• A PRIMARY KEY constraint automatically has a UNIQUE constraint.
CREATE TABLE Persons (
ID int UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);
Alter
ALTER TABLE Persons
ADD UNIQUE (ID);
14
Primary key
CREATE TABLE Persons
(
P_Id int PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
Or
ALTER TABLE Persons
ADD PRIMARY KEY (P_Id)
To drop primary key
ALTER TABLE Persons
DROP PRIMARY KEY
15
Foreign key "Orders" table:
"Persons" table:
O_Id Order P_Id
P_Id LastName FirstName Address City No
1 Hansen Ola Timoteivn 10 Sandnes 1 77895 3
2 Svendson Tove Borgvn 23 Sandnes 2 44678 3
3 Pettersen Kari Storgt 20 Stavanger 3 22456 2
4 24562 1
Drop
ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT
19
SQL AUTO INCREMENT Field
• Auto-increment allows a unique number to be generated when a
new record is inserted into a table.
• Very often we would like the value of the primary key field to be
created automatically every time a new record is inserted.
• We would like to create an auto-increment field in a table.
CREATE TABLE Persons
(
ID int IDENTITY(1,1) PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
In the example above, the starting value for IDENTITY is 1, and
20
it will increment by 1 for each new record.
INSERT INTO Statement
• The INSERT INTO statement is used to insert new records in a
table.
• It is possible to write the INSERT INTO statement in two forms.
• The first form does not specify the column names where the data
will be inserted, only their values:
INSERT INTO table_name
VALUES (value1,value2,value3,...);
• The second form specifies both the column names and the values
to be inserted:
INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);
Eg. INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal','Tom B. Erichsen','Skagen 21','Stavanger','4006','Norway');
21
UPDATE Statement
• The UPDATE statement is used to update existing records in a
table.
UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;
Eg
UPDATE Customers
SET ContactName='Alfred Schmidt', City='Hamburg'
WHERE CustomerName=Abebe';
22
DELETE Statement
• The DELETE statement is used to delete rows in a table.
DELETE FROM table_name
WHERE some_column=some_value;
• Eg
DELETE FROM person
WHERE PID=10
23
SELECT Statement
• The SELECT statement is used to select data from a database.
SELECT column_name,column_name
FROM table_name;
Or to select all columns
SELECT * FROM table_name;
WHERE Clause
The WHERE clause is used to extract only those records that fulfill a specified
criterion.
SELECT column_name,column_name
FROM table_name
WHERE column_name operator value;
Eg
SELECT * FROM Customers
WHERE Country=‘Hawassa'; 24
SELECT Statement cont….
• SQL requires single quotes around text values
• However, numeric fields should not be enclosed in quotes:
• Eg
SELECT * FROM Customers
Operators in The WHERE Clause
WHERE CustomerID=1;
Operator Description
= Equal
<> OR != Not equal. Note: In some versions of SQL this operator may be written
as !=
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
BETWEEN Between an inclusive range
LIKE Search for a pattern
25
IN To specify multiple possible values for a column
AND & OR Operators
• The AND & OR operators are used to filter records based on more
than one condition.
• The AND operator displays a record if both the first condition AND
the second condition are true.
• The OR operator displays a record if either the first condition OR
the second condition is true.
• Eg SELECT * FROM Customers
SELECT * FROM Customers WHERE City=‘AddisAbaba'
WHERE Country='Germany' OR City=‘Jima';
AND City='Berlin';
28
SQL Wildcards
• A wildcard character can be used to substitute for any other
character(s) in a string. Wildcard Description
% A substitute for zero or more characters
_ A substitute for a single character
[charlist] Sets and ranges of characters to match
[^charlist] Matches only a character NOT specified
or within the brackets
[!charlist]
• The following SQL statement selects all customers with a City
starting with any character, followed by "erlin":
SELECT * FROM Customers
WHERE City LIKE '_erlin';
• The following SQL statement selects all customers with a City
starting with "b", "s", or "p":
SELECT * FROM Customers
WHERE City LIKE '[bsp]%'; 29
BETWEEN Operator
• The BETWEEN operator is used to select values within a range.
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
• Eg
SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;
The following SQL statement selects all products with a ProductName beginning
with any of the letter BETWEEN 'C' and 'M':
SELECT * FROM Products
WHERE ProductName BETWEEN 'C' AND 'M';
NOT BETWEEN Operator Example
To display the products outside the range of the previous example, use NOT
BETWEEN:
SELECT * FROM Products
WHERE Price NOT BETWEEN 10 AND 20; 30
Aliases
• SQL aliases are used to temporarily rename a table or a column
heading.
SQL Alias Syntax for Columns
SELECT column_name AS alias_name
FROM table_name;
SQL Alias Syntax for Tables
SELECT column_name(s)
FROM table_name AS alias_name;
Eg.
SELECT CustomerName AS Customer, ContactName AS ContactPerson
FROM Customers;
In the following SQL statement we combine four columns (Address, City,
PostalCode, and Country) and create an alias named "Address":
SELECT CustomerName, Address+’-'+City+’-'+PostalCode+’- '+Country AS
Address
FROM Customers; 31
Joins
• An SQL JOIN clause is used to combine rows from two or more
tables, based on a common field between them.
• The most common type of join is: SQL INNER JOIN (simple join).
An SQL INNER JOIN returns all rows from multiple tables where
the join condition is met.
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID=Customers.CustomerID;
Different SQL JOINs
INNER JOIN: Returns all rows when there is at least one match in BOTH tables
LEFT JOIN: Return all rows from the left table, and the matched rows from the right
table
RIGHT JOIN: Return all rows from the right table, and the matched rows from the
left table
FULL JOIN: Return all rows when there is a match in ONE of the tables
32
JOIN example
SQL LEFT JOIN Syntax
SELECT column_name(s) FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;
Eg
SELECT Customers.CustomerName, Orders.OrderID FROM Customers
LEFT JOIN Orders ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;
33
SQL INNER JOIN
• The INNER JOIN keyword return rows when there is at least one match in both tables.
SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name
34
RIGHT JOIN
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
RIGHT JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName
35
LEFT JOIN
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName
36
FULL JOIN
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
FULL JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName
37
Functions
• SQL aggregate functions return a single value, calculated from
values in a column.
• The common aggregating functions are
– 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
38
Cont..
• The AVG() Function
The AVG() function returns the average value of a numeric column.
SQL AVG() Syntax
SELECT AVG(column_name) FROM table_name
Eg
SELECT AVG(Price) AS PriceAverage FROM Products;
SQL COUNT()
The COUNT(column_name) function returns the number of values (NULL
values will not be counted) of the specified column:
SELECT COUNT(column_name) FROM table_name;
Eg.
SELECT COUNT(CustomerID) AS OrdersFromCustomerID7 FROM Orders
WHERE CustomerID=7;
39
MAX , MIN, SUM
The MAX() Function
• The MAX() function returns the largest value of the selected column.
SELECT MAX(column_name) FROM table_name
Eg.
SELECT MAX(Price) AS HighestPrice FROM Products;
The MIN() Function
The MIN() function returns the smallest value of the selected column.
SELECT MIN(column_name) FROM table_name;
Eg.
SELECT MIN(Price) AS SmallestOrderPrice FROM Products;
The SUM() Function
The SUM() function returns the total sum of a numeric column.
SELECT SUM(column_name) FROM table_name;
Eg.
SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails; 40
SQL Views
A view is a virtual table.
SQL CREATE VIEW Statement
• In SQL, a view is a virtual table based on the result-set of an SQL
statement.
• A view contains rows and columns, just like a real table. The fields
in a view are fields from one or more real tables in the database.
• You can add SQL functions, WHERE, and JOIN statements to a
view and present the data as if the data were coming from one single
table.
• Note: A view always shows up-to-date data! The database engine
recreates the data, using the view's SQL statement, every time a user
queries a view.
41
SQL Views cont…
SQL CREATE VIEW Syntax
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
Example
CREATE VIEW CurrentProductList AS
SELECT ProductID, ProductName
FROM Products
WHERE Discontinued=‘No’
We can query the view above as follows:
SELECT * FROM CurrentProductList
SQL DROP VIEW Syntax
DROP VIEW view_name
42
Embedded SQL
• Embedded SQL is a method of combining the computing power of
a programming language and the database manipulation
capabilities of SQL.
• Embedded SQL statements are SQL statements written inline with
the program source code, of the host language.
• The embedded SQL statements are parsed by an embedded SQL
preprocessor and replaced by host-language calls to a code library.
• The output from the preprocessor is then compiled by the host
compiler.
• This allows programmers to embed SQL statements in programs
written in any number of languages
43
Embedded SQL cont..
• It is when you embed SQL with another language.
• The language that is embedded is known as host language and the
SQL standard which defines the embedding of SQL is known as
embedded SQL.
• For identification of this, we request to the preprocessor via EXEC
SQL statement: EXEC SQL embedded SQL statement END-EXEC
• It can execute the update, insert, delete statement
44
Dynamic SQL
• Dynamic SQL is a programming technique that allows you to
construct SQL statements dynamically at runtime.
• It allows you to create more general purpose and flexible SQL
statement because the full text of the SQL statements may be
unknown at compilation.
• For example, you can use the dynamic SQL to create a stored
procedure that queries data against a table whose name is not
known until runtime.
• Creating a dynamic SQL is simple, you just need to make it a
string as follows:
45
Differences between Static or Embedded and
Dynamic or Interactive SQL: