SQL BETWEEN Operator
BETWEEN Operator
The SQL BETWEEN operator tests an expression against a range. The range consists of a beginning, followed by an AND keyword and an end expression. The operator returns TRUE when the search value present within the range otherwise returns FALSE. The results are NULL if any of the range values are NULL.
Syntax:
SELECT [column_name... | expression1 ] FROM [table_name] WHERE expression2 [NOT] BETWEEN value_from AND value_to;
Parameters:
| Name | Description |
|---|---|
| column_name | Name of the column of the table. |
| expression1 | Expression made up of a single constant, variable, scalar function, or column name and can also be the pieces of a SQL query that compare values against other values or perform arithmetic calculations. |
| table_name | Name of the table. |
| WHERE expression2 | Compares a scalar expression, such as a column, to the range of values bounded by value_from and value_to |
| value_from, value_to | Starting value and ending value. |
DBMS Support: BETWEEN Operator
| DBMS | Command |
| MySQL | Supported |
| PostgreSQL | Supported |
| SQL Server | Supported |
| Oracle | Supported |
Example: SQL BETWEEN Operator
To know whether the search value 15 is present within the specified range from the DUAL table, the following SQL statement can be used:
SQL Code:
SELECT 'found'
-- Selecting the string 'found'
FROM dual
-- From the virtual table named "dual"
WHERE 10 BETWEEN 5 AND 20;
-- Where the value 10 is between 5 and 20
Explanation:
- SELECT 'found': This specifies that we want to select the string 'found' as a result.
- FROM dual: This indicates that we are selecting from the virtual table dual, which is a special one-row table present in some database systems like Oracle.
- WHERE 10 BETWEEN 5 AND 20: This is a conditional clause that filters the rows returned by the query. It specifies that the value 10 is between 5 and 20.
Output:
'FOUND' -------- found
Relational Algebra Expression:
Relational Algebra Tree:
Visual Presentation : SQL BETWEEN operator
Here we look for all agents in the agents table of inventory database whose commission should be within .12 to .14.
Sample table: agents+------------+----------------------+--------------------+------------+-----------------+---------+ | AGENT_CODE | AGENT_NAME | WORKING_AREA | COMMISSION | PHONE_NO | COUNTRY | +------------+----------------------+--------------------+------------+-----------------+---------+ | A007 | Ramasundar | Bangalore | 0.15 | 077-25814763 | | | A003 | Alex | London | 0.13 | 075-12458969 | | | A008 | Alford | New York | 0.12 | 044-25874365 | | | A011 | Ravi Kumar | Bangalore | 0.15 | 077-45625874 | | | A010 | Santakumar | Chennai | 0.14 | 007-22388644 | | | A012 | Lucida | San Jose | 0.12 | 044-52981425 | | | A005 | Anderson | Brisban | 0.13 | 045-21447739 | | | A001 | Subbarao | Bangalore | 0.14 | 077-12346674 | | | A002 | Mukesh | Mumbai | 0.11 | 029-12358964 | | | A006 | McDen | London | 0.15 | 078-22255588 | | | A004 | Ivan | Torento | 0.15 | 008-22544166 | | | A009 | Benjamin | Hampshair | 0.11 | 008-22536178 | | +------------+----------------------+--------------------+------------+-----------------+---------+
SQL Code:
SELECT *
-- Selecting all columns (*) from the table
FROM agents
-- From the table named "agents"
WHERE commission BETWEEN .12 AND .14;
-- Where the value in the column "commission" is between .12 and .14
Explanation:
- SELECT *: This specifies that we want to retrieve all columns from the table.
- FROM agents: This indicates the table from which we want to retrieve the data. In this case, the table is named "agents".
- WHERE commission BETWEEN .12 AND .14: This is a conditional clause that filters the rows returned by the query. It specifies that only rows where the value in the "commission" column is between .12 and .14 should be included in the result set.
Output:
AGENT_CODE AGENT_NAME WORKING_AREA COMMISSION PHONE_NO COUNTRY ---------- -------------------- -------------------- ---------- --------------- ---------- A003 Alex London .13 075-12458969 A001 Subbarao Bangalore .14 077-12346674 A008 Alford New York .12 044-25874365 A010 Santakumar Chennai .14 007-22388644 A012 Lucida San Jose .12 044-52981425 A005 Anderson Brisban .13 045-21447739
Visual Presentation: SQL BETWEEN operator
Example: SQL Between operator with IN
Sample table: customer+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ |CUST_CODE | CUST_NAME | CUST_CITY | WORKING_AREA | CUST_COUNTRY | GRADE | OPENING_AMT | RECEIVE_AMT | PAYMENT_AMT |OUTSTANDING_AMT| PHONE_NO | AGENT_CODE | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ | C00013 | Holmes | London | London | UK | 2 | 6000.00 | 5000.00 | 7000.00 | 4000.00 | BBBBBBB | A003 | | C00001 | Micheal | New York | New York | USA | 2 | 3000.00 | 5000.00 | 2000.00 | 6000.00 | CCCCCCC | A008 | | C00020 | Albert | New York | New York | USA | 3 | 5000.00 | 7000.00 | 6000.00 | 6000.00 | BBBBSBB | A008 | | C00025 | Ravindran | Bangalore | Bangalore | India | 2 | 5000.00 | 7000.00 | 4000.00 | 8000.00 | AVAVAVA | A011 | | C00024 | Cook | London | London | UK | 2 | 4000.00 | 9000.00 | 7000.00 | 6000.00 | FSDDSDF | A006 | | C00015 | Stuart | London | London | UK | 1 | 6000.00 | 8000.00 | 3000.00 | 11000.00 | GFSGERS | A003 | | C00002 | Bolt | New York | New York | USA | 3 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | DDNRDRH | A008 | | C00018 | Fleming | Brisban | Brisban | Australia | 2 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | NHBGVFC | A005 | | C00021 | Jacks | Brisban | Brisban | Australia | 1 | 7000.00 | 7000.00 | 7000.00 | 7000.00 | WERTGDF | A005 | | C00019 | Yearannaidu | Chennai | Chennai | India | 1 | 8000.00 | 7000.00 | 7000.00 | 8000.00 | ZZZZBFV | A010 | | C00005 | Sasikant | Mumbai | Mumbai | India | 1 | 7000.00 | 11000.00 | 7000.00 | 11000.00 | 147-25896312 | A002 | | C00007 | Ramanathan | Chennai | Chennai | India | 1 | 7000.00 | 11000.00 | 9000.00 | 9000.00 | GHRDWSD | A010 | | C00022 | Avinash | Mumbai | Mumbai | India | 2 | 7000.00 | 11000.00 | 9000.00 | 9000.00 | 113-12345678 | A002 | | C00004 | Winston | Brisban | Brisban | Australia | 1 | 5000.00 | 8000.00 | 7000.00 | 6000.00 | AAAAAAA | A005 | | C00023 | Karl | London | London | UK | 0 | 4000.00 | 6000.00 | 7000.00 | 3000.00 | AAAABAA | A006 | | C00006 | Shilton | Torento | Torento | Canada | 1 | 10000.00 | 7000.00 | 6000.00 | 11000.00 | DDDDDDD | A004 | | C00010 | Charles | Hampshair | Hampshair | UK | 3 | 6000.00 | 4000.00 | 5000.00 | 5000.00 | MMMMMMM | A009 | | C00017 | Srinivas | Bangalore | Bangalore | India | 2 | 8000.00 | 4000.00 | 3000.00 | 9000.00 | AAAAAAB | A007 | | C00012 | Steven | San Jose | San Jose | USA | 1 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | KRFYGJK | A012 | | C00008 | Karolina | Torento | Torento | Canada | 1 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | HJKORED | A004 | | C00003 | Martin | Torento | Torento | Canada | 2 | 8000.00 | 7000.00 | 7000.00 | 8000.00 | MJYURFD | A004 | | C00009 | Ramesh | Mumbai | Mumbai | India | 3 | 8000.00 | 7000.00 | 3000.00 | 12000.00 | Phone No | A002 | | C00014 | Rangarappa | Bangalore | Bangalore | India | 2 | 8000.00 | 11000.00 | 7000.00 | 12000.00 | AAAATGF | A001 | | C00016 | Venkatpati | Bangalore | Bangalore | India | 2 | 8000.00 | 11000.00 | 7000.00 | 12000.00 | JRTVFDD | A007 | | C00011 | Sundariya | Chennai | Chennai | India | 3 | 7000.00 | 11000.00 | 7000.00 | 11000.00 | PPHGRTS | A010 | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+
To get data of all columns from the 'customer' table with following conditions -
1. 'agent_code' must be within 'A003' and 'A008',
2. but 'agent_code' 'A004', 'A007' and 'A005' should not appear,
the following SQL statement can be used :
SQL Code:
SELECT agent_code, cust_code, cust_name, cust_city
-- Selecting specific columns: agent_code, cust_code, cust_name, and cust_city
FROM customer
-- From the table named "customer"
WHERE (agent_code BETWEEN 'A003' AND 'A008')
-- Where the value in the column "agent_code" is between 'A003' and 'A008' (inclusive)
AND NOT agent_code IN ('A004', 'A007', 'A005');
-- And the value in the column "agent_code" is not 'A004', 'A007', or 'A005'
Explanation:
- SELECT agent_code, cust_code, cust_name, cust_city: This specifies that we want to retrieve specific columns (agent_code, cust_code, cust_name, and cust_city) from the table.
- FROM customer: This indicates the table from which we want to retrieve the data. In this case, the table is named "customer".
- WHERE (agent_code BETWEEN 'A003' AND 'A008'): This is a conditional clause that filters the rows returned by the query. It specifies that only rows where the value in the "agent_code" column is between 'A003' and 'A008' (inclusive) should be included in the result set.
- AND NOT agent_code IN ('A004', 'A007', 'A005'): This further narrows down the results by excluding rows where the value in the "agent_code" column is 'A004', 'A007', or 'A005'.
Output:
AGENT_CODE CUST_CODE CUST_NAME CUST_CITY ---------- ---------- ---------------------------------------- ------------ A003 C00013 Holmes London A008 C00001 Micheal New York A008 C00020 Albert New York A006 C00024 Cook London A003 C00015 Stuart London A008 C00002 Bolt New York A006 C00023 Karl London
Example: SQL Between operator with text value
Sample table: customer+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ |CUST_CODE | CUST_NAME | CUST_CITY | WORKING_AREA | CUST_COUNTRY | GRADE | OPENING_AMT | RECEIVE_AMT | PAYMENT_AMT |OUTSTANDING_AMT| PHONE_NO | AGENT_CODE | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ | C00013 | Holmes | London | London | UK | 2 | 6000.00 | 5000.00 | 7000.00 | 4000.00 | BBBBBBB | A003 | | C00001 | Micheal | New York | New York | USA | 2 | 3000.00 | 5000.00 | 2000.00 | 6000.00 | CCCCCCC | A008 | | C00020 | Albert | New York | New York | USA | 3 | 5000.00 | 7000.00 | 6000.00 | 6000.00 | BBBBSBB | A008 | | C00025 | Ravindran | Bangalore | Bangalore | India | 2 | 5000.00 | 7000.00 | 4000.00 | 8000.00 | AVAVAVA | A011 | | C00024 | Cook | London | London | UK | 2 | 4000.00 | 9000.00 | 7000.00 | 6000.00 | FSDDSDF | A006 | | C00015 | Stuart | London | London | UK | 1 | 6000.00 | 8000.00 | 3000.00 | 11000.00 | GFSGERS | A003 | | C00002 | Bolt | New York | New York | USA | 3 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | DDNRDRH | A008 | | C00018 | Fleming | Brisban | Brisban | Australia | 2 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | NHBGVFC | A005 | | C00021 | Jacks | Brisban | Brisban | Australia | 1 | 7000.00 | 7000.00 | 7000.00 | 7000.00 | WERTGDF | A005 | | C00019 | Yearannaidu | Chennai | Chennai | India | 1 | 8000.00 | 7000.00 | 7000.00 | 8000.00 | ZZZZBFV | A010 | | C00005 | Sasikant | Mumbai | Mumbai | India | 1 | 7000.00 | 11000.00 | 7000.00 | 11000.00 | 147-25896312 | A002 | | C00007 | Ramanathan | Chennai | Chennai | India | 1 | 7000.00 | 11000.00 | 9000.00 | 9000.00 | GHRDWSD | A010 | | C00022 | Avinash | Mumbai | Mumbai | India | 2 | 7000.00 | 11000.00 | 9000.00 | 9000.00 | 113-12345678 | A002 | | C00004 | Winston | Brisban | Brisban | Australia | 1 | 5000.00 | 8000.00 | 7000.00 | 6000.00 | AAAAAAA | A005 | | C00023 | Karl | London | London | UK | 0 | 4000.00 | 6000.00 | 7000.00 | 3000.00 | AAAABAA | A006 | | C00006 | Shilton | Torento | Torento | Canada | 1 | 10000.00 | 7000.00 | 6000.00 | 11000.00 | DDDDDDD | A004 | | C00010 | Charles | Hampshair | Hampshair | UK | 3 | 6000.00 | 4000.00 | 5000.00 | 5000.00 | MMMMMMM | A009 | | C00017 | Srinivas | Bangalore | Bangalore | India | 2 | 8000.00 | 4000.00 | 3000.00 | 9000.00 | AAAAAAB | A007 | | C00012 | Steven | San Jose | San Jose | USA | 1 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | KRFYGJK | A012 | | C00008 | Karolina | Torento | Torento | Canada | 1 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | HJKORED | A004 | | C00003 | Martin | Torento | Torento | Canada | 2 | 8000.00 | 7000.00 | 7000.00 | 8000.00 | MJYURFD | A004 | | C00009 | Ramesh | Mumbai | Mumbai | India | 3 | 8000.00 | 7000.00 | 3000.00 | 12000.00 | Phone No | A002 | | C00014 | Rangarappa | Bangalore | Bangalore | India | 2 | 8000.00 | 11000.00 | 7000.00 | 12000.00 | AAAATGF | A001 | | C00016 | Venkatpati | Bangalore | Bangalore | India | 2 | 8000.00 | 11000.00 | 7000.00 | 12000.00 | JRTVFDD | A007 | | C00011 | Sundariya | Chennai | Chennai | India | 3 | 7000.00 | 11000.00 | 7000.00 | 11000.00 | PPHGRTS | A010 | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+
To get data of 'cust_code', 'cust_name', 'cust_city', 'working_area' columns from the 'customer' table with following condition -
1.'cust_name' must begin with any of the letters between 'A' and 'I',
the following SQL statement can be used :
SQL Code:
SELECT cust_code, cust_name, cust_city, working_area
-- Selecting specific columns: cust_code, cust_name, cust_city, and working_area
FROM customer
-- From the table named "customer"
WHERE cust_name BETWEEN 'A' AND 'I';
-- Where the value in the column "cust_name" is between 'A' and 'I' (inclusive)
Explanation:
- SELECT cust_code, cust_name, cust_city, working_area: This specifies that we want to retrieve specific columns (cust_code, cust_name, cust_city, and working_area) from the table.
- FROM customer: This indicates the table from which we want to retrieve the data. In this case, the table is named "customer".
- WHERE cust_name BETWEEN 'A' AND 'I': This is a conditional clause that filters the rows returned by the query. It specifies that only rows where the value in the "cust_name" column falls between 'A' and 'I' (inclusive) should be included in the result set.
Output:
CUST_CODE CUST_NAME CUST_CITY WORKING_AREA ---------- --------------------- ----------------------------------- -------------- C00013 Holmes London London C00020 Albert New York New York C00024 Cook London London C00002 Bolt New York New York C00018 Fleming Brisban Brisban C00022 Avinash Mumbai Mumbai C00010 Charles Hampshair Hampshair
Relational Algebra Expression:
Relational Algebra Tree:
Example: SQL Between operator with boolean NOT
Sample table: customer+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ |CUST_CODE | CUST_NAME | CUST_CITY | WORKING_AREA | CUST_COUNTRY | GRADE | OPENING_AMT | RECEIVE_AMT | PAYMENT_AMT |OUTSTANDING_AMT| PHONE_NO | AGENT_CODE | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ | C00013 | Holmes | London | London | UK | 2 | 6000.00 | 5000.00 | 7000.00 | 4000.00 | BBBBBBB | A003 | | C00001 | Micheal | New York | New York | USA | 2 | 3000.00 | 5000.00 | 2000.00 | 6000.00 | CCCCCCC | A008 | | C00020 | Albert | New York | New York | USA | 3 | 5000.00 | 7000.00 | 6000.00 | 6000.00 | BBBBSBB | A008 | | C00025 | Ravindran | Bangalore | Bangalore | India | 2 | 5000.00 | 7000.00 | 4000.00 | 8000.00 | AVAVAVA | A011 | | C00024 | Cook | London | London | UK | 2 | 4000.00 | 9000.00 | 7000.00 | 6000.00 | FSDDSDF | A006 | | C00015 | Stuart | London | London | UK | 1 | 6000.00 | 8000.00 | 3000.00 | 11000.00 | GFSGERS | A003 | | C00002 | Bolt | New York | New York | USA | 3 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | DDNRDRH | A008 | | C00018 | Fleming | Brisban | Brisban | Australia | 2 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | NHBGVFC | A005 | | C00021 | Jacks | Brisban | Brisban | Australia | 1 | 7000.00 | 7000.00 | 7000.00 | 7000.00 | WERTGDF | A005 | | C00019 | Yearannaidu | Chennai | Chennai | India | 1 | 8000.00 | 7000.00 | 7000.00 | 8000.00 | ZZZZBFV | A010 | | C00005 | Sasikant | Mumbai | Mumbai | India | 1 | 7000.00 | 11000.00 | 7000.00 | 11000.00 | 147-25896312 | A002 | | C00007 | Ramanathan | Chennai | Chennai | India | 1 | 7000.00 | 11000.00 | 9000.00 | 9000.00 | GHRDWSD | A010 | | C00022 | Avinash | Mumbai | Mumbai | India | 2 | 7000.00 | 11000.00 | 9000.00 | 9000.00 | 113-12345678 | A002 | | C00004 | Winston | Brisban | Brisban | Australia | 1 | 5000.00 | 8000.00 | 7000.00 | 6000.00 | AAAAAAA | A005 | | C00023 | Karl | London | London | UK | 0 | 4000.00 | 6000.00 | 7000.00 | 3000.00 | AAAABAA | A006 | | C00006 | Shilton | Torento | Torento | Canada | 1 | 10000.00 | 7000.00 | 6000.00 | 11000.00 | DDDDDDD | A004 | | C00010 | Charles | Hampshair | Hampshair | UK | 3 | 6000.00 | 4000.00 | 5000.00 | 5000.00 | MMMMMMM | A009 | | C00017 | Srinivas | Bangalore | Bangalore | India | 2 | 8000.00 | 4000.00 | 3000.00 | 9000.00 | AAAAAAB | A007 | | C00012 | Steven | San Jose | San Jose | USA | 1 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | KRFYGJK | A012 | | C00008 | Karolina | Torento | Torento | Canada | 1 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | HJKORED | A004 | | C00003 | Martin | Torento | Torento | Canada | 2 | 8000.00 | 7000.00 | 7000.00 | 8000.00 | MJYURFD | A004 | | C00009 | Ramesh | Mumbai | Mumbai | India | 3 | 8000.00 | 7000.00 | 3000.00 | 12000.00 | Phone No | A002 | | C00014 | Rangarappa | Bangalore | Bangalore | India | 2 | 8000.00 | 11000.00 | 7000.00 | 12000.00 | AAAATGF | A001 | | C00016 | Venkatpati | Bangalore | Bangalore | India | 2 | 8000.00 | 11000.00 | 7000.00 | 12000.00 | JRTVFDD | A007 | | C00011 | Sundariya | Chennai | Chennai | India | 3 | 7000.00 | 11000.00 | 7000.00 | 11000.00 | PPHGRTS | A010 | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+
To get data of 'cust_code', 'cust_name', 'cust_city', 'working_area' columns from the 'customer' table with following condition -
1. 'cust_name' must not begin with any of the letters between 'K' and 'Y' ,
the following SQL statement can be used :
SELECT cust_code, cust_name, cust_city, working_area
-- Selecting specific columns: cust_code, cust_name, cust_city, and working_area
FROM customer
-- From the table named "customer"
WHERE cust_name NOT BETWEEN 'K' AND 'Y';
-- Where the value in the column "cust_name" is not between 'K' and 'Y' (inclusive)
Explanation:
- SELECT cust_code, cust_name, cust_city, working_area: This specifies that we want to retrieve specific columns (cust_code, cust_name, cust_city, and working_area) from the table.
- FROM customer: This indicates the table from which we want to retrieve the data. In this case, the table is named "customer".
- WHERE cust_name NOT BETWEEN 'K' AND 'Y': This is a conditional clause that filters the rows returned by the query. It specifies that only rows where the value in the "cust_name" column is not between 'K' and 'Y' (inclusive) should be included in the result set.
Output
CUST_CODE CUST_NAME CUST_CITY WORKING_AREA ---------- ------------------------------ ----------------------------------- ------------- C00013 Holmes London London C00020 Albert New York New York C00024 Cook London London C00002 Bolt New York New York C00018 Fleming Brisban Brisban C00021 Jacks Brisban Brisban C00019 Yearannaidu Chennai Chennai C00022 Avinash Mumbai Mumbai C00010 Charles Hampshair Hampshair
Relational Algebra Expression:
Relational Algebra Tree:
Example:SQL Between operator with NOT on date value
Sample table: orders
ORD_NUM ORD_AMOUNT ADVANCE_AMOUNT ORD_DATE CUST_CODE AGENT_CODE ORD_DESCRIPTION
---------- ---------- -------------- --------- --------------- --------------- -----------------
200114 3500 2000 15-AUG-08 C00002 A008
200122 2500 400 16-SEP-08 C00003 A004
200118 500 100 20-JUL-08 C00023 A006
200119 4000 700 16-SEP-08 C00007 A010
200121 1500 600 23-SEP-08 C00008 A004
200130 2500 400 30-JUL-08 C00025 A011
200134 4200 1800 25-SEP-08 C00004 A005
200108 4000 600 15-FEB-08 C00008 A004
200103 1500 700 15-MAY-08 C00021 A005
200105 2500 500 18-JUL-08 C00025 A011
200109 3500 800 30-JUL-08 C00011 A010
200101 3000 1000 15-JUL-08 C00001 A008
200111 1000 300 10-JUL-08 C00020 A008
200104 1500 500 13-MAR-08 C00006 A004
200106 2500 700 20-APR-08 C00005 A002
200125 2000 600 10-OCT-08 C00018 A005
200117 800 200 20-OCT-08 C00014 A001
200123 500 100 16-SEP-08 C00022 A002
200120 500 100 20-JUL-08 C00009 A002
200116 500 100 13-JUL-08 C00010 A009
200124 500 100 20-JUN-08 C00017 A007
200126 500 100 24-JUN-08 C00022 A002
200129 2500 500 20-JUL-08 C00024 A006
200127 2500 400 20-JUL-08 C00015 A003
200128 3500 1500 20-JUL-08 C00009 A002
200135 2000 800 16-SEP-08 C00007 A010
200131 900 150 26-AUG-08 C00012 A012
200133 1200 400 29-JUN-08 C00009 A002
200100 1000 600 08-JAN-08 C00015 A003
200110 3000 500 15-APR-08 C00019 A010
200107 4500 900 30-AUG-08 C00007 A010
200112 2000 400 30-MAY-08 C00016 A007
200113 4000 600 10-JUN-08 C00022 A002
200102 2000 300 25-MAY-08 C00012 A012
To get data of 'ord_num', 'ord_amount', 'advance_amount', 'ord_date', 'cust_code' and 'agent_code' from the 'orders' table with following condition -
1.'ord_date' must be a date before '15-Feb-08' or after '30-Jul-08',
the following SQL statement can be used :
SQL Code:
SELECT ord_num, ord_amount, ord_date, cust_code, agent_code
-- Selecting specific columns: ord_num, ord_amount, ord_date, cust_code, and agent_code
FROM orders
-- From the table named "orders"
WHERE ord_date NOT BETWEEN '15-Feb-08' AND '30-Jul-08';
-- Where the value in the column "ord_date" is not between '15-Feb-08' and '30-Jul-08' (inclusive)
Explanation:
- SELECT ord_num, ord_amount, ord_date, cust_code, agent_code: This specifies that we want to retrieve specific columns (ord_num, ord_amount, ord_date, cust_code, and agent_code) from the table.
- FROM orders: This indicates the table from which we want to retrieve the data. In this case, the table is named "orders".
- WHERE ord_date NOT BETWEEN '15-Feb-08' AND '30-Jul-08': This is a conditional clause that filters the rows returned by the query. It specifies that only rows where the value in the "ord_date" column is not between '15-Feb-08' and '30-Jul-08' (inclusive) should be included in the result set.
Output:
ORD_NUM ORD_AMOUNT ORD_DATE CUST_CODE AGENT_CODE
---------- ---------- --------- ---------- ----------
200114 3500 15-AUG-08 C00002 A008
200122 2500 16-SEP-08 C00003 A004
200119 4000 16-SEP-08 C00007 A010
200121 1500 23-SEP-08 C00008 A004
200134 4200 25-SEP-08 C00004 A005
200125 2000 10-OCT-08 C00018 A005
200117 800 20-OCT-08 C00014 A001
200123 500 16-SEP-08 C00022 A002
200135 2000 16-SEP-08 C00007 A010
200131 900 26-AUG-08 C00012 A012
200100 1000 08-JAN-08 C00015 A003
200107 4500 30-AUG-08 C00007 A010
Relational Algebra Expression:
Relational Algebra Tree:
Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.