4 ms·

Learning outcomes: * Explain the difference between a database and a database manager. * Write SQL to select, filter, sort, group, and aggregate data. * Defi

Learning outcomes: * Explain the difference between a database and a database manager. * Write SQL to select, filter, sort, group, and aggregate data. * Define tables and insert, update, and delete records. * Describe different types of join and write queries that use them to combine data. * Use windowing functions to operate on adjacent rows. * Explain what transactions are and write queries that roll back when constraints are violated. * Explain what triggers are and write SQL to create them. * Manipulate JSON data using SQL. * Interact with a database using Python directly, from a Jupyter notebook, and via an ORM.

LegitShady 3y ago
these does seem to be a fairly good curriculum for an introduction to SQL
bigger_cheese 3y ago
One thing that I have noticed confuses a lot of people is "timeseries joins" (I don't know the real term for this) I'm talking about where there is no "one to one" match between keys in the two tables. I'm a non software type of engineer in my world a lot of tables are structured as timeseries data (such as readings from a device or instrument) which uses timestamp as a key. Then we have other tables which log event or batch data (such as an alarm start and end time, or Machine start/machine stop etc). So a lot of queries end up being of the form Select A.AlarmId, B.Reading, B.Timestamp from Alarms A, Readings B where A.StartTime >= B.Timestamp and A.EndTime < B.Timestamp A lot of people seem to have problems grasping these kinds of joins.
- mr_toad 3y ago
  You mean slowly changing dimensions? It’s not something there is much literature on, especially for outer joins.
- davery22 3y ago
  Cool use case. They're just called "non equi-joins" - because the join condition is an inequality. In general a join produces a row in the output table for each (left, right) pair of rows from the input tables that satisfies the join condition. It's just so common for joins to use a simple equality condition, where one or both sides is a unique id for its table, and people don't as often encounter joins where one input row can inform multiple output rows.
- Jgrubb 3y ago
  Duck and Clickhouse call this an AsOf join - https://duckdb.org/docs/guides/sql_features/asof_join.html https://duckdb.org/docs/guides/sql_features/asof_join.html
  - pradeepchhetri 3y ago
    Yup https://clickhouse.com/docs/en/sql-reference/statements/select/join#asof-join-usage https://clickhouse.com/docs/en/sql-reference/statements/sele...
pradeepchhetri 3y ago
If you are interested in reading about JOINS in detail, ClickHouse has good blogs around it: - https://clickhouse.com/blog/clickhouse-fully-supports-joins-part1 https://clickhouse.com/blog/clickhouse-fully-supports-joins-... - https://clickhouse.com/blog/clickhouse-fully-supports-joins-hash-joins-part2 https://clickhouse.com/blog/clickhouse-fully-supports-joins-... - https://clickhouse.com/blog/clickhouse-fully-supports-joins-full-sort-partial-merge-part3 https://clickhouse.com/blog/clickhouse-fully-supports-joins-... - https://clickhouse.com/blog/clickhouse-fully-supports-joins-direct-join-part4 https://clickhouse.com/blog/clickhouse-fully-supports-joins-... - https://clickhouse.com/blog/clickhouse-fully-supports-joins-how-to-choose-the-right-algorithm-part5 https://clickhouse.com/blog/clickhouse-fully-supports-joins-...
- tucnak 3y ago
  I'm sorry but Clickhouse isn't SQL-compliant so it has no business teaching us JOIN.
  - 392 3y ago
    No database is fully SQL compliant. Though clickhouse can at times be painfully so. https://stackoverflow.com/questions/784900/why-does-no-database-fully-support-ansi-or-iso-sql-standards https://stackoverflow.com/questions/784900/why-does-no-datab...
    - tucnak 3y ago
      > PostgreSQL supports most of the major features of SQL:2023. Out of 177 mandatory features required for full Core conformance, PostgreSQL conforms to at least 170. In addition, there is a long list of supported optional features. https://www.postgresql.org/docs/current/features.html https://www.postgresql.org/docs/current/features.html Clickhouse is simply a joke in comparison. No basic cursor support, no transactions, incomplete comparison operators, no single-row SELECT with GROUP BY and HAVING clauses grouped views, no procedures, no anti-joins or WHERE EXISTS, for that matter. The list goes on... it's basically impossible to write SQL to any degree of sophistication in Clickhouse.

LegitShady 3y ago

these does seem to be a fairly good curriculum for an introduction to SQL

bigger_cheese 3y ago

One thing that I have noticed confuses a lot of people is "timeseries joins" (I don't know the real term for this) I'm talking about where there is no "one to one" match between keys in the two tables. I'm a non software type of engineer in my world a lot of tables are structured as timeseries data (such as readings from a device or instrument) which uses timestamp as a key. Then we have other tables which log event or batch data (such as an alarm start and end time, or Machine start/machine stop etc). So a lot of queries end up being of the form Select A.AlarmId, B.Reading, B.Timestamp from Alarms A, Readings B where A.StartTime >= B.Timestamp and A.EndTime < B.Timestamp A lot of people seem to have problems grasping these kinds of joins.

mr_toad 3y ago
You mean slowly changing dimensions? It’s not something there is much literature on, especially for outer joins.
davery22 3y ago
Cool use case. They're just called "non equi-joins" - because the join condition is an inequality. In general a join produces a row in the output table for each (left, right) pair of rows from the input tables that satisfies the join condition. It's just so common for joins to use a simple equality condition, where one or both sides is a unique id for its table, and people don't as often encounter joins where one input row can inform multiple output rows.
Jgrubb 3y ago
Duck and Clickhouse call this an AsOf join - https://duckdb.org/docs/guides/sql_features/asof_join.html https://duckdb.org/docs/guides/sql_features/asof_join.html
- pradeepchhetri 3y ago
  Yup https://clickhouse.com/docs/en/sql-reference/statements/select/join#asof-join-usage https://clickhouse.com/docs/en/sql-reference/statements/sele...

pradeepchhetri 3y ago

If you are interested in reading about JOINS in detail, ClickHouse has good blogs around it: - https://clickhouse.com/blog/clickhouse-fully-supports-joins-part1 https://clickhouse.com/blog/clickhouse-fully-supports-joins-... - https://clickhouse.com/blog/clickhouse-fully-supports-joins-hash-joins-part2 https://clickhouse.com/blog/clickhouse-fully-supports-joins-... - https://clickhouse.com/blog/clickhouse-fully-supports-joins-full-sort-partial-merge-part3 https://clickhouse.com/blog/clickhouse-fully-supports-joins-... - https://clickhouse.com/blog/clickhouse-fully-supports-joins-direct-join-part4 https://clickhouse.com/blog/clickhouse-fully-supports-joins-... - https://clickhouse.com/blog/clickhouse-fully-supports-joins-how-to-choose-the-right-algorithm-part5 https://clickhouse.com/blog/clickhouse-fully-supports-joins-...

tucnak 3y ago
I'm sorry but Clickhouse isn't SQL-compliant so it has no business teaching us JOIN.
- 392 3y ago
  No database is fully SQL compliant. Though clickhouse can at times be painfully so. https://stackoverflow.com/questions/784900/why-does-no-database-fully-support-ansi-or-iso-sql-standards https://stackoverflow.com/questions/784900/why-does-no-datab...
  - tucnak 3y ago
    > PostgreSQL supports most of the major features of SQL:2023. Out of 177 mandatory features required for full Core conformance, PostgreSQL conforms to at least 170. In addition, there is a long list of supported optional features. https://www.postgresql.org/docs/current/features.html https://www.postgresql.org/docs/current/features.html Clickhouse is simply a joke in comparison. No basic cursor support, no transactions, incomplete comparison operators, no single-row SELECT with GROUP BY and HAVING clauses grouped views, no procedures, no anti-joins or WHERE EXISTS, for that matter. The list goes on... it's basically impossible to write SQL to any degree of sophistication in Clickhouse.