Selecting Rows in SQL
Operators
An operator is a symbol that computes a value from one or more other values, called operands.
- arithmetic operators compute numeric values from numeric operands
- comparison operators computer logical values
TRUEorFALSE- may be numeric, character, or other data types
- logical operators compute logical values from logical operands
- A unary operator has one operand
- A binary operator has two operands
- most operands are binary
- logical operator is not unary
- arithmetic operator can be unary or binary
Common Operators
Type Operator Description Example Value Arithmetic +Adds two numeric values 4 + 37Arithmetic -(unary)Reverses the sign of one numeric value -(-2)2Arithmetic -(binary)Subtracts one numeric value from another 11 - 56Arithmetic *Multiplies two numeric values 3 * 515Arithmetic /Divides one numeric value by another 4 / 22Arithmetic %(modulo)Divides one numeric value by another and returns
the integer remainder5 % 21Comparison =Compares two values for equality 1 = 2FALSEComparison !=<>Compares two values for inequality 1 != 21 <> 2TRUEComparison <Compares two values with < 2 < 2FALSEComparison <=Compares two values with ≤ 2 <= 2TRUEComparison >Compares two values with > '2019-08-13' > '2021-08-13'FALSEComparison >=Compares two values with ≥ 'apple' >= 'banana'FALSELogical ANDReturns TRUE only when both values are TRUE TRUE AND FALSEFALSELogical ORReturns FALSE only when both values are FALSE TRUE OR FALSETRUELogical NOTReverses a logical value NOT FALSETRUE
Expressions
An expression is a string of operators, operands, and parentheses that evaluates to a single value.
- operands can be column names or fixed values
- value can be any data type
- e.g.,
Salary > 34000 AND Department = 'Marketing' - operators are evaluated in order of operator precedence
- expressions may evaluate to
NULL
Operator Precedence
Precedence Operators 1 -(unary)2 ^3 */%4 +-(binary)5 =!=<><=>=6 NOT7 AND8 OR
SELECT Statement
The SELECT statement selects rows from a table.
- has a select clause and a from clause
FROMclause specifies the table from which rows are selectedSELECTclause specifies expressions separated by commas
- returns a set of rows, called the results table
Syntax
-- SELECT with expressions
SELECT Expression1, Expression2, …
FROM TableName;
— SELECT with column names
SELECT Column1, Column2, …
FROM TableName;— SELECT with asterisk
SELECT *
FROM TableName;
LIMIT Clause
MySQL has a LIMIT clause that limits the number of rows returned by a SELECT statement.
Example
SELECT * FROM City LIMIT 100;
WHERE Clause
A condition is an expression that evaluates to a logical value.
- A
SELECTstatement has an optionalWHEREclause that specifies a condition for selecting rows- follows the
FROMclause
- follows the
- can use
%as a wildcard
Syntax
SELECT Expression1, Expression2, ... FROM TableName WHERE Condition;