Chapter 4 Single-Table Queries

50 %
50 %
Information about Chapter 4 Single-Table Queries
Technology

Published on March 8, 2014

Author: dyang10

Source: slideshare.net

A Guide to SQL, Eighth Edition Chapter Four Single-Table Queries

Objectives • Retrieve data from a database using SQL commands • Use simple and compound conditions in queries • Use the BETWEEN, LIKE, and IN operators in queries • Use computed columns in queries A Guide to SQL, Eighth Edition 2

Objectives (continued) • Sort data using the ORDER BY clause • Sort data using multiple keys and in ascending and descending order • Use aggregate functions in a query • Use subqueries • Group data using the GROUP BY clause A Guide to SQL, Eighth Edition 3

Objectives (continued) • Select individual groups of data using the HAVING clause • Retrieve columns with null values A Guide to SQL, Eighth Edition 4

Constructing Simple Queries • What is a query ? – Question represented in a way that the DBMS can understand • How do you implement in SQL? – Use SELECT command • Are there any special formatting rules? – No A Guide to SQL, Eighth Edition 5

Constructing Simple Queries (continued) • SELECT-FROM-WHERE statement – SELECT columns to include in result – FROM table containing columns – WHERE any conditions to apply to the data WHERE clause is optional A Guide to SQL, Eighth Edition 6

Retrieving Certain Columns and Rows • Use SELECT command to retrieve specified columns and all rows – List the number, name, and balance of all customers • No WHERE clause needed, because all customers are requested A Guide to SQL, Eighth Edition 7

Retrieving Certain Columns and Rows (continued) A Guide to SQL, Eighth Edition 8

Retrieving All Columns and Rows • Use an asterisk (*) to indicate all columns in the SELECT clause • Will list all columns in the order used when table was created • List specific columns in SELECT clause to present columns in a different order A Guide to SQL, Eighth Edition 9

Retrieving All Columns and Rows (continued) A Guide to SQL, Eighth Edition 10

Using a WHERE Clause • WHERE clause – Used to retrieve rows that satisfy some condition – What is the name of customer number 148? • Simple Condition – Column name, comparison operator followed by either a column name or a value A Guide to SQL, Eighth Edition 11

Using a WHERE Clause (continued) A Guide to SQL, Eighth Edition 12

Using a WHERE Clause (continued) A Guide to SQL, Eighth Edition 13

Using a WHERE Clause (continued) • Simple conditions can compare columns A Guide to SQL, Eighth Edition 14

Using Compound Conditions • Compound conditions – Connect two or more simple conditions with AND, OR, and NOT operators • AND operator: all simple conditions are true • OR operator: any simple condition is true • NOT operator: reverses the truth of the original condition A Guide to SQL, Eighth Edition 15

Using Compound Conditions (continued) A Guide to SQL, Eighth Edition 16

Using Compound Conditions (continued) A Guide to SQL, Eighth Edition 17

Using Compound Conditions (continued) A Guide to SQL, Eighth Edition 18

Using the BETWEEN Operator • Use instead of AND operator • Use when searching a range of values • Makes SELECT commands simpler to construct • Inclusive – When using BETWEEN 2000 and 5000, values of 2000 or 5000 would be true A Guide to SQL, Eighth Edition 19

Using the BETWEEN Operator (continued) A Guide to SQL, Eighth Edition 20

Using Computed Columns • Computed column – Does not exist in the database but is computed using data in existing columns • Arithmetic operators – + for addition – - for subtraction – * for multiplication – / for division A Guide to SQL, Eighth Edition 21

Using Computed Columns (continued) A Guide to SQL, Eighth Edition 22

Using Computed Columns (continued) • Use AS clause to assign a name A Guide to SQL, Eighth Edition 23

Using the LIKE Operator • Used for pattern matching • 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, Eighth Edition 24

Using the LIKE Operator (continued) A Guide to SQL, Eighth Edition 25

Using the IN Operator • Concise phrasing of OR conditions A Guide to SQL, Eighth Edition 26

Sorting • By default, no defined order in which results are displayed • Use ORDER BY clause to list data in a specific order A Guide to SQL, Eighth Edition 27

Using the ORDER BY Clause • Sort key or key – Column on which data is to be sorted • Ascending is default sort order A Guide to SQL, Eighth Edition 28

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, Eighth Edition 29

Additional Sorting Options (continued) A Guide to SQL, Eighth Edition 30

Using Functions • Aggregate functions – Apply to groups of rows A Guide to SQL, Eighth Edition 31

Using the COUNT Function • Counts the number of rows in a table • Can use asterisk (*) to represent any column A Guide to SQL, Eighth Edition 32

Using the SUM Function • Used to calculate totals of columns • Column must be specified and must be numeric • Null values are ignored A Guide to SQL, Eighth Edition 33

Using the AVG, MAX, and MIN Functions • Numeric columns only • Ignores nulls A Guide to SQL, Eighth Edition 34

Using the DISTINCT Operator • Eliminates duplicate values • Used with COUNT function A Guide to SQL, Eighth Edition 35

Using the DISTINCT Operator (continued) A Guide to SQL, Eighth Edition 36

Using the DISTINCT Operator (continued) A Guide to SQL, Eighth Edition 37

Nesting Queries • Query results require two or more steps • Subquery: an inner query placed inside another query • Outer query uses subquery results A Guide to SQL, Eighth Edition 38

Nesting Queries (continued) A Guide to SQL, Eighth Edition 39

Nesting Queries (continued) A Guide to SQL, Eighth Edition 40

Grouping • Grouping: creates groups of rows that share common characteristics • Calculations in the SELECT command are performed for the entire group A Guide to SQL, Eighth Edition 41

Using the GROUP BY Clause • Group data on a particular column • Calculate statistics A Guide to SQL, Eighth Edition 42

Using the GROUP BY Clause (continued) A Guide to SQL, Eighth Edition 43

Using a HAVING Clause • Used to restrict groups that will be included A Guide to SQL, Eighth Edition 44

Having vs. Where • WHERE: limit rows • HAVING: limit groups • Can use together if condition involves both rows and groups A Guide to SQL, Eighth Edition 45

Having vs. Where (continued) A Guide to SQL, Eighth Edition 46

Nulls • Condition that involves a column that can be null – IS NULL – IS NOT NULL A Guide to SQL, Eighth Edition 47

Summary • Create queries that retrieve data from a single table using SELECT commands • Comparison operators – =, >,=>,<,=<, or <>, or != • Compound conditions – AND,OR, and NOT • Use the BETWEEN operator • Use the LIKE operator A Guide to SQL, Eighth Edition 48

Summary (continued) • IN operator • ORDER BY clause • Aggregate functions – COUNT, SUM, AVG, MAX, and MIN – DISTINCT operator • Subqueries • GROUP BY – HAVING • NULL A Guide to SQL, Eighth Edition 49

Add a comment

Related presentations

Related pages

A Guide to MySQL, Chapter 4: Single-Table Queries

CIS 331: Database Management Using SQL A Guide to MySQL, Chapter 4: Single-Table Queries. Objectives: This chapter introduces the student to the relations ...
Read more

A Guide to SQL, Sixth Edition 1 Chapter 4 Multiple-Table ...

... Sixth Edition 1 Chapter 4 Multiple-Table Queries." ... Sixth Edition 1 Chapter 3 Single-Table Queries. ... 1 Single Table Queries. 2 Objectives ...
Read more

ACCESS Chapter 4 Tables and Queries - IT Division ...

ACCESS CHAPTER 4 Tables and Queries Learning Objectives: Define table structure Enter data into a table Alter table structure ... Create single table queries
Read more

PPT - Single-Table Queries PowerPoint Presentation - ID ...

Single-Table Queries. A Guide to SQL - Chapter 4. SELECT-FROM-WHERE Compound conditions BETWEEN clause Computed fields LIKE clause IN clause NOT IN ...
Read more

Chapter 6: Writing SQL Queries: Part 1 – Single Table ...

Chapter 6: Writing SQL Queries: Part 1 – Single Table Queries. playback trouble? 2012 ...
Read more

Chapter 4. Using PostGIS: Data Management and Queries

Chapter 4. Using PostGIS: Data Management and Queries. Table of Contents. 4.1. GIS Objects 4.1.1. OpenGIS WKB and WKT 4.1.2. PostGIS EWKB, EWKT and ...
Read more

Chapter 4. Queries - Wake Forest University

Chapter 4. Queries; Prev Next: Chapter 4. Queries. This chapter describes how you perform queries, ...
Read more

MS Access - Chapter 4 - Queries - YouTube

MS Access - Chapter 4 - Queries ugur celik. Subscribe Subscribed Unsubscribe 3 3. Loading... Loading... ... Standard YouTube License; Loading...
Read more

Chapter 4 – Queries - Pearson Schools and FE Colleges ...

Chapter 4 – Queries 38 Chapter 4 ... In Chapter 5 we’ll look at other types of query, and in Chapter 6 we’ll look at more advanced form design features.
Read more

Chapter 2: Writing Single Table Queries - Building Your ...

... writing by writing single table queries. ... single table queries. Some topics covered in this chapter ... SQL Queries: Download: 4:
Read more