Please note, this is a STATIC archive of website www.w3resource.com from 19 Jul 2022, cach3.com does not collect or store any user information, there is no "phishing" involved.
w3resource

SQL: Using AND, OR operator with a specified condition

SQL Boolean Operator Statement: Exercise-6 with Solution.

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


Sample Solution :

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

Relational Algebra Expression:

Relational Algebra Expression: Using AND, OR operator with a specified condition.

Relational Algebra Tree:

Relational Algebra Tree: Using AND, OR operator with a specified condition.

Explanation :

Syntax of display either those orders which is 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

Pictorial presentation :

Result of display either those orders which is 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

Practice Online


Query Visualization:

Duration:

Query visualization of Using AND, OR operator with a specified condition - Duration

Rows:

Query visualization of Using AND, OR operator with a specified condition - Rows

Cost:

Query visualization of Using AND, OR operator with a specified condition - Cost

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

Previous: From the following table, write a SQL query to find those customers who belong to neither the ‘New York’ city nor their grade value exceeds 100. Return customer_id, cust_name, city, grade, and salesman_id.
Next: From the following table, write a SQL query to find the details of those salespeople whose commissions range from 0.10 to0.12. Return salesman_id, name, city, and commission.

Test your Programming skills with w3resource's quiz.

What is the difficulty level of this exercise?



SQL: Tips of the Day

SQL Server SELECT into existing table. 

INSERT INTO dbo.TABLETWO
SELECT col1, col2
  FROM dbo.TABLEONE
 WHERE col3 LIKE @search_key

This assumes there's only two columns in dbo.TABLETWO - you need to specify the columns otherwise:

INSERT INTO dbo.TABLETWO
  (col1, col2)
SELECT col1, col2
  FROM dbo.TABLEONE
 WHERE col3 LIKE @search_key

Database: SQL Server

Ref: https://bit.ly/3y6tpA3