Tuesday, May 17, 2016

Advancing with SQL

Through this week's assignments, I've developed a greater understanding of the relationships and interactions between tables.  Creating foreign keys requires establishing those tables first, as well as setting up associated primary keys, and joining tables requires consideration be given to the variety of data queries one may make.  Null values in the place of common, connecting attributes, for example, requires outer joins from left or right dependent on where the otherwise excluded pieces occur.  Other times, a simple natural join is the quickest command, if one is further specifying certain conditions be met, which would make any excluded bits of data irrelevant.

We've used spool commands to capture our output, which is very helpful for debugging, because it's far easier to re-run a file than to re-type the code.  We've also separated our creation and modification files to help to this end, which is a best practice I'll apply in the future.

I had some debugging issues with parent integrity, due to a mismatched character data type between a primary and foreign key.

Otherwise, I'm reminded that double- and triple-checking data is still sometimes not enough to catch every typo, certainly as the database grows, which is frustrating.  Sometimes, reading code backwards (right-to-left) to view data from a new perspective or taking a break and returning to review insertion commands later, can help.

It's nice to see Database Administrator jobs posted -- 189 today in SF alone on indeed.com -- and to be learning some of the skills required in the specs.  I also understand that software developers, engineers and analysts alike all use SQL in their work, more or less.

No comments:

Post a Comment