SQL query command Oracle database "return orders with either 25+ items or $1,000+ spent?"

I am having trouble trying to develop a query to return orders with either 25+ items or $1,000+ spent, who was the customer and were they satisfied? Any hints or help?  Also, these are made up tables for practice with SQL. Thanks.

Here is what I have started, which is wrong because it is returning 126 rows. It should only return Liam Neeson and Dwayne Wade.

SELECT A.ORDER_ID, A.CUST_ID, A.PRICE, A.NUM_ITEMS, B.NAME, C.REVIEW, C.RATING
FROM ORDERS A, WEB_CUSTOMERS B, REVIEWS C
WHERE PRICE > 1000 OR NUM_ITEMS >= 25;


DROP TABLE ORDERS;
DROP TABLE WEB_CUSTOMERS;
DROP TABLE REVIEWS;
CREATE TABLE ORDERS	(
	ORDER_ID	NUMBER NOT NULL PRIMARY KEY,
	CUST_ID		NUMBER  NOT NULL,
	PRICE		NUMBER  NOT NULL,
	NUM_ITEMS	NUMBER  NOT NULL);
CREATE TABLE WEB_CUSTOMERS	(
	CUST_ID	NUMBER NOT NULL PRIMARY KEY,
	NAME		VARCHAR2(15)  NOT NULL,
	ADDRESS		VARCHAR2(16)  NOT NULL);
CREATE TABLE REVIEWS	(
	ORDER_ID	NUMBER NOT NULL PRIMARY KEY,
	RATING		NUMBER,
	REVIEW		VARCHAR2(23)); 
INSERT INTO ORDERS VALUES (0,0,12,2);
INSERT INTO ORDERS VALUES (1,0,89,4);
INSERT INTO ORDERS VALUES (2,1,139,7);
INSERT INTO ORDERS VALUES (3,1,201,4);
INSERT INTO ORDERS VALUES (4,2,235,8);
INSERT INTO ORDERS VALUES (5,0,21,1);
INSERT INTO ORDERS VALUES (6,3,2000,26);
INSERT INTO ORDERS VALUES (7,4,50,1);
INSERT INTO ORDERS VALUES (8,5,900,30);

SELECT * FROM ORDERS;
INSERT INTO WEB_CUSTOMERS VALUES (0,'Viggo Mortensen', '1 CampusDr 49401');
INSERT INTO WEB_CUSTOMERS VALUES (1,'Clint Eastwood', '12 Main 49426');
INSERT INTO WEB_CUSTOMERS VALUES (2,'Simon Pegg', '1980 20th 49428');
INSERT INTO WEB_CUSTOMERS VALUES (3,'Liam Neeson', '2870 8th 49424');
INSERT INTO WEB_CUSTOMERS VALUES (4,'Lebron James', '2306 Ball 49503');
INSERT INTO WEB_CUSTOMERS VALUES (5,'Dwayne Wade', '5566 3rd 49341');
INSERT INTO WEB_CUSTOMERS VALUES (6,'Kevin Love', '7898 9th 45689');
INSERT INTO WEB_CUSTOMERS VALUES (7,'Kevin Garnett', '8798 12th 45689');
INSERT INTO WEB_CUSTOMERS VALUES (8,'Calvin Harris', '5555 5th 45608');
SELECT * FROM WEB_CUSTOMERS;
INSERT INTO REVIEWS VALUES (0,10,'Items were great!’);
INSERT INTO REVIEWS VALUES (2,5, 'Items were alright');
INSERT INTO REVIEWS VALUES (3,7,'Shipping took too long');
INSERT INTO REVIEWS VALUES (4,2,'Not what I ordered!');
INSERT INTO REVIEWS VALUES (5,8,'Excellent Product!');
INSERT INTO REVIEWS VALUES (6,10,'Fast shipping!');
INSERT INTO REVIEWS VALUES (7,1,'Not correct order');
INSERT INTO REVIEWS VALUES (8,4,'Very slow shipping');
SELECT * FROM REVIEWS;
SELECT A.ORDER_ID, B.NAME, C.RATING, C.REVIEW FROM ORDERS a, WEB_CUSTOMERS B, REVIEWS C WHERE A.CUST_ID = B.CUST_ID AND A.ORDER_ID = C.ORDER_ID and c.rating=10;

Open in new window

John SmithAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ioannis ParaskevopoulosCommented:
Hi,

What you have done with your join is fundamentally wrong. You have a cartesian product. If you want to join correctly, you need to define on clauses or proper wheres.

In my opinion you should use proper join and on clauses, as it makes the query more readable:

SELECT A.ORDER_ID, B.NAME, C.RATING, C.REVIEW 
FROM ORDERS A
JOIN WEB_CUSTOMERS B
ON A.CUST_ID = B.CUST_ID
JOIN REVIEWS C 
ON A.ORDER_ID = C.ORDER_ID
WHERE A.PRICE > 1000 OR A.NUM_ITEMS >= 25 

Open in new window


Reviewing your SELECT in your code sample, i see that you did define the ONs on the WHERE clause. That is another way of creating JOINs , but i highly recomend you change to proper JOIN syntax.

I would also like to mention that INNER JOINs are not always the best case as they will return only matching rowd. For instance, in your example, imagine an Order, that has no Review. This Order does have a price and a Um_Items, but would not be return because of the ON condition and the fact that you defined an INNER JOIN. A  more proper way of handling this would be to use a LEFT JOIN and start from the table you wish to get all the results from, the Orders:

SELECT A.ORDER_ID, B.NAME, C.RATING, C.REVIEW 
FROM ORDERS A
LEFY JOIN WEB_CUSTOMERS B
ON A.CUST_ID = B.CUST_ID
LEFT JOIN REVIEWS C 
ON A.ORDER_ID = C.ORDER_ID
WHERE A.PRICE > 1000 OR A.NUM_ITEMS >= 25 

Open in new window


Giannis

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
John SmithAuthor Commented:
Ok that makes more sense. Thank you.

Now if I am trying find which users left poor/negative feedback (rating < 5) and how many orders have each of these users’ placed?

I did this but I am getting a ORA-00979: not a GROUP BY expression error,


SELECT A.CUST_ID, COUNT(A.ORDER_ID), B.NAME, C.RATING, C.REVIEW 
FROM ORDERS A,
WEB_CUSTOMERS B, REVIEWS C 
WHERE A.CUST_ID = B.CUST_ID AND B.ORDER_ID = C.ORDER_ID
AND C.RATING < 5
GROUP BY A.CUST_ID
ORDER BY A.CUST_ID;

Open in new window

Ioannis ParaskevopoulosCommented:
Hi,

When you 'GROUP BY' you need to specify all columns that do not get aggregated. In your case you need to

GROUP BY A.CUST_ID, B.NAME, C.RATING, C.REVIEW 

Open in new window


Giannis
Mark GeerlingsDatabase AdministratorCommented:
I disagree with this statement: "A more proper way of handling this would be to use a LEFT JOIN".  It is true that in this three-table example with INNER JOIN connections, that if a matching record is missing in one of the three tables, the rows from the other two tables will not be returned.  But, whether that is good or bad depends on the application.  In some cases, you don't want incomplete rows to be returned, so in those cases an INNER JOIN (or direct join in the older Oracle syntax that John Smith suggested) is actually the "proper way" to handle this data.

You have to decide for your application which is best: return rows that are missing data in one of the three tables, or not.  But also be aware that the performance penalty can be significant for using a LEFT JOIN (or "outer join" in classic Oracle syntax) compared to the performance of an INNER JOIN.

In my opinion, if your query needs a LEFT (or OUTER) JOIN, then at least one of these conditions is true:
1. The application was not designed to answer this question.
2. The application has poor (or incomplete) data entry validation.
3. The table design does not fit the business well.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.