Quiz: AND and BETWEEN

 

Quiz: AND and BETWEEN


Questions using AND and BETWEEN operators


  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.

  2. Using the accounts table, find all the companies whose names do not start with 'C' and end with 's'.

  3. 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.

  4. 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.

Solutions to AND and BETWEEN Questions


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.


  1. SELECT *
FROM orders
WHERE standard_qty > 1000 AND poster_qty = 0 AND gloss_qty = 0;


2. Using the accounts table, find all the companies whose names do not start with 'C' and end with 's'.

SELECT name
FROM accounts
WHERE name NOT LIKE 'C%' AND name LIKE '%s';


3. 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;


You should notice that there are a number of rows in the output of this query where the gloss_qty values are 24 or 29. So the answer to the question is that yes, the BETWEEN operator in SQL is inclusive; that is, the endpoint values are included. So the BETWEEN statement in this query is equivalent to having written "WHERE gloss_qty >= 24 AND gloss_qty <= 29."

4. You will notice that using BETWEEN is tricky for dates! 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'.


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;

Comments

Post a Comment

Popular posts from this blog

Entity Relationship Diagrams

Image
  Entity Relationship Diagrams From the last lesson, you might remember that an  entity relationship diagram  (ERD) is a common way to view data in a database. It is also a key element to understanding how we can pull data from multiple tables. It will be beneficial to have an idea of what the ERD looks like for Parch & Posey handy, so I have posted it again below.  You might even print a copy to have with you as you work through the exercises in the remaining content. Tables & Columns In the Parch & Posey database there are 5 tables: web_events accounts orders sales_reps region You will notice some of the columns in the tables have  PK  or  FK  next to the column name, while other columns don't have a label at all. If you look a little closer, you might notice that the  PK  is associated with the first column in every table. The  PK  here stands for  primary key .  A primary key exists in every table, and...
Read more

Arithmetic Operators

  Arithmetic Operators Derived Columns Creating a new column that is a combination of existing columns is known as a  derived  column (or "calculated" or "computed" column). Usually you want to give a name, or "alias," to your new column using the  AS  keyword. This derived column, and its alias, are generally only temporary, existing just for the duration of your query. The next time you run a query and access this table, the new column will not be there. If you are deriving the new column from existing columns using a mathematical expression, then these familiar mathematical operators will be useful: *  (Multiplication) +  (Addition) -  (Subtraction) /  (Division) Consider this example: SELECT id , ( standard_amt_usd / total_amt_usd ) * 100 AS std_percent , total_amt_usd FROM orders LIMIT 10 ; Here we divide the standard paper dollar amount by the total order amount to find the standard paper percent for the order, and use the  ...
Read more

NOT

  NOT The  NOT  operator is an extremely useful operator for working with the previous two operators we introduced:  IN  and  LIKE . By specifying  NOT LIKE  or  NOT IN , we can grab all of the rows that do not meet a particular criteria. Questions using the NOT operator We can pull all of the rows that were excluded from the queries in the previous two concepts with our new operator. Use the  accounts  table to find the account name, primary poc, and sales rep id for all stores except Walmart, Target, and Nordstrom. Use the  web_events  table to find all information regarding individuals who were contacted via any method except using  organic  or  adwords  methods. Use the  accounts  table to find: All the companies whose names do not start with 'C'. All companies whose names do not contain the string 'one' somewhere in the name. All companies whose names do not end with 's'. Solutions to NOT...
Read more