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> /
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> /
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> /
(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
(7902,
employee_kids_table
(employee_kids('David','08-AUG-01'),
employee_kids('Peter','10-JUN-88'),
employee_kids('Mark','30-OCT-92')
)
)
SQL> /
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
FROM
THE(SELECT
kids FROM emp_family WHERE empno = 7788)
SQL> /
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');
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> /
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> /
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.
- The last name and
first name should be not null.
- Make a check
constraint to check the department number is between 9 and 100.
- Make a primary
constraint on the employee ID column.
- Make a foreign key on
the department number column.
- Use the "delete
cascade" to delete all records if parent gets deleted.
- 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,
(SELECT dname,
SUM (sal)
AS totals
FROM emp NATURAL JOIN dept
FROM emp NATURAL JOIN dept
GROUP BY dname)
SELECT dname, totals
SELECT dname, totals
FROM summary_totals
WHERE totals > (SELECT SUM (totals)*1/3
WHERE totals > (SELECT SUM (totals)*1/3
FROM summary_totals)
ORDER BY totals DESC
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)
WHERE ROWID IN (SELECT MAX(ROWID)
FROM dup_emp
GROUP BY empno
HAVING COUNT (empno) > 1)
SQL> /
A: Deletes duplicated records.
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> /
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;
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
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.
No comments:
Post a Comment