> It is scaresly possible to make this any *more* simple. (I guess you'd 
> have to have something with no PK/FK relationships at all - e.g., "list 
> all customers born after 1980" or something.)
>
> Seriously, this is probably the most *basic* thing you can ask SQL to do. 
> Jesus... AND you've allowed to use the SQL Server CHM files.
>
> Apparently "I've had people come in here claiming to have 20 years' 
> experience with SQL who were unable to do that test as well as you just 
> did". WTF?? O_O

OK so as an SQL beginner let me ask if this is possible.

Say you have lots of experiments, and each experiment is made up of several 
events repeated some number of times (like over-temperature, switched-off, 
switched-on etc).

I'm thinking that I make a table for events first, like:

EventID, Description
0, Switched on
1, Switched off
2, Over-temperature warning
...

Then I make another table for experiments

ExperimentID, StartDateTime, DeviceNumberTested, Description
0,blahblah,53535,"Test 1"
1,blahblah,23112,"Test 2"
...

You get the idea.

Then I should make another table to hold all the events that occur in all 
the experiments.

ExperimentID,EventDateTime,EventID
0,blah,1
0,blah,0
0,blah,2
0,blah,1
1,blah,1
...

OK.  That all seems quite basic to me so far and seems the best way to 
organise things.

Now, is it possible to do a search using SQL that does something like this: 
Find all the times that an experiment has gone over temperature while 
switched on (ie after a switched on event but not after a switched off 
event)?  This is beyond my basic knowledge how I would do such a thing 
without writing some code to manually search the results and checking times.

Post a reply to this message

scott wrote:

> OK so as an SQL beginner let me ask if this is possible.
> 
> Say you have lots of experiments, and each experiment is made up of 
> several events repeated some number of times (like over-temperature, 
> switched-off, switched-on etc).
> 
> I'm thinking that I make a table for events first, like:
> 
> EventID, Description
> 0, Switched on
> 1, Switched off
> 2, Over-temperature warning
> ...
> 
> Then I make another table for experiments
> 
> ExperimentID, StartDateTime, DeviceNumberTested, Description
> 0,blahblah,53535,"Test 1"
> 1,blahblah,23112,"Test 2"
> ...
> 
> You get the idea.
> 
> Then I should make another table to hold all the events that occur in 
> all the experiments.
> 
> ExperimentID,EventDateTime,EventID
> 0,blah,1
> 0,blah,0
> 0,blah,2
> 0,blah,1
> 1,blah,1
> ...
> 
> OK.  That all seems quite basic to me so far and seems the best way to 
> organise things.
> 
> Now, is it possible to do a search using SQL that does something like 
> this: Find all the times that an experiment has gone over temperature 
> while switched on (ie after a switched on event but not after a switched 
> off event)?  This is beyond my basic knowledge how I would do such a 
> thing without writing some code to manually search the results and 
> checking times.

Mmm, difficult to see, the Dark Side is...

I would suggest a 3-way self-join of the event table with itself. Find 
all occurrances of event #0, event #1 and event #2 in the same 
experiment, with constraints on the time orderings therein.

If that makes sense?

Post a reply to this message

>> Then I should make another table to hold all the events that occur in 
>> all the experiments.
>>
>> ExperimentID,EventDateTime,EventID
>> 0,blah,1
>> 0,blah,0
>> 0,blah,2
>> 0,blah,1
>> 1,blah,1
>> ...
>>
>> OK.  That all seems quite basic to me so far and seems the best way to 
>> organise things.
>>
>> Now, is it possible to do a search using SQL that does something like 
>> this: Find all the times that an experiment has gone over temperature 
>> while switched on (ie after a switched on event but not after a 
>> switched off event)?  This is beyond my basic knowledge how I would do 
>> such a thing without writing some code to manually search the results 
>> and checking times.
> 
> Mmm, difficult to see, the Dark Side is...
> 
> I would suggest a 3-way self-join of the event table with itself. Find 
> all occurrances of event #0, event #1 and event #2 in the same 
> experiment, with constraints on the time orderings therein.
> 
> If that makes sense?

SELECT *
FROM EventRecord AS X, EventRecord AS Y, EventRecord AS Z
WHERE
   X.ExperimentID = Y.ExperimentID AND
   Y.ExperimentID = Z.ExperimentID AND
   X.EventID = 0 AND
   Y.EventID = 2 AND
   Z.EventID = 1 AND
   X.EventDateTime < Y.EventDateTime AND
   Y.EventDateTime < Z.EventDateTime;

Or similar. (It's been a while since I did SQL...)

Post a reply to this message

>> Maybe a few decades ago, but not anymore.
>
> I thought it was more like a few centuries?

Don't think so, ask your dad how what % of the population went to University 
when he was that age.

> Hey, if *I* can actually get one, it can't be that rare.

What % of people in Milton Keynes do you think can program in a functional 
language, know half of what you know about knot theory, and half of what you 
know about fourier transforms etc?  What % would even be capable of 
understanding?  I can tell you, not many.

Post a reply to this message

> SELECT *
> FROM EventRecord AS X, EventRecord AS Y, EventRecord AS Z
> WHERE
>   X.ExperimentID = Y.ExperimentID AND
>   Y.ExperimentID = Z.ExperimentID AND
>   X.EventID = 0 AND
>   Y.EventID = 2 AND
>   Z.EventID = 1 AND
>   X.EventDateTime < Y.EventDateTime AND
>   Y.EventDateTime < Z.EventDateTime;
>
> Or similar. (It's been a while since I did SQL...)

Oh cool that helps a lot, I'm copying and pasting that to a file somewhere 
for reference, thanks.  I still need to order myself an SQL book...

I guess that's the sort of question they should ask you at an interview :-)

Post a reply to this message

scott wrote:
>> SELECT *
>> FROM EventRecord AS X, EventRecord AS Y, EventRecord AS Z
>> WHERE
>>   X.ExperimentID = Y.ExperimentID AND
>>   Y.ExperimentID = Z.ExperimentID AND
>>   X.EventID = 0 AND
>>   Y.EventID = 2 AND
>>   Z.EventID = 1 AND
>>   X.EventDateTime < Y.EventDateTime AND
>>   Y.EventDateTime < Z.EventDateTime;
>>
>> Or similar. (It's been a while since I did SQL...)
> 
> Oh cool that helps a lot, I'm copying and pasting that to a file 
> somewhere for reference, thanks.

It seems so easy when you already know how, eh? ;-)

> I still need to order myself an SQL book...

You wanna know what I have on *my* bookshelf?

http://www.amazon.com/Introduction-Database-Systems-C-Date/dp/0201385902

As I understand it, this is considered one of the "seminal texts" on 
relational databases. Take a look at those stats: it's almost a thousand 
pages, and weighs over 1.6 Kg. In other words, it's a *tome*.

And I've read it, cover to cover.

Even I cannot comprehend the relational calculus though. Relational 
algebra, sure. But relational calculus? Nah.

> I guess that's the sort of question they should ask you at an interview :-)

What, as opposed to "what is the unladen velocity of a swallow?"? 0;-)

Post a reply to this message

>>> Maybe a few decades ago, but not anymore.
>>
>> I thought it was more like a few centuries?
> 
> Don't think so, ask your dad how what % of the population went to 
> University when he was that age.

Mmm, OK. Well I certainly don't know, I just thought that degrees being 
rare dated back to the days when reading and writing was uncommon.

BTW, apparently my dad has a qualification in technical drawing. o_O

>> Hey, if *I* can actually get one, it can't be that rare.
> 
> What % of people in Milton Keynes do you think can program in a 
> functional language, know half of what you know about knot theory, and 
> half of what you know about fourier transforms etc?  What % would even 
> be capable of understanding?  I can tell you, not many.

On the other hand, I sat in a room full of about 80 people, and almost 
all of them got a Computer Science degree. Most of them *still* don't 
know what a "segmentation fault" actually is, but they all got their 
degrees.

Hell, most of them weren't even all that interested in computers. (Uh... 
WTF?) I never did figure that one out!

Anyway, as to what percentage of the population understand the Fourier 
Transform, I wouldn't like to say. I don't meet many people... I'm sure 
in any kind of competetive process though, you're going to meet a hell 
of a lot of them.

Post a reply to this message

> What, as opposed to "what is the unladen velocity of a swallow?"? 0;-)

I'd say about 30mph because when driving at that speed sometimes birds 
appear to be going about the same speed :-)

Some of the gems I've heard:

Estimate the mass of air in this room.
Would birds be hotter or colder with fur on their legs?
Draw x^infinity + y^infinity = 1
Prove which is bigger, e^pi or pi^e

But the best one was at my university interview, the guy had given me some 
quite tricky geometry/trig problem to solve and I think I almost had the 
answer, but the equation was a huge mess and I was dreading to try and solve 
it for x (or whatever, it had squares and cubes all over the place).  But 
then at that point he says "great, you seem to have the answer if you just 
tidy it up a bit, let's move on".  Was the best I could have hoped for!

Post a reply to this message

On 20-2-2009 14:30, Invisible wrote:
>>> The question is: Do you think I should do a PhD?
>>
>> Yes, but not in computer science. I suggest something applied. Not 
>> because you like it, but because you could be good at it and it would 
>> force you to meet other people.
> 
> So, what you're saying is, I'd be doing something I don't want to do, 

No I said you would not do the things that you now think that you would 
want to. Because I think you have the wrong impression of what you like.  ;)

> and being forced to get yelled at by a bunch of people? 

Last time somebody yelled at me... let me think... never happened.
Last time I yelled at somebody ... also never happened.
What makes you think working includes yelling?

> And yet you expect me to be "good" at this? Hmm...

If you put it that way: I think you might be an expert at being yelled at.

>>> I seem to vaguely recall somebody (I forget who) claiming to know who 
>>> to go to for this kind of thing, and offering to help me arrange it. 
>>
>> If you had an MSc I am pretty sure I could have arranged it. With a 
>> BSc it may require a bit more work.
> 
> Heh, oh well.

But not impossible.

>> Oh, and it would require you to not live with your mother anymore.
> 
> Not living with the She Devil sounds dreamy. Having to copy with a whole 
> bunch of other stuff sounds less inviting.

Just wait for the full offer ;)

Post a reply to this message

> On the other hand, I sat in a room full of about 80 people, and almost all 
> of them got a Computer Science degree. Most of them *still* don't know 
> what a "segmentation fault" actually is, but they all got their degrees.

That's exactly my point, and is why simply having a degree doesn't really 
help you in life, they are too easy for anyone to get.

Post a reply to this message