Study Notes #20
SQL AND and BETWEEN
- Write a query that returns all the orders where the
standard_qtyis over 1000, the
poster_qtyis 0, and the
SELECT * FROM orders WHERE standard_qty > 1000 AND poster_qty = 0 AND gloss_qty = 0;
- 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.
- 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_qtydata 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).
- Use the web_events table to find all information regarding individuals who were contacted via the
adwordschannels, 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’.
- 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 (
/), LIKE, IN, NOT, AND, 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'
- Find list of orders ids where either
poster_qtyis greater than 4000. Only include the
idfield in the resulting table.
SELECT id FROM orders WHERE gloss_qty >4000 OR poster_qty >4000;
- Write a query that returns a list of orders where the
standard_qtyis zero and either the
poster_qtyis over 1000.
SELECT * FROM orders WHERE (gloss_qty >1000 OR poster_qty >1000) AND standard_qty = 0; Answer Key: SELECT * FROM orders WHERE standard_qty = 0 AND (gloss_qty > 1000 OR poster_qty > 1000);
- 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%'; Answer Key: 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%');
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:
|Statement||How to Use It||Other Details|
|SELECT||SELECT Col1, Col2, …||Provide the columns you want|
|FROM||FROM Table||Provide the table where the columns exist|
|LIMIT||LIMIT 10||Limits based number of rows returned|
|ORDER BY||ORDER BY Col||Orders table based on the column. Used with DESC.|
|WHERE||WHERE Col > 5||A conditional statement to filter your results|
|LIKE||WHERE Col LIKE ‘%me%’||Only pulls rows where column has ‘me’ within the text|
|IN||WHERE Col IN (‘Y’, ‘N’)||A filter for only rows with column of ‘Y’ or ‘N’|
|NOT||WHERE Col NOT IN (‘Y’, ‘N’)||NOT is frequently used with LIKE and IN|
|AND||WHERE Col1 > 5 AND Col2 < 3||Filter rows where two or more conditions must be true|
|OR||WHERE Col1 > 5 OR Col2 < 3||Filter rows where at least one condition must be true|
|BETWEEN||WHERE Col BETWEEN 3 AND 5||Often easier syntax than using an AND|
|CREATE TABLE||is a statement that creates a new table in a database.|
|DROP TABLE||is a statement that removes a table in a database.|
|Entity-relationship diagram (ERD)||A common way to view data in a database.|
|FROM||specifies from which table(s) you want to select the columns. Notice the columns need to exist in this table.|
|SELECT||allows you to read data and display it. This is called a query and it specifies from which table(s) you want to select the columns.|
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 (
col5) within a table called
table1, this query would run just fine.
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
- ORDER BY
- Basic arithmetic operations
- AND & BETWEEN & OR
In the next lesson, you will be learning about JOINs. This is the real secret (well not really a secret) behind the success of SQL as a language. JOINs allow us to combine multiple tables together. All of the operations we learned here will still be important moving forward, but we will be able to answer much more complex questions by combining information from multiple tables! You have already mastered so much – potentially writing your first code ever, but it is about to get so much better!