1 / 13

Advanced Select Statements

This guide delves into advanced SQL SELECT statements, focusing on naming columns using aliases to enhance output readability. Learn how to utilize arithmetic expressions within SELECT lists for projections and analyses, leveraging constants and functions such as AVG and SUM. Understand the importance of the WHERE clause, applying logical operators (AND, OR, NOT) and character matches (LIKE, NOT LIKE) for robust data filtering. Through practical examples and insights on precedence hierarchy, become proficient in crafting precise SQL queries to manipulate and analyze datasets effectively.

bryce
Download Presentation

Advanced Select Statements

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. Advanced Select Statements

  2. Select List Variations • SELECT * • Column Naming • Arithmetic Expressions • Constants • Strings

  3. Column Naming Using column aliases we can control how the column names are displayed in our output. SELECT au_fname First_Name FROM authors The output will reflect this column name. First_Name -------------- Joe Dan Sue

  4. Expressions Arithmetic expressions can be used in the select list of a SELECT statement. • Column (+-*/) Column • eg. Unit_cost * items_sold • Constant (+-*/) Column • eg. Unit_cost * 1.1 • Constant • Functions • eg. Avg(sales)

  5. Expressions allow us... To make projections To do “What if” analyses Alter reported data without altering the physical data

  6. Precedence Hierarchy of Arithmetic Operators ParenthesesMultiplication, DivisionAddition, Subtraction (2+2*3)/4 = 2

  7. The Where Clause • Arithmetic (+-*/) /Comparative (><=,!=) Operators WHERE cost * 2 >= 20 WHERE state != ‘CA’ • Logical Operators (AND, OR, NOT) • Ranges (BETWEEN, NOT BETWEEN) • Lists (IN, NOT IN) WHERE state NOT IN (‘CA’, ‘MD’, ‘NJ’) • Unknowns (IS NULL, IS NOT NULL) • Character Matches (LIKE, NOT LIKE) WHERE au_lname LIKE (‘Gr%’) OR au_lname LIKE (‘_reen’)

  8. Precedence Hierarchy of Logical Operators ParenthesesMultiplication, DivisionAddition, SubtractionNOTANDOR

  9. Lists • Can save a lot of typing! • Can use a list or a sub query List example:Select au_lname, au_fname from authors where state in (‘CA’, ‘IN’, ‘MD’) SubQuery Example:Select pub_name from publishers where pub_id in (select pub_id from titles where total_sales > 3000)

  10. Character Matches • % - 0 or more character • _ - 1 character • @ - lets you escape a wildcard • You can use 1 or more wildcards in a string. Eg. I%a = Indiana, Iowa, Ina-goda-da-vida

  11. Things to Remember • Logical operators can only be used with the WHERE clause • The wildcards % and _ can only be used with LIKE and NOT LIKE • Wildcards can be used before and after the search string. • Strings must be quoted • NULL is not the same as zero

  12. Aggregate Functions • SUM ([DISTINCT] expr) • The total sum of values in expression • AVG ([DISTINCT] expr) • The average of values in the expression • COUNT ([DISTINCT] expr) • The number of non-null values in the expression • MAX (expr) • The highest value in the expression • MIN (expr) • The lowest value in the expression

  13. Syntax SELECT avg(price * 2) from titles • returns the average of all prices * 2 SELECT sum (price) from titles • return the sum of the prices

More Related