SQL help using Oracle Database - NOT MYSQL OR MICROSOFT SQL SERVER! Syntax matte

Question

SQL help using Oracle Database - NOT MYSQL OR MICROSOFT SQL SERVER! Syntax matters

Can someone please write the proper queries for this question? There are two parts to it.

Please don't mark it as "Needs more Info" because this is all of the info I have received to do this.

Link to question if you can't see the image below: http://i.imgur.com/Ex2ScnE.png

Table and database information below but I'm not sure how to include all of the data - if there is a command to show all the data fields for particular tables feel free to let me know.
Link to database information if you can't see the image below: http://i.imgur.com/VwpcPmo.png

Thank you.

Explanation / Answer

Q1:

select SALESPERSONS.EMPID, SALESPERSONS.ENAME, NVL(SUM(ORDERITEMS.qty*INVENTORY.price),0) as SOLD from SALESPERSONS, ORDERS,ORDERITEMS,INVENTORY
where SALESPERSONS.EMPID=ORDERS.EMPID and ORDERS.ORDERID=ORDERITEMS.ORDERID and ORDERITEMS.PARTID=INVENTORY.PARTID
group by SALESPERSONS.EMPID order by SOLD desc


For this query we need to use SALESPERSONS, ORDERS,ORDERITEMS,INVENTORY tables because we have to use EmpID,empname which is present in salesperson
and qty , price which are in orderitems and inventory tables respectively.

But it is not possible to reach orderitems from salesperson table directly because there is no foreign key in salesperson which is primary key of orderitems

So we need to process order table also.

NVL(SUM(ORDERITEMS.qty*INVENTORY.price),0) means if value of sum is null then it replace it with 0.

We used inner join to combine 2 tables example foreign key of one table will be the primary key of other table so using this column we can combine
like I do in salesperson and order table. EMPID is common in both salesperson and order table.

Now we need to use group by clause because for one empID there can be multiples orders. So to handle this we need to group by according to emp ID.

Q2:

SELECT ORDERS.ORDERID, NVL(SUM(ORDERITEM.qty*INVENTORY.price), 0) from ORDERS, ORDERITEMS,INVENTORY
where ORDERS.ORDERID=ORDERITEMS.ORDERID and ORDERITEMS.PARTID=INVENTORY.PARTID
group by ORDERS.ORDERID

For this query we need to use ORDERS,ORDERITEMS,INVENTORY tables because we have to use orderID, qty , price which are in orders, orderitems and inventory tables respectively.

We used inner join to combine 2 tables example foreign key of one table will be the primary key of other table so using this column we can combine
like I do in order and orderItem table. orderID is common in both orderItem and order table.

Now we need to use group by clause because for orderID there can be multiples orderID fro different partID. So to handle this we need to group by according to OrderID.

*******Please check columns names before executing because it is case sensitive***************

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.