Help with oracle SQL query, can't get working

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Help with oracle SQL query, can't get working

Author

Topic

loserspearl
Starting Member

1 Post

Posted - 2009-04-02 : 15:43:45

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));

These are the two tables with their information
Employee Table
TeamName EmpNumber LName FName Supervisor Mfg Line
Red 10201 Smith Jim 10113 Line A
Red 10113 George James (na) Line A
Blue 10998 Gomez Jose (na) Line C
Blue 18765 Palmer Earnst 10998 Line C
Green 12476 Abbott Paul 18634 Line D
Green 17397 Henry Carl 18634 Line D
Green 18634 Cabot Harold (na) Line D
Yellow 23987 Torrance Terry 19324 Line C
Yellow 19324 Carter Eric (na) Line C
Red 16302 Thomas Patrick 10113 Line A
Blue 28712 Buckler Tim 10998 Line C

Product Table
ProdNum ProdDescription MfgLine BOMRollup QtyOnHand
101 Retro Toaster A (na) 12
103 T-Bar CrossHang A 101 32
202 Retro TV Clock A (na) 65
209 Battery Unit A 202 32
232 Plastic Casing – Black A 202 65
234 Plastic Casing – White A 202 38
178 Brass Casing A 101 34
179 Aluminum Housing A 101 28
219 Hand Set A 202 20
220 Brass Knob A 101 87
320 Magnetic Balance Beam B 101 12
349 Beam Bar – Chrome B 320 45
398 Wind-up Clock B (na) 76
and then there like 40 more lines of similar information, I know the tables didn't come out right in the text but bear with me(tell me if you can't figure it out and I'll find a better view) It would be better if I could upload my crimson editor sql text or the pdf file of my assignment

1.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

I couldn't get any of these queries to work
2.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.
3.create another SQL query that selects everything from your EmpRollup view
4.create another SQL query that selects everything from your ProdRollup view

I welcome and appreciate and advice and help

update: here are the files for my sql code and my assignment
PDF class assignment
http://www.box.net/shared/huzfgj0c1x
SQL code
http://www.box.net/shared/75a86v4osa

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-04-02 : 15:54:21

This entire site is for MS Sql Server (not oracle)
Try the forums at http://www.dbforums.com/

Be One with the Optimizer
TG

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-04-02 : 23:05:09

That being said, you can change EmpRollup to:

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

And as far as I know you can use ANSI style joins in Oracle, so this should also work:

SELECT temp1.EMPNUM, temp1.EMPLNAME, temp1.EMPFNAME, temp1.SUPERVISOR, temp2.EMPNUM AS "SuperNum", temp2.EMPLNAME, temp2.EMPFNAME
FROM  employee temp1 INNER JOIN employee temp2 ON temp1.supervisor = temp2.empnum
ORDER BY temp1.EMPNUM;

You can do the same thing for ProdRollup. My guess is that should fix the error.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources