oracle interview questions part 3

101:   What does the MERGE statement in the SQL statement?

A:   We use the MERGE statement to merge one table into another table.

102:   Can you update, insert, or delete any records while you are using the MERGE statement?

A:   Yes.

 

103:   What is a Materialized View?

A:   A materialized view (MVIEW) is a replica of a target master from a single point in time.

 

104:   What are the Materialized View types?

A:         Read-Only Materialized Views 

            Updatable Materialized Views 

            Sub-query Materialized Views 

            Row-id vs. Primary Key Materialized Views 

 

105:   Write the difference between ROWID and PRIMARY KEY in the Materialized View.

A:   Fast refresh requires association between rows at snapshot and master sites. Snapshots that use ROWIDs to refresh are called ROWID snapshots while those that use primary keys are called primary key snapshots.

 

106:   What is the difference between a Materialized View and View?

A:   A Materialized View is a physical duplicated data in a table, but a View is just a logical presentation of a table.

 

107:   When or why do you use a Materialized View?

A:   You use Materialized Views to create summaries in a data warehouse environment or replicate a data in a distributed environment. In data warehouses, you can use materialized views to pre-compute and store aggregated data such as the sum of sales. In distributed environments, you can use materialized views to replicate data from a master site to other distributed sites.

 

108:   What is a materialized view log?

A:   A materialized view log is a holder that contains updated, inserted, or deleted records’ information in the primary table.

 

109:   What are the PRIMARY KEY and ROWID in the Materialized View Log?

A:   The Materialized View log that use ROWIDs to refresh are called ROWID view log while those that use primary keys are called primary key view log.

 

110:   What does the USER_SNAPSHOT_LOGS view contain?

A:    It shows if our log was created successfully and its name (MLOG$_EMP).

 

111:   Create a materialized view that contains the department number, number of employees, and total salaries paid to employees by department.

A:         SQL> CREATE MATERIALIZED VIEW mv_sal
                                    BUILD IMMEDIATE
                                    REFRESH ON DEMAND
                                    AS SELECT deptno,

                                    COUNT(1) AS no_of_emp, SUM(sal) AS salary
                        FROM emp
                        GROUP BY deptno
            SQL> /

112:   Who can create a materialized view?

A:   The one that was granted the CREATE MATERIALIZED VIEW privilege.

 

113:   What does the USER_MVIEWS view contain?

A:   It contains all the Materialized Views’ information that were created by the user.

 

114:   How do you refresh a materialized view?

A:   SQL> EXECUTE dbms_snapshot.refresh('mv_sal','C');

115:   What parameter should be used to update the materialized view every month automatically without human intervention?

A:   The START WITH SYSDATE option will create an immediate data, and the NEXT(SYSDATE+30) option will update the table every 30 days.

116 :  What does the USER_JOBS view contain?

A:   It contains all users’ jobs in the Oracle queue.

 

117:   How do you remove a job from the Oracle Job Queue?

A:   SQL> EXECUTE dbms_job.remove(job_number);

118:   How do you drop a materialized view log and a materialized view?

A:   SQL> DROP MATERIALIZED VIEW LOG ON emp;
            To drop it:

            SQL> DROP MATERIALIZED VIEW mv_sal;

119:   What does the BREAK ON clause in SQLPLUS?

A:   It builds a break on a column.

120:   What do the REPHEADER and REPFOOTER commands in SQLPLUS?

A:   They make a report header and footer.

121:   What does the following commands?

COLUMN sal HEADING 'Salary' FORMAT $99,999.99 --Creates heading format.

COLUMN ename HEADING 'Employee' FORMAT a20 – Creates heading format.

REPHEADER '' – Creates report heading.

BREAK ON dname SKIP 1 – Creates control bread on a column and skip 1 line after the break.

COMPUTE SUM OF sal ON dname – Computes total salary within a department.

SPOOL c:\salary.out  -- Activates spooling.

SPOOL OFF  -- Deactivate spooling.

REPFOOTER '' – Creates report footer.

CLEAR BUFFER  -- Clear the Oracle buffer.

CLEAR COLUMNS – Clears columns.

CLEAR COMPUTE  -- Clears compute functions.

 

122:   What does the CLEAR command in SQLPLUS?

A:   Note that all the values in REPHEADER, REPFOOTER, BUFFER, COLUMNS, COMPUTE and etc are going to stay the same during your open session. In order to clean them, you should use the CLEAR command for BUFFER, COLUMNS, and COMPUTE. And input NULL to REPHEADER and REPFOOTER.

 

123:   What does the UNION statement in the SQL statement?

A:    It will query all the records that match or not match with the base table.

124:   What does the INTERSET statement in the SQL statement?

A:   It will query all the records that match with the base table. It is the same as joining two tables.

 

125:   What does the MINUS statement in the SQL statement?

A:   It will query all the records that are not matching against your base table.

 

126:   Why it is important to eliminate duplicate records?

A:   To keep your database integrity.

 

127:   What does the following SQL statement?

SQL> DELETE FROM dup_emp
                  WHERE ROWID IN (SELECT MAX(ROWID)

                                                   FROM dup_emp
                                                   GROUP BY empno
                                                   HAVING COUNT (empno) > 1)
SQL> /
A:   Deletes all the rows that have the same employee number except the first one.

 

128:   What is a data partitioning in the Oracle database?

A:   The data partitioning in the Oracle database is that the data will be partitioned in multi-tablespaces for ease of maintenances.

 

129:   When should you use data partitioning?

A:   When you have a huge data file and can be classified to some partitions.

 

130:   What is the advantage of using a data partitioning?

A:    It is faster to access. It is easier to maintain.

 

131:   What is a partition key?

A:   It is used to separate data and associates them to their own assigned tablespace.

 

132:   What is a local index in the data partitioning?

A:   A Local index is one that is partitioned exactly like the table to which it belongs.

 

133:   What is a global index in the data partitioning?

A:   A Global index, unlike local indexes, you should explicitly partition range boundaries using the “VALUE LESS THAN” methods.

 

134:   What are the differences between local and global indexes?

A:   In the local index you don’t define explicitly partition range.

 

135:   How does the ‘VALUE LESS THAN’ method work in the data partitioning?

A:   The VALUES LESS THAN clause indicates the partition key value must be less then its assigned value in order to be illegible for any DML transaction on its assigned tablespace.

136:   Why do you need multiple tablespaces?

A:   Multiple tablespaces give us more flexibility to maintain a tablespace without affecting any performance or downtime to others.

 

137:   Create a range-based partitioning table named p_emp. Make sure that the data entry of the each department goes to its own provided tablespaces such as the accounting department goes to the dept10ts tablespace, the data entry of the research department goes to the dept20ts tablespace, etc.

A:   SQL> CREATE TABLE p_emp (
            empno             NUMBER(4) PRIMARY KEY,
            ename             VARCHAR2(10),
            job                   VARCHAR2(9),
            mgr                  NUMBER(4),
                  hiredate             DATE,
            sale                 NUMBER(7,2),
            comm             NUMBER(7,2),
            deptno             NUMBER(2))
                  STORAGE (INITIAL 5K

                                  NEXT 5K

                                  PCTINCREASE 0)
                  PARTITION BY RANGE (deptno)
                  (PARTITION dept10

                        VALUES LESS THAN (20)

                        TABLESPACE dept10ts,
                  PARTITION dept20

                        VALUES LESS THAN (30)

                        TABLESPACE dept20ts,
                  PARTITION dept30

                        VALUES LESS THAN (40)

                        TABLESPACE dept30ts,
                  PARTITION deptxx

                        VALUES LESS THAN (MAXVALUE)

                        TABLESPACE deptxxts)
SQL> /

138:   What does the MAXVALUE parameter mean in the data partitioning?

A:   It means as large as the column can hold.

 

139:   How do you analyze a partition table?

A:   SQL> ANALYZE TABLE p_emp COMPUTE STATISTICS;

140:   What does the USER_TAB_PARTITIONS view contain?

A:   A user can query its partitions table’s information that was created by the user.

 

141:   Write a query to list the accounting employees from the partition table. Use the partition option.

A:   SQL> SELECT * FROM p_emp PARTITION (dept10);

142:   Write a query to list employee number 7900 from the sales department?

A:         SQL> SELECT * FROM p_emp PARTITION (dept30)
                        WHERE empno = 7900
            SQL> /

143:    How do you create a local partition index?

A:   SQL> CREATE INDEX p_emp_ind ON p_emp (deptno) LOCAL;

144:   How do you analyze a partition table index?

A:   SQL> ANALYZE INDEX p_emp_ind COMPUTE STATISTICS;

145:   What does the USER_IND_PARTITIONS view contain?

A:   It contains information in regard to the user’s partition indexes.

 

146:   What does the ROLLUP operator?

A:   The ROLLUP operator returns both ‘regular rows’ and ‘super-aggregate rows.’ Super-aggregate rows are rows that contain a sub-total value.

 

 

147:   What does the CUBE function?

A:   The CUBE operator returns cross-tabulation values, thus produces totals in all possible dimensions, and is used for warehousing aggregated data reports.

 

148:   What are the differences between the CUBE and ROLLUP functions?

A:   See the output…

 

149:   What environments may use the CUBE and ROLLUP functions most?

A:    Warehousing.

 

150:   Write a query to list an aggregation sum report for each job, in each year, using the ROLLUP grouping option.

A:         SQL> SELECT year, job, SUM(sal), COUNT(*)
                        FROM emp
                        GROUP BY ROLLUP (year, job)