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
Pictorial presentation :
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?