chapter 4

Question 1

what does the DUAL table contain

Answer 1

Has one row called “X” and one column called “DUMMY”

Question 2

how many rows will queries using the dual table return

Answer 2

return 1 row

Question 3

what is the dual table used for ?

Answer 3

used to create SELECT statements and execute functions not directly related to a specific database table

Question 4

what are the 2 categories that single row functions are divided into ?

Answer 4

–> functions that convert the case of character strings

–> functions that can join, extract, show, find, pad, and trim character strings

Question 5

name 3 clauses where single row functions can be used

Answer 5

select, where, order by

Question 6

what does case manipulation allow you to do ?

Answer 6

You may not always know in which case (upper, lower, mixed) the data is stored in the database. Case manipulation allow you to temporarily cover the database data to a case of your choosing

Question 7

advantage of performing case manipulation

Answer 7

mismatches between database case storage and query case requests are avoided as you can format the output and search for specific strings

Question 8

name 7 character manipulation functions

Answer 8

concat
substr
length
instr
LPAD/RPAD
Trim
replace

Question 9

true or false

case manipulation functions can be used in most parts of the queries

Answer 9

true

Question 10

which function converts alpha characters to lower-case

Answer 10

LOWER(column)

Question 11

which function converts alpha characters to upper-case

Answer 11

UPPER(column)

Question 12

which function converts alpha characters values to uppercase for the first letter of each word

Answer 12

INITCAP(column)

Question 13

what are character manipulation functions used for

Answer 13

they are used to extract, change, format, or alter in some way a character string

Question 14

what does the character manipulation function take in as input and give out as output

Answer 14

inputs: one or more characters or words
output: changed, extracted, counted or altered value

Question 15

how many values does the concat function join

Answer 15

2

Question 16

what is the output for
SELECT CONCAT(‘HELLO’, ‘WORLD’)
FROM DUAL;

Answer 16

HELLOWORLD (there is no space similar to the double pipe operator used for concatenation)

Question 17

what does the SUBSTR function return

Answer 17

Extracts a string of a determined length

for example
SELECT SUBSTR(‘HelloWorld’, 1, 5)
FROM DUAL

will return a sub string that starts from 1st character and is 5 characters in length

Question 18

which argument is optional in sql substr function

Answer 18

length argument. If omitted, returns all the characters to the end of the string

Question 19

what does the length function in sql do

Answer 19

shows the length of a string as a number value

Question 20

what does the length function take in as argument

Answer 20

takes a character string as an argument and returns the number of characters in the string

Question 21

what does the INSTR function do

Answer 21

searches for the first occurrence of a substring within a character string and returns the position as a number

Question 22

what happens if no substring is found

Answer 22

0 is returned

Question 23

what does LPAD do

Answer 23

pads the left side of a character string, resulting in a right justified value

Question 24

how many arguments does LPAD require

Answer 24

takes in a character string, total number of characters in the padded string, which character to pad with

Question 25

what does rpad do

Answer 25

pads the right hand side of a character string resulting in a left justified value

Question 26

what does the TRIM function do

Answer 26

Removes alll specified characters from either the beginning, the end, or both beginning and end of a string

Question 27

what is the syntax for trim function to remove from beginning

Answer 27

SELECT TRIM(LEADING 'A' FROM 'ABC')

Question 28

what is the syntax for trim function to remove from ending

Answer 28

SELECT TRIM(TRAILING 'A' FROM 'BCA')

Question 29

what is the syntax for trim function to remove from both ending and beginning

Answer 29

SELECT TRIM(BOTH 'A' FROM 'ABCA')

Question 30

WHAT happens if there is nothing to trim

Answer 30

original character string is returned

Question 31

what does replace do

Answer 31

replaces a sequence of characters in a string with another set of characters

Question 32

what are the 3 arguments in the replace function

Answer 32

original string string/character to replace replacement string (option, if omitted will remove the character from the original string)

Question 33

often a column alias is used to name a _______

Answer 33

function

Question 34

what are substitution variables

Answer 34

substation variables allow you to run the same query with many different values to get different result sets. without them, the same statement would have to be repeatedly edited to change the where clause

Question 35

how to use substitution variables

Answer 35

:named_variable when you run the statement oracle apex will ask you to enter the text

Question 36

what is an argument

Answer 36

can be defined as a column name, expression, or a constant

Question 37

a substitution variable is _______

Answer 37

user-defined

Question 38

what are the 3 number functions

Answer 38

round trunc mod

Question 39

round can be used with both ______ and ______

Answer 39

round can be used with both numbers and dates

Question 40

what is the round function do

Answer 40

mainly used to round numbers to a specified number of decimal places but it can also be used to round numbers to the left of the decimal place

Question 41

what are the arguments for the round function

Answer 41

column | expression, decimal places(optional and if not specified will return 0)

Question 42

what happens when the decimal places argument in the round function is negative

Answer 42

the number is rounded to that number of decimal places to the left of the decimal point

Question 43

the trunc function can be used with whwat ?

Answer 43

numbers and dates

Question 44

what is the trunc function used for

Answer 44

it is used to terminate the column, expression, or value to a specified number of decimal places

Question 45

what does the MOD function do ?

Answer 45

finds the remainder after one value is divided by another value

Question 46

which comparison operator retrieves a list of values

Answer 46

IN

Question 47

answer to the script SELECT TRUNC(ROUND(456.98)) FROM DUAL;

Answer 47

457

Question 48

what is the default display and input format for dates

Answer 48

DD-Mon-YYYY

Question 49

how does oracle DB store dates internally

Answer 49

stores in a numeric format representing the: century year month day hour minute second

Question 50

valid oracle dates are between ____

Answer 50

Jan 1, 4712 B.C. and Dec 31, 9999 A.D.

Question 51

what is sysdate

Answer 51

date function that returns the current database server date and time

Question 52

which function returns values with a nondate data type

Answer 52

months_between

Question 53

what does teh months_between function take

Answer 53

2 date arguments

Question 54

what happens if the first argument is an earlier date than the second in months_between argument

Answer 54

negative number is returned

Question 55

what does add_months take

Answer 55

date and a number

Question 56

what happens if the number supplied is negative for the add_months function

Answer 56

function will subtract that number of months from the date argument

Question 57

what does the next_day function do

Answer 57

takes the date and a weekday and returns the date of the next occurrence of the weekday SELECT NEXT_DAY(SYSDATE, 'SATURDAY') FROM DUAL result: 29-Oct-2022

Question 58

what does last_day function do ?

Answer 58

takes a date argument and returns the date of the last day of the month

Question 59

how does the round function for date work?

Answer 59

returns a date rounded to the unit specified by the second argument SELECT ROUND(HIRE_DATE, 'MONTH') 16-Nov-1999 ==> 01-Dec-1999

Question 60

how does the trunc function for date work?

Answer 60

returns a date truncated to the unit specified by the second argument SELECT TRUNC(HIRE_DATE, 'MONTH') 16-nov-1999 ==> 1-Nov-1999