sql03 - 3 3 Single-Row Functions 3-2 Objectives After...

Info iconThis preview shows pages 1–14. Sign up to view the full content.

View Full Document Right Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: 3 3 Single-Row Functions 3-2 Objectives After completing this lesson, you should After completing this lesson, you should be able to do the following: be able to do the following: • Describe various types of functions available in SQL • Use character, number, and date functions in SELECT statements • Describe the use of conversion functions 3-3 SQL Functions Function Function Input Input arg 1 arg 1 arg 2 arg 2 arg n arg n Function Function performs action performs action Output Output Result Result value value 3-4 Two Types of SQL Functions Functions Functions Single-row Single-row functions functions Multiple-row Multiple-row functions functions 3-5 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 datatype • Can be nested function_name ( column | expression , [ arg1, arg2,. .. ]) 3-6 Single-Row Functions Conversion Conversion Character Character Number Number Date Date General General Single-row Single-row functions functions 3-7 Character Functions Character Character functions functions LOWER LOWER UPPER UPPER INITCAP INITCAP CONCAT CONCAT SUBSTR SUBSTR LENGTH LENGTH INSTR INSTR LPAD | RPAD LPAD | RPAD TRIM TRIM REPLACE REPLACE Case conversion Case conversion functions functions Character manipulation Character manipulation functions functions 3-8 Function Result Case Conversion Functions Convert case for character strings Convert case for character strings LOWER( ' SQL Course ' ) UPPER( ' SQL Course ' ) INITCAP( ' SQLCourse ' ) sql course SQL COURSE Sql Course 3-9 Using Case Conversion Functions Display the employee number, name, and Display the employee number, name, and department number for employee Blake. department number for employee Blake. SQL> SELECT empno, ename, deptno 2 FROM emp 3 WHERE ename = 'blake'; no rows selected no rows selected EMPNO ENAME DEPTNO--------- ---------- ---------7698 BLAKE 30 SQL> SELECT empno, ename, deptno 2 FROM emp 3 WHERE LOWER(ename) = 'blake'; 3-10 CONCAT( ' Good ' , ' String ' ) SUBSTR( ' String ' ,1,3) LENGTH( ' String ' ) INSTR( ' String ' , ' r ' ) LPAD(sal,10, ' * ' ) GoodString Str 6 3 ******5000 Function Result Character Manipulation Functions Manipulate character strings Manipulate character strings 3-11 Using the Character Manipulation Functions SQL> SELECT ename, CONCAT (ename, job), LENGTH(ename), 2 INSTR(ename, 'A') 3 FROM emp 4 WHERE SUBSTR(job,1,5) = 'SALES'; ENAME CONCAT(ENAME,JOB) LENGTH(ENAME) INSTR(ENAME,'A')---------- ------------------- ------------- ----------------MARTIN MARTINSALESMAN 6 2 ALLEN ALLENSALESMAN 5 1 TURNER TURNERSALESMAN 6 WARD WARDSALESMAN 4 2 3-12 Number Functions • ROUND: Rounds value to specified decimal ROUND(45.926, 2) ROUND(45.926, 2) 45.93 45.93 • TRUNC: Truncates value to specified decimal TRUNC(45.926, 2) TRUNC(45.926, 2) 45.92 45.92 • MOD: Returns remainder of division MOD(1600, 300) MOD(1600, 300) 100 100 3-13 Using the ROUND Function...
View Full Document

This note was uploaded on 07/15/2011 for the course ECO 2023 taught by Professor Mr.raza during the Summer '10 term at FAU.

Page1 / 51

sql03 - 3 3 Single-Row Functions 3-2 Objectives After...

This preview shows document pages 1 - 14. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online