oracle interview questions part 4

151:   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> /

152:   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.

 

153:   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.

 

154:   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> /

155:   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;

156:   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> /

157:   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> /

158:   What is the constructor?

A:   The constructor creates an empty nested table as opposed to leaving it null. Notice that without using the constructor, it is not possible to refer to the nested table with the "THE" clause.

159:   What is the ‘THE’ sub-query?

A:   To query a nested table you should use the "THE" clause. Also, the "THE" sub-query is used to identify the nested table to INSERT INTO.

160:   How do you query a record using the ‘THE’ sub-query?

A:         SQL> SELECT name
                        FROM

                        THE(SELECT kids FROM emp_family WHERE empno = 7788)
            SQL> /

161:   What is a nested table?

A:   It is a table within a table.

 

162:   How do you insert a record to a nested table?

A:         SQL> INSERT INTO

                        THE(SELECT kids FROM emp_family
                        WHERE empno = 7900)
                        VALUES ('Sue','10-DEC-99');

163:   How do you update a record to nested table?

A:         SQL> UPDATE emp_family
                        SET kids = employee_kids_table(
                        employee_kids('Sara','08-OCT-88'))
                        WHERE empno = 7788
            SQL> /

164:   How do you add a unique index to a nested table?

A:         SQL> CREATE UNIQUE INDEX i_nested_employee_kids_table
                        ON nested_employee_kids_table(nested_table_id,name)
            SQL> /

165:   What is a data replica?

A:   A duplicated data in a different location.

 

166:   What is the difference between a materialized view and a materialized view log?

A:   The Materialized view is a real duplicated data from a primary table but the materialized view log is an on going logs generated due to the table changes after the last refresh.

 

167:   What is an OID (Object ID)?

A:   It is a unique ID assigned to an object by Oracle.

 

168:   How do you retrieve an object ID?

A:   SQL> SELECT OWNER, TYPE_OID FROM DBA_TYPES

      WHERE TYPE_NAME LIKE 'ADDRESS%';

 

169:   How do you use an object ID to create an object type?

A:         SQL> CREATE OR REPLACE TYPE address_book_type_object

                        OID ‘XXXXXXXXXXXXXXXXXXXXX’

                        AS OBJECT (

                        id_address NUMBER(1),

                        address VARCHAR2(20));

 

170:   What is the relationship between primary and foreign keys?

A:   The relationships between two tables are normally established by defining primary or foreign keys. A primary key has the immutable responsibility of serving as a unique identifier in a table. A foreign key is a column that refers to the primary key of another table. To join two tables, a “where clause” is used to set up a table relationship between primary and foreign keys.

 

171:   What is a composite index?

A:   A primary key can be composed of more than one column.  We call it a composite index.

 

172:   What is the result of 100 + NULL?

A:   NULL value.

 

173:   Write a query to concatenate the customers’ last name and first name separated by comma.

A:   SELECT last_name || ‘, ‘ || first_name

            as “Full Name”

            FROM customers

            /

 

174:   Query the employees name and their department name using the “DECODE” function.  If the department number is 10 then print "accounting.” If the department number is 20 then print "research," or if the department number is 30 then print "sales."  Anything else prints others.

A:   SELECT ename, DECODE (deptno, 10, 'Accounting',

                                                                          20, 'Research',

                                                                          30, 'Sales',

                                                                         'Others') AS "Department"

          FROM emp

            /

 

175:   Query the department number and their total salaries that have more than 5 employees working in their department.

A:   SELECT deptno, SUM(sal)

           FROM emp

           GROUP BY deptno

           HAVING count(*) > 5

            /

 

176:   query the customer names which have more than four orders.

A:   SELECT name FROM customer c

           WHERE exists (SELECT 'T' FROM ord

                 WHERE custid = c.custid

                 GROUP BY custid

                 HAVING count(*) > 4)

            /

 

177:   Create an employee table that contains five columns:

Such as Employee Id, last name, First name, Phone number and Department number with the following constraints.

7. The last name and first name should be not null.
8. Make a check constraint to check the department number is between 9 and 100.
9. Make a primary constraint on the employee ID column.
10. Make a foreign key on the department number column.
11. Use the "delete cascade" to delete all records if parent gets deleted.
12. Use the "phone number" as a unique key.

A:   CREATE TABLE employee

(empid            NUMBER(10),

lastname            VARCHAR2(20) not null,

firstname            VARCHAR2 (20) not null,

phone_no            VARCHAR2 (15),

deptno              NUMBER(2) CHECK (deptno BETWEEN 9 AND 100),

constraint             pk_employee_01 PRIMARY KEY (empid),

constraint             fk_dept_01 FOREIGN KEY (deptno)

references       dept (deptno) ON DELETE CASCADE,

constraint             uk_employee_01 UNQUE (phone_no))

/

 

178:   What is the difference between the delete statement and the truncate statement?

A:   On the DELETE statement the watermark will not change. But using the TRUNCATE statement will change the watermark to the beginning of the table.

 

179:   Copy the “EMP” table to another table and name the new table "employee."  In the new employee table use the employee name, job, commission and department number.

A:   CREATE TABLE employee

           AS SELECT ename, job, comm, deptno

                 FROM emp

            /

 

180:   Reorganize the “employee_indx” index table.

A:   ALTER INDEX employee_indx REBUILD

            /

 

181:   What is the difference between public and private synonym?

A:   You create synonym so that the users don’t need to type schema name to a table when they query the table. The Public Synonym is available to all database users but the Private Synonym is available only to the owner of synonym.

 

182:   Can you have multiple SQL statements in the afiedt.buf file?

A:   No.

 

183:   How do you execute or run a SQL script?

A:   SQL> @my_sql_script; or start my_sql_script;

 

184:   Write a query to list all the department names that their total paid salaries are more than 1/3 of the total salary of the company.

A:   SQL> WITH summary_totals AS
                       (SELECT dname,

                          SUM (sal) AS totals
                               FROM emp NATURAL JOIN dept

                               GROUP BY dname)
                     SELECT dname, totals

            FROM summary_totals
                     WHERE totals > (SELECT SUM (totals)*1/3

                                           FROM summary_totals)
                     ORDER BY totals DESC

SQL>/

185:   What is a Materialized View?

A:   A materialized view (MVIEW) is a replica of a target master from a single point in time. 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.

 

186:   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 duplicated records.

 

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

A:   It is the maximum possible value that can be store into a column.

 

188:   What does the following SQL statement?

SQL> INSERT INTO THE(SELECT kids FROM emp_family

      WHERE empno = 7900)
      VALUES ('Sue','10-DEC-99')
SQL> /

A:   Inserts a record to a nested object in a table.

189 : What are the various types of queries ?

Answer: The types of queries are:

Normal Queries

Sub Queries

Co-related queries

Nested queries

Compound queries

190 :What is a transaction ?

Answer: A transaction is a set of SQL statements between any two COMMIT and ROLLBACK statements.

191: What is implicit cursor and how is it used by Oracle ?

Answer: An implicit cursor is a cursor which is internally created by Oracle.It is created by Oracle for each individual SQL.

192: Which of the following is not a schema object : Indexes, tables, public synonyms, triggers and packages ?

Answer: Public synonyms

193:  What is PL/SQL?

Answer: PL/SQL is Oracle's Procedural Language extension to SQL.The language includes object oriented programming techniques such as encapsulation, function overloading, information hiding (all but inheritance), and so, brings state-of-the-art programming to the Oracle database server and a variety of Oracle tools.

194: Is there a PL/SQL Engine in SQL*Plus?

Answer: No.Unlike Oracle Forms, SQL*Plus does not have a PL/SQL engine.Thus, all your PL/SQL are send directly to the database engine for execution.This makes it much more efficient as SQL statements are not stripped off and send to the database individually.

195:  Is there a limit on the size of a PL/SQL block?

Answer: Currently, the maximum parsed/compiled size of a PL/SQL block is 64K and the maximum code size is 100K.You can run the following select statement to querythe size of an existing package or procedure. SQL> select * from dba_object_size where name = 'procedure_name'

196 Can one read/write files from PL/SQL?

Answer: Included in Oracle 7.3 is a UTL_FILE package that can read and write files.The directory you intend writing to has to be in your INIT.ORA file (see UTL_FILE_DIR=...parameter).Before Oracle 7.3 the only means of writing a file was to use DBMS_OUTPUT with the SQL*Plus SPOOL command.
DECLARE
fileHandler UTL_FILE.FILE_TYPE;
BEGIN
fileHandler := UTL_FILE.FOPEN('/home/oracle/tmp', 'myoutput','W');
UTL_FILE.PUTF(fileHandler, 'Value of func1 is %sn', func1(1));
UTL_FILE.FCLOSE(fileHandler);
END;

197: How can I protect my PL/SQL source code?

Answer: PL/SQL V2.2, available with Oracle7.2, implements a binary wrapper for PL/SQL programs to protect the source code.This is done via a standalone utility that transforms the PL/SQL source code into portable binary object code (somewhat larger than the original).This way you can distribute software without having to worry about exposing your proprietary algorithms and methods.SQL*Plus and SQL*DBA will still understand and know how to execute such scripts.Just be careful, there is no "decode" command available. The syntax is: wrap iname=myscript.sql oname=xxxx.yyy

198:Can one use dynamic SQL within PL/SQL? OR Can you use a DDL in a procedure ? How ?

Answer: From PL/SQL V2.1 one can use the DBMS_SQL package to execute dynamic SQL statements.
Eg: CREATE OR REPLACE PROCEDURE DYNSQL AS
cur integer;
rc integer;
BEGIN
cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cur,'CREATE TABLE X (Y DATE)', DBMS_SQL.NATIVE);
rc := DBMS_SQL.EXECUTE(cur);
DBMS_SQL.CLOSE_CURSOR(cur);
END

199: What are the various types of Exceptions ?

Answer: User defined and Predefined Exceptions.

200: Can we define exceptions twice in same block ?

Answer: No.

201:  What is the difference between a procedure and a function ?

Answer: Functions return a single variable by value whereas procedures do not return any variable by value.Rather they return multiple variables by passing variables by reference through their OUT parameter.