Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

data storage administration > chapter 4 > Flashcards

chapter 4 Flashcards

1
Q

what does the DUAL table contain

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

how many rows will queries using the dual table return

A

return 1 row

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

what is the dual table used for ?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

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

A

–> functions that convert the case of character strings

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

name 3 clauses where single row functions can be used

A

select, where, order by

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

what does case manipulation allow you to do ?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

advantage of performing case manipulation

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

name 7 character manipulation functions

A

concat
substr
length
instr
LPAD/RPAD
Trim
replace

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

true or false

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

A

true

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

which function converts alpha characters to lower-case

A

LOWER(column)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

which function converts alpha characters to upper-case

A

UPPER(column)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

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

A

INITCAP(column)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

what are character manipulation functions used for

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

how many values does the concat function join

A

2

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

what does the SUBSTR function return

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

which argument is optional in sql substr function

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

what does the length function in sql do

A

shows the length of a string as a number value

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

what does the length function take in as argument

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

what does the INSTR function do

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

what happens if no substring is found

A

0 is returned

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

what does LPAD do

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

how many arguments does LPAD require

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

what does rpad do

A

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

26
Q

what does the TRIM function do

A

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

27
Q

what is the syntax for trim function to remove from beginning

A

SELECT TRIM(LEADING ‘A’ FROM ‘ABC’)

28
Q

what is the syntax for trim function to remove from ending

A

SELECT TRIM(TRAILING ‘A’ FROM ‘BCA’)

29
Q

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

A

SELECT TRIM(BOTH ‘A’ FROM ‘ABCA’)

30
Q

WHAT happens if there is nothing to trim

A

original character string is returned

31
Q

what does replace do

A

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

32
Q

what are the 3 arguments in the replace function

A

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

33
Q

often a column alias is used to name a _______

A

function

34
Q

what are substitution variables

A

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

35
Q

how to use substitution variables

A

:named_variable

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

36
Q

what is an argument

A

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

37
Q

a substitution variable is _______

A

user-defined

38
Q

what are the 3 number functions

A

round
trunc
mod

39
Q

round can be used with both ______ and ______

A

round can be used with both numbers and dates

40
Q

what is the round function do

A

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

41
Q

what are the arguments for the round function

A

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

42
Q

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

A

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

43
Q

the trunc function can be used with whwat ?

A

numbers and dates

44
Q

what is the trunc function used for

A

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

45
Q

what does the MOD function do ?

A

finds the remainder after one value is divided by another value

46
Q

which comparison operator retrieves a list of values

A

IN

47
Q

answer to the script

SELECT TRUNC(ROUND(456.98)) FROM DUAL;

A

457

48
Q

what is the default display and input format for dates

A

DD-Mon-YYYY

49
Q

how does oracle DB store dates internally

A

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

50
Q

valid oracle dates are between ____

A

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

51
Q

what is sysdate

A

date function that returns the current database server date and time

52
Q

which function returns values with a nondate data type

A

months_between

53
Q

what does teh months_between function take

A

2 date arguments

54
Q

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

A

negative number is returned

55
Q

what does add_months take

A

date and a number

56
Q

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

A

function will subtract that number of months from the date argument

57
Q

what does the next_day function do

A

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

58
Q

what does last_day function do ?

A

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

59
Q

how does the round function for date work?

A

returns a date rounded to the unit specified by the second argument

SELECT ROUND(HIRE_DATE, ‘MONTH’)
16-Nov-1999 ==> 01-Dec-1999

60
Q

how does the trunc function for date work?

A

returns a date truncated to the unit specified by the second argument

SELECT TRUNC(HIRE_DATE, ‘MONTH’)
16-nov-1999 ==> 1-Nov-1999

data storage administration (7 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions