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!

Help with an easy query for a test

Discussion in 'SQL PL/SQL' started by calamardo114, Oct 10, 2016.

  1. calamardo114

    calamardo114 Starter

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    UK
    Hi!!
    I need some help with this excercise. Any help will be very much appreciated. Thank you!

    Assume you have two tables in an Oracle database, one named BOOK and one named AUTHOR. There is a column in BOOK named author_id that references the AUTHOR table. The idea here is that a single author may have written multiple books. Write an SQL query that produces a result similar to this:

    Author Name Total number of books written

    Charles Dickens 23
    Dr. Seuss 51
    Norman Mailer 20
    Harper Lee 1
    Jane Doe 0
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    What have you written to solve this problem? Post your code and we can see where you may have gone wrong. We'll help you but we won't write your assignments for you.
     
  3. Muhammad Tariq

    Muhammad Tariq Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    KSA
    Code (SQL):
    SELECT A.AUTHOR, COUNT(B.BOOK_NAME)
    FROM AUTHORS A
    INNER JOIN BOOKS B ON B.AUTHOR_ID = A.AUTHOR_ID
    GROUP BY A.AUTHOR;
    S
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    First I see no output from this query so it apparently wasn't tested; not testing queries before they are posted for others to use is a mistake as they may not work as expected. Second this is someone apparently wanting US to do their work for them, and you did that quite readily without asking to see what THEY might have done to solve the problem. This is also a mistake as no one learns from such responses.
     
  5. Muhammad Tariq

    Muhammad Tariq Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    KSA
    It just an idea to the user to use this pattern I didn't use the same table he mentioned in his query. we are here to give an idea to them how can they achieve their goal not always provide a solution.
    This query is perfect if he just replace table and column name i am 100% sure it will work.
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Yet you failed to test it and prove that it works. That is the issue here. I created tables and ran your statement and, yes, it does work:

    Code (SQL):
    SQL> CREATE TABLE authors(
      2  author_id  NUMBER,
      3  author             varchar2(60));

    TABLE created.

    SQL>
    SQL> CREATE TABLE books(
      2  author_id  NUMBER,
      3  book_name  varchar2(60));

    TABLE created.

    SQL>
    SQL> INSERT INTO authors
      2  VALUES(1, 'Snorm Yazbut');

    1 ROW created.

    SQL> INSERT INTO authors
      2  VALUES(2, 'Iop Treq');

    1 ROW created.

    SQL> INSERT INTO authors
      2  VALUES(3, 'Kluy Vgupta');

    1 ROW created.

    SQL>
    SQL> commit;

    Commit complete.

    SQL>
    SQL> BEGIN
      2     FOR i IN 1..100 loop
      3             INSERT INTO books
      4             VALUES(MOD(i,3)+1, 'Book '||i);
      5     END loop;
      6     commit;
      7  END;
      8  /

    PL/SQL PROCEDURE successfully completed.

    SQL>
    SQL> SELECT A.AUTHOR, COUNT(B.BOOK_NAME)
      2  FROM AUTHORS A
      3  INNER JOIN BOOKS B ON B.AUTHOR_ID = A.AUTHOR_ID
      4  GROUP BY A.AUTHOR;

    AUTHOR                                                       COUNT(B.BOOK_NAME)
    ------------------------------------------------------------ ------------------
    Kluy Vgupta                                                                  33
    Iop Treq                                                                     34
    Snorm Yazbut                                                                 33

    SQL>
    Again the issue is that YOU didn't test it to verify it works. You posted another query in another thread that does NOT work:

    Code (SQL):
    SQL> SELECT *
      2  FROM EMP1 TB1
      3  FULL OUTER JOIN EMP2 TB2 ON TB1.EMPID = TB2.EMPID;
    FULL OUTER JOIN EMP2 TB2 ON TB1.EMPID = TB2.EMPID
                                            *
    ERROR at line 3:
    ORA-00904: "TB2"."EMPID": invalid identifier


    SQL>
    SQL> SELECT *
      2  FROM EMP1 TB1
      3  FULL OUTER JOIN EMP2 TB2 ON TB1.EMPLOYEE_ID = TB2.EMPLOYEE_ID;

    EMPLOYEE_ID EMPLOYEE_ID
    ----------- -----------
                         11
                         12
                         13
                         14
                         15
                         16
                         17
                         18
                         19
                         20
              5

    EMPLOYEE_ID EMPLOYEE_ID
    ----------- -----------
              8
              3
              1
              2
             10
              6
              7
              4
              9

    20 ROWS selected.

    SQL>
    SQL> SELECT *
      2  FROM EMP1 TB1
      3  FULL OUTER JOIN EMP2 TB2 ON TB1.EMPLOYEE_ID = TB2.EMPLOYEE_ID
      4  ORDER BY 1, 2;

    EMPLOYEE_ID EMPLOYEE_ID
    ----------- -----------
              1
              2
              3
              4
              5
              6
              7
              8
              9
             10
                         11

    EMPLOYEE_ID EMPLOYEE_ID
    ----------- -----------
                         12
                         13
                         14
                         15
                         16
                         17
                         18
                         19
                         20

    20 ROWS selected.

    SQL>
    even when modified to provide the correct column names as it produces output in two columns instead of one, as the original poster asked for.

    Posting code that isn't tested does a great disservice to you and those you are trying to assist, as you can't prove that it works and the person testing it can wonder WHY it's not working and blame themselves.