Transcript slides Topic slides

SQL Statements:
Queries
Relational Databases

Relational Databases organize the data in
tables with rows and columns. This is similar to
the organization of spreadsheets.
Column
STUDENT TABLE
Row
111 Smith
112 ElSawa
Joe
Jane
1
1
113 Chi
114 Monti
Jill
Jack
3
1
StudentNo. LastName
FirstName
Status
Relational Database Operations.

There are three fundamental operations on predefined (already created) tables.
 Query.
Retrieval of data from one or more columns
from one or more rows typically based on some
condition.
 Insert. Insert an entire row with all columns into the
table.
 Update. Modification of one or more columns from
one or more rows typically based on some condition.
 Delete. Removal of a row typically based on some
condition
Language Note!


One confusing term in Relational Database
usage is update. When used as a general term
it means any operation that can modify the state
of a table. The means that SQL UPDATE
statements, SQL INSERT statements and SQL
DELETE statements can be considered as
updates (in a general sense).
As a results statements that alter pre-defined
tables are divided into two categories, queries
and updates.
Types of Statements.


Queries. In SQL these are executed using the
SELECT statements.
Updates. In SQL these are executed typically
using INSERT to add entire rows to a table or
UPDATE to modify columns in pre-existing rows
in a table or DELETE to delete entire rows from
a table.
Querying a Database



You can retrieve information from a database by
executing a query.
Queries involve requesting the return of one or
all columns for each row that matches a
condition.
A query returns a result set which is a collection
of rows with each row containing only the
columns requested by query.
The Select Statement


A select statement is used to query a database.
Select statements typically (but not always)
require:
 Which
columns in a table or tables you wish to
retrieve.
 Which tables you will retrieve the information from.
 Any special conditions you may attach to the query.

Select statements must end with a semi-colon.
SELECT Statement
Columns to Retrieve
Select Lastname,Firstname
From Student
Where Status=1;
Table to Query
Special Conditions to refine
search.
SELECT Statement
Retrieve all columns
Select *
From Student;
Table to Query
No Condition is supplied so
columns from ALL rows in
Student will be returned…i.e.
unconditional query.
Special Syntax Notes.

Enclose string constants used in comparisons in
single quotes not double quotes.
 Select

* from tablename where sfield=‘Fred’;
Numeric constants do not need quotes.
 Select
* from tablename where numfield=27;
Simple Queries/No Conditions
STUDENT TABLE
Select
LastName,FirstName
from Student;
111 Smith
112 ElSawa
Joe
Jane
1
1
113 Chi
114 Monti
Jill
Jack
3
1
StudentNo. LastName
4 Two-Column Rows in the
Result Set
FirstName
Smith
ElSawa
Joe
Jane
Chi
Monti
Jill
Jack
LastName
FirstName
Status
Simple Queries w/Conditions
STUDENT TABLE
Select LastName,Status
111 Smith
Joe
1
from Student
112 ElSawa
113 Chi
114 Monti
Jane
Jill
Jack
1
3
1
Where Status=1 ;
StudentNo. LastName
3 Two-Column Rows in the
Result Set
FirstName
Smith
1
ElSawa
1
Monti
1
LastName
Status
Status
Simple Queries w/ Compound Condition
STUDENT TABLE
Select LastName,Status
111 Smith
Joe
1
from Student
112 ElSawa
113 Chi
114 Monti
Jane
Jill
Jack
1
3
1
Where Status=2;
StudentNo. LastName
No rows returned that satisfy
condition.
FirstName
NULL SET
LastName
Status
Status
Conditional Operations -Conventional
Expression
Evaluation
A=B
True if A equals B
A != B
True if A does not equal B
A <= B
True if A is less than or equal to B
A>B
True if A is greater than or equal to B
A<B
True if A is less than B
A>B
True if A is greater than B
Conditional Operations - Special
Expression
Evaluation
A <=> B
True if A is equal to B (NULL Safe)
A IS NULL
True if A is NULL
A IS NOT NULL
True if A is not NULL
A BETWEEN M AND N
True if A is between values M and N
A NOT BETWEEN M AND N
True if A is not between values M and N
A IN(value, value2, ...)
True if A is one of the listed values
A NOT IN (value, value2, ...)
True if A is not one of the listed values
Compound Operators.



And returns row only if both conditions in
compound statement are true.
Or returns row if either condition in compound
statement is true.
You can negate (NOT) a compound condition by
enclosing the condition in parentheses.
Simple Queries w/ Compound Condition
STUDENT TABLE
Select LastName,Status
from Student
Where Status=1
And FirstName=‘Jane’ ;
111 Smith
Joe
1
112 ElSawa
113 Chi
114 Monti
Jane
Jill
Jack
1
3
1
StudentNo. LastName
1 Two-Column Row in the
Result Set
ElSawa
LastName
FirstName
1
Status
Status
Simple Queries w/Conditions
STUDENT TABLE
Select LastName,Status
111 Smith
Joe
1
from Student
112 ElSawa
113 Chi
114 Monti
Jane
Jill
Jack
1
3
1
Where Status=3
Or FirstName=‘Jane’ ;
StudentNo. LastName
2 Two-Column Rows in the
Result Set
FirstName
ElSawa
1
Chi
3
LastName
Status
Status
Simple Queries w/ Compound Condition
STUDENT TABLE
Select LastName,Status
111 Smith
Joe
1
from Student
112 ElSawa
113 Chi
114 Monti
Jane
Jill
Jack
1
3
1
Where Status=1
And FirstName=‘Jill’ ;
No rows returned that satisfy
condition.
NULL SET
LastName
Status