trying to solve tricky question

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

trying to solve tricky question

Author

Topic

KazaX
Starting Member

28 Posts

Posted - 2008-11-05 : 17:18:53

Hi there!

Does anyone have an idea how to deal with that?

Suppose we have two relations R(X, Y ) and S(X, Y ). Give an example of SQL query over R and S so that:
1. If R and S contain no nulls, it correctly computes
Project x(R) - Project x(S).
2. It does not use the IS NULL condition.
3. For some relations R and S so that Projection x(R) = {1, 2} and Projection x(S) = {1} (in particular, there are no nulls in attribute X), it produces an empty table.

thx

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-05 : 17:57:04

Sample data and expected output?

E 12°55'05.63"
N 56°04'39.26"

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-05 : 18:07:06

I think this is a homework or some sort. Project x(R) - Project x(S) is some kind of mathematical formula...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-05 : 18:10:26

And you get a golden star in your book

E 12°55'05.63"
N 56°04'39.26"

KazaX
Starting Member

28 Posts

Posted - 2008-11-05 : 18:37:28

quote:
Originally posted by hanbingl

I think this is a homework or some sort. Project x(R) - Project x(S) is some kind of mathematical formula...

Project - is basic function of Relational Algebra .... FYI ... which is somekind of declarative language

And it is not a homework....

it would be useful to generate idea than quessing what is the probability of this being homework...and etc.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-05 : 19:15:02

You must specify what you are trying to do, sir. We simply don't have the time to guess what you are trying to achieve.
Some people simply copied their homework to this forum and we just don't have the time to DO IT FOR THEM, OKay?

Now, I can only assume there exists two tables:


R            S
X   Y        X   Y 
--  --       --  --
1   1        1   3
2   2        2   4

so that x(R) = {1,2} and x(S) = {1,2}

Now, please specify how Project x(R) - Project x(S) will correctly compute in this situation so we can clearly understand what you are trying to do.

KazaX
Starting Member

28 Posts

Posted - 2008-11-06 : 04:24:50

quote:
Originally posted by hanbingl

You must specify what you are trying to do, sir. We simply don't have the time to guess what you are trying to achieve.
Some people simply copied their homework to this forum and we just don't have the time to DO IT FOR THEM, OKay?

Now, I can only assume there exists two tables:

R S
X Y X Y
-- -- -- --
1 1 1 3
2 2 2 4

so that x(R) = {1,2} and x(S) = {1,2}

Now, please specify how Project x(R) - Project x(S) will correctly compute in this situation so we can clearly understand what you are trying to do.

Hello!
thx 4 reply

here is an interpretation:

Project x(R) - Project x(S)

SELECT R.x from R
WHERE R.x NOT IN (SELECT S.x FROM Y)
---And we have extra Operations here also which we must identify---
;

So,if we assume that there is no null in both tables, and x(R) = {1,2} and x(S) = {1} then it should return 2, when we run above mentioned not completed query. But if we will have nulls in both or in one table, in this case nulls must appear in Y column of tables or one of the tables as condition specifies that in particular there are no nulls in attribute X, as a result of abovementioned notcompleted query it will return null.

do not hesitate in case of questions
cheers

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-06 : 10:28:20

Let me rephrase:
assume there exists two tables: R, S


R            S
X   Y        X   Y 
--  --       --  --
1   1        1   3
2   null

SELECT R.x from R
where R.x not in (SELECT S.x from S)
--Additional conditions----

This query will return:

X
---
2

Your question is:
How to make the result to return NO MATCH when either column Y contains NULL value without having to check IS NULL condition.

KazaX
Starting Member

28 Posts

Posted - 2008-11-06 : 11:53:19

quote:
Originally posted by hanbingl

Let me rephrase:
assume there exists two tables: R, S

R S
X Y X Y
-- -- -- --
1 1 1 3
2 null

SELECT R.x from R
where R.x not in (SELECT S.x from S)
--Additional conditions----

This query will return:

X
---
2

Your question is:
How to make the result to return NO MATCH when either column Y contains NULL value without having to check IS NULL condition.

Yeah, the query should return no match when we have null values in Y, and 2 when we do not have NULLs.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-06 : 12:01:15

[code]SELECT r.X
FROM R r
LEFT JOIN S s
ON s.X=r.X
WHERE s.X IS NULL
AND r.Y IS NOT NULL
[/code]

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-06 : 12:03:49

You can simply count the number of records in Y and a total count of table. If match that means Y do not contain any NULL values so you perform your computation.
Otherwise, do not run the query.


select r.x from R r
where not exists (select 1 from S s where s.x = r.x)
and case when 
(select count(*) from R) > (select count(y) from R) 
or
(select count(*) from S) > (select count(y) from S) 
then 0 else 1 end = 1

KazaX
Starting Member

28 Posts

Posted - 2008-11-06 : 12:51:04

quote:
Originally posted by visakh16

SELECT r.X
FROM R r
LEFT JOIN S s
ON s.X=r.X
WHERE s.X IS NULL
AND r.Y IS NOT NULL

Even though it does not satisfy the conditions (query should not contain IS NULL), it no works if we have NULL in Y attribute of S table

KazaX
Starting Member

28 Posts

Posted - 2008-11-06 : 13:05:37

quote:
Originally posted by hanbingl

You can simply count the number of records in Y and a total count of table. If match that means Y do not contain any NULL values so you perform your computation.
Otherwise, do not run the query.

select r.x from R r
where not exists (select 1 from S s where s.x = r.x)
and case when
(select count(*) from R) > (select count(y) from R)
or
(select count(*) from S) > (select count(y) from S)
then 0 else 1 end = 1

it is kind of good solution, but the fact that query should run, where would we know that it returns nothing unless we run it. Your query works if we have assuption that no matter if we have null in query it will return null, so just avoid running.
cheers

KazaX
Starting Member

28 Posts

Posted - 2008-11-06 : 13:14:20

quote:
Originally posted by KazaX

quote:
Originally posted by hanbingl

You can simply count the number of records in Y and a total count of table. If match that means Y do not contain any NULL values so you perform your computation.
Otherwise, do not run the query.

select r.x from R r
where not exists (select 1 from S s where s.x = r.x)
and case when
(select count(*) from R) > (select count(y) from R)
or
(select count(*) from S) > (select count(y) from S)
then 0 else 1 end = 1

it is kind of good solution, but the fact that query should run, where would we know that it returns nothing unless we run it. Your query works if we have assuption that no matter if we have null in query it will return null, so just avoid running.
cheers

SELECT R.X
FROM R
WHERE R.X NOT IN (SELECT S.X FROM S)
AND (SELECT COUNT(*) FROM R) = (SELECT COUNT(Y) FROM R)
AND (SELECT COUNT(*) FROM S) = (SELECT COUNT(Y) FROM S)

I think this works.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-06 : 13:16:58

cheers

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-11-06 : 13:54:32

Careful with the not in clause when there are likely to be nulls, not using IS NULL is a strange requirement to demand of programmers. You can always cheat by using COALESCE. This is an excellent article on
NOT IN versus exists

Jim

http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources