All items are atomic, all tables
have a primary key, every row is determined by its primary key, there are no
duplicate rows, every column is dependent on ONLY the primary key.
2. What are cascading triggers?
Executing one trigger may cause
another trigger to also be executed.
3. What are snapshots?
Snapshots are copies of remote
data, based upon queries. In their simplest form, they can be thought of as a
table created by a command such as:
create
table t as select * from z;
4. What Oracle package allows you to schedule one-off or
recurring jobs in your database?
DBMS_JOB
5.
What packages has Oracle provided for use by developers?
The DBMS_ series of packages, i.e.
DBMS_JOB
DBMS_OUTPUT,
DBMS_UTILITY,
UTL_FILE,
UTL_HTTP,
UTL_SMTP,
UTL_TCP,
DBMS_SQL,
DBMS_PIPE,
DBMS_TRANSACTION,
DBMS_LOCK,
DBMS_ALERT,
DBMS_DDL.
6. What are some
methods for transferring a table from one schema to another?
Export-Import, Create table as .. Select
.., Copy
7. What happens if a tablespace clause is left off a
primary key constraint?
This results in the index
automatically generated being placed in the users' default tablespace, which is
usually the same tablespace as where the table is being created which can cause
performance problems.
8. Where is most tuning done?
80-90 percent at application
level, 10-20 percent at database level
9. What is a mutating table?
A mutating table is a table that is in the
process of being modified by an UDPATE, DELETE or INSERT statement. For
example, if your trigger contains a select statement or an update statement
referencing the table it is triggering off of you will receive the error.
10. What is a bind variable and why is it important?
A bind variable is a placeholder in a query.
The way the Oracle shared pool (a very important shared memory data structure)
operates is predicated on developers using bind variables.
11. How are reads and writes handled in Oracle that is
different than almost every other database?
Reads are not blocked by writes.
12. Why should you care about the NLS_DATE_FORMAT?
Because its' value (dd-mon-yy or
dd-mon-rr) determines the results of your date arithmetic when you are dealing
with years of 99 and 00..nn.
13. What is the purpose of the SUBSTR string function?
To return a specified substring
from a string.
14.
What's the difference between an equijoin and a self-join?
An equijoin
does an equality test between two fields in two different tables;
a self join
does the same thing on a copy of the same table.
15. In a Select statement, what is the
difference between a & and &&?
Both pass in
values at runtime, but if the && is used the user will not be bothered
with a second prompt for the value.
16. What is Oracle*Alert?
Oracle*Alert
is licensed as an Application but it functions as an extension of AOL in that
it supplements the features of all the Applications. Oracle Alert is an
end-user tool, and so individual alerts enjoy support from Oracle at the same
level as, for instance, FSG reports in Oracle General Ledger.
17. What is the TRANSLATE function?
TRANSLATE is a simple function
that does an orderly character-by-character substitution in a string. The
format is TRANSLATE (string, if, then). Example: select TRANSLATE (7671234,
234567890,'BCDEFGHIJ') from DUAL; The result would be: GFG1BCD. I have found
this useful during some data migrations where special characters needed to be
translated.
18. What are PL/SQL Tables (or Arrays)?
This is dependent upon your Oracle version.
PL/SQL Tables have only one dimension, but after PLSQL 2.3 that dimension could
be a record. Their main advantage is that when relatively small tables must be
constantly consulted, if they can be put in memory via a PL/SQL table,
performance can be enhanced.
19. What's the most important 'Best Practice' guideline you
follow?
Ask for Help if you find yourself
spending more than 30 minutes to solve a problem. I follow this advice when at
a client site; when I'm at home, I act like the Duracell bunny and just keep
going and going.
20. What's another Best Practice?
Make code reviews a regular part
of your development process.
21. Describe the PL/SQL Block structure.
Declare
Begin
Exception
End
22. Describe a nested PL/SQL Block.
Declare Begin Begin End; Begin
End; End;
23. What is %TYPE used for?
v_min_bal sales.balance%TYPE := 10.00;
- the var v_min_bal takes on the Type of
sales.balance and the value of 10.00.
24. What is %ROWTYPE used for?
Assigns a row to a table. Similar
to %TYPE but for a record, not just a field.
25. What is an anonymous block?
A stored procedure without a name.
26. Is PL/SQL truly compiled when stored in the database or
is it interpreted?
PL/SQL on the server is run in
much the same fashion as Java is run anywhere. PL/SQL is compiled into PCode
and the PCode is interpreted at runtime.
27. What is the
purpose of the PL/SQL FETCH command?
The FETCH command retrieves values returned by
the cursor from the active set into the local variables.
28. What does
truncating a table do?
It deletes the data from the
table.
29. What else may truncating a table do?
It can reset the high water mark
for a table if the REUSE STORAGE clause is not used.
30. Why is the high water mark important?
The high water mark is used in
association with each individual table and tells Oracle
1. where to start loading data
during a SQL*Loader process
2. how far to scan a table's
information when doing a full-table scan.
31. What does the TO_NUMBER function do?
It converts VARCHAR2 values to
numbers.
32. What is the default length of the CHAR column?
1
33. What is the purpose of a referential integrity
constraint?
Enforce the rule that a child
foreign key must have a valid parent primary key.
34. What is the purpose of the SQL*Plus command GET?
Get the contents of a previously
saved operating system file into the buffer.
35. What is the order of the stages of the system
development cycle?
1.
Strategy and analysis
2. Design
3. Build
and document
4.
Transition
5.
Production.
36. In a SELECT statement, which character is used to pass
in a value at runtime?
The '&' character or the
'&&' characters.
37. What is DNS? What does it stand for and why do we care
that it exists?
Dynamic Name Server is what allows us to type
in names instead of IP addresses to get to Web servers, use Telnet, FTO, etc.
38. What are realms?
Application security in Oracle Applications is
maintained and managed by assigning responsibilities, excluding attributes, and
securing attributes to users. Internet Procurement 11i uses a security realm as
an additional layer for application security. A security realm is a list of
objects (item source or a category) to which a user is granted access.
39. What occurs during the production phase of the system
development cycle?
Perform normal routine
maintenance.
40. A database trigger is fired automatically when what is
executed?
DML statement
41. In a PL/SQL block, what needs to be followed with a
semicolon?
All SQL statements, all PL/SQL statements and
the END clause
42. What character do you type to execute an anonymous
block?
/
43. What data type is used to store large binary objects
outside the database?
The BFILE data type
44. Which variable type accepts only character strings of a
specified length?
CHAR
45. Which variable type accepts any length of character up
to 32767 bytes?
VARCHAR2
46. What operator is used to assign a value to a variable
that doesn't have a typical value? :=
47. What keyword is used to assign a value to a variable
that has a typical value? DEFAULT
48. How frequently are block declared variables
initialized?
Every time a block is executed
49. With which symbol do you prefix a bind variable when you reference it in PL/SQL?
:
50. What are two statements that are true about the INTO
clause?
1. You have to specify the same
number of variables in the INTO clause as the values returned by the SELECT
statement.
2. The data types of the variables
specified in the INTO clause need to correspond with the values returned by the
SELECT statement.
51. What keyword is used when you populate a host variable
from the SQL prompt?
The VARIABLE keyword
52. How do you end each SQL statement in a PL/SQL block?
With a ;
53. Can you have more than one transaction in a PL/SQL
block?
Yes
54. What is common among these cursor attributes;
SQL%FOUND, SQL%NOTFOUND, SQL%ISOPEN?
They are all Boolean attributes.
55. What does it mean when the cursor attribute SQL%FOUND returns
the result TRUE?
The most recent SQL statement
issued affects one or more rows.
56. What are two true statements concerning the index in a
FOR loop?
1. You can't reference it outside
the loop.
2. You can use an expression to
reference its existing value within the loop.
57. How do you begin defining a record type?
TYPE emp_record_type IS RECORD
58. Do PL/SQL records have a predefined data type?
No.
59. Give an example of the correct syntax to reference a
row in a PL/SQL table. Dept_table(15)
60. The primary key of a PL/SQL table must be of what data
type?
Scalar
61. What is the term used for the rows produced by a query?
Active set
Active set
62. Name three things that are
true about explicit cursors.
1.
They are manipulated through specific statements in the block's executable
actions.
2. They individually process each row
returned by a multi row SELECT statement.
3. They need to be declared and named
before they can be used.
63. Name two things true about
cursor FOR loops.
1. They process rows in an explicit cursor.
2. They automate the processing as
the cursor is automatically opened and the rows fetched for each iteration in
the loop, and the cursor is closed when all the rows have been processed.
64. What are four attributes that provide status information
about a cursor?
1.
%ISOPEN
2.
%NOTFOUND
3.
%FOUND
4.
%ROWCOUNT
65. Describe at least one way explicit cursor attributes
are used.
You can use the explicit cursor
attributes to test the success of each fetch before any further references are
made to the cursor.
66. What clause do
you use to apply updates and deletes to the row currently being addressed,
without having to explicitly reference the ROWID?
67. How long does the Oracle server wait if it cannot
acquire the locks on the rows it needs in a SELECT FOR UPDATE?
indefinitely
68. Name three things about using cursors with parameters.
1. You can use parameters to pass
values to a cursor when it is open.
2. Parameters are used in a query
when it executes.
3. In the OPEN statement, each
formal parameter in the cursor declaration must have a corresponding real
parameter.
69. Name three things true about trapping exceptions
1. When
an exception occurs, PL/SQL processes only one handler before leaving the
block.
2. If
you use the OTHERS clause, it should be placed last of all the
exception-handling clauses.
3.
Exceptions cannot appear in assignment statements or SQL statements.
70. Describe two aspects about exceptions.
1. Once an Oracle error occurs,
the associated exception is raised automatically.
2. You can raise an exception
explicitly by issuing the RAISE statement within the block.
71. What exception occurs when the conversion of a
character string to number fails?
INVALID_NUMBER
72. Name three things about user-defined exceptions.
1. When defining your own exceptions, you need
to declare them in the DECLARE section of a PL/SQL block.
2. They are raised explicitly with
RAISE statements.
3. You need to reference your
declared exception within the corresponding exception-handling routine.
73. What's another Best Practice?
Set standards and guidelines for your
application before anyone starts writing code.
1. Selection of development tools
2. How SQL is written in PL/SQL
code.
3. How the exception handling
architecture is designed.
4. Processes for code review and
testing.
74. Explain the relationship between a Conceptual Data
Model (CDM) and a Physical Data Model (PDM).
Most of the objects in the logical
model correspond to a related object in the physical model, e.g. the logical
model contains entities, attributes, and key groups, which are represented in
the physical model as tables, columns, and indexes, respectively. The CDM
allows the designer to concentrate solely on defining the objects in the
information system and the relationships between them, without having to
consider the numerous parameters associated with the physical implementation
such as data integrity constraints, data access speed and data storage
efficiency. The CDM thus provides a clear and succinct picture of the information
system, which is independent of the targeted DBMS. A single CDM may therefore
be associated with a number of PDMs targeting different DBMSs. The conceptual
level schema, should present to the user a simple, physical
implementation-independent clear view of the format of the data sets and their
descriptions. A Conceptual Data Model lays the foundation for building shared
databases and re-engineering the business.
75. Elaborating on 74, describe conceptual vs logical vs
physical designs.
Conceptual database design is the process of
building a model of the essential part of the enterprise business process and
the used information, independent of all physical considerations. Logical
database design - The process of constructing a model of information used in an
enterprise based on a specific data model, using natural objects of information
and natural associations between them. The model of information is independent
of a particular implementation and other physical consideration. Physical
database design - The process of producing a description of the implementation
of the database on secondary storage. It describes the storage structures and
access methods used to achieve efficient access to the data.
76. What is a pseudo-column?
A pseudo-column
is a "column" that yields a value when selected, but which is not an
actual column of the table. 77. What are the more common pseudo-columns?
sequence.CurrVal, sequence.NextVal, RowID, RowNum, SysDate, UID, User
78. What is the difference between VARCHAR and VARCHAR2?
The VARCHAR data type is currently
synonymous with the VARCHAR2 data type. It is recommended that you use VARCHAR2
rather than VARCHAR. In a future version of Oracle, VARCHAR might be a separate
data type used for variable length character strings compared with different
comparison semantics.
79. Give an example of overloaded Built-in functions.
date_string := TO_CHAR (SYSDATE,
'MMDDYY');
number_string := TO_CHAR (10000);
If overloading was not supported in PL/SQL (TO_CHAR is a function in the
STANDARD package), then two different functions would be required to support
conversions to character format.
80. What is the difference between call and execute sql*+
commands.
The CALL statement is SQL(and only
understands SQL types). EXEC is really shorthand for begin/end;.
81. How can I check for duplicates?
select count(*), job from emp
group by job having count(*) > 0; 4 CLERK 4 SALESMAN 3 MANAGER 2 ANALYST 1
PRESIDENT
82. What is another name for ref cursors?
cursor variables
83. What data type column can not be used with INTERSECT?
LONG
84. When is the MINUS keyword used?
To remove those rows which
are retrieved by one SELECT from those
retrieved by another SELECT statement.
85. Give an example of the MINUS keyword.
List the numbers of all managers
who do not hold advanced degrees. SELECT MGRNO FROM DEPT WHERE MGRNO IS NOT
NULL MINUS SELECT EMPNO FROM EMP WHERE EDLEVEL >= 18;
86. When is the INTERSECT keyword used?
To return only those rows that are
the result of two or more SELECT statements.
87. Give an example of the INTERSECT keyword. List the
numbers of all managers who do not hold advanced degrees.
SELECT MGRNO FROM DEPT WHERE MGRNO
IS NOT NULL INTERSECT SELECT EMPNO FROM EMP WHERE EDLEVEL < 18;
88. Write a query to find the
duplicate record(s) of column a, b and c in a table of columns a..z. SELECT
count(*), a, b, c FROM t GROUP BY a, b, c HAVING COUNT(*) > 1; 89. Give an
example of the NOT keyword. SELECT c FROM t WHERE c != 'x'; SELECT c FROM t
WHERE NOT c = 'x';
90. Give an example of the LIKE keyword.
SELECT c FROM t WHERE c LIKE '_EU%L'; ie the first character can be any
character, the next two must be EU and the last must be L. Any number of
chararcters or numbers could be between the U and L.
91. What is SQLCODE?
A predefined symbol that contains
the Oracle error status of the previously executed PL/SQL statement. If a SQL
statement executes without errors, SQLCODE is equal to 0.
92. What is SQLERRM?
A PL/SQL symbol that contains the
error message associated with SQLCODE. If a SQL statement executes
successfully, SQLCODE is equal to 0 and SQLERRM contains the string ORA-0000:
normal, successful completion
93. What is ROWNUM?
A pseudocolumn that indicates the
order of the retrieved row. The ROWNUM for the first returned row is 1, ROWNUM
can limit the number of rows that are returned by a query.
94. What are the benefits of using the PLS_INTEGER Datatype
in PL/SQL?
If you have a whole-number
counter, for example in a loop or record counter, consider using a datatype of
PLS_INTEGER instead of INTEGER or NUMBER. When declaring an integer variable,
PLS_INTEGER is the most efficient numeric datatype because its values require
less storage than INTEGER or NUMBER values, which are represented internally as
22-byte Oracle numbers. Also, PLS_INTEGER operations use machine arithmetic, so
they are faster than BINARY_INTEGER, INTEGER, or NUMBER operations, which use
library arithmetic. Jayanta Sengupta Lowell, Massachusetts
95. Explain the difference between NVL and NVL2.
NVL (expr1, expr2);
NVL - If expr1 is null then return
expr2 else return expr1.
NVL2 (expr1, expr2, expr3)
NVL2 - If expr1 is not null then
the function will return expr2. Otherwise, the function will return expr3. The
expr1 can have any datatype and arguments expr2 and expr3 can be of any
datatype other than LONG. The datatype of the return value is that of expr2.
96. Describe RTRIM.
RTRIM (string [,'set']) RTRIM is
the opposite of RPAD and similar to LTRIM. The function removes characters from
the right-hand portion of a string. The string passed as the first parameter is
returned with all characters contained in the string passed as the second
parameter removed from the right of the last character not found in the remove
string. The second parameter is optional and defaults to a single space.
rtrim('ORACLE UPDATE ') --> 'ORACLE UPDATE'
rtrim('ORACLE UPDATE','EDATPU')
--> 'ORACLE '
rtrim('ORACLE UPDATE',' EDATPU')
--> 'ORACL'
97. Describe UNION and UNION ALL.
UNION returns distinct rows
selected by both queries while UNION ALL returns all the rows. Therefore, if
the table has duplicates, UNION will remove them. If the table has no
duplicates, UNION will force a sort and cause performance degradation as
compared to UNION ALL.
98. What is 1st normal form?
Each cell must be one and only one
value, and that value must be atomic: there can be no repeating groups in a
table that satisfies first normal form.
99. What is 2nd normal form?
Every nonkey column must depend on
the entire primary key.
100. What is 3rd normal form? (another explanation than #1)
No nonkey column depends on
another nonkey column.
101. What is 4th normal form?
Fourth normal form forbids
(prohibits, prevents) independent one-to-many relationships between primary key
columns and nonkey columns.
102. What is 5th normal form?
Fifth normal form breaks tables
into the smallest possible pieces in order to eliminate all redundancy within a
table. Tables normalized to this extent consist of little more than the primary
key.
103. What does pragma mean to Oracle?
A pragma is simply a compiler
directive, a method to instruct the compiler to perform some compilation
option.
104. What is a Latch?
A Latch is a low level
serialization mechanism that (released as quickly as it is acquired) protects
shared data structures. A process acquires and holds the latch as long as the
data structure is in use. The basic idea is to prevent concurrent access to
shared data structures in the SGA. In case the process dies without releasing
the latch, the PMON process will clean up the lock on the data structure and
release the latch. If a process is not able to obtain a latch, it must wait for
the latch to be freed up by the process holding it. This causes additional
spinning (looking for availability at fixed intervals of time) of the process,
thereby causing extra load on the CPU. This process will spin until the latch
is available. A dba has to monitor the latches for contention and make sure
that CPU cycles are not being burnt on process spinning.
105. Does ROLLUP work with multiple columns?
The ROLLUP feature can in fact be
applied to multiple columns. The result is multiple levels of rollup, as illustrated
here: select deptno, job, count(*), grouping(deptno), grouping(job) from emp
group by rollup(deptno, job);
DEPTNO JOB COUNT(*) GROUPING(DEPTNO)
GROUPING(JOB) ---- ---- ---- ---- ---- 10 CLERK 1 0 0 10 MANAGER 1 0 0 10
PRESIDENT 1 0 0 10 3 0 1 20 ANALYST 2 0 0 20 CLERK 2 0 0 20 MANAGER 1 0 0 20 5
0 1 30 CLERK 1 0 0 30 MANAGER 1 0 0 30 SALESMAN 4 0 0 30 6 0 1 14 1 1 As shown
in this example, we're able to count the employees by 1) department and job; 2)
department; and 3) grand total.
106. What is an inline view?
A subquery in the from clause of
your main query.
107. Give an example of an inline view and Top-N Query.
SELECT ename, job, sal, rownum
FROM (SELECT ename, job, sal FROM emp ORDER BY sal) WHERE rownum <= 3;
SMITH CLERK 800 1
JAMES CLERK 950 2
ADAMS CLERK 1100 3
108. What SQL*Plus command is useful for determining
whether the "N rows selected" message will appear?
Feedback
109. What SQL*Plus keyword is used for defining formats for
how SQL*Plus displays column information?
Set
110. This phrase describes a query that feeds one row of
results to a parent query for the purpose of selection when the exact where
clause criteria is not known?
Single-row subquery.
111. Use of what command requires that you first run the
plustrce.sql script?
Autotrace
112. The database for an international athletic competition
consists of one table, ATHLETES, containing contestant name, age, and
represented country. To determine the youngest athlete representing each
country, how do you write the code?
scott@PO816>SELECT name, country, age FROM athletes
WHERE (country, age ) IN ( SELECT country, min(age) FROM athletes GROUP BY
country);
113. What is a single-row subquery?
The main query expects the
subquery to return only one value.
114. What is an inline view?
A subquery in a from clause used
for defining an intermediate result set to query from.
115. What does AUTOTRACE do?
Allows us to see the execution plan of the queries we've
executed and the resources they used,
without having to use the EXPLAIN PLAN command.
116. What does SQL_TRACE do?
Enables logging of all application
SQL, performance stats and query plan used.
117. What does TKPROF do?
Formats the raw trace files into a
readable report.
118. What are the two main index types that Oracle uses?
B*Tree and Bitmap
B*Tree and Bitmap
119. When are Bitmap indexes appropriate?
In situations of low cardinality
data, i.e. data with few distinct values.
120. What is a top-n query?
select * from ( select ename from
emp order by sal ) where rownum <= 3;
In general it refers to getting
the top-n rows from a result set.
121. What is PostgreSQL?
PostgreSQL is a sophisticated
Object-Relational DBMS, supporting almost all SQL constructs, including
subselects, transactions, and user-defined types and functions. It is the most
advanced open-source database available anywhere. Commercial Support is also
available.
122. What are the three main reasons for partitioning a
database?
1. To increase availability
(derived from the fact that partitions are independent entities).
2. To ease administration burdens
(derived from the fact that performing operations on small objects is
inherently easier, faster, and less resource intensive than performing the same
operation on a large object).
3. To enhance DML and query
performance (potential to perform parallel DML).
123. What are the two types of cursors?
Implicit (Oracle's) and explicit
(yours).
124. Does the order of stored procedures in a package matter?
No comments:
Post a Comment