1 / 29

Single-Table Queries

Single-Table Queries. A Guide to SQL - Chapter 4. SELECT-FROM-WHERE Compound conditions BETWEEN clause Computed fields LIKE clause IN clause NOT IN clause ORDER By clause Aggregate functions. DISTINCT Clause Nesting Queries Subqueries GROUP BY HAVING. Instructional Objectives.

khanh
Download Presentation

Single-Table Queries

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Single-Table Queries A Guide to SQL - Chapter 4

  2. SELECT-FROM-WHERE Compound conditions BETWEEN clause Computed fields LIKE clause IN clause NOT IN clause ORDER By clause Aggregate functions DISTINCT Clause Nesting Queries Subqueries GROUP BY HAVING Instructional Objectives

  3. Compound Conditions • AND • Each condition must be true for compound condition to be true • OR • At least one condition must be true for compound condition to be true • NOT • Reverses the truth or falsity of the original condition

  4. Example using AND • List the descriptions of all parts that are located in warehouse 3 and for which there are more than 25 units on hand. • Solution:

  5. Example using OR • List the descriptions of all parts that are located in warehouse 3 or for which there are more than 25 units on hand, or both • Solution:

  6. Example using NOT • List the Description of all parts that are not in warehouse 3. • Solution: Alternate method???

  7. BETWEEN clause • Is used to specify a range in the condition • List the number, name, and balance of all customers with balances greater than or equal to $2,000 and less than or equal to $5,000 • Solution: Alternate method???

  8. Computed or Calculated Fields • Are defined in the SELECT clause • Example: • List the number, name, and available credit for all customers • Solution:

  9. Another example of a calculated field • List the number, name, and available credit for all customers with at least $5,000 of available credit • Solution:

  10. LIKE clause • Used when matches are not exact; also use wildcard • Example: List the number, name, and complete address of every customer located on a street that contains the letters ‘Central’ • Solution:

  11. IN clause • Used to specify certain types of conditions (i.e. collection of values) • Example: List the number, name, street, credit limit for every customer with a credit limit of $5,000; $10,000 or $15,000 • Solution: Alternate method???

  12. NOT IN clause • List the number, and name of all customers who do not have a credit limit of $300, $500 or $1000 • Solution:

  13. ORDER BY Clause • Used to sort data; field that you sort on is called the sort key • Example: List the number, name, and balance of each customer. Sort by balance. • Solution: Assumes Ascending sort

  14. Another example… • List the number, name, street, and credit limit of all customers. Sort by name within descending credit limit. • Solution: Major sort key Minor sort key

  15. Aggregate or Built-in Functions • Include: COUNT, SUM, AVG, MIN, MAX • Example: How many parts are in item class HW? • Solution:

  16. Another Example • Find the total number of customers and the total of their balances • Solution:

  17. AVG, MIN, MAX • Find the total balance, average balance, highest balance, and lowest balance • Solution:

  18. DISTINCT • Find the number of each customer that current has an open order • Solution:

  19. SUBQUERY • A SELECT-FROM-WHERE clause within a SELECT-FROM-WHERE clause; inner clause (or subquery) is executed 1st, then outer clause • Used when dealing with two or more tables • Example: • List the order number for each order that contains an order line for a part located in warehouse 3.

  20. Solution

  21. GROUP BY Clause • Group records based on some common characteristic (sometimes then perform aggregate functions on these group of records) • Example: for each sales rep, list the rep #, and the average balance of the rep’s customers. Sort the records on rep #.

  22. Solution

  23. HAVING Clause • Used for comparing a group of records to something • Example: • For each sales rep with fewer than 4 customers, list the rep #, and the average balance of the rep’s customers. Order by rep #.

  24. Solution

  25. Having vs. Where • Example 1: • List each credit limit and the number of customers having each credit limit • Solution:

  26. Example 2 • List each credit limit and the number of customers having each credit limit; list only those credit limits held by more than one customer • Solution:

  27. Example 3 • List each credit limit and the total number of customers of sales rep 20 that have this limit • Solution:

  28. Example 4 • List each credit limit and the total number of customers of sales rep 20 that have this limit; list only those limits held by more than one customer • Solution:

  29. NULL Values • List the number and name of each customer with a null (or unknown) street value • Solution:

More Related