1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Sql query

Discussion in 'SQL PL/SQL' started by tabjula, Feb 22, 2013.

  1. tabjula

    tabjula Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Hi All ,

    For a given ID, S_Type values should be same.
    If 'IB' string is mentioned, then S_Type can be either 'IB' or 'RT' for a given ID. If not it is an invalid record.


    CREATE TABLE TEST_1(ID NUMBER,S_TYPE VARCHAR2(20));
    INSERT INTO TEST_1 VALUES(1,'IB');
    INSERT INTO TEST_1 VALUES(1,'RT');
    INSERT INTO TEST_1 VALUES(1,'IB');
    INSERT INTO TEST_1 VALUES(1,'RT');
    INSERT INTO TEST_1 VALUES(1,'UI');
    INSERT INTO TEST_1 VALUES(1,'WE');
    INSERT INTO TEST_1 VALUES(2,'CI');
    INSERT INTO TEST_1 VALUES(2,'CI');
    INSERT INTO TEST_1 VALUES(2,'TY');
    INSERT INTO TEST_1 VALUES(2,'RF');
    INSERT INTO TEST_1 VALUES(3,'KKK');
    INSERT INTO TEST_1 VALUES(4,'OOO');

    REQUIRED DATA:

    ID S_TYPE
    1 UI
    1 WE
    2 TY
    2 RF

    Please help me on this.

    regards,
    dileep.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Your requirement makes no sense:

    "For a given ID, S_Type values should be same.
    If 'IB' string is mentioned, then S_Type can be either 'IB' or 'RT' for a given ID. If not it is an invalid record."

    The second sentence conflicts with the requirement of the first sentence. Which one is correct? Also the 'REQUIRED DATA' section is confusing -- what does that indicate?
     
  3. tabjula

    tabjula Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Hi

    1) id ,s_type should be same, example
    id s_type
    1 IB
    1 IB
    1 IB
    1 IB
    Example:2
    id s_type
    1 IB
    1 IB
    1 RT
    1 RT
    S_Type can be either 'IB' or 'RT' for ID.

    2) Required data is:
    example:
    id s_type
    1 IB
    1 IB
    1 UR

    For this required data is:
    id s_type
    1 UR

    example:2
    ID S_TYPE
    1 IB
    1 IB
    1 IB
    1 RT
    1 RT
    1 GH

    For this required data is:
    id s_type
    1 GH



    i need data only for cout(id) >1 .

    please help me on this.

    regards,
    dileep.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    From what I see you want to 'ignore' the two S_TYPE values of IB and RT for id 1 and count the occurrences of the other S_TYPEs associated with that id. What have you written to solve this problem?
     
  5. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    @ tabjula

    In addition, your test case is not normalized (not even a primary key)
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    There are many tables in relational databases which have no primary key; it is not a requirement to have one. I'm curious what that has to do with the solution to the posted 'problem'.
     
  7. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    That is true.

    That is also true.

    It has nothing to do with the solution to the posted problem directly, and this is precisely (if you read my last comment) why I started my statement by "In addition", which means an extra information that might be helpful for future reference for him (I mean @ tabjula) :)

    So far, I've seen many ERP and application databases and all of them have always been normalized. It is true that it is not a requirement. For me, it's a matter of choice during application design and when it is well done (because a highly normalized bad designed data model may even worsen the performance of the database) it could help to improve data coherence and data access.

    Regards,
    Dariyoosh
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Your comments are noted but, again, they have no real application so the solution of the 'problem'. What really piques my curiosity is that the original poster hasn't answered my original question of what he or she has written to solve the problem. Certainly we could provide an answer but, it appears, the original posted appears to be wanting someone else to do his or her work.