1 / 8

4 c . Structured Query Language - Built-in Functions

CSCI N207 Data Analysis with Spreadsheets. 4 c . Structured Query Language - Built-in Functions. Lingma Acheson Department of Computer and Information Science IUPUI. Built-In Functions. Functions – an agent that you can use to finish a task so you don’t need to do it yourself.

mostyn
Download Presentation

4 c . Structured Query Language - Built-in Functions

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. 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. ?

More Related