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!

Join Table query....

Discussion in 'SQL PL/SQL' started by MISTATE, Sep 17, 2009.

  1. MISTATE

    MISTATE Guest

    I need help bad with this project, I have come to the final step and cannot figure it out. Please keep in mind Im a novice at best...

    Instruction given for final step:
    "Develop the SELECT query that will display PID (i.e. Project Id, project title, project budget, project leader id, and the Leader’s full name in the ascending order of project id (as shown below). Hints you will need a Join operation. You will also need to derive the Leader’s name column. Be sure to format the budget field."


    There are three tables as the following:
    1)
    CREATE TABLE A1Employee(
    Eid VARCHAR2(4) CONSTRAINT nn_maEID NOT NULL,
    FirstName VARCHAR2(10) CONSTRAINT nn_maFName NOT NULL,
    LastName VARCHAR2(10) CONSTRAINT nn_maLName NOT NULL,
    Monthly_Salary NUMBER(10,2),
    City VARCHAR2(12),
    State Varchar2(2),
    CONSTRAINT PKA1Employee PRIMARY KEY(Eid)
    );

    2)
    CREATE TABLE A1Project(
    Pid VARCHAR2(5)PRIMARY KEY,
    PTitle VARCHAR2(20),
    PStartDate DATE,
    PBudget NUMBER(10,2),
    PLeader VARCHAR2(4) CONSTRAINT nl_PLeader NULL,
    CONSTRAINT _fk
    FOREIGN KEY (PLeader)
    REFERENCES A1Employee(EId)
    );

    3)
    CREATE TABLE A1PrjEmp(
    PId VARCHAR2(5),
    EId VARCHAR2(4),
    AsnDate DATE,
    EHours NUMBER(3,0),
    CONSTRAINT pk_PrjEmp
    PRIMARY KEY (PId, EId),
    CONSTRAINT fk_A1PrjEmpToEmp
    FOREIGN KEY (Eid)
    REFERENCES A1Employee(Eid),
    CONSTRAINT fk_A1PrjEmpToPrj
    FOREIGN KEY (PId)
    REFERENCES A1Project(PId)
    );

    My attempt at the final step SQL:

    SELECT * FROM A1Project;
    SELECT * FROM A1Employee;
    COLUMN FULLNAME HEADING 'Leader Name';
    COLUMN PBUDGET FORMAT $99,999.99;

    SELECT PID, PTITLE, PBUDGET, PLEADER, FIRSTNAME||' '||LASTNAME AS FULLNAME
    FROM A1Project , A1Employee;
    WHERE PLEADER = EId


    Any help or thoughts?
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I have done nothing more than move the semicolon in your query and add an order by:

    Code (SQL):
    SQL> CREATE TABLE A1Employee(
      2          Eid VARCHAR2(4) CONSTRAINT nn_maEID NOT NULL,
      3          FirstName VARCHAR2(10) CONSTRAINT nn_maFName NOT NULL,
      4          LastName VARCHAR2(10) CONSTRAINT nn_maLName NOT NULL,
      5          Monthly_Salary NUMBER(10,2),
      6          City VARCHAR2(12),
      7          State Varchar2(2),
      8          CONSTRAINT PKA1Employee PRIMARY KEY(Eid)
      9  );

    TABLE created.

    SQL>
    SQL> INSERT INTO a1employee
      2  SELECT empno, 'BOB', ename, sal, 'DULUTH','MN'
      3  FROM emp;

    14 ROWS created.

    SQL>
    SQL> commit;

    Commit complete.

    SQL>
    SQL> CREATE TABLE A1Project(
      2          Pid VARCHAR2(5) PRIMARY KEY,
      3          PTitle VARCHAR2(20),
      4          PStartDate DATE,
      5          PBudget NUMBER(10,2),
      6          PLeader VARCHAR2(4) CONSTRAINT nl_PLeader NULL,
      7          CONSTRAINT a1employee_fk
      8          FOREIGN KEY (PLeader)
      9          REFERENCES A1Employee(EId)
     10  );

    TABLE created.

    SQL>
    SQL> INSERT INTO a1project
      2  SELECT 'ABC'||lpad(rownum,2,'0'), 'YakkaBlakka'||lpad(rownum,2,'0'), sysdate-(100*rownum), nvl(sal,10000), empno FROM emp;

    14 ROWS created.

    SQL>
    SQL> CREATE TABLE A1PrjEmp(
      2          PId VARCHAR2(5),
      3          EId VARCHAR2(4),
      4          AsnDate DATE,
      5          EHours NUMBER(3,0),
      6          CONSTRAINT pk_PrjEmp
      7          PRIMARY KEY (PId, EId),
      8          CONSTRAINT fk_A1PrjEmpToEmp
      9          FOREIGN KEY (Eid)
     10          REFERENCES A1Employee(Eid),
     11          CONSTRAINT fk_A1PrjEmpToPrj
     12          FOREIGN KEY (PId)
     13          REFERENCES A1Project(PId)
     14  );

    TABLE created.

    SQL>
    SQL> INSERT INTO a1prjemp
      2  SELECT 'ABC'||lpad(MOD(rownum,7)+1,2,'0'), empno, sysdate, 100 FROM emp;

    14 ROWS created.

    SQL>
    SQL> commit;

    Commit complete.

    SQL>
    SQL> -- My attempt at the final step SQL:
    SQL>
    SQL> SELECT * FROM A1Project;

    PID   PTITLE               PSTARTDATE               PBUDGET PLEA
    ----- -------------------- -------------------- ----------- ----
    ABC01 YakkaBlakka01        09-JUN-2009 12:27:18     $800.00 7369
    ABC02 YakkaBlakka02        01-MAR-2009 12:27:18   $1,600.00 7499
    ABC03 YakkaBlakka03        21-NOV-2008 12:27:18   $1,250.00 7521
    ABC04 YakkaBlakka04        13-AUG-2008 12:27:18   $2,975.00 7566
    ABC05 YakkaBlakka05        05-MAY-2008 12:27:18   $1,250.00 7654
    ABC06 YakkaBlakka06        26-JAN-2008 12:27:18   $2,850.00 7698
    ABC07 YakkaBlakka07        18-OCT-2007 12:27:18   $2,450.00 7782
    ABC08 YakkaBlakka08        10-JUL-2007 12:27:18   $3,000.00 7788
    ABC09 YakkaBlakka09        01-APR-2007 12:27:18   $5,000.00 7839
    ABC10 YakkaBlakka10        22-DEC-2006 12:27:18   $1,500.00 7844
    ABC11 YakkaBlakka11        13-SEP-2006 12:27:18   $1,100.00 7876

    PID   PTITLE               PSTARTDATE               PBUDGET PLEA
    ----- -------------------- -------------------- ----------- ----
    ABC12 YakkaBlakka12        05-JUN-2006 12:27:18     $950.00 7900
    ABC13 YakkaBlakka13        25-FEB-2006 12:27:18   $3,000.00 7902
    ABC14 YakkaBlakka14        17-NOV-2005 12:27:18   $1,300.00 7934

    14 ROWS selected.

    SQL> SELECT * FROM A1Employee;

    EID  FIRSTNAME  LASTNAME   MONTHLY_SALARY CITY         ST
    ---- ---------- ---------- -------------- ------------ --
    7369 BOB        SMITH                 800 DULUTH       MN
    7499 BOB        ALLEN                1600 DULUTH       MN
    7521 BOB        WARD                 1250 DULUTH       MN
    7566 BOB        JONES                2975 DULUTH       MN
    7654 BOB        MARTIN               1250 DULUTH       MN
    7698 BOB        BLAKE                2850 DULUTH       MN
    7782 BOB        CLARK                2450 DULUTH       MN
    7788 BOB        SCOTT                3000 DULUTH       MN
    7839 BOB        KING                 5000 DULUTH       MN
    7844 BOB        TURNER               1500 DULUTH       MN
    7876 BOB        ADAMS                1100 DULUTH       MN

    EID  FIRSTNAME  LASTNAME   MONTHLY_SALARY CITY         ST
    ---- ---------- ---------- -------------- ------------ --
    7900 BOB        JAMES                 950 DULUTH       MN
    7902 BOB        FORD                 3000 DULUTH       MN
    7934 BOB        MILLER               1300 DULUTH       MN

    14 ROWS selected.

    SQL> COLUMN FULLNAME HEADING 'Leader Name';
    SQL> COLUMN PBUDGET FORMAT $99,999.99;
    SQL>
    SQL> SELECT PID, PTITLE, PBUDGET, PLEADER, FIRSTNAME||' '||LASTNAME AS FULLNAME
      2  FROM A1Project , A1Employee
      3  WHERE PLEADER = EId
      4  ORDER BY pid;

    PID   PTITLE                   PBUDGET PLEA Leader Name
    ----- -------------------- ----------- ---- ---------------------
    ABC01 YakkaBlakka01            $800.00 7369 BOB SMITH
    ABC02 YakkaBlakka02          $1,600.00 7499 BOB ALLEN
    ABC03 YakkaBlakka03          $1,250.00 7521 BOB WARD
    ABC04 YakkaBlakka04          $2,975.00 7566 BOB JONES
    ABC05 YakkaBlakka05          $1,250.00 7654 BOB MARTIN
    ABC06 YakkaBlakka06          $2,850.00 7698 BOB BLAKE
    ABC07 YakkaBlakka07          $2,450.00 7782 BOB CLARK
    ABC08 YakkaBlakka08          $3,000.00 7788 BOB SCOTT
    ABC09 YakkaBlakka09          $5,000.00 7839 BOB KING
    ABC10 YakkaBlakka10          $1,500.00 7844 BOB TURNER
    ABC11 YakkaBlakka11          $1,100.00 7876 BOB ADAMS

    PID   PTITLE                   PBUDGET PLEA Leader Name
    ----- -------------------- ----------- ---- ---------------------
    ABC12 YakkaBlakka12            $950.00 7900 BOB JAMES
    ABC13 YakkaBlakka13          $3,000.00 7902 BOB FORD
    ABC14 YakkaBlakka14          $1,300.00 7934 BOB MILLER

    14 ROWS selected.

    SQL>