Title: Single Table Queries Team 9A: HPM
1Single Table Queries Team 9AHPM Pratt Chap 3
- Justin Smith
- Joe Lewis
- Josh Matheney
- Brad Swink
- Justin Walker
2Single 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.
3Three 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.
4Example 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
5RESULT
- PRODUCT NAME ON HAND
- End Table 8
- Computer Desk 5
- 8-Drawer Desk 5
6REMEMBER
- 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.
7Example 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
8RESULT
- 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
9Expressions
- 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.
10Using 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.
11WILD 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.
12Comparison 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
14Boolean 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
15Ranges
- The operators lt and gt are used to set a range of
values. The keywords BETWEEN or NOT BETWEEN can
be used as well.
16IN 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)
17RESULTS
- 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
18Sorting 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.
20Sorting 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.
21Categorizing 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.
22EXAMPLE
- 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
23Results
- 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
24Important 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.
27Qualifying 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.
28Chapter 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.
29SELECT 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.
30Simple 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
!
31Select 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
32Compound 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.
33SELECT 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
34Use 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.
35Select 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
36Select 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
37The 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.
38SQLgt 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
39Using 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
40Using 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.
41Sorting
- 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
43Sorting 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.
44SQL Functions
45Using the COUNT Function
- SELECT COUNT()
- FROM PART514
- WHERE ITEM_CLASS 'HW
-
- -----------
- 4
46PART 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
47Use 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.
48Use 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.
49Distinct 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
50Nesting 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.
51Nested 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
52Use 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.
53Example 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.
54Use of Having
- The HAVING command is used to apply restriction
to groups. - The restrictions apply to the groups created not
the individual rows
55Having 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
56HAVING 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.
57Nulls
- 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.
58Null Example
- SQLgt SELECT CUSTOMER_NUMBER, LAST, FIRST
- 2 FROM CUSTOMER
- 3 WHERE STREET IS NULL
- no rows selected
59Chapter Review
60Chapter Review
61Justin 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
62Joes 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
63Joshs 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
64Justin Walkers Question
- What would be the input command and the output
for the number of customers with balances greater
than their credit limit?
65Brads Question
- What are the SQL Functions? Name and define each
function.