Limit queries in all steps to 30 lines or less, including only one code comment per instruction.
Do not pack your queries into fewer lines to get around this! Extra long exercises often make it difficult for students to focus on the learning objective. If you feel this limitation detracts from your ability to teach a concept, especially in a case study, talk to your Curriculum Manager or Content Developer about ways to approach the topic within our guidelines.
Creating tables/temp tables in one exercise, then moving it to pre-exercise code in the next. I suggest we don’t publicize this approach to instructors, as this is a great excuse to turn 30 line queries into 90 line queries. Queries really do become cumbersome very quickly.
Areas in which this may be acceptable: Case study courses, teaching students how to create crosstabs/dashboard, solving problems in other real-world scenarios.
2. Include real-world questions/problems to solve in the context of your exercise.
Teach students how to connect real-world questions to SQL queries in the context of your exercise! This is a great way to maximize the amount of information students gain and ensure they can apply the skills they learn to their own work.
3. Use our iterative exercise type to test differences between functions, filters, etc.
Iterative exercises should be used to convey one more of the following:
Differences in syntax. For example, you may want to have students practice comparing a correlated subquery vs. a join. Your output is identical, but you are comparing the length/complexity of syntax and processing time.
Differences in output. For example, you may want to demonstrate the differences in output between a CASE statement that specifies (or excludes) the ELSE clause.
4. Use our sequential exercise in one of two ways:
Build a query according to processing order (i.e., start with the subquery, add it to the main query in the next step).
Build a query to increase or refine your output.
See this article for more detail.