Archive for the ‘SQL’ Category
Posted by jpluimers on 2025/07/08
While writing Some SQLite things I recently learned a while ago I bumped into SQLite extending the SQL syntax allowing SELECT column aliases in WHERE and ON conditions of JOIN clauses whereas standard SQL only allows it in the HAVING clause (some other DMBS allow this as well).
It reminded I never wrote a blog post about WHERE versus HAVING, which can be phrased into just two points:
WHERE is applied before the SELECT is assembledHAVING is applied after the SELECT is assembled
This also means WHERE has a lot more influence on performance than HAVING, so using HAVING just so you can use aliases there instead of in the WHERE clauses is not a smart idea unless you verify in the query plan and query optimisation output of our DBMS this has no influence (some DBMS even allow you to see the results of query rewriting which you can use to verify if two queries really are equivalent from the DBMS perspective).
Since HAVING can be used without GROUP BY, many people work around the WHERE alias limitation. Given the above effects on performance, this is not a wise idea, despite the same end-results. See the example below that uses the [Wayback/Archive] MySQL :: Sakila Sample Database: customer table.
Luckily there has been enough other material explaining WHERE versus HAVING, so I can reference it from here, for instance (note note limited to MySQL/MariaDB) [Wayback/Archive] mysql – WHERE vs HAVING – Stack Overflow (thanks [Wayback/Archive] baloo and [Wayback/Archive] Fishdrowned):
Read the rest of this entry »
Posted in Conference Topics, Conferences, Database Development, Development, Event, MariaDB, MySQL, SQL, SQLite | Tagged: 1054, this | Leave a Comment »
Posted by jpluimers on 2025/07/02
Since I keep forgetting which DBMS uses which method to select just a plain value without a table. I always remember it as my search phrase [Wayback/Archive] SELECT from DUAL, but actually better titled like the question below:
[Wayback/Archive] sql – SELECT from nothing? – Stack Overflow
Posted in Access, Database Development, DB2, Development, Firebird, InterBase, MariaDB, MySQL, OracleDB, PL/SQL, PostgreSQL, SQL, SQL Server, SQLite | Leave a Comment »
Posted by jpluimers on 2025/05/27
This case, it was C# accessing a SQL back-end, but the responses to the Tweet how so many more examples not even related to software development.
Remember that plane crashing because they overloaded while they thought the fuel load numbers were in Imperial pounds where in fact they were in metric kilograms?
That’s why naming things that contain numbers should contain the unit in their name!
Related blog post: Watch “Felienne Hermans: How patterns in variable names can make code easier to read” on YouTube
Tweet: [Wayback/Archive] Nick Craver on Twitter: “Troubleshooting a hanging test suite and godDAMMIT. “In seconds”. Integer timeouts should be a felony offense punishable by an indeterminate amount of seconds/milliseconds/hours/fortnights/whatever the judge chooses.”
var csb = new SqlConnectionStringBuilder(TestConfig.Current. SQLServerConnectionString) { ConnectTimeout = 2000 }; int SqlConnectionStringBuilder.ConnectTimeout { get; set; } Gets or sets the length of time (in seconds) to wait for a connection to the server before terminating the attempt and generating an error. Returns: The value of the SqlConnectionStringBuilder, ConnectTimeout property, or 15 seconds if no value has been supplied.
Read the rest of this entry »
Posted in .NET, Agile, C#, Code Quality, Conference Topics, Conferences, Database Development, Development, Event, Software Development, SQL, SQL Server, Systems Architecture | Leave a Comment »
Posted by jpluimers on 2025/04/30
More on the reason why I learned a few SQLite things soon, but for my link and documentation archive, below is what I learned.
Most commands use the database file C:\temp\History which has no extension as that is how I got the file in the first place (spoiler: it’s a Chrome browser History from one of my user profiles).
Let’s get started:
Read the rest of this entry »
Posted in *nix, *nix-tools, bash, CommandLine, Conference Topics, Conferences, Console (command prompt window), Database Development, Development, Event, Power User, PowerShell, PowerShell, Software Development, SQL, SQLite | Leave a Comment »
Posted by jpluimers on 2025/03/06
2.5 years after Miguel summarised the state of AI text models, and given SQL Injection (because of mixing control and data channels) still is a thing in the 2020’s, I wonder both how much improvement there has been on the AI side of things and how much it is used in pen testing.
So I archived the below tweets to be able to read back and figure out on the current state.
[Wayback/Archive] Miguel de Icaza on Twitter: “This is so beautiful – SQL Injection attacks but for GPT-3 and other AI text models.”:
Read the rest of this entry »
Posted in AI and ML; Artificial Intelligence & Machine Learning, Blue team, Database Development, Development, Pen Testing, Power User, Red team, Security, Software Development, SQL | Leave a Comment »
Posted by jpluimers on 2025/02/11
For my link archive: [Wayback/Archive] Payload Box.
It has lots of examples on payloads for various kinds of injections that are excellent teaching material.
Covered are Cross Site Scripting (XSS), SQL Injection, Server Side Template Injection, RFI/LFI, Command Injection, CSV Injection, Directory, Open Redirect and XML External Entity (XXE) Injection.
Got there when inspired by:
Read the rest of this entry »
Posted in Blue team, Database Development, Development, Power User, Red team, Security, Software Development, SQL, Web Development | Leave a Comment »
Posted by jpluimers on 2025/01/07
For my link archive: [Wayback/Archive] MySQL: Row Literals | Die wunderbare Welt von Isotopp
Question on the Libera/#mysql IRC channel:
Is there a way to split a simple select into multiple returned rows? For example, select 1, 2, 3 to be returned as rows?
This is actually asking for a table literal notation. I know of four ways to construct a table literal in MySQL:
They are based on UNION ALL, JSON_TABLE and VALUES statement (the latter in two forms). I knew about the first (which I used in other database environments), not about the others.
Read the rest of this entry »
Posted in Database Development, Development, MySQL, SQL | Leave a Comment »
Posted by jpluimers on 2024/06/25
Every once in a while, b0rk (Julia Evans, of [Wayback/Archive] wizard zines fame) asks interesting questions like below that results in lot of cool links.
I have blogged assemblies of them before (see for instance Lots of interesting git links via b0rk on Twitter) and this one is no different:
[Wayback/Archive] Julia Evans on Twitter: “what are some helpful programming learning games? thinking of things like mystery.knightlab.com for SQL, and flexboxfroggy.com, and ohmygit.org especially interested in games that have helped you learn something”
The response was overwhelmingly good (I tried to indicate when games are not free or not playable from a web browser). I summarised it below.
Read the rest of this entry »
Posted in *nix, *nix-tools, Conference Topics, Conferences, CSS, Database Development, Development, DVCS - Distributed Version Control, Event, Games, git, Multi-Threading / Concurrency, Power User, RegEx, Scripting, sh, Sh Shell, Software Development, Source Code Management, SQL, Web Development | Leave a Comment »
Posted by jpluimers on 2023/01/12
[Wayback/Archive] SQLZOO has an extensive set of interactive tutorials spread over these topics:
- basic SQL tutorials gradually getting more difficult (including some Covid-19 materials)
- SQL “how to” style questions
- More involved examples from easy via medium to hard
- A White Christmas challenge
So the above is kind of a continuation of my series of games to learn software and database development PostgreSQL Exercises.
I found it via the first reaction to [Archive] Steve Polito on Twitter: “If you’re like me and want to level up your SQL game, give PostgreSQL Exercises a try. …” / Twitter (which initiated yesterday’s post):
[Archive] Loumarven Payot on Twitter: “@stevepolitodsgn I’ve also tried sqlzoo.net. Almost done with it. Next on my list are dataschool.com and selectstarsql.com” / Twitter
Which means I’ve more sites to try.
These will be the next: [Wayback/Archive] Learn SQL: Interactive SQL Book, from dataschool and [Wayback/Archive] Select Star SQL (which is an interactive book that I should be able to finish in a day full of reading and experimenting).
–jeroen
Posted in Conference Topics, Conferences, Database Development, Development, Event, Software Development, SQL | 1 Comment »
The Wiert Corner - irregular stream of stuff 