SQL Strategy - A site to master SQL while executing it on the Web

Home >> SQL Strategy - Basics of SELECT statement (2)

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

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

SELECT | ENAME,JOB,SAL |

FROM | EMP |

WHERE | SAL >= 2000 |

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. |

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

--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 |

Use logical operators to select records by compound conditions.

AND | and |

OR | or |

NOT | not |

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'.

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.

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

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.

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)].

SQL Strategy Menu

1 Introduction

2 SQL Basics

3 Basics of SELECT statement (1)

4 Basics of SELECT statement (2)

5 Sort (ORDER BY)

6 Functions (SUM, MAX, MIN, AVG, COUNT)

7 Extract by range (BETWEEN)

8 Pattern search (LIKE)

9 Grouping (GROUP BY)

10 Selection criteria for grouped tables (HAVING)

11 Duplicate row elimination (DISTINCT)

12 INSERT statement

13 UPDATE statement

14 DELETE statement

15 Inquiry to multiple tables

16 Nested Inquiry