need study partner for pl/sql

Discussion in 'Education & Personal Growth' started by anu333, Apr 5, 2012.

  1. prtywomen

    prtywomen Junior IL'ite

    Messages:
    43
    Likes Received:
    6
    Trophy Points:
    13
    Gender:
    Female
    Hi Friends,
    I would like join to this group study.. anu333, please send me the materials.. I will try to catch up with you all in couple of days.
    Thanks,
    Jothi.
     
  2. prtywomen

    prtywomen Junior IL'ite

    Messages:
    43
    Likes Received:
    6
    Trophy Points:
    13
    Gender:
    Female
    Questions:
    1.What is the difference between unique+not null and primary key constraint?
    2.Explain default constraint
    3.What are the restrictions of a foreign key constraint?
    4.what happens to index when you drop a primary key constraint
    5.what is the difference between equi join and outer join?
    6.Explain Self join with example

    Answers:
    1. Unique not null and primary key constraints - Both doesnt allow null value and values in that column should be unique but primary key constraint will have default clustered index, ie. data is sorted based on the primary key column.
    2. Default constraint - Column can be defined with default value which will be inserted to the table if the value is not passed to that column.
    3. Restrictions of Foreginkey - I guess, you can create foreign key referencing only to the primary key of the another table but not just any column of table.
    4. Both Primarykey constraint and index drops
    5. Equi join = Data in both the tables are compared and validated against the condition. So, Query returns rows only if both the tables has same data. OuterJoin - It could either right, left or full outer join, so irrespective of condition, query result all data from both the tables for the full outer join, it just replaces null for unmatched rows.
    6. Self Join - With Equi and outer join, it required 2 different tables to compare but with Self join, it compares data to itself. Best example: Find the Manager and its subordinates. Table Employee has
    EmployeeID,
    EmployeeNAme
    ManagerID

    SELECT m.EmployeeName ManagerName, e.EmployeeName SubOrdinates FROM Employee m JOIN Employee e
    ON m.EmployeeID = e.ManagerID
    WHERE m.EmployeeID <> e.EmployeeID

    Query to test:
    create table Employee
    (
    EmployeeID int constraint pk_EmployeeID primary key,
    EmployeeNAme varchar(50),
    ManagerID int
    )
    insert into Employee values (1, 'Manager', 0)
    insert into Employee values (2, 'SalesManager', 1)
    insert into Employee values (3, 'MarketingManager', 1)
    insert into Employee values (4, 'FinanceManager', 1)
    insert into Employee values (5, 'SalesRep1', 2)
    insert into Employee values (6, 'SalesRep2', 2)
    insert into Employee values (7, 'SalesRep3', 2)
    insert into Employee values (8, 'MarketingRep1', 3)
    insert into Employee values (9, 'MarketingRep2', 3)
    insert into Employee values (10, 'FinanceRep1', 4)
    insert into Employee values (11, 'FinanceRep2', 4)
    insert into Employee values (12, 'FinanceRep3', 4)
    insert into Employee values (13, 'FinanceRep4', 4)

    SELECT m.EmployeeName ManagerName, e.EmployeeName SubOrdinates FROM Employee m JOIN Employee e
    ON m.EmployeeID = e.ManagerID
    WHERE m.EmployeeID <> e.EmployeeID
     
    Last edited: Apr 10, 2012
    1 person likes this.
  3. anu333

    anu333 Senior IL'ite

    Messages:
    77
    Likes Received:
    18
    Trophy Points:
    23
    Gender:
    Female
    Awesome Prtywomen, thnks so much for the reply.....

    Also, I would like to add more

    1.when you talk about the differences between unique+not null compared to Primary Key, The performance is low with Unique+Not Null as compared to primary key! Also, Primary key is used for referential integrity while Unique+Not null is only for data integrity and validations.

    3. For the restrictions for a foreign key constraint, the child and parent table must be on the same Db and the composite FK cannot have more than 32 columns and it cannot be applied on tables with datatypes as LOB,LONG,VARRAY,Nested table etc
     
  4. anu333

    anu333 Senior IL'ite

    Messages:
    77
    Likes Received:
    18
    Trophy Points:
    23
    Gender:
    Female
    Thanks KKusuma, for explaing the Coalesce function, yes it is similar to the Case function
     
  5. anu333

    anu333 Senior IL'ite

    Messages:
    77
    Likes Received:
    18
    Trophy Points:
    23
    Gender:
    Female
    Now, questions for Day 3

    1. Can alias names be used in order by clause?
    2. What is the difference between where and having clause?
    3. What are the guidelines to use Group by clause?
    4. What are the different aggregate functions?
    5. What is the difference between count(columnname) and count(*)?
    6. Why is having clause used after group by clause?

    The above are few questions, I could think of from the days topic, the next topic is Subqueries, the tricky one! All geared up for the prep!
     
  6. anuram09

    anuram09 IL Hall of Fame

    Messages:
    820
    Likes Received:
    6,370
    Trophy Points:
    370
    Gender:
    Female
    Just my 2 cents. Please correct my understanding
    Case expression is not limited to find out non null expression. It is used to construct a if-else loop in a query itself.
    coalesce is to return first non-null epxression as said above.
    nvl will let you substitute a value if a null is encountered.
    nvl('test string',value_if_null)
    nvl2 extends the nvl function and let you substitute 1 value if null is encountered and another value for not null
    nvl2('test string',value_if_not_null,value_if_null)
     
    1 person likes this.
  7. Nettem

    Nettem New IL'ite

    Messages:
    400
    Likes Received:
    175
    Trophy Points:
    0
    Gender:
    Female
    1 person likes this.
  8. sravanitenali

    sravanitenali IL Hall of Fame

    Messages:
    3,447
    Likes Received:
    4,900
    Trophy Points:
    325
    Gender:
    Female
    HI Anu i am delayed here in study process due to long weekend now i am on track....here are my anws in RED....

    1. Can alias names be used in order by clause?
    Alias Names cannot be used in any of the clauses like Order BY, Having or group by....when we need to use the pupose of writing alias names, work around may be choosing sub-queries
    Eg: SELECT vendorname, invqty, invavg
    FROM
    (
    select vendorname, count(*) as invqty, avg(invoicetotal) as invavg
    from vendors join invoices on vendors.vendorid=invoices.vendorid group by vendorname ) x
    where invavg > 500
    order by invqty desc;

    2. What is the difference between where and having clause?
    "Where " clause is used to select the rows that are included in the query. for eg consider a table EMP, User need rows EMPNAME, EMPNO, SAL who belongs to couple of states for eg consider AP, TN...in this case Where clause works good....
    Query: select EMPNAME, EMPNO, SAL from EMP where state IN (AP, TN);
    It retrieves the rows that are mentioned in the Query.

    When the requirement is like retrieve the Employee sal irresptive of persons and W.r.t(Group By) States...then Having Clause came into picture....
    Query: select state, SUM(SAL) from EMP Group BY state Having SUM(SAL)>0 Order BY state;
    Here we will get output:
    State SUM(SAL)
    ------------------------------
    AP 10000.00
    TN 20000.00
    ---> Another difference also is by using HAVING clause we can use aggregate functions as condition to filter the rows....where as we cannot use aggregate functions in "WHERE" clause.
    3. What are the guidelines to use Group by clause?
    DEF: Group BY clause is used in a select statement to collect data across multiple records and group the results wrt one or more columns.
    syntax: select col1,col2...aggregate_func(expr) from Test where condition group by col1, col2..col_n;
    if we observe here aggr_func() is there...it reflects aggregate functions like SUM, COUNT, MIN, MAX....
    Eg: select deptName, SUM(sales) as "total sales" from Order_details Group BY deptName;
    here we get output of each department sales information grouped by depatment name.

    4. What are the different aggregate functions?
    SUM, MAX, MIN, COunt(*)...
    5. What is the difference between count(columnname) and count(*)?
    count(*)---> this aggregate function is used to get the return all values in the columns including NULL values.
    eg: select custID, count(*) from items group by custID having count(*)>1;
    This will return all values of the colum custID which are null also.
    count(columnname)--> this will return records only where the specified column name is NOT NULL.
    eg: select custID, count(custID) from items group by custID having count(custID)>1;
    this will return only the records whose custID column is not NULL.
    6. Why is having clause used after group by clause?
    The purpose of Group by clause is to display the results by grouping more than one column. And
    Having Clause is used to apply condition on that group, it may be single col condition or an aggregate function...
     
    3 people like this.
  9. anuram09

    anuram09 IL Hall of Fame

    Messages:
    820
    Likes Received:
    6,370
    Trophy Points:
    370
    Gender:
    Female
    Hi Sravani,
    Thanks for the answers. I have a doubt in the above answer. What is a single column condition in having clause? Please explain with an example.
     
  10. sravanitenali

    sravanitenali IL Hall of Fame

    Messages:
    3,447
    Likes Received:
    4,900
    Trophy Points:
    325
    Gender:
    Female
    Sorry for usage of wrong word....i mean it as without aggregate functions...like as in below example:
    select col1 from Test Group By col1 Having Col1>1;

    No Aggregate function is used here......hope you got my point here.
     

Share This Page