6 ms·

For a while now, whenever I have taught SQL to people (which was only I think 3 times over like 8 years) I ask them to imagine that the word SELECT can be repla

For a while now, whenever I have taught SQL to people (which was only I think 3 times over like 8 years) I ask them to imagine that the word SELECT can be replaced with AGGREGATE which unlocks these magical words GROUP BY and SUM and so forth, and they understand me perfectly well: and then I drop on them that I was lying and the magic word is still SELECT but you have to know which kind of SELECT it is from context clues and they are like “What???? Whyyyyyy.”

derefr 3y ago
To be honest, teaching SQL "syntax in" seems like a really backwards way of getting an understanding of what SQL is doing. I run the tech side of a company almost entirely built on complex SQL queries. If I were to take a fresh college student and "teach them SQL"... well, I wouldn't teach them SQL. I'd teach them relational algebra: projection, selection, join, etc. as mathematical operators over bags of tuples. I'd make sure that they could show me what the bag-of-tuples looks like at each step, as each operator is applied. I'd get them to write code in some programming language with first-class Set and Tuple datatypes, that does these operations as in-memory functional transforms over bag-of-tuples -typed variables. And then, once they have that solid understanding, I'd show them, in order: 1. that there exist these systems called RDBMSes that maintain durable, mutable bags-of-tuples they call tables (which is weird when you think about it; coming from a math world, a relation should be an immutable mathematical object, a value-object defined by its members. But RDBMS tables aren't relations, not exactly — and this is where you can go into detail about SQL `NULL`, "nullable columns", and how in an RDBMS, the absence of an asserted row-tuple in a table doesn't necessarily mean the implicit assertion of its logical negation as a logical predicate; it rather means "haven't been fed the truth-value of that relation yet." RDBMS query engines aren't relational-algebra computers; they're relational-algebra provers — and NULL is their "not proven"!); 2. that these systems accept a COBOL-like declarative syntax for either • referencing their tables (`TABLE foo`); • expressing relational algebra upon their tables (`SELECT ... FROM foo ...`); • defining arbitrary literal bags-of-tuples (e.g. `VALUES (1, 2)` — bet you didn't know that was toplevel-grammatical in SQL!); • expressing relational algebra upon arbitrary literal bags-of-tuples (e.g. `SELECT a FROM (VALUES (1, 2)) AS x (a, b)`); • or any combinations of the above — using UNION, INTERSECT, or introducing any of the above as a common table expression and then referencing it by name. 3. that these systems also accept COBOL-like imperative syntax — one which really has not-much-at-all to do with relational algebra — for modifying the durable, mutable bag-of-tuples asserted by a given table (INSERT, UPDATE, DELETE, TRUNCATE); 4. and that, completely unnecessarily, these systems also have a COBOL-like imperative syntax for modifying the definitions of the tables themselves (unnecessary because the data-definition schema could have just been, itself, a set of tables that you modify through DML statements. INFORMATION_SCHEMA exists, but it's read-only in every RDBMS I've ever seen. Wacky, isn't it?)
- QuercusMax 3y ago
  I took a class on Databases back during my undergrad in the early 2000s, and it covered pretty much all of this stuff. I thought this was standard stuff people learned as part of a CS degree.
- uticus 3y ago
  > I'd teach them relational algebra... Agreed. Curious what materials you've found that really drive this home? Like 90% of googlesearch results simplify things to the point where this is missed.
  - formerly_proven 3y ago
    A lot of the Relational Databases course materials in CS programs is built from Oracle educational material; it's probably freely available for many universities. I always assumed the approach outlined by GP is the default way to teach SQL and relational databases.
  - Tallain 3y ago
    SQL Server Central has a "Stairway to T-SQL" series that starts off with some light history and then has two fairly decent chapters diving into the mathematics of SQL. It's not intensive by any means, but more than most other resources (and even some textbooks I've read). [0]: https://www.sqlservercentral.com/stairways/stairway-to-t-sql-dml https://www.sqlservercentral.com/stairways/stairway-to-t-sql...
- refulgentis 3y ago
  There’s certainly merit in learning the mathematical foundations of SQL, like relational algebra. Beginning with SQL syntax allows students to quickly create something tangible, which can be incredibly gratifying and motivating. It’s a bit like learning to play music; while music theory is important, there's something to be said for picking up an instrument and playing a few notes to spark a passion. The intricacies of relational algebra can be woven into the curriculum once students have a handle on the basics and can appreciate the deeper structures at play.

derefr 3y ago

To be honest, teaching SQL "syntax in" seems like a really backwards way of getting an understanding of what SQL is doing. I run the tech side of a company almost entirely built on complex SQL queries. If I were to take a fresh college student and "teach them SQL"... well, I wouldn't teach them SQL. I'd teach them relational algebra: projection, selection, join, etc. as mathematical operators over bags of tuples. I'd make sure that they could show me what the bag-of-tuples looks like at each step, as each operator is applied. I'd get them to write code in some programming language with first-class Set and Tuple datatypes, that does these operations as in-memory functional transforms over bag-of-tuples -typed variables. And then, once they have that solid understanding, I'd show them, in order: 1. that there exist these systems called RDBMSes that maintain durable, mutable bags-of-tuples they call tables (which is weird when you think about it; coming from a math world, a relation should be an immutable mathematical object, a value-object defined by its members. But RDBMS tables aren't relations, not exactly — and this is where you can go into detail about SQL `NULL`, "nullable columns", and how in an RDBMS, the absence of an asserted row-tuple in a table doesn't necessarily mean the implicit assertion of its logical negation as a logical predicate; it rather means "haven't been fed the truth-value of that relation yet." RDBMS query engines aren't relational-algebra computers; they're relational-algebra provers — and NULL is their "not proven"!); 2. that these systems accept a COBOL-like declarative syntax for either • referencing their tables (`TABLE foo`); • expressing relational algebra upon their tables (`SELECT ... FROM foo ...`); • defining arbitrary literal bags-of-tuples (e.g. `VALUES (1, 2)` — bet you didn't know that was toplevel-grammatical in SQL!); • expressing relational algebra upon arbitrary literal bags-of-tuples (e.g. `SELECT a FROM (VALUES (1, 2)) AS x (a, b)`); • or any combinations of the above — using UNION, INTERSECT, or introducing any of the above as a common table expression and then referencing it by name. 3. that these systems also accept COBOL-like imperative syntax — one which really has not-much-at-all to do with relational algebra — for modifying the durable, mutable bag-of-tuples asserted by a given table (INSERT, UPDATE, DELETE, TRUNCATE); 4. and that, completely unnecessarily, these systems also have a COBOL-like imperative syntax for modifying the definitions of the tables themselves (unnecessary because the data-definition schema could have just been, itself, a set of tables that you modify through DML statements. INFORMATION_SCHEMA exists, but it's read-only in every RDBMS I've ever seen. Wacky, isn't it?)

QuercusMax 3y ago
I took a class on Databases back during my undergrad in the early 2000s, and it covered pretty much all of this stuff. I thought this was standard stuff people learned as part of a CS degree.
uticus 3y ago
> I'd teach them relational algebra... Agreed. Curious what materials you've found that really drive this home? Like 90% of googlesearch results simplify things to the point where this is missed.
- formerly_proven 3y ago
  A lot of the Relational Databases course materials in CS programs is built from Oracle educational material; it's probably freely available for many universities. I always assumed the approach outlined by GP is the default way to teach SQL and relational databases.
- Tallain 3y ago
  SQL Server Central has a "Stairway to T-SQL" series that starts off with some light history and then has two fairly decent chapters diving into the mathematics of SQL. It's not intensive by any means, but more than most other resources (and even some textbooks I've read). [0]: https://www.sqlservercentral.com/stairways/stairway-to-t-sql-dml https://www.sqlservercentral.com/stairways/stairway-to-t-sql...
refulgentis 3y ago
There’s certainly merit in learning the mathematical foundations of SQL, like relational algebra. Beginning with SQL syntax allows students to quickly create something tangible, which can be incredibly gratifying and motivating. It’s a bit like learning to play music; while music theory is important, there's something to be said for picking up an instrument and playing a few notes to spark a passion. The intricacies of relational algebra can be woven into the curriculum once students have a handle on the basics and can appreciate the deeper structures at play.