The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 2,977 other subscribers

Archive for the ‘Database Development’ Category

The SQL Murder Mystery

Posted by jpluimers on 2023/01/17

After last week’s posts PostgreSQL Exercises and SQLZOO: Interactive SQL tutorials (no login required), I did a Twitter search:

[Archive] pgexercises.com – Twitter Search / Twitter.

This got me to [Wayback/Archive] The SQL Murder Mystery (which is fun!).

I got there via [Archive] Lætitia on Twitter: “@nthonynowocien I’d say pgexercises is a good start for SQL pgexercises.com Even though, the last exercises are not beginner level. The SQL murder mystery is more fun mystery.knightlab.com” / Twitter

–jeroen

Posted in Conference Topics, Conferences, Database Development, Development, Event, Software Development, SQL | Leave a Comment »

SQLZOO: Interactive SQL tutorials (no login required)

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 | Leave a Comment »

PostgreSQL Exercises

Posted by jpluimers on 2023/01/11

[Wayback/Archive] PostgreSQL Exercises

This site was born when I noticed that there’s a load of material out there to help people learn about SQL, but not a great deal to make it easy to learn by doing. PGExercises provides a series of questions and explanations built on a single, simple dataset.

It was funny, as I bummped into right after writing the article Enabling GitHub pages to a HTML or markdown GitHub project is dead easy: Delphi deadlockempire is now hosted on github.io (which reached the top of the blog queue yesterday).

After reading the [Wayback/Archive] PostgreSQL Exercises: Getting Started, start the exercises at [Wayback/Archive] PostgreSQL exercises: basic exercises.

There is no login needed, which I really like.

Note that some of the assignments are hard, and can have multiple results, see for instance [Archive] Fahru on Twitter: “this: … I FINALLY completed it, and any win is worth telling🥳 took me like one hour on and off. The “more than 30$” requirement is bizarre 😂 a bit different than the official answer so I’m digging up more about this learned a heck ton, worth the time! ” / Twitter

Via: [Archive] Steve Polito on Twitter: “If you’re like me and want to level up your SQL game, give PostgreSQL Exercises a try. …” / Twitter

–jeroen

Posted in Database Development, Development, PostgreSQL, Software Development, SQL | Leave a Comment »

Securely Connecting to Autonomous DB Without a Wallet (Using TLS)

Posted by jpluimers on 2022/12/20

[Wayback/Archive] Securely Connecting to Autonomous DB Without a Wallet (Using TLS)

It is about moving from mTLS to TLS on Oracle Autonomous DB and at the same time IP-whitelisting the client IP addresses.

[Archive] Chris Bensen on Twitter: “This is extremely useful so I figured I’d share in the hopes it helps someone else “

–jeroen

Posted in Cloud, Cloud Development, Database Development, Development, Infrastructure, OracleDB, Software Development | Leave a Comment »

SQL comma bullet point formatting: because AS is optional

Posted by jpluimers on 2022/11/03

Do you see the error below?

(note: OCR via [Wayback/Archive.is] Best Free OCR API, Online OCR, Searchable PDF – Fresh 2021 OCR Software)

SELECT
  license date
  expiration_date,
  renewal_due_date
FROM license
WHERE expiration_date IS NULL
AND processing != 'Automatic'
AND edition != 'Other'
expiration_date renewal_ due date
1 2016-04-08 09:50:00 [NULL]
2 2013-11-14 11:15:00 [NULL]
3 2014-11-20 14:51:00 [NULL]
4 2017-07-21 16:00:00 [NULL]
5 2018-12-17 14:37:46 2020-12-17 14:37:46

All of the expiration_date columns have values, which is contrary to the WHERE clause. This is because the table itself contains an expiration_date column, and the SELECT part aliases license_date into expiration_date.

The result is that you see rows that have expiration_date being NULL, but license_date having a value.

So I totally agree with [Archive.is] Mathias Magnusson on Twitter: “That is one reason I’m a form believer in comma bullet point in my SQL. Problems like that has bit me far too often due to som issue with commas.… “

This is how the SQL should have looked:

SELECT
  license date
, expiration_date
, renewal_due_date
FROM license
WHERE expiration_date IS NULL
AND processing != 'Automatic'
AND edition != 'Other'

Yes indeed: an alias of a column without the AS keyword is allowed in quite a few SQL dialects (they differ even more widely in SQL extensions like SQL/PSM, T-SQL, PL/SQL, SQL_PL, or ABAB).

Aliases are for output, cannot be used in WHERE (but can in ORDER BY).

You can see what happens (and how hard this can become on one line) with these two dbfiddle queries running on Microsoft SQL Server 2019 dialect (though it works similar in other dialects):

  1. select a b, c from (values (9, 1, 2), (8, 3, 4), (7, 5, 6)) t(a, b, c) order by b
    I saved it as [Wayback/Archive.is] SQL Server 2019 | dbfiddle: select a b, c from (values (9, 1, 2), (8, 3, 4), (7, 5, 6)) t(a, b, c) order by b
    Resulting in

    b c
    7 6
    8 4
    9 2

    It shows only the column names a and b, but note the table itself is aliased to t above as well.

  2. select a, b, c from (values (9, 1, 2), (8, 3, 4), (7, 5, 6)) t(a, b, c) order by b
    I saved it as [Wayback/Archive.is] SQL Server 2019 | dbfiddle: select a, b, c from (values (9, 1, 2), (8, 3, 4), (7, 5, 6)) t(a, b, c) order by b
    Resulting in

    a b c
    9 1 2
    8 3 4
    7 5 6

    It shows only the column names a, b and c, but note the table itself is aliased to t above as well.

I really wish various SQL dialects would force the SQL syntax to be (together with a hint that the alias would overwrite an existing field):

SELECT
  license date AS expiration_date
, renewal_due_date
FROM license
WHERE expiration_date IS NULL
AND processing != 'Automatic'
AND edition != 'Other'

That is not going to happen, so the second best is to wish for tooling to hint/warn about it, and provide better syntax highlighting for it. That seems work in progress by now:

Read the rest of this entry »

Posted in Conventions, Database Development, Development, OracleDB, PL/SQL, SQL, SQL Server, T-SQL | Leave a Comment »

 
%d bloggers like this: