2-2-2 Reference With Specifying Conditions

Highlighted text? Practice this concept. Highlighted passages and figures open related questions.

Practice related questions (1)Progress & bookmarks

Current section

2-2-2 Reference With Specifying Conditions

p. 268

Practice this section 1 linked exam question with default filters

Progress

Not marked complete

2-2-2 Reference With Specifying Conditions

Reference with specifying conditions corresponds to the relational operation “Selection” that extracts the tuples that satisfy the specified conditions. Use the following SELECT statement in reference with specifying conditions.

Original textbook table, example or syntax box, with its complete original layout. Source text: SELECT Column name 1, Column name 2, ∙∙∙ FROM Table name WHERE Extraction conditions Notes: 1. Tuples that satisfy the conditions that are described in the WHERE clause are extracted. 2. The following are the main operators and notations used in extraction conditions. <Comparison operators> Notation Example of use Meaning A = B A is equal to B. = A <> B A is not equal to B. (A ≠ B) <> A < B A is smaller than B. < A <= B A is equal to or smaller than B. (A ≤ B) <= > A > B A is greater than B. A >= B A is equal to or greater than B. (A ≥ B) >= <Logical operators> Notation Meaning True if both the conditions are satisfied AND True if either of the conditions is satisfied OR False if the original condition is true, and true if it NOT is false (negation) <Others (specifying special conditions)> Notation Meaning BETWEEN A AND B Specifies the range of values (A or more, and B or less) Specifies the pattern of string (wild card specification) LIKE … (%: Characters above 0 characters, _: 1 character) Judgment of NULL IS NULL — p. 268 · Enlarge

SQL LIKE wildcards · 1 practice questions

p. 269

ex09: From the “Student” table, extract tuples where Gender is 'Female' (Selection).

Original SQL worked example with its complete source code, indentation, punctuation, source frame and any attached original notes. Source text: SELECT * FROM Student WHERE Gender='Female' — p. 269 · Enlarge

ex10: From the “Subject” table, extract subject names other than the SubjectName 'Mathematics' (Selection, Projection).

Original SQL execution-result assembly, including the original table labels, rows, columns and associated notes. Source text: SELECT SubjectName FROM Subject WHERE SubjectName<>'Mathematics' <Execution results of ex09> <Execution results of ex10> StudentNumber Name Gender SubjectName 6816 Mone Yamada Female English I 6817 Chiyo Yamamoto Female English II — p. 269 · Enlarge

ex11: From the “Score” table, extract StudentNumber, SubjectNumber, and Score where SubjectNumber contains '2' and Score is between 80 and 90.

ex12: From the “Score” table, extract StudentNumber, SubjectNumber, and Score where SubjectNumber contains '2' or Score is between 80 and 90.

Original ex12 OR query plus side-by-side results for ex11 AND and ex12 OR. Ex11 has two rows: 6724/K21/85 and 6817/K12/90. Ex12 has five rows. The original WHERE, LIKE and BETWEEN notation, captions and all result cells are preserved. Source text: SELECT StudentNumber, SubjectNumber, Score FROM Score WHERE (SubjectNumber LIKE '%2%') OR (Score BETWEEN 80 AND 90) <Execution results of ex11> <Execution results of ex12> Student Subject Student Subject Score Score Number Number Number Number 6724 K21 85 6724 K21 85 6817 K12 90 6725 K21 60 6817 K11 85 6817 K12 90 6817 K21 95 — p. 269 · Enlarge

ex13: From the “Score” table, among the tuples where the score is greater than 60, extract the tuples where the 2nd digit as counted from the left edge of StudentNumber is '7' or ExaminationDate is '20XX-10-21'.

p. 270