Stanford University research group Updated 2025-07-16
Stanford University alumnus Updated 2025-07-16
Stanford University faculty Updated 2025-07-16
Entrepreneurship at Stanford University Updated 2025-07-16
Standard output Updated 2025-07-16
SQL example Updated 2025-07-16
Sequelize is used minimally, just to feed raw queries in transparently to any underlying database, and get minimally parsed results out for us, which we then assert with standard JavaScript. The queries themselves are all written by hand.
By default the examples run on SQLite. Just like the examples from sequelize example, you can set the database at runtime as:
./index.jsor./index.js l: SQLite./index.js p: PostgreSQL. You must manually create a database calledtmpand ensure that peer authentication works for it
Here we list only examples which we believe are standard SQL, and should therefore work across different SQL implementations:
- nodejs/sequelize/raw/index.js: basic hello world to demonstrate the setup and very simple functionality
- nodejs/sequelize/raw/many_to_many.js: illustrates many-to-many relations with JOIN. Contains:
- SQL transaction examples:
- nodejs/sequelize/raw/commit_error.js: stackoverflow.com/questions/27245101/why-should-we-use-rollback-in-sql-explicitly/27245234#27245234 and stackoverflow.com/questions/48277519/how-to-use-commit-and-rollback-in-a-postgresql-function/48277708#48277708 suggest that on PostgreSQL, once something fails inside a transaction, all queries in the current transaction are ignored, and
COMMITsimply does aROLLBACK. Let's check. Yup, true for Postgres, but false for SQLite, SQLite just happily runs anything it can, you really needROLLBACKfor it. - SQL isolation level example
- nodejs/sequelize/raw/commit_error.js: stackoverflow.com/questions/27245101/why-should-we-use-rollback-in-sql-explicitly/27245234#27245234 and stackoverflow.com/questions/48277519/how-to-use-commit-and-rollback-in-a-postgresql-function/48277708#48277708 suggest that on PostgreSQL, once something fails inside a transaction, all queries in the current transaction are ignored, and
- GROUP BY and SQL aggregate functions:
- nodejs/sequelize/raw/group_by_extra_column.js: let's see if it blows up or not on different DB systems,
sqlite3Node.js package allows it:- github.com/sequelize/sequelize/issues/5481#issuecomment-964387232
- dba.stackexchange.com/questions/141594/how-select-column-does-not-list-in-group-by-clause/141600 says that it was allowed in SQL:1999 when there are no ambiguities due to constraints, e.g. when grouping by unique columns
- github.com/postgres/postgres/blob/REL_13_5/src/test/regress/sql/functional_deps.sql#L27 shows that PostgreSQL wants it to work for
UNIQUE NOT NULL, but they just haven't implemented it as of 13.5, where it only works if you group byPRIMARY KEY - dba.stackexchange.com/questions/158015/why-can-i-select-all-fields-when-grouping-by-primary-key-but-not-when-grouping-b also says that
UNIQUE NOT NULLdoesn't work. Dan Lenski then points to a rationale mailing list thread.
- nodejs/sequelize/raw/group_by_max_full_row.js: here we try to get the full row of each group at which a given column reaches the max of the group
- Postgres: has
SELECT DISCINTCT ONwhich works perfectly if you only want one row in case of multiple rows attaining the max.ONis an extension to the standard unfortunately: www.postgresql.org/docs/9.3/sql-select.html#SQL-DISTINCT Docs specify that it always respectsORDER BYwhen selecting the row.- stackoverflow.com/questions/586781/postgresql-fetch-the-row-which-has-the-max-value-for-a-column asks it without the multiple matches use case
- stackoverflow.com/questions/586781/postgresql-fetch-the-rows-which-have-the-max-value-for-a-column-in-each-group/587209#587209 also present in simpler form at stackoverflow.com/questions/121387/fetch-the-rows-which-have-the-max-value-for-a-column-for-each-distinct-value-of/123481#123481 gives a very nice OUTER JOIN only solution! Incredible, very elegant.
- dba.stackexchange.com/questions/171938/get-only-rows-with-max-group-value asks specifically the case of multiple matches to the max
- stackoverflow.com/questions/586781/postgresql-fetch-the-row-which-has-the-max-value-for-a-column asks it without the multiple matches use case
- SQLite:
- stackoverflow.com/questions/48326957/row-with-max-value-per-group-sqlite
- stackoverflow.com/questions/48326957/row-with-max-value-per-group-sqlite/48328243#48328243 teaches us that in SQLite min and max are magic and guarantee that the matching row is returned
- stackoverflow.com/questions/48326957/row-with-max-value-per-group-sqlite/72996649#72996649 Ciro Santilli uses the magic of
ROW_NUMBER
- stackoverflow.com/questions/17277152/sqlite-select-distinct-of-one-column-and-get-the-others/71924314#71924314 get any full row without specifying which, we teach how to specify
- code.djangoproject.com/ticket/22696 WONTFIXed
DISTINCT ON- stackoverflow.com/questions/50846722/what-is-the-difference-between-postgres-distinct-vs-distinct-on/72997494#72997494
DISTINCTvsDISTINCT ON, somewhat related question
- stackoverflow.com/questions/50846722/what-is-the-difference-between-postgres-distinct-vs-distinct-on/72997494#72997494
- stackoverflow.com/questions/48326957/row-with-max-value-per-group-sqlite
- stackoverflow.com/questions/5803032/group-by-to-return-entire-row asks how to take the top N with distinct after order limit. I don't know how to do it in Postgres
- Postgres: has
- nodejs/sequelize/raw/most_frequent.js: illustrates a few variants of findind the mode, including across GROUP
- nodejs/sequelize/raw/group_by_max_n.js: get the top N in each group
- nodejs/sequelize/raw/group_by_extra_column.js: let's see if it blows up or not on different DB systems,
- order results in the same order as
IN: - LIMIT by a running total: TODO links
SQL aggregate function Updated 2025-07-16
SQL transaction Updated 2025-07-16
SQL keyword Updated 2025-07-16
SQL standard Updated 2025-07-16
A quick look at PostgreSQL's compliance notes: www.postgresql.org/docs/13/features.html shows the complete utter mess that this standard is. Multiple compliance levels that no one fully implements and optional features everywhere.
SQL implementation Updated 2025-07-16
SQL tree traversal Updated 2025-07-16
Example: nodejs/sequelize/raw/tree.js
- Implementation agnostic
- Postgres
- stackoverflow.com/questions/67848017/simple-recursive-sql-query
- stackoverflow.com/questions/28688264/how-to-traverse-a-hierarchical-tree-structure-structure-backwards-using-recursiv
- stackoverflow.com/questions/51822070/how-can-postgres-represent-a-tree-of-row-ids
- depth first
- uspecified depth first variant
- preorder DFS
- breadth-first stackoverflow.com/questions/3709292/select-rows-from-table-using-tree-order
- MySQL
- Microsoft SQL Server
Spontaneous emission defies causality Updated 2025-07-16
TODO understand better, mentioned e.g. at Subtle is the Lord by Abraham Pais (1982) page 20, and is something that Einstein worked on.
Spin 0 Updated 2025-07-16
Spin half Updated 2025-07-16
Leads to the Dirac equation.
Spin 1 Updated 2025-07-16
Leads to the Proca equation.
Why is the spin of the electron half? Updated 2025-07-16
More interestingly, how is that implied by the Stern-Gerlach experiment?
physics.stackexchange.com/questions/266359/when-we-say-electron-spin-is-1-2-what-exactly-does-it-mean-1-2-of-what/266371#266371 suggests that half could either mean:
- at limit of large
lfor the Schrödinger equation solution for the hydrogen atom the difference between each angular momentum is twice that of the eletron's spin. Not very satisfactory. - it comes directly out of the Dirac equation. This is satisfactory. :-)
Stern-Gerlach experiment Updated 2025-07-16
Originally done with (neutral) silver atoms in 1921, but even clearer theoretically was the hydrogen reproduction in 1927 by T. E. Phipps and J. B. Taylor.
The hydrogen experiment was apparently harder to do and the result is less visible, TODO why: physics.stackexchange.com/questions/33021/why-silver-atoms-were-used-in-stern-gerlach-experiment
The Stern-Gerlach Experiment by Educational Services, Inc (1967)
Source. Featuring MIT Professor Jerrold R. Zacharias. Amazing experimental setup demonstration, he takes apart much of the experiment to show what's going on. Spintronics Updated 2025-07-16
Introduction to Spintronics by Aurélien Manchon (2020)
Source. The Spin on Electronics by Stuart Parkin
. Source. 2013. There are unlisted articles, also show them or only show them.