Basics of SELECT statement (2) - SQL Strategy - A site to master SQL while executing it on the Web

You will learn how to use the WHERE clause to select records that match the criteria.

1. how to select records using the WEHRE clause

--Select records with SAL (salary) greater than 2000

SELECT	ENAME,JOB,SAL
FROM	EMP
WHERE	SAL >= 2000

■Types of Comparison Operators

By placing a conditional expression after the WHERE clause, only records that match the condition can be selected. the comparison operators that can be used for the WHERE condition are

=	The left-hand side is equal to the right-hand side
<	left-hand side is smaller than right-hand side
<=	the left side is less than the right side
>	left-hand side is greater than right-hand side
>=	the left side is greater than the right side
<>	The left and right sides are not equal.

■Practice

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

Practice (1/2) Questions

Select ENAME (employee name) with DEPTNO (department number) of 20 from the EMP table.

Practice (1/2) Answers

SELECT ENAME FROM EMP WHERE DEPTNO = 20

Write the condition after the WHERE.

Practice (2/2) Questions

Select an ENAME (employee name) from the EMP table whose DEPTNO (department number) is other than 20.

Practice (2/2) Answers

SELECT ENAME FROM EMP WHERE DEPTNO <> 20

Not equal was <>, wasn't it?

2. record selection by composite criteria

--Select employees whose JOB is 'CLERK' and whose SAL is 1000 or more

SELECT	ENAME,JOB,SAL
FROM	EMP
WHERE	JOB = 'CLERK' AND SAL >= 1000

■Types of logical operators

Use logical operators to select records by compound conditions.

AND	and
OR	or
NOT	not

■How to use logical operators

If JOB = 'CLERK' AND SAL >= 1000, then the records that satisfy the conditions JOB = 'CLERK' and SAL >= 1000 are selected. To negate a conditional expression, put NOT in front of the expression, for example, NOT JOB = 'CLERK'.

■priority

Logical operators have precedence, and operations are performed in the order of NOT, AND, and OR. (parentheses) can be used to prioritize operations.

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

Practice (1/2) Questions

From the EMP table, select ENAME (employee name),JOB where JOB is 'CLERK' or 'SALESMAN'.

Practice (1/2) Answers

SELECT ENAME,JOB FROM EMP WHERE JOB = 'CLERK' OR JOB = 'SALESMAN'

You can choose A or B in the OR.

Practice (2/2) Questions

From the EMP table, select the ENAME (employee name), JOB, and SAL for which the JOB is 'SALESMAN' or 'CLERK' and the SAL (salary) is 1000 or more.

*AND has higher precedence than OR. Use parentheses well.

Practice (2/2) Answers

SELECT ENAME,JOB,SAL FROM EMP
WHERE (JOB = 'CLERK' OR JOB = 'SALESMAN') AND SAL >= 1000

Note the precedence of the operators.

3. past question with SELECT statements

Beginner System Administrator Morning Q35, 1995

Question
Answer and Explanation

The following table "Grades" contains the results of the English, Japanese, and mathematics examinations. Which of the following SQL statements is appropriate for the name of the student whose average score in the three subjects is 65 points or higher? Which one is appropriate?

Table grades

No	Name	English	Japanese	Math
3011	Sato	56	70	60
2023	Suzuki	70	65	80
3047	Tanaka	80	70	50
3066	Nakamura	70	80	75

A	SELECT No FROM grades WHERE English > 65 OR Japanese > 65 OR Math > 65
B	SELECT No FROM grades WHERE English >= 65 AND Japanese >= 65 AND Math >= 65
C	SELECT Name FROM grades WHERE English >= 65 OR Japanese >= 65 OR Math >= 65
D	SELECT Name FROM grades WHERE English ＋ Japanese ＋ Math > 195
E	SELECT Name FROM grades WHERE English ＋ Japanese ＋ Math >= 195

Answer

E

Explanation

This problem should be solved as follows.

1. check the items to be displayed after SELECT.
Here we want "Student's Name", so A and B are No, so we remove them from the candidate list.。

2. Check if the WHERE condition is correct.
C is not good because it is selected if any of the scores are 65 or higher, even if the average score is not 65 or higher.

In the case of D, the student is selected if WHERE English + Japanese + Math > 195 and the average score is greater than 65.

I see that E is selected for students with WHERE English + Japanese + Math >= 195 and an average score of 65 or higher. Therefore, E is the correct answer.

This concludes [Basics of SELECT statement (2)].