Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2009
    Posts
    3

    Unanswered: oracle SQL query help

    I'm taking a database besign class but I cannot get my queries to work, this is what I've got.

    Using oracle SQL plus I made user name mnfg and granted all permissions, Created two tables Employee and Product with this code

    CREATE TABLE employee (TEAMNAME varchar2(6), EMPNUM varchar2(6),EMPLNAME varchar2(32), EMPFNAME varchar2(20), SUPERVISOR varchar2(5), MFGLINE varchar2(10), primary key (EMPNUM));
    CREATE TABLE product (PRODNUM varchar2(4), PRODDESC varchar2(46), MFGLINE varchar2(1), BOMROLLUP varchar2(6), QTYONHAND varchar2(3), PRIMARY KEY (ProdNum));

    This is my assignment and the two tables with their information
    RelativeResourceManager.pdf - File Shared from Box.net - Free Online File Storage
    Right below #5 are the tables and below that are the queries needed anything above that is just some curriculum

    This is the sql file that is all my code so far
    mnfg.sql - File Shared from Box.net - Free Online File Storage

    DONEI did everything down to # 7 and the first part of 8

    8.In your manufacturing schema create these two views:
    •View Name: EmpRollup. Fields: Employee Number, Last Name, First Name. Supervisor Number, Supervisor Last Name, Supervisor First Name. I got this one to work

    SELECT temp1.EMPNUM, temp1.EMPLNAME, temp1.EMPFNAME, temp1.SUPERVISOR, temp2.EMPNUM AS "SuperNum", temp2.EMPLNAME, temp2.EMPFNAME
    FROM (SELECT * FROM employee)temp1, (select * FROM employee)temp2
    WHERE temp1.supervisor = temp2.empnum
    ORDER BY temp1.EMPNUM;

    using this code

    •View Name: ProdRollup. Fields: Product Number, Product Description, Product Rolls Up to Number, Product Rolls Up to Description. but I couldnt get this code to work
    SELECT temp1.PRODNUM, temp1.PRODDESC, temp1.BOMROLLUP temp2.PRODNUM AS "Rolls up to", temp2.BOMROLLUP, temp2.PRODDESC
    FROM (SELECT * FROM product)temp1, (select * FROM product)temp2
    WHERE temp1.BOMROLLUP = temp2.PRODNUM
    ORDER BY temp1.PRODNUM;

    it said "from" statement found not where expected
    DONE


    I couldn't get any of these queries to work
    9. a.Create an SQL query that counts all the parts that roll up into part 101 and sum the quantities. Your query should show: Product Number, Product Description, Count of Rollup Products, Sum of Rollup Quantities. The output from your query should be only one line, namely, just showing product 101.
    b.create another SQL query that selects everything from your EmpRollup view
    c.create another SQL query that selects everything from your ProdRollup view

    I welcome and appreciate and advice and help
    Last edited by loserspearl; 04-04-09 at 14:55.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1

    SQL Manual

    What version of Oracle to 4 decimal places?

    Contents
    If you get syntax errors, then the problem exists between keyboard & chair.
    Valid SQL is completely documented in manual above.

    Code:
    CREATE TABLE employee ( 
      teamname   VARCHAR2(6), 
      empnum     VARCHAR2(6), 
      emplname   VARCHAR2(32), 
      empfname   VARCHAR2(20), 
      supervisor VARCHAR2(5), 
      mfgline    VARCHAR2(10), 
         PRIMARY KEY ( EMPNUM )); 
    
    CREATE TABLE product ( 
      prodnum   VARCHAR2(4), 
      proddesc  VARCHAR2(46), 
      mfgline   VARCHAR2(1), 
      bomrollup VARCHAR2(6), 
      qtyonhand VARCHAR2(3), 
         PRIMARY KEY ( ProdNum ));
    Which looks better? Yours or mine?
    Last edited by anacedent; 04-02-09 at 23:16.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Feb 2005
    Posts
    57
    Quote Originally Posted by loserspearl
    using this code

    •View Name: ProdRollup. Fields: Product Number, Product Description, Product Rolls Up to Number, Product Rolls Up to Description. but I couldnt get this code to work
    SELECT temp1.PRODNUM, temp1.PRODDESC, temp1.BOMROLLUP temp2.PRODNUM AS "Rolls up to", temp2.BOMROLLUP, temp2.PRODDESC
    FROM (SELECT * FROM product)temp1, (select * FROM product)temp2
    WHERE temp1.BOMROLLUP = temp2.PRODNUM
    ORDER BY temp1.PRODNUM;

    it said "from" statement found not where expected
    You are missing a comma between temp1.BOMROLLUP and temp2.PRODNUM

    hth

  4. #4
    Join Date
    Apr 2009
    Posts
    3
    outrider: ya i looked at it for like 2 hours and didnt see that, always better for just another set of eyes to see it.

    it worked now

    anacedent:I am using crimson editor to write my SQLcode and my oracle version is "Oracle Database 10g Enterprise Edition Release 10.2.0.1.0" lol

    I still cannot get number 9 a, b, or c to work.

    Thanks for the help.

  5. #5
    Join Date
    Apr 2009
    Posts
    3
    bump anyone?

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I still cannot get number 9 a, b, or c to work.
    My car does not work.
    Tell me how to make my car go.
    While your statement is likely 100% true, it provides no useful data.

    Post DDL for tables.
    Post DML for test data.

    Post expected/desired results.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Feb 2005
    Posts
    57
    If you have created the views as per number 8 then numbers 9b and c look like they should be a straight forward select * from these views. I'm not so sure about 9a though

    but as anacedent says it would be helpful to know what you've tried and what errors you are getting.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •