Single Table Queries Team 9A: HPM

1
Single Table QueriesTeam 9AHPM Pratt Chap 3

• Ashley Stringfellow
• Bridgette Nathan
• Ashley Wilson
• Jeremy Thielemier
• Dustin Kemp
• Clay Mosley

2
Single Table Queries

• Four data manipulation language commands are used
  in SQL. We have seen UPDATE, INSERT, and DELETE
• The fourth data manipulation language is SELECT.
• The select command, with its various clauses,
  allows one to query the data contained in the
  tables and ask many different questions.

3
Three Clauses

• The three clauses of the Select statement are
• SELECT Lists the columns from base tables or
  views to be into the table that will be the
  result of projected the 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 HAND4
• Result
• PRODUCT NAME ON HAND
• End table
  8
• Computer Desk
  5
• 8-Drawer Desk
  5

5

• Two special keywords can be used along with the
  list of columns to display
• SELECT DISTINCT would display a result table with
  out duplicate rows, where SELECT is used as a
  wildcard to indicate all columns
• Clauses of a SELECT statement must 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.

6
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
• 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

7
Expressions

• One may create expressions, which are
  mathematical manipulations of the data in the
  table, or one may take advantage of stored
  functions, 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.

8
Using Functions

• Functions such as COUNT, MIN, MAX, SUM, and AVG
  of specified columns in the columns list of a
  SELECT command may be used to specify that the
  resulting answer table is to contain aggregated
  data instead of row-level data.
• COUNT tallies only those rows that contain a
  value it ignores 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 numeric
  columns, But COUNT, COUNT (), MIN, and MAX can
  be used with any data type.

9
Wild Cards

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

10
Comparison Operators

• Comparison operators can be used with numeric
  data, character data, and dates in SQL. The most
  common types are listed on the next slide

11

• Operator Meaning
• Equal To
• Greater Than
• Greater Than or Equal
  To
• Not Equal To
• ! Not Equal To

12
Boolean Operators

• More complex questions can be answered by
  adjusting the WHERE clause further. The Boolean
  or logical operators AND, OR, and NOT can be
  used.
• 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

13
Ranges

• The comparison operators are used to
  establish a range of values. The Keywords
  BETWEEN OR NOT BETWEEN can also be used.

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

• Results
• CUSTOMER_NAME CITY
  STATE
• Contemporary Casuals Gainesville
  FL
• Balue Furniture Plano
  TX
• Impressions Sacramento
  CA
• California Classics Santa Clara
  CA
• M and H Casual Furniture Clearwater
  FL
• Seminole Interiors Seminole
  FL
• Kaneohe Homes Kaneohe
  HI

7 rows selected
15
Sorting ResultsThe ORDER BY Clause

• Looking at the preceding results, it may seem
  that it would make more sense to list the
  California customers, followed by the Floridians,
  Hawaiians, and Texans. That brings us to the
  other three basic parts of the SQL statements

16
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 a secondary
WHERE clause, returning only those groups which
meet a specified condition.
17
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.
18
Categorizing ResultsThe GROUP BY ClauseGROUP
BY is particularly 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.
19
Query Count the number of customers with
addresses in each state to which we ship.SELECT
STATE, COUNT (STATE) FROM
CUSTOMER_T GROUP BY
STATEResultsST
COUNT(STATE)CA 2CO
1FL 3HI
1MI 1NJ
2NY 1PA
1TX 1UT
1WA 111 rows selected
20
While the GROUP BY clause seems straightforward,
it can produce unexpected results if 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 referenced in the
SELECT statement must be referenced in the GROUP
BY clause, unless the column is an argument
function included in the SELECT clause.
21
The HAVING clause acts like a WHERE clause, but
it identifies groups that meet a criterion,
rather than rows.To included more than one
condition in the HAVING clause, use AND, OR, and
NOT just as in the WHERE Clause.
Qualifying Results by CategoriesThe HAVING
Clause
22
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 questions
  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 world 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
  also include the conditions, which is called the
  WHERE clause.

23
SELECT Command to select certain columns

• SQL 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.

24
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
• !

25
SELECT command involving a comparison

• SQL select customer_number, last, first,
  balance
• 2 from customer
• 3 where balance credit_limit
• CUS LAST FIRST BALANCE
• --- ---------- -------- ---------
• 315 Daniels Tom 770.75
• 622 Martin Dan 1045.75

26
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.

27
SELECT command involving an OR condition

• SQL select part_description
• 2 from part
• 3 where warehouse_number '3'
• 4 or units_on_hand 100
• PART_DESCRIP
• ------------
• Iron
• Corn popper
• Washer
• Griddle
• Blender

28
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.

29
SELECT command involving an AND condition on a
single column

• SQL select customer_number, last, first, balance
• 2 from customer
• 3 where balance 500 AND balance
• CUS LAST FIRST BALANCE
• --- ---------- --------
  ----------------------
• 256 Samuels Ann 1478.5
• 405 Williams Al 1097.25
• 412 Adams Sally 1097.25
• 522 Nelson Mary 1401.25

30
SELECT command involving a BETWEEN condition

• SQL 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

31
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. Next is an example it is
  a SELECT command involving a computed column.

32
Figure 3.12SQLSELECT
CUSTOMER_NUMBER, LAST, FIRST(CREDIT_LIMIT-BALANCE
2 FROM CUSTOMER993 WHERE CREDIT_LIMIT 1500
CUS LAST FIRST (CREDIT
LIMIT-BAL) ---------------------------------------
------------------------ 256 Samuels
Ann 1478.5 405 Williams Al
1097.25 412 Adams Sally
182.5 522 Nelson Mary 1401.25
33
Use of LIKE
To check for a value in a character column that
is similar to a particular string of characters,
use the LIKE clause. The wildcard represents
any collection of character. The _wildcard
represents any single character.
34
Use of IN
An IN clause provides a concise way of phrasing
certain conditions. Basically, to check whether
a column contains one of a particular set of
values, you will use the IN clause.
35
Sorting
You use the ORDER BY command to list data in a
specific order. The column on which data is to
be sorted is called a sort key. In this next
example, you will see that the sort key is the
BALANCE column. To sort the output, use the
ORDER BY clause, followed by the sort key.
36
Figure 3.16SQLSELECT
CUSTOMER_NUMBER, LAST FIRST BALANCE2 FROM
CUSTOMER99 3 ORDER BY BALANCE

• CUS LAST FIRST BALANCE
• -----------------------------------------------
• 256 Samuels Ann 21.5
• 522 Nelson Mary 98.75
• 587 Galvez Mara 114.6
• 567 Dinh Tran 402.4
• 405 Williams Al 402.75
  
• 405 Daniels Tom 770.75
• 124 Adams Sally 818.75
• 311 Charles Don 825.75
• 622 Martin Dan 1045.75
• 412 Adams Sally 1817.5

37
Sorting with Multiple Keys in Descending Order
Sometimes you might need to sort data by more
than one key. This next example involves sorting
on multiple keys-CREDIT_LIMIT and LAST and
using descending order of one of t he keys. If
you are sorting on more than one column, the more
important column is called the major key and the
less important column is called the minor key.
To sort in descending order, you follow the name
of the sort key with the DESC operator.
38
Using Functions

• AVG Calculates the average value in a data
  series
• COUNT Determines the number of rows in a data
  series
• MAX Determines the number of rows in a data
  series
• SUM Calculates a total of the values in a data
  series

39
SQL Functions

40
Use of Count Functions

• Counts the number of rows in a table
• Rather than picking one column, most
  implementations of SQL allows you to use the
  asterisk () to represent any column

41
SQL SELECT COUNT () 2 FROM PART 3 WHERE
ITEM_CLASSHW COUNT()--------- 4
42
Use of Sum Function

• Used to calculate totals
• Must specify the column to total and the column
  data type must be numeric
• Null values in numeric columns can cause strange
  results and some calculations may be incorrect.

43
Use of Distinct

• Not a function
• Useful when used in conjunction with COUNT
  function to eliminate redundant results
• In the following example, by using DISTINCT
  command it lists the customer number only once to
  ensure uniqueness

44
Example of Distinct

• SQL SELECT CUSTOMER_NUMBER
• 2 FROM ORDERS
• CUS
• ---
• 124
• 311
• 315
• 256
• 522
• 124
• 522
• 7 rows selected.

• SQL SELECT DISTINCT (CUSTOMER_NUMBER)
• 2 FROM ORDERS
• CUS
• ---
• 124
• 256
• 311
• 315
• 522
• 5 rows selected

45
Nesting Queries

• When one SQL query is placed inside another, it
  is called a nested query. The inner query,
  called a subquery, is evaluated first. Then the
  outer query can use the results of the subquery
  to find its results.

46
Example of Nested Query

• SQL SELECT CUSTOMER_NUMBER, LAST, FIRST, BALANCE
• 2 FROM CUSTOMER
• 3 WHERE BALANCE
• 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 1017.50
• 622 Martin Dan 1045.75
• In this case, you use a subquery to obtain the
  average balance (519.58). Then you can compare
  each customers balance with the average

47
Use of Grouping

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

48
Example of GROUP BY
SQL SELECT ORDER_NUMBER, SUM(NUMBER_ORDEREDQUOTE
D_PRICE) 2 FROM ORDER_LINE 3 GROUP BY
ORDER_NUMBER 4 ORDER BY ORDER_NUMBER
You must use ORDER BY to sort the ORDERED_NUMBER
data.
49
Use of Having

• The HAVING command is used for groups.

50
Example of HAVING

• SQL SELECT ORDER_NUMBER, SUM(NUMBER_ORDEREDQUOTE
  D PRICE)
• 2 FROM ORDER_LINE
• 3 GROUP BY ORDER_NUMBER
• 4 HAVING SUM(NUMBER_ORDEREDQUOTED_PRICE)
  200
• 5 ORDER BY ORDER_NUMBER

51
HAVING VS. WHERE

• It seems as if the WHERE clause and the HAVING
  clause are identical, but they are not. The
  WHERE clause is used to limit the rows in the
  result of a SQL command, whereas the HAVING
  clause is used to limit groups that are included.

52
NULLS

• Sometimes you might run into a condition where
  the value is unknown (null).
• The following example prompts us to list the
  customer number, last name, and first name of
  very customer whose street value is null.
• You might expect the condition to be something
  like STREET NULL. The correct format is
  actually STREET IS NULL. To select a customer
  with a street value that is not null would look
  like, STREET IS NOT NULL.

53
Example of NULLS
SQL SELECT CUSTOMER_NUMBER,LAST, FIRST 2
FROM CUSTOMER 3 WHERE STREET IS NULL no
rows selected
No customers has a null street value in the
CUSTOMER table therefore no rows are retrieved
54
Chapter Review
55
Chapter Review
56
Jeremys Question
57
Ashley Question
58
Clays Question

• What would the input be for the following
  question
• What are the customer numbers, first names, and
  balances of the customers with the last name of
  Smith and with an account balance less than their
  credit limit?

59
Dustins Question

• What are the two different ways of sorting the
  output data and how are they used?

60
Ashleys Question

• What would be the input for the following
  statement
• How many different items were ordered on order
  number 1077?

61
Bridgettes Question

• If you have the input from the ORDER_LINE table
• SQL SELECT ORDER_NUMBER, SUM(NUMBER_ORDERED
  QUOTED_PRICE)
• 2 FROM ORDER_LINE
• 3 GROUP BY ORDER_NUMBER
• 4 HAVING SUM(NUMBER_ORDERED QUOTED_PRICE) 200
• 5 ORDER BY ORDER_NUMBER
• What will your output be?