SQL Exercise: Using AND, OR operator with a specified condition
6. Exclude Specific Orders
From the following table, write a SQL query to find details of all orders excluding those with ord_date equal to '2012-09-10' and salesman_id higher than 5005 or purch_amt greater than 1000.Return ord_no, purch_amt, ord_date, customer_id and salesman_id.
Sample table : orders
ord_no purch_amt ord_date customer_id salesman_id ---------- ---------- ---------- ----------- ----------- 70001 150.5 2012-10-05 3005 5002 70009 270.65 2012-09-10 3001 5005 70002 65.26 2012-10-05 3002 5001 70004 110.5 2012-08-17 3009 5003 70007 948.5 2012-09-10 3005 5002 70005 2400.6 2012-07-27 3007 5001 70008 5760 2012-09-10 3002 5001 70010 1983.43 2012-10-10 3004 5006 70003 2480.4 2012-10-10 3009 5003 70012 250.45 2012-06-27 3008 5002 70011 75.29 2012-08-17 3003 5007 70013 3045.6 2012-04-25 3002 5001
Sample Solution :
-- This query selects all columns from the 'orders' table.
SELECT *
-- Specifies the table from which to retrieve the data (in this case, 'orders').
FROM orders
-- Filters the rows to only include those where neither the conditions inside the parentheses are true.
WHERE NOT ((ord_date = '2012-09-10' AND salesman_id > 5005) OR purch_amt > 1000.00);
Output of the Query:
ord_no|purch_amt|ord_date |customer_id|salesman_id| ------|---------|----------|-----------|-----------| 70009| 270.65|2012-09-10| 3001| 5005| 70002| 65.26|2012-10-05| 3002| 5001| 70004| 110.50|2012-08-17| 3009| 5003| 70011| 75.29|2012-08-17| 3003| 5007| 70001| 150.50|2012-10-05| 3005| 5002| 70007| 948.50|2012-09-10| 3005| 5002| 70012| 250.45|2012-06-27| 3008| 5002|
Code Explanation:
The said SQL query that is selecting all columns (*) from the 'orders' table where NOT ( the value of the "ord_date" column is '2012-09-10' AND the value of the "salesman_id" column is greater than 5005 OR the value of the "purch_amt" column is greater than 1000.00) .
There will be a return of all rows from the 'orders' table that do not meet one of these conditions when this query is executed. It will return all the rows where ord_date is not '2012-09-10' or the salesman_id is less than or equal to 5005 or the purchase amount is less than or equal to 1000.
Relational Algebra Expression:

Relational Algebra Tree:

Explanation :
Visual presentation:

Go to:
PREV : Not New York and Not Grade > 100.
NEXT : Salespeople with Commission Range 0.10-0.12.
Practice Online
For more Practice: Solve these Related Problems:
- Write a SQL query to exclude orders with an order date of '2012-10-05' and a purchase amount greater than 1000.
- Write a SQL query to find orders excluding those with a purchase amount exceeding 2000 and a salesman_id greater than 5003.
- Write a SQL query to retrieve orders excluding those placed on '2012-09-10' with a customer_id less than 3005.
- Write a SQL query to display orders excluding those with a purchase amount greater than 5000 and a salesman_id of 5001.
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.