2b:null 2c:["$","title",null,{"children":"SQL Course: Session 2 | ggojedap | Observable"}] 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:{"id":"acc6a3c45c818eb4","slug":null,"title":"SQL Course: Session 2","thumbnail":"02edd998ed6b906cbda890bcb885a5ddbff16a855a5a9003dac9907adcfe2c6c","access_level":"public","create_time":"$D2023-05-31T16:05:52.372Z","update_time":"$D2023-05-31T16:32:59.124Z","view_time":null,"first_public_time":"$D2023-05-31T16:05:52.372Z","has_unpublished_edits":null,"fork":{"id":"dea50d3e38dca437","slug":"sql-course-session-2","title":"SQL Course: Session 2","owner":{"id":"f35c755083683fe5","login":"observablehq","name":"Observable"}},"stars":0,"starred":false,"owner":{"type":"team","id":"9248b272e1331053","login":"ggojedap","name":"ggojedap","avatar_url":"https://avatars-next.observableusercontent.com/b10c02becc9dd1f565b151a6cd5265a75cddf92155f5a3f65c9dc8ba8482cd24","tier":"starter_2024","bio":""},"version":313,"publish_version":null,"role":null,"authored":false,"authors":[{"type":"individual","id":"e00cf9378b559fc6","login":"ggojedap","name":"ggojedap","avatar_url":"https://avatars-next.observableusercontent.com/b10c02becc9dd1f565b151a6cd5265a75cddf92155f5a3f65c9dc8ba8482cd24","tier":null,"bio":""}],"members":[{"type":"individual","id":"e00cf9378b559fc6","login":"ggojedap","name":"ggojedap","avatar_url":"https://avatars-next.observableusercontent.com/b10c02becc9dd1f565b151a6cd5265a75cddf92155f5a3f65c9dc8ba8482cd24","tier":null,"role":"editor"}],"comments":[],"custom_thumbnail":false,"trashed":false,"lapsed":null,"subscribed":null,"forks":0,"collection":null,"type":"notebook","body":{"titling":"auto","title":"SQL Course: Session 2","cells":[{"id":0,"value":"# SQL Course: Session 2","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"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","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"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","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":12,"value":"import { pizzeriaDB } from '@observablehq/pizza-paradise-data'","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"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","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":14,"value":"---\n## Activity 1\n
","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"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","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":141,"value":"SELECT * \n FROM orders\n JOIN stores\n ON orders.storeId = stores.id","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","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","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":152,"value":"","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"var:pizzeriaDB"},{"id":19,"value":"---\n## Activity 2\n
","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"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","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":165,"value":"","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","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","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":175,"value":"","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","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","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":181,"value":"","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","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","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":232,"value":"","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"var:pizzeriaDB"},{"id":22,"value":"---\n## Activity 3\n
","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"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","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":186,"value":"","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","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","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":205,"value":"","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"var:pizzeriaDB"},{"id":25,"value":"---\n## Activity 4\n
","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":26,"value":"### Filters\n- Filter the product table by category, for only \"Classic\" products","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":246,"value":"","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"var:pizzeriaDB"},{"id":252,"value":"- Filter the stores table by stores that are in Utah.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":254,"value":"","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","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","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":257,"value":"","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"var:pizzeriaDB"},{"id":30,"value":"---\n## Activity 5\n
","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":31,"value":"### And, Or, Between\n- Filter products based on the size and category columns","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":126,"value":"","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","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","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":267,"value":"","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","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","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":276,"value":"","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","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","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":125,"value":"","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","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","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":134,"value":"","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"var:pizzeriaDB"},{"id":43,"value":"---\n## Wrap Up\nTest your knowledge from session one with these short questions.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":44,"value":"$31","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":45,"value":"

","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":46,"value":"

","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":47,"value":"

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