Single Table Queries Team 9A: HPM - PowerPoint PPT Presentation

1 / 65
About This Presentation
Title:

Single Table Queries Team 9A: HPM

Description:

There are four data manipulation language commands used is SQL. ... 650 Entertainment Center 4. 325 Writer's Desk 5. 9. Expressions ... – PowerPoint PPT presentation

Number of Views:63
Avg rating:3.0/5.0
Slides: 66
Provided by: smitty
Category:
Tags: hpm | queries | single | table | team

less

Transcript and Presenter's Notes

Title: Single Table Queries Team 9A: HPM


1
Single Table Queries Team 9AHPM Pratt Chap 3
  • Justin Smith
  • Joe Lewis
  • Josh Matheney
  • Brad Swink
  • Justin Walker

2
Single Table Queries
  • There are four data manipulation language
    commands used is SQL. We have seen UPDATE,
    INSERT, and DELETE
  • The fourth data manipulation command is SELECT.
  • The SELECT command has many clauses, which allows
    one to query the data contained in the tables and
    ask many different questions.

3
Three Clauses
  • Select Statement Clauses are
  • SELECT Lists the columns from base tables or
    views to be in the table that will be the result
    of the projected command.
  • FROM Identifies the tables or views from which
    columns will be chosen to appear in the result
    table, and includes the tables or views needed to
    join tables to process the query.
  • WHERE Includes the conditions for row selection
    within a single table or view, and the conditions
    between tables or views for joining.

4
Example of clauses of the SELECT statement
  • Query For which products are there more than
    four units on hand?
  • SELECT PRODUCT_NAME, ON HAND
  • FROM PRODUCT_T
  • WHERE ON HAND gt 4

5
RESULT
  • PRODUCT NAME ON HAND
  • End Table 8
  • Computer Desk 5
  • 8-Drawer Desk 5

6
REMEMBER
  • Two special keywords can also be used along with
    the list of columns to display
  • SELECT DISTINCT will display a result table with
    no duplicate rows, where SELECT is used as a
    wildcard to indicate all columns
  • Clauses of a SELECT statement have to be kept in
    order, or a syntax error will occur and the query
    will not execute.
  • When using the SELECT clause to pick out the
    columns for a result table, the columns can be
    rearranged so that they will be ordered
    differently in the result.

7
Example of SELECT CLAUSE Rearranging
  • Query List the unit price , product name, and
    product ID for all products in the PRODUCT table.
  • SELECT UNIT_PRICE,PRODUCT_NAME,
  • PRODUCT_ID FROM PRODUCT_T

8
RESULT
  • UNIT PRICE PRODUCT NAME PRODUCT ID
  • 175 End Table 1
  • 200 Coffee Table 2
  • 375 Computer Desk 3
  • 650 Entertainment Center 4
  • 325 Writers Desk 5

9
Expressions
  • One may make their own expressions, which are
    mathematical manipulations of the data in the
    table, or one may take advantage of stored
    functions in SQL, such as SUM or AVG, to
    manipulate the chosen rows of data from the
    table.
  • The precedence rules for the order in which
    complex expressions are evaluated are the same as
    those used in other programming languages and in
    algebra.

10
Using Functions
  • Functions such as COUNT, MIN, MAX, SUM and AVG of
    certain columns in the columns list of a SELECT
    command may also be used to specify that the
    resulting table is to contain aggregated data
    instead of row-level data.
  • COUNT tallies only those rows that contain a
    value it disregards all null values
  • COUNT counts all rows selected by a query
    regardless of whether any of the rows contain
    null values.
  • SUM and AVG can only be used with columns that
    contain numeric data, but COUNT, COUNT, MIN, and
    MAX can be used with any data type.

11
WILD CARDS
  • Wild cards are paired with keywords and usually a
    string containing the characters that are known
    to be desired matches. is used to indicate all
    columns. is used to represent any collection of
    characters. The underscore, _, is used to
    represent exactly one character.

12
Comparison Operators
  • Comparison operators are used with numeric data,
    character data, and dates in SQL. The most widely
    used types are listed on the next slide

13
OPERATER MEANING
  • Equal to
  • gt Greater Than
  • lt Less than
  • gt Greater than or equal to
  • lt Less than or
  • equal to
  • ltgt Not equal to
  • ! Not equal to

14
Boolean Operators
  • More complex inquiries can be answered by
    adjusting the WHERE clause further. The Boolean
    or logical operators AND, OR, and NOT can be
    implemented.
  • AND Joins two or more conditions and returns
    results only when all conditions are true.
  • OR Joins two or more conditions and returns
    results when any conditions are true.
  • NOT Negates an expression

15
Ranges
  • The operators lt and gt are used to set a range of
    values. The keywords BETWEEN or NOT BETWEEN can
    be used as well.

16
IN and NOT IN
  • To match a list of values, consider using IN.
  • Query List all customers, who live in warmer
    states.
  • SELECT CUSTOMER_NAME, CITY, STATE, FROM
    CUSTOMER_T
  • WHERE STATE IN(FL,TX,CA,HI)

17
RESULTS
  • CUSTOMER CITY STATE
  • Contemporary Casuals Gainesville FL
  • Balue Furniture Piano TX
  • Impressions Sacramento CA
  • California Classics Santa Clara CA
  • M and H Casual Furniture Clearwater FL
  • Seminole Interiors Seminole FL
  • Kaneohe Homes Kaneohe HI

18
Sorting Results
  • Observing the preceding results, it would seem
    more sensible to list the California customers,
    followed by the Floridians, Hawaiians, and the
    Texans. That brings us to the other three basic
    parts of the SQL statements ORDER BY, GROUP BY,
    and HAVING.

19
  • ORDER BY sorts the final results rows in
    ascending or descending order.
  • GROUP BY Groups rows in an intermediate results
    table where the values in those rows are the same
    for one or more columns.
  • HAVING can only be used following a GROUP BY
    and acts as secondary WHERE clause, returning
    only those groups which meets specified condition.

20
Sorting Results (cont.)
  • The sorting order is determined by the order in
    which the columns are listed in the ORDER BY
    clause.
  • If sorting from high to low, use DESC as a
    keyword placed after the column used to sort.

21
Categorizing ResultsThe GROUP BY Clause
  • GROUP BY is used when paired with aggregate
    functions, such as SUM or COUNT. GROUP BY divides
    a table into subsets (by groups) then an
    aggregate function can be used to provide summary
    information for that group.

22
EXAMPLE
  • Query Count the number of customers with address
    in each sate to which we ship.
  • SELECT STATE, COUNT(STATE)
  • FROM CUSTOMER_T
  • GROUP BY STATE

23
Results
  • ST COUNT(STATE)
  • CA 2
  • CO 1
  • FL 3
  • HI 1
  • MI 1
  • NJ 2
  • NY 1
  • PA 1
  • TX 1
  • UT 1
  • WA 1
  • 11 rows selected

24
Important Note!!!!
  • While the GROUP BY clause seems straightforward,
    it can produce unexpected results if the logic of
    the clause is not remembered. When a GROUP BY is
    included, the columns allowed to be specified in
    the SELECT clause are limited. Only those columns
    with one value for each group can be included.
    Usually, the columns referenced in the SLECT
    statement must be referenced in the GROUP BY
    clause, unless the column is an argument function
    included in the SELECT clause.

25
  • Query Count the number of customer with address
    in each state to which we ship.
  • Select STATE, COUNT (STATE)
  • From Customer_T
  • Group By STATE
  • Results
  • ST COUNT(STATE)
  • CA 2
  • CO 1
  • FL 3
  • HI 1
  • MI 1
  • NJ 2
  • NY 1
  • PA 1
  • TX 1
  • UT 1
  • WA 1

26
  • While the group by clause seems straightforward,
    it can produce unexpected results from the logic
    of the clause is forgotten. When a group by is
    included, the columns allowed to be specified in
    the select clause are limited. Only those columns
    with a single value for each group can be
    included. Usually, the columns references in the
    select statement must be referenced in the group
    by clause, unless the column is an argument
    function included in the select clause.

27
Qualifying Results by CategoriesThe Having
Clause
  • The having clause acts like a WHERE clause, but
    it identifies groups that meet a criterion,
    rather than rows. To include more than one
    condition in the having clause, use AND, OR, and
    NOT just as in the WHERE Clause.

28
Chapter 3 PRATT
  • Simple Queries
  • One of the most important features of a database
    management system is its ability to answer a wide
    variety of question concerning the data in the
    database.
  • Query a question represented in a way that the
    DBMS can understand.
  • In SQL, the select command is used to query a
    database.
  • The most basic form of select command appears as
    Select From.
  • After the word select, specify the columns to be
    listed, this is called the Select clause, (or
    type to select all columns), and then after the
    word FROM , specify the table name that contains
    these columns, this is called the From clause.
    After the word where you can be also include the
    conditions, which is called the WHERE clause.

29
SELECT Command to select certain columns
  • SQL gt select customer_number, last, first,balance
  • 2 from customer
  • CUS LAST FIRST BALANCE
  • --- ---------- -------- ----------
  • 124 Adams Sally 818.75
  • 256 Samuels Ann 21.5
  • 311 Charles Don 825.75
  • 315 Daniels Tom 770.75
  • 405 Williams Al 402.75
  • 412 Adams Sally 1817.5
  • 522 Nelson Mary 98.75
  • 567 Dinh Tran 402.4
  • 587 Galvez Mara 114.6
  • 622 Martin Dan 1045.75
  • 10 rows selected.

30
Simple Conditions
  • A simple condition has the written form of a
    column name, comparison operator, and then either
    another column name or a value.
  • Notice the different comparison operators
  • lt gt lt gt lt gt
    !

31
Select command involving a comparsion
  • SQLgt select customer_number,last,first,balance
  • 2 from customer
  • 3 where balance gt credit_limit
  • CUS LAST FIRST BALANCE
  • --- ---------- -------- ----------
  • 315 Daniels Tom 770.75
  • 622 Martin Dan 1045.75

32
Compound Conditions
  • You can form compound conditions by connecting
    two or more simple conditions using the AND, OR,
    and NOT operators.
  • If multiple Boolean operators are used in a SQL
    statement, Not is evaluated first, then and, then
    OR.

33
SELECT command involving an OR condition
  • SQLgt select part_description
  • 2 from part
  • 3 where warehouse_number 3
  • 4 or units_on_handgt100
  • PART_DESCRIP
  • ------------
  • Iron
  • Corn popper
  • Washer
  • Griddle
  • Blender

34
Use of BETWEEN
  • Use the between operator to indicate a range of
    values in a condition. The Between operator is
    not an essential feature of SQL however it does
    make certain select commands simpler. The and
    operator creates an alternative method to the
    BETWEEN function.

35
Select command involving an and condition on a
single column
  • SQLgt select customer_number, last, first, balance
  • 2 from customer
  • 3 where balance gt 500 and balance lt 1000
  • CUS LAST FIRST
    BALANCE
  • --- ---------- --------
    ----------
  • Samuels Ann 1478.5
  • Williams Al 1097.25
  • Adams Sally 1097.25
  • 522 Nelson Mary 1401.25

36
Select command involving a between condition
  • SQL gt select customer_number, last, first,
    balance
  • 2 from customer
  • 3 where balance BETWEEN 500 and 1000
  • CUS LAST FIRST
    BALANCE
  • ----- ---------- ---------
    -------------
  • 256 Samuels Ann 1478.5
  • 405 Williams Al 1097.25
  • 412 Adams Sally 1097.25
  • 522 Nelson Mary 1401.25

37
The Use of Computed Columns
  • Computed columns are also used in SQL queries. A
    computed column is a column that does not exist
    in the database but can be computed using data in
    the existing columns.
  • Example of a Select query with a computed column.

38
SQLgt SELECT CUSTOMER_NUMBER, LAST, FIRST ,
(CREDIT_LIMIT BALANCE) 2 FROM CUSTOMER
3 WHERE CREDIT_LIMIT gt 1500
  • customer_number last_name first_Name
    (credit_limit balance)
  • ---------------------- ------------
    ------------- ----------------------------
  • 256 Samuels Ann
    1478.50
  • 405 Williams Al
    1097.25
  • 412 Adams Sally
    182.50
  • 522 Nelson Mary 1401.25

39
Using the Like command
  • Use LIKE to check for a value in a column that
    is similar to a string of characters. Use as a
    wildcard to represent a collection of characters.
  • Example
  • SELECT FIRST, LAST
  • FROM CUSTOMER
  • WHERE LAST LIKE St

40
Using the IN clause
  • An IN clause provides a way of phrasing certain
    conditions. It checks whether a column contains
    one of a particular set of values.

41
Sorting
  • Use the ORDER BY command to list data in a
    certain order. The column on which data is to be
    sorted is called a sort key.

42
Figure 3.16SQLgt
SELECT CUSTOMER_NUMBER, LAST, FIRST, BALANCE
2 FROM CUSTOMER 3 ORDER BY BALANCE
  • customer_number last_name first_Name balance
  • ---------------------- ------------
    ------------- ---------
  • 256 Samuels Ann
    21.50
  • 522 Nelson Mary
    98.75
  • 587 Galvez Mara
    114.60
  • 567 Dinh Tran
    402.40
  • 405 Williams Al
    402.75
  • 315 Daniels Tom 770.75
  • 123 Adams Sally
    818.75
  • 311 Charles Don
    825.75
  • 622 Martin Dan
    1045.75
  • 412 Adams Sally
    1817.50

43
Sorting with Multiple Keys in Descending Order
  • Sometimes it is necessary to sort by more than
    one key. The more important column is called the
    major key and the less important column is the
    minor key.

44
SQL Functions
45
Using the COUNT Function
  • SELECT COUNT()
  • FROM PART514
  • WHERE ITEM_CLASS 'HW
  • -----------
  • 4

46
PART PART_DESCRIPTION UNITS_ON_HAND IT
W UNIT_PRICE ------ -------------------
------- ---------------------- ------- -----
--------------- AX12 Iron
104 HW 3
24.95 AZ52 Dartboard
20 SG 2 12.95 BA74
Basketball 40 SG
1 29.95 BH22 Cornpopper
95 HW 3
24.95 BT04 Gas Grill 11
AP 2 149.99 BZ66
Washer 52 AP 3
399.99 CA14 Griddle
78 HW 3 39.99 CB03
Bike 44 SG
1 299.99 CX11 Blender
112 HW 3
22.95 CZ81 Treadmill 68
SG 2 349.95
47
Use of the Sum Function
  • Used to calculate the total of a column.
  • Column data type must be numeric.
  • Null values are ignored.
  • Ignored null values can cause incorrect results
    when SUM is used in other statistics are computed.

48
Use of Distinct
  • NOT a function
  • Eliminates redundancy when used with the COUNT
    function.
  • The following example shows the use of the
    Distinct function to ensure that each customer is
    unique.

49
Distinct Example
  • SQLgt SELECT CUSTOMER_NUMBER
  • 2 FROM ORDERS
  • CUS
  • ---
  • 124
  • 311
  • 315
  • 256
  • 522
  • 124
  • 522
  • 7 ROWS SELECTED
  • SQLgt SELECT DISTINCT(CUSTOMER_NUMBER) 2 FROM
    ORDERS
  • CUS
  • ---
  • 124
  • 311
  • 315
  • 256
  • 522

50
Nesting Queries
  • A nested query is a query placed inside of
    another query.
  • The inner query is called a subquery and is
    evaluated first.
  • The outer query is capable of use the results
    from the inner query.

51
Nested Query Example
  • SQLgt SELECT CUSTOMER_NUMBER, LAST, FIRST, BALANCE
  • 2 FROM CUSTOMER
  • 3 WHERE BALANCE gt
  • 4 (SELECT AVG(BALANCE)
  • 5 FROM CUSTOMER)
  • CUS LAST FIRST BALANCE
  • --- ---------- -------- ----------
  • 124 Adams Sally 818.75
  • 311 Charles Don 825.75
  • 315 Daniels Tom 770.75
  • 412 Adams Sally 1817.5
  • 622 Martin Dan 1045.75

52
Use of Grouping
  • Grouping creates groups of rows that share some
    common characteristic.
  • GROUP BY command allows the user to group data in
    a particular order and then calculate statistics
    if desired.

53
Example of GROUP By
  • SQLgt SELECT ORDER_NUMBER, SUM(NUMBER_ORDERED
    QUOTED_PRICE)
  • 2 FROM ORDER_LINE
  • 3 GROUP BY ORDER_NUMBER
  • 4 ORDER BY ORDER_NUMBER
  • ORDER SUM(NUMBER_ORDEREDQUOTED_PRICE)
  • ----- --------------------------------
  • 12489 241.45
  • 12491 549.98
  • 12494 1119.96
  • 12495 45.9
  • 12498 125.7
  • 12500 149.99
  • 12504 651.98
  • 7 rows selected.

54
Use of Having
  • The HAVING command is used to apply restriction
    to groups.
  • The restrictions apply to the groups created not
    the individual rows

55
Having Example
  • SQLgt SELECT ORDER_NUMBER, SUM(NUMBER_ORDERED
    QUOTED_PRICE)
  • 2 FROM ORDER_LINE
  • 3 GROUP BY ORDER_NUMBER
  • 4 HAVING SUM(NUMBER_ORDERED QUOTED_PRICE) gt
    200
  • 5 ORDER BY ORDER_NUMBER
  • ORDER SUM(NUMBER_ORDEREDQUOTED_PRICE)
  • ----- --------------------------------
  • 12489 241.45
  • 12491 549.98
  • 12494 1119.96
  • 12504 651.98

56
HAVING vs. WHERE
  • The HAVING clause and the WHERE clauses are
    similar but not identical.
  • The WHERE clause is used to apply restrictions to
    the rows of results of a query.
  • The HAVING clause is used to restrict the groups
    that are included.

57
Nulls
  • A Null is a condition where the value is unknown.
  • The following example show the use of NULL to
    find every customer without a street value.

58
Null Example
  • SQLgt SELECT CUSTOMER_NUMBER, LAST, FIRST
  • 2 FROM CUSTOMER
  • 3 WHERE STREET IS NULL
  • no rows selected

59
Chapter Review
60
Chapter Review
61
Justin Smiths Question
  • Find and correct the errors in the following
    Query
  • SELECT PRODUCT-DESCRIPTION, PRODUCT_FINISH,
    STANDARD_PRICE FROM PRODUCT_V
  • WERE PRODUCT_DESCRIPTION LIKE desk OR
    PRODUCT_DESCRIPTION OR PRODUCT_DESCRIPTION LIKE
    table AND UNIT_PRICE gt300

62
Joes Question
  • Using the HAVING clause what would be the output
    for the following?
  • SELECT CREDIT_LIMIT, COUNT()
  • FROM CUSTOMER
  • WHERE SLSREP_NUMBER '03'
  • GROUP BY CREDIT_LIMIT
  • HAVING COUNT() gt 1

63
Joshs Question
  • What would the SQL statement look like for the
    following output using the part_number,
    part_description, units_on_hand columns from the
    part table?
  • PART_NUMBER PART_DESCRIPTION UNITS_ON_HAND
  • ------------------- -------------------------
    ----------------------
  • BZ66 Washer 52
  • CZ81 Treadmill 68
  • AX12 Iron
    104
  • CA14 Griddle 78
  • BT04 Gas Grill 11
  • AZ52 Dartboard 20
  • BH22 Cornpopper 95
  • CX11 Blender 112
  • CB03 Bike 44
  • BA74 Basketball 40

64
Justin Walkers Question
  • What would be the input command and the output
    for the number of customers with balances greater
    than their credit limit?

65
Brads Question
  • What are the SQL Functions? Name and define each
    function.
Write a Comment
User Comments (0)
About PowerShow.com