Thursday 30 August 2012

SQL Interview Questions and Answers Part7

Q: 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.
Q: 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.
Q: 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.
Q: 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.
Q: 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.
Q: What does the USER_SNAPSHOT_LOGS view contain?
A: It shows if our log was created successfully and its name (MLOG$_EMP).
Q: 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> /
Q: Who can create a materialized view?
A: The one that was granted the CREATE MATERIALIZED VIEW privilege.
Q: What does the USER_MVIEWS view contain?
A: It contains all the Materialized Views’ information that were created by the
user.
Q: How do you refresh a materialized view?
A: SQL > EXECUTE dbms_snapshot.refresh('mv_sal','C');
Q: 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.
Q: What does the USER_JOBS view contain?
A: It contains all users’ jobs in the Oracle queue.
Q: How do you remove a job from the Oracle Job Queue?
A: SQL > EXECUTE dbms_job.remove(job_number);
Q: 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;

Q: What does the BREAK ON clause in SQLPLUS?
A: It builds a break on a column.
Q: What do the REPHEADER and REPFOOTER commands in SQLPLUS?
A: They make a report header and footer.
Q: 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.
Q: 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.
Q: 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.
Q: 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.
Q: What does the MINUS statement in the SQL statement?
A: It will query all the records that are not matching against your base table.
Q: Why it is important to eliminate duplicate records?
A: To keep your database integrity.
Q: 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.
Q: 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.
Q: When should you use data partitioning?
A: When you have a huge data file and can be classified to some partitions.
Q: What is the advantage of using a data partitioning?
A: It is faster to access. It is easier to maintain.
Q: What is a partition key?
A: It is used to separate data and associates them to their own assigned
tablespace.
Q: 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.
Q: 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.
Q: What are the differences between local and global indexes?
A: In the local index you don’t define explicitly partition range.
Q: 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.
Q: 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.
Q: 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 > /
Q: What does the MAXVALUE parameter mean in the data partitioning?
A: It means as large as the column can hold.
Q: How do you analyze a partition table?
A: SQL > ANALYZE TABLE p_emp COMPUTE STATISTICS;
Q: What does the USER_TAB_PARTITIONS view contain?
A: A user can query its partitions table’s information that was created by the
user.
Q: Write a query to list the accounting employees from the partition table. Use
the partition option.
A: SQL > SELECT * FROM p_emp PARTITION (dept10);
Q: Write a query to list employee number 7900 from the sales department?
A: SQL > SELECT * FROM p_emp PARTITION (dept30)
WHERE empno = 7900
SQL> /
Q: How do you create a local partition index?
A: SQL > CREATE INDEX p_emp_ind ON p_emp (deptno) LOCAL;


Q: How do you analyze a partition table index?
A: SQL > ANALYZE INDEX p_emp_ind COMPUTE STATISTICS;
Q: What does the USER_IND_PARTITIONS view contain?
A: It contains information in regard to the user’s partition indexes.
Q: 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.
Q: 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.
Q: What are the differences between the CUBE and ROLLUP functions?
A: See the output…
Q: What environments may use the CUBE and ROLLUP functions most?
A: Warehousing.
Q: 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)
SQL> /
Q: Write a query to list an aggregation sum report for each job, in each year,
using the CUBE grouping option.
A: SQL > SELECT year, job, SUM (sal), COUNT(*)
FROM emp
WHERE deptno = 20
GROUP BY CUBE (year, job)
SQL> /
Q: What is an object type?
A: The object type in the Oracle database is like the class eliminate in the
C++ developer tool or any object oriented tool.
Q: What is a collection object?
A: The collection object in the Oracle database is like a nested table and a
variable array in a table.
Q: Create an object type with two columns to hold the employee's child name and
date of birth and name it employee_kids .
A: SQL > CREATE TYPE employee_kids AS OBJECT (
NAME VARCHAR2 (30),
dob DATE
)
SQL> /
Q: Create a table type using employee_kids and name it employee_kids_table.
A: SQL > CREATE TYPE employee_kids_table
IS TABLE OF employee_kids;
Q: Create the emp_family table containing the kid’s column with a type of
employee_kids_table.
A: SQL > CREATE TABLE emp_family
(empno NUMBER,
kids employee_kids_table)
NESTED TABLE kids STORE AS nested_employee_kids_table
SQL> /
Q: How do you insert a record in the object type?
A: SQL > INSERT INTO emp_family VALUES
(7902,
employee_kids_table(employee_kids('David','08-AUG-01'),
employee_kids('Peter','10-JUN-88'),
employee_kids('Mark','30-OCT-92')
)
)
SQL > /

No comments:

Post a Comment