44:["$","title",null,{"children":"Learn SQL in 10 Minutes! | Nolan Johnson | Observable"}] 4c:T51b,md` In the next 10 minutes you’ll learn a method for writing SQL that will initially serve as a crutch, allowing you to write working SQL slowly, but effectively. Pretty quickly, you'll internalize the method and will rely on the crutch less and less, allowing you to walk, run, skip and dance with SQL. One note on this approach: as unintuitive as it sounds, I recommend that you **don't work through the examples** your first time through. When learning to code, students tend to get into a mindless see/do copy/paste mode when they follow along tutorials. Instead, read through the examples and try to understand the method as clearly as possible. Once you've completed the lesson, try out your new skills with [SQLite in Observable](https://beta.observablehq.com/@nolan-ga/sqlite-in-observable) or by downloading your own simple-to-setup database, like [SQLiteBrowser](http://sqlitebrowser.org/&sa=D&ust=1545435482000000). Testing yourself is where the real learning will happen. By the end of this, even if you've never seen a single SQL statement before, you'll be able to understand and even write one like the following: \`SELECT title, count(1)\`
\`FROM people\`
\`WHERE last < 'N'\`
\`GROUP BY 1\`
\`HAVING count(1) > 1\`
\`ORDER BY 2 DESC;\` OK, start your timer.`4d:T596, md`In the SGT, \`SELECT\`, \`FROM\` and the semicolon (;) are part of every query, indicated in **bold**. All the other clauses are optional and skipped if not needed. The description indicates what effect including that clause has on the data that are returned to you. For instance, \`WHERE\` filters out data based on a condition; \`ORDER BY\` orders data; \`LIMIT\` allows us to return a specific number of records. ___ > **SELECT and FROM are part of every SQL statement; all others are optional** ___ As you can see, the type of “reference to the data” is dependent on which keyword precedes it. \`SELECT\` is followed by one or more field names from the table that’s specified in the \`FROM\` clause. Those field names are the ones you will get back from the database. The method is as follows: 1. Start from the top of SGT, with the required \`SELECT\` followed by one or more field name, separated by commas, and \`FROM\` followed by the table name. 2. For each subsequent row in SGT, if the effect in the "Description" column is desired (based on the question being asked), include the "Keyword" verbatim and a "Reference to the Data" as described. Otherwise, proceed to the next row. 3. Once you've gone through all the rows, end the statement with the required semi-colon (;) ___ > **Write the "Keyword" verbatim and replace the appropriate "Reference to the Data" based on the table you're querying** ___ `4e:T6f4,md`Looks a lot like English, huh? Starting from the top of the SGT, we write the keyword verbatim, \`SELECT\`, followed by the appropriate “reference to the data,” which for \`SELECT\` is a list of field names: \`first, last\`. Only place a comma **between items** in the list, **not after the last one**. Next we have \`FROM\` followed by the name of the table we are accessing from the database, \`people\`. We don’t want to invoke any of the optional properties of the other SQL clauses such as filtering out records or limiting to a specific number of rows, so we’re done. We end the statement with the required semicolon and send it off to the database. Here's what the matching SGT would look like: SQL Keyword | Reference to the Data | Description --- | --- | --- **SELECT** | **first, last** | *The data you want to see* ** FROM ** | **people** | *The table where the data lives* ${greyStrike} WHERE | ${greyStrike} condition is true | *Filters to rows where condition is true* ${greyStrike} GROUP BY | ${greyStrike} field(s) to group on | *Summarize groups of data* ${greyStrike} HAVING | ${greyStrike} condition is true for the group| *Filters to groups where aggregate is true* ${greyStrike} ORDER BY | ${greyStrike} field(s) to sort on | *Sorts the data* ${greyStrike} LIMIT | ${greyStrike} a number (of rows to return) | *Limits to a certain number of rows* **;** | | *Ends the SQL statement* To align with the SGT, the semi-colon is placed on its own line, but that's not required. In fact, the following is identical to the above, from a SQL perspective. > \`SELECT first, last FROM people;\` New lines don't affect the correctness of the SQL, but as your statement gets longer, it does help with readability!`4f:T6f3,md`There's only one change to our previous query here. We replaced the two field names in the \`SELECT\` clause with the function \`count\` to which we passed an argument, the number 1. The argument 1 is a convention; what’s important is how it is interpreted. SQL is all about tables, so whatever you put on the right side of the \`SELECT\` clause is what is returned for every record. Previously, when we had the fields \`first\` and \`last\` in the \`SELECT\` clause, it returned those fields for every record. In this case, the number 1 is returned for every record. You can prove it by writing \`SELECT 1\` instead of \`SELECT count(1)\`. The SQL function \`count\` can pretty much count anything so it's just a convention to use the number 1. As we know from using functions to summarize data elsewhere (like Excel), a function takes a bunch of data and turns it into a single number, like the count of rows, and that’s exactly what happens here. Functions like this one are called aggregate functions and some others include \`sum\` and \`avg\` for average. In the query written above, we can’t mix aggregate functions and field names in the \`SELECT\` clause, \`SELECT first, count(1)\` because the function should return a single row while the field should return multiple rows. That doesn't work. If we want to know the count of people with a particular first name, we would need to split our table into groups, which is what we'll be doing in the next and final example. One last note before we move on, I kept the ORDER BY clause in the query above for consistency and because it will be handy again in the next example. The count of records is not going to change based on how we sort the data, so that could have been removed without changing the result.`50:T903,md`Wow, that’s a doozy, right? But we’ve got this, one piece of SGT at a time. We added the \`GROUP BY\` clause and the right side of that clause is a field name, like we had with \`SELECT\` and \`ORDER BY\` previously. We added \`title\` to our \`SELECT\` list since we want to know which “count” corresponds to which title. Most of the time when grouping, your \`SELECT\` list will contain all the fields in the \`GROUP BY\` clause, which are essentially the group’s title, as well as any aggregate functions (yes, there could be more than one). Just like in the previous example, it can’t contain fields that aren’t either being grouped on or aggregated because they would want to return a different number of rows. We also included the \`HAVING\` clause, which is similar to \`WHERE\` in that it contains a condition, but instead of a “row-wise” condition that’s True or False for each record, it contains a “group-wise” condition that’s True or False for each group. For that reason, \`HAVING\` conditions use conditions that contain aggregate functions and **can only be used when \`GROUP BY\` is also used.** In this case, we get rid of any groups that don’t satisfy the condition of having a group count greater than 1, a single record. Lastly, we’re doing something different with the \`ORDER BY\`. Namely, sorting by the function count with the highest value at the top by specifying \`DESC\`. This will put the most common title (by count) at the top and the least common at bottom. There’s a convenient shortcut when you are grouping and sorting by one of the fields in your \`SELECT\` clause. We can refer to it numerically by its position or index. So instead of \`ORDER BY count(1) DESC\`, we could have used \`ORDER BY 2 DESC\`. The 2 refers to the fact that in the \`SELECT\` clause, \`count(1)\` is the second item and we want to sort by it descending. This same trick works for the \`GROUP BY\` clause but those are the only two. So the more convenient (and common) version of this query, in its final form, is below. \`SELECT title, count(1)\`
\`FROM people\`
\`WHERE last < 'N'\`
\`GROUP BY 1\`
\`HAVING count(1) > 1\`
\`ORDER BY 2 DESC;\` Feel free to copy and paste that to confirm that it returns an identical result.`51:[] 48:{"id":"1bba43723222c335","slug":"learn-sql-in-10-minutes","title":"Learn SQL in 10 Minutes!","thumbnail":"1daaecafe44bdfe5493b4f21d15c026f95ffd796c65f201a379d63495cb475d4","access_level":"public","create_time":"$D2018-12-21T15:29:27.420Z","update_time":"$D2019-02-25T22:17:09.901Z","view_time":null,"first_public_time":"$D2018-12-25T04:52:41.982Z","has_unpublished_edits":null,"fork":false,"stars":8,"starred":false,"owner":{"type":"team","id":"4e72771d598f1f0e","login":"nolan-ga","name":"Nolan Johnson","avatar_url":"https://avatars-next.observableusercontent.com/2cc7f62950d5c643ca51dbe74fbe2553416a808edb6044dd4aca45cf74764626","tier":"starter_2024","bio":"","followed_by_you":false,"follows_you":false},"version":844,"publish_version":844,"role":null,"authored":false,"authors":[{"type":"individual","id":"00b1feedfef17585","login":"nolan-ga","name":"Nolan Johnson","avatar_url":"https://avatars-next.observableusercontent.com/2cc7f62950d5c643ca51dbe74fbe2553416a808edb6044dd4aca45cf74764626","tier":null,"bio":"","followed_by_you":false,"follows_you":false}],"members":[{"type":"individual","id":"00b1feedfef17585","login":"nolan-ga","name":"Nolan Johnson","avatar_url":"https://avatars-next.observableusercontent.com/2cc7f62950d5c643ca51dbe74fbe2553416a808edb6044dd4aca45cf74764626","tier":null,"role":"editor"}],"comments":[],"custom_thumbnail":false,"trashed":false,"lapsed":null,"subscribed":null,"forks":6,"collection":{"id":"c2b855f2a3c2cb5a","slug":"learn-sql","title":"Learn SQL","owner":{"id":"4e72771d598f1f0e","login":"nolan-ga","name":"Nolan Johnson"}},"type":"notebook","body":{"titling":"auto","title":"Learn SQL in 10 Minutes!","cells":[{"id":278,"value":"md`# Learn SQL in 10 Minutes!`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":314,"value":"md`*The [first version of this tutorial](https://docs.google.com/document/u/1/d/1EMVi8qywx8NJjXKZSzVN1oy9-NKCKa1MmXGeNfQvf_c/pub) was offered as a Google Doc in the Dark Ages before Observable existed. It provided no interactivity (let alone reactivity) and took about 10 minutes to read.*`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":280,"value":"$4c","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":285,"value":"md`## All the background you need`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":287,"value":"md`*Databases* are where we store mass amounts of data. One way of storing data in databases is called *relational*, which means \"a bunch of related, but separate, tables.\" SQL is meant for relational databases. Tables have rows (called records) and columns (called fields). A piece of data is stored in a field in a record.\n___\n> **SQL allows us to retrieve records from tables within relational databases**\n___`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":305,"value":"md`## SQL is easy`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":304,"value":"md`SQL is more structured than most programming languages—in fact SQL is an acronym of Structured Query Language (pronounced ess-cue-ell or sequel, either is fine). Query means question, and we’re learning how to pose questions that a database can respond to with data. Questions posed to a database begin with the keyword SELECT.\n___\n> **Query means question, and we’re learning how to pose questions that a database can respond to with data.**\n___\n\nSQL is made up of “clauses” which are pairings of a **SQL Keyword** (like \\`SELECT\\`) followed by a **Reference to the Data** that’s in a table in the database. These clauses are always written in the same order!\n___\n> **Learning a few special keywords and their correct order is all you need to start writing SQL.**\n___`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":713,"value":"md`## The SGT Method`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":709,"value":"md`The table below shows the order in which the clauses are written. From here on I’m calling this the SQL Grammar (or [Grimoire](https://en.wikipedia.org/wiki/Grimoire)) Table (SGT), since the word “table” is reserved to mean database table already. SGT is your crutch. [Lean on SGT](https://www.youtube.com/watch?v%3DKEXQkrllGbA).`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":325,"value":"sgt = md`#### SQL Grammar Table (SGT)\n\nSQL Keyword | Reference to the Data | Description\n--- | --- | ---\n**SELECT** | field names separated by commas | *The data you want to see*\n** FROM ** | table name | *The table where the data lives*\nWHERE | condition is true | *Filters to rows where condition is true*\nGROUP BY | field(s) to group on | *Summarize groups of data*\nHAVING | condition is true for the group | *Filters to groups where aggregate is true*\nORDER BY | field(s) to sort on | *Sorts the data*\nLIMIT | a number (of rows to return) | *Limits to a certain number of rows*\n**;** | not applicable | *Ends the SQL statement*\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":349,"value":"md`*The semicolon isn’t actually a clause, it just ends the statement and so there is no “reference to the data*`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":838,"value":"md`__Pro Tip__: take a screen shot of the table and keep it open for reference.`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":323,"value":"$4d","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":357,"value":"md`## Examples`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":462,"value":"viewof answers = checkbox({\n description: \"Check to pre-populate all the SQL answers\",\n options: [{ value: \"toggle\", label: \"Show answers\" }],\n value: \"toggle\"\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":380,"value":"md`___\n*Example 1*`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":359,"value":"md`We have a table called \\`people\\` in our database full of first and last names in fields called \\`first\\` and \\`last\\`. To get all the records from that table, we use the simplest worthwhile query, the two required clauses from the first two rows of the table.`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":58,"value":"viewof sql_statement1 = textarea({\n title: \"The first and last name of everyone in 'people'\", \n placeholder: \"Type a SQL statement and hit 'Run'\",\n value: answers ? \"SELECT first, last\\nFROM people\\n;\" : \"\",\n // spellcheck: true,\n width: \"40%\",\n rows: 4,\n submit: \"Run\"\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":114,"value":"dbRecords(sql_statement1)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":362,"value":"$4e","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":382,"value":"md`___\n*Example 2*`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":384,"value":"md`Next let’s get everyone with the same last name together in our data by sorting.`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":374,"value":"viewof sql_statement2 = textarea({\n title: \"The first and last name of everyone in 'people', sorted by last name A-Z\", \n placeholder: \"Type a SQL statement and hit 'Run'\",\n value: answers ? \"SELECT first, last\\nFROM people\\nORDER BY last\\n;\" : \"\",\n // spellcheck: true,\n width: \"40%\",\n rows: 5,\n submit: \"Run\"\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":376,"value":"dbRecords(sql_statement2)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":387,"value":"md`Again, starting from the top, we always begin with \\`SELECT\\` and \\`FROM\\`; no change there. Skip over all rows in SGT that aren’t necessary until we come to \\`ORDER BY\\`, which we write verbatim, followed by the field name that we want to sort, \\`last\\`. Multiple fields to sort on are separated by commas just like with the \\`SELECT\\` clause. Sorting can be done for each field ascending (\\`ASC\\` the default if not specified) or descending (\\`DESC\\`). \\`ORDER BY last DESC\\`, means sort on last from Z-A.\n\nNotice that both this query and the previous both returned *40 rows total*, though only 5 are shown to conserve space. That's because we haven't used any filters yet and the table contains 40 rows.`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":397,"value":"md`___\n*Example 3*`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":399,"value":"md`Next we’ll look at a subset of the \\`people\\` table by filtering down to records where the last name is in the first half of the alphabet (all last names beginning with letters A-M).`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":401,"value":"viewof sql_statement3 = textarea({\n title: \"The name of everyone with a last name in the first half of the alphabet, sorted Z-A\", \n placeholder: \"Type a SQL statement and hit 'Run'\",\n value: answers ? \"SELECT first, last\\nFROM people\\nWHERE last < 'N'\\nORDER BY last DESC\\n;\" : \"\",\n // spellcheck: true,\n width: \"40%\",\n rows: 7,\n submit: \"Run\"\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":403,"value":"dbRecords(sql_statement3)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":407,"value":"md`Only 30 rows this time! So 75% of the \\`people\\` (30 out of 40) have a last name in the first half of the alphabet.\n\nHopefully you're starting to get the hang of using SGT and why we just fit the \\`WHERE\\` clause in between \\`FROM\\` and \\`ORDER BY\\`. \n\nThe “reference to the data” for \\`WHERE\\` is a little different than we have encountered. It’s a *condition* that for each row in our table is either True or False. When it's True, that record is included in the data returned to us. There are many options when it comes to conditions and additional keywords that allow you to get just the data you want.\n\nWe can even combine conditions with AND/OR to be more specific. It's easy to learn these as you go, but keep in mind that instead of the equals sign we can use other common symbols (<, >, <=, >=, <>, !=), the last two both mean not equal.`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":480,"value":"md`___\n*Example 4*`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":483,"value":"md`Two more examples will round us out; the second example will build on the first. Let’s calculate how many records were returned in our last query. Unlike in all previous queries, which returned multiple records, the query below returns a single row containing the count of records returned by our query.`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":485,"value":"viewof sql_statement4 = textarea({\n title: \"The count of people with last name in the first half of the alphabet\", \n placeholder: \"Type a SQL statement and hit 'Run'\",\n value: answers ? \"SELECT count(1) AS count_people\\nFROM people\\nWHERE last < 'N'\\nORDER BY last DESC\\n;\" : \"\",\n // spellcheck: true,\n width: \"40%\",\n rows: 7,\n submit: \"Run\"\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":487,"value":"dbRecords(sql_statement4)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":500,"value":"$4f","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":503,"value":"md`___\n*Example 5*`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":506,"value":"md`OK, in this final example, we're going to look at groups of data within a single table, which is almost always more interesting with aggregate functions than using the whole table as in the previous example. \n\nA group contains all the records that contain the same value in the grouped field, like a Pivot Table in Excel. For instance, if we group on \\`first\\` then we have a group for Kimberly, Edmund, Edgar, Diane, Reba and so on. Since each group contains the same value, when we use \\`GROUP BY\\` our result will contain one row per group, or one row per unique value in the group(ed) fields.\n\nIn our table, there's another field called \\`title\\` that indicates whether that person is a Professor, Curator, Assistant Professor, etc. Let’s find out which titles are most common for people with a last name in the first half of the alphabet. To round out the entire SGT, we’ll even get rid of any titles that occur only once.`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":509,"value":"viewof sql_statement5 = textarea({\n title: \"The count of people with each 'title' that have at least 2 members in the group\", \n placeholder: \"Type a SQL statement and hit 'Run'\",\n value: answers ? \"SELECT title, count(1)\\nFROM people\\nWHERE last < 'N'\\nGROUP BY title\\nHAVING count(1)>1\\nORDER BY count(1) DESC\\n;\" : \"\",\n // spellcheck: true,\n width: \"40%\",\n rows: 9,\n submit: \"Run\"\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":512,"value":"dbRecords(sql_statement5)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":523,"value":"$50","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":595,"value":"md`## SQL Grammar Table (revisited)`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":544,"value":"md`This brings us to the end of the lesson and a slightly revised version of the SGT. It now includes a couple footnotes in “Reference to the Data” because as we saw in the last couple examples we’re not limited to just field names. We can also use aggregate functions, and in the wild world of SQL, many other possibilities exist. You can find more\n[SQL aggregates](https://sqlite.org/lang_aggfunc.html) and [functions](https://sqlite.org/lang_corefunc.html) to try that will work in the SQL editor below.`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":546,"value":"md`\nSQL Keyword | Reference to the Data | Description\n--- | --- | ---\n**SELECT** | field name(s)¹ separated by commas | *The data you want to see*\n** FROM ** | table name | *The table where the data lives*\nWHERE | condition is true | *Filters to rows where condition is true*\nGROUP BY | field(s)² to group on | *Summarize groups of data*\nHAVING | condition is true for the group | *Filters to groups where aggregate is true*\nORDER BY | field(s)² to sort on | *Sorts the data*\nLIMIT | a number (of rows to return) | *Limits to a certain number of rows*\n**;** | not applicable | *Ends the SQL statement*\n\n^{1 - *can include aggregate functions as well as many other SQL functions and constructs*}\n
\n^{2 - *almost always, using the position index in the SELECT list is easiest (e.g., ORDER BY 2 DESC)*}\n\n\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":591,"value":"md`*Time!* \n\nThat covers almost everything you need to know to work with a single table in SQL, and there's *a lot* you can do by combining what you already know in unique ways. \n\nThe next big hurdle is to learn how to combine data from multiple tables, using JOINs. I plan to cover this in a future tutorial in a similar format. So if this approach worked for you, let me know you're interested!\n\nThere are a couple more nice-to-haves below, but you've got the important stuff, so try it out for yourself. Remember to test yourself to help commit it to memory, and lean on the SGT when you can't remember. And for something a little more *fun*, check out [The SQL Song](https://www.youtube.com/watch?v=vcKeG3R7A3M), which could help you remember the order of SQL without the SGT through the power of music!\n\nThe editor below can run SQL against the \\`people\\` table. If you want to use your own data, check out [SQLite in Observable](https://beta.observablehq.com/@nolan-ga/sqlite-in-observable)`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":540,"value":"viewof sqlStatement6 = textarea({\n title: \"Have some fun with the people table!\", \n placeholder: \"Type a SQL statement and hit 'Run'\",\n value: \"SELECT *\\nFROM people\\nLIMIT 5;\",\n // spellcheck: true,\n width: \"40%\",\n rows: 9,\n submit: \"Run\"\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":542,"value":"dbRecords(sqlStatement6)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":795,"value":"md`___\n## Addendum`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":598,"value":"md`### Stuff you should know for the real world`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":600,"value":"md`There are two types of databases: file-based and server-based. A file-based database is housed completely within your computer in a single file. A server-based database is a piece of software that sits on another computer. To interact with this type of database, you use a client (a piece of software on your computer that couples with the piece of software on the other computer) to connect to the server and then send a SQL statement over the connection and receive data back. For obvious reasons, server-based databases are more difficult to set up, but are more commonly used in the real world because multiple users can easily connect to and use the same database. File-based databases are simple to set up and great for practicing SQL and for when you’re the only one who needs to access the data.`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":602,"value":"md`### You can live without this, but probably shouldn't`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":605,"value":"md`One thing I have neglected to mention thus far and that is typically the first query a beginner learns is what's called \"select star.\" The so called \"star\" in SQL is the asterisk character and is shorthand for returning the entire row (all the fields). It's primarily used in the \\`SELECT\\` clause, and many people say \\`count(*)\\` (count star) instead of \\`count(1)\\`, meaning count the number of rows. The following query returns the first 20 records containing every field in the people table.\n\n\\`SELECT *\\`
\n\\`FROM people\\`
\n\\`LIMIT 20;\\`
\n\nThe reason I avoided mentioning it until now is that the star character seems to cause a lot of confusion for beginners, and it's not even a good practice, though it's often the first query against a new table to avoid typing out all the field names. You will encounter it, and so when you do, now you know.`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":516,"value":"md`___ \n## Appendix`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":240,"value":"//d3.csv(\"https://data.sfgov.org/api/views/88g8-5mnd/rows.csv?accessType=DOWNLOAD\")","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":676,"value":"html``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":824,"value":"greyStrike = ''","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":256,"value":"salaries = [\n { title: \"Assistant Professor\", salary: ()=>randBetween(50000,60000) },\n { title: \"Associate Curator\", salary: ()=>randBetween(50000,60000) },\n { title: \"Curator\", salary: ()=>randBetween(50000,60000) },\n { title: \"Professor\", salary: ()=>randBetween(90000,170000) },\n { title: \"Research Professor\", salary: ()=>randBetween(100000,200000) },\n ]","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":248,"value":"randBetween = (min, max) => Math.floor(Math.random() * (max - min) + 1) + min","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":130,"value":"people = d3.csv(\"https://raw.githubusercontent.com/lawlesst/vivo-sample-data/master/data/csv/people.csv\").then(data=> {\n return data.map(d=> ({ ...d, salary: salaries.filter(f=> f.title === d.title.trim())[0].salary() }))\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":489,"value":"function dbRecords(sql, nRows=5) {\n const records = db.exec(sql)[0];\n \n if (records === undefined) { return md`*No records*`}\n \n const recordCount = records.values.length; \n \n if (records.values.length > nRows) {\n return html`${tableView(head(records, nRows))}
...showing first ${nRows} of ${recordCount} rows`\n } else {\n return tableView(head(records))\n }\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":195,"value":"function head(records, k=10) {\n if (records.values.length < k) { return records };\n let data = records\n data.values = data.values.filter((_, i) => i < k)\n return data;\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":52,"value":"records = {\n let resultSet = db.exec(sql_statement1)[0];\n let columns = resultSet.columns, \n rows=resultSet.values, \n n=columns.length,\n records = [];\n // return rows\n rows.map(row => { \n let obj={};\n for (let i=0; i`;\n \n const {tHead, tBodies} = table;\n while(tHead.firstChild) {\n tHead.removeChild(tHead.firstChild);\n }\n tHead.appendChild(html`${columns.map(h => html`${cellView(h)}`)}`);\n \n const tBody = tBodies[0];\n \n while(tBody.firstChild) {\n tBody.removeChild(tBody.firstChild);\n }\n \n values.forEach((row, i) => {\n tBody.appendChild(html`${row.map(cell => html`${cellView(cell)}`)}`);\n });\n \n return table;\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":125,"value":"function cellView(value) {\n // code from @rumkin/tableview -- had to rename inputs, so I just copy/pasted\n if (value instanceof HTMLElement) {\n return value;\n }\n else {\n return DOM.text(value);\n }\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":56,"value":"import {textarea, checkbox} from \"@jashkenas/inputs\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":127,"value":"d3 = require(\"d3\")","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":35,"value":"db = {\n \n let db = new sql.Database();\n const cols = Object.keys(people[0]).join(\", \");\n const placeholders = Object.keys(people[0]).fill('?').join(\", \");\n let table = \"people\"\n let sqlCreateTable = `CREATE TABLE ${table} (person_ID char,\n name char, \n first char, \n last char, \n middle char,\n email char,\n phone char,\n fax char,\n title char,\n salary number);`;\n db.run(sqlCreateTable) // create table\n \n let sqlInsertTemplate = `INSERT INTO ${table} (${cols}) VALUES (${placeholders});`;\n \n people.forEach(person => {\n db.run(sqlInsertTemplate, Object.values(person));\n })\n // db.run(sqlstr);\n return db\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":26,"value":"sql = require('sql.js@0.5.0/js/sql.js')","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"}],"files":"$W51","stdlib":"1"}} 24:null