Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Exercises in t-SQL Expand / Collapse
Author
Message
Posted Thursday, November 15, 2012 11:51 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:11 PM
Points: 35,772, Visits: 32,441
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


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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1385485
Posted Tuesday, November 27, 2012 5:02 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 6:04 AM
Points: 12, Visits: 164
Ok, point taken, thanks for your additional info too, sounds interesting!
Post #1389105
Posted Tuesday, November 27, 2012 3:05 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 6:49 PM
Points: 646, Visits: 2,995
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



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)

"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

My blog
Post #1389430
Posted Tuesday, November 27, 2012 3:41 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 12:12 PM
Points: 1,945, Visits: 3,180
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, ..


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.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1389439
Posted Tuesday, November 27, 2012 3:51 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 12:12 PM
Points: 1,945, Visits: 3,180
I was wondering whether there were any standard t-SQL exercises that I can make use of that are based on Adventureworks? Or if not, something that I could tailor for my environment?


There is always Chris Date's Supplier-Parts-Jobs (SPJ) database. I have a book on SQL Puzzles; a lot of the puzzles there are really circus tricks and not good uses for SQL. I have a few classroom things I used over the years, but ..

Anyone know of any decent content? This would be from complete beginner up...


..with a mixed group, it is hard to pick something that teaches each student something. Jeff had the best idea. Show them the procedural way, the naive SQL way and finally the declarative set-oriented good SQL way.

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.





Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1389444
Posted Tuesday, November 27, 2012 5:58 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:11 PM
Points: 35,772, Visits: 32,441
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1389475
Posted Tuesday, November 27, 2012 6:02 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:11 PM
Points: 35,772, Visits: 32,441
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, ..


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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1389476
Posted Wednesday, November 28, 2012 1:40 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 12:50 PM
Points: 1,061, Visits: 2,580
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, ..


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
Post #1390105
Posted Wednesday, November 28, 2012 5:33 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:11 PM
Points: 35,772, Visits: 32,441
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, ..


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.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1390212
Posted Wednesday, November 28, 2012 5:46 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, September 19, 2014 5:16 AM
Points: 887, Visits: 1,774
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, ..


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.


What position were the people interviewing for? was it entry level developer or some higher position?



For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2

Jeremy Oursler
Post #1390216
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse