Scribd
Upload
199 views

DP4 Practice Activities - Answers

This document provides examples of SQL queries using various Oracle SQL functions for manipulating strings, numbers, and dates. It includes examples using functions like CONCAT, SUBSTR, LENGTH, INSTR, REPLACE, TRIM, TRUNC, ROUND, MOD, SYSDATE, ADD_MONTHS, LAST_DAY, NEXT_DAY, MONTHS_BETWEEN on sample data from tables like employees, orders, and events. The document also provides practice questions for learners to try out these functions.

Uploaded by

gery sumual
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
199 views

DP4 Practice Activities - Answers

This document provides examples of SQL queries using various Oracle SQL functions for manipulating strings, numbers, and dates. It includes examples using functions like CONCAT, SUBSTR, LENGTH, INSTR, REPLACE, TRIM, TRUNC, ROUND, MOD, SYSDATE, ADD_MONTHS, LAST_DAY, NEXT_DAY, MONTHS_BETWEEN on sample data from tables like employees, orders, and events. The document also provides practice questions for learners to try out these functions.

Uploaded by

gery sumual
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 4

Practice Activity for DP 4.1 – 4.

3
Gery J. Sumual (01123574291-53)

4.1 Case and Character Manipulation


Vocabulary: DUAL; format; INITCAP; character functions; TRIM; substitution variable; single-row
functions; UPPER; input; CONCAT; output; LOWER; LPAD; SUBSTR; REPLACE; INSTR; LENGTH;
RPAD
Try It / Solve It:
1. SELECT CONCAT(‘Oracle’, ‘Internet’, ‘Academy’) “The Best Class”
FROM DUAL;
2. SELECT SUBSTR(‘Oracle Internet Academy’, 13, 15) “The Net”
FROM DUAL;
3. SELECT LENGTH(‘Oracle Internet Academy’)
FROM DUAL;
4. SELECT INSTR((‘Oracle Internet Academy’, ‘I’)
FROM DUAL;
5. SELECT REPLACE(RPAD(LPAD('Oracle Internet Academy',27,'*'),31,'*'),' ', '****')
FROM DUAL;
6. SELECT TRIM(BOTH '$' FROM REPLACE(RPAD(LPAD('Oracle Internet Academy',27,'$'),31,'$'),'
', '$$$$'))
FROM DUAL;
7. SELECT REPLACE('Oracle Internet Academy','Internet', '2013-2014')
FROM DUAL;
8. SELECT order_date, LPAD(order_total, LENGTH(order_total)+1, '$') AS total
FROM f_orders;
9. SELECT UPPER(first_name)||' '||UPPER(last_name)||' '||UPPER(address)||'
'||UPPER(CITY)||', '||UPPER(state)||' '||zip "ADDRESS"
FROM f_customers;
10. SELECT CONCAT(SUBSTR(first_name,1,1),last_name) "Name", salary, department_id
FROM employees
WHERE department_id = :dept_id;
11. SELECT department_id, department_name, location_id
FROM departments
WHERE department_name = :the_department_of_your_choice;
12. SELECT*
FROM employees
WHERE UPPER(hire_date) LIKE '%'||UPPER(:the_month)||'%';
Or
SELECT*
FROM employees
WHERE UPPER(SUBSTR(hire_date,4,3)) = UPPER(:the_month);
4.2 Number Functions
Vocabulary:TRUNC, number functions, MOD, ROUND
Try It / Solve It:
1. SELECT last_name, salary, ROUND(salary/1.55, 2)
FROM employees
WHERE employee_id BETWEEN 100 AND 102
2. SELECT last_name, salary, TRUNC(salary+(salary*5.333/100), 2) "Raise"
FROM employees
WHERE department_id = 80
3. SELECT MOD(38872,2)
FROM DUAL;
4. SELECT ROUND(845.553, 1), ROUND(30695.348, 2), ROUND(30695.348, -2), TRUNC(2.3454,
1)
FROM DUAL;
5. SELECT last_name, salary
FROM employees
WHERE MOD(salary,30)=0;
6. SELECT MOD(34,8) "EXAMPLE"
FROM DUAL;
7. It’s better for my paycheck to not be rounded or truncated, because it will be either
exploitation or loss profit for the business. But if I have to choose then I’d go with rounded
because it can go up or down unlike truncated which always go down.
SELECT (565.784 - ROUND(565.784,2))*:x AS "Difference"
FROM DUAL;
4.3 Date Functions
Vocabulary: SYSDATE, ADD_MONTHS, LAST_DAY, NEXT_DAY, MONTHS_BETWEEN
Try It / Solve It:
1. SELECT ROUND(MONTHS_BETWEEN(SYSDATE, event_date)) “number of months”
FROM d_events
WHERE name = 'Vigil wedding';
2. SELECT ROUND(MONTHS_BETWEEN(TO_DATE('24-Aug-2021'),TO_DATE('01-Jun-
2021'))*30.5) "Days.", TO_DATE('24-Aug-2021')-TO_DATE('01-Jun-2021') AS "Actual days"
FROM DUAL;
3. SELECT TO_DATE('01/Jan/2021') - TO_DATE('31/Dec/2020')
FROM DUAL;
4. SELECT SYSDATE, TRUNC(ROUND(SYSDATE, 'Month'), 'Year') AS RMTY,
TRUNC(ROUND(SYSDATE, 'Year'), 'Month') AS RYTM
FROM DUAL;
5. SELECT LAST_DAY(TO_DATE('01-Jun-2005')) "The last day of June 2005"
FROM DUAL;
6. SELECT birthdate, ROUND(MONTHS_BETWEEN(SYSDATE, birthdate)/12) AS age
FROM f_staffs
WHERE first_name = 'Bob' AND last_name = 'Miller';
7. SELECT ADD_MONTHS(SYSDATE, 6) AS appointment
FROM DUAL;
8. SELECT LAST_DAY(SYSDATE) AS deadline
FROM DUAL;
9. SELECT ROUND(MONTHS_BETWEEN(TO_DATE('01-Jan-2022'), TO_DATE('09-Nov-2021')),1)
FROM DUAL;
10. SELECT NEXT_DAY(TO_DATE('09-Nov-2021'), 'Friday') "First Friday."
FROM DUAL;
11. MONTHS_BETWEEN
12. ADD_MONTHS, NEXT_DAY, LAST_DAY, ROUND, TRUNC
13. In order to properly track and pin down product warranty or business contract, also to shift
due dates or schedules because of holiday, weekend, and etc.

Extension exercises

1. SELECT ROUND(86.678, 2)
FROM DUAL;
2. SELECT UPPER(title) "DJs on Demand Collections"
FROM d_cds
WHERE cd_number in (90,91);
3. SELECT CONCAT(LOWER(last_name), UPPER(SUBSTR(first_name,1,1))) "User Passwords"
FROM d_partners;
4. SELECT CONCAT(LOWER(last_name), UPPER(SUBSTR(first_name,1,1))) "User Passwords"
FROM d_partners;
5. SELECT UPPER(REPLACE('It''s a small world', 'It''s a small', 'HELLO'))
FROM DUAL;
6. SELECT REPLACE('Mississippi', 'i', '$')
FROM DUAL;
7. SELECT ROUND(5332.342, -2)
FROM DUAL;
8. SELECT ROUND(3.14159, 2)
FROM DUAL;
9. SELECT TRUNC(73.892, 1)
FROM DUAL;
10. SELECT NEXT_DAY(ADD_MONTHS(SYSDATE, 6), 'Friday')
FROM DUAL;
11. SELECT SYSDATE, ADD_MONTHS(SYSDATE, 10*12) "Future."
FROM DUAL;
12. SELECT TO_DATE('29-Feb-2004') "Past.", LAST_DAY(ADD_MONTHS(TO_DATE('29-Feb-
2004'),4*12)) "Future."
FROM DUAL;
13. SELECT description
FROM d_themes
WHERE description LIKE '%ie%';
14. SELECT*
FROM d_cds
WHERE year > 2000 and year < 2003;
15. SELECT employee_id, hire_date
FROM employees
WHERE hire_date BETWEEN TO_DATE('01-Jan-1997') AND SYSDATE
ORDER BY hire_date DESC;

You might also like