25:["$","title",null,{"children":"SQL Course: Session 1 | Ashish Soni | Observable"}] 4b:T6b6,testYourKnowledge([ { title: 'Aggregation', backText: [`You don't always need a GROUP BY clause to aggregate values in SQL. You only need a GROUP BY when you are aggregating by some value. To aggregate all values, exclude the GROUP BY clause and only use aggregation functions in the SELECT clause.`, `If you try to aggregate values in a SQL query by some column name, then you will need a GROUP BY clause, and will see an error message if its missing.`], question: "To aggregate values in a SQL query, you always need a GROUP BY clause", answer: false }, { title: 'DuckDB', backText: ['While a database just about ducks would sure be nice, DuckDB is not that.', 'DuckDB is a database framework and is used in Observable to create SQL databases in notebooks.'], question: 'DuckDB is a database on the various types of duck breeds, their wingspan, and bill length.', answer: false }, { title: 'Ordering Clauses', backText: [`Order matter in SQL queries! If you write your clauses in the wrong order, you will get an error message`, `Every SELECT statement will start with the SELECT clause`, `The general order is SELECT ➡️ FROM ➡️ WHERE ➡️ GROUP BY ➡️ HAVING ➡️ ORDER BY ➡️ LIMIT`], question: `The order of your SELECT clauses does not matter in SQL.`, answer: false }, { title: 'Star Operator', backText: [`While you can use the star operator for multiply two values, it's also the short-hand to pull all the columns from a table.`, `For example, SELECT * FROM myTable will pull all the data from myTable.`], question: `The star operator (*) in SQL is only for multiplying two numerical values.`, answer: false }, ], 300, 300)4c:[] 47:{"id":"a72f9192e8bfdbad","slug":"session-1","title":"SQL Course: Session 1","thumbnail":"b95a2eeb77d03ae4030cbea577e18063c2c7de2bedb43c75decf99c386857441","access_level":"public","create_time":"$D2023-06-07T16:59:25.213Z","update_time":"$D2025-07-19T12:21:02.252Z","view_time":null,"first_public_time":"$D2023-06-07T16:59:25.213Z","has_unpublished_edits":null,"fork":{"id":"b32a5cf263af8a48","slug":"sql-course-session-1","title":"SQL Course: Session 1","owner":{"id":"f35c755083683fe5","login":"observablehq","name":"Observable"}},"stars":0,"starred":false,"owner":{"type":"team","id":"a909dbdeb1a30505","login":"ashish-soni08","name":"Ashish Soni","avatar_url":"https://avatars-next.observableusercontent.com/ef95a17c9bd17b5baf10172639039a87a94018599893f4205c0cbb85040fedfb","tier":"starter_2024","bio":"","followed_by_you":false,"follows_you":false},"version":316,"publish_version":null,"role":null,"authored":false,"authors":[{"type":"individual","id":"f1ef76bb5f9b5c1d","login":"ashish-soni08","name":"Ashish Soni","avatar_url":"https://avatars-next.observableusercontent.com/ef95a17c9bd17b5baf10172639039a87a94018599893f4205c0cbb85040fedfb","tier":null,"bio":"","followed_by_you":false,"follows_you":false}],"members":[{"type":"individual","id":"f1ef76bb5f9b5c1d","login":"ashish-soni08","name":"Ashish Soni","avatar_url":"https://avatars-next.observableusercontent.com/ef95a17c9bd17b5baf10172639039a87a94018599893f4205c0cbb85040fedfb","tier":null,"role":"editor"}],"comments":[],"custom_thumbnail":false,"trashed":false,"lapsed":null,"subscribed":null,"forks":0,"collection":{"id":"a5792c3c6886ddd9","slug":"sql-course","title":"SQL Course","owner":{"id":"a909dbdeb1a30505","login":"ashish-soni08","name":"Ashish Soni"}},"type":"notebook","body":{"titling":"auto","title":"SQL Course: Session 1","cells":[{"id":0,"value":"# SQL Course: Session 1","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":40,"value":"- [Session 1 slide deck](https://docs.google.com/presentation/d/19CTi6r8VDtptlL4l60J6NOKmYXEyPsTBVmgefs-P0m0/edit?usp=sharing)","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":5,"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","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":8,"value":"import { pizzeriaDB } from '@observablehq/pizza-paradise-data'","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":29,"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","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":43,"value":"---\n## Activity 1\n
","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":57,"value":"### Observable Setup\n1. Sign-in / Sign-up\n2. Fork this notebook\n4. Edit and run the cell below","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":72,"value":"note(md`__Note:__ You can run a cell by clicking on the play button to the right of the code, or using Shift + Enter`)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":63,"value":"whereIHaveLived = [\n {location: \"Magdeburg\", date: \"2018-03-25\"},\n {location: \"Bonn\", date: \"2022-09-01\"},\n {location: \"Leipzig\", date: \"2024-09-23\"},\n {location: \"Chandigarh\", date: \"1992-08-12\"},\n {location: \"Mohali\", date: \"2009-06-01\"},\n {location: \"Boulder\", date: \"2011-08-01\"},\n {location: \"Brooklyn\", date: \"2013-04-01\"},\n {location: \"Boulder\", date: \"2019-12-01\"},\n]","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":67,"value":"SELECT location FROM whereIHaveLived","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"var:whereIHaveLived"},{"id":55,"value":"---\n## Activity 2\n
","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":12,"value":"### Select * Query\n- Use `*` to select all the columns","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":27,"value":"SELECT * FROM products","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"var:pizzeriaDB"},{"id":79,"value":"---\n## Activity 3\n
","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":88,"value":"### Select specific columns\n- Separate column names with a comma","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":81,"value":"SELECT name,\n price,\n category,\n ingredients\nFROM products","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"var:pizzeriaDB"},{"id":92,"value":"---\n## Activity 4\n
","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":98,"value":"### Select distinct values\n- Use SELECT DISTINCT to get distinct values of states from the stores table","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":100,"value":"SELECT DISTINCT name\n FROM products","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"var:pizzeriaDB"},{"id":218,"value":"- Use SELECT DISTINCT to get distinct values of states and cities from the stores table","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":132,"value":"SELECT DISTINCT name, size\n FROM products","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"var:pizzeriaDB"},{"id":118,"value":"---\n## Activity 5\n
","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":312,"value":"SELECT state,\n count(*) as num_stores\nFROM stores\nGROUP BY state","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"var:pizzeriaDB"},{"id":117,"value":"### Aggregate values\n- Count the number of stores by state using GROUP BY","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":119,"value":"SELECT price,\n count(*) as product_prices,\nFROM products\nGROUP BY price","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"var:pizzeriaDB"},{"id":138,"value":"- Count the number of stores","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":304,"value":"SELECT name, \n count(*) as num_products\nFROM products\nGROUP BY name","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"var:pizzeriaDB"},{"id":143,"value":"SELECT count(*) as num_products\n FROM products","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"var:pizzeriaDB"},{"id":257,"value":"SELECT count(distinct state) as num_stores\n FROM stores","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"var:pizzeriaDB"},{"id":140,"value":"- Count the number of products","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":307,"value":"SELECT count(distinct name) as num_products\nFROM products","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"var:pizzeriaDB"},{"id":150,"value":"SELECT count(*) as num_products\n FROM products","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"var:pizzeriaDB"},{"id":148,"value":"- Count the number of products by category, and find the average price","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":153,"value":"SELECT Category\n , avg(Price) as avg_price\n , count(*) as num_products\n FROM products\n GROUP BY Category","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"var:pizzeriaDB"},{"id":160,"value":"- Count the number of distinct prices by category","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":165,"value":"SELECT Category,\n count(distinct Price) as price\nFROM products\nGROUP BY Category","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"var:pizzeriaDB"},{"id":185,"value":"- Count the number of products by category AND size","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":190,"value":"SELECT Category\n , \"Size\"\n , avg(Price) as avg_price\n , count(*) as num_products\n FROM products\n GROUP BY Category\n , \"Size\"","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"var:pizzeriaDB"},{"id":198,"value":"---\n## Wrap Up\nTest your knowledge from session one with these short questions.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":200,"value":"$4b","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":21,"value":"

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

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

","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":16,"value":"---\n## Appendix","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":18,"value":"import {note} from \"@observablehq/callout-boxes\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":51,"value":"import { showMe } from \"@observablehq/show-me\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":195,"value":"import {testYourKnowledge} from \"@observablehq/test-your-knowledge-cards\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":105,"value":"color = d3.schemeBlues[7]","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"}],"files":"$W4c","stdlib":"1"}} 24:null