SQL Exercise: Using NOT, AND operators with specified condition
8. Orders with Amount < 200 or Exclusions
From the following table, write a SQL query to find details of all orders with a purchase amount less than 200 or exclude orders with an order date greater than or equal to '2012-02-10' and a customer ID less than 3009. 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 either the 'purch_amt' is less than 200
-- or the conditions inside the parentheses following 'NOT' are not true.
WHERE (purch_amt < 200 OR NOT (ord_date >= '2012-02-10' AND customer_id < 3009));
Output of the Query:
ord_no purch_amt ord_date customer_id salesman_id 70002 65.26 2012-10-05 3002 5001 70004 110.50 2012-08-17 3009 5003 70003 2480.40 2012-10-10 3009 5003 70011 75.29 2012-08-17 3003 5007 70001 150.50 2012-10-05 3005 5002
Code Explanation:
The SQL query that is selecting all columns (*) from the 'orders' table where the value of the "purch_amt" column is less than 200 or NOT ( the value of the "ord_date" column is greater than or equal to '2012-02-10' and the value of the "customer_id" column is less than 3009).
All rows from the 'orders' table that meet either of these criteria will be returned as a result of the query.
It will return all the rows where purchase amount is less than 200 or ord_date is less than '2012-02-10' or customer_id is greater or equal to 3009.
Relational Algebra Expression:

Relational Algebra Tree:

Explanation :

Visual presentation:

Go to:
PREV : Salespeople with Commission Range 0.10-0.12.
NEXT : Conditional Orders Exclusions.
Practice Online
For more Practice: Solve these Related Problems:
- Write a SQL query to find orders with a purchase amount less than 100 or exclude orders with an order date of '2012-07-27'.
- Write a SQL query to retrieve orders with a purchase amount below 150 or exclude orders with a customer_id greater than 3007.
- Write a SQL query to display orders with a purchase amount less than 50 or exclude orders placed on '2012-08-17'.
- Write a SQL query to list orders with a purchase amount below 250 or exclude orders with a salesman_id greater than 5005.
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.