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