Oracle Online Test Series 1, Oracle Mock Test, Oracle Online Quiz

According to the precedence of expression as in BODMAS (Brackets, Order, Division, Multiplication, Addition, Subtraction) the expression is evaluated, which ensures the equation steps are completed in the right order. So, the correct answer is 100 / 0.15.

Correct answer is: SELECT deptno, avg(sal) FROM emp GROUP BY deptno;
For aggregate functions (COUNT, MAX, MIN, SUM, AVG), GROUP BY clause is mandatory.

Correct answer is: select job, sum(sal) from emp where comm is null group by job having sum(sal) > 2500;
For aggregate functions (COUNT, MAX, MIN, SUM, AVG), GROUP BY clause is mandatory. Otherwise, you will get the error.

AVG, SUM, STDDEV can only be used with numeric data types except COUNT. COUNT is used for counting the number of rows, tables and collections. It returns the number of rows returned by the query. You can use it as an aggregate or analytic function.

IN operator is used to prevent the error: ‘ORA-01427:single row sub query returns more than one row’. IN operator is used to specify which values to compare or reword the query so only one row is retrieved.

LIKE determines if a character string matches a pattern. It is used when only a fragment of a text value is known. It supports two wildcard match options: % and _.

The above statement is wrong. A foreign key column must not contain unique values. A FOREIGN KEY constraint can contain null values; however, if any column of a composite FOREIGN KEY constraint contains null values, verification of all values that make up the FOREIGN KEY constraint is skipped. To make sure that all values of a composite FOREIGN KEY constraint are verified, specify NOT NULL on all the participating columns. A foreign key (FK) is a column or combination of columns that is used to establish and enforce a link between the data in two tables. You can create a foreign key by defining a FOREIGN KEY constraint when you create or modify a table.

A stored procedure is only executed when they explicitly called. It is a set of functionality which is executed when it is explicitly invoked. Stored Procedure is a set of Structured Query Language (SQL) statements. It can be helpful in controlling access to data, preserving data integrity and improving productivity.

A database trigger is an alternative for foreign key for implementing referential integrity. A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server.

Primary key may not contain duplicate values. Primary Key is a combination of Unique and NOT NULL Constraints so it can’t have duplicate values or any NULL values. A primary key is a special relational database table column (or combination of columns) designated to uniquely identify all table records. It must contain a unique value for each row of data.

A hot backup is taking up the backup of the database while it is still up and running. A few systems that need to support continuous operation, it is difficult to bring down the database without interrupting the service provided to the users. In such cases, hot backup approach should be used. While taking the backup, if the database remains open and available to users then this kind of back up is referred to as hot backup. In hot backup, the database has to run all the time due to critical requirements. Because of this there may be changes in the data files while backup is being taken.

A data block is the smallest logical storage unit for a database object. Data block is the optimum level of storage. Data blocks are also called logical blocks, Oracle blocks, or pages. At the finest level of granularity, Oracle stores data in data blocks. A data block corresponds to a specific number of bytes of physical database space on disk.

There is no dbms_metadata_get_ddl package. There is dbms_metadata package and get_ddl() function. ( dbms_metadata.get_ddl() )

The alert log file (also referred to as the ALERT.LOG) is a chronological log of messages and errors written out by an Oracle Database. Typical messages found in this file is: database startup, shutdown, log switches, space errors, etc. This file should constantly be monitored to detect unexpected messages and corruptions. Oracle will automatically create a new alert log file whenever the old one is deleted.

Bitmap indexes are used on a fact table. Bitmap indexes are widely used in data warehousing environments. It reduces response time for large classes of ad hoc queries and storage requirements compared to other indexing techniques. It provides efficient maintenance during parallel DML and loads.

Following are the stages of instance startup to a usable state where normal users may access it:
STARTUP NOMOUNT – Instance startup
STARTUP MOUNT – The database is mounted
STARTUP OPEN – The database is opened

ORACLE_BASE is the root of the Oracle Database directory tree. ORACLE_BASE is an environment variable used as the base directory for an OFA installation. If you install an OFA-compliant database using Oracle Universal Installer defaults, ORACLE_BASE is X:\oracle\product\10.2.0 where X is any hard drive.

DBTIMEZONE is a function which returns the current value of Database Time Zone. It can be queried using the example below:

SELECT DBTIMEZONE FROM DUAL;

DBTIME
——
+00:00

Please note the return type of function is Time Zone Offset. The format ([+|-] TZH: TZM) contains the lead (+) and lag (-) with hour and minutes specifications.

WRAP is used to encrypt a PL/SQL application. WRAP command is used while we are delivering a PL/SQL application without exposing the source code. Syntax for this would be: wrap iname=[file] oname=[file]

A log switch occurs when LGWR stops writing to one redo log group and starts writing to another. By default, a log switch occurs automatically when the current redo log file group fills. This option is useful in configurations with large redo log files that take a long time to fill. To force a log switch, you must have the ALTER SYSTEM privilege. Use the ALTER SYSTEM statement with the SWITCH LOGFILE clause.
The following statement forces a log switch: ALTER SYSTEM SWITCH LOGFILE;

The EXPLAIN PLAN statement displays execution plans chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements. A statement’s execution plan is the sequence of operations. Oracle performs to run the statement. The EXPLAIN PLAN results let you determine whether the optimizer selects a particular execution plan, such as, nested loops join. It also helps you to understand the optimizer decisions, such as why the optimizer chose a nested loops join instead of a hash join, and lets you understand the performance of a query.

Fetch by rowid is the fastest query method for a table. The “table fetch by rowid” Oracle metric occurs when rows are fetched using a ROWID (usually recovered from an index), each row returned increments this counter. This statistic is an indication of row fetch operations being performed with the aid of an index. Because, doing table scans usually indicates either non-optimal queries or tables without indexes, this statistic should increase as the above issues have been addressed in the application.

Trigger is stored in the database which includes SQL and PL/SQL or Java statements to run as a unit and invokes stored procedures. Triggers are implicitly fired by Oracle when a triggering event occurs, no matter which user is connected or which application is being used. There are 12 types of triggers can exist in a table in Oracle: 3 before statement, 3 after statement, 3 before each row and 3 after each row.

Implicit cursors are automatically created by Oracle whenever an SQL statement is executed, when there is no explicit cursor for the statement. Implicit cursors are not named. Implicit cursors are used by Oracle every time you issue a Select statement in PL/SQL. If you use an implicit cursor, Oracle will perform the open, fetches, and close for you automatically. Implicit cursors are used in statements that return only one row.

The key word in the “A pseudocolumn is also similar to a function without arguments” statement is “similar”. If we take a closer look at the definition of pseudo-columns we will notice the following:

“However, functions without arguments typically return the
same value for every row in the result set, whereas pseudocolumns typically return a different value for each row.”

SYSDATE as well as USER will always return same value for every row selected from a table, always.