Lecture 3

Introduction to
SQL

Prof. Aileen Mae B. Coronado, MBA, MIT

 Objectives
Describe the relational model
Understand Query-By-Example (QBE)
Use criteria in QBE
Create calculated columns in QBE
Use functions in QBE
 Objectives (continued)

Sort data in QBE

Join tables in QBE
Update data using QBE
Understand relational algebra
 Relational Databases
A relational database is a collection of
tables
Each entity is stored in its own table
Attributes of an entity become the fields or
columns in the table
Relationships are implemented through
common columns in two or more tables
Should not permit multiple entries
(repeating groups) in a table
 Relational Databases (Con’t)

Relation: two-dimensional table in which:

– Entries are single-valued
– Each column has a distinct name (called the
attribute name)
– All values in a column are values of the same
attribute
– Order of columns is immaterial
– Each row is distinct
– Order of rows is immaterial
Relational Databases (con’t)
Relational database: collection of
relations
Unnormalized relation
– A structure that satisfies all properties of a
relation except for the first item
– Entries contain repeating groups; they are not
single-valued
 Relational Databases (con’t)

Database structure representation

– Write name of the table followed by a list of all
columns within parentheses
– Each table should appear on its own line
– Notation to be used with duplicate column names
within a database: Tablename.Columnname
You qualify the column names
Primary key: column or collection of columns of a
table (relation) that uniquely identifies a given row in
that table
 Introduction to SQL
What is SQL?
– When a user wants to get some information
from a database file, he can issue a query.
– A query is a user–request to retrieve data or
information with a certain condition.
– SQL is a query language that allows user to
specify the conditions. (instead of algorithms)
Introduction to SQL
Concept of SQL

– The user specifies a certain condition.

– The program will go through all the records
in the database file and select those records
that satisfy the condition.(searching).
– Statistical information of the data.
– The result of the query will then be stored in
form of a table.
 Data Definition Language
Allows the specification of:
The schema for each relation, including attribute types.
Integrity constraints
Authorization information for each relation.
Non-standard SQL extensions also allow specification of
– The set of indices to be maintained for each relations.
– The physical storage structure of each relation on disk.
Basic structure of an SQL query
General SELECT, ALL / DISTINCT, *,
Struc ture AS, FROM, WHERE

Comparison IN, BETWEEN, LIKE "% _"

Grouping GROUP BY, HAVING,

COUNT( ), SUM( ), AVG( ), MAX( ), MIN( )
Display Order ORDER BY, ASC / DESC

L ogic al AND, OR, NOT

Operators

Output INTO TABLE / CURSOR

TO FILE [ADDITIVE], TO PRINTER, TO SCREEN
Union UNION
 Simple Criteria
Criteria: conditions that data must satisfy
Criterion: single condition that data must
satisfy
To enter a criterion for a field:
– Include field in the design grid
– Enter criterion in Criteria row for that field
 Simple Criteria (continued)
Comparison operator
– Also called a relational operator
– Used to find something other than an exact match
= (equal to)
> (greater than)
< (less than)
>= (greater than or equal to)
<= (less than or equal to)
NOT (not equal to)
 Compound Criteria
Compound criteria, or compound conditions
– AND criterion: both criteria must be true for the
compound criterion to be true
– OR criterion: either criteria must be true for the
compound criterion to be true
To create an AND criterion in QBE:
– Place the criteria for multiple fields on the same
Criteria row in the design grid
To create an OR criterion in QBE:
– Place the criteria for multiple fields on different Criteria
rows in the design grid
 Computed Fields

Computed field or calculated field

– Result of a calculation on one or more existing
fields
To include a computed field in a query:
– Enter a name for the computed field, followed by a
colon, followed by an expression in one of the
columns in the Field row
Alternative method
– Right-click the column in the Field row, and then
click Zoom to open the Zoom dialog box
– Type the expression in the Zoom dialog box
 Functions
• Built-in functions
– Called aggregate functions in Access

Count StDev (standard

deviation)
Sum
Var (variance)
Avg (average)
First
Max (largest value)
Last
Min (smallest value)
 Grouping
Grouping: creating groups of records that
share some common characteristic
To group records in Access:
– Select Group By operator in the Total row for
the field on which to group
 Sorting
Sorting: listing records in query results in an
ordered way
Sort key: field on which records are sorted
Major sort key
– Also called the primary sort key
– First sort field, when sorting records by more than one
field
Minor sort key
– Also called the secondary sort key
– Second sort field, when sorting records by more than
one field
©2015 Cengage Learning. All Rights Reserved. May not be copied,
scanned, or duplicated, in whole or in part, except for use as
permitted in a license distributed with a certain product or service or
otherwise on a password-protected website for classroom use.
19
 Sorting on Multiple Keys
Specifying more than one sort key in a
query
Major (primary) sort key
– Sort key on the left in the design grid
Minor (secondary) sort key
– Sort key on the right in the design grid
 Using an Update Query
Update query: a query that changes data
– Makes a specified change to all records
satisfying the criteria in the query
To change a query to an update query:
– Click Update button in the Query Type group on
the QUERY TOOLS DESIGN tab
Update To row is added when an update query is
created
– Used to indicate how to update data selected
by the query
 Using a Delete Query

Delete query: permanently deletes all

records satisfying the criteria entered in
the query
To change query type to a delete query:
– Click Delete button in the Query Type group
on the QUERY TOOLS DESIGN tab
Delete row is added
– Indicates this is a delete query
 Create Table Construct
An SQL relation is defined using the create table
command:
create table r (A1 D1, A2 D2, ..., An Dn,
(integrity-constraint1),
...,
(integrity-constraintk))
– r is the name of the relation
– each Ai is an attribute name in the schema of relation r
– Di is the data type of attribute Ai

Example:
create table branch
(branch_name char(15),
branch_city char(30),
assets integer)
 Domain Types in SQL
char(n). Fixed length character string, with user-specified length
n.
varchar(n). Variable length character strings, with user-specified
maximum length n.
int. Integer (a finite subset of the integers that is machine-
dependent).
smallint. Small integer (a machine-dependent subset of the
integer domain type).
numeric(p,d). Fixed point number, with user-specified precision
of p digits, with n digits to the right of decimal point.
real, double precision. Floating point and double-precision
floating point numbers, with machine-dependent precision.
float(n). Floating point number, with user-specified precision of
at least n digits.
More are covered in Chapter 4.
Integrity Constraints on Tables
not null
primary key (A1, ..., An )
Example: Declare branch_name as the primary key for branch
.
create table branch
(branch_name char(15),
branch_city char(30) not null,
assets integer,
primary key (branch_name))

primary key declaration on an attribute automatically ensures not

null in SQL-92 onwards, needs to be explicitly stated in SQL-89
Basic Insertion and Deletion of Tuples
Newly created table is empty
Add a new tuple to account
insert into account
values ('A-9732', 'Perryridge', 1200)

– Insertion fails if any integrity constraint is

violated
Delete all tuples from account
delete from account
Note: Will see later how to delete selected tuples
Example
 Drop and Alter Table Constructs
The drop table command deletes all information about the dropped
relation from the database.
The alter table command is used to add attributes to an existing
relation:
alter table r add A D
where A is the name of the attribute to be added to relation r and D is
the domain of A.
– All tuples in the relation are assigned null as the value for the new
attribute.
The alter table command can also be used to drop attributes of a
relation:
alter table r drop A
where A is the name of an attribute of relation r
– Dropping of attributes not supported by many databases
 Basic Query Structure
A typical SQL query has the form:
select A1, A2, ..., An
from r1, r2, ..., rm
where P
Ai represents an attribute
– Ri represents a relation
– P is a predicate.
This query is equivalent to the relational algebra
expression.
A1,A2 ,,An ( P (r1 r2  rm ))

The result of an SQL query is a relation.

 The select Clause
The select clause list the attributes desired in the result of a
query
– corresponds to the projection operation of the relational
algebra
Example: find the names of all branches in the loan relation:
select branch_name
from loan
In the relational algebra, the query would be:
branch_name (loan)
NOTE: SQL names are case insensitive (i.e., you may use
upper- or lower-case letters.)
– E.g. Branch_Name ≡ BRANCH_NAME ≡ branch_name
– Some people use upper case wherever we use bold font.
 The select Clause (Cont.)
SQL allows duplicates in relations as well as in query
results.
To force the elimination of duplicates, insert the
keyword distinct after select.
Find the names of all branches in the loan relations,
and remove duplicates
select distinct branch_name
from loan
The keyword all specifies that duplicates not be
removed.
select all branch_name
from loan
 The select Clause (Cont.)
An asterisk in the select clause denotes “all
attributes”
select *
from loan
The select clause can contain arithmetic
expressions involving the operation, +, –, ,
and /, and operating on constants or attributes
of tuples.
E.g.:
select loan_number, branch_name,amount  100
from loan
 The where Clause
The where clause specifies conditions that the result must
satisfy
– Corresponds to the selection predicate of the relational
algebra.
To find all loan number for loans made at the Perryridge
branch with loan amounts greater than $1200.
select loan_number
from loan
where branch_name = 'Perryridge' and amount >
1200
Comparison results can be combined using the logical
connectives and, or, and not.
 The from Clause
The from clause lists the relations involved in the query
– Corresponds to the Cartesian product operation of
the relational algebra.
Find the Cartesian product borrower X loan
select 
from borrower, loan
 Find the name, loan number and loan amount of all customers
having a loan at the Perryridge branch.

select customer_name, borrower.loan_number, amount

from borrower, loan
where borrower.loan_number = loan.loan_number and
branch_name = 'Perryridge'
 The Rename Operation

SQL allows renaming relations and attributes using the

as clause:
old-name as new-name
E.g. Find the name, loan number and loan amount of all
customers; rename the column name loan_number as
loan_id.

select customer_name, borrower.loan_number as loan_id,

amount
from borrower, loan
where borrower.loan_number = loan.loan_number
 Tuple Variables
Tuple variables are defined in the from clause via the use
of the as clause.
Find the customer names and their loan numbers and
amount for all customers having a loan at some branch.

select customer_name, T.loan_number, S.amount

from borrower as T, loan as S
where T.loan_number = S.loan_number
 Find the names of all branches that have greater assets
than
some branch located in Brooklyn.
select distinct T.branch_name
from branch as T, branch as S
where T.assets > S.assets and S.branch_city =
'Brooklyn'
Keyword as is optional and may be omitted
borrower as T ≡ borrower T
 String Operations
SQL includes a string-matching operator for comparisons
on character strings. The operator “like” uses patterns
that are described using two special characters:
– percent (%). The % character matches any substring.
– underscore (_). The _ character matches any
character.
Find the names of all customers whose street includes
the substring “Main”.
select customer_name
from customer
where customer_street like '% Main%'
Match the name “Main%”
like 'Main\%' escape '\'
 String Operations
SQL supports a variety of string operations
such as
– concatenation (using “||”)
– converting from upper to lower case (and
vice versa)
– finding string length, extracting substrings,
etc.
 Ordering the Display of Tuples
List in alphabetic order the names of all customers having
a loan in Perryridge branch
select distinct customer_name
from borrower, loan
where borrower loan_number = loan.loan_number
and
branch_name = 'Perryridge'
order by customer_name
We may specify desc for descending order or asc for
ascending order, for each attribute; ascending order is the
default.
– Example: order by customer_name desc
 Duplicates
In relations with duplicates, SQL can define how many
copies of tuples appear in the result.
Multiset versions of some of the relational algebra operators
– given multiset relations r1 and r2:
1.  (r1): If there are c1 copies of tuple t1 in r1, and t1 satisfies
selections ,, then there are c1 copies of t1 in  (r1).
2. A (r ): For each copy of tuple t1 in r1, there is a copy of
tuple A (t1) in A (r1) where A (t1) denotes the projection
of the single tuple t1.
3. r1 x r2 : If there are c1 copies of tuple t1 in r1 and c2 copies
of tuple t2 in r2, there are c1 x c2 copies of the tuple t1. t2 in
r1 x r2
 Duplicates (Cont.)
Example: Suppose multiset relations r1 (A, B) and r2
(C) are as follows:
r1 = {(1, a) (2,a)} r2 = {(2), (3), (3)}
Then B(r1) would be {(a), (a)}, while B(r1) x r2 would
be
{(a,2), (a,2), (a,3), (a,3), (a,3), (a,3)}
SQL duplicate semantics:
select A1,, A2, ..., An
from r1, r2, ..., rm
where P
is equivalent to the multiset version of the expression:

A1,A2 ,,An ( P (r1 r2  rm ))

 Set Operations
The set operations union, intersect, and except
operate on relations and correspond to the relational
algebra operations 
Each of the above operations automatically eliminates
duplicates; to retain all duplicates use the
corresponding multiset versions union all, intersect
all and except all.

Suppose a tuple occurs m times in r and n times in s,

then, it occurs:
– m + n times in r union all s
– min(m,n) times in r intersect all s
– max(0, m – n) times in r except all s
 Set Operations
Find all customers who have a loan, an account, or both:

(select customer_name from depositor)

union
(select customer_name from borrower)

 Find all customers who have both a loan and an account.

(select customer_name from depositor)

intersect
(select customer_name from borrower)

 Find all customers who have an account but no loan.

(select customer_name from depositor)

except
(select customer_name from borrower)
 Aggregate Functions

These functions operate on the

multiset of values of a column of a
relation, and return a value
avg: average value
min: minimum value
max: maximum value
sum: sum of values
count: number of values
 Aggregate Functions (Cont.)
Find the average account balance at the Perryridge branch.

select avg (balance)

from account
where branch_name = 'Perryridge'

 Find the number of tuples in the customer relation.

select count (*)

from customer

 Find the number of depositors in the bank.

select count (distinct customer_name)

from depositor
 Aggregate Functions – Group By
Find the number of depositors for each branch.

select branch_name, count (distinct customer_name)

from depositor, account
where depositor.account_number =
account.account_number
group by branch_name

Note: Attributes in select clause outside of aggregate functions

must
appear in group by list
Aggregate Functions – Having Clause
Find the names of all branches where the average
account balance is more than $1,200.

select branch_name, avg (balance)

from account
group by branch_name
having avg (balance) > 1200

Note: predicates in the having clause are applied after the

formation of groups whereas predicates in the where

clause are applied before forming groups

 Nested Subqueries

SQL provides a mechanism for the

nesting of subqueries.
A subquery is a select-from-where
expression that is nested within another
query.
A common use of subqueries is to
perform tests for set membership, set
comparisons, and set cardinality.
 “In” Construct
Find all customers who have both an account
and a loan at the bank.
select distinct customer_name
from borrower
where customer_name in (select customer_name
from depositor )

 Find all customers who have a loan at the bank but do not
have
an account at the bank
select distinct customer_name
from borrower
where customer_name not in (select customer_name
from depositor )