Single-Row Functions

Single-Row Functions

SINGLE-ROW FUNCTIONS Lecture 9 SQL Functions Functions are very powerful feature of SQL and can be used to do the following: Perform a calculation on data Modify individual data items

Manipulate output of groups of rows Format dates and numbers for display Convert column data types Two Types of SQL Functions There are two distinct types of functions: Single-Row Functions These functions operate on single rows only and return one result per row. There are different types of single-row

functions Multiple-Row Functions Functions can manipulate groups of rows to give one result per group of rows. These functions are known as group functions Single-Row Functions Single row functions:

Manipulate data items Accept arguments and return one value Act on each row returned Return one result per row May modify the data type Can be nested Can be used in SELECT, WHERE, and ORDER BY clauses

Accept arguments which can be a column or an expression Syntax: function_name [(arg1, arg2,...)] Single-Row Functions This lesson covers the following single -row functions: Character functions: accept character input and can return both character and number values

Number functions: Accept numeric input and return numeric values Character Functions Character Functions Casemanipulation 1. LOWER 2. UPPER

3. INITCAP Charactermanipulation 1. SUBSTR 2. LENGTH Character Functions: Case Manipulation Functions

These functions convert case for character strings. See (Example 1, Example 2) Function result LOWER(SQL Course)

sql course UPPER(SQL Course) SQL COURSE INITCAP(SQL Course) Sql Course

Character Functions: Case Manipulation Functions Example1: SELECT 'The job id for '||UPPER(last_name)||' is ' ||LOWER(job_id) AS "EMPLOYEE DETAILS" FROM employees;

Example2: Display the employee number, name, and department number for employee Higgins: Character Functions: Character Manipulation Functions Function

Purpose LENGTH(Column|expression) Returns the number of characters in the expression SUBSTR(column|expression,m [,n])

Returns specified characters from character value starting at character position m,n character long (if m is negative the count starts and the end of the character value . If n is omitted all characters to the end of the string are returned Character Functions:

Character Manipulation Functions These functions manipulate character strings. For example: Function Result LENGTH('HelloWorld')

10 SUBSTR('HelloWorld',1,5) Hello Character Functions: Character Manipulation Functions (Cont.)

Example: SELECT employee_id, job_id,LENGTH (last_name) FROM employees WHERE SUBSTR(job_id, 4) = 'REP'; Number Functions ROUND: Rounds value to specified decimal (Example 3) Syntax: ROUND(column|expression, n) :Rounds the column, expression, or

value to n decimal places, or, if n is omitted, no decimal places. Example: ROUND(45.926, 2) 45.93 Number Functions TRUNC: Truncates value to specified decimal (Example 4)

Syntax: TRUNC(column|expression,n) Truncates the column, expression, or value to n decimal places, or, if n is omitted, then n defaults to zero Example: TRUNC(45.926, 2) 45.92

Number Functions MOD: Returns remainder of division (Example 5) Syntax: MOD(m,n) Returns the remainder of m divided by n Example: MOD(1600, 300) 100

Example 3 Example 4 Example 5

Recently Viewed Presentations

  • GCSE Food Preparation and Nutrition, Mrs Giles  Brownhills

    GCSE Food Preparation and Nutrition, Mrs Giles Brownhills

    - it doesn't 'like' water). When an emulsiļ¬er is added to a mixture of oil and water, its molecules arrange themselves so that they prevent the oil and water from separating. The mixture is now an emulsion. This is why...
  • Speed continued - Mrs. Blackmon's Science Blackboard

    Speed continued - Mrs. Blackmon's Science Blackboard

    A car travels for a half hour at a speed of 40 km/h. How far does the car travel? ... You look at the speedometer and see that the car's speed hardly changes. If you are traveling at a ....
  • Electrostatic - cpb-us-e1.wpmucdn.com

    Electrostatic - cpb-us-e1.wpmucdn.com

    Three things coulomb's law relate. Charge on object. Distance between them. Force applied between them. ... Force is directly proportional to the size of each charge. When one increase, the other will increase by the same amount. Double 1 charge,...
  • Quiz - De La Salle Institute

    Quiz - De La Salle Institute

    ***Remember*** Thermal energy can be measured Heat is what we feel. Quick Tip.. Radiation Heat transfer from a light source. Conduction Heat transfer through a solid . Convection Heat transfer through a liquid or gas. Quiz Name the first four...
  • 4.10 - Notes Oxidation and Reduction

    4.10 - Notes Oxidation and Reduction

    LEO the lion goes GER. Losing Electrons Oxidation, Gaining Electrons Reduction. Oxidation-reduction reactions. Abbreviated to redox reactions. Occur together in reactions. Can't have one without the other. Oxidizing agent. Species involved in removing electrons.
  • The Texas Public Information Act: An Overview

    The Texas Public Information Act: An Overview

    TDRPC Rule 5.03. A lawyer who has direct supervision over a non-lawyer must take reasonable steps to ensure the employee's conduct is compatible with the professional obligations of the lawyer. A lawyer can be subject to discipline if an employee...
  • WATER A guide for GCSE students KNOCKHARDY PUBLISHING

    WATER A guide for GCSE students KNOCKHARDY PUBLISHING

    WATER A guide for GCSE students 2010 SPECIFICATIONS KNOCKHARDY PUBLISHING WATER PURIFICATION Water of the correct quality is essential for life. For humans, drinking water should have sufficiently low levels of dissolved salts and microbes.
  • Forestry Science I

    Forestry Science I

    Main Parts of a Tree Trunk Parts Heartwood: Dead wood located in the center of the stem. Provides strength to the tree. Phloem: Food conducting tissue, brings the food from the leaves to the other parts of the tree Xylem:...