SQL interview questions and answers

Document Sample
SQL interview questions and answers Powered By Docstoc
					1.Explain the different views with examples (4) 

Simple views created by create or replace command Example: CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition


Inline views created while giving a select form table_name statement in from clause or column list or in where clause of a query

An inline view is a SELECT statement in the FROM-clause of another SELECT statement. In-line views are commonly used to simplify complex Queries by removing join operations and condensing several separate queries into a single query. Example:

FROM (select deptno, count(*) emp_count from emp group by deptno) emp, dept WHERE dept.deptno = emp.deptno; 

Materialized views used for data replication.

A materialized view is a replica of a target master from a single point in time. The master can be either a master table at a master site or a master materialized view at a materialized view site. Example: CREATE OR REPLACE MATERIALIZED VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition

2.Define Analytic Functions with example (2) Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. Analytic functions are commonly used to compute cumulative, moving, centered, and reporting aggregates.

The general syntax of analytic function is: Function(arg1,..., argn) OVER ( [PARTITION BY <...>] [ORDER BY <....>] [<window_clause>] ) Example:SELECT empno, deptno, COUNT(*) OVER (PARTITION BY deptno) DEPT_COUNT FROM emp WHERE deptno IN (20, 30); EMPNO DEPTNO DEPT_COUNT ---------- ---------- ---------7369 20 5 7566 20 5 7788 20 5 7902 20 5 7876 20 5 7499 30 6 7900 30 6 7844 30 6 7698 30 6 7654 30 6 7521 30 6

3.Define Hierarchical query operators with example (3)

There are two Hierarchical operators, PRIOR and CONNECT_BY_ROOT, :PRIOR In a hierarchical query, one expression in the CONNECT BY condition must be qualified by the PRIOR operator. If the CONNECT BY condition is compound, then only one condition requires the PRIOR operator, although you can have multiple PRIOR conditions. PRIOR evaluates the immediately following expression for the parent row of the current row in a hierarchical query. PRIOR is most commonly used when comparing column values with the equality operator. (The PRIOR keyword can be on either side of the operator.) PRIOR causes Oracle to use the value of the parent row in the column. Operators other than the equal sign (=) are theoretically possible in CONNECT BY clauses. However, the conditions created by these other operators can result in an infinite loop through the possible combinations. In this case Oracle detects the loop at run time and returns an error. Please refer to "Hierarchical Queries" for more information on this operator, including examples. CONNECT_BY_ROOT:

CONNECT_BY_ROOT is a unary operator that is valid only in hierarchical queries. When you qualify a column with this operator, Oracle returns the column value using data from the root row. This operator extends the functionality of the CONNECT BY [PRIOR] condition of hierarchical queries. Example: The following example uses a GROUP BY clause to return the total salary of each employee in department 110 and all employees below that employee in the hierarchy Example:SELECT name, SUM(salary) "Total_Salary" FROM ( SELECT CONNECT_BY_ROOT last_name as name, Salary FROM employees WHERE department_id = 110 CONNECT BY PRIOR employee_id = manager_id) GROUP BY name; NAME Total_Salary ------------------------- -----------Gietz 8300 Higgins 20300 King 20300 Kochhar 20300
4.How do the exists and IN conditions differ? (2)

1.Exists always returns the boolean value where as IN returns actual value .
2.When we use 'IN' ,While Checkin for where Condition SQL server Engine does whole table scan. Where as if we use 'EXISTS' as soon as engine finds the required row it will stop executing Query and going further scanning table.

5.Explain the „column_value‟ pseudo column (2)

When we refer to a Table construct without the COLUMNS clause, or when we use the TABLE function to refer to a scalar nested table type, the database returns a virtual table with a single column.This name of this pseudocolumn is COLUMN_VALUE. Example: CREATE TYPE phone AS TABLE OF NUMBER; CREATE TYPE phone_list AS TABLE OF phone; This statement uses COLUMN_VALUE to select from the phone type: SELECT t.COLUMN_VALUE FROM TABLE(phone(1,2,3)) t;

COLUMN_VALUE -----------1 2 3

6.How does the “is not dangling” phrase work in the where clause of a sql statement? (1)

An object identifier (represented by the keyword OID) uniquely identifies an object and enables you to reference the object from other objects or from relational tables. A datatype category called REF represents such references. A REF datatype is a container for an object identifier. REF values are pointers to objects. When a REF value points to a nonexistent object, the REF is said to be "dangling". To determine whether a REF is dangling or not, use the condition IS [NOT] DANGLING. For example, given object view oc_ orders in the sample schema oe, the column customer_ref is of type REF to type customer_typ, which has an attribute cust_email: SELECT o.customer_ref.cust_email FROM oc_orders o WHERE o.customer_ref IS NOT DANGLING;
7.How are the union and union all operators different? (1)

UNION is used to select distinct values from two tables where as UNION ALL is used to select all values including duplicates from the tables
8.Please explain antijoins and semijoins with examples. (2)

An anti-join returns rows from the left side of the predicate for which there is no corresponding row on the right side of the predicate. That is, it returns rows that fail to match (NOT IN) the subquery on the right side. For example, an anti-join can select a list of employees who are not in a particular set of departments: SELECT * FROM emp WHERE deptno NOT IN (SELECT deptno FROM dept WHERE loc = 'HEADQUARTERS');

A semi-join returns rows that match an EXISTS subquery, without duplicating rows from the left side of the predicate when multiple rows on the right side satisfy the criteria of the subquery. For example: SELECT * FROM dept WHERE EXISTS (SELECT * FROM emp WHERE dept.ename = emp.ename AND emp.bonus > 5000); In this query, only one row needs to be returned from DEPT even though many rows in EMP might match the subquery. If there is no index on the BONUS column in EMP, a semi-join can be used to improve query performance.
9.How do you recompile an invalid procedure when you do not access to the source code and how would you inactivate a trigger? (1)

A trigger can be deactivate using the syntax below DISABLE TRIGGER trigger_name; And by using apps schema we can access source code and we can recompile ALTER TRIGGER my_trigger COMPILE; dbms_ddl.alter_compile (TRIGGER,'SCOTT',XYZ); can be used for compilation

10.Explain the “cursor Expression” in sql. (2)

A CURSOR expression returns a nested cursor. This form of expression is equivalent to the PL/SQL REF CURSOR and can be passed as a REF CURSOR argument to a function. Examples The following example shows the use of a CURSOR expression in the select list of a query: SELECT department_name, CURSOR(SELECT salary, commission_pct FROM employees e WHERE e.department_id = d.department_id) FROM departments d;

Shared By:
About These Lines below describe me well. The Woods are lovely dark and deep,But I have promises to keep,Miles to go before I sleep ,And miles to go before I sleep.