Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Exercises in t-SQL


Exercises in t-SQL

Author
Message
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45071 Visits: 39907
T4FF (11/15/2012)
Jeff Moden (11/14/2012)
Create a million row FizzBuzz table.


You saw this right??
T4FF (11/14/2012)who could be literally learning how to write a SELECT statement for the first time


:-D I'd scare them back to their departments with that ;-)



Jeff Moden (11/14/2012)The other exercise would be to teach people how to use Books Online and Google.

True!


Yep... saw that. That's why I suggested the FizzBuzz thing first. Too many people jump into queries to find out things like how many people have Red cars and the like before they learn the one thing that's common and absolutely essential to all computer languages... how to count. Done correctly, it teaches by revelation (This is your brain on cursors and loops. BAD cursors and loops. This is your brain on recursive CTE's that count. BAD recursive CTEs that count. This is your brain on set based code. See? It likes you!) and once the students realize that they just created a million row table in a matter of seconds, they get a sense of accomplishment and a real sense of what performance actually is. It all leads to them taking on a bit of pride and excitement that will lead them to the Red car things.

For what you're actually asking for, the best book ever for teaching the basics (in my humble opinion) is a very old book call "SQL Server 6.5 Design & Implimentation". It's absolutely loaded with some wonderful exercises along some review questions. It's out of print but you can usually find a used copy on Amazon.com. The ISBN is 1-56205-830-4. You should be able to float one for just a couple of bucks and it'll be worth every penny you spend on the shipping.

Another good source of non-intimidating exercises is W3Schools.com. It even includes "try me" windows where folks can work from home even if they don't have SQL Server installed.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
T4FF
T4FF
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 183
Ok, point taken, thanks for your additional info too, sounds interesting!
Alan.B
Alan.B
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2242 Visits: 7425
For the crowd you describe I think the SQL tutorial at W3Schools is a good start (my 2¢). Like Jeff mentioned they have a "Try it yourself" section and they even have a little, 20-question test.

-- Alan Burstein



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45071 Visits: 39907
CELKO (11/27/2012)
I would also spend more time on the DDL than most people do. Good DDL with lots of constraints, DRI actions, etc. can do 80-90% of the hard work. And have at least one exercise in Normalization.

Unfortunately, Adventureworks sucks as a schema. Its purpose was to show off as many T-SQL features as possible without much regard to Normalization, ISO standards, or basic data modeling.


Hear HERE! I strongly second those recommendations! A bit on formatting, readability, and consistancy would go a long way as well. I just got done trying to read some code written by a 3rd party and the inconsistancies in case, indentation, and style (all written by the same person) were just awful. Teach them to take pride in their code. Seeing the word "SET" spelled as "set", "Set", "SET", and "SeT" within 4 adjacenct lines of code just doesn't make sense to me especially with all of the tools available nowadays.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45071 Visits: 39907
CELKO (11/27/2012)
Create a million row FizzBuzz table. FizzBuzz isn't a comment. It's the name of a very common problem based on a child's game, is frequently given on interviews, ..


Laugh I had to do a FizzBuzz on an interview two years ago! I was appalled to be told that a lot of people failed it. The trick that even the experienced guys miss is MOD(n, 15) rather than just MOD(n, 3) and MOD(n, 5) in the CASE expression.

The other exercise would be to teach people how to use Books Online and Google.


Amen.


Heh... too funny. I've given it as an interview question a couple of times just to see what would happen. DISMAL failure! I'd be happy if someone could actually get it right even using a WHILE loop never mind something like a cteTALLY or a cross join with ROW_NUMBER. Even the ones that think of the MOD(n,15) (n%15 in SQL Server) always put it last in the CASE statement simply because it comes last in the problem description.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
wolfkillj
wolfkillj
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1104 Visits: 2582
Jeff Moden (11/27/2012)
CELKO (11/27/2012)
Create a million row FizzBuzz table. FizzBuzz isn't a comment. It's the name of a very common problem based on a child's game, is frequently given on interviews, ..


Laugh I had to do a FizzBuzz on an interview two years ago! I was appalled to be told that a lot of people failed it. The trick that even the experienced guys miss is MOD(n, 15) rather than just MOD(n, 3) and MOD(n, 5) in the CASE expression.

The other exercise would be to teach people how to use Books Online and Google.


Amen.


Heh... too funny. I've given it as an interview question a couple of times just to see what would happen. DISMAL failure! I'd be happy if someone could actually get it right even using a WHILE loop never mind something like a cteTALLY or a cross join with ROW_NUMBER. Even the ones that think of the MOD(n,15) (n%15 in SQL Server) always put it last in the CASE statement simply because it comes last in the problem description.


I'm not sure I'd think less of a coder who didn't know the "n mod 15" trick - that's a nugget of math trivia, not a fundamental programming construct.

If the coder attempting to solve the "fizz-buzz" puzzle (a) knows how to generate a set of sequential integers in T-SQL without a loop construct, (b) understands that the "fizz-buzz-fizzbuzz" requirement is most easily and efficiently satisfied in T-SQL with a CASE expression, (c) recognizes that the "fizzbuzz" condition must come first in the CASE expression, and (d) codes the "fizzbuzz" condition to produce the correct result (either "WHEN n % 3 = 0 AND n % 5 = 0" or "WHEN n % 15 = 0"), he or she has passed with flying colors, in my opinion!

In fact, I wouldn't fault a coder for getting the order of conditions in the CASE expression wrong at first as long as he or she identifies the cause of the incorrect result for multiples of 15 and corrects the error on the second try.

Jason Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45071 Visits: 39907
wolfkillj (11/28/2012)
Jeff Moden (11/27/2012)
CELKO (11/27/2012)
Create a million row FizzBuzz table. FizzBuzz isn't a comment. It's the name of a very common problem based on a child's game, is frequently given on interviews, ..


Laugh I had to do a FizzBuzz on an interview two years ago! I was appalled to be told that a lot of people failed it. The trick that even the experienced guys miss is MOD(n, 15) rather than just MOD(n, 3) and MOD(n, 5) in the CASE expression.

The other exercise would be to teach people how to use Books Online and Google.


Amen.


Heh... too funny. I've given it as an interview question a couple of times just to see what would happen. DISMAL failure! I'd be happy if someone could actually get it right even using a WHILE loop never mind something like a cteTALLY or a cross join with ROW_NUMBER. Even the ones that think of the MOD(n,15) (n%15 in SQL Server) always put it last in the CASE statement simply because it comes last in the problem description.


I'm not sure I'd think less of a coder who didn't know the "n mod 15" trick - that's a nugget of math trivia, not a fundamental programming construct.

If the coder attempting to solve the "fizz-buzz" puzzle (a) knows how to generate a set of sequential integers in T-SQL without a loop construct, (b) understands that the "fizz-buzz-fizzbuzz" requirement is most easily and efficiently satisfied in T-SQL with a CASE expression, (c) recognizes that the "fizzbuzz" condition must come first in the CASE expression, and (d) codes the "fizzbuzz" condition to produce the correct result (either "WHEN n % 3 = 0 AND n % 5 = 0" or "WHEN n % 15 = 0"), he or she has passed with flying colors, in my opinion!

In fact, I wouldn't fault a coder for getting the order of conditions in the CASE expression wrong at first as long as he or she identifies the cause of the incorrect result for multiples of 15 and corrects the error on the second try.


I consider knowing modulus fuctions to be required basic mathematical knowledge for all programmers but especially for anyone working with a database. It goes hand-in-hand with such things as knowing how to do simple integer math for grouping and I certainly don't consider it to be a trivial math function or a trick question. I agree that all of what you've stated is equally important but the knowledge of basic math functions in any programmig language is not a trivial requirement.

Still, I agree... missing one part of one question in the interview process does't guarantee a "No" by any means especially since most of the people I've asked do, in fact, get the modulus portion right and all of the rest of it wrong. :-P

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search