29:null 2a:["$","title",null,{"children":"SQL Cell | Isobel Cobb | Observable"}] 2f:T65f,The SQL cell above already has a selected database. Here’s what you should notice: - The first ten rows of the results of the query are visible. - You can scroll down to see more rows. - You can scroll right to see more columns. - The small charts above each column are [summaries](#summaryCharts); we’ll discuss those below. - To view the schema, click the button to open the [schema browser](#schemaBrowser).30:T53d,The schema browser is located within the databases pane, which you can open by clicking the on the right edge of the window.31:[{"id":"b3711cfd9bdf50cbe4e74751164d28e907ce366cd4bf56a39a980a48fdc5f998c42a019716a8033e2b54defdd97e4a55ebe4f6464b4f0678ea0311532605a115","href":"https://static.observableusercontent.com/files/b3711cfd9bdf50cbe4e74751164d28e907ce366cd4bf56a39a980a48fdc5f998c42a019716a8033e2b54defdd97e4a55ebe4f6464b4f0678ea0311532605a115","name":"chinook.db","type":"application/x-sqlite3","lastModified":1674753224827.014,"size":884736},{"id":"7e651635bea00fc0ba5f61d7a1b73ca8bb2f531d43045a2a69af045657df3587e8fc877deee491237d627f5013454d5a066641b589535c06c91cf3cf280f9784","href":"https://static.observableusercontent.com/files/7e651635bea00fc0ba5f61d7a1b73ca8bb2f531d43045a2a69af045657df3587e8fc877deee491237d627f5013454d5a066641b589535c06c91cf3cf280f9784","name":"Screen Shot 2022-11-02 at 9.22.25 AM.png","type":"image/png","lastModified":1674753224827.014,"size":108150},{"id":"24009f535214a5c391e831e45b55d43d2f571c6f0ed4564d72a4f2320cb5ad320402a584c5b21a0fb6d3bbd0e2ef70ffe56433eae1680ba2ca74bdbd2d201513","href":"https://static.observableusercontent.com/files/24009f535214a5c391e831e45b55d43d2f571c6f0ed4564d72a4f2320cb5ad320402a584c5b21a0fb6d3bbd0e2ef70ffe56433eae1680ba2ca74bdbd2d201513","name":"SQLCell_SummaryChartHeaders_v2.png","type":"image/png","lastModified":1674753224827.014,"size":157681},{"id":"e751693e2a973f27bfb96a7c3638931c627f618109f3df67e8d23155d424761fa400ba298da659509a84f98af78ff91a306dff4fa69acf731e19b4f202267907","href":"https://static.observableusercontent.com/files/e751693e2a973f27bfb96a7c3638931c627f618109f3df67e8d23155d424761fa400ba298da659509a84f98af78ff91a306dff4fa69acf731e19b4f202267907","name":"SQLCell_SummaryTableInteractive_v2.png","type":"image/png","lastModified":1674753224827.014,"size":144368},{"id":"60d530760636715779aa952762aabdb95a1c0dd89788f09feb6efab3a42de2cdd04cbf020ad1a5c81ed8ddcf7a1f0b57cabe54c207927b6c9466641886bd16c0","href":"https://static.observableusercontent.com/files/60d530760636715779aa952762aabdb95a1c0dd89788f09feb6efab3a42de2cdd04cbf020ad1a5c81ed8ddcf7a1f0b57cabe54c207927b6c9466641886bd16c0","name":"SQLCell_Hist_Before.png","type":"image/png","lastModified":1674753224827.014,"size":11881},{"id":"ac26419262a6738cefd5d4a18f44de25dcaa9b9a513b3df174f48fd6e6913d8dea14fd845f05f9407be8c72cb8bd3f851b7eb17e45d01508c20686e2a4a06e78","href":"https://static.observableusercontent.com/files/ac26419262a6738cefd5d4a18f44de25dcaa9b9a513b3df174f48fd6e6913d8dea14fd845f05f9407be8c72cb8bd3f851b7eb17e45d01508c20686e2a4a06e78","name":"SQLCell_Hist_After.png","type":"image/png","lastModified":1674753224827.014,"size":14309},{"id":"bfbd8a8dc0872b291a68131b1d748d2e7cef023df12579750bc701ef51aede357dc9be28b7f5e678e1a133bd0ba6563289abcefd180b57a98b7643206c6ba80f","href":"https://static.observableusercontent.com/files/bfbd8a8dc0872b291a68131b1d748d2e7cef023df12579750bc701ef51aede357dc9be28b7f5e678e1a133bd0ba6563289abcefd180b57a98b7643206c6ba80f","name":"SQLCell_Message.png","type":"image/png","lastModified":1674753224827.014,"size":14122},{"id":"fff9e8c1501e6191fd0fab84704e6f24c137ef77e3c2d943e47af60e507570ebfdc4b3cf97fb865b721ed464260bbb04f62bcce079c30579b9c4c3e499f006d3","href":"https://static.observableusercontent.com/files/fff9e8c1501e6191fd0fab84704e6f24c137ef77e3c2d943e47af60e507570ebfdc4b3cf97fb865b721ed464260bbb04f62bcce079c30579b9c4c3e499f006d3","name":"rowHoverSummaryCharts.png","type":"image/png","lastModified":1674753224827.014,"size":157106}] 2d:{"id":"b2e4045b3ceefbbf","slug":null,"title":"SQL Cell","thumbnail":"7680c81c831b42f643e81863156f5db87e1be3f7201cf17511c94e74ce5083b3","access_level":"public","create_time":"$D2023-01-26T17:13:44.827Z","update_time":"$D2023-04-03T23:23:16.485Z","view_time":null,"first_public_time":"$D2023-01-26T17:13:44.827Z","fork":true,"stars":0,"starred":false,"owner":{"type":"team","id":"3e2509283042eb59","login":"icobb","name":"Isobel Cobb","avatar_url":"https://avatars-next.observableusercontent.com/7b05262d8809fccadb2a7d8df2214cd26ac3ad57393de009a985fb8257469bfe","tier":"starter_2024"},"version":603,"publish_version":null,"role":null,"authored":false,"authors":[{"type":"individual","id":"2d6b5bbb66e9af61","login":"icobb","name":"Isobel Cobb","avatar_url":"https://avatars-next.observableusercontent.com/7b05262d8809fccadb2a7d8df2214cd26ac3ad57393de009a985fb8257469bfe","tier":null}],"members":[{"type":"individual","id":"2d6b5bbb66e9af61","login":"icobb","name":"Isobel Cobb","avatar_url":"https://avatars-next.observableusercontent.com/7b05262d8809fccadb2a7d8df2214cd26ac3ad57393de009a985fb8257469bfe","tier":null,"role":"editor"}],"comments":[],"custom_thumbnail":false,"trashed":false,"lapsed":null,"subscribed":null,"forks":0,"collection":null,"type":"notebook","body":{"title":"SQL Cell","cells":[{"id":402,"value":"# SQL Cell\n\nIn addition to JavaScript, Markdown, and other [types of cells](/@observablehq/cell-modes), Observable’s SQL cell type enables easy exploration of databases. You can connect a SQL cell to a database, view the schema of the database, write a SQL query directly in the cell, and view the results of that query as a table.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":17,"value":"To use a SQL cell, you first need a database, which can either be an [external database](/@observablehq/databases), a [SQLite file](/@observablehq/sqlite), or a [custom database client](/@observablehq/database-client-specification). External databases are only allowed in private notebooks, but SQLite files and custom database clients can work in both public and private notebooks. ","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":403,"value":"Once you have your database, create a SQL cell by inserting a new cell and selecting **Database query** in the cell menu. You’ll then see a selector in the cell that allows you to select a database. You can select either an external database that you’ve set up on your workspace, a SQLite file attachment, or a DatabaseClient you’ve defined in another JavaScript cell.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":404,"value":"With your database selected, you’re ready to enter a query!","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":405,"value":"In this notebook, we’ll show you what is possible with SQL cells using an example SQLite database client. This database uses the [Chinook sample database](https://github.com/lerocha/chinook-database), which represents a set of music-related data.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":24,"value":"SELECT * FROM customers","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"","database":"chinook.db"},{"id":593,"value":"SELECT * FROM artists\n INNER JOIN albums\n ON artists.artistid = albums.artistid;","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"","database":"chinook.db"},{"id":378,"value":"$2f","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":376,"value":"## Schema browser\n\nThe schema browser shows the structure of a database. It helps you compose a SQL query by showing which tables are available, which columns each table has, and the associated column types.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"schemaBrowser","database":"$undefined"},{"id":499,"value":"$30","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":375,"value":"

\n \n

The schema browser in the databases pane.

\n","pinned":false,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":457,"value":"To view the tables in a particular database, click on the database to expand it. You can click on the database again to collapse it. Likewise, to show or hide the columns in a particular table, click on the table to expand or collapse it. The names of the columns are shown on the left; the types are shown on the right in gray.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":467,"value":"For convenience, you can click on the button to quickly insert a SQL cell already connected to the given database.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":370,"value":"## Example SQL queries\n\nTo query a database, write SQL into the editor and then click the play button to run the query. You can also use the ${buttons(\"Shift-Enter\")} keyboard shortcut to commit and run your query.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":508,"value":"SELECT * FROM tracks","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"","database":"chinook.db"},{"id":534,"value":"To reference the results of a query from another cell, click on the cell name input in the toolbar. The cell above is unnamed, and so shows `cell 508`; the cell below has been named `longTracks`.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":530,"value":"SELECT *\n FROM tracks\n ORDER BY Milliseconds DESC\n LIMIT 10","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"longTracks","database":"chinook.db"},{"id":539,"value":"The value of a SQL cell is an array of objects representing the query results.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":537,"value":"longTracks","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":548,"value":"Most database clients also expose some schema information alongside query results. For example, the SQLite databases expose a _columns_ property including the names of the selected columns in the order they were selected.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":545,"value":"longTracks.columns","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":157,"value":"We can also make a query dynamic by adding parameters. SQL cells function exactly the same way as the [DatabaseClient `sql` tagged template literal](/@observablehq/databases#sql): we can interpolate parameters into the query by wrapping them in `$\\{…}`.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":558,"value":"For example, here we use a [text input](/@observablehq/input-text) to query a table interactively.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":90,"value":"viewof name = Inputs.text({label: \"Name\", placeholder: \"Search tracks\", value: \"jimmy\"})","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":96,"value":"likeName = `%${name}%`","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":92,"value":"SELECT * FROM tracks WHERE Name LIKE ${likeName} OR Composer LIKE ${likeName};","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"filteredQuery","database":"chinook.db"},{"id":309,"value":"## Summary charts\n\nThese small graphics at the top of each column provide a quick summary of key data characteristics for each column below.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"summaryCharts","database":"$undefined"},{"id":308,"value":"

\n \n

Each small chart provides a summary of that column’s data.

\n","pinned":false,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":307,"value":"Summary charts are interactive: when you hover over a summary, you can get more detailed information. By moving your mouse along the displayed values, you can see individual values for each histogram or bar chart segment. Compare the following figure with the previous one:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":306,"value":"

\n \n

Highlighting an individual value in a summary chart causes the accompanying text to reflect that value’s data.

\n","pinned":false,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":305,"value":"By hovering over the dark bar in the highlighted chart above, we now see additional information:\n- The bar represents 6 rows of the total, or 6.7%.\n- The composer selected is “Jimmy Page”.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":304,"value":"Here is another example, this time a histogram. Before hovering over an individual vertical bar:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":303,"value":"html`\n

\n \n

A histogram summary chart before any individual vertical bar is highlighted.

\n\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":302,"value":"Here is the same histogram, after the pointer is hovered over one of the vertical bars:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":301,"value":"html`\n

\n \n

A histogram summary chart after one vertical bar is highlighted.

\n\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":300,"value":"We now see additional information:\n- The bar represents 28 rows of the total, or 31%.\n- The values in that column range from 10M bytes to 20M bytes.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":299,"value":"If the column contains values that are best represented by text only, the summary charts display a descriptive message:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":298,"value":"html`\n

\n \n

Summary charts display text when appropriate.

\n\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":297,"value":"The types of columns available in summary charts are:\n- *Continuous columns* display a histogram showing the distribution of values.\n- *Categorical columns* display a stacked bar chart of the frequency of each value.\n\n","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":334,"value":"Special cases use text to clarify uniform or unique values:\n- All values are the same (for a continuous or categorical column).\n- All values are unique (for a categorical column).","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":333,"value":"In addition to interacting with the columns in the summary charts, you can interact with the rows of data. Hovering over a row of data causes individual marks in the summary chart to highlight. ","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":332,"value":"In the following example, the pointer is hovered over the row with “Dazed and Confused”. This results in the vertical bars for “Dazed and Confused” and “Jimmy Page” (the composer) to be highlighted. (Values in the other columns are also highlighted, but are harder to see in the example.)","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":331,"value":"

\n \n

Hovering over a row of data highlights individual marks in the summary charts.

\n","pinned":false,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":258,"value":"## Custom database clients\n\nWe can also use a custom database client as a data source for a SQL cell. In order for a database client to be recognized as a valid data source, it must satisfy the requirements outlined in [DatabaseClient Specification](https://observablehq.com/@observablehq/database-client-specification).","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"customDB","database":"$undefined"},{"id":581,"value":"Here’s an oversimplified example where any query returns the same static value. You can extend this example to return data from any source. See the [DuckDB Client for Observable](/@cmudig/duckdb-client) for a fully-fledged example of this.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":262,"value":"class CustomDatabase {\n async sql(strings, ...args) {\n return [{column1: \"value1\", column2: \"value2\", column3: \"value3\"}];\n }\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":266,"value":"custom = new CustomDatabase()","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":269,"value":"select *","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"","database":"var:custom"},{"id":521,"value":"---\n\n## Appendix","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":523,"value":"import {buttons} from \"@observablehq/keys\"","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"}],"files":"$W31","stdlib":"1","titling":"auto"}} 2e:"$2d:body"