SQL Chapter 3
Single-Table Queries
Objectives
Retrieve data from a database
using SQL commands
Use compound conditions
Use computed columns
Use the SQL LIKE operator
Use the SQL IN operator
Sort data using the ORDER BY
clause
Sort data using multiple keys and
in ascending and descending order
Use SQL aggregate functions
Use subqueries
Group data using the GROUP BY
clause
Select individual groups of data
using the HAVING clause
Retrieve columns with null values
Simple Queries
Query - a question represented in
a way that the DBMS can understand
Basic format
l SELECT-FROM
Optional
l SELECT-FROM-WHERE
SELECT Command
SELECT clause
l Followed by columns to be included in the query
FROM clause
l Followed by name of the table that contains the data to query
WHERE clause (optional)
l Followed by conditions that apply to the data to be retrieved
Retrieving Certain Columns
and All Rows
List columns to include in the
SELECT clause and the table name in the FROM clause
Retrieving All Columns and
All Rows
An asterisk (*) indicates that you
want to include all columns
The result shows all columns in
the order in which they were described to the system when the table was created
Using a WHERE Clause
The WHERE clause retrieves rows
that satisfy some condition
A simple condition has the form:
l Column name, comparison operator, either another column name or a value
Comparison Operators
Note
Generally SQL is not
case-sensitive
Exception:
l Values within quotation marks
l Use the correct case for these values
Example:
l
WHERE LAST ‘adams’
will not select any rows if the stored value is “
Compound Conditions
Compound conditions
l Formed by connecting two or more simple conditions
l Uses of AND, OR, and NOT operators
l AND: all conditions must be true
l OR: any one of the conditions is true
l NOT: reverses the truth of the original condition
Use of BETWEEN
BETWEEN operator
l Not an essential feature
l Can arrive at same answer without it using AND
l Does make certain SELECT commands simpler
Computed Columns
Computed columns
l Do not exist in the database
l Can be computed using data in existing columns
l Use arithmetic operators
Arithmetic Operators
Use of LIKE
LIKE operator is used when exact
matches will not work
Use LIKE with a wildcard symbol
Wildcard Symbols
Percent symbol (%)
l represents any collection of characters
l ‘%Pine%’
Underscore (_)
l Represents any individual character
l ‘T_m’
Use of IN
The IN clause provides a concise
way of phrasing certain conditions
The IN clause consists of the IN
operator followed by a collection of values
Sorting
Generally, the order of rows is
immaterial to the DBMS
There is no defined order in which
results are displayed
In SQL, the results order can be
specified by using the ORDER BY clause
Use of ORDER BY
Use the ORDER BY command to list
data in a specific order
The ORDER BY clause is followed by
the sort key
The column on which data is to be
sorted is called a sort key or simply key
If a sort order is not specified,
the default is ascending
Additional Sorting Options
When sorting more than one column:
l The more important column is called the major key (or primary sort key)
l The less important column is called the minor key (or secondary sort key)
List keys in the order of
importance in the ORDER BY clause
Sort in a descending order by
using the DESC operator
Using Functions
SQL has special functions, called
aggregate functions, to calculate:
l Sums
l Averages
l Counts
l Maximum values
l Minimum values
SQL Functions
Use of COUNT Function
Count function counts the number
of rows in a table
The specific row to be counted is
not important because each count should provide the same answer
Most implementations of SQL use
the asterisk (*) to represent any column
Use of the SUM Function
The SUM function is used to
calculate totals
The column to be totaled must be
specified
The column to be totaled must be
numeric
Using AVG, MAX, and MIN
AVG, MAX and MIN functions are
similar to the SUM function
SUM, AVG, MAX, and MIN functions
ignore (eliminate) null values
l Null values can cause strange results when calculated
Use of DISTINCT
DISTINCT operator is not a
function
Useful when used in conjunction
with COUNT function
Nesting Queries
Sometimes obtaining the results
you need is a two-step process (or more)
Problem:
l List the number of each part in class AP
l List the order numbers that contain an order line for a part in class AP
Subqueries
It is possible to place one query
inside another
An inner query is called a subquery and it is evaluated first
An outer query can use the results
of the subquery to find its results
Grouping
Grouping creates groups of rows
that share some common characteristics
Calculations are performed for the
entire group
Use the GROUP BY command
Using GROUP BY
GROUP BY command allows data to be
grouped in a particular order
Statistics are calculated on the
groups
Using a HAVING Clause
The HAVING clause is used to
restrict the groups that will be included
HAVING vs. WHERE
l WHERE clause limits rows
l HAVING clause limits groups
Nulls
Sometimes a condition involves a
column that can be null
Problem:
l List the number and name of each customer with a null (unknown) street value
The correct format of the
condition to use for this problem is STREET IS NULL
To select a customer whose street
is not null, the condition STREET IS NOT NULL is used
Summary
The basic form of the SQL SELECT
command is SELECT-FROM-WHERE
You can form compound conditions
by combining simple conditions
Use the BETWEEN operator to
indicate a range of values in a condition
Use the LIKE operator to check for
a value in a character column that is similar to a particular string of
characters
Use the IN operator to determine
whether a column contains one of a particular set of values
Use an ORDER BY clause to sort
data
SQL processes the aggregate
functions COUNT, SUM, AVG, MAX, and MIN
When one SQL query is placed
inside another, it is called a subquery
Use a GROUP BY clause to group
data
Use a HAVING clause to restrict
the output to certain groups
SQL Project Three Completed
Good Luck
H. Zamanzadeh