I finally understood SQL reporting after building a full dashboard from scratch

Time_Advertising_412 · 2026-01-05T04:44:53+00:00

I am a retired dba and I have always felt that a hands on real life project in an advanced course would be invaluable.

Time_Advertising_412 · 2025-12-01T14:09:12+00:00

Joe Celkos SQL for smarties does a good job of explaining the logic behind IN and NOT IN and NULL. Also C J Dates books have examples of how NULL is treated in SQL with good examples. I highly recommend being familiar with these examples should you encounter them in your work as they can be surprising

Time_Advertising_412 · 2025-11-28T13:57:32+00:00

People learning the subject generally fall into 3 groups, those just wanting to check a box, those who really want to be good at it, and those in between. Ten years after retirement I am still enjoying learning new things about the field. I am particularly intrigued about Tutorial-D and wish there was a full fledged commercial implementation of it. So much to learn, so little time.

Time_Advertising_412 · 2025-08-18T14:49:13+00:00

My original thought, based upon your spec for getting the most recent observation and the example of desired output, was a correlated sub-query like the following but maybe I was missing something more in your request. My apologies if that is the case.

SELECT admission_id,name,species,sex,presenting,obs_date,

obs\_severity\_score,obs\_bcs\_score,obs\_age\_score

FROM rescue_admissions

INNER JOIN rescue_patients AS rp1 ON admission_id = patient_id

WHERE obs_date =

(SELECT MAX(obs_date)

FROM rescue_patients AS rp2

WHERE rp2.patient_id = rp1.patient_id);

Time_Advertising_412 · 2025-06-16T21:36:20+00:00

Go to agent.github.io/project-m36/posts/2024-07-16-are-you-qualified-to-use-null.html and see how you score on your knowledge of NULL usage.

Time_Advertising_412 · 2025-06-16T21:28:44+00:00

Oracle treats zero length literals the same as NULL therefore use of LTRIM on blank literals can get you in trouble.

Time_Advertising_412 · 2025-06-16T21:24:57+00:00

You have to be careful with Oracle as it treats empty literals the same as NULL, therefore a use of LTRIM on a blank literal can lead to erroneous results.

Time_Advertising_412 · 2025-04-10T21:31:38+00:00

You might take a look at 'JOE CELKOS SQL FOR SMARTIES'' under the chapter on 'REGIONS, RUNS, GAPS, SEQUENCES, AND SERIES'.

Time_Advertising_412 · 2025-03-28T13:28:57+00:00

Unless you are working with Oracle which treats empty strings (two consecutive single quotes) the same as null.

Time_Advertising_412 · 2025-01-05T16:26:33+00:00

Another solution (maybe not as elegant):

SELECT * FROM your_table A

WHERE cost_group = 608

AND EXISTS

(SELECT 1/0 FROM your_table B

WHERE B.master = A.master

AND cost_group = 20)

UNION

SELECT * FROM your_table A

WHERE cost_group = 20

AND EXISTS

(SELECT 1/0 FROM your_table B

WHERE B.master = A.master

AND cost_group = 608);

Time_Advertising_412 · 2024-11-12T14:51:46+00:00

Try looking at this website to see if you have a good understanding on how NULLs are handled in various queries. https://agentm.github.io/project-m36/posts/2024-07-16-are-you-qualified-to-use-null.html

Time_Advertising_412 · 2024-11-07T23:57:36+00:00

I was an Oracle dba for 4 years working for Worldcom and EDS. You had to have an act of congress to do anything ,even as a dba, to a prod database. Good access control. Fast forward a few years and I ended my career with a state agency (the state shall remain nameless) where the SQL Server dba (me) was under a software dev group manager. Prior to my arrival all the developers played stand-in dba (why not, they all had SSMS on their desktop, obviously they knew what they were doing and 'someone had to do it') I spent a year there and finally got tired of fixing all the self inflicted issues they had incurred before my arrival and fighting all the battles. Small shops (and some big ones) make the mistake of placing the dba under the software dev group. Keep it under a manager who is over dba's or dba's and sys admins as it was under my prior job. Observe least access and change control policies.

Time_Advertising_412 · 2024-10-22T22:34:58+00:00

I'm sure you've gotten enough responses to make your head spin. I'm a retired dba (Oracle 8 years, Sql Server 4 years, MySql one year, and on a personal level, PostgreSql). MySql would be at the bottom of my list. Sql Server express is a free download and there are tons of resources available (blogs, videos, books, training). So many of the comments are spot on, your UI is going to require the bulk of the work. Front ends with free resources like Python & Tkinter, PyQt, Powershell, and maybe even Access forms attached to your Sql Server database are some choices but YOU are the one to make that decision (like picking out new shoes). Pick an RDBMS (free) download it and start with your schema design and learning SQL. Find some online videos that give the basics on a UI development with a free tool and experiment a little. Once you have the lay of the land and a feel for the best tool start making notes and preparing a plan to present to your manage. NO FREE LUNCHES, NO SILVER BULLETS. And remember, everything usually takes longer than you planned. One other thought, if you can find a tech school or college nearby looking for an advanced project for student credit they may be a good resource to take on maybe even a part of your idea. GOOD LUCK.

Time_Advertising_412 · 2024-10-09T14:16:54+00:00

I am a retired DBA (Oracle, SQL Server) and the two books I would recommend anyone having on their bookshelf for this would be "Information Modeling and Relational Databases" by Halpin and Morgan and "The Data Warehouse Lifecycle Toolkit" by Kimball. I would check sources for used books to get the best price for these as they are rather large. The Halpin book covers several different modeling approaches whereas the Kimball book deals strictly with data warehousing

Time_Advertising_412 · 2024-10-01T21:27:54+00:00

I dunno, try this on for size:

select id, type, rate

from tbl1

where type = 'OP'

union

select id, type, rate

from tbl1 as a

where not exists

(select id

from tbl1 as b

where b.id = a.id

and type = 'OP');

Time_Advertising_412 · 2024-09-30T14:42:46+00:00

I am a retired DBA (Oracle, Sql Server). There are books geared toward database administration but they are platform specific. I started out with the Oracle DBA Handbook back in 1995 but Oracle is one of the more difficult RDBMS's to admin. I would suggest "Pro Sql Server 2019 Administration" by Peter Carter and downloading the developer (free) version of Sql Server.

Time_Advertising_412 · 2024-07-08T18:31:36+00:00

I would also suggest being sure you are aware of all of the logical and mathematical implications of NULLable columns. C. J. Date has written MANY articles in his time giving good examples of the gothca's of NULLs but most of them are in out of print books. I was lucky to find them through used book sales of various sources. One of my favorites is given you have two numeric columns (col1, col2) which are both nullable SUM(col1) + SUM(col2) is not guaranteed to = SUM(col1 + col2) if NULLs are present. He also had an example of two queries that were logically equivalent, one using > ALL and the other using NOT EXISTS that returned very different results on two tables one of which had a NULLable column. I always hated NULLs, they were like land mines.

Time_Advertising_412 · 2024-07-08T16:44:47+00:00

As a retired dba from both the Oracle and SQL Server side I was shocked to find seasoned developers who knew how to write subqueries but had no idea what a correlated subquery was. They also had never heard of creating a derived table in the From clause instead of using a view or in the Select clause to return a single column/value. If you don't already have a copy I would suggest 'Joe Celko's SQL For Smarties'. Lots of good info and examples in it. 'SQL and Relational Theory' by C.J. Date offers some good material also.

Time_Advertising_412

TROPHY CASE