Question

Write a SQL statement to display either those orders which are not issued on date 2012-09-10 and issued by the salesman whose ID is 505 and below or those orders which purchase amount is 1000.00 and below.

orders table

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

Answer 1

Database:

SELECT * FROM orders WHERE (ord_date<>'2012=09-10' and salesman_id=505) OR
purch_amt<=1000.00;

CREATE TABLE orders(ord_no integer PRIMARY KEY,purch_amt float , ord_date text,customer_id int,salesman_id int);

/* Create few records in this table */
INSERT INTO orders VALUES(70001,150.5, '2012-10-05', 3005, 5002);
INSERT INTO orders VALUES (70009, 270.65 , '2012-09-10', 3001, 5005);

INSERT INTO orders VALUES (70002 , 65.26 ,'2012-10-05', 3002, 5001);

INSERT INTO orders VALUES (70004, 110.5 ,'2012-08-17', 3009, 5003);

INSERT INTO orders VALUES (70007 , 948.5 ,'2012-09-10', 3005 , 5002);
INSERT INTO orders VALUES (70005 , 2400.6 ,'2012-07-27', 3007 , 5001);
INSERT INTO orders VALUES (70008 , 5760 ,'2012-09-10', 3002, 5001);
INSERT INTO orders VALUES (70010 , 1983.43 ,'2012-10-10' ,3004 , 5006);
INSERT INTO orders VALUES (70003 , 2480.4 , '2012-10-10', 3009 , 5003);
INSERT INTO orders VALUES (70012 , 250.45 ,'2012-06-27' ,3008 , 5002);
INSERT INTO orders VALUES (70011 ,75.29, '2012-08-17', 3003 , 5007);
INSERT INTO orders VALUES (70013, 3045.6 ,'2012-04-25', 3002 , 5001);

SELECT * FROM orders WHERE (ord_date<>'2012=09-10' and salesman_id=505) OR
purch_amt<=1000.00;