# SQL AND and BETWEEN

## Quiz

1. Write a query that returns all the orders where the standard_qty is over 1000, the poster_qty is 0, and the gloss_qty is 0.
SELECT *
FROM orders
WHERE standard_qty > 1000 AND poster_qty = 0 AND gloss_qty = 0;
1. Using the accounts table, find all the companies whose names do not start with ‘C’ and end with ‘s’.
SELECT *
FROM accounts
WHERE name NOT LIKE 'C%' AND name LIKE '%s';

I almost forgot the "NAME" in the second condition.
1. When you use the BETWEEN operator in SQL, do the results include the values of your endpoints, or not? Figure out the answer to this important question by writing a query that displays the order date and gloss_qty data for all orders where gloss_qty is between 24 and 29. Then look at your output to see if the BETWEEN operator included the begin and end values or not.
SELECT occurred_at, gloss_qty
FROM orders
WHERE gloss_qty BETWEEN '24' AND '29';

It includes the values of the endpoints (except for dates).

1. Use the web_events table to find all information regarding individuals who were contacted via the organic or adwords channels, and started their account at any point in 2016, sorted from newest to oldest.
SELECT *
FROM web_events
WHERE channel IN('organic', 'adwords')  AND occurred_at BETWEEN '2016-01-01' AND '2017-01-01'
ORDER BY occurred_at DESC;

While BETWEEN is generally inclusive of endpoints, it assumes the time is at 00:00:00 (i.e. midnight) for dates. This is the reason why we set the right-side endpoint of the period at ‘2017-01-01’.

## SQL OR

• a logical operator in SQL that allows you to select rows that satisfy either of two conditions.
• Each time you link a new statement with an OR, you will need to specify the column you are interested in looking at.
• This operator works with all of the operations we have seen so far including arithmetic operators (+*-/), LIKEINNOTAND, and BETWEEN logic can all be linked together using the OR operator.
SELECT account_id,
occurred_at,
standard_qty,
gloss_qty,
poster_qty
FROM orders
WHERE standard_qty = 0 OR gloss_qty = 0 OR poster_qty = 0
• When combining multiple of these operations, we frequently might need to use parentheses to assure that logic we want to perform is being executed correctly.
SELECT account_id,
occurred_at,
standard_qty,
gloss_qty,
poster_qty
FROM orders
WHERE (standard_qty = 0 OR gloss_qty = 0 OR poster_qty = 0)
AND occurred_at = '2016-10-01'

## Quiz

1. Find list of orders ids where either gloss_qty or poster_qty is greater than 4000. Only include the id field in the resulting table.
SELECT id
FROM orders
WHERE gloss_qty >4000 OR poster_qty >4000;
1. Write a query that returns a list of orders where the standard_qty is zero and either the gloss_qty or poster_qty is over 1000.
SELECT *
FROM orders
WHERE (gloss_qty >1000 OR poster_qty >1000)
AND standard_qty = 0;

SELECT *
FROM orders
WHERE standard_qty = 0 AND (gloss_qty > 1000 OR poster_qty > 1000);
1. Find all the company names that start with a ‘C’ or ‘W’, and the primary contact contains ‘ana’ or ‘Ana’, but it doesn’t contain ‘eana’.
SELECT name, primary_poc
FROM accounts
WHERE (name LIKE 'C%' OR name LIKE 'W%')
AND (primary_poc LIKE '%ana%' OR primary_poc LIKE '%Ana%')
AND primary_poc NOT LIKE '%eana%';

SELECT *
FROM accounts
WHERE (name LIKE 'C%' OR name LIKE 'W%')
AND ((primary_poc LIKE '%ana%' OR primary_poc LIKE '%Ana%')
AND primary_poc NOT LIKE '%eana%');

# Recap

### Commands

You have already learned a lot about writing code in SQL! Let’s take a moment to recap all that we have covered before moving on:

### Other Tips

Though SQL is not case sensitive (it doesn’t care if you write your statements as all uppercase or lowercase), we discussed some best practices. The order of the key words does matter! Using what you know so far, you will want to write your statements as:

SELECT col1, col2
FROM table1
WHERE col3  > 5 AND col4 LIKE '%os%'
ORDER BY col5
LIMIT 10;


Notice, you can retrieve different columns than those being used in the ORDER BY and WHERE statements. Assuming all of these column names existed in this way (col1col2col3col4col5) within a table called table1, this query would run just fine.

## Lesson Overview

Now that we have completed this lesson we have covered the following and you are able to:

• Describe why SQL is important
• Explain how SQL data is stored and structured
• Create SQL queries using proper syntax including
• SELECT & FROM
• LIMIT
• ORDER BY
• WHERE
• Basic arithmetic operations
• LIKE
• IN
• NOT
• AND & BETWEEN & OR