w3resource
SQL exercises

SQL: Using NOT, AND operators with specified condition

SQL Boolean Operator Statement : Exercise-8 with Solution.

Write a SQL query to display all orders where purchase amount less than 200 or exclude those orders which order date is on or greater than 10th Feb,2012 and customer id is below 3009.

Sample table : orders


Sample Solution :

SELECT * 
FROM  orders 
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

Explanation :

Syntax of display all orders where purchase amount less than a specified amount or reverse orders in a specified date and customer ID less than a specified number

Pictorial presentation :

Result of display all orders where purchase amount less than a specified amount or reverse orders in a specified date and customer ID less than a specified number

Query Visualization:

Duration:

Query visualization of Using NOT, AND operators with specified condition- Duration

Rows:

Query visualization of Using NOT, AND operators with specified condition - Rows

Cost:

Query visualization of Using NOT, AND operators with specified condition - Cost

 

Practice Online


Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: Write a SQL statement to display salesman_id, name, city and commission who gets the commission within the range more than 0.10% and less than 0.12%.
Next: Write a SQL statement where i) order dates are anything but 2012-08-17, or customer id is not greater than 3005 ii) and purchase amount is not below 1000.

What is the difficulty level of this exercise?



New Content: Composer: Dependency manager for PHP, R Programming