CODEWITHSUNDEEP
sqlsql-server-2008
David Dziezynski
David Dziezynski

Reputation: 11

Writing a SELECT statement to determine invalid values?

I'm taking a Database Design course online this semester, and this is my first time using SQL. I graduated with a degree in communications, but I'm taking certain computer science classes to help myself. We're using Microsoft SQL Server 2008, and I'm stumped on the last problem of our exercises. First 6 were a breeze (basic select functions, ordering the results, using aliases to rename tables, etc), but the last one deals with null values.

It states:

Write a SELECT statement that determines whether the PaymentDate column of the Invoices table has any invalid values. To be valid, PaymentDate must be a null value if there's a balance due and a non-null value if there's no balance due. Code a compound condition in the WHERE clause that tests for these conditions.

Don't even know where to begin. Ha ha. I typically learn better in a classroom setting, but my schedule would not allow it with this course, so any explanation would help as well! Any help is appreciated!

Dave D.

So which one is correct? It's difficult to break it down when there's two different answers :) On my day off I'm gonna head to the professor's office so she can explain it to me in person anywho lol

Upvotes: 1

Views: 3724

Answers (2)

tchrikch
tchrikch

Reputation: 2468

The code below will select the records with invalid PaymentDate 

SELECT * FROM Invoices WHERE (PaymentDate is not null and BalanceDue is not null) or (PaymentDate is null and BalanceDue is null)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270391

Because there is an incorrect answer already posted, I'm going to walk through this.

This is a question of logic, that says that one of PaymentDate or BalanceDue are null. In SQL, you test for NULL with the expression IS NULL.

So, the where clause for this would look like:

where (PaymentDate is null and BalanceDue is not null) or -- this is the first clause
      (PaymentDate is not null and BalanceDue is null)    -- this is the second clause

Any other comparison with a NULL value (=, <>, <, <=, >, >=, or in) return NULL boolean values, which are interpreted as FALSE.

Best of luck learning SQL.

Upvotes: 1

Related Questions