Study Notes #20

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;

Answer Key: 
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%';

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%');

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:

StatementHow to Use ItOther Details
SELECTSELECT Col1Col2, …Provide the columns you want
FROMFROM TableProvide the table where the columns exist
LIMITLIMIT 10Limits based number of rows returned
ORDER BYORDER BY ColOrders table based on the column. Used with DESC.
WHEREWHERE Col > 5A conditional statement to filter your results
LIKEWHERE Col LIKE ‘%me%’Only pulls rows where column has ‘me’ within the text
INWHERE Col IN (‘Y’, ‘N’)A filter for only rows with column of ‘Y’ or ‘N’
NOTWHERE Col NOT IN (‘Y’, ‘N’)NOT is frequently used with LIKE and IN
ANDWHERE Col1 > 5 AND Col2 < 3Filter rows where two or more conditions must be true
ORWHERE Col1 > 5 OR Col2 < 3Filter rows where at least one condition must be true
BETWEENWHERE Col BETWEEN 3 AND 5Often easier syntax than using an AND

Key Terms

KeyTermDefinition
CREATE TABLEis a statement that creates a new table in a database.
DROP TABLEis a statement that removes a table in a database.
Entity-relationship diagram (ERD)A common way to view data in a database.
FROMspecifies from which table(s) you want to select the columns. Notice the columns need to exist in this table.
SELECTallows 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.

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

Looking Ahead

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!

This entry was posted in Study Notes and tagged . Bookmark the permalink.

Leave a Reply