I am stuck on these SQL queries!?

Question

I tried but i got error every time i ran my codes. please provide me with correct code and explanation. Thank You.

SQL Query 1: List the ratio of commission to salary for each employee. For employees
with no commission, the value should be 0. (Do NOT use
the NVL function.)
SQL Query 2: List the customer name, order id, and order date for all orders from all
customers. If a customer has placed no orders, there should be null values
for order id and order date.
SQL Query 3: For each product, list the product id, the order id of the order on which
the greatest quantity of that product was purchased. List also the
quantity

Tables are: CUSTOMER(CUST_ID, CUST_NAME, CUST_ADDRESS, CUST_CITY, CUST_STATE, CUST_ZIP, CUST_AREA, CUST_PHONE, CUST_CREDITLIMIT, CUST_COMMENTS, EMP_ID)
TABLE 2: DEPARTMENT (DEPT_NO, DEPT_NAME, DEPT_LOC)
TABLE 3: EMPLOYEE (EMP_ID, EMP_NAME, EMP_JOB, EMP_MGR, EMP_HIREDATE, EMP_SALARY, EMP_COMMISSION, DEPT_NO)
TABLE 4: ORD (ORD_ID, ORD_ORDERDATE, ORD_COMMPLAN, ORD_SHIPDATE, ORD_TOTAL, CUST_ID)
TABLE 5: ORDERITEM (ORDITEM_ID, ORDITEM_ACTPRICE, ORDITEM_QTY, ORDITEM_TOTAL, ORD_ID, PROD_ID)
TABLE 6: PRODUCT (PROD_ID, PROD_DESCRIP)

Answer 1

I'll do #3 as I'd need some more info regarding #1 (for example, is COALESCE also banned and is EMP_COMMISSION a percentage or what?) and #2 is a fairly straightforward outer join.



Whenever doing a complex query, break it down:

1) How much of each product was purchased in each order?

SELECT prod_id, ord_id, SUM(orditem_qty) AS tot_qty FROM orderitem

GROUP BY prod_id, ord_id

2) For a given product, find the largest such quantity and give the info for its order(s)

SELECT a.* FROM

(SELECT prod_id, ord_id, SUM(orditem_qty) AS tot_qty FROM orderitem

GROUP BY prod_id, ord_id) a

WHERE a.tot_qty >= ALL

(SELECT SUM(orditem_qty) FROM orderitem b

WHERE b.prod_id = a.prod_id GROUP BY b.prod_id, b.ord_id)

Answer 2

Subselects do have an inclination to run sluggish using fact the subselect must be evaluated for each ability row of the standard choose. Your syntax additionally appeared atypical in a pair of places - i'm assuming a typo while copying into your question? attempt this: choose (column checklist of consumers table needed) FROM consumers, user_rooms the place superuser = a million AND room_id = '17' AND identity = userid ORDER via numbered DESC decrease 9

Answer 3

Often the best way to build advance SQL statements is to use a visualiser to allow quick development, like for example this: "http://www.razorsql.com/docs/query_builder.html" or "http://www.dbvis.com/download/"



They show the SQL they've written so you can then add your own on top to extend it.

Answer 4

SELECT EMP_NAME, (EMP_COMMISSION/EMP_SALARY) AS Ratio

FROM EMPLOYEE;



SELECT CUST_NAME, ORD_ID, ORD_ORDERDATE

FROM CUSTOMER, ORD

WHERE CUSTOMER.CUST_ID = ORD.CUST_ID;



SELECT ORDITEM_ID, ORD_ID, MAX(ORDITEM_QTY)

FROM ORDERITEM;



Not sure about the last one. You might need a subquery to accomplish that.



--edit--



Oh yeah, the MP is probably right about the commission. It might be more like...



SELECT EMP_NAME, (SUM(ORD_TOTAL)*EMP_COMMISSION/EMP_SALARY) AS Ratio

FROM EMPLOYEE e, CUSTOMER c, ORD o

WHERE e.EMP_ID = c.EMP_ID

AND o.CUST_ID = c.CUST_ID;