Filtering, Sorting, and Calculating Data with SQL

Question 1

Why filter?

Answer 1

1. Reduce the number of records you retrieve
2. Increase query performance
3. Reduce the strain on the client application

SQL for Data Science Week 2

Question 2

WHERE clause syntax

Answer 2

SELECT column_name, column_name
FROM table_name
WHERE column_name operator value

SQL for Data Science Week 2

Question 3

WHERE clause operators

Answer 3

1. =
2. <>
3. >
4. <
5. > =
6. <=
7. BETWEEN
8. IS NULL

SQL for Data Science Week 2

Question 4

Does the column used in the WHERE clause need to be in the SELECT clause?

N

Answer 4

No

SQL for Data Science Week 2

Question 5

How do you find nulls?

Answer 5

WHERE ProductName IS NULL

SQL for Data Science Week 2

Question 6

Syntax for IN

Answer 6

WHERE SupplierID IN (1, 6, 7, 9)

Not that BETWEEN wouldn’t have been helpful here becuase we want specific number, not a range of numbers.

SQL for Data Science Week 2

Question 7

Benefit of using IN over OR

Answer 7

IN
- You don’t have to think about the order
- You can use a subquery to return the values you want included

OR
- You have to think about the order because OR stops executing once the first condition is met.

SQL for Data Science Week 2

Question 7

Which executes faster, IN or OR?

Answer 7

IN

SQL for Data Science Week 2

Question 8

What do you need to watch out for when using OR and AND?

Answer 8

Answer: Operator order of precedence

--Example
SELECT 
ProductID, 
SupplierID 
FROM Products 
WHERE Supplier1D = 9 OR SupplierID = 11 
AND UnitPrice > 15

Objective
Get records for supplier 9 and supplier 11 where the UnitPrice>15

Problem
AND is processed before OR

Result
First records for supplier 11 with a unit price of 15 are selected. Then records for supplier 9 with any unit price are selected

Solution
Put parenthesis around (Supplier1D = 9 OR SupplierID = 11). Expressions inside parenthesis are evaluated first.

SQL for Data Science Week 2

Question 9

Operator order of precedence in SQLite

Answer 9

1. () Expressions inside parentheses are evaluated first
2. NOT
3. *, /, %
4. +, –
5. <, <=, >, >=
6. =, !=, IS, LIKE, GLOB, REGEXP
7. AND
8. OR

SQL for Data Science Week 2

Question 10

How do you use the WHERE clause to exclude values you don’t want?

Answer 10

• It appears that <> may not be used in SQLite, need to research more
• Use WHERE NOT City=’Seattle’
• Instead of City<>’Seattle’

SQL for Data Science Week 2

Question 11

What data type can wildcards be used on?

Answer 11

Strings only
They cannot be used on non-text datatypes

Just writing a bunch of text to get the card to left justify

SQL for Data Science Week 2

Question 12

What special character do you use for wildcards when using LIKE?

Answer 12

Examples
- ‘%Pizza’
- ‘Pizza%’
- ‘%Pizza%’
- ‘S%E’ (would pick up the name Sadie)
- ‘t%@gmail.com (gmail addresses that start with t, instead of also looking in hotmail or outlook emails)

SQL for Data Science Week 2

Question 13

How does a wildcard treat a null value when using LIKE?

Answer 13

It will not match with null values since NULL has no value

SQL for Data Science Week 2

Question 14

What special character matches just a single character when using LIKE?

Answer 14

The underscore _

SQL for Data Science Week 2

Question 15

What special characters specify a set of characters in a specific location when using LIKE?

Answer 15

Brackets []

This does not work on all database systems

SQL for Data Science Week 2

Question 16

Downsides of using wildcards?

Answer 16

1. Takes longer to run
2. =, <, =<, etc. will be faster
3. Wildcards at the end of the string will take longer to run