WHERE Syntax

Question 1

What is the WHERE statement syntax?

Answer 1

Column, Operator, and Value

Question 2

When do you need to add quotes around a value in a WHERE statement?

Answer 2

For date values like ‘12/08/1991’ and Text Values like ‘Smith’

Question 3

When do you not need to add quote values for a WHERE statement?

Answer 3

For number values like the number 72

Question 4

How would you write a WHERE statement where you compare columns against each other?

Answer 4

WHERE ColumnName1 operator ColumnName2

Question 5

List the WHERE operators?

Answer 5

= (equal), <> and != (not equal) > (greater than) < (less than) >= (greater than or equal to) <= (less than or equal)

Question 6

What are two ways you can use parenthesis in a WHERE clause?

Answer 6

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

Question 7

What does NULL represent?

Answer 7

NULL represents a missing or unknown value in the WHERE statement

Question 8

Why can’t use use operators to for the NULL value?

Answer 8

Operators do not work with NULL values because NULL signifies an unknown value, and any comparison with an unknown value is also unknown.

Question 9

Is this statement valid, and why?

SELECT * FROM Employees WHERE Department = NULL

Answer 9

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;

Question 10

What keyword would you use to search for values within a range?

Answer 10

The BETWEEN keyword

Question 11

What datatypes can you use with the BETWEEN value AND value keyword?

Answer 11

Date/Time, Alphanumeric, and Numeric

Question 12

What WHERE keyword would you use if you needed to filter several values?

Answer 12

Use the IN keyword.

Question 13

What’s the usage for the LIKE keyword?

Answer 13

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.

Question 14

How would you write the WHERE statement to find all Gmail emails in a list?

Answer 14

WHERE Email LIKE ‘%gmail%’

Question 15

Why do wildcard expressions use apostrophes?

Answer 15

Wildcard patterns are enclosed in single quotes to indicate that they are string literals to be used with the LIKE keyword. For example ‘A’

Question 16

Write the syntax for an IN statement if you were filtering your Country column to only results from the United States, Brazil, and Canada

Answer 16

WHERE Country IN (‘Brazil’,’United States’,’Sweden’)

Question 17

Why do we surround this information with apostrophes? When do we not surround information with apostrophes?

Answer 17

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.

Question 18

What are some examples of non string data types?

Answer 18

Integer (INT)
Decimal (DECIMAL or NUMERIC)
Floating-point (FLOAT)
Date (DATE)
Time (TIME)
Datetime (DATETIME)
Timestamp (TIMESTAMP)
Binary (BINARY)
Varbinary (VARBINARY)

Question 19

Can you use a subquery as a value in an IN statement?

Answer 19

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

Question 20

What does the EXIST keyword do?

Answer 20

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.

Question 21

What’s considered best practice in a SELECT clause of a subquery?

Answer 21

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.

Question 22

What is a correlated subquery

Answer 22

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.

Question 23

What does the NOT keyword do?

Answer 23

Returns everything else besides what you listed out. What you list is negated.