Title: A Guide to SQL, Seventh Edition
1- A Guide to SQL, Seventh Edition
2Objectives
- 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
A Guide to SQL, Seventh Edition
3Objectives
- 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
A Guide to SQL, Seventh Edition
4Constructing Simple Queries
- Important feature of DBMS is ability to answer a
wide variety of questions about the data - A query is a question represented in a way that
the DBMS can understand - Use the SELECT command to query a database
A Guide to SQL, Seventh Edition
5Constructing Simple Queries
- SELECT-FROM-WHERE is the basic form of the
command - SELECT clause is the list of columns to include
in query results - FROM clause is the name of the table with the
data being queried - WHERE clause is optional, listing any conditions
to apply to the data
A Guide to SQL, Seventh Edition
6Retrieving Certain Columns and Rows
- A command can retrieve specified columns and all
rows - List the number, name and balance of all
customers - No WHERE clause is needed, because all customers
are requested
A Guide to SQL, Seventh Edition
7A Guide to SQL, Seventh Edition
8Retrieving All Columns and Rows
- Use an asterisk () to indicate all columns in
the SELECT clause - Results will list all columns in the order in the
description when the table was created - List columns in SELECT clause to present columns
in a different order
A Guide to SQL, Seventh Edition
9A Guide to SQL, Seventh Edition
10Using a WHERE Clause
- WHERE clause is used to retrieve rows that
satisfy some condition - What is the name of customer number 148?
- A simple condition form column name, comparison
operator, and then either a column name or a value
A Guide to SQL, Seventh Edition
11A Guide to SQL, Seventh Edition
12A Guide to SQL, Seventh Edition
13Using a WHERE Clause
- Character values in SQL are case sensitive
- Grove is different than grove
- Simple conditions can compare columns
- WHERE BALANCE gt CREDIT_LIMIT
A Guide to SQL, Seventh Edition
14Using Compound Conditions
- Compound conditions connect two or more simple
conditions with AND, OR, and NOT operators - AND operator shows results that all simple
conditions are true - OR operator shows results that any simple
condition is true - NOT operator reverses the truth of the original
condition
A Guide to SQL, Seventh Edition
15A Guide to SQL, Seventh Edition
16A Guide to SQL, Seventh Edition
17A Guide to SQL, Seventh Edition
18A Guide to SQL, Seventh Edition
19Using the BETWEEN Operator
- Not an essential feature in SQL
- Same results can be obtained without it
- Does make certain SELECT commands simpler to
construct - BETWEEN operator is inclusive
- When using BETWEEN 2000 and 5000, values of 2000
or 5000 would be true
A Guide to SQL, Seventh Edition
20A Guide to SQL, Seventh Edition
21A Guide to SQL, Seventh Edition
22Using Computed Columns
- Computed column does not exist in the database
but is computed using data in existing columns - Computations can involve arithmetic operators
- for addition
- - for subtraction
- for multiplication
- / for division
A Guide to SQL, Seventh Edition
23A Guide to SQL, Seventh Edition
24Using the LIKE Operator
- Used to retrieve data where there may not be an
exact match using wildcards - LIKE Central will retrieve data with those
characters - 3829 Central or Centralia
- Underscore (_) represents any single character
- T_M for TIM or TOM or T3M
A Guide to SQL, Seventh Edition
25Using the IN Operator
- IN operator allows for concise phrasing of
certain conditions
A Guide to SQL, Seventh Edition
26Sorting
- ORDER BY clause to list data in a specific order
- Column on which data is to be sorted is the sort
key - Use ORDER BY clause followed by sort key
- Rows are sorted in ascending order unless another
order is specified
A Guide to SQL, Seventh Edition
27Additional Sorting Options
- Possible to sort data by more than one key
- Major sort key and minor sort key
- List sort keys in order of importance in the
ORDER BY clause - For descending order sort, use DESC
A Guide to SQL, Seventh Edition
28A Guide to SQL, Seventh Edition
29Using Functions
- Aggregate functions calculate sums, averages,
counts, minimum and maximum values to groups of
rows
A Guide to SQL, Seventh Edition
30Using the COUNT Function
- Counts the number of rows in a table
- Use of an asterisk allowed to represent any column
A Guide to SQL, Seventh Edition
31Using the SUM Function
- Used to calculate totals of columns
- Column to be summed must be specified and must be
numeric - AVG, MAX, and MIN functions are similar,
resulting in different statistics - Null values are ignored and not used in these
calculations
A Guide to SQL, Seventh Edition
32Using the DISTINCT Operator
- Used to ensure uniqueness in the data results
A Guide to SQL, Seventh Edition
33A Guide to SQL, Seventh Edition
34A Guide to SQL, Seventh Edition
35Nesting Queries
- Some queries will take two or more steps to
obtain desired results - A subquery is an inner query placed inside
another query - Outer query can use results of the subquery to
find its results
A Guide to SQL, Seventh Edition
36A Guide to SQL, Seventh Edition
37A Guide to SQL, Seventh Edition
38Grouping
- Grouping creates groups of rows that share common
characteristics - Calculations in the SELECT command are performed
for entire groups - Data can be GROUPED BY a particular column, such
as REP_NUM and then the statistics are calculated - One line of output is produced for each group
A Guide to SQL, Seventh Edition
39Using a HAVING Clause
- The HAVING clause is used to restrict groups that
will be included
A Guide to SQL, Seventh Edition
40Nulls
- Sometimes a condition involves a column that can
be null
A Guide to SQL, Seventh Edition
41A Guide to SQL, Seventh Edition
42Summary
- Create queries that retrieve data from single
tables using SELECT commands - Comparison operators , gt,gt,lt,lt, or ltgt, or !
- Compound conditions using AND,OR, and NOT
- Use the BETWEEN operator
- Use the LIKE operator
A Guide to SQL, Seventh Edition
43Summary
- Use the IN operator
- Use and ORDER BY clause to sort data
- Process aggregate functions with COUNT,
SUM,AVG,MAX, and MIN - Use the DISTINCT operator and subqueries
- Use GROUP BY, HAVING and IS NULL
A Guide to SQL, Seventh Edition