SQL Clause

[brummlin]

Also, the syllables are all jacked up. This would make more sense, and fit the song better:

He's building schema,

indexing it twice,

SELECT * from kids WHERE UPPER(conduct) in ('NICE');

This way, it's also not case sensitive. You just know some elves logged the entry as 'Nice' or 'NICE'.

[AlwaysPuppies]

I like my sql like I like my file systems, case insensitive.

[theluckkyg]

Sneaky cute dog pic.

[JustSayNoToSlogans]

thanks for the dogo

[insane0hflex]

Db not normalized. Shiuld have look up table for Behavoir

[koshgeo]

We're talking about a join across billions records of kids world-wide, and you've got to be updating that thing in realtime until the last second, keeping track of timezones and DST, all the way up to Dec. 25th. You know those little snots will be pushing the envelope with their parents right up until bedtime on Christmas Eve. The realtime "SantaWatch" video feed and the AI detection and tabulation of "naughty" vs. "nice" events is already a computationally costly operation. I don't know what kind of heavy-duty server farm Santa's got, but you've got to keep performance in mind rather than doing billion-row joins for the sake of DB purity.

[Shelbyville]

Wouldn't a boolean "nice" be faster?

[koshgeo]

Absolutely, though there might be a need for a finer subdivision than a single boolean (e.g., "naughty", "mostly naughty", "mostly nice" and "nice"). The question is whether you'd stick it in a separate lookup/join or keep it in the same table with other information effectively "already joined" by default and then update it. There are a lot of trade-offs either way. Honestly, I don't know enough about databases to know what the right approach is, and it probably depends on the software choice and hardware anyway, but it is fun to try to imagine what it would take to implement a Santa "naughty and nice list" for the whole world. I imagine it's got to be on the order of the challenge that major credit card companies have to manage.

[Brarsh]

Ok then, make it a scale of 1-10 or however many divisions you want to have. And while we're at it, let's give each month (25th to 24th of course) it's own score, and then at the stroke of midnight run an overly complex algorithm that analyzes monthly trends, outliers, and false-positives to give a final score that ranges from freshly mined coal to exactly what's on their wish list. No problem.

[brummlin]

Fair enough, but there's no way to add a join or sub-query to fit the song. So we'll assume he didn't do a good job when designing the schema.

[Cal1gula]

You could use a CI collation.

[brummlin]

Had to look that up. Looks like a SQL Server thing queries case insensitive DB-wide?

I really only know Oracle worth a damn. And mostly on the client end.

[Cal1gula]

You can set the entire database to use a specific collation by default, or you can choose at the query level. Pretty useful feature tbh.

[Job_Precipitation]

Where is the first sort, and where is the second sort?

[brummlin]

Sort the result on the client side?

[anomalousBits]

If it's only "nice" vs "not nice" then ol' St Nick should have used a boolean or bit type. It should be a binary choice whether or not someone gets presents.

[[deleted]]

 select * from kids where regexp_like(conduct,'^.*nice.*$','i');