31:T525,# ❷ Intro To SQL Syntax *ClickHouse Tutorial - 2* ${await FileAttachment("clickhouse-mark-4238843745.png").image()} This tutorial aims to introduce beginners to the topic of relational and non-relational databases and SQL in the context of data analytics using [ClickHouse](https://clickhouse.com/), a fast open-source OLAP DBMS. This tutorial uses an [Observable Notebook](https://observablehq.com/@observablehq/observable-overview-video?collection=@observablehq/getting-started) to give you an interactive experience. Note that ClickHouse is usually used via other clients such as `clickhouse-client`, [DataGrip](https://www.jetbrains.com/datagrip/), and [DBeaver](https://dbeaver.io/). If you accidentally change the cells of this notebook, you can undo the changes by pressing the "Discard" button on the top bar or by refreshing the page. ## SQL Cells Observable Notebook's SQL Client allows you to write and execute your own queries from your own web browser. Feel free to practice your SQL skills as you progress through this tutorial. If you would like to save your changes, feel free to create an ObservableHQ account and fork this notebook. > **Note**: Run cells by pressing SHIFT + ENTER or the blue triangle on the top right of the textbox Cells that allow you to run SQL look like the following:32:T411,test("Select all columns from the `menu_item_denorm` table", () => { checkExerciseAttempted(s1s2e1); expect(_.keys(s1s2e1[0])).to.deep.equal( [ "price", "high_price", "created_at", "updated_at", "xpos", "ypos", "dish_id", "dish_name", "dish_description", "dish_menus_appeared", "dish_times_appeared", "dish_first_appeared", "dish_last_appeared", "dish_lowest_price", "dish_highest_price", "menu_id", "menu_name", "menu_sponsor", "menu_event", "menu_venue", "menu_place", "menu_physical_description", "menu_occasion", "menu_notes", "menu_call_number", "menu_keywords", "menu_language", "menu_date", "menu_location", "menu_location_type", "menu_currency", "menu_currency_symbol", "menu_status", "menu_page_count", "menu_dish_count" ], "Missing some columns, incorrect column order, or incorrect column names." ); })33:T41f,--- #### Combine and subtract result sets using `UNION`, `INTERSECT`, and `EXCEPT` ${await FileAttachment("截屏2023-02-03 下午6.12.10.png").image()} [`UNION `](https://clickhouse.com/docs/en/sql-reference/statements/select/union) combines the results of two queries into one table by appending rows rows. `UNION DISTINCT` keeps only unique rows when merging result sets. `UNION ALL` keeps all result rows including duplicates. [`INTERSECT`](https://clickhouse.com/docs/en/sql-reference/statements/select/intersect) keeps the rows that both result sets have in common. `INTERSECT DISTINCT` eliminates duplicate rows. [`EXCEPT`](https://clickhouse.com/docs/en/sql-reference/statements/select/except) returns rows that exist in first set but not the second set > These functions are good for merging tables on the row axis and making the table longer. We'll get to joining tables on the columns axis and making the table wider later. Below we get the 5 most expensive items on the menu and the 5 least expensive items on the menu34:T95a,--- ### Data Types Each column should contain one particular kind of value (ie. a number, a sequence of letters, true or false, a sequence of values, a date, etc). This structure to our schema helps ensure data integrity and makes it easier to read and perform operations on our data. In a column-oriented database, this also allows for efficient data storage via encodings and compression ClickHouse provides many data types to work with but here are the most commonly used. | Category | ClickHouse Data Types | Description | Examples | --- | --- | --- | --- | | Integers | `Int8`, `Int16`, `Int32`, `Int64`, ... | Integers, ${tex`\mathbb{Z}`} | -20, 0, 5 | | Unsigned Integers | `UInt8`, `UInt16`, `UInt32`, `UInt64`, ... | Positive integers and zero (aka the Whole Numbers, ${tex`\mathbb{W}`}) | 0, 5, 10 | | Floating Point Numbers | `Float32`, `Float64` | Numbers including decimal point precision. (aka the Real Numbers, ${tex`\mathbb{R}`}) | 1.0, -2.2 | | Booleans | `Boolean` | True or False | `true`, `false` | | Dates | `Date`, `Date32` | Dates | '2023-02-02' | | DateTimes | `DateTime`, `DateTime64` | The date and time stored together | `'2023-02-03 23:48:25'` | | Strings | `String`, `FixedString(N)` | A sequence of characters | `'Hello World'` | | Arrays | `Array(T)` | A series of values | `[1, 2, 3]`, `['a', 'b', 'c', 'd']` | Note that the number following some of the ClickHouse Data Types' names indicates the number of bits used to store each value. Fewer bits means smaller memory footprint, but also constrains the maximum value that can be stored. For example, a `Date` is stored in 2 bytes or 16 bits, so it can only represent dates from `1970-01-01` until `2149-06-06`. A `Date32` is stored as a signed 32-bit integer and can represent dates from `1900-01-01` until `2299-12-31`. Be careful storing dates outside these ranges as date-typed columns! ClickHouse may incorrectly store out-of-range values. #### Read More - https://clickhouse.com/docs/en/sql-reference/data-types/ --- #### Get a quick overview of a databases, tables, columns, and types with `DESCRIBE TABLE`, `SHOW TABLES`, `SHOW DATABASES` Syntax: `DESCRIBE TABLE ` Shows the name of columns of the table, the type contained in each column, and other info. This is very useful when I forget the exact column_names, information, or formatting that a table I'm working with contains. 35:T52e,___ ### Operators & Functions In addition to retrieving columns, ClickHouse can perform operations on the data in the columns using various operators and functions to act on particular data types. Operators are keywords that act on expressions that are left or right of them. For example the `AND` operator (ie `true AND false` evaluates to `false`). Functions operate on zero or more parameters. For example `lower` is a function that converts `String`s to lowercase. `lower('FOO')` evaluates to `'foo'`. Notice that the parentheses that come after the name of the function contain a comma separated list of the parameters of the function. --- For this section we'll be using the `recipes` table as well as the `numbers(n)` table function. Columns of `recipes` table ${await client.describe('recipes')} Learn about the `numbers(N)` table function [here](https://clickhouse.com/docs/en/sql-reference/table-functions/numbers). #### Read More - [ClickHouse Documentation](https://clickhouse.com/docs/en/sql-reference/functions/). --- #### Operators & Functions for Numbers We had mentioned the numerical comparison operators (`<`, `<=`, `=`, `>=`, `>`, `!=`) earlier. Arithmetic - `plus(a,b)`, `a + b` - `minus(a,b)`, `a - b` - `multiply(a,b)`, `a * b` - `divide(a,b)`, `a / b` - `modulo(a,b)`, `a % b` - `abs(a)` 36:[{"id":"5acf54989c2155c1ad0c6dcb7433fc703e3464572d573c9873dc6d38863339d5e3178453f2ce927068ebe471a97c8e28cfdb1f927513263259c88bdcbfb653ec","href":"https://static.observableusercontent.com/files/5acf54989c2155c1ad0c6dcb7433fc703e3464572d573c9873dc6d38863339d5e3178453f2ce927068ebe471a97c8e28cfdb1f927513263259c88bdcbfb653ec","name":"clickhouse-mark-4238843745.png","type":"image/png","lastModified":1677592403668.465,"size":7715},{"id":"291ffdaaef1366e902439d072cbfd6c80c09852c66799b541a6daa1f69663fab2ba05f40b560f6c45ab174e3fd77e00f1bb2cdeacdad8ae3ae1ee69a1b26df5c","href":"https://static.observableusercontent.com/files/291ffdaaef1366e902439d072cbfd6c80c09852c66799b541a6daa1f69663fab2ba05f40b560f6c45ab174e3fd77e00f1bb2cdeacdad8ae3ae1ee69a1b26df5c","name":"answers1@1.db","type":"application/x-sqlite3","lastModified":1677592403668.465,"size":45056},{"id":"1e70693fe6f506afc9e19ec21d3d0eb06f64a06c508706ac571dbb56fd1c0aee4566920db3a3146e98efbea75c8ee25da135e2a140d71eeec78d0eedced94d81","href":"https://static.observableusercontent.com/files/1e70693fe6f506afc9e19ec21d3d0eb06f64a06c508706ac571dbb56fd1c0aee4566920db3a3146e98efbea75c8ee25da135e2a140d71eeec78d0eedced94d81","name":"截屏2023-02-03 下午6.12.10.png","type":"image/png","lastModified":1677592403668.465,"size":4074367}] 2f:{"title":"❷ Intro To SQL Syntax","cells":[{"id":0,"value":"$31","pinned":false,"hidden":false,"mode":"md"},{"id":341,"value":"SELECT * FROM stock LIMIT 10","pinned":true,"hidden":false,"mode":"sql","output":"query1","database":"var:client"},{"id":369,"value":"## Exercises\n\nSome cells can check your work and make sure your practice SQL queries are the expected results.\n\nTry changing the query above to `SELECT * FROM stock` (and press CTRL/CMD + ENTER) and the text below will turn red since we are no longer getting only the first 10 rows.","pinned":false,"hidden":false,"mode":"md"},{"id":349,"value":"test(\"Get the first 10 rows of the `stock` table\", () => {\n expect(query1).to.have.lengthOf(10, \"Expected 10 rows of results.\");\n const columns = _.keys(query1[0]);\n expect(columns).to.deep.equal(\n [\"symbol\", \"date\", \"price\", \"volume\", \"open\", \"low\", \"high\"],\n \"Not all the expected columns are present.\"\n );\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":32,"value":"toc({\n headers: \"h3\",\n skip: [\n \"Imports\",\n \"Topics\",\n \"SQL Cells\",\n \"Exercises\",\n \"Other great references for ClickHouse\",\n \"Appendix\",\n \"What we will be covering\",\n \"What won't be covered\"\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":50,"value":"## Introduction to SQL Syntax\n\nFor these examples we will use the `trips` table as well as the `menu_item_denorm` table.\n\nThe `trips` table's columns are the following:\n\n${await client.describe('trips')}\n
\nThe `menu_item_denorm` table's columns are as follows:\n\n${await client.describe('menu_item_denorm')}","pinned":false,"hidden":false,"mode":"md"},{"id":315,"value":"### `SELECT ... FROM ...`\n\nTo retrieve columns from a particular table use `SELECT FROM

`\n\nIf there are many columns, separate the column names with commas.\n\nExample:","pinned":false,"hidden":false,"mode":"md"},{"id":324,"value":"SELECT trip_id, pickup_datetime, dropoff_datetime FROM trips","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":388,"value":"#### Exercises","pinned":false,"hidden":false,"mode":"md"},{"id":400,"value":"test(\"Retrieve all rows of the `pickup_datetime` column\", () => {\n checkExerciseAttempted(s1s1e1);\n expect(_.values(s1s1e1[0])).to.have.lengthOf(\n 1,\n \"Expected one column of results\"\n );\n expect(_.keys(s1s1e1[0])).to.deep.equal(\n [\"pickup_datetime\"],\n \"Expected a `pickup_datetime` column\"\n );\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":394,"value":"SELECT 'Hello World'; -- REPLACE THIS WITH YOUR QUERY","pinned":true,"hidden":false,"mode":"sql","output":"s1s1e1","database":"var:client"},{"id":409,"value":"test(\"Retrieve all rows of the `trip_id`, `trip_distance`, `fare_amount` columns\", () => {\n checkExerciseAttempted(s1s1e2);\n expect(_.keys(s1s1e2[0])).to.deep.equal(\n [\"trip_id\", \"trip_distance\", \"fare_amount\"],\n \"Expected columns named `trip_id`, `trip_distance`, `fare_amount`\"\n );\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":411,"value":"SELECT 'Hello World'; -- REPLACE THIS WITH YOUR QUERY","pinned":true,"hidden":false,"mode":"sql","output":"s1s1e2","database":"var:client"},{"id":429,"value":"---\n#### Select all columns using `*`\n\nTo select all columns of a table, use `*` instead of the list of column names.\n\nThis is generally not recommended because you should only retrieve the columns that you need. Usually you only need a subset of all the columns.","pinned":false,"hidden":false,"mode":"md"},{"id":435,"value":"SELECT * FROM trips","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":601,"value":"To select all columns except one particular column, you can use\n> `EXCEPT ()` ","pinned":false,"hidden":false,"mode":"md"},{"id":607,"value":"SELECT * EXCEPT (trip_id, vendor_id) FROM trips","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":439,"value":"#### Exercise","pinned":false,"hidden":false,"mode":"md"},{"id":442,"value":"$32","pinned":false,"hidden":false,"mode":"ojs"},{"id":444,"value":"SELECT 'Hello World'; -- REPLACE THIS WITH YOUR QUERY","pinned":true,"hidden":false,"mode":"sql","output":"s1s2e1","database":"var:client"},{"id":425,"value":"---\n#### Filter your results with `WHERE`\n\nAdd a `WHERE ` to specify which rows you want. \n\nCommon Logical Operators: `AND`, `OR`, `NOT`\n\nCommon Comparison Operators: `<`, `<=`, `=`, `>=`, `>`, `!=` (not equals), `LIKE`, `BETWEEN`\n\nBelow, we keep only the trips where `cab_type` is yellow and the pickup occurred before January 1st, 2015.\nNotice that the conditions are joined with an `AND`.","pinned":false,"hidden":false,"mode":"md"},{"id":427,"value":"SELECT \n cab_type, pickup_datetime, trip_distance, fare_amount \nFROM trips WHERE \n cab_type = 'yellow' AND \n pickup_datetime < '2015-01-01'","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":525,"value":"Below, we get rows with a `fare_amount` between 2 and 9 and a `dropoff_ntacode` with 'N' as the second letter","pinned":false,"hidden":false,"mode":"md"},{"id":527,"value":"SELECT \n fare_amount, dropoff_ntacode, cab_type, trip_distance\nFROM trips\nWHERE \n fare_amount BETWEEN 2 AND 9 AND\n dropoff_ntacode LIKE '_N%'","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":465,"value":"#### Read More\n- [ClickHouse Comparison Operators](https://clickhouse.com/docs/en/sql-reference/operators/#comparison-operators)\n- [ClickHouse Logical Operators](https://clickhouse.com/docs/en/sql-reference/operators/#logical-and-operator)","pinned":false,"hidden":false,"mode":"md"},{"id":483,"value":"#### Exercises","pinned":false,"hidden":false,"mode":"md"},{"id":485,"value":"test(\"Retrieve the `mta_tax` and `passenger_count` columns for rows that have non-zero `mta_tax`\", () => {\n checkExerciseAttempted(s1s3e1);\n expect(_.keys(s1s3e1[0])).to.deep.equal(\n [\"mta_tax\", \"passenger_count\"],\n \"Expected columns named `mta_tax` and `passenger_count`\"\n );\n expect(_.every(_.map(s1s3e1, \"mta_tax\"), (x) => x > 0)).to.equal(\n true,\n \"Seems like not ever `mta_tax` value is greater than zero...\"\n );\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":473,"value":"SELECT 'Hello World'; -- REPLACE THIS WITH YOUR QUERY","pinned":true,"hidden":false,"mode":"sql","output":"s1s3e1","database":"var:client"},{"id":535,"value":"test(\"Get `trip_id`, `payment_type_` and `dropoff_ntacode` columns for rows with `payment_type_` of CRE and `dropoff_ntacode` that ends with 2 \", () => {\n checkExerciseAttempted(s1s3e2);\n expect(_.keys(s1s3e2[0])).to.deep.equal(\n [\"trip_id\", \"payment_type_\", \"dropoff_ntacode\"],\n \"Expected columns `trip_id`, `payment_type_`, and `dropoff_ntacode`.\"\n );\n expect(\n _.every(s1s3e2, (r) => r[\"dropoff_ntacode\"].slice(-1) === \"2\")\n ).to.equal(true, \"Expected every `dropoff_ntacode` to end with 2\");\n expect(_.every(s1s3e2, (r) => r[\"payment_type_\"] === \"CRE\")).to.equal(\n true,\n 'Expected to only find values equalling \"CRE\" in the `payment_type_` column'\n );\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":511,"value":"SELECT 'Hello World'; -- REPLACE THIS WITH YOUR QUERY","pinned":true,"hidden":false,"mode":"sql","output":"s1s3e2","database":"var:client"},{"id":563,"value":"---\n#### Give a name to result columns with `AS`\n\nSyntax: `AS `\n\nBelow, we alias the `pickup_longitude` column to `longitude` only within our result set, without modifying the underlying table.","pinned":false,"hidden":false,"mode":"md"},{"id":565,"value":"SELECT trip_id AS id, vendor_id AS vendor FROM trips;","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":572,"value":"---\n#### Find unique values with `DISTINCT`\n\nAdd `DISTINCT` before the column name to retrieve the distinct values of that column. \n\nBelow, we retrieve all types of cab","pinned":false,"hidden":false,"mode":"md"},{"id":574,"value":"SELECT DISTINCT cab_type FROM trips;","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":579,"value":"#### Read More\n- https://clickhouse.com/docs/en/sql-reference/statements/select/distinct/","pinned":false,"hidden":false,"mode":"md"},{"id":610,"value":"#### Exercise","pinned":false,"hidden":false,"mode":"md"},{"id":591,"value":"test(\"List the different payment types found in the `payment_type_` column\", () => {\n checkExerciseAttempted(s1s5e1);\n expect(_.keys(s1s5e1[0])).to.deep.equal(\n [\"payment_type_\"],\n \"Expected a column named `payment_type_`\"\n );\n expect(\n _.map(s1s5e1, \"payment_type_\"),\n \"The values in the list of distinct payment types are incorrect\"\n ).to.include(\"NOC\", \"CRE\", \"CSH\", \"UNK\", \"DIS\");\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":588,"value":"SELECT 'Hello World'; -- REPLACE THIS WITH YOUR QUERY","pinned":true,"hidden":false,"mode":"sql","output":"s1s5e1","database":"var:client"},{"id":599,"value":"---\n#### Sort the rows with `ORDER BY`\n\nSyntax: `ORDER BY `\n\nHere we sort the `menu_item_denorm` table first by highest price, and for items of the same price, we sort by dish name alphabetically.\n","pinned":false,"hidden":false,"mode":"md"},{"id":613,"value":"SELECT dish_name, price FROM menu_item_denorm ORDER BY price DESC, dish_name ASC","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":624,"value":"#### Read More\n- https://clickhouse.com/docs/en/sql-reference/statements/select/order-by","pinned":false,"hidden":false,"mode":"md"},{"id":628,"value":"#### Exercise","pinned":false,"hidden":false,"mode":"md"},{"id":640,"value":"test(\"Get the created_at and dish_name columns with rows sorted such that the more recently created dishes are on top\", () => {\n checkExerciseAttempted(s1s6e1);\n const createdAt = _.map(s1s6e1, \"created_at\");\n expect(\n _.every(createdAt, (v, i, arr) =>\n i + 1 === arr.length ? true : v >= arr[i + 1]\n )\n ).to.equal(\n true,\n \"Expected `createdAt` column to be sorted with more recent dishes on top\"\n );\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":630,"value":"SELECT 'Hello World'; -- REPLACE THIS WITH YOUR QUERY","pinned":true,"hidden":false,"mode":"sql","output":"s1s6e1","database":"var:client"},{"id":665,"value":"---\n#### Truncate or paginate your results with `LIMIT` and `OFFSET`\n\nSyntax: `LIMIT OFFSET `\n\nWhen we don't need all potential result rows, it's more efficient to only get a subset of the rows. Subsequent queries can target rows that were previously neglected.\n\nHere we return only 5 rows from the trips table. This is particularly useful to get an idea of the formatting of a table without having to load all the data into memory at once.","pinned":false,"hidden":false,"mode":"md"},{"id":670,"value":"SELECT * FROM trips LIMIT 5","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":673,"value":"Using `OFFSET` we can get the next 3 rows. In other words skip the first five rows and get the 3 rows immediately after.","pinned":false,"hidden":false,"mode":"md"},{"id":676,"value":"SELECT * FROM trips LIMIT 5 OFFSET 3","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":679,"value":"#### Read More\n- https://clickhouse.com/docs/en/sql-reference/statements/select/limit\n- https://clickhouse.com/docs/en/sql-reference/statements/select/offset","pinned":false,"hidden":false,"mode":"md"},{"id":681,"value":"#### Exercises","pinned":false,"hidden":false,"mode":"md"},{"id":690,"value":"test(\"Get three trip_ids\", () => {\n checkExerciseAttempted(s1s7e1);\n expect(s1s7e1).to.have.lengthOf(3, \"Expected only three rows\");\n expect(_.keys(s1s7e1[0])).to.deep.equal(\n [\"trip_id\"],\n \"Expected only the `trip_id` column\"\n );\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":683,"value":"SELECT 'Hello World'; -- REPLACE THIS WITH YOUR QUERY","pinned":true,"hidden":false,"mode":"sql","output":"s1s7e1","database":"var:client"},{"id":719,"value":"$33","pinned":false,"hidden":false,"mode":"md"},{"id":752,"value":"SELECT \n dish_name, \n price \nFROM menu_item_denorm \nORDER BY price DESC \nLIMIT 5\n\nUNION ALL\n\nSELECT\n dish_name,\n price\nFROM menu_item_denorm\nORDER BY price ASC\nLIMIT 5","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":745,"value":"#### Read More\n- https://clickhouse.com/docs/en/sql-reference/statements/select/union\n- https://clickhouse.com/docs/en/sql-reference/statements/select/intersect\n- https://clickhouse.com/docs/en/sql-reference/statements/select/except","pinned":false,"hidden":false,"mode":"md"},{"id":757,"value":"$34","pinned":false,"hidden":false,"mode":"md"},{"id":955,"value":"DESCRIBE TABLE recipes","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":1095,"value":"`SHOW TABLES` and `SHOW DATABASES` do exactly what they say","pinned":false,"hidden":false,"mode":"md"},{"id":1097,"value":"SHOW TABLES","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":1100,"value":"SHOW DATABASES","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":800,"value":"$35","pinned":false,"hidden":false,"mode":"md"},{"id":818,"value":"SELECT \n rand(0) AS a, \n rand(1) AS b,\n a + b AS added,\n a - b AS subtracted, \n a * b AS multiplied,\n a / b AS divided,\n a % b AS moded,\n abs(subtracted)\nFROM numbers(10);","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":885,"value":"SELECT \n rand(0) AS a, \n rand(1) AS b,\n a > b AS gt,\n a < b AS lt, \n a = b AS eq,\n a != b AS ne\nFROM numbers(10);","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":808,"value":"#### Read More\n- https://clickhouse.com/docs/en/sql-reference/functions/arithmetic-functions\n- https://clickhouse.com/docs/en/sql-reference/functions/random-functions\n- https://clickhouse.com/docs/en/sql-reference/functions/comparison-functions","pinned":false,"hidden":false,"mode":"md"},{"id":840,"value":"#### Exercise","pinned":false,"hidden":false,"mode":"md"},{"id":849,"value":"test(\"Find the range of dish prices (high-low) using `dish_highest_price` and `dish_lowest_price` columns of `menu_item_denorm` table. Remove rows where those columns are zero. Get the first thousand results sorted by menu_id and dish_id. Name the result column `range`. Include the `dish_name` column associated with each of those ranges\", async () => {\n checkExerciseAttempted(s3s1e1);\n const answer = await answers1.query(\"SELECT dish_name, range FROM s3s1e1\");\n expect(_.keys(s3s1e1[0])).to.deep.equal(\n [\"dish_name\", \"range\"],\n \"Expected columns named `dish_name` and `range` in that order.\"\n );\n expect(s3s1e1).to.deep.equal(\n answer,\n \"The result of your query does not match that of the correct answer.\"\n );\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":836,"value":"SELECT 'Hello World'; -- REPLACE THIS WITH YOUR QUERY","pinned":true,"hidden":false,"mode":"sql","output":"s3s1e1","database":"var:client"},{"id":892,"value":"---\n#### Operators and Functions For Strings\n\nOperators\n- `concat(a,b)`, `a || b` -- combine two strings\n- `a LIKE b` -- TRUE if String matches a pattern, b.\n- `a = b` -- TRUE if String a is the same as String b\n\n[(There are more sophisticated search functions if necessary)](https://clickhouse.com/docs/en/sql-reference/functions/string-search-functions) \n\nThere are a multitude of useful functions for working with strings. I'll cover a few I find very useful. \n\n- `empty(x)` - TRUE if String is empty. Otherwise FALSE.\n- `length(x)` - Length of String\n- `trim(x)` - Remove leading and trailing blank space.\n- `substring(s, offset, length)` - Extract part of a string\n","pinned":false,"hidden":false,"mode":"md"},{"id":898,"value":"SELECT \n title, \n source,\n length(title),\n concat(source, '-', trim(title)),\n lower(title) LIKE '%chocolate%'\nFROM recipes","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":915,"value":"#### Read More\n- https://clickhouse.com/docs/en/sql-reference/functions/string-functions\n- https://clickhouse.com/docs/en/sql-reference/functions/string-search-functions\n- https://clickhouse.com/docs/en/sql-reference/functions/string-replace-functions","pinned":false,"hidden":false,"mode":"md"},{"id":922,"value":"#### Exercise","pinned":false,"hidden":false,"mode":"md"},{"id":924,"value":"test(\"Get all values from the `link` column of the `recipes` table and add 'https://' to the beginning of each link. Name this the `answer` column.\", () => {\n checkExerciseAttempted(s3s2e1);\n expect(\n _.every(_.map(s3s2e1, \"answer\"), (str) => str.substr(0, 8) === \"https://\")\n ).to.equal(\n true,\n 'Expected each row in the `answer` column to start with \"https://\"'\n );\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":927,"value":"SELECT 'Hello World'; -- REPLACE THIS WITH YOUR QUERY","pinned":true,"hidden":false,"mode":"sql","output":"s3s2e1","database":"var:client"},{"id":963,"value":"___\n#### Date and Time functions\n\n- `now()` -- Get the current date and time\n- `today()` -- Get the current date\n- `yesterday()` -- Get yesterday's date\n- `dateDiff(unit, start, end)` -- subtract datetimes in terms of a unit (second, minute, hour, day, week, month, quarter, year)\n- `addYears`, `addMonths`, `addWeeks`, `addDays`, `addHours`, `addMinutes`, `addSeconds`, `addQuarters`\n- `subtractYears`, `subtractMonths`, `subtractWeeks`, `subtractDays`, `subtractHours`, `subtractMinutes`, `subtractSeconds`, `subtractQuarters`\n- `formatDateTime(datetime, format[, timezone])` - Return a string containing the date time formatted in a way you specify","pinned":false,"hidden":false,"mode":"md"},{"id":970,"value":"SELECT\n now(),\n today(),\n dateDiff('hour', yesterday(), today()) AS hrs_btn_td_ytd,\n today() + INTERVAL 1 day AS tomorrow,\n today() - INTERVAL 1 year AS yester_year,\n formatDateTime(now(), '%D at %I:%M:%S %p', 'EST')","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":967,"value":"#### Read More\n- https://clickhouse.com/docs/en/sql-reference/functions/date-time-functions","pinned":false,"hidden":false,"mode":"md"},{"id":1777,"value":"#### Functions for working with Arrays\n\nArrays are a list of some kind of value for example: \n- a list of `String` like `['a', 'b', 'c']`\n- a list of `UInt8` like `[1,2,3]`\n- a list of `Array(UInt8)` like `[[1], [2], [3]]`\n\nClickHouse provides many functions to make it easier to work with Arrays \nHere are some examples.\n- `empty(arr)` - Check if Array is empty\n- `has(arr, elem)` - Check whether Array has a value\n- `length(arr)` - Get length of array\n- `indexOf(arr, elem)` - Get the position of a value in the list\n- `arrayDistinct(arr)` - Get only the unique items in the list","pinned":false,"hidden":false,"mode":"md"},{"id":1779,"value":"SELECT\n number,\n [1,5,2,3,2,4,5,1] AS arr,\n empty(arr),\n length(arr),\n has(arr, number),\n indexOf(arr, number),\n arrayDistinct(arr)\nFROM numbers(10) -- numbers 0 thru 9","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":1785,"value":"#### Exercises\n\nHere is the description for the recipes table again. It happens to use some `Array(String)`\n\n${await client.describe('recipes')}","pinned":false,"hidden":false,"mode":"md"},{"id":1801,"value":"test(\"Get the titles (`recipe.title`) of all recipes for which '1 coke' is one of the ingredients. Sort the titles in alphabetical order.\", () => {\n checkExerciseAttempted(s3s4e1);\n const expected = [\n { title: \"Classic Cola Glazed Ham\" },\n { title: \"Coca Cola Barbecue Chicken\" },\n { title: \"Long Island Ice Tea\" },\n { title: \"Vanilla Coke\" }\n ];\n\n expect(s3s4e1).to.deep.equal(\n expected,\n \"The result of your query does not match that of the correct answer\"\n );\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1798,"value":"SELECT 'Hello World'; -- REPLACE THIS WITH YOUR QUERY","pinned":true,"hidden":false,"mode":"sql","output":"s3s4e1","database":"var:client"},{"id":986,"value":"---\n### Convert between data types using `CAST`\n\nSometimes you need to treat numbers as Strings to add commas, dollar signs, and percent signs. Sometimes you need to treat Strings as Dates. Sometimes you need to convert booleans to numbers. `CAST` will tell ClickHouse to treat one data type as another.\n\nSyntax: \n- `CAST( AS )` \n- `CAST(, '')` \n- `::type`\n\nThere are also many more specific conversion functions such as `toDate32` and `toString`. [See documentation](https://clickhouse.com/docs/en/sql-reference/functions/type-conversion-functions)","pinned":false,"hidden":false,"mode":"md"},{"id":990,"value":"SELECT\n CAST(1234 AS String) AS my_string,\n CAST('1970-01-01' AS Date) AS my_date,\n CAST('1899-01-01' AS Date) AS my_wrong_date,\n CAST(0 AS BOOLEAN) AS my_false,\n CAST(1 AS BOOLEAN) AS my_true\n","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":998,"value":"#### Read More\n- https://clickhouse.com/docs/en/sql-reference/functions/type-conversion-functions/#castx-t","pinned":false,"hidden":false,"mode":"md"},{"id":1013,"value":"#### Exercise","pinned":false,"hidden":false,"mode":"md"},{"id":1015,"value":"test(\"Add a percent sign to the end of each of the numbers below. Name the resulting column `answer`.\", () => {\n if (!(\"answer\" in s4s1e1[0])) throw new UnattemptedExerciseError();\n expect(_.every(s4s1e1, (r) => typeof r.answer === \"string\")).to.equal(\n true,\n \"Expected the `answer` column to be of type String\"\n );\n expect(_.every(s4s1e1, (r) => r.answer.slice(-1) === \"%\")).to.equal(\n true,\n \"Expected all rows of the `answer` column to end with '%'\"\n );\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1010,"value":"SELECT number FROM numbers(10)","pinned":true,"hidden":false,"mode":"sql","output":"s4s1e1","database":"var:client"},{"id":1001,"value":"---\n### Conditional logic using `if(cond, then, else)`, `CASE WHEN` Expression, and the Ternary Operator\n\n- `if(cond, then, else)`\n- `cond ? val_if_true : val_if_false` -- aka the ternary operator\n- ```sql\n CASE\n WHEN x = a THEN b\n ...[WHEN THEN ]\n ELSE c\n END\n ```","pinned":false,"hidden":false,"mode":"md"},{"id":1041,"value":"SELECT\n title, \n length(ingredients) > 3 ? 'four or more ingredients' : 'three or less ingredients' AS category,\n CASE \n WHEN source ='Gathered' THEN true\n ELSE false\n END AS was_gathered\nFROM recipes;","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":1032,"value":"#### Read More\n\n- https://clickhouse.com/docs/en/sql-reference/operators/#conditional-expression\n- https://clickhouse.com/docs/en/sql-reference/functions/conditional-functions","pinned":false,"hidden":false,"mode":"md"},{"id":1235,"value":"test('Get title (`recipes.title`) of each recipe and make a column (named answer) that summarizes how long the list of directions (`recipes.directions`) is. \"Short\" if <= 3 directions, \"Average\" if 4-6 directions inclusive. \"Long\" if more than 6 directions. Get first 50 results sorted by `title`', async () => {\n checkExerciseAttempted(s5s1e1);\n expect(_.keys(s5s1e1[0])).to.equal(\n [\"title\", \"answer\"],\n \"Expected columns named `title` and `answer` in that order.\"\n );\n const expected = await answers1.query(\n \"SELECT * FROM s5s1e1;\",\n \"The result of your query does not match that of the answer.\"\n );\n expect(s5s1e1).to.deep.equal(expected);\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1226,"value":"SELECT 'Hello World'; -- REPLACE THIS WITH YOUR QUERY","pinned":true,"hidden":false,"mode":"sql","output":"s5s1e1","database":"var:client"},{"id":1060,"value":"---\n### Get the results in your desired output format using `FORMAT`\n\nClickHouse supports a vast variety of input and output formats. See the list [here](https://clickhouse.com/docs/en/sql-reference/formats). It uses `TabSeparated` by default (`PrettyCompat` if you're using clickhouse-client interactively). \n\nSyntax: `FORMAT `\n\n```sql\nSELECT * FROM trips WHERE cab_type = 'green' LIMIT 10 FORMAT CSVWithNames;\n```\n\nOutput:\n```csv\n${await fetch(\n \"https://play.clickhouse.com/?add_http_cors_header=1&user=play&default_format=JSON\",\n {\n method: \"POST\",\n body: \"SELECT * FROM trips WHERE cab_type = 'green' LIMIT 10 FORMAT CSVWithNames;\"\n }\n ).then((res) => res.text())\n}\n```\n\n#### Read More\n- [List of formats](https://clickhouse.com/docs/en/sql-reference/formats)\n- https://clickhouse.com/docs/en/sql-reference/statements/select/format/","pinned":false,"hidden":false,"mode":"md"},{"id":1088,"value":"---\n### Save to file with `INTO OUTFILE`\n\n`INTO OUTFILE file_name [COMPRESSION type]` saves the results into a file on the **client** side. It's able to infer compression type based on file extension or you can input it explicitly.\n\n```sql\nSELECT \n * \nFROM trips \nWHERE cab_type = 'green' \nLIMIT 10 \nINTO OUTFILE 'my-result.csv.gz'\nFORMAT CSVWithNames;\n```\n\nAlternatively, you can redirect output from your command-line process into a file. For example:\n\n```bash\n$ clickhouse-client --query \"SELECT * FROM table\" --format CSVWithNames > output.csv\n```\n\n#### Read More\n- https://clickhouse.com/docs/en/faq/integration/file-export/\n- https://clickhouse.com/docs/en/sql-reference/statements/select/into-outfile/","pinned":false,"hidden":false,"mode":"md"},{"id":94,"value":"## Appendix","pinned":false,"hidden":false,"mode":"md"},{"id":5,"value":"import { client, ClickHouseDatabaseClient } from '@stas-sl/clickhouse-playground'","pinned":false,"hidden":false,"mode":"ojs"},{"id":28,"value":"import {toc} from \"@nebrius/indented-toc\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":867,"value":"answers1 = FileAttachment(\"answers1@1.db\").sqlite()","pinned":false,"hidden":false,"mode":"ojs"},{"id":87,"value":"import { test, expect, editor } from \"@samchencode/utilities\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":697,"value":"class UnattemptedExerciseError extends Error {\n name = \"Exercise Not Attempted\";\n constructor() {\n super(\"Modify the SQL Cell below to get started!\");\n }\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":700,"value":"function checkExerciseAttempted(rows) {\n const firstVal = _.values(rows[0])[0];\n if (firstVal === \"Hello World\") {\n throw new UnattemptedExerciseError();\n }\n}","pinned":false,"hidden":false,"mode":"ojs"}],"files":"$W36","stdlib":"1","titling":"auto"}