Page 2 of 10 FirstFirst 1234 ... LastLast
Results 21 to 40 of 194

Thread: Geeks - Tip of the Day

  1. #21
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    Analytical functions – I
    Group functions and Analytical functions
    Group functions operate on a set of rows to give one result of a group Where as Analytic functions return multiple rows for each group.

    Ex 1:- SELECT deptno, count(*)deptcount
    FROM emp
    GROUP BY deptno
    EX 2:- SELECT empno, ename deptno,
    COUNT(*) OVER (PARTITION BY deptno) deptcount
    FROM emp

    The PARTITION BY clause is just like group by logically breaks a single result set into groups.

    In absence of any PARTITION inside the OVER( ) portion, the function acts on entire record set
    Ex 3:- SELECT empno, ename deptno,
    COUNT(*) OVER () deptcount
    FROM emp


  2. #22
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    analytical functions – ii
    order of execution in analytical functions
    join
    where
    group by
    having
    analytical functions
    order by
    so analytic functions can only appear in the select list and in the main order by clause of the query.

    Last edited by krishnaindia2007; 03-16-2008 at 12:04 AM.

  3. #23
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    Analytical functions – III
    Difference between RANK and DENSE_RANKRANK and DENSE_RANK functions provide rank to the records based on some column value or expression. Both these functions provide same rank for rows with equal values. The only difference is
    Dense_rank:- Rank values are not skipped in the event of ties .
    Rank :- Rank values are Skipped in the event of ties
    Ex :- . SELECT deptno, ename, sal,
    DENSE_RANK()
    OVER ( PARTITION BY deptno ORDER BY sal desc ) DENSERANK,
    RANK()
    OVER ( PARTITION BY deptno ORDER BY sal desc ) RANK FROM emp
    ORDER BY deptno, sal DESC


  4. #24
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    Analytical Functions - IV
    LAG / LEAD
    These two functions are useful for comparing one row of a result set with another row of a result set.
    LAG provides access to a rows which are prior to that position
    LEAD provides access to a rows which are beyond that position.(Which are going to come after the current row)

    Parameters
    1.Expression from which value to be taken.
    2.Offset:- It is index relative to the current row . Default is 1
    3.Default is the value to return if the <offset> points to a row outside the partition range. Default value is null.

    Ex:- SELECT deptno, ename, hiredate,
    LAG(hiredate,1,NULL)
    OVER (PARTITION BY deptno
    ORDER BY hiredate, ename) last_hire,
    LEAD(hiredate,1,NULL)
    OVER (PARTITION BY deptno
    ORDER BY hiredate, ename) next_hire
    FROM emp
    ORDER BY deptno, hiredate


  5. #25
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    Analytical functions – V
    First Value / Last Value of a Group
    The FIRST_VALUE and LAST_VALUE functions allow you to select the first and last rows from a group.
    Ex :- How many days after the first hire of each department were the next employees hired?
    SELECT empno, deptno, hiredate - FIRST_VALUE(hiredate)
    OVER (PARTITION BY deptno ORDER BY hiredate) DAY_GAP
    FROM emp
    WHERE deptno IN (20, 30)
    ORDER BY deptno, DAY_GAP;


  6. #26
    Expert Member
    Join Date
    Apr 2007
    Answers
    500

    Re: Geeks - Tip of the Day

    NVL2 FUNCTION
    The NVL function which checks for the existence of NULL values. NVL2 which checks for the existence of NOT NULL. The syntax for this function is as follows.

    NVL2(exp1,exp2,exp3);

    If exp1 is not null then the function will return exp2. Otherwise, the function will return exp3.
    Example:
    Select empno,ename,sal,comm,nvl2(comm,1,0)
    from emp order by empno;


  7. #27
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    Analytical functions – VI
    Window Clause
    In analytical functions we use The PARTITION BY clause to break a single result set into groups.
    We can further sub-partition the result using window clause.
    Window clause works with these functions
    AVG, COUNT, FIRST_VALUE, LAST_VALUE, MAX, MIN and SUM, STDDEV,VARIANCE .

    We can set up windows based on two criteria:
    1.Range type window:- The definition is in terms of values before or after the current ORDER..
    It is not valid to use RANGE with datatypes other than numbers and dates.

    Ex:- SELECT ename, hiredate, hiredate-100 hiredate_pre,
    COUNT(*)
    OVER (
    ORDER BY hiredate ASC
    RANGE 100 PRECEDING
    ) cnt
    FROM emp
    ORDER BY hiredate ASC
    The range window goes back 100 days from the current row's hiredate and then counts the rows within this range

    2. Row Type Window:- The windowing clause is in terms of record numbers

    Ex:- SELECT deptno "Deptno", ename "Ename", sal "Sal",
    SUM(SAL)
    OVER (PARTITION BY deptno
    ORDER BY ename
    ROWS 2 PRECEDING) "Sliding Total"
    FROM emp
    ORDER BY deptno, ename

    The above query access the 2 rows prior to the current row in a group in order to sum the salaries.

    The ROW or RANGE window cannot appear together in one OVER clause.


  8. #28
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    Avoid including a HAVING clause in SELECT statements. The HAVING clause filters selected rows only after all rows have been fetched. Using a WHERE clause helps reduce overheads in sorting, summing, etc. HAVING clauses should only be used when columns with summary operations applied to them are restricted by the clause.

    SELECT Deptno, AVG (sal)
    FROM emp
    GROUP BY deptno
    HAVING deptno != 10

    This query gives better performance than above query.

    SELECT Deptno, AVG (sal)
    FROM emp
    WHERE deptno != 10
    GROUP BY deptno


  9. #29
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    Difference Between Syntax Check and Semantic Check
    Parsing is the first step in the processing of any statement in Oracle. Parsing is the act of breaking the submitted statement down into its component parts. Determining what type of statement it is (query, DML, DDL) and performing various checks on it.
    .
    The parsing process performs two main functions:
    1. Syntax Check: During this process it checks
    Is the statement a valid one? Does it follow SQL Syntax rules?

    Example for syntax error
    select from where 2;
    select from where 2
    *
    ERROR at line 1:
    ORA-00936: missing expression

    2.Semantic Analysis: Is the statement valid in light of the objects in the database (do the tables and columns referenced exist). Whether the particular user has access to the objects or not ? Are there ambiguities in the statement ? For example if there are two tables T1 and T2 and both have a column X, the query ?select X from T1, T2 where ? is ambiguous, we don’t know which table to get X from. And so on.

    Ex :- select * from not_a_table;
    select * from not_a_table
    *
    ERROR at line 1:
    ORA-00942: table or view does not exist


  10. #30
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    difference between hard and soft parsing
    oracle uses a piece of memory called the shared pool to enable sharing of sql statements. The shared pool is a piece of memory in the system global area (sga) and is maintained by the oracle database.
    After completing first two phases of parsing i.e. Syntax and semantic checks it looks in the shared pool to see if that same exact query has already been processed by another session. If found, the parsed representation will be picked up and the statement executed immediately. This is called soft parsing.
    If not found, then it has to go for next two steps in the process, that of optimization (this involves finding an optimal execution path for the statement) and row source generation. This entire process is called hard parsing.
    A soft parse will save a considerable amount of cpu cycles when running your query. It is especially important that developers write and design queries that take advantage of soft parses so that parsing phase can skip the optimization and row source generation functions, which are very cpu intensive and a point of contention. If a high percentage of your queries are being hard-parsed, your system will function slowly, and in some cases, not at all.

    Last edited by krishnaindia2007; 03-18-2008 at 11:02 PM.

  11. #31
    Expert Member
    Join Date
    Apr 2007
    Answers
    500

    Re: Geeks - Tip of the Day

    To print matrix report in sql
    SELECT job,
    sum(decode(deptno,10,sal)) DEPT10,
    sum(decode(deptno,20,sal)) DEPT20,
    sum(decode(deptno,30,sal)) DEPT30,
    sum(decode(deptno,40,sal)) DEPT40
    FROM scott.emp
    GROUP BY job


  12. #32
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    While practicing commands you may disturb defualt tables emp, dept etc. To bring it to the normal form find demobld.sql and execute it.

    Example:-
    @ d:\oracle\oracle9ids\tools\dbtab90\demobld.sql


  13. #33
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    Where ever it is possible use UNION ALL , which gives better performance than UNION.
    UNION first sorts records, then merge the records and finally eliminates duplicate records. Where as UNION ALL simply returns all rows including duplicates and does not have to perform any sort, merge or filter.


  14. #34
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    If the FETCH statement in cursors fail to return a row, no exception is raised.
    To detect the failure, you must use the cursor attribute %FOUND or %NOTFOUND.
    Do not use NO_DATA_FOUND exception in cursors.

    Last edited by krishnaindia2007; 03-23-2008 at 03:37 AM.

  15. #35
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    Data Dictionary – I
    It is a series of table and views that contain information about the structures and users in the database.
    It provides
    - User names of oracle users.
    - The rights and Privileges they have been granted
    - Names of database objects
    - Constraints applied to a table
    - Who accessed and updated database objects

    The data dictionary is created in the database when we give create database command.

    The information available in data dictionary tables is very difficult to understand. So it provides views in a form that is easier for a users to understand. All these tables and views are owned by SYS.

    Data Dictionary Provides three types of views
    USER_XXX :- Objects owned by the user and that can be accessed
    ALL_XXX :- List of all objects that the user has access to
    DBA_XXX :- Users with DBA privilege only can access these views and can access any object in the database


  16. #36
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    Data Dictionary – II
    Useful synonyms for data dictionary views most of the data dictionary views have long names. Public synonyms have been provided for convenient access.
    Synonym name synonym for table
    cat user_catalog
    clu user_clusters
    dict dictionary
    ind user_indexes
    obj user_objects
    seq user_sequences
    sys user_synonyms
    tab user_tables
    you can directly use these synonyms in queries
    select * from obj


  17. #37
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    Data dictionary – III
    Some dictionary views do not use prefixes user, all, dba. Examples for such views are
    dictionary - list of all dictionary objects accessible to the user
    dict_columns - lists columns in dictionary objects accessible to the user
    role_role_privs - roles which are granted to roles
    role_sys_privs - system privileges granted to roles role_tab_privs - table privileges granted to roles
    session_privs - privileges which current user has set session_roles - roles which current user currently has enabled


  18. #38
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    How to use apostrophe?
    When the apostrophe/single quote is at the start of the string, you need to enter 3 single quotes for Oracle to display a quote symbol.

    SQL> SELECT '''Hi There' Message FROM dual;

    MESSAGE
    --------------
    'Hi There

    In the middle of the string, you need to enter 2 single quotes

    SQL> SELECT 'He''s always the first to arrive' Message FROM dual;

    MESSAGE
    -------------------------------
    He's always the first to arrive

    Single quote is at the end of a string, you need to enter 3 single quotes
    SQL> SELECT 'Smiths''' FROM dual;

    'SMITHS
    -------
    Smiths'
    If you were to concatenate an apostrophe/single quote in a string, you need to enter 4 single quotes
    SQL> SELECT 'There' || '''' || 's Henry' Message FROM dual;

    MESSAGE
    -------------
    There's Henry


  19. #39
    Expert Member
    Join Date
    Nov 2006
    Answers
    518

    Re: Geeks - Tip of the Day

    Is this thread only for SQL/PL-SQL ?

    Lack of WILL POWER has caused more failure than
    lack of INTELLIGENCE or ABILITY.

    -sutnarcha-

  20. #40
    Junior Member
    Join Date
    Feb 2008
    Answers
    25

    Re: Geeks - Tip of the Day

    Hi,

    SQL every where!!!

    i hope to get tip(s) from testing issues in near future!!!

    Thanks & Regards
    Trainee_tester

    Last edited by trainee_tester; 03-27-2008 at 02:28 AM. Reason: ;)

Page 2 of 10 FirstFirst 1234 ... LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact