June 14, 2010 at 8:24 am
scott.pletcher (6/14/2010)
Jeff has made two significant points here, that the challenges are unrepresentative of real-world coding and also that they may not be the best means to advance the capabilities of a TSQL novice.
He made that claim. That doesn't mean it's proven. I've seen whole books of challenges used to help people learn advanced SQL. So all those authors, editors and book publishers are wrong??
Maybe Jeff just doesn't want to consider anything I say long enough to understand that learning a technique can be useful in itself to apply to another situation, regardless of the "purity" of the original code used to learn the technique.
It depends 😉
Personally I wouldn't recommend the Challenges to a novice in favour of more solid experience of the fundamentals of TSQL. Muscle memory. Whole books of challenges might easily meet that requirement, so long as the target audience includes the capabilities of our OP.
Keep novices well away from the Sneaky Tricks Department until they're in a position to judge when they should be used.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 14, 2010 at 8:28 am
All,
I wasn't trying to start a fight. I was just wondering if there was a good hearted individual that would be willing to help me learn to think through things so that I could learn. I am a martial arts instructor and if I were to tell my students to simply read they would never advance but when I have them study and I show them the reason behind the technique they learn very rapidly.
JD
June 14, 2010 at 8:29 am
scott.pletcher (6/14/2010)
Sigh. Consistently all you want to offer is pompous air.
Scott, you simply don't know what you're saying. If you have some arguments, go on, but I suggest you to stop offending Jeff.
He helped many of us and earned our respect and friendship.
What have you done so far to deserve the same? Not much, I would dare to say.
-- Gianluca Sartori
June 14, 2010 at 8:30 am
Keep novices well away from the Sneaky Tricks Department
This is not a Sneaky technique, just full use of what's available from SQL.
The great part is that this was a real-world situation. When I taught databases and SQL at night at a local college, one of the other instructors gave three tests per semester. He wanted such a list.
So afterward I configured it into a challenge for the students. Many of them were amused by 'studid'. I'm not a pedant like Jeff -- I'm willing to inject a little levity if it helps people learn about databases. Oh the horror!!
Scott Pletcher, SQL Server MVP 2008-2010
June 14, 2010 at 8:38 am
scott.pletcher (6/14/2010)
I'm not a pedant like Jeff
I see you decided not to take my advice.
scott.pletcher (6/14/2010)
Many of them were amused by 'studid'.
Object and column names are part of the database design. Try asking Joe Celko what he thinks of such a column name.
I'm not surprised they were amused.
-- Gianluca Sartori
June 14, 2010 at 8:50 am
Scott, Jeff could you possibly take your argument elsewhere please. It's getting a little off topic and we're likely scaring away a new member.
Thanks
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 14, 2010 at 8:52 am
OK.
Scott Pletcher, SQL Server MVP 2008-2010
June 14, 2010 at 9:17 am
Scott, I can't see how solving such challenges help to learn SQL in deep. However, they might be good for a starting point plus they do provide some entertainment to offset very boring everyday problems (especially the really challenging ones :-D).
create table studentgrade (studid int, test int, score int)
insert studentgrade
select 1 , 1, 10
union select 2,1,10
union select 2,2,20
union select 3,1,30
union select 3,2,40
union select 3,3,50
select studid, count(*) as NbrOfTest
,max(score) as hiscore
,case when count(*) = 1 then null
when count(*) = 2 then max(case when test = 1 then null else score end)
else avg(score) end as midscore
,case when count(*) = 3 then min(score)
else null
end as LowScore
from studentgrade
group by studid
I hope "mid score" you mentioned is the average one...
June 14, 2010 at 9:25 am
Excellent start!
No, sorry for not being more clear, the 'mid score' is the middle score. That is, if the scores are 10, 100, 80, the middle score is 80.
You're right, I did leave out the avg, which the original list did include. It should be:
hi, middle, low, avg
Scott Pletcher, SQL Server MVP 2008-2010
June 14, 2010 at 9:26 am
Scott, I can't see how solving such challenges help to learn SQL in deep.
Joe Celko has several (I think) excellent books along these lines.
Scott Pletcher, SQL Server MVP 2008-2010
June 14, 2010 at 9:29 am
john-902052 (6/11/2010)
Because I want to get to know the person and go deep with the topic. Here people may or may not answer and only will work against a small defined problem that won't get me to the level of learning that I am after. I can read books to solve individual select problems, it is those problems that require experience and insight that I am after.
Through SSC I have gotten to know a bunch of people. Sure it is all in the virtual world, but we exchange pms, emails, tweets and help each other in the forums. I may not have hung out or had dinner with them, but I have had the opportunity to get to know some people pretty good here. Most of them will answer questions if they see it or will find somebody else who may be able to help.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 14, 2010 at 9:38 am
create table #studentgrade (studid int, test int, score int)
insert #studentgrade
select 1 , 1, 10
union select 2,1,10
union select 2,2,20
union select 3,1,30
union select 3,2,40
union select 3,3,60
select studid, count(*) as NbrOfTest
,max(score) as hiscore
,case when count(*) = 1 then null
else max(case when test = 1 or test = 3 then null else score end)
end as midscore
,case when count(*) = 3 then min(score)
else null
end as LowScore
,avg(score) as avgscore
from #studentgrade
group by studid
But! What the value of it?
"Each student may have taken anywhere from 0 to 3 (never more) tests."
What about if next year number of tests will change (word "never", in real life, can only be applied to the fixed, sertian things like: Microsoft SQL Server is always better than Oracle Oracle :-D), and how on earth you can show in this table that the student did not take any of tests? Will you have a record with test# 0 ? That would be the best ever table design possible...
June 14, 2010 at 9:44 am
You can't go by test#: the middle score might have occurred on any test 🙂 .
This was a request for a specific situation. It's not a design q, was not presented as one, and is not intended to be one; it's a query q for the specific conditions given.
Scott Pletcher, SQL Server MVP 2008-2010
June 14, 2010 at 10:13 am
The most challenging part of your challenge was trying to understand given written requirements in the same way as you (and that is reall life challenge working with BA's :-D):
scott.pletcher (6/11/2010)
studentid# of tests taken
hi score (if only one test, it shows here)
mid score (if only two tests, 2nd shows here; if only one test, will be null)
low score (will only be non-null if all 3 tests are found for that student)
You have stated above that you want to see the result of 2nd test as mid score. Of cause, it is very clear to programmer that in reality you mean 2nd largest result not the result of the 2nd test (sorry, English is not my native language, you can blaim me)
declare @studentgrade table (studid int, test int, score int)
insert @studentgrade
select 1 , 1, 10
union select 2,1,10
union select 2,2,20
union select 3,1,30
union select 3,2,60
union select 3,3,40
select studid, count(*) as NbrOfTest
,max(score) as hiscore
,case when count(*) = 1 then null
when count(*) = 2 then max(score)
else sum(score) - max(score) - min(score)
end as midscore
,case when count(*) = 3 then min(score)
else null
end as LowScore
,avg(score) as avgscore
from @studentgrade
group by studid
June 14, 2010 at 10:23 am
Do you want the real challenge?
How to get End-Of-Business Day Friday report that includes the data which will be inserted into the database only on the following Monday?
:w00t:
Viewing 15 posts - 16 through 30 (of 54 total)
You must be logged in to reply to this topic. Login to reply