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:
