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

4 c . Structured Query Language - Built-in Functions

mostyn avatar
mostyn
8 diapositives · 246 Views · Jul 27, 2014

This guide provides an overview of built-in SQL functions essential for data analysis using spreadsheets. Learn how to utilize functions like SUM, AVG, MIN, MAX, and COUNT to efficiently perform calculations on your data. Each function is explained with practical SQL examples, highlighting their typical use cases. Discover how to calculate totals, averages, and counts relevant to your projects and employees, and gain insights from your data effortlessly.

4 c . Structured Query Language - Built-in Functions - slide 1
01 / 84 c . Structured Query Language - Built-in Functions
1 / 17

Télécharger la présentation

4 c . Structured Query Language - Built-in Functions
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. CSCI N207 Data Analysis with Spreadsheets 4c. Structured Query Language- Built-in Functions Lingma Acheson Department of Computer and Information Science IUPUI

  2. Built-In Functions • Functions –an agent that you can use to finish a task so you don’t need to do it yourself. • Built-In functions –The agent is already created for you by the database tool. You only need to know which agent to use and provide information needed to accomplish the task. • SQL built-in functions • SUM • AVE • MAX • MIN • COUNT

  3. Built-In Functions – SUM • SUM(fieldName):display the total of all the values in the field. The field must be a numeric type. • E.g. Show the total maximum hours of all the projects SELECT SUM(MaxHours) FROM PROJECT; • E.g. Show the total hours worked by employee number 10 SELECT SUM(HoursWorked) FROM ASSIGNMENT WHERE EmployeeNumber = 10; • E.g. Show the total hours worked by employee number 10, but give a meaningful field name in the query SELECT SUM(HoursWorked) AS TotalHours FROM ASSIGNMENT WHERE EmployeeNumber = 10;

  4. Built-In Functions – AVG • AVG(fieldName):display the average of all the values in the field. The field must be a numeric type. • E.g. Show the average maximum hours of all the projects SELECT AVG(MaxHours) FROM PROJECT; • E.g. Show the average hours worked by employee number 10 SELECT AVG(HoursWorked) FROM ASSIGNMENT WHERE EmployeeNumber = 10; • E.g. Show the average hours worked by employee number 10, but give a meaningful field name in the query SELECT AVG(HoursWorked) AS AverageHours FROM ASSIGNMENT WHERE EmployeeNumber = 10;

  5. Built-In Functions – MIN and MAX • MIN(fieldName):display the smallest value of all the values in the field. The field must be a numeric type. • E.g. Show the smallest maximum hours of all the projects SELECT MIN(MaxHours) FROM PROJECT; • E.g. Show the highest hours worked from all the employees SELECT MAX(HoursWorked) FROM ASSIGNMENT;

  6. Built-In Functions – COUNT • COUNT(*):count how many rows in total in a table • E.g. How many projects so far? SELECT COUNT(*) FROM PROJECT; • E.g. How many finished projects so far? SELECT COUNT(*) FROM PROJECT WHERE EndDate IS NOT NULL;

  7. Built-In Functions – COUNT • COUNT(FieldName): count how many values in that field, including duplicate values, FieldName doesn’t have to be numeric. • E.g. How many department names in the project table? SELECT COUNT(department) FROM PROJECT; • E.g. How many finished projects so far? SELECT COUNT(EndDate) FROM PROJECT;

  8. Embed Math Functions • Standard math calculations can be done using SQL. • E.g. Show all the projects’ MaxHours and their cost if the cost is $18.5 per hours. SELECT ProjectID, ProjectName, MaxHours, MaxHours * 18.5 AS MaxProjectCost FROM PROJECT; • E.g. Show hours worked for all the employees and their wage if the pay is $9 dollars per hour. SELECT EmployeeNumber, HoursWorked, HoursWorked * 9 AS Wage FROM ASSIGNMENT; - E.g., Display a list of employee numbers with their hoursworked, wage, bonus and total pay. Wage is calculated by hoursworked times 10. Bonus is calculated by hoursworked times 3. Total pay is the sum of wage and bonus. ?