Lesson 1 Case Character Manipulation Answer Guide 296p1p

Oracle Academy Database Programming with SQL Instructor Resource Guide PRACTICE SOLUTIONS SECTION 1 LESSON 1 – Case and Character Manipulation Vocabulary Directions: Identify the vocabulary word for each definition below. DUAL

Dummy table used to view results from functions and calculations

Format

The arrangement of data for storage or display.

INITCAP

Converts alpha character values to uppercase for the first letter of each word, all other letters in lowercase.

Character functions

Functions that accept character data as input and can return both character and numeric values.

TRIM

Removes all specified characters from either the beginning or the ending of a string.

Expression

A symbol that represents a quantity or a relationship between quantities

Single-row functions

Functions that operate on single rows only and return one result per row

UPPER

Converts alpha characters to upper case

Input

Raw data entered into the computer

CONCAT

Concatenates the first character value to the second character value; equivalent to concatenation operator (||).

Output

Data that is processed into information

LOWER

Converts alpha character values to lowercase.

LPAD Oracle Academy

Pads the left side of a character, resulting in a rightjustified value 9 Database Programming with SQL Copyright © 2010, Oracle. All rights reserved.

Oracle Academy Database Programming with SQL Instructor Resource Guide SUBSTR

Returns specific characters from character value starting at a specific character position and going specified character positions long

REPLACE

Replaces a sequence of characters in a string with another set of characters.

INSTR

Returns the numeric position of a named string.

LENGTH

Returns the number of characters in the expression

RPAD

Pads the right-hand side of a character, resulting in a left- justified value.

Try It / Solve It 1. Using the three separate words “Oracle,” “Internet,” and “Academy,” use one command to produce the following output: The Best Class Oracle Internet Academy Solution: SELECT CONCAT('Oracle ', 'Internet')||' Academy' AS "The Best Class" FROM DUAL; 2. Use the string “Oracle Internet Academy” to produce the following output: The Net net Solution: SELECT SUBSTR('Oracle Internet Academy', 13,3)AS "The Net" FROM DUAL; 3. What is the length of the string “Oracle Internet Academy”? Solution: 23 SELECT LENGTH('Oracle Internet Academy')AS "Length" FROM DUAL;

Oracle Academy

10 Database Programming with SQL Copyright © 2010, Oracle. All rights reserved.

Oracle Academy Database Programming with SQL Instructor Resource Guide 4. What’s the position of “I” in “Oracle Internet Academy”? Solution: 8 SELECT INSTR('Oracle Internet Academy', 'I')AS "Position" FROM DUAL; 5. Starting with the string “Oracle Internet Academy”, pad the string to create ****Oracle****Internet****Academy**** Solution: SELECT LPAD('Oracle',10,'*')||LPAD('Internet',12,'*')||RPAD(LPAD('Academy',11,'*'),15,'*') AS "OIA" FROM DUAL; 6. Starting with the string “Oracle Internet Academy”, pad the string to produce: Oracle$$$Internet$$$Academy Solution: SELECT RPAD('Oracle',9,'$')||RPAD('Internet',11,'$')||'Academy' AS "OIA" FROM DUAL;

7. Using the string ‘Oracle Internet Academy’, produce the output shown using the REPLACE function. The Best Class Oracle 2004-2005 Academy Solution: SELECT REPLACE('Oracle Internet Academy', 'Internet','2004-2005') AS "The Best Class" FROM DUAL; 8. List the order date and the order total from the Global Fast Foods F_ORDERS table. Name the order total as TOTAL, and fill in the empty spaces to the left of the order total with $. Solution: SELECT order_date, LPAD(ORDER_TOTAL,10,'$')AS "TOTAL" FROM f_orders; Oracle Academy

11 Database Programming with SQL Copyright © 2010, Oracle. All rights reserved.

Oracle Academy Database Programming with SQL Instructor Resource Guide 9. Write a query that will output a column called “ADDRESS” which has the following information: ZOE TWEE 1009 OLIVER AVENUE BOSTON, MA 12889. Use the Global Fast Foods F_CUSTOMERS table. Solution: SELECT UPPER(first_name) ||' '|| UPPER(last_name) ||' '|| UPPER(address)||' ' ||UPPER(city) ||', '||UPPER(state)||' '||zip AS "ADDRESS" FROM f_customers WHERE id = 456; 10. Write a query to return the first character of the first name concatenated to the last_name, the salary and the department id for employees working in department 20. Give the first expression an alias of Name. Use the EMPLOYEES table. Change the query to use a substitution variable instead of the hard coded value 20 for department id. Run the query for department 30 and 50 without changing the original whereclause in your statement. Solution: SELECT SUBSTR(first_name, 1,1) || last_name AS "Name", salary, department_id FROM employees WHERE department_id = 20; SELECT SUBSTR(first_name, 1,1) || last_name AS "Name", salary, department_id FROM employees WHERE department_id = :dept_id; 11. Using a substitution variable for the department name, write a query listing department id, department name and location id for departments located in the_department_of_your_choice. Use the DEPARTMENTS table. Note: All substitution variables in OAE are treated as character strings, so no quotes (‘ ‘) are needed. Solution: SELECT department_id, department_name, location_id FROM departments WHERE department_name = :dept_name;

Oracle Academy

12 Database Programming with SQL Copyright © 2010, Oracle. All rights reserved.

Oracle Academy Database Programming with SQL Instructor Resource Guide 12. Write a query that returns all the employee data depending on the month of their hire date. Use the EMPLOYEES table. The statement should return the month part of the hiredate which is then compared to an abbreviated month (JAN, FEB, MAR) ed into the query via a substitution variable. Solution: SELECT * FROM employees WHERE SUBSTR(hire_date, 4, 3) = :entered_month;

Oracle Academy

13 Database Programming with SQL Copyright © 2010, Oracle. All rights reserved.