My SQL: Data
Manipulation
Prof. Sin Min Lee
Deparment of Computer Science
San Jose State University
Introduction




Writing an SQL Command
Retrieving Data
Building SQL Statements
Performing Database Updates
SQL







SQL (Structured Query Language) is
the standard language for commercial DBMSs
SEQUEL (Structured English QUEry Language)
was originally defined by IBM for System R
standardization of SQL began in the 80s
current standard is SQL-99
SQL is more than a query language it includes a DDL,
DML and administration commands
SQL is an example of a transform-oriented
language.
A language designed to use relations to transform
inputs into required outputs.
2
Basic structure of an SQL
query
G e n e ra l
S tru c tu re
S E L E C T , A L L / D IS T IN C T , *,
A S, FRO M , W H ERE
C o m p a ris o n
IN , B E T W E E N , L IK E "% _"
G ro u p in g
G R O U P B Y , H A V IN G ,
C O U N T ( ), S U M ( ), A V G ( ), M A X ( ), M IN ( )
D is p la y O rd e r
O RD ER BY , A SC / D ESC
L o g ic a l
AND, OR, NOT
O p e ra to rs
10/3/2015
O u tp u t
IN T O T A B L E / C U R S O R
T O F IL E [A D D IT IV E ], T O P R IN T E R , T O S C R E E N
U n io n
U N IO N
2
The Situation:
Student Particulars
field
id
name
dob
sex
class
hcode
dcode
remission
mtest
10/3/2015
type
width
numeric
4
character
10
date
8
character
1
character
2
character
1
character
3
logical
1
numeric
2
contents
student id number
name
date of birth
sex: M / F
class
house code: R, Y, B, G
district code
fee remission
Math test score
I
General Structure
SELECT ...... FROM ...... WHERE ......
SELECT [ALL / DISTINCT] expr1 [AS col1], expr2 [AS col2] ;
FROM tablename WHERE condition
10/3/2015
I
General Structure
SELECT [ALL / DISTINCT] expr1 [AS col1], expr2 [AS col2] ;
FROM tablename WHERE condition
The query will select rows from the
source tablename and output the result
Expressions expr1, expr2
canform.
be :
in table


 (1)
a column, or
 (2) an expression of functions and fields.

10/3/2015
And col1, col2 are their corresponding column names
in the output table.
I
General Structure
SELECT [ALL / DISTINCT] expr1 [AS col1], expr2 [AS col2] ;
FROM tablename WHERE condition

DISTINCT will eliminate duplication in the output
while ALL will keep all duplicated rows.

condition can be :
 (1)
an inequality, or
 (2) a string comparison
 using logical operators AND, OR, NOT.
10/3/2015
I
General Structure
Before using SQL, open the student file:
USE student
eg. 1
List all the student records.
SELECT * FROM student
Result
10/3/2015
id
name
9801 Peter
9802 Mary
9803 Johnny
9804 Wendy
9805 Tobe
:
:
dob
06/04/86
01/10/86
03/16/86
07/09/86
10/17/86
:
sex
M
F
M
F
M
:
class
1A
1A
1A
1B
1B
:
mtest
70
92
91
84
88
:
hcode
R
Y
G
B
R
:
dcode
SSP
HHM
SSP
YMT
YMT
:
remission
.F.
.F.
.T.
.F.
.F.
:
I
eg. 2
General Structure
List the names and house code of 1A students.
SELECT name, hcode, class FROM student ;
WHERE class="1A"
Class
1A
1A
1A
1B
1B
:
10/3/2015
Class


class="1A"



1A
1A
1A
1B
1B
:
I
eg. 2
General Structure
List the names and house code of 1A students.
Result
10/3/2015
name
Peter
Mary
Johnny
Luke
Bobby
Aaron
:
hcode
R
Y
G
G
B
R
:
class
1A
1A
1A
1A
1A
1A
:
I
eg. 3
General Structure
List the residential district of the Red House
members.
SELECT DISTINCT dcode FROM student ;
WHERE hcode="R"
Result
10/3/2015
dcode
HHM
KWC
MKK
SSP
TST
YMT
What is SQL?

ISO SQL has two major
components:
Data Definition Language (DDL)
 Data Manipulation Language
(DML)

Source: Database Systems Connolly/Begg
Data Definition Language (DDL)

Defining the database structure


Controlling access to the data

Source: Database Systems Connolly/Begg
Tables
What a user can legally access
Data Manipulation Language (DML)

Retrieving Data


Updating Data

Source: Database Systems Connolly/Begg
Query tables
Populate tables
Writing SQL Commands

SQL statement consists of
reserved words and user-defined
words
Reserved words are a fixed part
of the SQL language and have a
fixed meaning
 User-defined words are made up
by the user (according to syntax
rules)

Source: Database Systems Connolly/Begg
Reserved Words




Source: Database Systems Connolly/Begg
Are fixed part of the SQL language
Have a fixed meaning
Require exact spelling
Kept on the same line
User-defined Words



Are made up by the user
Governed by a set of syntax rules
Represent names of database
objects such as:
Tables
 Columns
 Views
 Indexes

Source: Database Systems Connolly/Begg
Data Manipulation




Source: Database Systems Connolly/Begg
Select: query data in the database
Insert: insert data into a table
Update: updates data in a table
Delete: delete data from a table
Literals

Non-numeric data values must be
enclosed in single quotes:
’16 Holland Drive’
 ‘CS157B’


Numeric data values must NOT be
enclosed in single quotes:
6
 600.00

Source: Database Systems Connolly/Begg
Writing SQL Commands
Most components of an SQL
statement are case insensitive,
but one exception is that literal
character data must be typed
exactly as it appears in the
database.
Source: Database Systems Connolly/Begg
Simple Query
Source: Database Systems Connolly/Begg

Select specifies which columns
are to appear in the output.

From specifies the table(s) to be
used.

Where filters the rows subject to
some condition(s).
Simple Query
Source: Database Systems Connolly/Begg

Group By forms groups of rows
with the same column value.

Having filters the groups subject to
some condition.

Order By specifies the order of the
output.
Retrieve all columns and all rows
SELECT firstColumn,…,lastColumn
FROM tableName;
SELECT *
FROM tableName;
Use of Distinct
SELECT DISTINCT columnName
FROM tableName;
columnName
A
A
B
B
C
D
columnName
A
B
C
D
Calculated fields
SELECT columnName/2
FROM tableName
price
10.00
6.00
12.00
price
5.00
3.00
6.00
Comparison Search Condition
= equals
< > is not equal to (ISO standard)
!= “ “ “ “ (allowed in some dialects)
< is less than
> is greater than
<= is less than or equal to
>= is greater than or equal to
Source: Database Systems Connolly/Begg
Comparison Search Condition




Source: Database Systems Connolly/Begg
An expression is evaluated left to
right.
Subexpressions in brackets are
evaluated first.
NOTs are evaluated before ANDs
and ORs.
ANDs are evaluated before ORs.
Range Search Condition
SELECT columnName
FROM tableName
WHERE columnName BETWEEN 20
AND 30;
SELECT columnName
FROM tableName
WHERE columnName >= 20
AND columnName <= 30;
Set membership search condition
SELECT columnName
FROM tableName
WHERE columnName
IN (‘name1’, ‘name2’);
SELECT columnName
FROM tableName
WHERE columnName = ‘name1’
OR columnName = ‘name2’;
Pattern matching symbols
% represents any sequence of zero
or more characters (wildcard).
_ represents any single character
Source: Database Systems Connolly/Begg
Pattern match search condition
‘h%’
: begins with the character h .
‘h_ _ _’ : four character string beginning with the
character h.
‘%e’
: any sequence of characters, of length at
least 1, ending with the character e.
‘%CS157B%’ : any sequence of characters of any
length containing CS157B
Source: Database Systems Connolly/Begg
Pattern match search condition
LIKE ‘h%’
begins with the character h .
NOT LIKE ‘h%’
does not begin with the
character h.
Source: Database Systems Connolly/Begg
Pattern match search condition
To search a string that includes a
pattern-matching character
‘15%’
Use an escape character to represent
the pattern-matching character.
LIKE ‘15#%’ ESCAPE ‘#’
Source: Database Systems Connolly/Begg
NULL search condition
DOES NOT WORK
comment = ‘ ’
comment != ‘ ’
DOES WORK
comment IS NULL
comment IS NOT NULL
Sorting

The ORDER BY clause
consists of list of column
identifiers that the result is to be
sorted on, separated by commas.
 Allows the retrieved rows to be
ordered by ascending (ASC) or
descending (DESC) order

Source: Database Systems Connolly/Begg
Sorting

Column identifier may be
A column name
 A column number (deprecated)

Source: Database Systems Connolly/Begg
Sorting
SELECT type, rent
FROM tableName
ORDER BY type, rent ASC;
Source: Database Systems Connolly/Begg
type
rent
type
rent
Flat
Apt
Flat
Apt
650
450
600
500
Apt
Apt
Flat
Flat
450
500
600
650
Aggregate Functions





COUNT returns the number …
SUM returns the sum …
AVG returns the average …
MIN returns the smallest …
MAX returns the largest …
value in a specified column.
Source: Database Systems Connolly/Begg
Use of COUNT( * )
How many students in CS157B?
SELECT COUNT( * ) AS my count
FROM CS157B
my count
40
GROUP BY clause


When GROUP BY is used, each item in the
SELECT list must be single-valued per group.
The SELECT clause may contain only
Column names
 Aggregate functions
 Constants
 An expression involving combinations of the
above

Source: Database Systems Connolly/Begg
Grouping
SELECT dept, COUNT(staffNo) AS my count
SUM(salary)
FROM tableName
GROUP BY dept
ORDER BY dept
dept
A
B
C
A
B
staffNo
1
1
1
2
2
Salary
200.00
200.00
200.00
100.00
100.00
dept
A
B
C
my
count
2
2
1
Salary
300.00
300.00
200.00
Restricting Grouping

HAVING clause
is with the GROUP BY clause.
 filters groups into resulting table.
 includes at least one aggregate
function.


WHERE clause
filters individual rows into resulting
table.
 Aggregate functions cannot be
used.

Source: Database Systems Connolly/Begg
SELECT dept, COUNT(staffNo) AS my count,
SUM(salary) AS my sum
FROM Staff
GROUP BY dept
HAVING COUNT(staffNo) > 1
ORDER BY dept;
dept
A
B
C
A
B
staffNo
1
1
1
2
2
Source: Database Systems Connolly/Begg
Salary
200.00
200.00
200.00
100.00
100.00
dept
A
B
my
count
2
2
my
sum
300.00
300.00
Subqueries
SELECT columnNameA
FROM tableName1
WHERE columnNameB = (SELECT columnNameB
FROM tableName2
WHERE condition);
result from inner
SELECT applied as a
condition for the
outer SELECT
Source: Database Systems Connolly/Begg
Subquery with Aggregate Function
List all staff whose salary is greater than the average salary,
show by how much their salary is greater than the average.
SELECT fName, salary –
( SELECT AVG(salary)
FROM Staff ) AS salDiff
FROM Staff
WHERE salary > ( SELECT AVG(salary)
FROM Staff );
Source: Database Systems Connolly/Begg
Nested Subqueries: Use of IN
SELECT property
FROM PropertyForRent
WHERE staff IN(
SELECT staff
FROM Staff
WHERE branch = (
SELECT branch
FROM Branch
Selects branch at
WHERE street
112 A St
= ‘112 A St’));
Source: Database Systems Connolly/Begg
Nested Subqueries: Use of IN
SELECT property
FROM PropertyForRent
WHERE staff IN(
SELECT staff
FROM Staff
WHERE branch = ( branch ) );
Select staff
members who
works at
branch.
Source: Database Systems Connolly/Begg
Nested Subqueries: Use of IN
SELECT property
FROM PropertyForRent
WHERE staff IN( staffs who works
at branch on ‘112 A St’);
Since there are more than
one row selected, “=“
cannot be used.
Source: Database Systems Connolly/Begg
Use of ANY/SOME
SELECT name, salary
FROM Staff
WHERE salary > SOME( SELECT salary
FROM Staff
WHERE branch = ‘A’ );
Result: {list of
staff with salary
greater than 2000.}
Source: Database Systems Connolly/Begg
Result:{2000,3000,4000}
Use of ALL
SELECT name, salary
FROM Staff
WHERE salary > ALL( SELECT salary
FROM Staff
WHERE branch = ‘A’ );
Result: {list of
staff with salary
greater than 4000.}
Source: Database Systems Connolly/Begg
Result:{2000,3000,4000}
Use of Any/Some and All

If the subquery is empty:
ALL returns true
 ANY returns false


Source: Database Systems Connolly/Begg
ISO standard allows SOME to be
used interchangeably with ANY.
4 Natural Join
A Natural Join is a join operation that joins two
tables by their common column. This operation
is similar to the setting relation of two tables.
SELECT a.comcol, a.col1, b.col2, expr1, expr2 ;
FROM table1 a, table2 b ;
WHERE a.comcol = b.comcol
10/3/2015
4 Natural Join
eg. 25
Make a list of students and the instruments they
learn. (Natural Join)
id
name class
id
Same id
9801
Join
Student
id
name class
9801
10/3/2015
Product
type
9801
Music
type
4 Natural Join
eg. 25
Make a list of students and the instruments they
learn. (Natural Join)
SELECT s.class, s.name, s.id, m.type ;
FROM student s, music m ;
WHERE s.id=m.id ORDER BY class, name
Result
10/3/2015
class
1A
1A
1A
1A
1A
1A
1A
:
name
Aaron
Bobby
Gigi
Jill
Johnny
Luke
Mary
:
id
9812
9811
9824
9820
9803
9810
9802
:
type
Piano
Flute
Recorder
Piano
Violin
Piano
Flute
:
4 Natural Join
Find the number of students learning piano in
each class.
eg. 26
Three Parts :
(1) Natural Join.
(2) Condition: m.type="Piano"
(3) GROUP BY class
10/3/2015
4 Natural Join
eg. 26
Student
Join
Condition
m.type= "Piano"
Product
Music
10/3/2015
Group By
class
4 Natural Join
eg. 26
Find the number of students learning piano in
each class.
SELECT s.class, COUNT(*) ;
FROM student s, music m ;
WHERE s.id=m.id AND m.type="Piano" ;
GROUP BY class ORDER BY class
Result
10/3/2015
class
1A
1B
1C
cnt
4
2
1
4 Outer Join
An Outer Join is a join operation that includes
rows that have a match, plus rows that do not
have a match in the other table.
10/3/2015
4 Outer Join
eg. 27
List the students who have not yet chosen an
instrument. (No match)
id
name class
9801
Student
10/3/2015
id
type
No match
Music
4 Outer Join
eg. 27
List the students who have not yet chosen an
instrument. (No match)
SELECT class, name, id FROM student ;
WHERE id NOT IN ( SELECT id FROM
music ) ;
ORDER BY class, name
Result
10/3/2015
class
1A
1B
1B
1C
1C
:
name
Mandy
Kenny
Tobe
Edmond
George
:
id
9821
9814
9805
9818
9817
:
4 Outer Join
eg. 28
10/3/2015
Make a checking list of students and the
instruments they learn. The list should also
contain the students without an instrument.
(Outer Join)
4 Outer Join
eg. 28
Natural Join
Outer Join
No Match
10/3/2015
4 Outer Join
eg. 28
SELECT s.class, s.name, s.id, m.type ;
FROM student s, music m ;
WHERE s.id=m.id ;
UNION ;
SELECT class, name, id, "" ;
FROM student ;
WHERE id NOT IN ( SELECT id FROM music ) ;
ORDER BY 1, 2
10/3/2015
4 Outer Join
class
1A
1A
1A
1A
1A
1A
1A
:
name
Aaron
Bobby
Gigi
Jill
Johnny
Luke
Mary
:
id
9812
9811
9824
9820
9803
9810
9802
:
type
Piano
Flute
Recorder
Piano
Violin
Piano
Flute
:
Natural Join
class
1A
1B
1B
1C
1C
:
10/3/2015
name
Mandy
Kenny
Tobe
Edmond
George
:
id
9821
9814
9805
9818
9817
:
No Match
class
1A
1A
1A
1A
1A
1A
1A
1A
1A
1A
1B
1B
1B
1B
:
name
Aaron
Bobby
Gigi
Jill
Johnny
Luke
Mandy
Mary
Peter
Ron
Eddy
Janet
Kenny
Kitty
:
id
9812
9811
9824
9820
9803
9810
9821
9802
9801
9813
9815
9822
9814
9806
:
type
Piano
Flute
Recorder
Piano
Violin
Piano
Flute
Piano
Guitar
Piano
Guitar
empty
Recorder
:
Outer Join
Multi-Table Queries





Source: Database Systems Connolly/Begg
Join
Inner Join
Left Outer Join
Right Outer Join
Full Outer Join
Join
SELECT client
FROM Client c, View v
WHERE c.client = v.client;
ISO standard Alternatives
FROM Client c JOIN View v ON c.client = v.client
(creates two identical client columns)
FROM Client JOIN View USING client
FROM Client NATURAL JOIN View
Source: Database Systems Connolly/Begg
Join


Source: Database Systems Connolly/Begg
The join operation combines data
from two tables by forming pairs of
related rows where the matching
columns in each table have the
same value.
If one row of a table is unmatched,
the row is omitted from the
resulting table.
Descargar

SQL: Data Manipulation