3 ms·

Have used SQL, not super advanced. What are the "advanced" SQL and RDB things to learn after you learn how to create and populate tables, query, join between ta

Have used SQL, not super advanced. What are the "advanced" SQL and RDB things to learn after you learn how to create and populate tables, query, join between tables?

nojito 5y ago
Window functions.
kthejoker2 5y ago
Nearly 30 years of SQL experience here, I tell new people there's really no advanced SQL, just "exotic" SQL. (Or a joke: "There's 3 types of Advanced SQL. Really long SQL. Exotic SQL. And SQL no one should have written.") The "advanced" part is: 1) Set Theory - really understanding set theory so you can model datasets appropriately and write efficient SQL 2) The Toolkit - knowing how the major concepts of SQL (join types, CTEs, table valued functions, windowing, aggregation, conditional logic, indexes, constraints, normalization/denormalization, etc.) contribute to #1 3) Performance Analysis - understanding how to analyze the performance characteristics of a query so you can apply #2 to #1 to achieve good results And usually the difference I see between juniors and seniors is if you give them 2 "advanced" queries and tell them one runs fine and one doesn't, seniors very quickly know which one is bad and why it runs bad ... juniors aren't even confident the two queries will run because they contain syntax or patterns they're unfamiliar with.
- denton-scratch 5y ago
  > really long SQL Some systems generate really long SQL queries; I'm thinking of you, Drupal! I haven't worked with D8, and only a little with D7. But D6 would routinely produce SELECT queries with 20 tables joined. Drupal is notorious for caching nearly everything, including the results of SQL queries, because those queries were essentially untunable (and the "cache" was usually yet another database table). "Flush all of the caches" was the first advice to anyone struggling with a D6 issue.
denton-scratch 5y ago
How to use EXPLAIN PLAN. As a contractor, I was once asked to improve the performance of a query that had a main table with many millions of rows. EXPLAIN PLAN revealed that the query planner was making some bad choices, despite the table statistics being up-to-date. I bullied[0] the optimiser into making some better choices, and got a 100x performance improvement. For large databases, the query plan can make the difference between a query taking a second and taking an hour. Unfortunately the format of query-plan output from EXPLAIN PLAN is unstandardised and vendor-dependent. [0] "bullied": This was Oracle; you could use "hints" to force the optimiser to change it's behaviour. If you don't have hints, then sometimes transforming a JOIN query into a query using an inner SELECT will expand the optimiser's consciousness. It all gets a bit voodoo, if the optimiser is stubborn and you don't have hints.

Have used SQL, not super advanced. What are the "advanced" SQL and RDB things to learn after you learn how to create and populate tables, query, join between tables?

nojito 5y ago

Window functions.

kthejoker2 5y ago

Nearly 30 years of SQL experience here, I tell new people there's really no advanced SQL, just "exotic" SQL. (Or a joke: "There's 3 types of Advanced SQL. Really long SQL. Exotic SQL. And SQL no one should have written.") The "advanced" part is: 1) Set Theory - really understanding set theory so you can model datasets appropriately and write efficient SQL 2) The Toolkit - knowing how the major concepts of SQL (join types, CTEs, table valued functions, windowing, aggregation, conditional logic, indexes, constraints, normalization/denormalization, etc.) contribute to #1 3) Performance Analysis - understanding how to analyze the performance characteristics of a query so you can apply #2 to #1 to achieve good results And usually the difference I see between juniors and seniors is if you give them 2 "advanced" queries and tell them one runs fine and one doesn't, seniors very quickly know which one is bad and why it runs bad ... juniors aren't even confident the two queries will run because they contain syntax or patterns they're unfamiliar with.

denton-scratch 5y ago
> really long SQL Some systems generate really long SQL queries; I'm thinking of you, Drupal! I haven't worked with D8, and only a little with D7. But D6 would routinely produce SELECT queries with 20 tables joined. Drupal is notorious for caching nearly everything, including the results of SQL queries, because those queries were essentially untunable (and the "cache" was usually yet another database table). "Flush all of the caches" was the first advice to anyone struggling with a D6 issue.

denton-scratch 5y ago

How to use EXPLAIN PLAN. As a contractor, I was once asked to improve the performance of a query that had a main table with many millions of rows. EXPLAIN PLAN revealed that the query planner was making some bad choices, despite the table statistics being up-to-date. I bullied[0] the optimiser into making some better choices, and got a 100x performance improvement. For large databases, the query plan can make the difference between a query taking a second and taking an hour. Unfortunately the format of query-plan output from EXPLAIN PLAN is unstandardised and vendor-dependent. [0] "bullied": This was Oracle; you could use "hints" to force the optimiser to change it's behaviour. If you don't have hints, then sometimes transforming a JOIN query into a query using an inner SELECT will expand the optimiser's consciousness. It all gets a bit voodoo, if the optimiser is stubborn and you don't have hints.