WHERE Syntax Flashcards
What is the WHERE statement syntax?
Column, Operator, and Value
When do you need to add quotes around a value in a WHERE statement?
For date values like ‘12/08/1991’ and Text Values like ‘Smith’
When do you not need to add quote values for a WHERE statement?
For number values like the number 72
How would you write a WHERE statement where you compare columns against each other?
WHERE ColumnName1 operator ColumnName2
List the WHERE operators?
= (equal), <> and != (not equal) > (greater than) < (less than) >= (greater than or equal to) <= (less than or equal)
What are two ways you can use parenthesis in a WHERE clause?
To prioritize order of search
EX: WHERE Country = ‘Brazil’
OR (Country = ‘USA’ AND State = ‘CA’)
And to group together search expressions
AB.Title=T.Name AND G.Name IN (‘Rock’,’Latin’) AND AT.NAME NOT LIKE ‘[G-Z]%’)
OR
(AT.Name. = ‘Iron Maiden’ AND G.Name = ‘Blues’)
What does NULL represent?
NULL represents a missing or unknown value in the WHERE statement
Why can’t use use operators to for the NULL value?
Operators do not work with NULL values because NULL signifies an unknown value, and any comparison with an unknown value is also unknown.
Is this statement valid, and why?
SELECT * FROM Employees WHERE Department = NULL
That statement was not valid because it used an operator.
Here’s an example of a valid statement:
SELECT * FROM Employees WHERE Department IS NULL;
What keyword would you use to search for values within a range?
The BETWEEN keyword
What datatypes can you use with the BETWEEN value AND value keyword?
Date/Time, Alphanumeric, and Numeric
What WHERE keyword would you use if you needed to filter several values?
Use the IN keyword.
What’s the usage for the LIKE keyword?
The LIKE keyword in the WHERE clause is used to search for a specified pattern in a column. It is typically used with string data types to find rows where the column values match a particular pattern.
How would you write the WHERE statement to find all Gmail emails in a list?
WHERE Email LIKE ‘%gmail%’
Why do wildcard expressions use apostrophes?
Wildcard patterns are enclosed in single quotes to indicate that they are string literals to be used with the LIKE keyword. For example ‘A’
Write the syntax for an IN statement if you were filtering your Country column to only results from the United States, Brazil, and Canada
WHERE Country IN (‘Brazil’,’United States’,’Sweden’)
Why do we surround this information with apostrophes? When do we not surround information with apostrophes?
In SQL, when you’re specifying a list of values for the IN operator, each individual value needs to be enclosed in single quotes if it’s a string or a character data type. This is because SQL treats anything within single quotes as a string literal.
What are some examples of non string data types?
Integer (INT)
Decimal (DECIMAL or NUMERIC)
Floating-point (FLOAT)
Date (DATE)
Time (TIME)
Datetime (DATETIME)
Timestamp (TIMESTAMP)
Binary (BINARY)
Varbinary (VARBINARY)
Can you use a subquery as a value in an IN statement?
Yes! As long as it’s only evaluating only one column.
SELECT Customer ID, InvoiceID
FROM Invoice (table)
WHERE CustomerID IN (
SELECT CustomerID
FROM Customer
WHERE Country = ‘India’)
What does the EXIST keyword do?
The EXIST keyword checks a subquery whether a record exists. It tells if you if a match is found or not. If a match is found it says TRUE. If it’s not found, it says FALSE.
What’s considered best practice in a SELECT clause of a subquery?
It’s best practice to use SELECT *. because it simplifies the syntax and conveys the intention clearly: you’re interested only in whether rows exist, not in the values of specific columns.
What is a correlated subquery
A correlated subquery is where the inner query references columns from the outer query. In other words, the inner query depends on the outer query for its execution.
What does the NOT keyword do?
Returns everything else besides what you listed out. What you list is negated.
