Advent Of Code 2020
Advent Of Code – Day 2: “Password Philosophy”
I just completed “Password Philosophy” – Day 2 – #AdventOfCode 2020 in pure #SQL
Advent Of Code 2020
Advent Of Code in SQL (and PL/SQL if necessary)
I’ll try to solve the #AdventOfCode2020 with SQL (and PL/SQL if necessary).
First challenge is to find the two numbers of a list that sum up to 2020 and multiply them.
#100CodeExamples
PL/SQL Object Types: putting the Structure into the Force Powers Table
Object Types are a very useful thing when used in PL/SQL, but Oracle even allows us to have them inside a table.
Let’s see how we can put the numeric_strucutre-logic inside the Force Powers table and use indizes on the new column.
#100CodeExamples
Structure Force Powers with PL/SQL Object Types
Modern SQL and especially PL/SQL is turing complete and can be used to solve every problem, but Oracle SQL Types can help greatly to do it in a much more readable way.
#100CodeExamples
More Pitfalls of the MINUS comparison
Comparing the results of 2 queries is a task more complicated than I thought initially.
I show some more pitfalls of the MINUS approach (which I still find convenient in some cases) and alternatives.
#100CodeExamples
Testing the Deathstar security with utPLSQL contexts
The context-feature of #utPLSQL can greatly help to organize your tests and reduce the setup/teardown time of tests.
Learn how to use them to test the Deathstar security system.
#100CodeExamples
#100CodeExamples
Updating a Table with the Values of a Different One
I am working professionally with databases for over 15 years now and have a huge focus on Oracle – but I really keep forgetting how to update a table with values of a different one (this is one thing which is so much easier in SQL Server by the way).
Therefore let’s assume we have a table containing planets and one containing garrisons which are on these planets.
| Garrison ID | Planet Name | Planet Faction |
| 1 | Korriban | imperium |
| 2 | Korriban | imperium |
| 3 | Dromund Kaas | imperium |
| 4 | Hoth | republic |
We would now like to have a new column in the garrisons table which can contain a name.
alter table garrisons add name varchar2(300)
The imperial side now has a request to update all their garrisons with a name according to this schema: <PlanetName> (<Garrison ID>)
(more…)#100CodeExamples
Pitfalls of the MINUS-comparison
The MINUS comparison of two views (or other statements) is a powerful tool to check the validity of a new, rewritten approach I use often.
However, be aware of
#100CodeExamples
#100CodeExamples – Constants in PL/SQL and SQL
Constants help us lazy devs in many ways, reduce the likelyness of unnoticed typos and prevent a given value from changing.
While PL/SQL supports them, it’s a bit tricky to get them into SQL.
#100CodeExamples
#100CodeExamples – Only one active protocol
For those who didn’t know: the death star runs on one of several protocols, but only one may be active at a time.
This can be easily achieved with simple tools most relational databases provide.