SlideServe
Télécharger Obtenir Pro
Menu
Accueil Pro
Préférences
ApparenceLight Aide et commentaires
© SlideServe · v2026

Advanced Select Statements

bryce avatar
bryce
13 diapositives · 262 Views · Sep 04, 2014

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.

Advanced Select Statements - slide 1
01 / 13Advanced Select Statements
1 / 17

Télécharger la présentation

Advanced Select Statements
Une image / un lien ci-dessous est fourni (tel quel) pour télécharger la présentation

Politique de téléchargement : le contenu du site vous est fourni TEL QUEL pour votre information et votre usage personnel ; il ne peut être vendu, concédé sous licence ni partagé sur d’autres sites sans l’accord de son auteur.Le contenu vous est fourni TEL QUEL, pour votre information et votre usage personnel uniquement. Téléchargez la présentation en cliquant sur ce lien. Lors du téléchargement, si vous ne parvenez pas à télécharger une présentation, l’éditeur a peut-être supprimé le fichier de son serveur.Pendant le téléchargement, si vous ne parvenez pas à obtenir une présentation, le fichier a peut-être été supprimé par l’éditeur.

F I N

Plus de contenu associé

Transcription de la présentation

  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