Search

OakieTags

Who's online

There are currently 0 users and 28 guests online.

Recent comments

SQL Server

Accelerating Your Experience

We all want more experience. The fastest way to get it is by helping solve
other people's problems.



Read the full post at www.gennick.com/database.

Accelerating Your Experience

Eleventh in a series of posts in response to Tim Ford's #EntryLevel Challenge.


We all want more experience. The fastest way to get it is by helping solve other people's problems. I'm talking here of online forums and similar venues in which you can grow in skill by taking your current knowledge and applying it to scenarios others are struggling in. Because those others will present with far more problems in a shorter period of time than you could ever cause or encounter on your own. 

Here's a snippet from a recent email I received offering some paid work:

"We would like to have someone whom we can contact who can assist us on occasion with Squarespace and are wondering how much you would charge for occasional help?"

Nice! Isn't it? 

Here's another, asking my help on a project:

The Roots of Relational

A conversation with Chris Date about the term relational, and how that term
came to be used to describe database engines such as SQL Server and Oracle
Database.



Read the full post at www.gennick.com/database.

The Roots of Relational

Tenth in a series of posts in response to Tim Ford's #EntryLevel Challenge.

Special guest this month: Chris Date

Subqueries

Subqueries can take the place of column and table references, helping you
to formulate queries that otherwise would be more difficult or less
efficient to express using join operations. 



Read the full post at www.gennick.com/database.

Subqueries

Ninth in a series of posts in response to Tim Ford's #EntryLevel Challenge.


Subqueries are queries within a query – one SELECT nested within another. They can take the place of column and table references, helping you to formulate queries that otherwise would be more difficult or less efficient to express using join operations. 

As Columns

Following is an example of a subquery filling a position typically occupied by a column name or expression. The goal of the query is to list the number of products per subcategory.

SELECT ps.name,
    (SELECT COUNT(*)
    FROM Production.Product p
    WHERE p.ProductSubcategoryID = ps.ProductSubcategoryID) AS sub_count
FROM production.ProductSubcategory ps

The output looks as  follows:

Surprise Attack of the Nulls: Hidden Traps in the NOT IN

Eight in a series of posts in response to Tim Ford's #EntryLevel Challenge.


My Fear Nothing post last month introduced some of the trouble that nulls can cause by generating results that are counterintuitive to what you would expect from a superficial reading of a query. This month we’ll go deeper into the rabbit hole, because the situation with nulls is rather worse than you might think. 

Let’s imagine for a moment that we’re interested in answering this business question:

Surprise Attack of the Nulls: Hidden Traps in the NOT IN

Nulls are tricky, and their effects go beyond query formulation to affect
even the very the business questions you’re able to pose. Business
questions must be formulated with nulls in mind. 



Read the full post at www.gennick.com/database.

Fear Nothing

Null represents the absence of a value in a database column. Null means no value at all, and in that sense null can be thought of as nothing. Should you fear the nothing? Yes, indeed! Because nulls lead to three-valued logic, which is more like a three-headed monster because of all the unintended, counterintuitive, and often just plain wrong results it can cause.

Counterintuitive Results

Following is a contrived example showing how nulls can lead to counterintuitive results. Execute the query and check the number of rows that are returned. Compare to the total number of products. Evidently there is a large quantity of products that are simultaneously not red and not some other color. How can this be?

Fear Nothing

Three-valued logic arising from nulls in the database is like a
triple-headed monster attacking your queries through unintended and often
wrong results. 



Read the full post at www.gennick.com/database.