31:T7ff,testYourKnowledge([ { title: 'Joins', backText: [`While there are many types of joins, 99% of the time you'll only need JOIN and LEFT JOIN`, `Err on the side of simplicity. It may seem that you need a more complicated join, but often if you change your point of view, a simple JOIN or LEFT JOIN will suffice.`], question: "The only joins you'll need are JOIN and LEFT JOIN", answer: true }, { title: 'Filtering', backText: ['Although they are similar, the WHERE clause and HAVING clause are different – the HAVING clause can filter computed values, while the WHERE clause cannot.', 'The HAVING clause is more expensive than the WHERE clause, expensive as in it can make your query slow. It is better to use WHERE when you can.'], question: 'The WHERE and HAVING clause do basically the same thing, and are interchangable.', answer: false }, { title: 'ANSI', backText: [`Yes, The American National Standards Institute (ANSI) is a private nonprofit organization that oversees the development of voluntary consensus standards for products, services, processes, systems, and personnel in the United States.`, `ANSI and ISO are acronyms that stand for two of the organizations which set consensus standards for products. For example, ANSI, the American National Standards Institute, and ISO, the International Organization for Standardization, provide the two most common physical layouts for keyboards.`], question: `ANSI stands for the American National Standards Institute`, answer: true }, { title: 'Filtering', backText: [`When you add filters to your JOIN logic, you're filtering the table you're joining on, thereby changing it. This is different than filtering in the WHERE clauses, which occurs after the JOIN happens.`, `JOIN filters can be an effective way to reduce the number of records you're joining on, which can also make your query run faster.`], question: `Adding filters to your JOIN logic or in the WHERE clause does the same thing.`, answer: false }, ], 300, 300)32:[] 2f:{"title":"SQL Course: Session 2","cells":[{"id":0,"value":"# SQL Course: Session 2","pinned":false,"hidden":false,"mode":"md"},{"id":10,"value":"- [Session 2 slide deck](https://docs.google.com/presentation/d/1N1yuZx3Lq9o_9LrmJ7CnS1XOsXa7fFXLoj7_afgJdLM/edit?usp=sharing)","pinned":false,"hidden":false,"mode":"md"},{"id":11,"value":"For the hands-on portion of this course, we'll use the [Pizza Paradise](https://observablehq.com/@observablehq/pizza-paradise-data) dataset. ","pinned":false,"hidden":false,"mode":"md"},{"id":12,"value":"import { pizzeriaDB } from '@observablehq/pizza-paradise-data'","pinned":true,"hidden":false,"mode":"ojs"},{"id":13,"value":"note(md`__Note:__ This is a large, realistic mock dataset, so it may take a few seconds for it to load in your notebook.`)","pinned":false,"hidden":false,"mode":"ojs"},{"id":14,"value":"---\n## Activity 1\n
","pinned":false,"hidden":false,"mode":"md"},{"id":15,"value":"### Join tables\n- Join the stores table to the orders table and count the number of orders by the store's state.","pinned":false,"hidden":false,"mode":"md"},{"id":141,"value":"SELECT stores.state\n , count(*) as num_orders_one\n , count(distinct orders.id) as num_orders\n FROM stores\n JOIN orders\n ON stores.id = orders.storeId\n GROUP BY stores.state","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:pizzeriaDB"},{"id":148,"value":"- Join three tables, the stores, customers, and orders tables, counting number of orders and distinct customers by the store's state.","pinned":false,"hidden":false,"mode":"md"},{"id":152,"value":"SELECT stores.state\n , count(*) as num_orders_one\n , count(distinct orders.id) as num_orders\n , count(distinct customers.id) as num_customers\n FROM stores\n JOIN orders\n ON stores.id = orders.storeId\n JOIN customers\n ON orders.customerId = customers.id\n GROUP BY stores.state","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:pizzeriaDB"},{"id":19,"value":"---\n## Activity 2\n
","pinned":false,"hidden":false,"mode":"md"},{"id":20,"value":"### 1:0, 1:1, 1:many relationships\n- Filter the stores table by joining to california_stores","pinned":false,"hidden":false,"mode":"md"},{"id":165,"value":"SELECT stores.*\n FROM stores\n JOIN california_stores\n ON stores.id = california_stores.id","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:pizzeriaDB"},{"id":172,"value":"- Return 0 results by joining california_stores to nevada_stores on the store_id","pinned":false,"hidden":false,"mode":"md"},{"id":175,"value":"SELECT stores.*\n FROM stores\n JOIN california_stores\n ON stores.id = california_stores.id\n JOIN nevada_stores\n ON stores.id = nevada_stores.id","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:pizzeriaDB"},{"id":179,"value":"- Join the stores table to the orders table to aggregate the number of customers per store.","pinned":false,"hidden":false,"mode":"md"},{"id":181,"value":"SELECT stores.state\n , count(*) as num_orders_one\n , count(distinct orders.id) as num_orders\n , count(distinct orders.customerId) as num_customers\n FROM stores\n JOIN orders\n ON stores.id = orders.storeId\n GROUP BY stores.state","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:pizzeriaDB"},{"id":224,"value":"- Join stores table by joining to california_daily_orders, pulling only data from the stores table. (_Note the number of results._)","pinned":false,"hidden":false,"mode":"md"},{"id":232,"value":"SELECT stores.state\n , count(*) as num_orders_one\n , count(distinct orders.id) as num_orders\n , count(distinct orders.customerId) as num_customers\n FROM stores\n JOIN orders\n ON stores.id = orders.storeId\n JOIN california_stores\n ON stores.id = california_stores.id\n GROUP BY stores.state","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:pizzeriaDB"},{"id":22,"value":"---\n## Activity 3\n
","pinned":false,"hidden":false,"mode":"md"},{"id":23,"value":"### Left Join\n- Left join the california_stores to the stores table to show which stores are in california","pinned":false,"hidden":false,"mode":"md"},{"id":186,"value":"SELECT stores.zipcode\n , stores.state\n , stores.city\n , california_stores.state as \"Is Cali?\"\n , nevada_stores.state as \"Is Nevada?\"\n FROM stores\n LEFT\n JOIN california_stores\n ON stores.id = california_stores.id\n LEFT\n JOIN nevada_stores\n ON stores.id = nevada_stores.id","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:pizzeriaDB"},{"id":199,"value":" - Left join the california_stores to the orders table to count sales that are in and not in California.","pinned":false,"hidden":false,"mode":"md"},{"id":205,"value":"SELECT california_stores.state\n , count(*) as num_orders_one\n , count(distinct orders.id) as num_orders\n , count(distinct orders.customerId) as num_customers\n FROM stores\n JOIN orders\n ON stores.id = orders.storeId\n LEFT\n JOIN california_stores\n ON stores.id = california_stores.id\n GROUP BY california_stores.state","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:pizzeriaDB"},{"id":25,"value":"---\n## Activity 4\n
","pinned":false,"hidden":false,"mode":"md"},{"id":26,"value":"### Filters\n- Filter the product table by category, for only \"Classic\" products","pinned":false,"hidden":false,"mode":"md"},{"id":246,"value":"SELECT * \n FROM products \n WHERE Category = 'Classic'","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:pizzeriaDB"},{"id":252,"value":"- Filter the stores table by stores that are in Utah.","pinned":false,"hidden":false,"mode":"md"},{"id":254,"value":"SELECT * \n FROM stores \n WHERE state = 'Utah'","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:pizzeriaDB"},{"id":249,"value":"- Filter stores by state (state = 'Nevada'), and by those that have had over 100,000 orders using the HAVING clause","pinned":false,"hidden":false,"mode":"md"},{"id":257,"value":"SELECT stores.state\n , stores.city\n , count(distinct orders.id) as num_orders\n FROM stores \n JOIN orders\n ON stores.id = orders.storeId\n WHERE stores.state = 'Nevada'\n GROUP BY 1, 2\nHAVING num_orders >= 100000","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:pizzeriaDB"},{"id":30,"value":"---\n## Activity 5\n
","pinned":false,"hidden":false,"mode":"md"},{"id":31,"value":"### And, Or, Between\n- Filter products based on the size and category columns","pinned":false,"hidden":false,"mode":"md"},{"id":126,"value":"SELECT * \n FROM products\n WHERE Category = 'Classic'\n AND Size = 'Large'","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:pizzeriaDB"},{"id":269,"value":"- Filters products based on the size and category, where size can be either large **or** small","pinned":false,"hidden":false,"mode":"md"},{"id":267,"value":"SELECT * \n FROM products\n WHERE Category = 'Classic'\n AND (Size = 'Large' OR Size = 'Small')","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:pizzeriaDB"},{"id":273,"value":"- Filters order_by_product_day where the dates are **between** January 1st 2021 and February 1st 2021","pinned":false,"hidden":false,"mode":"md"},{"id":276,"value":"SELECT *\n FROM orders_by_product_day\n WHERE order_date BETWEEN '2021-01-01' AND '2021-02-01'","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:pizzeriaDB"},{"id":123,"value":"- Add additional filters to the JOIN logic (make that a LEFT JOIN) and compare to adding those filters in the WHERE.","pinned":false,"hidden":false,"mode":"md"},{"id":125,"value":"","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:pizzeriaDB"},{"id":163,"value":"Compare the results to when these filters are added to the WHERE clause.","pinned":false,"hidden":false,"mode":"md"},{"id":134,"value":"","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:pizzeriaDB"},{"id":43,"value":"---\n## Wrap Up\nTest your knowledge with these short questions.","pinned":false,"hidden":false,"mode":"md"},{"id":44,"value":"$31","pinned":false,"hidden":false,"mode":"ojs"},{"id":45,"value":"

","pinned":false,"hidden":false,"mode":"md"},{"id":46,"value":"

","pinned":false,"hidden":false,"mode":"md"},{"id":47,"value":"

","pinned":false,"hidden":false,"mode":"md"},{"id":48,"value":"---\n## Appendix","pinned":false,"hidden":false,"mode":"md"},{"id":49,"value":"import {note} from \"@observablehq/callout-boxes\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":50,"value":"import { showMe } from \"@observablehq/show-me\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":51,"value":"import {testYourKnowledge} from \"@observablehq/test-your-knowledge-cards\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":52,"value":"color = d3.schemeBlues[7]","pinned":false,"hidden":false,"mode":"ojs"}],"files":"$W32","stdlib":"1","titling":"auto"}