Answered Questions

  • Difference between a "where" clause and a "having" clause?

    Star Read Best Answer

    Editorial / Best Answer

    Answered by: Ankush Sharma

    • Sep 14th, 2011


    Though the HAVING clause specifies a condition that is similar to the purpose of a WHERE clause, the two clauses are not interchangeable. Listed below are some differences to help distinguish between the two:

    1. The WHERE clause specifies the criteria which individual records must meet to be selcted by a query. It can be used without the GROUP BY clause. The HAVING clause cannot be used without the GROUP BY clause.
    2. The WHERE clause selects rows before grouping. The HAVING clause selects rows after grouping.
    3. The WHERE clause cannot contain aggregate functions. The HAVING clause can contain aggregate functions.

    for Example: if for an "Select" statement we use the "where" clause then the the result based on the "where" condition results and then we can use "group by" clause to arrange in some order, Now if we want to impose the condition on that group then we use "having" clause.

    The main reason for using WHERE clause is to select rows that are to be included in the query. For example, assume table Test.Suppose I want the names, account numbers, and balance due of all customers from California and Los Angles. Since STATE is one of the fields in the record format, I can use WHERE to select those customers.

    Using the code

    Code
    1. SELECT cusnum, lstnam, init
    2. FROM Test
    3. WHERE state IN ('CA', 'LA')
    4.  
    5. CUSNUM LSTNAM INIT BALDUE
    6. ====== ============ ==== ========
    7. 938472 John G K 37.00
    8. 938485 Mark J A 3987.50
    9. 593029 Lily E D 25.00
    Suppose I want the total amount due from customers by state. In that case, I would need to use the GROUP BY clause to build an aggregate query.
    Code
    1. SELECT state,SUM(baldue)
    2. FROM Test
    3. GROUP BY state
    4. ORDER BY state
    5.  
    6. State Sum(Baldue)
    7. ===== ===========
    8. CA 250.00
    9. CO 58.75
    10. GA 3987.50
    11. MN 510.00
    12. NY 589.50
    13. TX 62.00
    14. VT 439.00
    15. WY .00
    Using Having
    Code
    1. SELECT state,SUM(baldue)
    2. FROM Test
    3. GROUP BY state
    4. HAVING SUM(baldue) > 250
    5.  
    6.  
    7. State Sum(Baldue)
    8. ===== ===========
    9. GA 3987.50
    10. MN 510.00
    11. NY 589.50
    12. VT 439.00

    Ankush Sharma

    • Sep 14th, 2011

    Though the HAVING clause specifies a condition that is similar to the purpose of a WHERE clause, the two clauses are not interchangeable. Listed below are some differences to help distinguish between ...

    Ankush Sharma

    • Sep 14th, 2011

    Though the HAVING clause specifies a condition that is similar to the purpose of a WHERE clause, the two clauses are not interchangeable. Listed below are some differences to help distinguish between ...

  • How many types of database triggers can be specified on a table? What are they?

    Star Read Best Answer

    Editorial / Best Answer

    krishnaindia2007  

    • Member Since Sep-2007 | May 6th, 2008


    A trigger may be a
    1. DML Trigger on tables
    2. Instead of triggers on views
    3. System triggers on database or schema

    Based on the way it executes statements  triggers are of two types
    1. Statement leve trigger
    2.  Row level trigger

    A trigger fires for three actions
    1. Insert
    2. Delete 
    3.Update

    and the trigger can the fired
    1. Before action
    2. After action.

    Pradeep

    • Feb 20th, 2016

    Basically 5 main types in Oracle Triggers
    1. DML Trigger (3 statement * 2 timing * 2 level = 12 types).
    2. DDL Trigger.
    3. Instead of Trigger (Fired Complex View).
    4. System Trigger.
    5. Compound Trigger (Introduced by 11g)

    tarini sankar das

    • Feb 7th, 2014

    Actual answer is we have 14 types of triggers in pl sql. For statement level :- 1-before insert 2-before update 3-before delete 4-after insert 5-after update 6-after delete For row level :- 7-before ...