Urgent ...oracle Help....

[tommy]

bhayya naaku inteerview undhi so ee questions ki answers kavali...

1 What is the difference between a Matrix and Matrix with group report?
2 Can you explain the difference between Group By and Partition By clauses?
3 What are the analytical functions available in Oracle?
4 What is the difference between Single and Correlated sub-queries?
5 What is query to get first day of the last quarter?
6. How can you tell if an UPDATE updated no rows, in SQL and PL/SQL?
7.Difference between DELETE, TRUNCATE and DROP?
8. Difference between Primary, Unique and Foreign keys?
9. How to debug PL/SQL code?
10. Explain the difference between implicit and explicit cursors?

[MeetFriendz]

[color=#282828][font=helvetica, arial, sans-serif]1 What is the difference between a Matrix and Matrix with group report?[/font][/color]



no idea

[color=#282828][font=helvetica, arial, sans-serif]2 Can you explain the difference between Group By and Partition By clauses?[/font][/color]
[color=#000000][font=Arial,]
[background=transparent]
PARTITION BY is analytic, while GROUP BY is aggregate. In order to use PARTITION BY, you have to contain int with an [url="http://msdn.microsoft.com/en-us/library/ms189461.aspx"]OVER clause[/url].[/background]
(or)[/font][/color]


[color=#000000][font=Arial,][background=transparent]They're used in different places. [/background][/font][/color]group by[color=#000000][font=Arial,][background=transparent] modifies the entire query, like:[/background][/font][/color][color=#000000][font=Arial,]

[color=#00008B][background=transparent]select[/background][/color][background=transparent] customerId[/background][background=transparent],[/background][background=transparent] count[/background][background=transparent])[/background][background=transparent] [/background][color=#00008B][background=transparent]as[/background][/color][background=transparent] orderCount
[/background][color=#00008B][background=transparent]from[/background][/color][background=transparent] Orders
[/background][color=#00008B][background=transparent]group[/background][/color][background=transparent] [/background][color=#00008B][background=transparent]by[/background][/color][background=transparent] customerId[/background][background=transparent]
But partition by just works on a window function, like row_number:[/background]
[color=#00008B][background=transparent]select[/background][/color][background=transparent] row_number[/background][background=transparent]()[/background][background=transparent] [/background][color=#00008B][background=transparent]over[/background][/color][background=transparent] [/background][background=transparent]([/background][color=#00008B][background=transparent]partition[/background][/color][background=transparent] [/background][color=#00008B][background=transparent]by[/background][/color][background=transparent] customerId [/background][color=#00008B][background=transparent]order[/background][/color][background=transparent] [/background][color=#00008B][background=transparent]by[/background][/color][background=transparent] orderId[/background][background=transparent])[/background][background=transparent]
[/background][color=#00008B][background=transparent]as[/background][/color][background=transparent] OrderNumberForThisCustomer
[/background][color=#00008B][background=transparent]from[/background][/color][background=transparent] Orders[/background][background=transparent]
A group by normally reduces the number of rows returned by rolling them up and calculating averages or sums for each row. partition by does not affect the number of rows returned, but it changes how a window function's result is calculated.[/background]

[background=transparent]
[color=#282828][font=helvetica, arial, sans-serif]3. What are the analytical functions available in Oracle?[/font][/color][/background][/font][/color]

[color=#000000][font=Verdana, Arial, Helvetica, sans-serif,]Oracle actually provides many analytic functions such as [/font][/color][i]AVG[/i][color=#000000][font=Verdana, Arial, Helvetica, sans-serif,], [/font][/color][i]CORR[/i][color=#000000][font=Verdana, Arial, Helvetica, sans-serif,],[/font][/color][i]COVAR_POP[/i][color=#000000][font=Verdana, Arial, Helvetica, sans-serif,], [/font][/color][i]COVAR_SAMP[/i][color=#000000][font=Verdana, Arial, Helvetica, sans-serif,], [/font][/color][i]COUNT[/i][color=#000000][font=Verdana, Arial, Helvetica, sans-serif,], [/font][/color][i]CUME_DIST[/i][color=#000000][font=Verdana, Arial, Helvetica, sans-serif,], [/font][/color][i]DENSE_RANK[/i][color=#000000][font=Verdana, Arial, Helvetica, sans-serif,],[/font][/color][i]FIRST[/i][color=#000000][font=Verdana, Arial, Helvetica, sans-serif,], [/font][/color][i]FIRST_VALUE[/i][color=#000000][font=Verdana, Arial, Helvetica, sans-serif,], [/font][/color][i]LAG[/i][color=#000000][font=Verdana, Arial, Helvetica, sans-serif,], [/font][/color][i]LAST[/i][color=#000000][font=Verdana, Arial, Helvetica, sans-serif,], [/font][/color][i]LAST_VALUE[/i][color=#000000][font=Verdana, Arial, Helvetica, sans-serif,], [/font][/color][i]LEAD[/i][color=#000000][font=Verdana, Arial, Helvetica, sans-serif,], [/font][/color][i]MAX[/i][color=#000000][font=Verdana, Arial, Helvetica, sans-serif,],[/font][/color][i]MIN[/i][color=#000000][font=Verdana, Arial, Helvetica, sans-serif,], [/font][/color][i]NTILE[/i][color=#000000][font=Verdana, Arial, Helvetica, sans-serif,], [/font][/color][i]PERCENT_RANK[/i][color=#000000][font=Verdana, Arial, Helvetica, sans-serif,], [/font][/color][i]PERCENTILE_CONT[/i][color=#000000][font=Verdana, Arial, Helvetica, sans-serif,],[/font][/color][i]PERCENTILE_DISC[/i][color=#000000][font=Verdana, Arial, Helvetica, sans-serif,], [/font][/color][i]RANK[/i][color=#000000][font=Verdana, Arial, Helvetica, sans-serif,], [/font][/color][i]RATIO_TO_REPORT[/i][color=#000000][font=Verdana, Arial, Helvetica, sans-serif,], [/font][/color][i]STDDEV[/i][color=#000000][font=Verdana, Arial, Helvetica, sans-serif,],[/font][/color][i]STDDEV_POP[/i][color=#000000][font=Verdana, Arial, Helvetica, sans-serif,], [/font][/color][i]STDDEV_SAMP[/i][color=#000000][font=Verdana, Arial, Helvetica, sans-serif,], [/font][/color][i]SUM[/i][color=#000000][font=Verdana, Arial, Helvetica, sans-serif,], [/font][/color][i]VAR_POP[/i][color=#000000][font=Verdana, Arial, Helvetica, sans-serif,], [/font][/color][i]VAR_SAMP[/i][color=#000000][font=Verdana, Arial, Helvetica, sans-serif,],[/font][/color][i]VARIANCE[/i][color=#000000][font=Verdana, Arial, Helvetica, sans-serif,].[/font][/color]

[url="http://www.cs.utexas.edu/~cannata/dbms/Analytic%20Functions%20in%20Oracle%208i%20and%209i.htm"]http://www.cs.utexas.edu/~cannata/dbms/Analytic%20Functions%20in%20Oracle%208i%20and%209i.htm[/url]

[color=#282828][font=helvetica, arial, sans-serif]8. Difference between Primary, Unique and Foreign keys?[/font][/color]
[url="http://www.programmerinterview.com/index.php/database-sql/differences-between-primary-and-foreign-keys/"]http://www.programmerinterview.com/index.php/database-sql/differences-between-primary-and-foreign-keys/[/url]


[color=#282828][font=helvetica, arial, sans-serif]10. Explain the difference between implicit and explicit cursors? [/font][/color]

[color=#000000][font=Arial,]An implicit cursor is one created "automatically" for you by Oracle when you execute a query. It is simpler to code, but suffers from[/font][/color][list]
[*]inefficiency (the ANSI standard specifies that it must fetch twice to check if there is more than one record)
[*]vulnerability to data errors (if you ever get two rows, it raises a TOO_MANY_ROWS exception)
[/list][color=#000000][font=Arial,]
Example[/font][/color]
[color=#00008B][background=transparent]SELECT[/background][/color][background=transparent] col [/background][color=#00008B][background=transparent]INTO[/background][/color][background=transparent] var [/background][color=#00008B][background=transparent]FROM[/background][/color][background=transparent] [/background][color=#00008B][background=transparent]table[/background][/color][background=transparent] [/background][color=#00008B][background=transparent]WHERE[/background][/color][background=transparent] something[/background][background=transparent];[/background][color=#000000][font=Arial,]
An explicit cursor is one you create yourself. It takes more code, but gives more control - for example, you can just open-fetch-close if you only want the first record and don't care if there are others.[/font][/color][color=#000000][font=Arial,]
Example[/font][/color]
[color=#00008B][background=transparent]DECLARE[/background][/color][background=transparent]
[/background][color=#00008B][background=transparent]CURSOR[/background][/color][background=transparent] cur [/background][color=#00008B][background=transparent]IS[/background][/color][background=transparent] [/background][color=#00008B][background=transparent]SELECT[/background][/color][background=transparent] col [/background][color=#00008B][background=transparent]FROM[/background][/color][background=transparent] [/background][color=#00008B][background=transparent]table[/background][/color][background=transparent] [/background][color=#00008B][background=transparent]WHERE[/background][/color][background=transparent] something[/background][background=transparent];[/background][background=transparent]
[/background][color=#00008B][background=transparent]BEGIN[/background][/color][background=transparent]
[/background][color=#00008B][background=transparent]OPEN[/background][/color][background=transparent] cur[/background][background=transparent];[/background][background=transparent]
[/background][color=#00008B][background=transparent]FETCH[/background][/color][background=transparent] cur [/background][color=#00008B][background=transparent]INTO[/background][/color][background=transparent] var[/background][background=transparent];[/background][background=transparent]
[/background][color=#00008B][background=transparent]CLOSE[/background][/color][background=transparent] cur[/background][background=transparent];[/background][background=transparent]
[/background][color=#00008B][background=transparent]END[/background][/color][background=transparent];[/background]

[color=#282828][font=helvetica, arial, sans-serif]9. How to debug PL/SQL code?[/font][/color]
[url="http://www.dba-oracle.com/t_plsql_debugging_techniques.htm"]http://www.dba-oracle.com/t_plsql_debugging_techniques.htm[/url]

[color=#282828][font=helvetica, arial, sans-serif]7.Difference between DELETE, TRUNCATE and DROP?[/font][/color]
[color=#333333]
DELETE
Delete is the command that only remove the data from the table. It is DML statement. Deleted data can be rollback. By using this we can delete whole data from the table(if use without where clause).If ew want to remove only selected data then we should specify condition in the where clause
SQL>delete from employee;(this command will remove all the data from table)
SQL>delete from employee where employee_name='JOHN';(This command will remove only that row from employee table where employee_name is JOHN');[/color][color=#333333]
DROP:
Drop command remove the table from data dictionary. This is the DDL statement. We can not recover the table before Oracle 10g. But Oracle 10g provide the command to recover it by using the command (FLASHBACK)[/color][color=#333333]
TRUNCATE:
This is the DML command. This command delete the data from table. But there is one difference from ordinary delete command. Truncate command drop the storage held by this table. Drop storage can be use by this table again or some other table. This is the faster command because it directly drop the storage[/color]

[color=#282828][font=helvetica, arial, sans-serif]6. How can you tell if an UPDATE updated no rows, in SQL and PL/SQL?[/font][/color]
[color=#444444][font=Verdana, Geneva, Lucida,][background=rgb(250, 250, 250)]using %ROWCOUNT in plsql, sql lo update kagane its give no of rows effected.[/background][/font][/color]


[color=#282828][font=helvetica, arial, sans-serif]5 What is query to get first day of the last quarter?[/font][/color]

[color=#00008B][background=transparent]SELECT[/background][/color][background=transparent]
ADD_MONTHS[/background][background=transparent]([/background][background=transparent]TRUNC[/background][background=transparent]([/background][background=transparent]SYSDATE[/background][background=transparent],[/background][background=transparent] [/background][color=#800000][background=transparent]'Q'[/background][/color][background=transparent]),[/background][background=transparent] [/background][color=#800000][background=transparent]-3[/background][/color][background=transparent])[/background][background=transparent] [/background][color=#00008B][background=transparent]AS[/background][/color][background=transparent] First[/background][background=transparent],[/background][background=transparent]
TRUNC[/background][background=transparent]([/background][background=transparent]SYSDATE[/background][background=transparent],[/background][background=transparent] [/background][color=#800000][background=transparent]'Q'[/background][/color][background=transparent])[/background][background=transparent] [/background][background=transparent]-[/background][background=transparent] [/background][color=#800000][background=transparent]1[/background][/color][background=transparent] [/background][color=#00008B][background=transparent]AS[/background][/color][background=transparent] Last
[/background][color=#00008B][background=transparent]FROM[/background][/color][background=transparent] DUAL[/background]
or

[color=#00008B][background=transparent]SELECT[/background][/color][background=transparent]
ADD_MONTHS[/background][background=transparent]([/background][background=transparent]D[/background][background=transparent],[/background][background=transparent] [/background][color=#800000][background=transparent]-3[/background][/color][background=transparent])[/background][background=transparent] [/background][color=#00008B][background=transparent]AS[/background][/color][background=transparent] First[/background][background=transparent],[/background][background=transparent]
D [/background][background=transparent]-[/background][background=transparent] [/background][color=#800000][background=transparent]1[/background][/color][background=transparent] [/background][color=#00008B][background=transparent]AS[/background][/color][background=transparent] Last
[/background][color=#00008B][background=transparent]FROM[/background][/color][background=transparent] [/background][background=transparent]([/background][color=#00008B][background=transparent]SELECT[/background][/color][background=transparent] TRUNC[/background][background=transparent]([/background][background=transparent]SYSDATE[/background][background=transparent],[/background][background=transparent] [/background][color=#800000][background=transparent]'Q'[/background][/color][background=transparent])[/background][background=transparent] [/background][color=#00008B][background=transparent]AS[/background][/color][background=transparent] D [/background][color=#00008B][background=transparent]FROM[/background][/color][background=transparent] DUAL[/background][background=transparent])[/background]

[color=#282828][font=helvetica, arial, sans-serif]4 What is the difference between Single and Correlated sub-queries?[/font][/color]
[url="http://www.folkstalk.com/2012/01/oracle-subquerycorrelated-query.html"]http://www.folkstalk.com/2012/01/oracle-subquerycorrelated-query.html[/url]


-----all from google-------

[tommy]

bhayya

[nenunanu]

from heaven sake pls dont refer this as ORACLE. you can call it SQL..

all the best