A Guide to SQL, Seventh Edition - PowerPoint PPT Presentation

1 / 43
About This Presentation
Title:

A Guide to SQL, Seventh Edition

Description:

A Guide to SQL, Seventh Edition Objectives Retrieve data from a database using SQL commands Use compound conditions Use computed columns Use the SQL LIKE operator Use ... – PowerPoint PPT presentation

Number of Views:153
Avg rating:3.0/5.0
Slides: 44
Provided by: annec3
Category:
Tags: sql | clause | edition | guide | seventh

less

Transcript and Presenter's Notes

Title: A Guide to SQL, Seventh Edition


1
  • A Guide to SQL, Seventh Edition

2
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

A Guide to SQL, Seventh Edition
3
Objectives
  • 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
4
Constructing 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
5
Constructing 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
6
Retrieving 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
7
A Guide to SQL, Seventh Edition
8
Retrieving 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
9
A Guide to SQL, Seventh Edition
10
Using 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
11
A Guide to SQL, Seventh Edition
12
A Guide to SQL, Seventh Edition
13
Using 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
14
Using 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
15
A Guide to SQL, Seventh Edition
16
A Guide to SQL, Seventh Edition
17
A Guide to SQL, Seventh Edition
18
A Guide to SQL, Seventh Edition
19
Using 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
20
A Guide to SQL, Seventh Edition
21
A Guide to SQL, Seventh Edition
22
Using 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
23
A Guide to SQL, Seventh Edition
24
Using 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
25
Using the IN Operator
  • IN operator allows for concise phrasing of
    certain conditions

A Guide to SQL, Seventh Edition
26
Sorting
  • 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
27
Additional 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
28
A Guide to SQL, Seventh Edition
29
Using Functions
  • Aggregate functions calculate sums, averages,
    counts, minimum and maximum values to groups of
    rows

A Guide to SQL, Seventh Edition
30
Using 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
31
Using 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
32
Using the DISTINCT Operator
  • Used to ensure uniqueness in the data results

A Guide to SQL, Seventh Edition
33
A Guide to SQL, Seventh Edition
34
A Guide to SQL, Seventh Edition
35
Nesting 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
36
A Guide to SQL, Seventh Edition
37
A Guide to SQL, Seventh Edition
38
Grouping
  • 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
39
Using a HAVING Clause
  • The HAVING clause is used to restrict groups that
    will be included

A Guide to SQL, Seventh Edition
40
Nulls
  • Sometimes a condition involves a column that can
    be null

A Guide to SQL, Seventh Edition
41
A Guide to SQL, Seventh Edition
42
Summary
  • 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
43
Summary
  • 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
Write a Comment
User Comments (0)
About PowerShow.com