|
Sql Interview Questions and Answers
How is a connection established by ODBC ?
ODBC uses the description of the datasource available in the ODBC.INI
file to load the required drivers to access that particular back end
database.
SQL/ SQL Plus
1. 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.
2. You want to include a carriage return/linefeed in your output from a
SQL script, how can you do this?
Level: Intermediate to high
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.
3. How can you call a PL/SQL procedure from SQL?
Level: Intermediate
Expected answer: By use of the EXECUTE (short form EXEC) command.
4. How do you execute a host operating system command from within SQL?
Level: Low
Expected answer: By use of the exclamation point "!" (in UNIX and some
other OS) or the HOST (HO) command.
5. You want to use SQL to build SQL, what is this called and give an
example
Level: Intermediate to high
Expected answer: 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.
6. What SQLPlus command is used to format output from a select?
Level: low
Expected answer: This is best done with the COLUMN command.
7. You 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
Level: Intermediate
Expected answer: The only column that can be grouped on is the "item_no"
column, the rest have aggregate functions associated with them.
8. What special Oracle feature allows you to specify how the cost based
system treats a SQL statement?
Level: Intermediate to high
Expected answer: 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.
9. 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?
Level: High
Expected answer: 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.
10. What is a Cartesian product?
Level: Low
Expected answer: 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.
11. You are joining a local and a remote table, the network manager
complains about the traffic involved, how can you reduce the network
traffic?
Level: High
Expected answer: 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.
12. What is the default ordering of an ORDER BY clause in a SELECT
statement?
Level: Low
Expected answer: Ascending
13. What is tkprof and how is it used?
Level: Intermediate to high
Expected answer: 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.
14. What is explain plan and how is it used?
Level: Intermediate to high
Expected answer: 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.
15. How do you set the number of lines on a page of output? The width?
Level: Low
Expected answer: 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.
Page Numbers
:
1
2
3
4
5
6
7
8
9
10
11
Have a Question ?
post your questions here. It
will be answered as soon as possible.
Check
HTML Interview
Questions for more HTML Interview Questions with Answers
Check
Job Interview Questions
for more Interview Questions with Answers
|
