Some Minutes Of The SQL Brown Bag Lunch #3

[Note: This blog is part of a series on sequel learning. [1]]

After a hiatus of several months, this afternoon at work we had our third brown bag lunch, this one whose lunch from the Corner Café was provided by our company, on the subject of basic queues and joins. The meeting ended up lasting for about two hours, all told, but it got off to a bit of a slow start as we ate and as the presenter hunted for Pokèmon in the room [2]. Thus well-fed and sufficiently amused, we began our lecture, which was interrupted a couple of times by people coming in a bit late which required a bit of returning back to review material that the others had missed. The end result was thoughtful and worthwhile, and revealed as much about the person giving the presentation, and her own approach to structure and data, as it did about SQL itself, and that was a worthwhile result.

After beginning by commenting on the difference between Data Definition Language, which deals with objects like tables and databases, and Data Manipulation Language dealing with the records and data. In fact, a great deal of the discussion dealt with contrasts, as right after that we discussed the difference between truncating and deleting. Truncating deals with tables and completely deletes the material, while deleting deals with rows within tables and does not free up the data. Then after that we contrasted the structure of a query as written, beginning with the select clause, and the structure as processed, which begins with the from clause choosing the table where the data comes from. Later on there was a contrast between the where clause and the having clause, which both deal with different areas of timing. There was also the contrast between unions and joins when it came to looking at data, and even though joins are far more commonly used in sequels, I was able to find one use of a union between the call and opportunity data of one of our campaigns that would seem like an obvious opportunity for reports, even if it does mean a bit more work for the presenter and I. Even the end of the talk discussed a contrast between if-then statements and case expressions, the first of which controls the flow of an action, and the second of which provides a substitution within the data itself.

Besides the contrasting, demonstrating parallel ways of accomplishing the same task, we spent a lot of time in the Object Explorer of SSMS, which would be at least somewhat familiar to those who are familiar with the File Explorer feature of various Windows operating systems. Various queries were tested to point out where they would go wrong, and whether the amount of records would stay the same, increase, or decrease from the existing query based on the logic inherent in the queries, which was a bit of a mindbending area for many of us. We discussed subqueries, correlated and uncorrelated queries, the dangers of doing implicit joins that lead to cross joins (Cartesian cross products) that massively proliferate the number of records and that, if done particularly clumsily, can break instances and have queries running for horribly long periods of time. It was a scared straight public service announcement for budding data scientists in training. And, even though the meeting went for two hours, there was still plenty of information that was not covered because, as is often the case, there was far more to discuss than there was time to discuss it, and with some people, myself included, we always think we can cover more in less time than is actually the case.

There are many times where people write in order to present their understanding to others. Although I am aware that most of the people who read this blog are not particularly interested in data, even if it is a notable interest of my own [3], the main reason I write this post and others like it is not to brag about how nerdy I am, as if that needed any emphasis on my part and was not plainly obvious. Rather, when it comes to working with SQL and the technical matter of dealing with data, I am working at the edge of my own competence when it comes to matters of language and communication, and so I write down what I am dealing with so that I may better understand it by seeking to communicate it to others. Indeed, a great many of the writings I have undertaken have been for precisely that purpose, to understand something by attempting to communicate it, however successfully, to others. Let us hope that those few people who will stumble upon this attempt to discuss basic and trivial matters of SQL does not try the patience of those few people too much, at least.

[1] See, for example:

https://edgeinducedcohesion.wordpress.com/2016/01/14/some-minutes-of-the-sql-brown-bag-lunch-1/

https://edgeinducedcohesion.wordpress.com/2016/02/26/some-minutes-of-the-sql-brown-bag-lunch-2/

https://edgeinducedcohesion.wordpress.com/2015/12/10/some-partial-minutes-of-the-december-2015-oregon-sql-meeting/

https://edgeinducedcohesion.wordpress.com/2015/08/01/book-review-microsoft-sql-server-reporting-services-recipies-for-designing-expert-reports/

https://edgeinducedcohesion.wordpress.com/2015/05/28/book-review-professional-microsoft-sql-server-2008-reporting-services/

https://edgeinducedcohesion.wordpress.com/2015/04/23/book-review-transact-sql-cookbook/

https://edgeinducedcohesion.wordpress.com/2015/04/21/book-review-sql-in-easy-steps/

[2] See, for example:

https://edgeinducedcohesion.wordpress.com/2016/07/12/games-in-the-key-of-life/

[3] See, for example:

https://edgeinducedcohesion.wordpress.com/2016/06/22/dudda-waes-gebur-into-haedfelda/