Extract by range (BETWEEN) - SQL Strategy - A site to master SQL while executing it on the Web

Students will learn how to extract a range using the BETWEEN operator.

1. how to use BETWEEN

--Search for employees with a SAL (salary) greater than 1000 and less than 2000.

SELECT	ENAME
FROM	EMP
WHERE	SAL BETWEEN 1000 AND 2000

■How to use the BETWEEN operator

The BETWEEN operator is used in the WHERE clause.[Column name BETWEEN lower limit AND upper limit] and returns true if the column value is greater than or equal to the lower limit and less than or equal to the upper limit.The lower and upper limits cannot be written in reverse. The above SQL can be rewritten with comparison operators as follows

SELECT	ENAME
FROM	EMP
WHERE	1000 <= SAL AND SAL <= 2000

■Negation of the BETWEEN operator

To negate the BETWEEN operator, put NOT in front of the BETWEEN operator as in [Column name NOT BETWEEN Lower limit value AND Upper limit value Upper limit value].Returns true if the column value is below the lower limit and above the upper limit.

■Practice

Now for the Practice, I will give you two questions.

Practice (1/2) Questions

Please show the ENAME (employee name) and SAL (salary) from the EMP Table with a SAL greater than or equal to 1000 and less than or equal to 2000.

Practice (1/2) Answers

SELECT	ENAME,SAL
FROM	EMP
WHERE	SAL BETWEEN 1000 AND 2000

If BETWEEN is not used, then 1000 <= SAL AND SAL <= 2000.

Practice (2/2) Questions

Please show the ENAME (employee name) and SAL from the EMP Table if the SAL is less than 1000 or greater than 2000.

Practice (2/2) Answers

SELECT	ENAME,SAL
FROM	EMP
WHERE	SAL NOT BETWEEN 1000 AND 2000

Note the position of the NOT.

2.past question of Extract by range (BETWEEN)

Beginner System Administrator AM Q28, Fall 2001

Question
Answer and Explanation

There is a "BUSHO" Table and a "SHAIN" Table. The "SHAIN" Table is defined by the following SQL statement
CREATE TABLE SHAIN
　(S_CODE CHAR(3) PRIMARYKEY,
　S_NAMEN CHAR(3),
　BU_CODE CHAR(3),
　S_AGE DECIMAL(2),
　FOREIGN KEY(BU_CODE) REFERENCE SBUSHO,
　CHECK(S_AGE BETWEEN 18 AND 60))

Also The "BUSHO" and "SHAIN" tables currently contain the following data.
BUSHO

BU_CODE	BU_NAME
B01	HR department
B02	general affairs department (bureau)
B03	accounting department

SHAIN

S_CODE	S_NAME	BU_CODE	S_AGE
111	Yamada	B02	60
122	Kawakami	B03	55
233	Tanaka	B01	35
259	Okamoto	B02	34

In this case, which of the following tuples can be added to the "SHAIN" Table?

	S_CODE	S_NAME	BU_CODE	S_AGE
A	012	Yamada	B03	60
B	111	Yamada	B02	55
C	320	Yamamoto	B04	34
D	920	Yamashita	B03	17

This is a hint because it is slightly more difficult and does not explanation about the CREATE statement.

・The CREATE statement is SQL that creates a table.
・PRIMARYKEY is not allowed to have duplicate values in the primary key.
・REFERENCE is a value defined as a primary key in an external, and values not in the external table are not allowed.
・Data cannot be stored in the Table unless the conditions in CHECK are met.

Answer

A

Explanation

Since PRIMARYKEY is specified in S_CODE, B is a duplicate key.

C cannot be added because B04 does not exist for BU_CODE, which is specified as a foreign key in REFERENCE.

Of the rest, only A will meet the condition S_AGE BETWEEN 18 AND 60, so the answer is A.

This completes [Extract by range (BETWEEN)].