260 - Exam 2 COMPLETE
The AVG Function
calculates the average of numeric values in a specified column
The NEXT_DAY function
can determine the next occurrence of a specific day of the week after a given date.
The NULLIF function
compare two values for equality
A format argument
consists of a series of elements representing exactly what the data should look like and must be entered in single quotation marks
INITCAP function
convert character strings to mixed case, with each word beginning with a capital letter
The TO_CHAR function
convert dates and numbers to a formatted character string.
The TO_NUMBER function
converts a value to a numeric datatype, if possible.
The LAST_DAY function
determines the last day of the month for a given date
column qualifier
indicates the table containing the column being referenced
INTERSECT
lists only records that are returned by both queries
The POWER function
raises the number in first argument to the power indicated as the second argument
LTRIM function
remove a specific string of characters from the left side of data values.
RTRIM function
remove specific characters from the right side of data values.
SUBSTR function
return a substring (a portion of a string)
Group functions, also called multiple-row functions
return one result per group of rows processed
Single-row functions
return one row of results for each record processed
multiple-row functions
return only one result per group or category of rows processed, such as counting the number of books published by each publisher.
The MOD (modulus) function
returns only the remainder of a division operation
The ABS (absolute) function
returns the absolute, or positive, value of the numeric values supplied as the argument.
CURRENT_DATE function
returns the current date and time from the user session.
The SYSDATE function
returns the current date and time set on the operating system where the database resides
The INSTR (instring) function
searches a string for a specified set of characters or a substring, and then returns a numeric value representing the first character position in which the substring is found
The DECODE function
takes a specified value and compares it to values in a list
underscore
the _____ symbol represents exactly one character
CROSS
the ______ keyword, combined with the JOIN keyword, can be used in the FROM clause to explicitly instruct Oracle to create a Cartesian (cross) join.
MINUS
the ________ set operator removes the second query's results from the output if they are also found in the first query's results
character functions
to change the case of characters
MONTHS_BETWEEN function
to determine the number of months between two dates.
logical operators
to search for records based on two or more conditions
TRUNC (truncate) function
to truncate a numeric value to a specific position
The SUM Function
used to calculate the total amount stored in a numeric field for a group of records.
The CONCAT function
used to concatenate data from two columns
The LPAD function
used to pad, or fill in, the area to the left of a character string with a specific character
The TRANSLATE function
used to replace a character in a string with a new value
The ROUND function
used to round numeric fields to the stated precision
RPAD function
uses a symbol to pad the right side of a character string to a specific width.
.Nesting
using one function as an argument inside another function.
arguments
values listed inside parentheses
Self Join
you must join a table to itself
Role
A group, or collection, of privileges.
Privileges
Allow Oracle 12c users to execute certain SQL statements.
System privileges
Allow access to the Oracle 12c database and let users perform DDL operations, such as CREATE, ALTER, and DROP, on database objects (for example, tables and views).
Object privileges
Allow users to perform DML operations, such as INSERT and UPDATE, on the data contained in database objects.
character manipulation functions
At times you might need to determine a string's length, extract portions of a string, or reposition a string by using _________
Equality Join
Creates a join by using a commonly named and defined column
Authorization
Granting object privileges to users based on their identities, which is addressed by issuing GRANT commands for specific privileges.
Condition
Identifies what must exist or a requirement that must be met for a record to be included in the results.
Cartesian join, Cartesian product
In a _____________, also called a __________ or cross join, each record in the first table is matched with each record in the second table
comparison operator
Indicates how data should relate to the search value
Non-equality Join
Joins tables when there are no equivalent rows in the tables to be joined
aggregate functions
Multiple-row functions are commonly referred to as group functions because they process groups of rows. Because these functions return only one result per group of data, they're also known as ________
SOUNDEX
Oracle 12c can reference the phonetic sound or representation of words with the ______function.
Encryption
Refers to scrambling data to make it unreadable to anyone other than the sender and receiver.
Cartesian Join
Replicates each row from the first table with every row from the second table. Creates a join between tables by displaying every possible record combination.
List of Object Privileges
SELECT: Allows users to display data contained in a table, view, or sequence; also allows generating the next sequence value by using NEXTVAL. INSERT: Allows users to insert data in a table or view. UPDATE: Allows users to modify data in a table or view. DELETE: Allows users to delete data in a table or view. INDEX: Allows users to create an index for a table. ALTER: Allows altering the definition of a table or sequence. REFERENCES: Allows users to reference a table when creating a FOREIGN KEY constraint. This privilege can be granted only to a user, not to a role.
T
T OR F: Both the CURRENT_DATE and SYSDATE functions identify the current date and time
T
T of F: By default, the JOIN keyword creates an inner join. To use it to create an outer join, you include the keyword LEFT, RIGHT, or FULL to identify the join type
T
T or F: A date value must be enclosed in single quote
F: non-equality join is used when the related columns can't be joined with an equal sign—meaning there are no equivalent rows in the tables to be joined.
T or F: An equality join is used when the related columns can't be joined with an equal sign—meaning there are no equivalent rows in the tables to be joined.
F - DD-MON-YY, with MON being the standard three-letter abbreviation for the month.
T or F: Oracle 12c displays dates in the default format MON/DD/YYYY, with MON being the standard three-letter abbreviation for the month.
T
T or F: Users can be assigned a default role that's enabled automatically whenever they log in to the database. The default role should consist of only the privileges the user needs frequently
F - It does allow
T or F: WITH ADMIN OPTION does NOT allow any user or role identified in the TO clause to grant the system privilege to any other database users.
T
T or F: WITH GRANT OPTION enables the user to grant the same object privileges to other users.
T
T or F: You can also use the ORDER BY clause with the optional NULLS FIRST or NULLS LAST keywords to change the order for listing NULL values.
T
T or F: You can use the REGEXP_SUBSTR function to extend the capabilities of the SUBSTR function, using the same pattern-matching operators.
T
T or F: You can use the optional ANY keyword when granting a system privilege to allow the user to perform the privilege system-wide.
T
T or F: the NOT option can be used with all comparison operators to reverse the operation.
T
T or F: the default INNER keyword can be included with the JOIN keyword to specify that only records having a matching row in the corresponding table should be returned in the results.
T
T or F: you must add the ON clause to the JOIN keyword to specify how the tables are related
IN
The ____ operator returns records matching one of the values listed in the condition
ALL
The _____ keyword instructs Oracle 12c to include multiple occurrences of numeric values when totaling a field(When using SUM function)
REPLACE
The ______ function is similar to the "search and replace" function used in many programs. It looks for the occurrence of a specified string of characters and, if found, substitutes it with another set of characters
percent
The _______ sign represents any number of characters (zero, one, or more)
NATURAL JOIN
The _________ ___________ keywords create a join automatically between two tables, based on columns with matching names.
IDENTIFIED BY
The _________ clause specifies the new password
equality
The most common type of join used in the workplace is based on two (or more) tables having equivalent data stored in a common column. These joins are called _________ joins but are also referred to as equijoins, inner joins, or simple joins.
Julian date
The numeric version of a date used byOracle 12c is a _______, which represents the number of days that have passed between a specified date and January 1, 4712 B.C.
DISTINCT
The optional________ keyword instructs Oracle 12c to include only unique numeric values in its calculation (When using SUM function).
Authentication
The process of identifying a user attempting to connect to a system, typically based on a username and password.
Selection
The process of seeing only records meeting certain conditions
USING
The_________clause allows you to create joins based on a column that has the same name and definition in both tables
LENGTH function
To determine the number of characters in a string
UNION ALL set operator
To include duplicates in the results, use the ______
Outer Join
To include records in the join results that exist in one table but don't have a matching row in the other table
outer join
To include records in the join results that exist in one table but don't have a matching row in the other table, you use an ______
+
To tell Oracle 12c to create NULL rows for records that don't have a matching row, use an outer join operator, which looks like this: ______. It's placed in the WHERE clause immediately after the column name from the table that's missing the matching row and tells Oracle to create a NULL row in that table to join with the row in the other table
A secondary sort
When multiple columns are specified in the ORDER BY clause, it is called______
When you use a string literal, such as FL, as part of a search condition, the value is interpreted exactly as listed.
When must you include single quotes?
A primary sort
When only one column is specified in the ORDER BY clause, it is called _____
Inner Joins
With the equality, non-equality, and self-joins you've used so far, a row is returned only if a corresponding record in each table is queried. These types of joins can be categorized as _________ because records are listed in the results only if a match is found in each table.
NVL
You can use the _____ function to address problems caused when performing arithmetic operations with fields that might contain NULL values.
common column
a column with equivalent data existing in two or more tables.
function
a predefined block of code that accepts one or more arguments and then returns a single value as output
The NVL2 function
a variation of the NVL function with different options based on whether a NULL value exists.
regular expressions
allow describing complex patterns in textual data.
The TO_DATE function
allows users to enter a date in any format, and then it converts the entry into the default format used by Oracle 12c
Case conversion functions
alter the case of a character string
join conditions
are instructions in queries that combine data from more than one table.
Set operators
are used to combine the results of two (or more) SELECT statements
Wildcard characters
are used to represent one or more alphanumeric characters. The wildcard characters available for pattern searches in Oracle 12c are the percent sign (%) and the underscore symbol ( _ ).
