Oracle Database 19c Exam Part 2

There are 2 types of wildcards ___ and ____.

% and _

What does an alternative quote operator allow you to do?

- Specify your own quotation mark delimiter - select any delimiter - increase readability and usability

Substitution vars are used to supplement

- WHERE condition - ORDER BY clauses - Table names - Entire SELECT statements

What are two types of sql functions?

- single row functions - multiple row functions

What are the 5 diff types of single-row functions?

1. Character 2. Number 3. Date 4. Conversion 5. General

A ____ Immediately follows the column name (there can also be the optional AS keyword between the column name and the alias. you should use AS to make it clearer to people reading your code)

A column alias

What is useful in calculations

A column alias

____ requires double quotation marks if it contains spaces or special chars. In oracle it requires double " if it is case sensitive for instance if you want it to start with a NUMBER you have to use quotes around it...

A column alias

___ is a character, number or date that is included in select statement

A literal

Returns TRUE if BOTH component conditions are true

AND

What are the logical operators

AND, OR, NOT

What is the default order by setting?

ASC

what does the following return? select LPAD ('Hello World', 15, '*') FROM dual;

Adds 4 * to the left of the string to make the string 15 chars total: LPAD(string, length, pad char) ****Hello World

what does the following return? select RPAD ('Hello World', 15, '*') FROM dual;

Adds 4 * to the right of the string to make the string 15 chars total: Hello World****

What's the difference between alternative quote operator and escape sequence?

Alternative Quote Op is for Oracle and is q' [ ]' Escape Sequence is for MYSQL and is \ '

when you want to use an apostrophe in your literal string you can use a ____ in ORACLE.

Alternative Quote Operator

The ____ function only allows for 2 parameters in Oracle Database MySQL will actually accept more than 2 parameters

CONCAT()

Is represented by ||

Concatenation Operator

What is the default format of date in Oracle databases?

DD-MON-RR RR- Rounded year either round up or round down for the century

Anytime you want to look at the structure of a table you use the ___ command

DESCRIBE or DESC

What does the following return: Select TRIM ('d' from 'Dolly world') from dual;

Dolly worl - it's case sensitive, otherwise if Uppercase it would remove both d's

When using an escape char you do need to declare what that char is by using the ____ statement

ESCAPE ie. WHERE job_id LIKE 'SA/_%' ESCAPE '/'

What does INSTR do?

It allows you to return the particular location of a particular char occurrence. for instance: SELECT INSTR('Hello World', 'l', 1, 3 from dual; Returns: 10 1 = Starting from this char position 3 = you what this # occurrence of your search string.

You can use the ____ Operator to perform wildcard searches of valid string patterns. ____ Is used for wildcard searches. Used in conjunction with WHERE statements

LIKE

<

Less than

<=

Less than or equal to

Date and character ____ values must be enclosed within single quotation marks

Literal

You can use the ___ ____ to filter the result set based on more than one condition or invert the result set

Logical Operators

You can use the ___ ___ cllause to limit the rows that are returned by query. Can use to implement Top-N reporting

Row Limiting clause

the wild card _ denotes ___.

denotes only one char

the wild card % denotes ___.

denotes zero or more chars

For Where Clause, when looking for character data and date data the values are ____.

enclosed within single quotation marks

multiple row functions return one result per row t/f

f - single row function

multiple row functions accept args and return one value t/f

f - single row functions

multiple row functions can be nested at any level t./f

f - single row functions

date values are ____ sensitive

format

You only use && in what part of a SQL statement

in the Select statement, everywhere else in your query can use 1 &

what does the SUBSTR function do?

it returns part of a string. you can choose the character # you wish to start at and the number of characters you want to return.

For ___ character string, it is output once for each row returned.

literal

The item in single quotes is an example of a ___. Concat(last_name, ' is a ', jobid)

literal

Using the LIKE operator the search conditions can contain either ____ or _____

literal chars or numbers

What does the following return: Select TRIM (leading 'd' from 'dolly world') from dual;

olly world

Select lastname||' '||firstname =

one column with both first and last name combined WITH space

Select lastname||firstname =

one column with both first and last name combined with NO space

what does the LOWER function do?

puts all letters in lowercase

What does the UPPER function do?

puts all letters in uppercase

what is the syntax for an alternative quote operator

q' [insert string]'

What does the LENGTH functions do?

returns the # of chars in a string.

if your substituion variable can accept characters/date values make sure to add ____ around it

single quotes where job_id = '&job_title'

which function don't take any input arguments?

sysdate

single row functions accept args that can be a column or an expression: functions_name[(arg1, arg1,....)]

t

single row functions act on each row that is returned t/f

t

single row functions can manipulate data items t/f

t

single row functions might modify the data type t/f

t

Use substitution vars to

temporarily store values with single ampersand & and double ampersand && substitution

Creates a resultant column that is a character expression. When combined with a number, it justfies to the LEFT like a character based column

|| OR CONCAT()

Links to columns or character strings to other columns

|| OR CONCAT()

What renames a column heading?

A Column Alias

Define a Null Value

1. Unavailable 2. Unassigned 3. Unknown 4. Inapplicable

A since function takes an ____ input > performs an ____ > and outputs a _____. functionswill always output a ____.

1. argument 2. action 3 and 4. result value

what are the 7 character-manipulation functions

1. concat 2. substr 3. length 4. instr 5. lpad | rpad 6. trim 7. replace

Write October 17, 2011 in Oracle date default format

17-Oct-11

Write October 17, 2011 in MySQL date default format

2011-10-17

What are the comparison Operators

= > < <= >= <> BETWEEN ____ AND ____ IN(set) LIKE IS NULL

Since && stores whatever value you input for the rest of the session. How do you unstore that value?

Enter UNDERFINE COLUMN in the very first line before select statement

=

Equal to

If you have an _ in your table data and you want to treat a wildcard as a LITERAL character AND include that in the returned data, you need to use a ____ character

Escape

In oracle, using substitution vars do not verify the new result you added.

FALSE It automatically shows you the before SQL statement and the NEW query that uses the substituted value entered.

How to use column positions in order by?

For order by you can also use column positions to indicate a column you want to order by based off the order you have columns in the select statement. seelct last_name, depart_id, manager_id, salary from employees order by 2 asc, 3 desc; 2 = depart_id 3 = manager_id

>

Greater than

>=

Greater than or equal to

LIKE

Match a char pattern

IN(set)

Match any of a list of values. Easier than using an OR statement.

Returns TRUE if the condition is false. Used to negate a condition.

NOT

Arithmetic Expressions or calculations with a value containing a NULL value equate to ____.

NULL

<>

Not equal to

Null is not the same as ____ or a ____.

Null is not the same as ZERO or a BLANK SPACE.

Returns TRUE if either component condition is true

OR

LImiting clause goes after the ____ ___ statement

ORDER BY

what does the INITCAP function do?

Put the first letter in each word in uppercase.

___ allows you to see the rows in the order they are returned. Where ____ < 6 = returns 5 rows in the order they appear in

ROWNUM

what does the following return? Select substr ('Hello World', 7, 5) from dual;

Returns "World"

If you want to return your SQL code that you executed AND your results you include ___.

SET ECHO ON in the very first line before SELECT statement

How do you turn off the substituion variable verification?

SET VERIFY OFF in the very first line before SELECT statement

IS NULL

Searches for Is NULL values

BETWEEN ____ AND ____

Searching a range. Between two values ( This is "inclusive," meaning values returned are values that match the min value, in between values, and max value)

NOT BETWEEN ____ AND ______

Searching outside of a range. Not inclusive.

How do you get rid of duplicate values?

Select Distinct

Null is listed first in DESC order? T/F

T

What is an example of a multiple row function

average

Character values are ___ sensitive

case

OFFSET

default skips the first insert # of rows

Describe command allows you to abbreviate it? T/F

True - DESC

Write a query that searches for job_id that is has "SA" as first two chars, and udnerscore, and anything after?

WHERE job_id LIKE 'SA/_%' ESCAPE '/'

What operators can you use to limit # of rows in a result?

WHERE rownum , FETCH FIRST/LAST # ROWS (WITH TIES) , LIMIT , OFFSET # ROWS FETCH NEXT # ROWS ONLY , FETCH FIRST/LAST # PERCENT ROWS ONLY

Write a query that prompts the user to enter a dept_id

Where department_id = &dept_id; -You need to NAME the variable name you're try to return. in this case dept_id = department_id in the backend

Write a query to search for last names where the letter N as the 3rd character and anything after it.

Where last_name LIKE '__n%'

What is the default format of date in MySQL dbs?

YYYY-MM-DD

How do you use the concat function with more than 2 parameters?

You need to do NESTING: Select CONCAT(CONCAT(first_name, ' '), last_name) CONCAT(first_name, ' ') is your first argument CONCAT(first argument, last_name) is your second and final arg This equals: FirstName LastName

To allow a quotation mark to be included in a string, use the ____ in MYSQL.

\ ' escape sequence select ' Dept\ 's Manager ID: ' = Dept's Manager ID: