SQL Expert 1Z0-061, 1Z0-061 Oracle SQL Fundamentals
Cross JOIN
A Cartesian Product.
Number Datatypes
A data type to store integers (negative, positive, floating) of up to 38 digits of precision. This data type can store numbers in the range of 1.0E-130 to 1.0E126.
1:1 (One-to-One)
Aircraft <> Electrical System
CLOB
An Oracle data type that can hold up to 4 GB of data. These are handy for storing text.
Reference
An object that is referred to.
Group-level results
Clustering rows using the GROUP BY clause and applying an aggregate function to these groups returns the result often referred to as __________________. The HAVING clause restricts group-level results.
Heap table
Consists of variable length rows in random order.
The INITCAP Function
Converts a string of characters into capitalized case. It is often used for data presentation purposes. The first letters of each word in the string are converted to their uppercase equivalents, while the remaining letters of each word are converted to their lowercase equivalents.
What does the principle of Atomicity state?
Either all parts of the transaction much complete or none of them.
What is the DUAL table?
It is a table that contains on column called DUMMY of the character data type.
BLOB
Like CLOB, but binary data that will not undergo character set conversion by Oracle Net.
Logical Data Structures
Tablesspaces, Segments, Extents and Data Blocks
SYSDATE
Takes no parameters and returns the current system date and time according to the database server.
ETL
The process of extracting data from the source system and bringing it to the warehouse.
Atomicity
This ACID rule ensures completeness of a transaction by enforcing the all or nothing rule, meaning all statements must either succeed or fail.
ROWID
This a value coded in base 64 that is the pointer to the location of a row in a table. It is encrypted. Within it is the exact physical address. ROWID is an Oracle proprietary data type that is not visible unless specifically selected.
DECODE
This is a function in Oracle that is used to provide if-then-else type of logic to SQL.
The size of the set affected by an UPDATE is determined by a __________________.
WHERE CLAUSE
Where will appropriate settings for INCREMENT BY, START WITH, and MAXVALUE or MINVALUE come from?
Your business analysts
Adding a column
alter table emp add (job_id number);
ERD
A diagram that displays the relationships between the tables in a database.
SET
These operators combine the results of two component queries into a single result. Queries containing set operators are called compound queries.
Tablespace
This physical storage structure consists of one or more datafiles. They are either dictionary managed or locally managed.
Object Privileges
This provides the ability to perform a task on a specific schema object.
INTERVAL DAY TO SECOND
This type stores time intervals measured in days and seconds.
True or False? All the rows updated in an UPDATE statement will be in one table.
True - It is not possible for a single UPDATE command to affect rows in multiple tables.
True or False? The DATE data type always includes century, year, month, day, hour, minute, and second-even if all these elements are not specified at insert time.
True - Year, month, and day must be specified; if the hours minutes, and seconds are omitted, they will default to midnight. Using the TRUNC function on a date also has the effect of setting the hours, minutes, and seconds to midnight.
How many levels can group functions be nested?
Two levels deep.
TRIM(bothtrimstring from s) & TRIM(trimstring from s)
removes all occurrences of trimstring from the beginning and end of string s if it is present.
Query
A request for data from a table, a combination of tables or a view.
Partitioned Indexes
A resource that allows you to complement partitioned tables or simplify index management.
What is non equijoin?
A row is associated with one or more rows in another table if its column values fall into a range determined by inequality operators.
Instance
A set of memory structures that manages database files
MINUS
A set operator that allows you to see the results of one query that don't exist in another query.
What happens if the percentage_of_rows option is a NULL or negative value?
It defaults to 0. This option specifies the percentage of the result set to retrieve.
What is the purpose of a temporary table?
Programmers can use them as a private storage area for manipulating large amounts of data.
select nvl2(1234, 1, 'a string') from dual;
The ifnotnull term in this query is a number, and the ifnull paramter is a string. Since there is a data type incompatability between them, an "ORA-01722: invalid number" error is returned.
What is Undo Data?
The information needed to reverse the effects of DML statements. It is often referred to as rollback data, but try to avoid that term.
Data Mart
An access layer of a data warehouse. It's a subset of a data warehouse that is built for a specific purpose, usually a particular business unit. These objects are read only and usually used for reporting.
LEAD
An analytic function that lets you query more than one row in a table at a time without having to join the table to itself.
select nvl2(substr('abc', 2), 'Not bc', 'No substring') from dual;
Extracts the characters bc using the SUBSTR function and the NVL2('bc', 'Not bc' , 'No substring') function is evaluated and the ifnotnull parameter, 'Not bc', is returned.
select initcap('init cap or init_cap or init%cap') from dual
This query returns Init Cap Or Init_Cap Or Init%Cap.
select nvl(1234) from dual;
This query returns an error ORA-00909: invalid number of arguments. NVL is supposed to take two parameters.
The names of days and months are automatically padded with spaces. These may be removed using a modifier to the format mask called the ________________________operator.
fill mode (fm), By prefixing the format model with the letters fm, you instruct Oracle to trim all spaces from the names of days and months.
A DDL lock requires an ______________________ on the object it affects.
exclusive lock
Correlated Subquery
A subquery that is used for row-by-row processing. It is executed once for every row of the outer query.
Scala Subquery
A subquery that returns exactly one column value from one row.
EBS
A suite of applications with different modules, such as: • Oracle Financials • Oracle Service Management • Oracle CRM • Oracle Supply Chain It has since been replaced with Oracle Fusion.
Relation
A mathematical term for a table.
UNION
A set operator that allows for the results of one query to be combined with the results of another query, and duplicates removed.
Why use views at all?
Possible reasons include providing security, simplifying user SQL statements, preventing errors, improving performance, and making data comprehensible.
Once the data blocks required for the query are in the database buffer cache, any further processing (such as sorting or aggregation) is carried out in the _______________of the session.
Program Global Area
select nvl(null, 1234) from dual;
Returns1234 after the null keyword is tested and found to be null.
Embedded SQL
SQL statements that are coded into the program and cannot be changed by the application. They may include bind variables that allow for some variation, but the statements themselves are set. The opposite of this concept is dynamic SQL.
INTERVAL DAY TO SECOND
THIS IS USED FOR RECORDING A PERIOD IN DAYS AND SECONDS BETWEEN TWO DATESS OR TIMESTAMPS.
OCM
The third level of the DBA certification track and is targeted to senior DBAs and consultants.
GROUP BY clause
This clause facilitates the creation of groups. It appears after the WHERE clause but before the ORDER BY clause.
What kind of problems can Oracle numeric functions solve?
trigonometric, exponentiation, and logarithmic among others.
COUNT(DISTINCT expr)
Only unique occurrences of expr are counted for each group.
What are the functions of Indexes?
To enforce primary key and unique constraints and to improve performance.
Savepoint
A marker in a transaction that can be set as a point to roll back to at a later time.
Not equal to
<> or !=
Oracle Exadata
A "cloud in a box" composed of database servers, An InfiniBand fabric for storage networking and all the other components required to host an Oracle Database.
ROWID
A 10-byte address of a row in a database. Sufficient information is encoded in this datatype to locate the row on disk, as well as identify the object this datatype points to.
Repository of Metadata
A Data Dictionary
CONCAT()
A STRING function that can be performed to link two or more separate strings values together.
Composite Key
A a primary key that contains more than one column.
Object Relational Tables
A basis for employing datatypes for column definitions, object tables, nested tables, varying arrays, inheritance and more object-oriented concepts by use of tables.
PERCENTILE_CONT
A built in inverse distribution analytic function which works on a continuous distribution model to get the median of a column value. It takes a numeric input which is assumed as a percentile rank and a grouping specification as input.
Wildcard
A character that can be substituted for one or more characters.
Pluggable Database (PDB)
A child database in a multitenant architecture.
OLTP
A class of software programs capable of supporting transaction-oriented applications on the Internet. Typically, these systems are used for order entry, financial transactions, customer relationship management (CRM) and retail sales. Such systems have a large number of users who conduct short transactions. Database queries are usually simple, require sub-second response times and return relatively few records.
ORDER BY
A clause that allows you to sort on a retrieval query.
PaaS
A cloud computing infrastructure which refers to an application development and deployment platform that is delivered as a service to developers, enabling them to quickly build and deploy a SaaS application to end users. The platform typically includes databases, middleware, and development tools, all delivered as a service via the internet.
SaaS
A cloud computing infrastructure which refers to applications that are delivered to end users over the internet. Oracle CRM On Demand is an example of this component offering that provides both multitenant as well as single-tenant options, depending on the customer's preferences.
Single-Row (Tuple)
A collection of fields or a cells, representing one record. It is uniquely identified by it's hidden ROWID
Cloud Computing
A computing-infrastructure and software model for enabling ubiquitous access to shared pools of configurable resources (such as computer networks, servers, storage, applications and services), which can be rapidly provisioned with minimal management effort, often over the Internet. this allows users and enterprises with various computing capabilities to store and process data either in a privately-owned entity, or on a third-party server located in a data center - thus making data-accessing mechanisms more efficient and reliable. This structure relies on sharing of resources to achieve coherence and economy of scale, similar to a utility.
Relational Tables
A data structure that allows for you to store data, by use of columns and rows, which are able to have reference to other similar data structures.
Interval
A data type in Oracle that stores a duration of time. It is different to a date or timestamp, which store a period in time.
Ampersand Substitution
A desirable form of a statement that has a variable or placeholder defined that can be substituted at runtime. Every element of the SELECT statement may be substituted, and the reduction of queries to their core elements to facilitate reuse can save you hours and repetitive work.
ON NULL
A feature added in Oracle 12c, that allows for you to specify on table create, what the default value should be in a NULL situation.
Autocommit
A feature found in many SQL tools, that when it is turned on, every DML statement will commit automatically.. In SQL Developer, you have to enable this feature.
Lock
A feature of Oracle database that allows concurrency control and data consistency. It ensures that only one user can change a particular record at the one time, and that a user cannot delete a record while another user is updating it.
TNSNAMES.ora
A file that contains network service names and how they map to connection descriptors or connection strings.
TO_NUMBER
A function that converts a string to a number.
TO_DATE
A function that is used to change a test string (or variable) into an internal date format.
TO_CHAR
A function that is used to transform a DATE or NUMBER datatype into a displayable text string.
COALESCE
A function that takes many parameters and returns the first parameter that is not NULL.
UML
A general purpose modeling language for visualizing the design of a system.
Equi-Join
A join comparison which is done using an equals sign.
Self JOIN
A join in which a table is joined with itself. To join a table itself means that each row of the table is combined with itself and with every other row of the table.
Outer JOIN
A join similar to the equi join, but Oracle will also return non matched rows from the table.
Non-EQUIJOIN
A join where the condition does not contain an equality operator (e.g. - the operator might be greater than or less than). This type of join combines rows that have non-equivalent values for the specified columns.
Candidate Key
A key or column that can uniquely identify rows in a table. Any of these such keys in a table can be used as the primary key for the table.
Logical Operator
A keyword that allows two expressions to be compared and an overall value of TRUE or FALSE to be returned. In Oracle, they are AND, OR and NOT.
LIKE
A keyword that allows you to compare on partial strings. It us often used in the WHERE clause and uses either % wildcards or _ wildcards for string comparison.
Full Outer JOIN
A keyword that returns all records when there is a match in either left (table1) or right (table2) table records.
ANY
A keyword, when granting privileges, which allows for universal scope for that privilege.
Self-join
A less common technique is to associate rows with other rows in the same table. This association is based on columns with logical and usually hierarchical relationships with each other.
JSON
A lightweight data-interchange format. It is easy for humans to read and write. It is easy for machines to parse and generate. It is based on a subset of the JavaScript Programming Language, Standard ECMA-262 3rd Edition - December 1999.
BFILE
A locator pointing to a file stored on the operating system of the database server. The size of the files is limited to 2 to the power of 64 minus 1, though your operating system may have other idea.
View
A named SELECT statement. If the SELECT is saved as this, then whenever the users query or update rows in the view (under the impression that it is a table), the statement runs and the result is presented to users as though it were a table.
Surrogate Key
A new field created for the purpose of a primary key for a table, instead of using an existing field.
Data File
A physical file on the disk that is created by Oracle Database and contains data structures, such as tables and indexes.
Segment
A portion of the database that consists of one or more extents. Each extent is in turn made up of units called blocks, which are the smallest database units. One or more of these make up a tablespace..
Physical Data Model
A representation of a data design as implemented, or intended to be implemented, in a database management system. In the lifecycle of a project it typically derives from a logical data model, though it may be reverse-engineered from a given database implementation. A complete model of this sort, will include all the database artifacts required to create relationships between tables or to achieve performance goals, such as indexes, constraint definitions, linking tables, partitioned tables or clusters. Analysts can usually use this model to calculate storage estimates; it may include specific storage allocation details for a given database system.
Cartesian Join
A result of data from two tables that have not been joined to each other. The result will include all combinations of table A matching to all combinations of table B, and will be a lot more data than you probably anticipated.
Control File
A root file that tracks the physical components of the database
What is an equijoin?
A row is associated with one or more rows in another table based on the equality of column values or expressions.
Oracle Sharding
A scalability and availability feature for suitable OLTP applications in which data is horizontally partitioned across discrete Oracle databases, which share no hardware or software. An application sees the collection of partitions as a single logical Oracle database. By utilizing this technique, it provides linear scalability with fault isolation, automation of many lifecycle management tasks, excellent runtime performance, and the many advantages that come using an Oracle database in this manner (such as SQL and JSON support) .
NoSQL
A scalable, distributed database. Data includes: relational database-style tables, JSON documents, or key-pairs. This type of database is sharded.
DBA_
A set of views that show the data dictionary objects across all schemas.
LTRIM
A single row character function that removes from the left end of char all of the characters contained in a set. If the set is not specified,, it defaults to a single space.
RTRIM
A single row character function that removes from the right end of char all of the characters contained in a set. If the set is not specified,, it defaults to a single space.
SUBSTR
A single row character function that returns a portion of char, beginning at character position, substring_length characters long. This function calculates lengths using characters as defined by the input character set.
LPAD
A single row character function that returns expr1, left-padded to length n characters with the sequence of characters in exp2. If expr2 is not specified, then the default value is a single space.
JDK
A software development environment that's used for developing Java applications. It is required for using SQL Developer, but is bundled with recent versions.
Hint
A special keyword that can be used in a query to alter the execution plan. They are used mainly when the developer or DBA knows things about the query that the database doesn't.
TRUNCATE
A statement is used to remove all records from a table in Oracle. It performs the same function as a DELETE statement without a WHERE clause. Warning: When you use this statement on a table, the results can not be rolled back.
SELECT
A statement that restricts and filters a set of rows to obtain a result set (subset) of records from one or more tables.
Inner Query
A subquery
Operator
A symbol (>, =, <=, etc) or keyword (NOT, IN, ALL, UNION) that is placed between expressions to compare them.
Private Synonym
A synonym that is accessible to users of the particular schema where the synonym is created.
Heap-Organized Table
A table in Oracle where the rows are not stored in any particular order. When a specific order is required, it is retrieved using a SELECT query with an ORDER BY clause. The other types of tables are index-organized tables and external tables.
Dimension Table
A table in a data warehouse that contains more information about an attribute in a fact table.
Fact Table
A type of data warehouse table that stores the measurements or values that you want to track in your data warehouse. It's the center of the design and relates to dimension tables.
UDT
A type that is created by a user in a PL/SQL program.
RMAN
A utility used to backup, restore, clone, and recover Oracle databases.
Constant
A variable that does not change value.
self-referencing foreign key constraint
A variation of the foreign key constraint. This defines a condition where the parent and child rows exist in the same table. An example is a table of employees that includes a column for the employee's manager. The manager is an employee and must exist in the table.
Query Language
A vehicle that allows applications to access data
USER_SYNONYMS
A view that describes the private synonyms (synonyms owned by the current user). Its columns () are the same as those in ALL_SYNONYMS.
Examples of group functions
AVG, SUM, MIN, MAX, COUNT, STDDEV, and VARIANCE
Anonymous Block
Aa piece of PL/SQL code that has not been created as a function, package, or procedure. You can run PL/SQL code by typing the code into an SQL window and running it, without creating a function, procedure, or package.
What are the two forms of VARIANCE and STDDEV?
Aggregate form and Analytic form.
The SQL Row-Limiting Clause
Allows a dataset to be segmented in various ways. The format of the row-limiting clause in the context of the SQL SELECT statement is as follows: SELECT * From table Where Order by OFFSET offset FETCH ROW
Autonomous Transaction
An activity generated by another activity. The generated action executes without interfering with the action that created it. The creating activity is suspended until the referenced definition here, is completed using a COMMIT or ROLLBACK.
Multivalued Attribute
An attribute that has a value of a set of values. Oracle includes the Nested Table and VARRAY data types for this.
Service Identity Domain
An identifier that Oracle uses to control the authentication and authorization of the users who can sign in to an Oracle Cloud service and what features they can access.
WITH GRANT
An option that allows the granted user to grant the OBJECT privilege to other users. When this privilege is revoked, it cascades to all it's grantees, therefore revoking them as well.
WITH ADMIN
An option that allows the user granted a privilege to grant it to other users in turn.
OR REPLACE
An optional view keyword. If the view already exists, it will be dropped before being created.
Tuple
Another name for a row in a table.
grouping attribute
Another name for the column or expression specified in the GROUP BY clause and is the component that rows are grouped by.
Determinant
Any attribute (simple or composite) on which some other attribute is fully functionally dependent.
What case must you use to submit SQL statements to the database?
Any case
Statement
Any text that the database engine recognizes as a valid command.
My Service Page
Area to manage the services and its users.op
What else can address synonyms?
As well as SELECT statements, DML statements can address synonyms as though they were the object to which they refer.
select months_between(sysdate, sysdate-31) from dual;
Assume that the current date is 16-APR-2009. This query returns 1 as the number of months between 16-APR-2009 and 16-MAR-2009.
Character Datatypes
CHAR, NCHAR, NVARCHAR2, VARCHAR2
What is it called if the unique constraint is composed of more than one column?
Composite key unique constraint - the columns do not have to be the same data type or be adjacent in the table definition.
select count(commission_pct), count(1) from employees;
Counts the rows with non-null COMMISSION_PCT values and returns 36. It also counts the literal expression 1, which is not based on a named column and is therefore evaluated for every row, returning 107.
1:N (One-to-Many)
Customer <> Reservation
M:N (Many-to-Many)
Customers <> Flight
A simple view
Draws data from one detail table, uses no functions, and does no aggregation.
What is the most common way that tables can be joined?
Equijoin
select min(start_date), max(end_date) from job_history;
Evaluates a DATE column and indicates that the earliest START_DATE in the JOB_HISTORY table is 17-SEP-1995 and the latest END_DATE is 07-JAN-2015.
ROWS WITH TIES
Fetches any additional rows with the same sort key as the last row retrieved but has any effect only if an ORDER BY clause is specified.
select length(SYSDATE) from dual;
First evaluates the SYSDATE function, which is assumed to be 07-APR-38. This date is implicitly converted into the character string '07-APR-38', and the LENGTH function returns the number 9.
CHAR
Fixed-length character data, from 1 byte to 2,000 bytes, in the database character set. If the data is not the length of the column, then it will be padded with spaces.
What leverages the power of the undo mechanism by giving you the option of querying the database as it was at some time in the past?
Flashback Query Capability
select next_day(to_date('01-JAN-2009','DD-MON-YYYY'), 'tue') from dual;
Here, 01-JAN-2009 is a Thursday. Therefore, the net time a Tuesday occurs will be five days later on 06-JAN-2009.
When the business analysts specify business rules that will be implemented as constraints, They are in effect specifying _______________.
Indexes
By default, all constraints are in what constraint checking state?
IMMEDIATE and not deferrable
How is the date displayed when using SYSDATE?
If the database server is installed with AMERICAN as the default language, the SYSDATE function returns the DD-MON-RR components of the current server system date. If the database server is located in a different timezone from a client querying the database, the date and time returned by the SYSDATE will differ from the local time on the client machine.
What does CURRVAL mean?
In a sequence this is the last value issued to the current session, not necessarily the last value issued. You cannot select this value until after selecting the NEXTVAL.
Key column
In a unique constraint if the constraint is based on a single column, this is known as a _______________.
insert into order_items (order_id, order_item_id, product_id) values (order_seq.currval, line_seq.nextval, 'A111');
Inserts the line of the order, using the previously issued order number from ORDER_SEQ as the foreign key to connect the line items to the order.
CBO
It's a method of query optimization that uses statistics to generate several execution plans, and uses the one with the lowest number of resources required.
CLI
It's a type of tool that uses text and a command line to allow users to run commands on a database. Oracle's version of this tool is called SQL*Plus. They have also recently developed a newer tool called SQLcl, which is a more powerful version of SQL*Plus.
ADF
It's a web-development framework that is based on JSF (Java Server Faces). It provides a framework for J2EE developers to develop on.
ASM
It's feature that was introduced in Oracle 10g to simplify the storage of data files, control files, and log files.
LISTAGG syntax
LISTAGG(expr, ['delimiter']) WITHIN GROUP (ORDER_BY_CLAUSE) The expr parameter can be any valid expression. Null values are ignored. The delimiter specifies the optional string to separate the expr. The ORDER BY clause determines the order in which the concatenated values are returned.
NCLOB
Like CLOB, but data is stored in the alternative national language character set, one of the permitted Unicode character sets.
The searched CASE expression
Lists a separate condition for each comparison expression.
The simple CASE expression
Lists the conditional search item once, and equality to the search item is tested by each comparison expression.
The INSTR Function (In-String)
Locates the position of a search string within a given string. It returns the numeric position at which the nth occurrence of the search string begins, relative to a specified start position. If the search string is not present the INSTR function returns zero.
Composite inequality operators
Made up of more than one symbol
What happens in division by a NULL value?
NULL
select to_number('$1,000.55' , '$999,999.99') from dual;
Matches the dollar symbol, comma, and period from the string to the format mask, and although the numeric width is larger than the string width, the number 1000.55 is returned.
What would the data type of expr be?
NUMBER, DATE, CHAR, or VARCHAR2
______________________means that if a row in the parent table is deleted, Oracle will search the child table for all the matching rows and delete them too.
ON DELETE CASCADE
How many parameters does the date ROUND function take?
One mandatory parameter and one optional parameter and has the syntax ROUND(source date, [date precision format]). The source date parameter represents any date item. The date precision format parameter specifies the degree of rounding and is optional. If it is absent, the default degree of rounding is day.
Left Outer JOIN
One of the JOIN operations that allow you to specify a join clause. It preserves the unmatched rows from the first (left) table, joining them.
Group functions
Operate on aggregated data and return a single result per group. These groups usually consist of one or more rows of data.
A table can have __________ primary key. A table can however, have any number of _____________ constraints and not-null columns, so if there are several columns that the business analysts have decided must be unique and populated, one of these can be designated the primary key and the other made unique and not null.
One, Unique
Date functions
Provide a convenient way to solve date-related problems without needing to keep track of leap years or the number of days in particular months.
Joining
Refers to the interaction of tables with each other in a query.
Projection
Refers to the restriction of columns selected from a table.
Selection
Refers to the restriction of the rows selected from a table.
Variance
Refers to the sum of the squared differences between the actual value and the average value divided by N-1 or N depending whether variance is being established for a sample or the entire population.
SELECT job_id, avg(salary), count(*) FROM employees GROUP BY job_id HAVING avg(salary) > 10000;
Refines the results by conditionally excluding those aggregate rows where the average salary is less than or equal to 12000, using a HAVING clause.
WHERE clause
Restricts rows returned by a query. Rows are included if they fulfill the conditions listed and are sometimes known as row-level results.
What does it mean to rollback a transaction?
Rolling back a transaction means to use data from the undo segments to construct an image of the data as it was before the transaction occurred. This is usually done automatically to satisfy the requirements of the ACID test.
What happens when you append the word DESC to the ORDER BY clause?
Rows are returned sorted in descending order.
All tables belong to a _________________ or _____________.
Schema, Owner
select instr('1#3#5#7#9#', '#' ,5) from dual;
Searches for the has from position 5 and finds the next occurrence at position 6.
Text Indexes
Sets of tables and indexes maintained by Oracle to support complex text-searching requirements. Examples,expanding word stems or searching for phrases.
ONLY or WITH TIES
Should be read together with ROW/ROWS keywords.
fx modifier
Similar to fm used with the TO_CHAR function. It specifies an exact match for string1 and the format mask. When the _______________________ is specified, character items that do not exactly match the format mask yield an error.
select add_months(to_date('07-APR-2009','DD-MON-YYYY'), -12) from dual;
Since the number of months added in this query is -12, the date 07-APR-2008 is returned, which is 12 months prior to the start date.
G1(G2(group_item)=result
Supports two nested group functions, like LUM(AVG(group_item)). In this case, a GROUP BY clause is necessary because the average value of the goup_item per group is calculated before being aggregated by the SUM function.
Converting Characters to dates using the TO_DATE Function.
TO_DATE(string1, [format], [nls_parameter]) Only string1 is mandatory, and if no format mask is supplied, string1 must be implicitly convertible into a date. The optional format parameter is almost always used and is specified in single quotation marks.
SYSDATE
Takes no parameters and returns the current system date and time according to the database server. If the database server is installed with the AMERICAN as the default language, the SYSDATE function returns the DD_MON_RR components of the current server system date.
How many parameters does the CASE expression take?
The CASE expression takes at least three mandatory parameters but can take many more. Its syntax depends on whether a simple or a searched CASE expression is used.
How many parameters does NULLIF take?
The NULLIF function takes two mandatory paramters of any data type. Its syntax is NULLIF(ifunequal, comparison_term), where the parameters ifunequal and the comparison_term are compared. If they are identical, the NULL is returned. If they differ, the ifunequal parameter is returned.
Views for performance
The SELECT statement behind a view can be optimized by programmers so that users don't need to worry about tuning code.
ORACLE_HOME
The directory or environment variable that points to where Oracle is installed.
select to_char(sysdate, 'fmMonth')||'is a special time' from dual;
The fm modifier is applied to query 3, and the resultant string is 'Januaryis a special time'. Note there is no space between January and the literal 'is a special time'.
XE
The freely available Oracle database. It is also the SID for connecting to the Express Edition.
select replace('1#3#5#7#9#' , '#', '->') from dual;
The hash is specified as the search character, and the replacement string is specified as ->. The hash symbol occurs five times in the source, and the resultant string is 1->3->5->->7->9->.
Container Database (CDB)
The host database in a multitenant architecture.
Field
The intersection of a row and a column
Outer Query
The main query
What is scale?
The number of digits from the decimal point to the least significant digit. A positive scale is the number of significant digits to the right of the decimal point to (and including) the least significant digit. A negative scale is the number of significant digits to the left of the decimal point to (but not including) the least significant digit.
select add_months(to_date('31-DEC-2008','DD-MON-YYYY'), 2.5) from dual;
The parameter specifying the number of months to add contains a fractional component, which is ignored. Add two months to the date 31-DEC-2008 should return the date 31-FEB-2009, but there is no such date, so the last day of the month, 28-FEB-2009, is returned.
DBA
The person responsible for maintaining the database. Their role includes monitoring the database for issues, applying patches, performing backups, and much more.
What happens if a DML statement runs out of undo space?
The portion of it that had already succeeded will be rolled back. The rest of the transaction remains intact and uncommitted.
OCP
The second certification on the Oracle DBA track. It is targeted to mid to senior level DBAs.
ENABLE NOVALIDATE
There may already be nonconforming data in the table, but all data entered now must conform.
Bind Variable/Dynamic Parameter
These are an alternative way to pass data to the database.
Referential Integrity
This term means that the foreign key in any referencing table must always refer to a valid row in the referenced table. Using this technique ensures that the relationship between two tables remains synchronized during updates and deletes.
Business Intelligence
This term refers to technologies, applications and practices for the collection, integration, analysis, and presentation of information.
Bounce
To shut down and restart a database or server.
An application's indexing strategy is critical for performance. (True or False)
True
Undo resides in tablespaces, whereas redo resides in files.
Undo data is a segment structure within the database. Redo is written out to operating system files.
Why would you use a synonym?
Use of synonyms means that an application can function for any user, irrespective of which schema owns the views and tables or even in which database the tables reside.
Temporary undo segments
Used for storing undo data generated by DML against global temporary tables.
On-Demand Self-Service
Users can obtain computing capabilities such as server time or network storage as needed automatically on their own
select to_date('25-DEC-10', 'fxDD-MON-YYYY') from dual;
Uses the fx modifier in its format mask. Since the year component of the string is 10 and the corresponding format mask is YYYY, the fx modifier results in an ORA-01862 error being returned: "the numeric value does not match the length of the format item."
UNUSED
When you set your table or column to this term, Oracle deletes this column definition from the dictionary. Space allocated to this column is not freed up.
Large Object Datatypes (LOBs)
With the growth of the internet and content-rich applications, it has become imperative that the database support a datatype that fulfills the following: Can store unstructured data Is optimized for large amounts of such data Provides a uniform way of accessing large unstructured data within the database or outside
Is it possible to make you undo datafiles autoextensible?
Yes - This will ensure that transactions will never run out of space, but Oracle will not extend them merely to meet the UNDO_RETENTION target; it is therefore still possible for a query to fail with "snapshot too old". However, you should not rely on the autoextend; your tablespace should be the correct size to begin with.
Is the UPDATE command used to change rows that already exist?
Yes - rows that have been created by an INSERT command or possibly by a tool such a Data Pump.
Dropping columns
alter table emp drop column comm;
Creating a public synonym
create public synonym emp for hr.employees@prod;
Creating a table with a subquery
create table employees_copy as select * from employees;
The ____________________________, who should have the best idea of what is going on in the code and the nature of the data, will also be involved in developing the indexing strategy.
developer
What command in SQL*Plus shows the setting for the four instance parameters that include the string "undo" in their name.
show parameters undo
What can the input values to a single-row function include:
user-specified constants or literals, column data, variables, or expressions optionally supplied by other nested single-row functions.
Resource Pooling
Cloud computing services to multiple customers that are hosted on shared physical resources and dynamically allocated to meet customer demand.
What is a literal?
Commonly used in expressions and refer to numeric, character or date and time values found in SELECT clauses that do not originate from any database object.
select decode(1234, 123, '123 is a match') from dual;
Compares the number 1234 with the first comparison term 123. Since they are not equal, the first result term cannot be returned. Further, as there is no default iffalse parameter defined, a null is returned.
A function is often described as what?
A black box that takes an input, performs a calculation, and returns a value. Instead of focusing on their implementation details, it is more useful to concentrate on the features that built-in functions provide.
OFFSET
A clause that allows for you to specify a starting row for the return set.
GROUP BY
A clause that is used to assemble the results of a SELECT query based on one or more columns. It is also used with SQL functions to assemble the result from one or more tables.
DISTINCT
A clause that is used to remove duplicates from the result set.
FETCH FIRST
A clause that limits the number of rows returned by a given query.
Explain Plan
A command in SQL for showing the estimated execution cost and method of a query. It's helpful to see how the query runs and identify any opportunities for improvement.
Audit
A command that can be used on the Oracle database to track SQL statements in user sessions. There are several privileges you need, depending on what you want to assess.
UNDEFINE
A command that is used to clear a substitution variable.
DEFINE`
A command that is used to set a substitution variable to a given value. If it is set prior to running the SQL statement with a substitution variable, there will be no dialog box presented.
Logical Data Model
A data model of a specific problem domain expressed independently of a particular database management product or storage technology (physical data model) but in terms of data structures such as relational tables and columns, object-oriented classes, or XML tags. This is as opposed to a conceptual data model, which describes the semantics of an organization without reference to technology.
REF CURSOR
A data type that holds a cursor value. It is used to pass the single cursor object to a different part of the code, instead of the individual values within it.
Star Schema
A database design for data warehouses and data marts that contains a single fact table and multiple dimension tables linked to it.
Network Database Model
A database model conceived as a flexible way of representing objects and their relationships. Its distinguishing feature is that the schema, viewed as a graph in which object types are nodes and relationship types are arcs, is not restricted to being a hierarchy or lattice.
Directory
A database object on the Oracle database that refers to a folder directory on the server.
TIMESTAMP
A datatype which stores all the information that the DATE datatype stores, but also includes fractional seconds. If you want to convert a DATE datatype to this datatype format, just use the CAST function.
Literal
A number, character, or string that is enclosed in single quotes in a query.
OBIEE
A set of business intelligence and analytics platforms that perform business intelligence functions on a data warehouse.
GV$
A set of views that have, as their first column, the instance identifier (INST_ID). You should always use these views rather than V$ whenever possible.
USER_
A set of views that show only the data dictionary objects that exists in the schema that is performing the query.
LAST_DAY
A single row datetime function that returns the last day of the month that contains date.
1st Normal Form
A table shall not contain repeating groups.
Mutating Table
A table that is being changed. Errors can happen if a table is being modified, which calls a trigger to refer to that same table.
SQL Operator
A vehicle used to manipulate individual data items and return results. The data items are called operand or arguments. They are represented by special characters or keywords. There are two general classes associated with them Unary and Binary.
USER_COLL_TYPES
A view that describes named collection types (VARRAYs, nested tables, object tables, and so on) in the current user's schema. Its columns are the same as those in "ALL_COLL_TYPES".
USER_COL_PRIVS_RECD
A view that describes the column object grants for which the current user is the grantee. Its columns (except for GRANTEE) are the same as those in ALL_COL_PRIVS_RECD.
USER_COL_PRIVS_MADE
A view that describes the column object grants for which the current user is the object owner. Its columns (except for OWNER) are the same as those in ALL_COL_PRIVS_MADE.
PLS_INTEGER
A data type, only available in PL/SQL, that is used for storing signed integers. They are generally faster than using NUMBER variables in PL/SQL.
REVOKE
A statement to remove privileges from a specific user or role, or from all users, to perform actions on database objects.
TNS
A technology for connecting to Oracle databases.
SESSION_PRIVS
A view that describes the privileges that are currently available to the user.
DBA_USERS
A view with information describing all users of the database.
Broad Network Access
Access through a browser on any networked device
select sum(2) from employees;
Adds the number 2 across 107 rows and returns 214.
MAX_STRING_SIZE
Allows string data types to be much larger than in previous versions when it is changed from its default value of STANDARD to EXTENDED.
RAW
Also called source data or atomic data, is data that has not been processed for use. A distinction is sometimes made between data and information to the effect that information is the end product of data processing.
LAG
An analytical function that can be used to get the value of a column in a previous row.
Rollback Segment
An object that contains images of before data was written to the database. They are used to undo changes when a transaction is rolled back.
ACID
Atomicity, Consistency, Isolation, and Durability
Outer-join
Available to fetch these one-legged or orphaned rows if necessary.
AVG(ALL expr) and AVG(expr)
Add the non-null values of expr for each row and divide the sum by the number of non-null rows in the group. The expr parameter must be a numeric value.
select avg(commission_pct) from employees;
Adding the non-null COMMISSION_PCT values produces a total of 7.9. Dividing this by the employee records with non-null COMMISSION_PCT values (36) yields 0.219444444, which is returned by this query.
How is variance calculated?
By first calculating the average of the values in the set. Then for each number, subtract the average value and square the result. This is the squared difference. The variance is then the sum of all the squared differences divided by N-1.
The syntax for the Simple CASE expression
CASE search_expr WHEN comparison_expr1 THEN iftrue1 [WHEN comparison_expr2 THEN iftrue2 ... WHEN comparison_exprN THEN iftrueN ELSE iffalse] END
select count(distinct salary) from employees;
Considers the 107 non-null rows, determines the number of unique SALARY values, and returns 58.
Constraint Checking
Constraints can be checked as a statement is executed (an IMMEDIATE constraint) or when a transaction is committed (a DEFERRED constraint).
ORACLE_DATAPUMP
An "access driver", a method whereby you can load an external table using an Oracle facility by the same name. By its use, it allows you to unload and transform large volumes of data into a platform-independent flat file for data propagation or storage, either serially or in parallel. To unload data using an external table, utilize the Oracle Database 10g new feature named this access driver.
FLOAT
An ANSI datatype, floating point number with a precision of 126 binary (or 38 decimal). Oracle also provides BINARY_FLOAT and BINARY_DOUBLE as alternatives.
ODI
An ETL tool used to move data between different systems.
NULL refers to __________________________.
An absence of data.
Expression
An arbitrarily complex combination of operands (variables, constants, literals, operators, function calls, and placeholders) and operators. The simplest of such is a single variable.
IS TRUNCATE a DML or DDL statement?
DDL Command
External Tables
Data stored in flat files, that can be treated like tables.
What is "expired undo"?
Data that is no longer needed for read consistency and may be overwritten at any time as space in undo segments is reused.
Synonym
Database objects that enable you to call a table by another name. Because they are simply aliases, it requires no storage other than its definition in the data dictionary. In addition they are useful for hiding the identity and location of an underlying schema object.
select mod(6, 2) from dual;
Divides 6 by 2 perfectly, yielding 0 as the remainder.
MIN(expr) MIN(ALL expr) MIN(DISTINCT expr)
Examine the values for expr in a group of rows and return the smallest value.
"ORA-00923: FROM keyword not found where expected" error is returned, what does this mean?
If a multiworded, space-separated alias is specified, and is not double quoted.
ON DELETE SET NULL
If a row in the parent table is deleted, Oracle will search the child table for all the matching rows and set the foreign key columns to null. This means that the child rows will be orphaned but will still exist. If the columns in the child table also have a not-null constraint, then the deletion from the parent table will fail.
OR REPLACE
If the view already exists, it will be dropped before being created.
Dictionary Managed Tablespaces
In this form of physical storage management, space management is recorded in the data dictionary.
What does the cache keyword instruct Oracle to do?
It instructs oracle to pregenerate sequence numbers in batches. This means that they can be issued faster than if they had to be generated on demand.
Object types that share the same namespace
Materialized views, Packages, Private synonyms, Sequences, Stand-alone procedures, Stand-alone stored functions, User-defined types and operators, Tables, Views
Substitution Variables
May be regarded as placeholders.
You are guaranteed that any column restricted by the _______________ constraint contains some data.
NOT NULL
What datatypes do the MAX and MIN functions operate on?
NUMBER, DATE, CHAR, and VARCHAR2 They return a value of the same data type as their input arguments, which are either the largest or smallest items in the group.
How many undo tablespaces may be in use at a time?
No matter how many undo tablespaces there may be in a database, generally speaking only one will be in use at a time. The segments in any other undo tablespaces will have a status of offline, indicating that they will not be used.
Is a COMMIT necessary to make the increment of a sequence permanent?
No. It is permanent and made visible to the rest of the world the moment it happens. It can't be rolled back either.
Is it possible to find out what the last value issued by a sequence was.
No. There is no way to find out what the last value issued by a sequence was. You can always obtain the next value by incrementing it with NEXTVAL, and you can always recall the last value issued to your session with CURRVAL, but can not find the last value issued.
date ROUND function
Performs a rounding operation on a value based on a specified date precision format. The value returned is rounded either up or down to the nearest date precision format.
SUM(expr) and SUM(ALL expr)
Provide a total by adding expr for each row in the group. Null values are ignored. The expr parameter must be a numeric value.
SUM(DISTINCT expr)
Provides a total by adding all the unique values returned after expr is evaluated for each row in the group.
Foreign Key constraint
Relates a column in the child table to the primary key or to a unique key in the parent table.
What is relational theory?
Relations or tables are operated on by a formal language called relational algebra.
TRIM(leadingtrimstring from s)
Removes all occurrences of trimstring from the beginning of string s if it is present.
What does TRIM do?
Removes spaces from both sides of the input string.
The LOWER Function
Replaces the uppercase characters in a string with their lowercase equivalents. select lower(100+100), lower('SQL'), lower(sysdate) from dual; The numeric and date expressions are evaluated and implicitly converted into character data before the LOWER function is executed.
select department_id, sum(commission_pct) from employees where commission_pct is not null group by department_id;
Restricts the rows returned from the EMPLOYEES table to the 35 rows with non-null COMMISSION_PCT values. These rows are then divided into two groups: 80 and NULL based on the DEPARTMENT_ID grouping attribute. The result set contains two rows, which return the sum of the COMMISSION_PCT value for each group.
What is the predefined order of evaluation (operator precedence) when more than one operator occurs in an expression?
Round brackets have the highest precedence. Division and multiplication operations are next in the hierarchy and are evaluated before addition and subtraction, which have the lowest precedence.
Inner joins
Rows with null or differing entries in common join columns are excluded when equijoins and non-equijoins collectively known as inner joins are performed.
An example of concatenation
SELECT 'THE' || REGION_NAME || ' region is on Planet Earth' "Planetary Location" FROM REGIONS; Here, the character liter The is concatenated to the contents of the REGION_NAME column. This new string of characters is further concatenated to the character literal region is on Plant Earth, and the entire expression is aliased with the friendly heading Planetary location.
TRUE or FALSE? Use of synonyms means that an application can function for any user, irrespective of which schema owns the views and tables or even in which database the tables reside.
TRUE (ex... select* from hr.employees@prod;
Views to make Data Comprehensible
Tables that are linked by primary key-to-foreign key relationships, are not defined on any identifiers visible to users (such as customer number). They are based on columns the user never see that have values generated internally from sequences. The forms and reports used to retrieve customer information never address these table directly; they all work through views.
Invisible Column
The column won't appear in generic queries until the column will be explicit in the sql statement.
Datatypes
There are 20 of these divided into 6 groups: Character Number Long/Raw Datetime Large Objects ROW ID
Hash Clusters
These force a random distribution of rows, which will break down any ordering based on the entry sequence.
BINARY_DOUBLE
This is a double-precision native floating point number that supports +Inf, -Inf, and NaN values. It is an approximate numeric value consisting of an exponent and mantissa. You can use exponential or E-notation. It has binary precision 53.
Role
This is a security convention, which are used to group together privileges or instances of this item.
CASE
This is a statement that is like a series of IF statements, only using the key word WHEN. This statement is evaluated from top to bottom. If a condition is true, then corresponding THEN clause is executed and execution jumps to the end of the (short circuit evaluation) clause.
4th Normal Form (4NF)
This is the step in the normalization process that ensures there are "no non-trivial multivalued dependencies other than a candidate key."
q Operator
This operator is used in SQL statements to show single quotation mark. It is not supported in 9i. This operator will support for 10g or higher. For using the quote (q) operator, We can use single or multi byte delimiter or any of the following character pairs: -> [] -> {} -> () -> <>
ALL
This operator returns true if all of the subquery values meet the condition.
WITH READ ONLY
This prevents DML through the view.
Ordinality
This term is also known as "modality," of the relationship, which indicates whether the relationship from the parent table to the child table is optional or not. For instance, the relationship between a book and author is mandatory, while the relationship between a book and an owner would be optional, since a book can be unowned, but somebody must have written it. This is represented by having an O on the relationship if it is optional (Zero or more) or a straight, perpendicular line, which indicates it is required (One or more).
Precision
This term is the number of significant digits. Oracle guarantees the portability of numbers ranging from 1 to 38. This element is always includes the scale portion.
How many parameters does the NVL2 function take?
Three mandatory parameters with the syntax NVL2(original, ifnotnull, ifnull), where original represents the term being tested. Ifnotnull is returned if original is not null, and ifnull is returned if original is null.
How many parameters does the REPLACE Function take?
Three parameters, with the first two being mandatory. If the replacement term parameter is omitted, each occurrence of the search item is removed from the source string. In other words, the search item is replaced by an empty string.
How many parameters does the substring function take?
Three parameters, with the first two being mandatory. The default number of characters to extract is equal to the number of characters from the start position to the end of the source string.
TRUE or FALSE? An INSERT command can insert one row, with column values specified in the command or can insert a set of rows created by a SELECT statement.
True
True or False? Deadlocks are resolved automatically.
True
True or False? Each column has an associated data type that constrains the nature of the data it can store.
True - A NUMBER column cannot store character information. A DATE column cannot store random characters or numbers. However, the character equivalents of both number and date information can be stored in a VARCHAR2 field.
True or False? The TRUNC function performs a truncation operation on a numeric value based on the decimal precision specified.
True - A numeric truncation is different from rounding in that it drops the numbers beyond the decimal precision specified and does not attempt to round up or down if the decimal precision is positive. However, if the decimal precision (n) is negative, the input value is zeroed down from the nth decimal position.
True or False? Public synonyms exist independently of a schema.
True - A public synonym can be referred to by any user to whom permission has been granted to see it without the need to qualify it with a schema name.
Suppose you want the last names of employees who earn a salary in the range of $3,400 and $4,000.
select last_name from employees where salary between 3400 and 4000; This is also equivalent: select last_name from employees where salary >=3400 and salary <=4000;
When the statement is executed, the SQL *Plus client processes the statement, notices a substitution variable, and attempts to resolve this variable's value in one of two ways. What are they?
(1) It checks whether the variable is defined in the user session. (DEFINE COMMAND). (2) If the variable is not defined, the user process prompts for a value that will be substituted in place of the variable. Once a value is submitted, the statement is complete and is executed by the Oracle server.
When is the ampersand substitution variable resolved?
At execution time and is sometimes known as run-time binding or run-time substitution.
Extent
A set of logically contiguous blocks allocated in an Oracle database. These are units of database space distribution made up of data blocks.
JDBC
A standard API that allows Java programs to access databases.
USER_TAB_COLUMNS
A view that describes the columns of the tables, views, and clusters owned by the current user. Its columns (except for OWNER) are the same as those in "ALL_TAB_COLUMNS". To gather statistics for this view, use the ANALYZE SQL statement.
USER_TAB_PRIVS
A view that describes the object grants for which the current user is the object owner, grantor, or grantee. Its columns are the same as those in DBA_TAB_PRIVS.
What does ACID stand for?
Atomicity, consistency, isolation, and durability
select mod(7, 35) from dual;
Attempts to divide 7 by 35. Since the divisor is larger than the dividend, the number 7 is returned as the modulus value.
Physical Data Structures
Data Files, Control Files, and Online Redo Files
What happens when a NULL is encountered by the character concatenation operator?
It is simply ignored.
What commands are DML?
SELECT, INSERT, UPDATE, DELETE, MERGE
True or False? The percentage symbol may be similarly escaped when it occurs naturally as character data.
True
Dropped Tables
Using the FLASHBACK TABLE TO BEFORE DROP command to recover tables.
ORA-00979: not a GROUP BY expression error
If a GROUP BY clause is present but that item is not a grouping attribute.
Second normal form
Removes attributes from the entity (1NF) that are not dependent on the primary key.
MOD
Returns the numeric remainder of a division operation.
select substr('1#3#5#7#9#', 5, 3) from dual;
Returns the three characters occupying positions 5-7, which form the substring 5#7.
The REGIONS and COUNTRIES table both share the REGION_ID column. They may be naturally joined without specifying join columns, as shown here:
SELECT region_name FROM regions NATURAL JOIN countries WHERE country_name='Canada'; In the first query, the REGION_ID column is identified as the only commonly named column in both tables. In this case, because the COUNTRY_NAME value must be Canada, a REGION_NAME of Americas is returned.
If the variable is meant to substitute a character or date value, the literal needs to be enclosed in __________________.
Single quotes
Conversion functions
Single-row functions designed to alter the nature of the data type of a column value, expression, or literal.
IDE
Software that allows developers to write code and perform many other tasks for application development.
select trunc(1601.916, -3) from dual;
Specifies a negative number (-3) as its decimal precision. Three places to the left of the decimal point implies that the truncation happens at the hundreds digit.
Inner Joins
The ________________________ is implemented using three possible join clauses that use the following keywords in different combinations: NATURAL JOIN, USING, and ON.
Commit
The act of saving a transaction to the database permanently.
Boolean
The concept of true or false. Can also be represented as 1 or 0. In Oracle, it's a PL/SQL data type. It can hold the values of TRUE, FALSE, or NULL.
OCA
The entry-level Oracle DBA certification. Once you have this certification, you have the skills for a junior level DBA.
select department_id, max(salary), count(*) from employees group by department_id order by department_id;
The grouping attribute in this example is the DEPARTMENT_ID column. The dataset, on which the group functions in the SELECT list must operate, is divided into 12 groups, one for each department. For each group(department), the maximum salary value and the number of rows are returned. Since the results are sorted by DEPARTMENT_ID, the third row in the set of results contains the values 11000 and 6. This indicates that 6 employees have the same DEPARTMENT_ID value (which you happen to know is 30). Of these 6, the highest earner has a SALARY value of 11000. This query deomonstrates that the grouping attribute does not have to be included in the SELECT list.
Relationship
The link or association between two entities in an ERD or tables in a database. There are several types of relationships: • One to one • One to many • Many to many
Data Aggregation
The process of computing data to present it in summary form for statistical analysis. It is the process of compiling data for specific groups based on variables such as age, job, or salary. You may want to know who the top 10 selling artists were for a record company.
Conversion
The process of modifying a value from one data type to another.
Datablock
The smallest amount of data that a program can request. It is a multiple of an operating system block, which is the smallest amount of data that can be retrieved from storage or memory. Multiple blocks in a database comprise an extent.
Views
To the user, a _________looks like a table, in other words, a two-dimensional structure of rows of columns against which the user can run SELECT and DML statements.
True of False? ROUND, TRUNC, AND MOD are single-row functions.
True
True or False? A foreign key constraint is defined on the child table, but a unique or primary key constraint must already exist on the parent table.
True
True or False? As far as datafile management is concerned, an undo tablespace is the same as any other tablespace.
True - Files can be added, resized, taken online and offline, and moved or renamed. But it is not possible to specify any options regarding storage. You cannot specify automatic segment space management, and you cannot specify a uniform extent size.
True or False? It is usually possible to execute DML against a simple view, but not always.
True - For example, if the view does not include a column that has a NOT NULL constraint, then an INSERT through the view cannot succeed (unless the column has a default value). This can produce a disconcerting effect because the error message will refer to a table and a column that not mentioned in the statement.
True or False? An UPDATE command must honor any constraints defined for the table, just as the original INSERT would have.
True - For example, it will not be possible to update a column that has been marked as mandatory to a NULL value or to update a primary key column so that it will no longer be unique.
True or False? Conforming to just one of the OR conditions is sufficient for the record to be returned.
True - If it conforms to none of the conditions, the row is excluded.
True or False? Oracle allows columns to defined with ANSI, DB2, and SQL/DS data types.
True - These are converted internally to Oracle data types.
True or False? It is only possible to join two terms with one instance of the concat function but you can nest the CONCAT function function within another CONCAT function.
True - You can nest a concat function as shown here: select concat('Outer1 ', concat('Inner1', 'Inner2')) from dual;
What is "active undo"?
Undo data will always be kept until the transaction that generated it completes with a COMMIT or a ROLLBACK.
Returns those rows that contain a SALARY value that is less than or equal to 3000.
where salary <= 3000
What are the three components that form the basis of defining a function.
(1) The input parameter list - It specifies zero or more arguments that may be passed to a function as input for processing. (2) The data type of its resultant value - Upon execution, only one value of a predetermined data type is returned by the function. (3) Encapsulates the details of the processing performed by the function and contains the program code that optionally manipulates the input parameters, performs calculations and operations, and generates a return value.
What are the parameters that the LPAD and RPAD take?
(s, n, p) - s represents the source string, n represents the final length of the string returned and p specifies the character string to be used as padding. If LPAD is used the padding characters are added to the left of the source string s until reaches length n. Note that if the parameter n is smaller than or equal to the length of the source string s, then no padding occurs and only the first n characters of s are returned.
BLOB
A binary large object Variable-length binary value with a maximum size of 16 MB.
NVARCHAR2
A data type, which specifies a variable-length character string in the national character set. You specify the national character set as either AL16UTF16 or UTF8 when you create your database. AL16UTF16 and UTF8 are two encoding forms of the Unicode character set (UTF-16 and CESU-8, correspondingly). Independently of the maximum column length in characters, the absolute maximum length of any value that can be stored into this data type's column is 32767 or 4000 bytes, depending on MAX_STRING_SIZE.
Trigger
A database object that is attached to a table that is called when a row is inserted, updated, or deleted. These objects contain PL/SQL code and allow you to check that changes are correct or add in any data that needs to be there but is not specified by the SQL statement.
Data Dictionary
A file or a set of files that contains a database's metadata. This item contains records about other objects in the database, such as data ownership, data relationships to other objects, and other data.
Attribute
A piece of information that describes an entity in some fashion. These are mapped to columns when the design model moves to the physical model.
UID
A pseudocolumn that holds a numeric value for the currently logged-in user.
NLS_SESSION_PARAMETERS
A publicly available view that contains the NLS parameters for your current session.
RBO
A query optimizer that uses different rules to define the best query execution plan. However, it is an old method of optimizing queries. The Cost-Based Optimizer should be used instead.
ASCII
A single row character function that returns the decimal representation in the database character set of the first character of char.
LENGTH
A single row character function that returns the length of char.
NEXT_DAY
A single row datetime function that returns the supplied date plus integer months.
ABS
A single row numeric function that returns the absolute value of n.
Substitution Variable
A vehicle in Oracle that allows for you to build scripts that are not 100% fixed to run the same way every time they are executed. By replacing portions of one or more parts of a SQL statement with ampersands, you can cause the tool to replace those elements of the SQL with data that will be supplied at run-time.
V$NLS_PARAMETER
A view that contains current values of NLS parameters.
V$DATAFILE
A view that contains datafile information from the control file.
DICT_COLUMNS
A view that contains descriptions of columns in data dictionary tables and views.
V$VERSION
A view that displays version numbers of core library components in the Oracle Database. There is one row for each component.
What is an alias?
An alternative name for a column or an expression. They are especially useful with expressions or calculations and may be implemented in several ways.
Synonym
An alternative name for an object. If synonyms exist for objects, then any SQL statement can address the object either by its actual name or by it synonym.
Synonym
An alternative name for an object. If this exists for objects, then any SQL statement can address the object either by it's actual name or by its synonym.
V$DATABASE
An object that displays information about the database from the control file.
Exception
An object that is created, either by Oracle or by a developer, when something goes wrong in PL/SQL code. They can be created, or raised, in functions, procedures, packages, or anonymous blocks.
GLOBAL_NAME
An object that specifies whether a database link is required to have the same name as the database to which it connects. If the value of this object is false, then no check is performed. If you use or plan to use distributed processing, then Oracle recommends that you set this parameter to true to ensure the use of consistent naming conventions for databases and links in a networked environment.
SID
An object that uniquely identifies an Oracle database instance on a system. It can also identify a unique session in the session database views.
CTAS
An operation that uses statements to create a NEW table by using an existing table. By so doing it forms the basis for the structure (and possibly the contents) of the new table. This includes inheriting NOT NULL constraints from the parent table, but not other constraints.
IS NULL
An operator that is designed to evaluate to TRUE for values that are NULL.
Unary Operator
An operator that operates on only one operand.
Variations of the COUNT function.
COUNT(*) COUNT(DISTINCT expr) COUNT(ALL expr) COUNT(expr)
Complex Views
Can join detail tables, use functions, and perform aggregations.
Rapid Elasticity
Computing resources can be rapidly provisioned, increased, or decreased to meet changing user demand
ENABLE VALIDATE
It is possible to enter rows that will violate the constraint, and all rows in the table conform to the constraint.
Normalization
The process of organizing the logical structure of a database in order to facilitate both ad-hoc and data updates.
True or False? Group functions may, be used as part of the ORDER BY clause.
True
ORA-00937: Not a single-group group function
It is common to see the grouping attribute in the SELECT list alongside grouping functions. If an item that is not a group function appears in the SELECT list and there is no GROUP BY clause, this error is raised.
select to_date('25-DEC') from dual;
Cannot implicitly convert the string into a date because there is insufficient information and an ORA-01840: input value is not long enough for the date format; an error is returned.
Functions can operate on any data type: the most common are ____________________________.
Character, data, and numeric data. These operands may be columns or expressions.
INTEGER
Equivalent to to NUMBER, with scale zero.
select to_date('25-DEC-2010') from dual;
Evaluates the string 25-DEC-2010 and has sufficient information to convert it implicitly into a DATE item with a default mask of DD-MON-YYYY. The hyphen separator could be substituted with another punctuation character. Since no time components are provided, the time for this converted date is set to midnight, or 00:00:00.
NVL
Evaluates whether a column or expression of any data type is null. If the term is null, it returns an alternative not-null value; otherwise, the original term is returned.
Ampersand Substitution
Every element of the SELECT Statement may be substituted, and the reduction of queries to their core elements to facilitate reuse can save you hours of tedious and repetitive work.
MAX(expr) MAX(ALL expr) MAX(DISTINCT expr)
Examine the values for expr in a group of rows and return the largest value. Nulls are ignored.
ADR
It is a hierarchical file-based repository for handling diagnostic information. It was introduced in Oracle 11g to help DBAs with resolving problems on their databases.
What does the public in public synonym mean?
It is not a schema object and cannot therefore be prefixed with a schema name. I does not mean that everyone has permissions against it.
Why is it rare for Cycle to be used?
It is rare for cycle to be used because it lets the sequence issue duplicate values. If the sequence is being used to generate primary key values, CYCLE makes sense only if there is a routine in the database that will delete old rows faster than the sequence will reissue numbers.
Pipelined
PL/SQL functions that return data as (more or less) regular tables that can be accessed with regular SQL. This feature makes it possible to fetch the first records while the function is still running.
select to_date('25-DEC-2010 18:03:45', 'DD-MON-YYYY HH24:MI:SS') from dual;
Performs a complete conversion of a string with all the date time elements present and no default values are supplied by Oracle.
The ROUND function
Performs a rounding operation on a numeric value based on the decimal precision specified. The value returned is rounded either up or down, depending on the numeric value of the significant digit at the specified decimal precision position. If the specified decimal precision is n, the digit significant to the rounding is found (n+1) places to the RIGHT of the decimal point. If it is negative, the digit significant to the rounding is found n places to the LEFT of the decimal point. If the numeric value of the significant digit is greater than or equal to 5, a "round up" occurs; otherwise, a "round down" occurs.
Date TRUNC function
Performs a truncation operation on a date value based on a specified date precision format.
The implementation of a ______________________is in effect the union of a unique constraint and a not-null constraint. The key columns must have unique values, and they may not be null.
Primary key constraint
NOT
This boolean operator negates conditional operators. A selected row must conform to the logical opposite of the condition in order to be included in the result set.
CONSTRAINTconstraintname
This can be used to name the WITH CHECK OPTION and WITH READ ONLY restriction so that error messages when the restrictions cause statements to fail will be more comprehensible.
CONSTRAINTconstraintname
This can be used to name the WITH CHECK OPTION and WITH READ ONLY restrictions so that error messages when the restrictions cause statements to fail will be more comprehensible.
OFFSET
This clause specifies the number of rows to skip to get to the start position in a results set. It defaults to 0 in the absence of an OFFSET clause or if a NULL, negative, or offset that is larger than the total rows in a results set is provided.
HAVING
This clause was added to SQL because the WHERE keyword could not be used with aggregate functions.
SQL WITH
This clause was introduced in Oracle 12c, which allows you to be able to define a PL/SQL function. When this is done, you can call the function within a SQL statement
Transitive Dependency
This concept means that one attribute determines a second attribute indirectly, as it involves a third attribute in the middle. For example, let's say we have a table with three columns: A, B, and C. A can be used to determine B. B can be used to determine C.
IN
This condition is used to help reduce the need to use multiple OR conditions in a SELECT, INSERT, UPDATE, or DELETE statement.
CYCLE
This controls the behavior on reaching MAXVALUE or MINVALUE. The default behavior is to give an error, but if CYCLE is specified, the sequence will return to its starting point and repeat.
CHAR
This data is stored in strings with byte values corresponding to one of the character sets, such as 7-bit ASCII or EBCDIC, specified when the database was created. Oracle Database supports both single-byte and multibyte character sets.
LONG
This data type is used to store character set data. It is only used now for backward compatibility.
LONG RAW
This data type is used to store data in binary format. It accepts media objects, images, etc. It is only used for backward compatibility.
NCLOB
This datatype is similar to a CLOB datatype, but characters are stored in a NLS/Unicode or multibyte national character set.
VARCHAR2
This datatype is used to store variable length character strings. The string value's length will be stored on disk with the value itself. It's size depends on how many bytes you are actually going to store in the database. The number you specify is just the maximum number of bytes that can be stored (although 1 byte is minimum). You should use this datatype when you don't know the exact length of stored strings.
Data Model
This defines how data is related to other data. It can be represented as entities and attributes in an Entity Relationship Diagram.
Consider employee number 176 on the ninth row of output. This employee started as a sales manager on January 1, 2007 and ended employment on December 31, 2007. Therefore this employee worked exactly how long?
This employee worked for exactly one year, which in 2007, consisted of 365 days.
Functional Dependency
This exists when the value of one thing is fully determined by another. For example, given the relation EMP(empNo, empName, sal), attribute empName and empNo.
G1(G2(G3(group_item))) is NOT allowed.
This form is disallowed by ORACLE. If the MAX function is applied to the previous example, the expression MAX(SUM(AVG(group_item))) is formed. The two inner group functions return a single value representing the sum of a set of average values. This expression becomes MAX(single value), which is not sensible since a group function cannot be applied to a single value.
True or False? Further, OR conditions may be specified by separating them with an OR operator. The more OR conditions you specify, the less restrictive your query becomes.
True
True or False? It is possible to bypass the need to specify a value by including a DEFAULT clause on the column when creating the table.
True
True or False? Positional sorting applies only to columns in the SELECT list.
True
True or False? Single-row functions may be nested to any level, but group functions may be nested to, at most, two levels deep.
True
True or False? The HAVING clause can be specified only when a GROUP BY clause is present. A GROUP BY clause can be specified without a HAVING clause.
True
True or False? The NVL function is invaluable for converting null numeric values to zero so that arithmetic on them doesn't return null.
True
True or False? The date functions provide a convenient way to solve date-related problems without needing to keep track of leap years or the number of days in particular months.
True
True or False? The first form of the ORDER BY clause shows that results of a query may be sorted by one or more columns or expressions.
True
True or False? The result column is obtained by evaluating the TO_CHAR function using the date 24-SEP-1000 BC, with the format mask from the format element column in the table. 8-6
True
True or false? The character concatenation operators ignore null, while the arithmetic operations involving null values always result in null.
True
True or False? NUMBER and DATE items can be converted explicitly into character items using the TO_CHAR function.
True - A character string can be explicitly changed into a NUMBER using the TO_NUMBER function. The TO_DATE function is used to convert character strings into DATE items. Oracle's format masks enable a wide range of control over character-to-number and character-to-date conversions.
True or False? To enforce operator precedence of the subtraction operation, the subexpression end_date - is enclosed in round brackets.
True - Adding 1 makes the result inclusive of the first day.
True or False? Most of the group functions return numeric results.
True - Additionally, the MIN and MAX functions may return character and date results.
TRUE or FALSE? Ascending sort order is natural for most types of data and is therefore the default sort order used whenever the ORDER BY clause is specified.
True - An ascending sort order for numbers is lowest to highest, while it is earliest to latest for dates and alphabetically for characters.
True or False? Date arithmetic using the addition and subtraction operators is supported.
True - An expression like END_DATE - START_DATE returns the number of days between START_DATE and END_DATE. START_DATE + 30 returns a date 30 days later than START_DATE.
True or False? Views share the same namespace as tables.
True - Anywhere that a table name can be used, a view name is also syntactically correct.
True or False? Unless specified at creation time in the datafile clause, the datafiles of an undo tablespace will not be set to autoextend.
True - But if your database is created wit the Database Configuration Assistant (DBCA), it will enable automatic extension for the undo tablespace's datafile with maximum size unlimited. Automatic extension can be enabled or disabled at anytime, like it can be for any datafile.
True or False? SQL Developer does not require a statement terminator if only a single statement is present.
True - But it will not object if one is used. It is good practice to always terminate your SQL statements with a semicolon.
True or False? These functions operate on non-null values.
True - But unlike the others, the COUNT function call also counts rows with null values under certain conditions.
True or False? A major feature of undo segments is that they are managed automatically.
True - But you must set the limits within which Oracle will do its management. After considering the nature and volume of activity in your database, you set certain instance parameters and adjust the size of your undo tablespace in order to achieve your objectives.
True or False? Although implicit data type conversions are available, it is more reliable to convert values explicitly from one data type to another using single-row conversion functions.
True - Converting character information to NUMBER and DATE relies on format masks.
True or False? The column that the foreign key is based on is nullable.
True - Data model design sometimes leads to problematic situations when tables are related to each other via a primary and foreign key relationship.
True or False? DATE columns are useful for storing date and time information.
True - Date literals must be enclosed in single quotation marks just like character data.
True or False? The NEXTVAL will be globally unique.
True - Each session that selects it will get a different, incremented value for each SELECT. The CURRVAL will be constant for one session until it selects NEXTVAL again.
True or False? Private Synonyms are schema objects.
True - Either they must be in your own schema or they must be qualified with the schema name.
True or False?The data types of the ifnotnull and ifnull parameters must be compatible, and they cannot be of type LONG.
True - Either they must be of the same type or it must be possible to convert ifnull to the type of the ifnotnull paramters.
True or False? It is not possible to drop or truncate the parent table in a foreign key relationship.
True - Even if there are no rows in the child table. This still applies if the ON DELETE SET NULL or ON DELETE CASCADE clause was used.
True or False? Undo data is generated by DML against temporary tables.
True - This is necessary because the usual transactional rules apply.
True or False - It is possible to insert many rows with NULLs in a column with a unique constraint.
True - This is not possible for a column with a primary key constraint.
True or False? The sequence mechanism is independent of tables, the row locking mechanism, and commit or rollback processing.
True - This means that a sequence can issue thousands of unique values a minute -- far faster than any method involving selecting a column from a table, updating it, and committing the change.
True or False? To update a set of rows, use a less restrictive WHERE clause than the primary key.
True - To update every row in a table, do not use any WHERE clause at all.
True or False? Double ampersand substitution is used to avoid repetitive input when the same variable occurs multiple times in a statement.
True - When a double ampersand substitution occurs, the variable is stored as a session variable. As the statement executes, all further occurrences of the variable are automatically resolved using the stored session variable.
An index gives (near immediate access to key values, so the check for existence can be made virtually instantaneously. (True or False)?
True - When a primary key constraint is defined, Oracle will automatically create an index on the primary key columns, if one does not exist already.
True or False? Constraints can be defined when creating a table or added to the table later?
True - When defining constraints at table creation time, the constraint can be defined inline with the column to which it refers or at the end of the table definition. There is more flexibility when using the latter technique. For example it is impossible to define a foreign key that refers to two columns or a check constraint that refers to any column other than that being constrained if the constraint is defined inline, but both these are possible if the constraint is defined at the end of the table.
True or False? Just as a unique constraint permits null values in the constrained column, so does a foreign key constraint.
True - You can insert rows into the child table with null foreign key columns even if there is not a row in the parent table with a null value. This creates orphan rows and can cause dreadful confusion. As a general rule all the columns in a unique constraint and all the columns in a foreign key constraint are best defined with not-null constraints as well; this will often be a business requirement.
True or False? You can submit SQL statements to the database in any case.
True - You must pay careful attention to case when interaction with character literal data and aliases. Requesting a column called JOB_ID or job_id returns the same column, but asking for rows where the JOB_ID value is PRESIDENT is different from asking for rows where the value is President.
Foreign key constraints are enforced by indexes (True or False)?
True - but the index must exist on the parent table, not necessarily on the table for which the constraint is defined.
True or False? Blank spaces do not count as NULL.
True - since they are present in the row and have some length even though they are not visible.
What are the four parameters that control undo?
UNDO_MANAGEMENT, UNDO_TABLESPACE, UNDO_RETENTION TEMP_UNDO_ENABLED
Oracle's mechanism for assuring transactional integrity is the combination of __________________________________________.
Undo segments and redo log files
What does the ACID test require third?
Undo segments are also used for transaction isolation.
What is another constraint that requires an index?
Unique Constraint, It differs from a primary key constraint in that the columns of the unique constraint can be left null. This does not affect the creation and use of the index.
Right JOIN
Use of this keyword returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left
How do you create an undo tablespace?
Use the keyword UNDO: CREATE UNDO TABLESPACE tablespace name DATAFILE datafile_name SIZE size (M | G | T) (RETENTION NOGUARANTEE | GUARANTEE) By default the tablespace will not guarantee undo retention.
Nested Functions
Use the output from one function as the input to another. Functions always return exactly one result. Therefore, you can reliably consider a function call in the same way as you would a literal value when providing input parameters to a function.
The ______________________ environmental setting controls whether SQL *PLUS displays the old and new versions of statement lines that contain substitution variables.
VERIFY
VARCHAR2
Variable-length character data, from 1 byte to 4,000 bytes if MAX_STRING_SIZE=STANDARD and up to 32,767 bytes if MAX_STRING_SIZE=EXTENDED. The data stored in the database character set.
Partial Functional Dependency
When a nonprime attribute is functionally dependent on part of a candidate key.
What happens when there is a substitution variable?
When the statement is executed, the SQL *Plus client processes the statement, notices a substitution variable, and attempts to resolve this variable in one of two ways. (1) It checks whether the variable is defined in the user session. If the variable is not defined, the user process prompts for a value that will be substituted in place of the variable. Once a value is submitted, the statement is complete and is executed by the Oracle server.
Invisible Index
When this Column is defined, it will not appear in generic queries (select * from). It will need to be explicitly referred to in the SQL statement or condition.
Verify
When this function is turned on, the text that is returned on by SQL*Plus and SQL Developer that shows the old and new versions of a SQL statement that uses substitution variables.
Clustered Tables
When two tables are commonly queried together, the process of physically storing them together via a structure.
How are words that contain single quotation marks dealt with?
You can either add an additional single quotation mark next to each naturally occurring single quotation mark in the character string. select 'Plural ' 's have one quote too many ' from dual; or the q operator: SELECT q'<Plural's can also.......
If a public synonym has already been created, you can use this query to (any user).
select * from emp; This gives both data independence and location transparency.
The following query limits the rows retrieved from he JOB_HISTORY table by specifying a WHERE condition based on the DEPARTMENT_ID column values.
select department_id from job_history where department_id in (50, 60, 80, 110); This query returns seven rows. If the WHERE clause were absent, all ten rows would be retieved.
This query shows how the HAVING clause is used to restrict an aggregated dataset. Records for the JOB_HISTORY table are divided into four groups. The rows that meet the HAVING clause condition (contributing more than one row to the group row count) are returned:
select department_id, count(*) from job_history where department_id in (50, 60, 80, 110) group by department_id having count(*) > 1; three rows with DEPARTMENT_ID values of 50, 80, and 110, each with a COUNT(*) value of 2, 2 and 3, are returned respectively.
A DML statement requires ____________________ on the objects involved and _________________________ exclusive locks on the rows involved.
shared locks, exclusive locks
The underscore wildcard symbol substitutes exactly one other character in a literal. Consider searching for employees whose last names are fours letters long, begin with a K, have an unknown second letter, and end with an ng. You may issue the following statement:
where last_name like 'K_ng'';
Wildcard symbols are not mandatory when using the LIKE operator. In such cases, LIKE behaves as an equality operator testing for exact character matches; so the following two WHERE clauses are equivalent.
where last_name like 'King'; and where last_name = 'King';
Demonstrates one of the two forms of the 'not equal to' operators. It returns the rows that have SALARY column values that are not equal to the DEPARTMENT_ID values.
where salary <> department_id
The century component for the literal '01-JAN-99' becomes the previous century (19) and is converted to a date value of 01-JAN-1999 for the fourth condition since the RR component, 99, is greater than 50.
where start_date = '01-JAN-99'
LIKE is accompanied by which two wildcard characters?
% and the _(underscore) The percentage symbol is used to specify zero or more wildcard characters, while the underscore character specifies one wildcard character. A wildcard may represent any character.
NVL
???(expr1, expr2) A single row function that will use this term to replace NULL with a string. If expr1 is NULL, then this term returns expr2. If expr1 is NOT NULL, then this term will return expr1.
ONLY
A clause that will return the exact number of rows specified in the FETCH FIRST clause.
Multitenant
A new option for Oracle Database 12c Enterprise Edition that helps customers reduce IT costs by simplifying consolidation, provisioning, upgrades, and more. It is supported by a new architecture that allows a container database to hold many pluggable databases.
Function
A piece of PL/SQL code that is stored in the database and can be used by your SQL queries. They could be built-in (such as SUM) or user-defined that you create yourself. These items need to return a value and can be used in SQL queries.
NEXTVAL
A property of an Oracle sequence that increments the current sequence value and returns it. Often used for inserting primary key values into a table.
Single-Row Subquery
A query that returns one row as its result.
Oracle Fusion
A set of applications that help with enterprise resource planning. It is the replacement for EBS (Oracle E-Business Suite), and includes financials, supply chain management, HR, and many other areas.
INITCAP
A single row character function that returns the char, with the first letter of each word in uppercase, and all the other letters in lowercase.
MONTHS_BETWEEN
A single row datetime function that returns number of months between date1 and date2. If date1 is later than date2, then the result is positive. If date1 is earlier than date2, then the result is negative. If date1 and date2 are either the same days of the month or both last days of months, then the result is an integer.
ADD_MONTHS
A single row datetime function that returns you the same day, n number of months away. Note that the n can be positive or negative, so you can go backwards in time.
PUBLIC
A special user role, that is created by Oracle. When a privilege is granted to this role, it is available to every single database user.
Procedure
A stored piece of PL/SQL code that allows parameters to be provided and returned, and perform complex functionality.
Connection String
A string used to identify and hitch up to an Oracle database. They are specified in a TNSNAMES.ORA file. It includes the protocol, host, port, SID, service name, and database connection type.
Sequence
A structure for generating unique integer values. Only one session can read the next value and thus force it to increment. This is a point of serialization, so each value generated will be unique.
SCN
A unique number for every time a commit is made.
DICTIONARY
A view that contains descriptions of data dictionary tables and views.
USER_OBJECTS
A view that describes all objects owned by the current user. Its columns are the same as those in "ALL_OBJECTS".
View
A virtual table, which provides access to a subset of column from one or more table. It can derive its data from one or more table. An output of query can be stored as this item. In Oracle is nothing but a stored sql scripts. This item in itself contain no data.
Index Organized Tables
A way of organizing a table structure by sorting and then referencing one or more columns to allow for speedy retrieval and reducing storage requirements.
Foreign Key
A way to enforce referential integrity within your Oracle database. It means that values in one table must also appear in another table (usually as the Primary Key).
What are the cardinal arithmetic operators?
Addition, Subtraction, Multiplication and Division for numeric columns. The concatenation operator for character or string columns, and the addition and subtraction operators for data and timestamp columns.
select avg(2) from employees;
Adds the number 2 across 107 rows and divides the total by the number rows to return the number 2. Numeric literals submitted to the AVG function are returned unchanged.
select avg(salary) from employees;
Adds the salary value for each row to obtain the total salary amount of 721166, which is divided by the rows with non-null SALARY values (107) to return the average 6739.86916.
DISABLE VALIDATE
All data in the table conforms to the constraint, but new rows need not. The index is also dropped on the constraint.
COUNT(*)
All rows in the group, including those with nulls or duplicate values, are counted.
What happens if the undo tablespace is changed?
All the undo segments in the old undo tablespace will be taken offline and those in the new undo tablespace will be brought online. There are two exceptions to this: (1) In a Real Application Cluster (RAC) database (2) If the undo tablespace is changed by changing the UNDO_TABLESPACE parameter.
Variables can be assigned any __________________ name that is a valid identifier name.
Alphanumeric, The literal you substitute when prompted for a variable must be an appropriate data type for that context; otherwise, an "ORA-00904: invalid identifier" error is returned.
select mod('$11', 2) from dual;
Also fails with the invalid number error since the dollar symbol cannot be implicitly converted into a number.
Toad
An IDE for SQL developers, allowing developers to connect to an Oracle database, write and run queries, and much more.
Why does this query raise an error? SELECT end_date, start_date, count(*) FROM job_history GROUP BY end_date;
An ORA-00979 error is returned from this query, since the START_DATE item is listed in the SELECT clause, but it is not a grouping attribute.
Nested table
An Oracle data type that can support entire tables of values in a single column.
SQLCODE
An Oracle function that returns the number code for the most recent exception. It is often used along with the SQLERRM function.
Listener
An Oracle process for tuning for incoming database connections on the server. If this process is not running, then you cannot connect to the database.
Real Application Clusters (RAC)
An Oracle product that lets multiple nodes connect to the same database.
TKProf
An Oracle utility used to format the output of SQL Trace into something that's more readable.
Dynamic SQL
An SQL statement that is constructed and executed during run time. The opposite of this is a static SQL statement, which is written and known before the program is run.
PGA
An area in RAM that stores data for a single process.
With inequality-based conditions, how are range and pattern matching comparisons possible?
By using inequality and equality operators, but it is often preferable to use the BETWEEN and LIKE operators for these comparisons.
LONG
Character data in the database character set, up to 2GB minus 1. All the functionality of LONG (and more) is provided by CLOB: LONGs should not be used ina modern database, and if your databse has any columns of this datatype, they should be converted to CLOB. There can be ony one LONG column in a table.
CLOB
Character data stored in the database character set. The size is effectively unlimited: 4GB minus 1 multiplied by the database block size.
What are the character strings used for padding?
Character literals, column values, expressions, blank spaces (the default), tabs, and special characters.
MAX and MIN on character strings
Character strings are converted to numeric representations of their constituent characters based on the NLS settings in the database. When the MIN function is applied to a group of character strings, the word that appears first alphabetically is returned, while MAX returns the word that appears last.
What are the categories of single-row functions?
Character, numeric, date, conversion and general
Measured Service
Clients can monitor and measure transactions and use of resources
A transaction consists of one or more _________ statements.
DML - Followed by a rollback or commit. It is possible to use the SAVEPOINT command to give a degree of control within the transaction.
How do you drop a synonym?
DROP [PUBLIC] SYNONYM synonym; If the object to which a synonym refers (the table or view) is dropped, the synonym continues to exist. Any attempt to use it will return an error. If the object is re-created, the synonym must be recompiled before use.
The HR schema (and every other schema) has access to a special table called ___________, which belongs to the SYS schema.
DUAL, This table can be structurally described with the command describe sys.dual.
Data Manipulation Language
Data in a relational database is managed with _______________?
UNDO _MANAGEMENT
Defaults to AUTO, though it is possible to set it to MANUAL, meaning that Oracle will not use undo segments at all. This is for backwards compatibility, and if you use this, you will have to do a vast amount of work creating and turning rollback segments. It is advised to set this to AUTO.
Group
Defined as a subset of the entire dataset sharing one or more common attributes. These attributes are typically column values but can also be expressions. The number of groups created depends on the distinct values present in the common attribute.
select mod(5, 3) from dual;
Divides 5 by 3, yielding 1 with remainder 2.
select trunc(1601.916) from dual;
Does not have a decimal precision parameter, implying that truncation is done at the whole-number degree of precision. The number returned is 1601.
select replace('1#3#5#7#9#','#') from dual;
Does not specify a replacement string. The default behavior is therefore to replace the search string with an empty string; this, in effect, removes the search character completely from the source, resulting in the string 13579 being returned.
Simple views
Draws data from one detail table, uses no functions, and does no aggregation.
The alter table commands are virtually instantaneous , with the exception of which one?
Dropping a column. Dropping a column can be time-consuming exercise because as each column is dropped, every row must be restructured to remove the column's data.
Boolean or logical operators
Enable multiple conditions to be specified in the WHERE clause of the SELECT statement.
select coalesce(substr('abc', 4), 'Not bc' , 'No substring') from dual;
Evaluates its first parameter, which is a nested SUBSTR function, and finds it to be null. The second parameter is not null, so the string 'Not bc' is returned.
select to_char(00001)||' is a special number' from dual;
Evaluates the number 00001, removes the leading zeros, converts the number 1 into the character '1', and returns the character string '1 is a special number'.
What does the ACID test require second?
For consistency, the database must be able to present a query with a version of the database as it was at the time the query started.
select mod(5.2, 3) from dual;
Has a decimal fraction as the dividend. Dividing 5.2 by 3 yields 1 with remainder 2.2.
Temporary Table
Has a definition that is visible to all sessions, but the rows within it are private to the session that inserted them.
select round(1601.916) from dual;
Has dispensed with the decimal precision parameter. This implies that rounding is done to the nearest whole number. Since the tenth unit is 9, the number is rounded up, and 1602 is returned.
ORA-00934: group function is not allowed here
If a group function is placed in a WHERE clause, this error is returned. Imposing group-level conditions is achieved using the HAVING clause.
What happens when a literal occurs in an expression?
If a literal occurs in an expression involving a DATE column, it is automatically converted into a date value using the default format mask. DD represents days, MON represents the first three letters of a month, and RR represents a Year 2000-compliant year (that is, if RR is between 50 and 99, then Oracle server returns the previous century, or else it returns the current century). The full four-digit year, YYYY, can also be specified.
3rd Normal Form
If one or more fields in a table do not depend at all on the primary key for that table (or any part of it), move them to a separate table along with copies of the fields on which they do depend.
select to_char(sysdate) ||' is today's date' from dual;
If the current system date is 03/JAN/09 and the default display format is DD/MON/RR, then query 1 returns the character string '03/JAN/09 today's date'.
The UPPER Function
Is the logical opposite of the LOWER function and replaces the lowercase characters in a given string with their uppercase equivalents.
Locally Managed Tablespaces
In this form of physical storage management, Oracle maintains a bitmap in each datafile, to track space availability.
select nvl(substr('abc', 4), 'No substring exists') from dual;
Involves a nested SUBSTR function that attempts to extract the fourth character from a three-character string that returns null, leaving the outer function NVL(null, 'No substring exists') to execute, which then returns the string 'No substring exists'.
The LIKE operator
Is designed exclusively for character data and provides a powerful mechanism for searching for letters or words.
Cross-join or Cartesian product
Is formed when every row from one table is joined to all rows in another. This join is often the result of missing or inadequate join conditions but is occasionally intentional.
What is an important difference between the HAVING clause and the other SELECT statement clauses?
It may be specified only if a GROUP BY clause is present. This dependency is sensible since group-level rows must exist before they can be restricted.
BI
It refers to the area of study and work that uses technology and tools to view a company's data in more meaningful ways, which allows people at a company to make better decisions. Its technologies include development practices to change the data into a more usable format for reporting (related to ETL or data warehousing), and tools to view this data (Cognos, Business Objects, etc).
The CONCAT Function
Joins two character literals, columns, or expressions to yield one larger character expression.
How many parameters does NVL take?
NVL takes two mandatory parameters; its syntax is NVL(original, ifnull), where original represents the term being tested and ifnull is the result returned if the original term evaluates to null. The datatypes of the original and ifnull parameters must always be compatible. Either they must be of the same type or it must be possible to implicitly convert ifnull to the type of the original parameter. The NVL function returns a value with the same data thype as the original parameter.
NULLIF
Tests two terms for equality. If they are equal, the function returns a null, or else it returns the first of the two terms tested.
V$UNDOSTAT
Often referred to as the undo advisor because it lets you predict how long undo data can be kept for a given workload.
select to_char(sysdate, 'Month')||' is a special time' from dual;
Only the month component of the current system date is extracted for conversion to a character type. Second, since the format mask is case sensitive and 'Month' appears in title case, the string returned is 'January is a special time'. There is no need to add a space in front of the literal 'is a special time', since the TO_CHAR function automatically pads the name of the month with a space. If the format mask in this query was 'MONTH', the string returned would be 'JANUARY is a special time'.
Logical changes that are made to the table after creation are made by whom?
SQL developers
select instr('1#3#5#7#9#', '#', 3, 4) from dual;
Searches for the hash from position 3 and finds the fourth occurrence at position 10.
Isolation
States that an incomplete (that is, uncommitted) transaction must be invisible to the rest of the world. While the transaction is in progress, only the one session that is executing the transaction is allowed to see the changes.
Durability
States that once a transaction completes, it must be impossible for the database to lose it.
Consistency
States that the results of a query must be consistent with the state of the database at the time the query started. ex... The principle of consistency requires that the database ensure that changed values are not seen by the query; it will give you an average of the column as it was when the query started, no matter how long the query takes or what other activity is occurring on the tables concerned.
IN operator
Tests whether an item is a member of a set of literal values.The set is specified by comma-separating the literals and enclosing them in round brackets. If the literals are character or date values, then these must be delimited using single quotes. You may include as many literals in the set as you want.
EXISTS
The condition is used in combination with a subquery and is considered "to be met" if the subquery returns at least one row. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.
What is CURRVAL?
The last value issued to the current session, not necessarily the last value issued. You cannot select the CURRVAL until after selecting the NEXTVAL.
Granularity
The level of depth represented by the data in a fact or dimension table. When it is High this means a minute, sometimes atomic grade of detail, often at the level of the transaction. When it is Low, it zooms out into a summary view of data and transactions.
Materialized View
These are disk based (making them updateable) and are updated periodically based upon the query definition.
Pseudocolumn
These are not actually stored on the database. They can be referenced by SELECT statements, but cannot be inserted, updated or deleted. The two of these that exist for all SQL statements are ROWNUM and ROWID.
Double Quotes
These are used to enclose identifiers (like table and column names, column aliases, table aliases, etc.) .
Long and Raw Datatypes
These datatypes have been deprecated in favor of LOBs
What does the LPAD and RPAD functions do?
These functions return a string padded with a specified number of characters to the left or right of a given string, respectively.
FIRST and NEXT
These keywords also serve to clarify semantics but have identical meaning. Fetching the FIRST 2 rows from OFFSET 100 in a dataset retrieves the same two rows as fetching the NEXT 2 rows from OFFSET 100.
What could errors ORA-00923 - FROM keyword not found where expected and ORA-00942: table or view does not exist mean.
These often indicate spelling or punctuation errors, such as missing enclosing quotes around character literals.
What is a significant differentiator between numeric and other functions?
They accept and return only numeric data.
Ambiguous
This aspect usually occurs when you're trying to SELECT a column that has the same name in two different tables, and Oracle doesn't know what column to display.
JOIN
This clause combines columns from one or more tables in a relational database. It creates a set that can be saved as a table or used as it is.
insert into hr.regions (region_id) values (12);
This example lists on column and therefore only one value. All other columns will be left null. This statement will fail if the REGION_NAME column is not nullable. Some value (even a NULL) must be provided for each column.
Multitable Insert
This feature allows the INSERT . . . SELECT statement to use a number of tables as targets. In addition, it can distribute data among target tables based on logical attributes of the new rows. Using this DML operative thus enables a single scan and transformation of source data to insert data into multiple tables, sharply increasing performance.
ROWNUM
This function returns a number that represents the order that a row is selected by Oracle from a table or joined tables. The first row has a value of 1, the second has a value of 2, and so on.
SYSDATE
This function returns the current date and time of the SERVER on which the Oracle database is running.
CURRENT_DATE
This function returns the current date in the time zone of the DATABASE SESSION where it is called from.
LAST_DAY
This function returns the date of the last day in the month to which the given day belongs.
COALESCE
This function returns the first not-null value from its parameter list. If all its parameters are null, then null is returned.
STDDEV
This function returns the standard deviation of a set of numbers. This function can be used two ways - as an Aggregate function or as an Analytic function.
How many parameters does the CONCAT function take?
This functions takes two parameters. Its syntax is CONCAT(s1, s2), where s1 and s2 represent string literals, character column values, or expressions resulting in character values.
Reverse Key Indexes
This index resource is useful, when there are I/O contention issues during the inserts of sequential values.
How do you execute a double ampersand substitution?
This involves prefixing the first occurrence of the substitution variable that occurs multiple times in a query with two ampersand symbols instead of one. When Oracle encounters a double ampersand substitution variable, a session value is defined for that variable, and you are not prompted to enter a value to be substituted for this variable in subsequent references.
BINARY_FLOAT
This is a 32-bit floating-point number It is a single-precision native floating-point type that supports +Inf, -Inf, and NaN values. It is an approximate numeric value consisting of an exponent and mantissa. You can use exponential or E-notation. It has binary precision 24.
Format Model
This is a character literal that describes the format of DATETIME or NUMBER data stored in a character string. When you convert a character string into a datetime or number, this is what tells Oracle how to interpret the string.
WITH Clause
This provision enables you to reuse the same query block in a SELECT statement when it occurs more than once within a complex query.
MINVALUE
This is the lowest number a descending sequence can go before generating an error or returning to its START WITH value. The default is no minimum.
Privilege
This is the right to perform a particular action on an object or to access another user's object.
Projection
This means choosing which columns (or expressions) a query shall return. A unary operation written as where is a set of attribute names. The result of such projection is defined as the set obtained when the components of the tuple are restricted to the set - it discards (or excludes) the other attributes.
Unique Index
This object is automatically created when you define a column in a table to have a PRIMARY Key or a UNIQUE constraint. It is named identically to the name given to the constraint.
Nonunique Index
This object is created by a user.
LOB
This object replaces the LONG and LONG RAW data types from older Oracle versions.
UNDO_TABLESPACE
This parameter nominates a tablespace, which must have been created as an undo tablespace, as the active undo tablespace. All the undo segments within it will be brought online (that is, made available for use) automatically.
WITH READ ONLY
This prevents any DML through the view.
System Privileges
This provides the ability to perform a particular action within the database system, or to perform an action on any schema objects of a particular type.
Bitmap Indexes
This resource is good for columns with few unique values. It should only be used when the data is batch loaded and is rarely updated or deleted.
NOT EXISTS
This returns one or more rows. Since it returns multiple values, the query must use the set comparison operators (IN,ALL,ANY).
INCREMENT BY
This sets how much higher (or lower) than the last number a number will be. This defaults to +1 but can be any positive number (or negative number for a descending sequence).
&&
This signals to reuse this substitution variable, relieving a user from having to re-enter it, when prompted at the dialog box that comes with usage of a sustitution variable.
TRUNCATE (Numbers)
This single row numerical function is similar to the ROUND function, it differs though in that it removes all numbers to the right of the defined precision.
Why are views used?
To enforce security
Types of Constraints
UNIQUE, NOT NULL, PRIMARY KEY, FOREIGN KEY, CHECK, REF
Alterations can be made to a table after creation. (True or False)
True
True or False? A table has at least one column and zero or more rows of data.
True
True or False? Any SELECT statement, specified as a subquery, can be used as the source of rows passed to an INSERT. This enables insertion of many rows. Alternatively, using the VALUES clause will insert one row. The values can be literals or prompted for a substitution variables.
True
True or False? Any item in the SELECT list that is not a group function must be a grouping attribute of the GROUP BY clause.
True
True or False? Conditional logic, also know as if-then-else logic, refers to choosing a path of execution based on data values meeting certain conditions.
True
True or False? Foreign keys in an entity are based on unique keys in a related entity, but those unique keys do not have to be the primary key; they just have to be unique.
True
True or False? Functions may be nested within other functions, such as F1(x, y, F2(a,b), z), where F2 takes two input parameters, a and b, and forms the third of four parameters submitted to F1.
True
True or False? It is not possible to execute INSERT, UPDATE, or DELETE commands against a complex view.
True - The mapping of the rows I the view back to the rows in the detail tables cannot always be established on a one-to-one basis, which is necessary for DML operations.
True or False? It is not possible to execute INSERT, UPDATE or DELETE commands against a complex view.
True - The mapping of the rows in the view back to the rows in the detail tables cannot always be established on a one-to-one basis, which is necessary for DML operations.
True or False? If you are describing a table that belongs to a schema to which you have connected, you can omit the SCHEMA portion of the command.
True! The HR notational prefix could be omitted since the DEPARTMENTS table belongs to the HR schema.
How many parameters does the NEXT_DAY function return?
Two mandatory parameters and has the syntax NEXT_DAY(start date, day of the week). The function computes the date on which the day of the week parameter next occurs after the start date.
How many parameters does the ADD_MONTHS function take?
Two mandatory parameters. Start date and number of months.
How many parameters does the MOD function take?
Two parameters. It's syntax is MOD(dividend, divisor). The dividend and divisor parameters represent a numeric literal, column, or expression, which may be negative or positive.
How many parameters does TRUNC take?
Two parameters. TRUNC(source number, decimal precision). The source number represents any numeric value. The decimal precision specifies the degree of truncation and is optional. If the decimal precision parameter is absent, the default decimal precision is zero, which means the source number is truncated to an integer value.
How many parameters does the ROUND function take?
Two parameters. The source number parameter represents any numeric value. The decimal precision parameter specifies the degree of rounding and is optional. If the decimal precision parameter is absent, the default degree of rounding is zero, which means the source is rounded to the nearest whole number.
The __________________ command may be used to unset both implicitly (double ampersand substitution) and explicitly defined session variables.
UNDEFINED
Undo operates at the logical level, whereas redo operates at the physical level.
Undo data is row oriented; changes are grouped according to the transaction that made them, and within an undo segment all the entries are related to rows of the same transaction.
Undo is transient, whereas redo is permanent.
Undo data persists for atleast the duration of the transaction that generates it, but possibly longer. Eventually, it will always be overwritten. Redo persists indefinitely, first in the online log files and then in the archive log files.
Undo can reverse changes, whereas redo can repeat changes.
Undo data provides the ability to reverse committed transactions. Provides the ability to reverse committed transactions. Redo provides the ability to replay work that has been lost.
What is "unexpired undo"?
Undo data will be retained for a period after it becomes inactive to satisfy any read consistency requirements of long-running queries.
DISTINCT
Using the DISTINCT keyword allows duplicate rows to be eliminated from the results set.
ORDER BY
Usually the last clause in a SELECT statement, the format of this clause in the context of the SQL SELECT statement is as follows: SELECT * {DISTINCT} column | expression {alias} FROM table [WHERE condition] [ORDER BY {col (s).......
Double ampersand substitution
When a substitution variable is referenced multiple times in the same query and your intention is that the variable must have the same value at each occurrence in the statement.
Complex JOIN
When you have another condition to limit the rows retrieved, beyond the join condition in the WHERE clause
UROWID
When you select or fetch a physical rowid into a this datatype variable, you can use the function ROWIDTOCHAR, which converts the binary value to an 18-byte character string. Then, you can compare the datatype variable to the ROWID pseudocolumn in the WHERE clause of an UPDATE or DELETE statement to identify the latest row fetched from a cursor.
What happens if the SELECT statement is saved as a view?
Whenever the users query or update rows in the view (under the impression that it is a table), the statement runs, and the result is presented to users as though it were a table. The SELECT statement on which a view is based can be anything. It can join tables, perform aggregations, or do sorts; absolutely any legal SELECT command can be used as the basis for a view.
If you want to reset the sequence to its starting value, the only way is to drop it and create it again.
drop sequence order seq;
select distinct job_id from job_history;
Will return the eight distinct job types. An important feature of this keyword is the elimination of duplicate values from combinations of columns.
By default, constraints are ______________ and ___________________, and they are not deferrable.
enabled, validated
How do you adjust the cache value from the default to improve the performance in a sequence.
alter sequence order_seq cache 1000;
Modifying columns
alter table emp modify (comm number (4,2) default 0.05);
marking the table as read-only:
alter table emp read only;
Renaming columns
alter table emp rename column hiredate to recruited;
Marking columns as unused
alter table emp set unused column job_id;
What do the date precision formats include?
century (CC), year (YYYY), quarter (Q), month (MM), week (W), day (DD), hour (HH), and minute (MI)
A typical use of sequences is for primary key values. This example uses a sequence CUST_SEQ to generate unique customer number, ORDER_SEQ to generate unique order numbers, and LINE_SEQ to generate unique line numbers for the line items of the order.
create sequence order_seq start with 10; create sequence line_seq start with 10; Then insert the orders with their lines as a single transaction.
Create a view that cannot possibly see "deleted" staff members.
create view current_staff as select * from emp where active='Y';
Finance staff will need to be able to see the costing information with a view. This view will depersonalize the data.
create view hr.emp_fin as select hire_date, job_id, salary, commission_pct, department_id from hr. employees;
The ____________________________will be monitoring the execution of code running in the database and will make recommendations for indexes.
database administrator
Always remember that the server processes read blocks from ___________________ into the _________________________, DBWn writes blocks from the database buffer cache to the datafiles.
datafiles, database buffer cache
Locks are ________________ for all DML commands and are _________________ for SELECT
required, optional
SUM
returns the aggregated total of the non-null numeric values in a group.
What is the default level of locking?
row level
This query extracts the rows from the COUNTRIES table where the COUNTRY_NAME values contain the letters U, S, and A (in any case) in that order.
select * from countries where upper(country_name) like '%U%S%A%';
Finance staff be given permission to see the view but not the table and can issue statements such as this:
select * from emp_fin where department_id=50;
A query to retrieve the database server date.
select sysdate from dual;
Full Functional Dependency
state of database normalization that equates to the normalization standard of Second Normal Form (2NF).
Concatenates the string literal A to the LAST_NAME and FIRST_NAME columns.
where 'A' || last_name || first_name= 'A King"
Illustrates that literal expressions may also be placed on the left of the conditional operator.
where 'SA_REP' || 'King' = job_id || last_name This is identical to: where job_id || last_name = 'SA_REP' || 'King'
Examples of the NOT operator
where first_name LIKE 'R%' where first_name NOT LIKE 'R%' where salary BETWEEN 1 and 3000 where salary NOT BETWEEN 1 and 3000
The wildcard characters can appear alone.
where first_name like '%'; In this case, every row containing a FIRST_NAME value that is not null will be returned.
Demonstrates that character expressions may be placed on both sides of the conditional operator.
where first_name ||' '|| last_name = last_name || ' ' || first_name
How do you resolve lock contention?
Either by terminating blocking transactions or by killing the blocking session.
NLS_DATABASE_PARAMETERS
A view that lists permanent NLS parameters of the database.
USER_SYS_PRIVS
A view that lists system privileges granted to the current user. Its columns are the same as those in "DBA_SYS_PRIVS".
Why is the performance on temporary tables usually superior to the performance of permanent tables?
(1) The I/O is direct. The session reads an writes its temporary segment without going through the buffer cache and without involving the database writer. (2) No redo is generated on global temporary tables. There would be no reason. The purpose of redo is to make changes persistent, and global temporary tables do not store persistent data.
What are the principles of error conditions Related to Undo?
(1) There should always be sufficient undo space available to allow all transactions to continue. (2) There should always be sufficient undo data available for all queries to succeed.
Supposed you wanted to restrict this list to only those departments with more than one employee. The HAVING clause limits or restricts the group-level rows as required.
1. Consider the entire row-level dataset. 2. Limit the dataset by any WHERE clause conditions. 3. Segment the data into one or more groups using the grouping attributes specified in the GROUP BY clause. 4. Apply any aggregate functions to create a new group-level dataset. Each row may be regarded as an aggregation of its source row-level data based on the groups created. 5. Limit or restrict the group-level data with a HAVING clause condition. Only group-level results matching these conditions are returned.
What are the two functions of indexes?
1. To enforce primary key and unique constraints. 2. To improve performance.
NULLIF
???(expr1, expr2) A single row function where IF expr1 and expr2 are equal, then ??? returns NULL. If they are not equal, then it returns expr1. You cannot specify the literal NULL for expr1.
Oracle Exadata Database Machine
A "cloud in a box" composed of database servers, Oracle Exadata Storage Servers, an InfiniBand fabric for storage networking and all the other components required to host an Oracle Database. It delivers outstanding I/O and SQL processing performance for online transaction processing (OLTP), data warehousing (DW) and consolidation of mixed workloads
Temporary Tables
A GLOBAL table in which multiple users can insert rows.
Natural JOIN
A JOIN operation that creates an implicit join clause for you based on the common columns in the two tables being joined. Common columns are columns that have the same name in both tables.
Binary Integer
A PL/SQL data type used for storing signed integers. It can store values from between -2^31 and 2^31, which is values between -2,147,483,648 and 2,147,483,647 (about 2 billion).
Fetch
A PL/SQL statement that is used to load data from a cursor.
WITH TIES
A clause that will return at most 5 rows, except if the 5th row and the 6th row (and so on) are "tied" according to the ORDER BY clause, meaning that the ORDER BY clause does not deterministically produce a 5th or 6th row.
IaaS
A cloud computing infrastructure which refers to computing hardware (servers, storage and network) delivered as a service. This typically includes the associated software as well as the operating systems, virtualization,clustering, and so on.
Schema
A collection of objects such as tables, views, and sequences. It is owned by a database user and has the same name as the user.
Package
A collection of related PL/SQL code (functions and procedures) into a single unit. These are used to improve security and design of your code.
Relational Database
A collection of relations or two-dimensional tables controlled by the Oracle Server.
Drop
A command used to remove an object from the database, such as a table or a view.
ROLLBACK
A command used to undo any changes made to the data in the tables during your current transaction. The opposite of the COMMIT keyword.
Pragma
A compiler directive, or hint, that is used to provide an instruction to the compiler. There are several pragma directives available in Oracle: • AUTONOMOUS_TRANSACTION • SERIALLY_REUSABLE • RESTRICT_REFERENCES • EXCEPTION_INIT • INLINE It's commonly used to bind a user defined exception to a particular error number (PRAGMA EXCEPTION_INIT)
Alias
A concept in Oracle that allows you to define a temporary name for a column or a table.
Grid Computing
A concept whereby multiple servers (nodes) can be connected together to meet your needs. Some nodes can run your database. Other nodes can run your application (web) server. You can add nodes on the fly. You can shift a node from one role to another on the fly.
SQL ANALYZE
A construct in Oracle that provides you with the tools to collect information about the database environment and schema objects, analyze SQL performance, identify and compare different optimizer approaches, and edit SQL statements for optimal performance--in some cases, automatically.
Namespace
A construct that Oracle uses when locating a database object during the execution of a SQL statement. In SQL syntax, object names that share this construct, must each be unique.
TIMESTAMP WITH TIME ZONE
A data type in Oracle that stores the date, time, fractional time in seconds, and a timezone value.
INTERVAL YEAR TO MONTH
A data type used to store a period of time. It stores a difference in years and months
VARRAY
A data type used to support a column that contains multiple values.
NCHAR
A data type which specifies a fixed-length character string in the national character set. You specify the national character set as either AL16UTF16 or UTF8 when you create your database. AL16UTF16 and UTF8 are two encoding forms of the Unicode character set (UTF-16 and CESU-8, correspondingly). You can omit size from the column definition. The default value is 1. The maximum value of size is 1000 characters.
RAC
A database solution that allows more than one system to share a single Oracle database. Clusters have nodes, and they can access the same files at the same time. Clustering has its advantages and requirements.
Hierarchical Database Model
A database that consists of a collection of records which are connected to one another through links. A link is an association between precisely two records. The records are organized as collections of trees.
Data Warehouse
A database that has been designed in a way that maximizes reading and reporting. It is usually a large database and follows a different design method to transactional databases. This item could be designed as a star schema, and contains fact and dimension tables.
RAW
A datatype for binary data - Variable-length binary data, from 1 byte to 4,000 bytes if MAX_STRING_SIZE=STANDARD and up to 32,767 bytes if MAX_STRING_SIZE=EXTENDED. Unlike the CHAR and VARCHAR2 data types, RAW data is not converted by Oracle Net from the database's character set to the user process's character set on SELECT or the other way on INSERT.
ORACLE_LOADER
A driver that can be used to access any data stored in any format that can be loaded by SQL*Loader. No DML can be performed on external tables but they can be used for query, join and sort operations. Views and synonyms can be created against external tables. They are useful in the ETL process of data warehouses since the data doesn't need to be staged and can be queried in parallel. They should not be used for frequently queried tables.
Flat File
A file that stores data in a series of single rows. A CSV file can also be called this term.
Aggregate Function
A function where the values of multiple rows are grouped together as input on certain criteria to form a single value of more significant meaning or measurement such as a set, a bag or a list.
Histogram
A graph that shows different sizes depending on the frequency of a value in a table.
Flashback
A group of features, that provide ways to view past states of database objects, or to return database objects to a previous state, without using traditional point-in-time recovery.
Entity
A grouping of things (or a class of things) with rules or data in common. It is an object in the REAL world with an independent existence.
DCL
A language that is used to control access to data stored in a database.
DDL
A language that is used to define elements of the database structure.
DML
A language that is used to manipulate data values in existing tables.
SQL
A language used to communicate with a database.
Standard Deviation
A measure of the spread or distribution of the values in a column or expression. It is obtained by calculating the square root of the variance.
Text Literal
A notation that is uses to specify values whenever string appears in the syntax of expressions, conditions, SQL functions, and SQL statements in other parts of this reference. They are always surrounded by single quotation marks.
CRUD Model
A paradigm that focuses on four (4) operations that form the fundamental ways that data is utilized in a database.
Clause
A part of an SQL statement.
Cursor
A pointer to a result set in PL/SQL. This pointer can be explicitly used by defining a named pointer, or implicitly used by using the SQL% version of this item.
PDB
A portable collection of schemas, schema objects, and nonschema objects that appears to an Oracle Net client as a non-CDB.
Function
A program written to optionally accept input parameters, perform an operation, and return a single value. A __________________ returns only one value per execution.
What is a function?
A program written to optionally accept input parameters, perform an operation, and return a single value. It returns only one value per execution.
CURRVAL
A property of a sequence object. It returns the present value of a sequence, without incrementing it.
Isolation Level
A property that can be set on a session that determines what kind of data can be seen. I has 5 types in Oracle: • READ UNCOMMITTED • READ COMMITTED • REPEATABLE READ • SERIALIZABLE • READ ONLY
Scalar Subquery
A query that returns exactly one value: a single row, with a single column.
Multi-Row Subquery
A query that returns more than one row as its result.
Index
A schema object that can speed up the retrieval of rows by using a pointer and improves the performance of some queries.
Dependency
A schema objects can reference other objects as part of their definition. For example, a view is defined by a query that references tables or other views; a procedure's body can include SQL statements that reference other objects of a database. An object that references another object as part of its definition is then categorized as this term, while the object being referenced is a referenced object.
PDB Lockdown Profile
A security mechanism to restrict operations that are available to users connected to a specified PDB. For example, a CDB administrator might create this item to restrict networking access such as UTL_HTTP and UTL_SMTP, or local user access to objects in a common schema.
How do you use a sequence with the NEXTVAL pseudocolumn?
A session can select either the next value with the NEXTVAL pseudocolumn, which forces the sequence to increment, or the last (or"current") value issued to that session with the CURRVAL pseudocolumn.
Column
A set of data values of a particular simple type, one for each row of the table. These provide the structure according to which the rows are composed.
Online Redo Log
A set of files containing records of changes made to the data
ALL_
A set of views that show the data dictionary objects across multiple schemas. The objects shown are filtered based on object level privileges. Objects in schemas other than the user querying the view will only be shown if the user has privileges on the object in the other schema.
Keyword
A set of words to explore the interconnected tuple structures in an RDB that cannot be easily identified using SQL on RDBMS.
INTERSECT
A set operator in SQL that allows you to view the results of one query that exist in the results of another query.
UNION ALL
A set operator that allows for the results of one query to be combined with the results of another query, and duplicates kept.
On Delete Cascade
A setting when creating a foreign key that says if a record in the parent table is deleted, then the corresponding records in the child table are also deleted.
On Delete Set Null
A setting when creating a foreign key that says if a record in the parent table is deleted, then the corresponding records in the child table have their key value set to NULL.
RPAD
A single row character function that returns expr1, right-padded to length n characters with the sequence of characters in exp2. If expr2 is not specified, then the default value is a single space.
LOWER
A single row character function that returns the char, with all letters in lowercase.
UPPER
A single row character function that returns the char, with all letters uppercase.
FLOOR
A single row numeric function that returns the largest integer equal to or less than n.
ROUND
A single row numeric function that returns the n rounded to integer places to the right of the decimal point. If the integer is not supplied, then n is rounded to zero places. If the integer is a negative, then n is rounded to the left of the decimal point.
SIGN
A single row numeric function that returns the sign of n.This function takes as an argument any numeric data type, or any nonnumeric data type that can be implicitly converted to NUMBER, and returns NUMBER.
CEIL
A single row numeric function that returns the smallest integer that is greater than or equal to n.
Transaction
A single unit of work that must be saved or rolled back in its entirety. These start when a user connects to a database and end when a user commits or rolls back.
DUAL
A special one-row, one-column table present by default in Oracle and other database installations. In Oracle, the table has a single VARCHAR2(1) column called DUMMY that has a value of 'X'. It is suitable for use in selecting a pseudo column such as SYSDATE or USER.
Primary Key
A special relational database table column (or combination of columns) designated to uniquely identify all table records. It's main features are: It must contain a unique value for each row of data. It cannot contain null values.
Inline View
A subquery in the FROM clause of a SELECT statement.
Conceptual Model
A summary-level data model that is most often used on strategic data projects. It typically describes an entire enterprise.
Global Temporary Table
A table whose definition may be visible to all sessions (global) but whose rows are private to the session that inserted them. The duration of the rows is either until COMMIT or until the session terminates (temporary).
Check Constraint
A type of repressionary tool that can be placed on a table or column. It allows you to specify a rule for a column where the values need to meet the condition.
Complex View
A type of view that cannot have DML (INSERT, UPDATE, or DELETE) performed on them. This is because complex views can contain join conditions, a GROUP BY clause, and an ORDER BY clause.
ROLLBACK Command
A user can use this command interactively to back out any DML statements that were issued and not committed.
USER_SEQUENCES
A view that describes all sequences owned by the current user. Its columns are the same as those in "ALL_SEQUENCES".
USER_INDEXES
A view that describes indexes owned by the current user. To gather statistics for this view, use the SQL ANALYZE statement. This view supports parallel partitioned index scans. Its columns (except for OWNER) are the same as those in "ALL_INDEXES".
USER_IND_COLUMNS
A view that describes the columns of the indexes owned by the current user and columns of indexes on tables owned by the current user. Its columns are the same as those in "ALL_IND_COLUMNS".
USER_COL_PENDING_STATS
A view that describes the pending statistics of the columns owned by the current user. Its columns (except for OWNER) are the same as those in ALL_COL_PENDING_STATS.
USER_TABLES
A view that describes the relational tables owned by the current user. Its columns (except for OWNER) are the same as those in ALL_TABLES. To gather statistics for this view, use the ANALYZE SQL statement.
USER_COL_COMMENTS
A view that displays comments on the columns of the tables and views owned by the current user. Its columns (except for OWNER) are the same as those in ALL_COL_COMMENTS.
V$PARAMETER
A view that displays information about the initialization parameters that are currently in effect for the session. A new session inherits parameter values from the instance-wide values displayed by the V$SYSTEM_PARAMETER view.
V$SQLTEXT_WITH_NEWLINES
A view that is identical to the V$SQLTEXT view except that, to improve legibility, V$SQLTEXT_WITH_NEWLINES does not replace newlines and tabs in the SQL statement with spaces.
select &rest_of_statement;
Almost any element of a SQL statement may be substituted at run-time. The constraint is that Oracle requires at least the first word to be static. The select keyword is required, and the remainder of the statement may be substituted as in this select statement.
Oracle Exalogic Elastic Cloud
An Engineered System, consisting of software, firmware and hardware, on which enterprises may deploy Oracle business applications, Oracle Fusion Middleware or software products provided by Oracle partners.
FETCH NEXT
An extension clause that will retrieve the specified number of records as an extension to an earlier request.
Why are indexes needed?
An index gives (near) immediate access to key values, so the check for existence can be made virtually instantaneously. When a primary key constraint is defined, Oracle will automatically create an index on the primary key columns, if one does not exist already.
Database Link
An object in one database that allows you to access objects (such as tables or views) in another database. It's helpful to get data from another database when the query is run, without have to import it and export it.
DISABLE NOVALIDATE
Any data (conforming or not) can be entered, and there may already be nonconforming data in the table.
Persistent Storage
Any data storage device that retains data after power to that device is shut off.
Where can single row functions be used?
Apart from their inclusion in the SELECT list of a SQL query, single row functions may be used in the WHERE and ORDER BY clauses.
select to_char(00001, '0999999') ||' is a special number' from dual;
Applies the numeric format mask '0999999' to the number 00001, converting it into the character string '0000001'. After concatenation to the character literals, the string returned is '0000001 is a special number'. The zero and six nines in the format mask indicate to the TO_CHAR function that leading zeros must be displayed and that the display width must be set to seven characters. Therefore, the string returned by the TO_CHAR function contains seven characters.
Optional national language support (NLS)
Are useful for specifying the language and format in which the names of date and numeric elements are returned. These parameters are usually absent, and the default values for elements such as day or month names and abbreviations are used.
M:1 (Many-to-One)
Artists <> Recording Company
Why does this query raise an error? SELECT end_date, count(*) FROM job_history;
Because the END_DATE column is in the SELECT list with a group function and there is no GROUP BY clause.
Where can the HAVING clause occur?
Before the GROUP BY clause in the SELECT statement. However, it is more common to place the HAVING clause after the GROUP BY clause. All grouping is performed and group functions are executed prior to evaluating the HAVING clause.
What is counted in the LENGTH function?
Blank spaces, tabs, and special characters are all counted by the LENGTH function. The LENGTH function takes only one string parameter.
How can blocking caused by row locks be identified?
By querying the V$SESSION view or through Database Express.
select to_date('25-DEC', 'DD-MON') from dual;
By supplying a format mask DD-MON to the string 25-DEC in query 3, Oracle can match the number 25 to DD and the abbreviated month name DEC to the MON component. Year and time components absent, so the current year returned by the SYSDATE function is used and the time is set to midnight. If the current year is 2009, query 3 returns the date 25/DEC/09 00:00:00.
What is the algorithm of sizing undo tablespaces?
Calculate the rate at which undo is being generated at you peak workload and multiply by the length of your longest query. There is a view, V$UNDOSTAT, that will tell you all you need to know.
What are some typical uses of multiple-row functions?
Calculating the sum or average of the numeric column values or counting the total number of records in sets. These are sometimes known as aggregation or group functions.
Check constraint
Can be used to enforce simple rules, such as that the value entered in a column must be within a range of values. The rule must be an expression that will evaluate to TRUE or FALSE.
A complex view
Can join detail tables, use functions, and perform aggregations.
These object types each have their own namespace
Constraints, Clusters, Database triggers, Dimensions, Private database links
G1(group_item)=result
Contains no nested functions. An example includes the SUM(group_item) and AVG(group_item) functions that return a single result per group.
select trunc(sysdate) day, trunc(sysdate, 'w') week, trunc(sysdate, 'month') month, trunc(sysdate, 'year') year from dual;
DAY WEEK MONTH YEAR ------------------------------------------------------ 17-APR-09 15-APR-09 01-APR-09 01-JAN-09
select round(sysdate) day, round(sysdate, 'w') week, round(sysdate, 'month') month, round(sysdate, 'year') year from dual;
DAY WEEK MONTH YEAR ------------------------------------------------------- 17-APR-09 15-APR-09 01-MAY-09 01-JAN-09
Session-persistent variables may be set explicitly using the ____________________ command.
DEFINE
The Oracle server stores information about all tables in a special set of relational tables called the _______________.
Data Dictionary
Alterations that affect the physical storage fall into whose domain?
Database administrator
This equation illustrates an important principle regarding date arithmetic:
Date1 - Date2 = Num1 A date can be subtracted from a date item. This principle implies that adding, multiplying, or dividing two date items is not permitted.
First normal form (1NF)
Deals with the issue of eliminating unnecessary repeating groups of data. An example would be in multiple columns where the descriptive information about the car is repeated.
Namespace
Defines a group of object types, within which all names must be uniquely identified by schema and name. Objects in different namespaces can share the same name.
select count(hire_date), count(manager_id) from employees;
Demonstrates how the COUNT function is used on both a DATE column and a NUMBER column. The integers 107 and 106 are returned, since there are 107 non-null HIRE_DATE values and 106 non-null MANAGER_ID values in the group.
SELECT job_id, avg(salary), count(*) FROM employees GROUP BY job_id HAVING avg(salary) > 10000 AND count(*) >1;
Demonstrates that the Boolean operators may be used to specify multiple HAVING clause conditions.
SELECT job_id, avg(salary), count(*) FROM employees GROUP BY job_id;
Divides the 107 records from the EMPLOYEES table into 19 groups based on common JOB_ID values. The average salary for each JOB_ID group and the aggregate row count are computed.
SUSTR Function (Substring)
Extracts a substring of a specified length from the source string beginning at a given position. If the start position is larger than the length of the source string, null is returned. If the number of characters to extract from a given start position is greater than the length of the source string, the segment returned is the substring from the start position to the end of the string.
select substr('1#3#5#7#9#', 5) from dual;
Extracts the substring beginning at position5. Since the third parameter is not specified, the default extraction length is equal to the number of characters from the start position to the end of the source string, which is 6.
F1(param1.1, F2(param2.1, param2.2, F3(param3.1)), param1.3)
F3(param3.1 is evaluated, and its return value provides the third parameter to function F2 and may be called param2.3. F2(param2.1 param2.2, param2.3) is evaluated, and its return value provides the second parameter to function F1 and is param1.2 F1(param1.1, param1.2, param1.3) is evaluated, and the result is returned to the calling program.
The CASE expression
Facilitates if-then-else conditional logic. There are two variants of the CASE expression. The simple case expression and the searched CASE expression.
Two fundamental rules for studying group functions.
First, they always operate on a single group of rows at a time. The group can be one of many groups a dataset has been segmented into, or it can be an entire table. The group function executes once per group. Second, rows with nulls occurring in a group columns or expressions are ignored by all group functions, except the COUNT(*) and COUNT(literal) forms of the COUNT function.
Why are the quotation marks in an alias important?
First, this alias consists of more than one word. Second, case preservation of an alias is possible only if the alias is double quoted.
CACHE
For performance, Oracle can preissue sequence values in batches and chache them for issuing to users. The default is to generate and cache the next 20 values.
Not-Null Constraints
Forces values to be entered into the key column. They are defined per column and are sometimes called mandatory columns; if the business requirement is that a group of columns should all have values, you cannot define one not-null constraint for the whole group but must define a not-ull constraint for each column.
________________________define the relational structure of the database, that is, the many-to-one relationships that connect the table, in their third normal form.
Foreign key constraints
Explicit Data Type Conversion
Functions that convert items from on data type to another. These return a value guaranteed to be the type required and offer a safe and reliable method of converting data items.
What is a single row function?
Functions that operate on one row of a dataset at a time. If a query selects ten rows, the function is executed ten times, once per row with the values from that row as input to the function.
What is a benefit of undo data combined with row and table locks.
Guarantees transaction isolation, which means the impossibility of incompatible transactions. Even though several transactions may be running concurrently, isolation requires that the end result must be as if the transactions were serialized.
select trunc(1601.916, 1) from dual;
Has a decimal precision parameter of 1, which implies that the source number is truncated at its tenths unit, and the number returned is 1601.9.
select round(1601.916, 1) from dual;
Has a decimal precision paramter (n) of 1 which implies that the source number is rounded to the nearest tenth. Since the hundredths is (n+1) is 1 (less than 5), rounding down ocurs, and the number returned is 1601.9.
ROW and ROWS
Have identical meaning, and both singular and plural forms are provided to make your OFFSET clause read more naturally.
2nd Normal Form
If a table has a Compound Primary Key, and one or more fields in the table depend on only part of the Primary Key for that table, move them to seperate tables along with that part of the key.
Where is the UNDO_RETENTION parameter NOT optional?
If you have a configured guaranteed undo retention.
select length(1234567890) from dual;
Implicitly converted into a character string. '1234567890', before being evaluated by the LENGTH function, which returns 10.
select mod('11', 2) from dual; select mod('11.123', 2) from dual;
Implicitly converts the character strings '11' and '11.123' into the numbers 11 and 11.123, respectively, before the MOD function evaluates them and returns the results 1 and 1.123.
select decode(1234, 123, '123 is a match', 'No match') from dual;
Is identical to the first except an iffalse parameter is defined. Therefore, since 1234 is not equal to 123, the string 'No match' is returned.
Implicit character to date conversion
Is possible when the character string conforms to the following date patterns: [D|DD] separator1 [MON|MONTH] separator2 [R|RR|YY|YYYY]. D and DD represent single digit and two digit days of the month. MON is a three-character abbreviation, while MONTH is the full name for a month. R and RR represent single and two digit years. YY and YYYY represent two and four digit years, respectively. The separator1 and separator2 elements may be most punctuation marks, spaces, and tabs.
ANSI
It describes many standards, including the standard for the SQL language. When it is mentioned, it refers to the SQL standard and is included in all databases that adopt the standard (MySQL, Oracle, SQL Server, etc). In Oracle SQL.
How many parameters does LAST_DAY return?
It takes a single mandatory parameter and has the syntax LAST_DAY(start date). The function extracts the month that the start date parameter belongs to and calculates the date of the last day of that month.
How many parameters does the Date TRUNC function take?
It takes one mandatory parameter and one optional parameter. Its syntax is TRUNC(source date, [date precision format]). The source date parameter represents any date item. The date precision format parameter specifies the degree of truncation and is optional.
How many parameters does INSTR take?
It takes two optional and two mandatory parameters. The default value for the search start position is 1, or the beginning of the source string. The default value for the nth occurrence is 1, or the first occurrence.
What happens if a query encounters a block that has been changed since the query started?
It will go to the undo segment to find the pre-updated version of the data. If, when it goes to the undo segment, that bit of undo data has been overwritten, the query will fail with the famous ORA-1555: snapshot of too old.
ASH
It's an Oracle feature that allows you to see current and historical information about active database sessions, based on the V$ dynamic performance views. It's useful for performance tuning and tracking session activity.
DATE
It's format is: YYYY-MM-DD HHMISS. Valid date range is from January 1, 4712 BC to December 31, 9999 AD.
BFILE
It´s a datatype that enables storing a pointer to an object in the file system in a database column and to read this file directly from the file system. This allows you to access the files available on the file system of database server as if they were stored in the database table itself.
select department_id, job_id, sum(commission_pct) from employees where commission_pct is not null group by department_id, job_id;
JOB_ID in both the SELECT and GROUP BY clauses. This second grouping attribute decomposes the two groups by DEPARTMENT_ID into the constituent JOB_ID components belonging to the rows in each group. The distinct JOB_ID values for rows with DEPARTENT_ID=80 are AC_ACCOUNT, SA_RE, and SA_MAN. The distinct JOB_ID value for rows with a null DEPARTMENT_ID is SA_REP. Therefore, this query returns two groupings: one that consists of three subgroups, and the other with only one as shown in figure 9-6.
VIEWS
Just a named SELECT statement - If the SELECT statement is saved as a view, then whenever the users query or update rows in the view (under the impression that it is a table), the statement runs, and the result is presented to users as though it were a table.
How large should the undo tablespace be?
Large enough to store the worst case of all the undo generated by concurrent transactions, which will be active undo plus enough unexpired undo to satisfy the longest-running query. In an advanced environment, you may also have to add space to allow for flashback queries.
LONG RAW
Like LONG, but binary data that will not be converted by Oracle Net. Any LONG RAW columns should be converted to BLOBs.
NVARCHAR2
Like VARCHAR2, but the data is stored in the alternative national language character set, one of the permitted Unicode character sets.
What happens if the undo tablespace is undersized for the transaction volume and the length of queries.
Oracle has a choice: Either let transactions succeed and risk queries failing with ORA-1555 or let queries succeed and risk transactions failing with ORA-30036. The default behavior is to let the transactions succeed to allow them to overwrite unexpired undo.
Datetime Datatypes
Oracle stores point-in-time values (dates and times) in a table. This datatype stores the year (including the century), the month, the day, the hours, the minutes, and the seconds (after midnight).
OTN
Oracle's community website. It includes forums and product documentation.
MAX and MIN with dates
MAX returns the latest date and MIN returns the earliest date.
You can think of this DML command as a shortcut for executing either an INSERT or an UPDATE or a DELETE.
MERGE
Composite Sorting - Results may be sorted by more than one column using composite sorting.
Multiple columns may be specified (either literally or positionally) as the composite sort key by comma-separating them in the ORDER BY clause.
Unique Constraint
Nominates a column (or combination of columns) for which the value must be different for every row in the table.
NUMBER
Numeric data, for which you can specify precision and scale. The precision can range from 1 to 38, and the scale can range from -84 to 127.
How are operators with the same level of precedence handled?
Operators with the same level of precedence are evaluated from left to right.
Three concepts from relational theory encompass the capabilities of the SELECT statement. What are they?
Projection, Selection, Joining
NVL2
Provides an enhancement to NVL but serves a similar purpose. It evaluates whether a column or expression of any data type is null or not. If the term is not null, the second parameter is returned, or else the third parameter is returned.Recall that the NVL functionis different since it returns the original term if it is not null.
The ___________________________________ means that it is not a schema object and cannot therefore be prefixed with a schema name. It does not mean that everyone has permissions against it.
Public Synonym
insert into orders (order_id, order_date, customer_id) values (order_seq.nextval, sysdate, '1000');
Raises an order with a unique order number drawn from the sequence ORDER_SEQ for customer number 1000.
Third normal form
Removes all interdependent attributes from a 2NF entity. An example would be that you could define a new customers entity that uniquely identifies a customer using the Customer ID primary key attribute where interdependent information such as the customer's name and company are stored.
TRIM(trailingtrimstring from s)
Removes all ofccurrences of trimstring from the end of string s if it is present.
The REPLACE function
Replaces all occurrences of a search item in a source string with a replacement term. If the length of the replacement term is different from that of the search item, then the length of the returned and source strings will be different. If the search string is not found the source string is returned unchanged.
What happens in Division by 0?
Results in an error.
ROWS ONLY
Retrieves the exact number or percentage of rows specified.
select min(commission_pct), max(commission_pct) from employees;
Returns 0.1 and 0.4 for the minimum and maximum COMMISSION_PCT values in the EMPLOYEES table. Notice that null values for COMMISSION_PCT are ignored.
select add_months(to_date('07-APR-2009','DD-MON-YYYY'), 1) from dual;
Returns 07-MAY-2009, since the day component remains the same if possible and the month is incremented by one.
select sum(commission_pct) from employees;
Returns 7.9 after adding the non-null COMMISSION_PCT values.
select min(job_id), max(job_id) from employees;
Returns AC_ACCOUNT and ST_MAN as the JOB_ID values appearing first and last alphabetically in the EMPLOYEES table.
ADD_MONTHS
Returns a date item calculated by adding a specified number of months to a given date value.
select nullif(1234, 1234) from dual;
Returns a null since the parameters are identical.
The LISTAGG function
Returns a string aggregation of column values. If an ORDER BY clause is present, values are sorted and concatenated. This function operates as a single-set aggregate function that operates on all rows and returns a single output row or as a group-set aggregate function that returns an output row for each group in the GROUP BY clause (discussed in the next section).
select sum(distinct salary) from employees;
Returns a total of 417158, since many employees get paid the same salary, and the DISTINCT keyword only adds unique values in the column to the total.
TO_CHAR
Returns an item of data type VARCHAR2. TO_CHAR(num, [format], [nls_parameter]) The num parameter is mandatory and must be a numeric value. The optional format parameter may be used to specify numeric formatting information such as width, currency symbol, position of a decimal point, and group (or thousands) separators and must be enclosed in single quotation marks.
Conditional Functions
Returns different values based on evaluating comparison conditions. Functions within this category simplify working with null values and include the NVL, NVL2, NULLIF, and COALESCE functions.
select coalesce(null, null, null) from dual;
Returns null because all its parameters are null.
NEXT_DAY
Returns the date when the next occurrence of a specified day of the week occurs.
select mod('11.123.456', 2) from dual;
Returns the error "ORA-1722: invalid number" when Oracle tries to perform an implicit character to number conversion because the string '11.123.456' is not a valid number.
select coalesce(null, null, null, 'a string') from dual;
Returns the fourth parameter, which is a string since this is the first not-null parameters encountered.
select nvl2(null, 1234, 5678) from dual;
Returns the ifnull parameter, which is 5678.
What does the LENGTH function do?
Returns the number of characters that constitute a character string. select * from countries where length(country_name) > 10;
MONTHS_BETWEEN
Returns the number of months between two mandatory date parameters. select months_between(sysdate, sysdate-31) from dual; This query returns 1 as the number of months between 16-APR-2009 and 16-MAR-2009.
MONTHS_BETWEEN
Returns the number of months between two mandatory date parameters. It's syntax is MONTHS_BETWEEN(date1, date2). If date1 occurfs before date2, a negative number is returned.
select employee_id, last_name, phone_number from employees where last_name = '&LASTNAME' or employee_id = &EMPNO;
SQL *Plus prompts you to input a value for the variable called LASTNAME. You enter an employee's last name, if you know it, for example, King. If you don't know the last name but know the employee ID number, you can type in any value and press the ENTER key to submit the value. Oracle then prompts you to enter a value for the EMPNO variable. After typing in a value, for example 0, and hitting ENTER, there are no remaining substitution variables for Oracle to resolve, and the following statement is executed.
select employee_id, last_name, phone_number from employees where last_name = '&LASTNAME' or employee_id = &EMPNO;
SQL*Plus prompts you to input a value for the variable called LASTNAME. You enter an employee's last name, if you know it, for example King. If you don't know the last name but know the employee ID number, you can type in any value and press the ENTER key to submit the value. Oracle then prompts you to enter a value for the EMPNO variable. After typing in a value, for example, 0, and hitting ENTER, there are no remaining substitution variables for Oracle to resolve, and the following statement is executed.
select instr('1#3#5#7#9#', '#') from dual;
Searches from the start of the source string for the first occurrence of the hash and returns position 2.
select decode('search', 'comp1', 'true1', 'comp2', 'true2', 'search', 'true'3', 'substr('2search', 2, 6), 'true4', 'false') from dual;
Searches through the comparison parameters for a match. The strings comp1 and comp2 are not equal to search, so the results true1 and tru2 are not returned. A match is found in the third comparison term comp3(parameter6), whifch contains the string search. Therefore, the third result term iftrue3(parameter 7) containing the string 'true3' is returned. Note that since a match has been found, no further searching takes place. So, although the fourth comparison term (parameter8) is also a match to expr1, this expression is never evaluated because a match was found in an earlier comparison term.
Consider the SALARY column in the EMPLOYEES table. This column has a data type of NUMBER(8,2). The SALARY column can be restricted as follows:
Select last_name, salary from employees where salary = 10000; The LAST_NAME and SALARY values of the employees who earn $10,000 are retrieved since the data types on either side of the operator match and are compatible.
IS NULL
Selects only the rows where a specific column value is NULL. Testing column values for equality to NULL is performed using the IS NULL operator instead of the "is equal to" operator (=).
select next_day(to_date('01-JAN-2009','DD-MON-YYYY'), 'WEDNE') from dual;
Specifies the character literal WEDNE, which is interpreted Wednesday. The next Wednesday after 01-JAN-2009 is 07-JAN-2009.
NULLS LAST
Specify that if the sort column contains null values, then these rows are to be listed last after sorting the remaining NOT NULL values.
select substr('1#3#5#7#9#', -3, 2) from dual;
Starts at position -3. The negative start position parameter instructs Oracle to commence searching three characters from the end of the string, which is position 8. The third parameter is 2, which results in the substring #9 being returned.
Public Synonym
Synonyms that are accessible to all users. When resolving references to an object, Oracle Database uses this type of synonym only if the object is not prefaced by a schema and is not followed by a database link.
TO_CHAR syntax for converting dates
TO_CHAR(date1, [format], [nls_parameter]) Only the date1 parameter is mandatory; it must take the form of a value that can be implicitly converted to a date. The optional format parameter is case sensitive and must be enclosed in single quotes. The format mask specifies which date elements are extracted and whether the element should be described by a long or an abbreviated name.
select sum(salary) from employees;
Takes the SALARY column value for ever row in the group, which in this case is the entire table, and returns the total salary amount of 721166.
BETWEEN
Tests whether a column or expression value falls within the range of two boundary values. The item must be at least the same as the lower boundary value or 'at most' the same as the higher boundary value or fall within the range for the condition to be true.
What does the ACID test require first?
That the database should keep preupdated versions of data in order that incomplete transactions can be reversed-either automatically in the case of an error or on demand through the use of the ROLLBACK command.
COUNT(ALL expr) COUNT(expr)
The All keyword is part of the default syntax so COUNT(ALL expr) and COUNT(expr) are equivalent If expr is based on named columns, then nulls are ignored, but if expr is based on anything else, it will be evaluated for every row, whether there are null values in the row or not.
How many parameters does the COALESCE function take?
The COALESCE function takes two mandatory parameters and any number of optional parameters. The syntax is COALESCE(expr1, expr2,..., exprn), where expr1 is returned if it is not null, else expr2 if it is not null, and so on. COALESCE is a general form of the NVL function.
COUNT(1)
The COUNT of a literal, like this one is internally rewritten to COUNT(*) and counts every row regardless of the presence of nulls.
DECODE
The DECODE function implements if-then-else conditional logic by testing its first two terms for equality and returns the third if they are equal and optionally returns another term if they are not.
How many parameters does the DECODE function take.
The DECODE function takes at least three mandatory parameters, but it can take many more. The syntax of the function is DECODE(expr1, comp1, iftrue1, [comp2, iftrue2...[compN, iftrueN]], [iffalse]).
FORCE or NOFORCE
The FORCE keyword will create the view even if the detail tables in the subquery do not exist. NOFORCE is the default and will cause an error if the detail table does not exist.
V$
The Oracle Server contains a set of underlying views that are maintained by the server and accessible to the database administrator user SYS. These views are called dynamic performance views because they are continuously updated while a database is open and in use, and their contents relate primarily to performance. Although these views appear to be regular database tables, they are not. These views provide data on internal disk structures and memory structures. These views can be selected from, but never updated or altered by the user.
Converting Characters to Numbers Using the TO_NUMBER Function.
The TO_NUMBER function returns an item of type NUMER. Character strings converted into numbers must be suitably formatted so that any nonnumeric components are translated or stripped away with an appropriate format mask. TO_NUMBER(string1, [format], [nls_parameter]) Only the string1 parameter is mandatory and if no format mask is supplied, it must be a value that can be implicitly converted into a number.
What is another name for the ampersand substitution variable?
The ampersand substitution variable is resolved at execution time and is sometimes known as run-time binding or run-time substitution.
OR
The boolean OR operator separates multiple conditions, atleast one of which must be satisfied by the row selected to warrant inclusion in the results set. If two conditions specified in the WHERE clause are joined with an OR operator, then a row is tested consecutively for conformance to either or both conditions before being retrieved.
select nullif('24-JUL-2009', '24-JUL-09') from dual;
The character literals in this query are not implicitly converted to DATE items and are compared as two character strings by the NULLIF function. Since the strings are of different lengths, the ifunequal parameter 24-JUL-2009 is returned.
Views can prevent errors. A view can help to present data in a way that is unambiguous. For example, many applications never actually delete rows. Consider this table: create table emp (empno number constraint emp_empno_pk primary key, ename varchar2 (10), deptno numer, active varchar2(1) default 'Y');
The column ACTIVE is a flag indicating that the employee is currently employed and will default to Y when a row is inserted. When a user, through the user interface, deletes an employee, the underlying SQL statement will be an update that sets ACTIVE to N. If users who are not aware of this query the table, they may severely misinterpret the results. It will often be better to give them access to a view.
What is returned if you execute the following query? SELECT * FROM DUAL;
The data value X is returned as the contents of the DUMMY column. Testing complex expressions during development by querying the dual table is an effective method to evaluate whether these expressions are correct.
select round(1601.916, 2) from dual;
The decimal precision number is 2, so the source number is rounded to the nearest hundredth. Since the thousandths unit is 6 (greater than 5), rounding up occurs, and the number returned is 1601.92.
select trunc(1601.916, 2) from dual;
The decimal precision parameter (n) is 2, so the source number is truncated at its hundredths unit, and the number returned is 1601.91.Note that this result would be different if a rounding operation were performed since the digit in position (n+1) is 6 (greater than 5).
select round(1601.916, -3) from dual;
The decimal precision parameter is -3. Since it is negative, the digit significant for rounding is found 3 places to the left of the decimal point, at the hundreds digit, which is 6. Since the hundreds units 6, rounding up occurs and the number returned is 2000.
What is the default number of cache values?
The default number of values to cache is only 20. Experience shows that this is usually not enough. If your application selects from the sequence 10 times a second, then set the cache value to 50,000. Don't be shy about this.
AVG(DISTINCT expr)
The distinct values of expr are summed and divided by the number of unique occurrences of expr.
NLS_CURRENCY value
The dollar symbol, but this can be changed at the user session level. For example, to change the currency to the three-character string GBP, you can issue the following command: ALTER SESSION set NLS_CURRENCY='GBP';
What is the || symbol?
The double pipe symbol and it represents the character concatenation operator. This operator is used to join character expressions or columns together to create a larger character expression.
The START_DATE column is compared to the this character literal: '01-JAN-2001'.
The entire four-digit year component (YYYY) has been specified. where start_date = '01-JAN-2001' This condition is equivalent: where start_date = '01-JAN-01' This is because of the RR component being less than 50, so the current (21st) century, 20, is prefixed to the year RR component to provide a century value.
Predicate
The expression used in a WHERE clause to specify which rows should be returned.
Column Alias
The facility to override the name of a column on a SELECT statement by us the keyword AS or by using a new name in double quotes.
select next_day(last_day(sysdate) -7, 'true') from dual;
The innermost function is evaluated first. SYSDATE returns the current date. Assume that today's date is 28-OCT-2009. The second innermost function is evaluated next. LAST_DAY('28-OCT-2009') returns the date of the last day in October, which is 31-OCT-2009. Finally the NEXT_DAY('24-OCT-2009', 'true') function is evaluated, and the query returns the number of the last Tuesday of the month, which in this example is 27-OCT-2009.
Single Ampersand Substitution
The most basic and popular form of SQL element.
Single Ampersand Substitution
The most basic and popular form of SQL element. The ampersand character (&) is the symbol chosen to designate a substitution variable in a statement and precedes the variable name with no spaces between them.
CDB
The multitenant architecture enables an Oracle database to function as a multitenant container database. This type of database includes zero, one, or many customer-created pluggable databases (PDBs).
Data Mining
The practice of automatically searching large stores of data to discover patterns and trends that go beyond simple analysis. This practice uses sophisticated mathematical algorithms to segment the data and evaluate the probability of future events. It is also known as Knowledge Discovery in Data (KDD)
Escape
The process of marking some special characters as being part of the string you want to capture. Often done with single quote characters.
Denormalize
The process to transform a database design from a ordinary format ideal for transactions to a format more suited to data warehouses. It often results in larger tables but less tables.
What happens if no ORDER BY clause is specified?
The same query executed at different times may return the same set of results in different row order, so no assumptions should be made regarding the default row order.
Session
The set of events that happen from when a user connects to a database to when the user disconnects from the database.
select last_name, salary, hire_date, sysdate-hire_date tenure from employees order by tenure
The smallest TENURE value appears first in the output since the ORDER BY clause specifies that the results will be sorted by the expression alias. Note that the results could be sorted by the explicit expression and the alias could be omitted, but using the aliases renders the query easier to read.
Cluster
The smallest unit of storage that is addressable (can be written to or read). Typically, these are 256 or 512 bytes in length.
B-Tree Index
The standard type of index available in Oracle. It is useful for selecting rows that meet an equivalence criteria or range criteria.
What is positional notation in insert statements?
The statement does not say which value should be inserted into which column; it relies on the position of the values, namely, their ordering in the command. When the database receives a statement using positional notation, it will match the order of the values to the order in which the columns of the table are defined.
&
The symbol that is used to add a substitution variable to a SQL statement.
When the following code is done in a view, how can you query the information? create view dept_sal as select d.department_name, sum(e.salary) dept_sal from departments d left outer join employees e on d.department_id=e.department_id group by department_name order by department_name;
The users can select from DEPT_SAL without needing to know anything about joins or even how to sort the results. select * from dept_sal:
select avg(distinct salary) from employees;
There are 58 unique salary values, which, when added, yield a total of 417158. Dividing417158 by 58 returns 7192.37931 as the average of the distinct salary values, which is returned by the third query.
Single Quotes
These are used to enclose string literals (and, in recent versions, DATE literals), character literals in SQL statements.
Index Clusters
These can denormalize tables in parent-child relationships so that related rows from different tables are stored together.
SET Operators
These combine sets of rows returned by queries, instead of individual items. Examples: UNION, INTERSECT, MINUS, UNION ALL, etc.
The ALTER command and the CREATE command in sequences.
These commands are the same in sequences with one exception: There is no way to set the starting value. If you want to restart the sequence, the only way is to drop it and recreate it.
TCL
These commands are used to manage transactions in database.These are used to manage the changes made by DML statements. It also allows statements to be grouped together into logical transactions.
Constraint
These enforce limits to the data or type of data that can be inserted/updated/deleted from a table. Their whole purpose is to maintain the data integrity during an update/delete/insert into a table.
Group Function
These functions operate on sets of rows to give one result per group. These sets may comprise the entire table or the table that is split into groups.
What does the error 'ORA-00923: FROM keyword not found where expected" or "ORA-00942:table or view does not exist." indicate?
These often indicate spelling or punctuation errors, such as missing enclosing quotes around character literals.
Partitioned Tables
These store rows in separate physical structures, the partitions, and allocate rows according to the value of a column.
Single-Row Function
These type of functions are the one who work on single row and return one output per row.
Text literal
These values are always surrounded by single quotation marks. If the syntax uses the term char, you can specify either this term or another expression that resolves to character data — for example, the last_name column of the hr.employees table. When char appears in that particular syntax scenario, the single quotation marks are not used.
Inner JOIN
They are also referred to as an EQUIJOIN. It creates a new result table by combining column values of two tables (table1 and table2) based upon the join-predicate. May or may not have the same column name.
All the alter table commands are DDL commands with the built in COMMIT. What does this mean?
They are nonreversible and will fail if there is an active transaction against the table.
What happens to any subsequent executions of the statement within the same session?
They automatically resolve the substitution variables from stored session values.
Undo and redo are not opposites.
They have different functions. Undo is about transactional integrity, whereas redo is about preventing data loss.
Why are Global temporary tables useful to Developers?
They offer a storage location for data that may be useful for interim result sets, without any need to worry about clearing the data when it is no longer needed or ensuring that sessions will not interfere with (or even see) data created by another session.
Situations that demand the use of conversion functions.
They range from formatting DATE fields in a report to ensuring that numeric digits extracted from character fields are correctly converted into numbers before applying them in an arithmetic expression.
What do the MAX and MIN functions do?
They return the maximum(largest) and minimum(smallest) expr value in a group.
What will the finance staff NOT see when querying a view?
They will see only the five columns that make up the view, not the remaining columns of EMPLOYEES with the personal information. The view can be joined to other tables or aggregated as though it were a table.
Isolation
This ACID rule enforces that the results of any query against data in the process of being changes must display the unchanged data until a transaction is complete. In other words, the changes must be hidden until a transaction is finished.
Durability
This ACID rule refers to the guarantee that, once committed, transactions cannot be lost.
Consistency
This ACID rule states that data returned from a query will be undeviating with the state of data when the transaction began.
LISTAGG
This analytic function orders data within each group specified in the ORDER BY clause and then concatenates the values of the measure column.
AND
This boolean operator merges conditions into one large condition to which a row must conform to be included in the results set. If two conditions specified in a WHERE clause are joined with an AND operator, then a row is tested consecutively for conformance of both conditions before being retrieved. If it conforms to neither or only one of the conditions, the row is excluded.
SGA
This forms part of the RAM and contains all of the information needed to operate the instance. It includes: • buffer cache • dictionary cache • redo log buffer • shared pool • Java pool • large pool • stream pool • log buffer
ANY / SOME
This function returns YES when any values of a Boolean expression are ... Specify this keyword only when the original performance is extremely slow.
"ORA-30036: unable to extend segment in undo tablespace.
This happens if a transaction runs out of undo space. The statement that hit the problem is rolled back, but the rest of the transaction remains intact and uncommitted.
WITH CHECK OPTION
This has to do with DML. If the subquery includes a WHERE clause, then this option will prevent insertion of rows that wouldn't be seen in the view or updates that would cause a row to disappear from the view. By default, this option is not enabled, which can give disconcerting results.
WITH CHECK OPTION
This has to do with DML. If the subquery includes a where clause, then this option will prevent insertion of rows that wouldn't be seen in the view or update that would cause a row to disappear from the view By default, this option is not enabled, which can give disconcerting results.
IDENTITY
This is a field in a table that is made up of values generated by the database. This is much like an AutoNumber field in Microsoft Access or a sequence in Oracle.
Fill Mode (FM)
This is a modifier that suppresses the padding in the return value of a TO_CHAR function. It uses trailing blank characters and leading zeroes to load format elements to a constant width. The width is equal to the display width of the largest element for the relevant format model.
Subquery
This is a query that is nested inside of another DML statement. It can return a set of rows or just one row to its parent.
Table
This is a set of data elements (values) using a model of vertical columns (identifiable by name) and horizontal rows, the cell being the unit where a row and column intersect. This has a specified number of columns, but can have any number of rows.
Sequence
This is an object in Oracle that is used to generate a numerical arrangement. This can be useful when you need to create a unique number to act as a primary key.
Metadata
This is data about data. This component gathers behind-the-scenes information that is used everywhere, by every industry, in multiple ways.
DATE
This is either length zero, if the column is empty, or 7 bytes. All DATE data includes century, month, day, hour, minute, and second. The valid range is from January 1, 4712 BC, to December 31, 9999 AD.
TIMESTAMP
This is length zero if the column is empty, or up to 11 bytes, depending on the precision specified. This is similar to DATE, but with a precision of up to nine decimal places fro the seconds and six places by default.
TIMESTAMP WITH TIMEZONE
This is like TIMESTAMP, but the data is stored with a record kept of the time zone to which it refers. The length may be up to 13 bytes, depending on precision This data type lets Oracle determine the difference between two times by normalizing them to Coordinated Universal Time (UTC), even if the times are for different time zones.
ORDER
This is relevant only for a clustered database: ORDER forces all instances in the cluster to coordinate incrementing the sequence so that numbers issued are always in order even when issued to sessions against different instances.
Recursive Subquery Factoring
This is the ability to carry out hierarchical queries using WITH clauses. It is ANSI SQL, it provides all the same functionality as connect by.
MAXVALUE
This is the highest number an ascending sequence can got to before generating an error or returning to its START WITH value. The default is no maximum.
START WITH
This is the starting point for the sequence, in other words, the number issued by the first selection. It defaults to 1 but can be anything.
INTERVAL YEAR TO MONTH
This is used for recording a period in years and months between two DATEs or TIMESTAMPs.
Binary Operator
This is used to force a column comparison byte by byte rather than character by character. It is also case sensitive and makes trailing spaces relevant.
NLS
This is what is is used to define the date, number, currency, and language settings for different nations.
Hash Join
This is where two tables are joined based on a hashing algorithm. You may see this in your Explain Plan output.
Cross Apply JOIN
This join is a variant of the ANSI CROSS JOIN, except it returns all rows from the left of the join,, where at least one row is returned by the expression on the right of it, but it also allows access to tables that appear to the left of the join.
Outer Apply JOIN
This join is a variant of the LEFT OUTER JOIN. The usage is similar to the CROSS APPLY join, but it returns all rows from the table on the left side of the join. If the right side of the join returns no rows, the corresponding columns in the output contain NULL values.
Cascade Constraints
This keyword is added to the DROP TABLE keyword. It allows you to drop all referential integrity constraints (foreign keys) that refer to the primary keys and unique keys of the table you're dropping.
TIMESTAMP WITH LOCAL TIMEZONE
This like TIMESTAMP, but the data is normalized to the database time zone on saving. When retrieved, it is normalized to the time zone of the user process selecting it.
Kernel Code
This manages memory and storage for the RDBMS
number_of_rows
This option of the FETCH clause defaults to 0 if a NULL or negative row count is specified. Mixed fractions provided as number_of_rows are truncated, and only the whole number portion is used as this parameter.If the number_of_rows value is larger than the number of rows remaining starting from position OFFSET +1, all rows in a results set are returned.
What happens if UNDO_MANAGEMENT is changed?
This parameter is static, meaning that if it is changed, the change will not come into effect until the instance is restarted. The other parameters are dynamic-they can be changed while the instance is running.
select to_number('$1,000.55') from dual;
This query cannot perform an implicit conversion to a number because of the dollar sign, comma, and period; it returns the error ORA-1722: invalid number.
SELECT to_char(end_date, 'yyyy') "Year" count(*) "Number of Employees" FROM job_history GROUP BY to_char(end_date, 'yyyy') ORDER BY count(*) DESC;
This query divides the JOB_HISTORY rows into groups based on the four-digit year component from the END_DATE column. Four groups are created using this grouping attribute. These represent different years when employees ended their jobs. The COUNT shows the number of employees who quit their jobs during each of these years. The results are listed in descending order based on the "Number of Employees" expression. Note that the COUNT group function is present in the ORDER BY clause.
select * from countries where upper(country_name) like '%U%S%A%';
This query extracts the rows from the COUNTRIES table where the COUNTRY_NAME values contain the letters U, S, and A (in any case) in that order.
select last_day(to_date('01_JAN-2009','DD-MON-YYYY')) from dual;
This query returns the date 31-JAN-2009.
select trim(both '*' from '****Hidden****') trim(leading '*' from '****Hidden****') trim(trailing '*' from '****Hidden****') from dual;
This query returns the strings Hidden, Hidden****, and ****Hidden. Note that although one trim character is specified, multiple occurrences will be trimmed if they are consecutively present.
OLAP
This refers to relatively a small number of transactions using complex queries. It retrieves aggregated data from a set of historical or archived data stored in the database. This system is suitable for applications such as business intelligence and data mining
Data Integrity
This refers to the accuracy and consistency of data in a database. This is achieved in Oracle by using ACID transactions.
Function-Based Indexes
This resource allows for you to index on a function based column like UPPER(NAME).
Row
This sometimes called a record. It is the set of fields within a table that are relevant to a specific entity.
NULLS FIRST
This specifies that rows with null values in the sort column should be displayed first. This is also appended to the ORDER BY clause.
NLS_DATE_FORMAT
This specifies the default date format to use with the TO_CHAR and TO_DATE functions. The default value of this parameter is determined by NLS_TERRITORY. The value of this parameter can be any valid date format mask, and the value must be surrounded by double quotation marks
Merge
This statement allows you to join a data source with a target table or view, and then perform multiple actions against the target based on the results of that join.
GRANT
This statement is used to give privileges to a specific user or role, or to all users, to perform actions on database objects. You can also use it to give a role to a user, to PUBLIC, or to another role.
1. create table employees_copy as select * from employees;
This statement will create a table EMPLOYEES_COPY, which is an exact copy of the EMPLOYEES table.
Sector
This storage term is one of the "pie slices" the diskette or disk is divided into. Dividing the circular medium into pie slices is a way to organize it so that data can be located by the read/write heads of the drive. The diskette or disk is also divided into a number of concentric circles. Data can be located by knowing the number of this term and the concentric track that passes through this particular term. Each track is divided into a number of clusters
Cardinality
This term refers to the uniqueness of data values contained in a particular column (attribute) of a database table. The lower this value is, the more duplicated elements in a column.
True or False? Automatic undo management will always keep undo data for as long as possible.
True - However, there are two cases where DBA action may be necessary: if it is necessary to ensure that queries will always succeed, even it this means that DML may fail, and if flashback queries are being used extensively.
When a row is inserted in the child table, Oracle will do a lookup on the index on the parent table to confirm that there is a matching row before permitting the insert. (True or False)?
True - However, you should always create indexes on the foreign key columns within the child table for performance reasons; a DELETE on the parent table will be much faster if Oracle can use an index to determine whether there are any rows in the child table referencing the row that is being deleted.
Indexes are part of the constraint mechanism. (True or False)
True - If a column (or a group of columns) is marked as a table's primary key, then every time a row is inserted into the table, Oracle must check that a row with the same value in the primary key does not already exist.
True or False? The VARCHAR2 data type must be qualified with a number indicating the maximum length of the column.
True - If a value is inserted into the column that is less than this, it is not a problem. The value will take up only as much space as it needs. If the value is longer than this maximum, the INSERT will fail with an error. If the value is updated to a longer or shorter value, the length of the column (and therefore the row itself) will change accordingly. If it is not entered at all or is updated to NULL, then it will take up no space at all.
True or False? Unique and primary key constraints need an index.
True - If one does not exist, one will be created automatically.
True or False? Limiting rows returned by a query involves specifying a suitable WHERE clause.
True - If the clause is too restrictive, then few or no rows are returned. If the conditional clause is too broadly specified, then more rows than are required are returned.
True or False? The FETCH clause specifies either an exact number_of_rows or a percentage_of_rows to return.
True - In the absence of this clause, the entire result set is returned, starting from row number OFFSET+1 to the last row.
True or False? When the source and target tables share identically named columns, it is possible to perform a natural join between them without specifying a join column.
True - In this scenario, columns with the same names in the source and target tables are automatically associated with each other. Rows with matching column values in both tables are retrieved.
True or False? A single SQL statement or even groups of associated statements, are often saved as script files for future use.
True - Individual statements in SQL scripts are commonly terminated by a line break (or carriage return) /
True of False? It is not possible to create segments in an undo tablespace, other that the undo segments that will be created automatically.
True - Initially, there will be a pool of ten undo segments created in an undo tablespace. More will be created if there are more than ten concurrent transactions. Oracle will monitor the concurrent transaction rate and adjust the number of segments as necessary.
True or False? The SELECT statement on which a view is based can be anything.
True - It can join tables, perform aggregations, or do sorts; absolutely any legal SELECT command can be used as the basis for a view.
A unique constraint requires and index. (True or False)?
True - It differs from a primary key constraint in that the columns of the unique constraint can be left null.
True or False? Use of undo segments is incompatible with use of rollback segments?
True - It is one or the other, depending on the setting of the UNDO_MANAGEMENT parameter.
True or False? UNDO_RETENTION set in seconds is usually optional.
True - It specifies a target for keeping inactive undo data and determines when it becomes classified as expired rather than unexpired. If, for example, your longest-running query is 30 minutes, you would set this parameter to 1800. Oracle will then attempt to keep all undo data for at least 1,800 seconds after COMMIT, and your query should therefore never fail with ORA-1555.
True or False? Conditional clauses compare two terms using comparison operators.
True - Knowing the data types of the terms is important so that they can be enclosed in single quotes, if necessary.
True or False? Sequences are an invaluable tool for generating primary keys.
True - Many applications will need automatically generated primary key values. Examples in everyday business data processing are customer numbers and order numbers.
True or False? The relational database paradigm includes a requirement that every table should have a primary key, namely, a column (or combination of columns) that can be used to distinguish every row.
True - Oracle Database deviates from the paradigm (as do some other RDBMS implementations) by permitting tables without primary keys.
True or False? Any arithmetic calculation with a NULL value always returns NULL.
True - Oracle offers a mechanism for interacting arithmetically with NULL values using the general functions discussed in Chapter 8.
True or False?You must pay careful attention to case when interacting with character literal data and aliases.
True - Requesting a column called JOB_ID or job_id returns the same column, but asking for rows where the JOB_ID value is PRESIDENT is different from asking for rows where the value is President.
True or False? SQL offers a more formalized way of inserting aliases by inserting the AS keyword between the column or expression and the alias.
True - SELECT EMPLOYEE_ID AS "employee ID", JOB_ID AS "Occupation" FROM JOB_HISTORY;
True or False? Caching sequence values is vital for performance.
True - Selecting from a sequence is a point of serialization in the application code; only one session can do this at once. The mechanism is efficient. It is much faster than locking a row, updating the row, and then unlocking it with a COMMIT. But even so, selecting from a sequence can be a cause of contention between sessions.
True or False? The user must have privileges to access the underlying object in order the synonym-based reference to succeed.
True - Tables and views can be renamed or relocated without ever having to change code; only the synonyms need to be adjusted.
True or False? Generic conditional logic is implemented by the DECODE function and the CASE expression.
True - The DECODE function is specific to ORACLE, while the CASE expression is ANSI SQL compliant.
True or False? It is uncommon for character data to be implicitly converted into numeric data types, since the only condition under which this can occur is if the character data represents a valid number.
True - The character string '11' will be implicitly converted to a number, but '11.123.456' will not be, as the following queries demonstrate:
True or False? The day of the week parameter must be a valid character value as determined by the NLS_DATE_LANGUAGE=AMERICAN, the default values are at least the first three characters of the day name.
True - The character values representing the days of the week may be specified in any case. The short name may be longer than three characters; for example, Sunday may be referenced as sun, sund, sunda, or Sunday.
True or False? Columns of a table may be linked to each other or to strings of literal characters to create one resultant character expression.
True - The concatenation operator is flexible enough to be used multiple times and almost anywhere in a character expression.
True or False? A foreign key constraint is defined on the child table, but a unique or primary key constraint must already exist on the parent table.
True - The constraint nominates a column (or columns) in the child table that corresponds to the primary key columns in the parent table. The columns do not need to have the same names, but they must be of the same data type.
True or False? A date can be subtracted from another date.
True - The difference between two date items represents the number of days between them. Any number, including fractions, may be added to or subtracted from a date item. In this context, the number represents a number of days. The sum or difference between a number and a date item always returns a date item. This principle implies that adding, multiplying, or dividing two date items is not permitted.
True or False? The INITCAP function can take only one parameter.
True - The following query illustrates the usage of this function: select initcap('init cap or init_cap or init%cap') from dual. The query returns Init Cap Or Init_Cap Or Init%Cap
True or False? Single-row functions can be nested to any level of depth.
True - The general form of a function is as follows: Function1(parameter1, parameter2,...) = result1
True or False? Variables can assigned any alphanumeric name that is a valid identifier name.
True - The literal you substitute when prompted for a variable must be an appropriate data type for that context; otherwise, an "ORA-00904: invalid identifier" error is returned.If the variable is meant to substitute a character or date value, the literal needs to be enclosed in single quotes.
True or False? When used in conditional WHERE clauses, date columns may be compared to other date columns, literals, or expressions.
True - The literals are automatically converted into DATE values based on the default date format, which is DD-MON-RR.
True or False? The number of months may be fractional, but the fractional component is ignored, and the integer component is used.
True - The number of months may also be negative, resulting in a target date earlier than the start date being returned.
True or False? Functions can return a value with a different data type from its input parameters.
True - The preceding query shows how the LENGTH function accepts one character input parameter and returns a numeric output.
True or False? The NUMBER data type may optionally be qualified with a precision and a scale.
True - The precision sets the maximum number of significant decimal digits, where the most significant digit is the left-most nonzero digit and the least significant digit is the right-most known digit in the number.
True or false? Changing the status of a constraint between ENABLED/DISABLED and VALIDATE/NOVALIDATE is an operation that will affect all sessions.
True - The status change is a data dictionary update. Switching a deferrable constraint between IMMEDIATE and DEFERRED is session specific, though the initial state will apply to all sessions.
True or False? Views can enforce security?
True - There are several ways of enforcing this, but a view is often the simplest. Consider the HR.EMPLOYEES table. This includes personal details that should not be visible to staff outside the personnel department.
True or False? Character fields are flexible and allow the storage of almost any type of information.
True - Therefore, DATE and NUMBER values can easily be converted to their character equivalents. These conversions are known as number to character and date to character conversions.
True or False? The algorithm controlling which expired undo data is overwritten first will always choose to overwrite the oldest bit of data.
True - Therefore, UNDO_RETENTION is always at the maximum allowed by the size of the tablespace.
True or False? Nested functions are first evaluated before their return values are used as parametric input to other functions.
True - They are evaluated from the innermost to outermost levels.
True or False? Group functions aggregate a number of values from multiple rows into a single result.
True - They are widely used for reporting purposes, providing sum totals, averages, and counts. They are also known as summary or aggregate functions.
An oddity of unique constraints is that it is possible to enter a NULL value into the key columns; it is indeed possible to have any number of rows with NULL values in their key columns. (True or False)?
True - This is because NULL is not equal to anything, not even to another NULL. So selecting rows on a key column will guarantee that only one row is returned - unless you search for NULL, in which case all the rows where the key columns are null will be returned.
Suppose you want to know how many employees were previously employed in each of these departments. There are seven rows that can be manually grouped and counted. However, if there are a large number of rows, an aggregate function like COUNT may be used, as shown in this query:
select department_id, count(*) from job_history where department_id in (50, 60, 80, 110) group by department_id;
This query to provides a list of employees whose first names begin with the letter A.
select first_name from employees where first_name like 'A%'; The character literal that the FIRST_NAME column is compared to is enclosed in single quotes like a regular character literal. The percentage symbol substitutes zero or more characters appended to the letter A. The wildcard characters can appear at the beginning, the middle, or the end of the character literal.
Retrieving employee records having FIRST_NAME values beginning with the letter B or those with a COMMISSION_PCT greater than 35 percent can be written as follows:
select first_name, last_name, commission_pct, hire_date from employees where first_name like 'B%' or commission_pct > 0.35; Notice that the two conditions are separated by the OR keyword. All employee records with FIRST_NAME values beginning with an uppercase B will be returned regardless of their COMMISSION_PCT values, even if they are NULL. Records with COMMISSION_PCT values greater than 35 percent (regardless of what letter their FIRST_NAME begins with) are also returned.
Employee records with FIRST_NAME values beginning with the letter J and COMMISSION_PCT greater than 10 percent can be retrieved using the following query.
select first_name, last_name, commission_pct, hire_date from employees where first_name like 'J%' and commission_pct > 0.1; To specify further mandatory conditions, simply add them and ensure that they are separated by additional AND operators. You can specify as many conditions as you want.
What about when you are searching for a literal that contains a percentage or underscore character? A naturally occurring underscore character may be escaped (or treated as a regular nonspecial symbol) using the ESCAPE identifier in conjunction with an ESCAPE character. In this example, any JOB_ID values that begin with the three characters SA_ will be returned.
select job_id from jobs where job_id like 'SA\_%' escape '\'; This is equivalent - it uses a dollar symbol as the ESCAPE character: select job_id from jobs where job_id ike 'SA$_%' escape '$';
To fetch the JOB_ID, LAST_NAME, SALARY, and HIRE_DATE values from the EMPLOYEES table such that the results must be sorted in reverse alphabetical order by JOB_ID first, then in ascending alphabetical order by LAST_NAME, and finally in numerically descending order based on the SALARY column, you can run this query.
select job_id, last_name, salary, hire_date from employees where job_id in ('SA_REP','MK_MAN') order by job_id desc, last_name, 3 desc;
If you wanted a list of the LAST_NAME values of those employees currently employed as sales representatives. The JOB_ID value for a sales representative is SA_REP. The following statement produces such a list:
select last_name from employees where job_id='SA_REP'; If you tried specifying the character literal without the quotes, an Oracle error would be raised. Remember that character literal data is case sensitive. so these where clauses would not work: where job_id=SA_REP no quotes where job_id='Sa_Rep' All should be capital where job_id='sa_rep' supposed to be capital
The query fetches the LAST_NAME column from the EMPLOYEES table for those rows that have NULL values stored in the COMMISSION_PCT column:
select last_name from employees where commission_pct is null; This WHERE clause reads naturally and retrieves only the records that contain NULL COMMISSION_PCT values.
The following two statements demonstrate the use of the IN operator with DATE and CHARACTER data:
select last_name from employees where last_name in ('King','Kochhar'); select last_name from employees where hire_date in ('30-JAN-2004','21-SEP-2005');
The SALARY value in each row is compared for equality to the literals specified in the set.
select last_name from employees where salary in (1000, 4000, 6000);
The character literal 'King' is converted to a numeric representation.
select last_name from employees where last_name < 'King'; Assuming a US7ASCII database character set with AMERICAN NLS settings, the literal 'King' is converted into its ordinal character values.
Positional Sorting - Oracle offers an alternative, shorter way to specify the sort column or expression. Instead of specifying the column name, the position of the column as it occurs in the SELECT list is appended to the ORDER BY clause.
select last_name, hire_date, salary from employees order by 2; The ORDER BY clause specifies the numeric literal 2. This is equivalent to specifying ORDER BY HIRE_DATE since that is the second column in the SELECT clause.
Suppose that a report is requested that must contain an employee's LAST_NAME, HIRE_DATE, and SALARY information, sorted alphabetically by the LAST_NAME column for all sales representatives and marketing managers. This report could be extracted with the following:
select last_name, hire_date, salary from employees where job_id in ('SA_REP','MK_MAN') order by last_name; The data selected may be ordered by any of columns from the tables in the FROM clause, including those that do not appear in the SELECT list.
A numeric column can be compared to another numeric column in the same row to construct a WHERE clause condition, as the following query demonstrates:
select last_name, salary from employees where salary = department_id;
WHERE clause conditions can also be used to compare numeric columns and expressions or to compare expressions to other expressions.
select last_name, salary from employees where salary = department_id*100; select last_name, salary from employees where salary/10 = department_id*10;
This query may be issued to obtain a list of LAST_NAME and SALARY values for employees who earn more than $5,000.
select last_name, salary from employees where salary > 5000;
This query selects two columns of the REGIONS table along with an expression using the LENGTH function with the REGION_NAME column:
select region_id, region_name, length(region_name) from regions. The length of the REGION_NAME column is calculated for each of the four rows in the REGIONS table; the function executes four separate times, returning one result per row.
Tests equality between two DATE columns.
where start_date = end_date Rows that contain the same values in their START_DATE and END_DATE columns will be returned. Note, however, that DATE values are equal to each other only if there is an exact match between all their components, including day, month, year, hours, minutes, and seconds.