Chapter 3 Selected SingleRow Functions and Advanced DML
- Slides: 47
Chapter 3 Selected Single-Row Functions and Advanced DML & DDL
Chapter Objectives • Use the UPPER, LOWER, and INITCAP functions to change the case of field values and character strings • Extract a substring using the SUBSTR function • Determine the length of a character string using the LENGTH function
Chapter Objectives • Use the LPAD and RPAD functions to pad a string to a desired width • Use the LTRIM and RTRIM functions to remove specific characters strings • Round and truncate numeric data using the ROUND and TRUNC functions • Calculate the number of months between two dates using the MONTHS_BETWEEN function
Chapter Objectives • Identify and correct problems associated with calculations involving null values using the NVL function • Display dates and numbers in a specific format with the TO_CHAR function • Determine the current date setting using the SYSDATE keyword • Nest functions inside other functions • Identify when to use the DUAL table
Terminology • Function – predefined block of code that accepts arguments • Single-row Function – returns one row of results for each record processed • Multiple-row Function – returns one result per group of data processed
Types of Functions
Case Conversion Functions Alter the case of data stored in a column or character string
LOWER Function Used to convert characters to lower-case letters
UPPER Function Used to convert characters to upper-case letters
INITCAP Function Used to convert characters to mixed-case
Character Manipulation Functions Manipulates data by extracting substrings, counting number of characters, replacing strings, etc.
SUBSTR Function Used to return a substring, or portion of a string
LENGTH Function Used to determine the number of characters in a string
LPAD and RPAD Functions Used to pad, or fill in, a character string to a fixed width
LTRIM and RTRIM Functions Used to remove a specific string of characters
REPLACE Function Substitutes a string with another specified string
CONCAT Function Used to concatenate two character strings
Number Functions Allows for manipulation of numeric data
ROUND Function Used to round numeric columns to a stated precision
TRUNC Function Used to truncate a numeric value to a specific position
Date Functions Used to perform date calculations or format date values
MONTHS_BETWEEN Function Determines the number of months between two dates
ADD_MONTHS Function Adds a specified number of months to a date
NEXT_DAY Function Determines the next occurrence of a specified day of the week after a given date
TO_DATE Function Converts various date formats to the internal format (DD-MON-YYYY) used by Oracle 9 i
Format Model Elements - Dates
NVL Function Substitutes a value for a NULL value
NVL 2 Function Allows different actions based on whether a value is NULL
TO_CHAR Function Converts dates and numbers to a formatted character string
Format Model Elements – Time and Number
Other Functions • • • NVL 2 TO_CHAR DECODE SOUNDEX
DECODE Function Determines action based upon values in a list
SOUNDEX Function References phonetic representation of words
Nesting Functions • One function is used as an argument inside another function • Must include all arguments for each function • Inner function is resolved first, then outer function
Summary of functions Single-Row Functions • Text Functions lpad, rpad, lower, upper, initcap, length, substr, instr, trim, concat • Arithmetic Functions abs, round, ceil, floor, mod, sign, sqrt, trunc, vsize • List Functions greatest, least, decode • Date Functions add_months, last_day, months_between, new_time, next_day, round, trunc • Conversion Functions to_char, to_number, to_date
DUAL Table • Dummy table • Consists of one column and one row • Can be used for table reference in the FROM clause Ex: select sysdate from dual
Advanced Data Selection in Oracle Using a Subquery SELECT ename, job, sal FROM emp WHERE sal = (SELECT MIN(sal) FROM emp); Multiple-Row Subqueries SELECT empno, ename, job, sal FROM emp WHERE sal < ANY (SELECT sa FROM emp WHERE job = 'SALESMAN') AND job <> 'SALESMAN'; multiple-row comparison operators – IN, ANY, ALL
Manipulating Oracle Data Inserting Rows with Null Values and Special Values INSERT INTO emp (empno, ename, hiredate, job, sal, comm, mgr, deptno) VALUES (113, 'Louis', SYSDATE, 'MANAGER', 6900, NULL, 30); Copying Rows from Another Table INSERT INTO sales_reps(id, name, salary, com) SELECT empno, ename, sal, comm FROM emp WHERE job LIKE '%SALES%';
Creating and Managing Database Objects (Tables) Creating a Table by Using a Subquery CREATE TABLE dept 30 AS SELECT empno, ename, sal*12 ANNSAL, hiredate FROM emp WHERE deptno = 30;
Creating and Managing Database Objects (Tables) Common Datatypes Datatype Description VARCHAR 2(siz Variable-length character data, can up to e) 4, 000 bytes. CHAR(size) Fixed-length character data, can up to 2, 000 bytes. NUMBER(p. s) Variable-length numeric data, can up to 38 digits. E. g. Number(5. 2) 999. 99 DATE Date and time values, 7 bytes. Other data types included: LONG, CLOB, RAW, LONG RAW, BLOB, BFILE, ROWID … etc.
Creating and Managing Database Objects (Tables) The Drop Table Statement DROP TABLE table; Dropping a Table DROP TABLE dept 30;
Other Database Objects (Views, Sequences) Views CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view [(alias[, alias]. . . )] AS subquery [WITH CHECK OPTION [CONSTRAINT constraint]] [WITH READ ONLY [CONSTRAINT constraint]]; DROP VIEW view; q. To restrict data access q. To make complex queries easy q. To provide data independence q. To present different views of the same data q. You can retrieve data from a view as from a table.
Other Database Objects (Views, Sequences) Creating a View CREATE VIEW empv 30 AS SELECT empno, ename, sal FROM emp WHERE deptno = 30; Modifying a View CREATE OR REPLACE VIEW empv 30 (id_no, name, salary) AS SELECT empno, ename, sal FROM emp WHERE deptno = 30; Drop a View DROP VIEW empv 30
Other Database Objects (Views, Sequences) Sequences CREATE SEQUENCE sequence [INCREMENT BY n] [START WITH n] [{MAXVALUE n | NOMAXVALUE}] [{MINVALUE n | NOMINVALUE}] [{CYCLE | NOCYCLE}] [{CACHE n | NOCACHE}]; q. Automatically generates unique numbers q. Is typically used to create a primary key q. Replaces application code
Other Database Objects (Views, Sequences) Creating a Sequence CREATE SEQUENCE deptid_seq INCREMENT BY 10 START WITH 5 MAXVALUE 9999; NEXTVAL and CURRVAL Pseudocolumns q. NEXTVAL returns the next available sequence value. It returns a unique value every time it is referenced, even for different users q. CURRVAL obtains the current sequence value. NEXTVAL must be issued for that sequence before CURRVAL contains a value
Other Database Objects (Views, Sequences) Using a Sequence INSERT INTO dept(deptno, dname, loc) VALUES (deptid_seq. NEXTVAL, 'Support', ' HONG KONG' ); View the current value SELECT deptid_seq. CURRVAL FROM dual; Removing a Sequence DROP SEQUENCE deptid_seq;
Appendix B: Useful link • Try these with SQL http: //www. cse. cuhk. edu. hk/~csc 3170/tutorial/index. html • http: //db 00. cse. cuhk. edu. hk • http: //www. db. cs. ucdavis. edu/teaching/sqltutorial • http: //www. w 3 schools. com/sql
- Ddl and dml
- Ddl y dml ejemplos
- Sql dcl
- Practicing ddl commands
- Dml commands
- Ddl naredbe
- Data manipulation language dml
- Dml basis data
- Dml passo fundo
- Contoh dml
- V-av
- Data manipulation language in sql
- The conceptual data model is the set of concepts that
- Bahasa manipulasi data
- Pl/sql ddl
- What is quantitative example
- Quantitative vs qualitative
- Word basic
- The bsr and the io modes of 8255 is selected by the
- Advanced function
- Advanced automation functions
- Advanced excel functions for finance
- Survivorship curve for k selected species
- Selected response assessment
- Vba combobox selected item
- K selected species survivorship curve
- Example of r selected species
- Weights of the backpacks of first graders on a school bus
- Fort bragg population pyramid
- Not only the students but also their instructor
- Delete it
- Selected response tasks
- Congratulations you have been selected
- Three randomly selected households are surveyed 2 6 7
- K selection r selection
- R-selected species
- Ionic bonding worksheet answers
- Whats conditional relative frequency
- Gwen harwood: selected poems
- Dreamweaver php extensions
- I can evaluate functions
- Evaluating functions and operations on functions
- Chapter 6 advanced shielded metal arc welding
- Advanced evolution chapter 4
- Advanced evolution chapter 4
- Advanced evolution chapter 8
- Advanced accounting chapter 1
- Absolute value of x as a piecewise function