1. How do you generate file output from SQL?
By use of the SPOOL command
The SET options FEEDBACK and VERIFY can be set to OFF.
3. How do you prevent output from coming to the screen?
The SET option TERMOUT controls output to the screen. Setting TERMOUT OFF turns off screen output. This option can be shortened to TERM.
4. How do you set the number of lines on a page of output? The width?
The SET command in SQLPLUS is used to control the number of lines generated per page and the width of those lines, for example SET PAGESIZE 60 LINESIZE 80 will generate reports that are 60 lines long with a line width of 80 characters. The PAGESIZE and LINESIZE options can be shortened to PAGES and LINES.
5. What is explain plan and how is it used?
The EXPLAIN PLAN command is a tool to tune SQL statements. To use it you must have an explain_table generated in the user you are running the explain plan for. This is created using the utlxplan.sql script. Once the explain plan table exists you run the explain plan command giving as its argument the SQL statement to be explained. The explain_plan table is then queried to see the execution plan of the statement. Explain plans can also be run using tkprof.
6. What is tkprof and how is it used?
The tkprof tool is a tuning tool used to determine cpu and execution times for SQL statements. You use it by first setting timed_statistics to true in the initialization file and then turning on tracing for either the entire database via the sql_trace parameter or for the session using the ALTER SESSION command. Once the trace file is generated you run the tkprof tool against the trace file and then look at the output from the tkprof tool. This can also be used to generate explain plan output.
Push the processing of the remote data to the remote instance by using a view to pre-select the information for the join. This will result in only the data required for the join being sent across.
9. What is a Cartesian product?
A Cartesian product is the result of an unrestricted join of two or more tables. The result set of a three table Cartesian product will have x * y * z number of rows where x, y, z correspond to the number of rows in each table involved in the join.
Oracle tables always have one guaranteed unique column, the rowid column. If you use a min/max function against your rowid and then select against the proposed primary key you can squeeze out the rowids of the duplicate rows pretty quick. For example:
select rowid from emp e
where e.rowid > (select min(x.rowid)
from emp x
where x.emp_no = e.emp_no);
In the situation where multiple columns make up the proposed key, they must all be used in the where clause.
The COST based system allows the use of HINTs to control the optimizer path selection. If they can give some example hints such as FIRST ROWS, ALL ROWS, USING INDEX, STAR, even better.
The only column that can be grouped on is the "item_no" column, the rest have aggregate functions associated with them.
13. What SQLPlus command is used to format output from a select?
This is best done with the COLUMN command.
14. You want to use SQL to build SQL, what is this called and give an example?
This is called dynamic SQL. An example would be:
set lines 90 pages 0 termout off feedback off verify off
spool drop_all.sql
select ?drop user ?||username||? cascade;? from dba_users
where username not in ("SYS?,?SYSTEM?);
spool off
Essentially you are looking to see that they know to include a command (in this case DROP USER...CASCADE;) and that you need to concatenate using the ?||? the values selected from the database.
15. How do you execute a host operating system command from within SQL?
Expected answer: By use of the exclamation point "!" (in UNIX and some other OS) or the HOST (HO) command.
16. How can you call a PL/SQL procedure from SQL?
Expected answer: By use of the EXECUTE (short form EXEC) command.
Expected answer: The best method is to use the CHR() function (CHR(10) is a return/linefeed) and the concatenation function "||". Another method, although it is hard to document and isn?t always portable is to use the return/linefeed as a part of a quoted string.
18. How can variables be passed to a SQL routine?
Level: Low
Expected answer: By use of the & symbol. For passing in variables the numbers 1-8 can be used (&1, &2,...,&8) to pass the values after the command into the SQLPLUS session. To be prompted for a specific variable, place the ampersanded variable in the code itself:
"select * from dba_tables where owner=&owner_name;" . Use of double ampersands tells SQLPLUS to resubstitute the value for each subsequent use of the variable, a single ampersand will cause a reprompt for the value unless an ACCEPT statement is used to get the value from the user.
19. What description of a data source is required for ODBC?
The name of the DBMS, the location of the source and the database dependent information.
20. What is the function of a ODBC manager?
The ODBC Manager manages all the data sources that exists in the system.
Inheritance is a method by which properties and methods of an existing object are automatically passed to any object derived from it.
22. What are the four types of events?
1. System Events.
2. Control Events
3. User Events
4. Other Events.
23. What is the main disadvantage of developing an application using an API?
The application cannot use any special features of the backend server.
24. Give some examples of standard APIs?
Open Database Connectivity (ODBC),
Integrated Database Application Programming Interface (IDAPI),
XOpen
SQL/CLI
25. What are the responsibilities of a Server?
1. Manage resources optimally across multiple clients.
2. Controlling database access and security.
3. Protecting the databse and recovering it from crashes.
4. Enforcing integrity rules globally.
27. What are the advantages of client/server model?
Flexibility of the system, scalability, cost saving, centralised control and implementation of business rules, increase of developers productivity, portability, improved network and resource utilization.
Because an integrity constraint is automatically checked while data is inserted into a table. A stored has to be specifically invoked.
29. What is an sql integrity constraint?
An integrity constraint allows the definition of certain restrictions, at the table level, on the data that is entered into a table.
30. What are the types of processes that a server runs?
Foreground process and Background process.
31. What is sql event trigger?
An event trigger, a segment of code which is associated with each event and is fired when the event occurs.
32. What does one do when one is rightsizing?
With rightsizing, one would move applications to the most appropriate server platforms.
33. Why is the most of the processing done at the sever?
To reduce the network traffic and for application sharing and implementing business rules.
34. What is the most important requirement for OLTP?
OLTP requires real time response.
35. What does preemptive in preemptive multitasking mean?
Preemptive refers to the fact that each task is alloted fixed time slots and at the end of that time slot the next task is started.
