161⟩ What SQLPlus command is used to format output from a select?
This is best done with the COLUMN command.
SQL
Home Databases Programming SQL
“SQL Interview Questions and Answers will guide us now that SQL (Structured Query Language) is a database computer language designed for managing data in relational database management systems (RDBMS), and originally based upon Relational Algebra. So learn SQL or get preparation for the job of SQL (Structured Query Language) with the help of this SQL Interview Questions with Answers guide”
172 SQL Questions And Answers
162⟩ What special Oracle feature allows you to specify how the cost based system treats a SQL statement?
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.
163⟩ ou want to group the following set of select returns, what can you group on? Max(sum_of_cost), min(sum_of_cost), count(item_no), item_no?
The only column that can be grouped on is the "item_no" column, the rest have aggregate functions associated with them.
164⟩ 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.
165⟩ 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.
166⟩ What is the default ordering of an ORDER BY clause in a SELECT statement?
Ascending
167⟩ You are joining a local and a remote table, the network manager complains about the traffic involved, how can you reduce the network traffic?
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.
168⟩ You want to determine the location of identical rows in a table before attempting to place a unique index on the table, how can this be done?
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.
169⟩ How do you prevent Oracle from giving you informational messages during and after a SQL statement execution?
The SET options FEEDBACK and VERIFY can be set to OFF.
170⟩ 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.
171⟩ How do you generate file output from SQL?
By use of the SPOOL command
172⟩ 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.
