sql homework solutions

SQL Tutorial

Sql database, sql references, sql examples, sql exercises.

You can test your SQL skills with W3Schools' Exercises.

We have gathered a variety of SQL exercises (with answers) for each SQL Chapter.

Try to solve an exercise by filling in the missing parts of a code. If you're stuck, hit the "Show Answer" button to see what you've done wrong.

Count Your Score

You will get 1 point for each correct answer. Your score and total score will always be displayed.

Start SQL Exercises

Start SQL Exercises ❯

If you don't know SQL, we suggest that you read our SQL Tutorial from scratch.

Kickstart your career

Get certified by completing the course

COLOR PICKER

Contact Sales

If you want to use W3Schools services as an educational institution, team or enterprise, send us an e-mail: [email protected]

Report Error

If you want to report an error, or if you want to make a suggestion, send us an e-mail: [email protected]

Top Tutorials

Top references, top examples, get certified.

TSQLBeginners

A tsql course for beginners, tsql for beginners, jump to section, course summary, course slack discussion, pre-requisites, introducing selects and aliasing, solutions for select homework and introduction to joins, solutions for joins homework and learn group by and case.

• Solutions for Group By and CASE; then CTEs, Subqueries, Derived Tables, Oh My!

Solutions for CTEs and Friends, then Oh No, it’s Scalar User Defined Functions

Solutions for scalar udfs, then table valued functions and a little cross apply, solutions for tvfs, then ranking, numbering, and running totals with windowing functions, solutions for windowing functions, then stored procedures.

This free course introduces you to the Transact SQL language implemented in SQL Server and takes you from newbie to a master of SELECT statements. Each session of the course features a demo introducing a subject and a homework assignment to try on your own. The following session covers sample solutions for the homework previously assigned, then introduces a new subject with more homework.

The course covers:

• SELECT statements and aliasing tables
• Using WHERE clauses and JOINs
• Aggregating data using GROUP BY and CASE statements
• Common Table Expressions, Subqueries, and Derived tables
• Scalar and Table Valued User Defined Functions
• An introduction to Stored Procedures

This course is intended for anyone who wants to learn TSQL. This could be database administrators, developers, business analysts, students, IT support staff, or anyone who is interested to learn a programming language where they can work with sets of data.

Live classes will air each Wednesday on YouTube , generally at 3PM British Summer Time / 10AM Eastern.

Recordings will automagically appear in this Redgate University Community Circle Course

Need some help with your homework, or just want to chat with others in the course? You can use this Slack channel to chat while attending a course live, or afterward anytime – don’t worry if you have joined the course late, just start where you are.

• Join the SQL Community Slack group for discussion: https://t.co/w5LWUuDrqG
• Click the + next to ‘Channels’ and join #tsqlbeginners

To get started with the course, you will need to have a couple of applications installed. The course doesn’t show how to install these tools, but jumps right in under the assumption that you have already installed them successfully.

• Windows : You probably want to click ‘Download now’ under where it says ‘Developer’
• Mac : You probably want to click “Choose your installation setup” under “Docker”, which will eventually take you to this Quickstart page on how to Run SQL Server container images with Docker
• Windows : Take your pick of either.
• Mac : You want Azure Data Studio (SSMS is Windows-only)
• If using Azure Data Studio, it is very helpful to add the ‘SSMS Keymap’ extension by Kevin Cunnane. Here is how to install that .

Session Overview

We cover: Getting going in SQL Server Management Studio or in Azure Data Studio. Using databases. SELECT statements and aliasing. Bonus topic: What does ‘deprecated’ mean?

Follow along with the introductory demo using 1_SelectAndAlias_Demo.sql

Tackle the homework for next session using 2_SelectAndAlias_Homework.sql

Video timeline:

• 00:22 Course Syllabus
• 02:10 Notes on what to install to follow along
• 07:00 Basics on using SQL Server Management Studio (SSMS)
• 08:00 Info about the RAISERROR command I use at the top of the scripts
• 09:49 Using the RESTORE DATABASE script
• 16:11 Azure Data Studio (ADS) – why you might want to use this and comparing ADS to SSMS
• 17:44 Explanation of ‘USE’ and discussion of the concept of “using a database”
• 20:24 Running our first SELECT statement
• 22:24 Brief overview of schemas
• 23:15 Discussion of GO batch separator
• 25:30 Discussion of semi-colon terminators
• 27:34 Why “SELECT *” is an anti-pattern
• 32:05 A brief return to SSMS to demo dragging object names and column names over from Object Explorer (this doesn’t work in Azure Data Studio, but it does have good object auto-completion for typing)
• 35:28 What square[brackets] do around object names, and how they can allow you to use special characters (with a “stupid pet trick” demo of how to create a database with a space as its name)
• 39:00 Example of using a column alias with ‘AS’
• 40:48 Discussion of when you want to use a column alias and why
• 41:20 Demo of aliasing without the word ‘AS’ – and a warning of how this can be done accidentally
• 43:09 Demo of aliasing with reversed order and “=” (and a little discussion of ANSI compliance)
• 47:43 A quick step through of the “quiz” questions in the demo file
• 51:17 Demo of aliasing a table, and comparing three and two part names
• 53:30 Recap of the “quiz” questions on table name aliasing
• 56:08 Brief introduction of next week’s homework

We cover sample solutions for ‘SELECTS and Aliasing’, then introduce inner joins outer joins (left, right, full), cross joins.

Sample solutions for ‘SELECTS and Aliasing’ are in 3_SelectAndAlias_Solutions.sql

Follow along with the demo using 4_Joins_DiagramsForHomework.ipynb – this is a Jupyter notebook file which will open in Azure Data Studio

Try your hand at the homework with 5_Joins_Homework.sql

• 00:52 Overview of the course files on GitHub
• 01:30 View of the Azure Data Studio Jupyter notebook as it displays in GitHub
• 03:25 Using the WideWorldImporters database
• 03:40 Solution to Question 1 in 2_SelectAndAlias_Solutions.sql
• 05:11 What execution plans are and why I’m showing you some
• 08:40 the sp_help procedure, and why it’s useful
• 10:00 Computed Columns
• 12:30 Solution to Question 2 in 2_SelectAndAlias_Solutions.sql
• 15:25 Solution to Question 3 in 2_SelectAndAlias_Solutions.sql (and the controversy of NULLS, with an overview of NVARCHAR for good measure)
• 22:54 ANSI NULLS and why you shouldn’t turn it off
• 24:44 Solution to Question 4 in 2_SelectAndAlias_Solutions.sql
• 28:00 Solution to Question 5 in 2_SelectAndAlias_Solutions.sql
• 30:23 Solution to Question 6 in 2_SelectAndAlias_Solutions.sql and a discussion of collation
• 37:31 Solution to Question 7 in 2_SelectAndAlias_Solutions.sql and an intro to ORDER BY
• 41:52 Solution to Question 8 in 2_SelectAndAlias_Solutions.sql with intro to OFFSET and FETCH
• 44:19 Demo that your TSQL syntax generally doesn’t determine a specific execution plan
• 46:19 Solution to Question 9 in 2_SelectAndAlias_Solutions.sql
• 49:40 Review of where column aliases DON’T work
• 51:22 JOINs discussion begins here!
• 53:00 Overview of the joins sample database and an explanation of the Jupyter notebook
• 54:50 INNER join: matching rows from both tables
• 59:37 LEFT OUTER join: rows from the ‘left’ table with matching rows from the ‘right’ table when present
• 01:01:48 RIGHT OUTER join: rows from the ‘right’ table with matching rows from the ‘left’ table when present
• 01:04:44 FULL OUTER join: return all rows from each ‘side’, matching where possible
• 01:07:18 Simplify our data a bit, and then CROSS JOIN
• 01:09:40 Recap of the joins and info about homework for next time

We step through sample solutions for JOINs homework, then introduce GROUP BY clauses and CASE statements.

Sample solutions for JOINs homework are in 6_Joins_Solutions.sql

Follow along with the demo using 7_GroupingAndCase_DiagramsForHomework.ipynb – this is a Jupyter notebook file which will open in Azure Data Studio

Try your hand at the homework with 8_GroupingAndCase_Homework.sql

• 00:00 Showing the scripts covered in the syllabus
• 01:40 Recapping the tables used in the homework in 6_Joins_Solutions.sql and how to know which join columns to use on tables
• 06:40 Solution to Question 1 in 6_Joins_Solutions.sql
• 11:16 Solution to Question 2 in 6_Joins_Solutions.sql (left and right outer joins)
• 15:20 Solution to Question 3 in 6_Joins_Solutions.sql
• 23:50 Solution to Question 4 in 6_Joins_Solutions.sql
• 15:22 Solution to Question 5 in 6_Joins_Solutions.sql
• 27:00 Solution to Question 6 in 6_Joins_Solutions.sql
• 18:18 Solution to Question 7 in 6_Joins_Solutions.sql
• 33:30 Introduction to GROUP BY and Case begins with an overview of logical processing order
• 33:50 Setting up the same table in 7_GroupingAndCase_DiagramsForHomework.ipynb (a Jupyter notebook you can open and use in Azure Data Studio)
• 35:48 GROUP BY and COUNT()
• 37:55 GROUP BY and MAX()
• 39:07 GROUP BY and MIN()
• 40:25 GROUP BY and AVG() with a discussion of rounding INTs
• 43:09 GROUP BY and HAVING
• 46:40 CASE - the “CASE colname when” syntax
• 49:16 CASE - the “CASE WHEN condition” syntax
• 50:31 CASE - the “CASE WHEN condition” syntax with compound logic in the condition, with an example of concatenation and notes on NULL’s impact on combining strings
• 53:23 Combining GROUP BY and CASE
• 55:53 Brief overview of homework

Solutions for GROUP BY and CASE; then CTEs, Subqueries, Derived Tables, Oh My!

We’ll go through sample solutions for the GROUP BY and CASE homework, then dig into differnet ways to nest/combine queries in your queries: Common Table Expressions, Subqueries, and Derived Tables

Sample solutions are in 8_GroupingAndCase_Homework.sql

Follow along with the demo using 9_GroupingAndCase_Solutions.sql

Try your hand at the homework with 11_CTEsSubqueriesDerivedTables_Homework.sql

• 00:20 Accidental demo of a bug in Azure Data Studio where a connection “goes bad”
• 07:08 Solution to Q1 from 9_GroupingAndCase_Solutions.sql with a discussion of data types and the AVG function
• 10:30 Solution to Q2 from 9_GroupingAndCase_Solutions.sql - discussion of COUNT ALL vs COUNT DISTINCT
• 14:06 Solution to Q3 from 9_GroupingAndCase_Solutions.sql - discussion showing that multiple syntaxes can result in the same query execution plan being used behind the scenes
• 18:16 Solution to Q4 from 9_GroupingAndCase_Solutions.sql - discussion of HAVING clauses
• 21:05 Solution to Q5 form 9_GroupingAndCase_Solutions.sql - working with CASE
• 24:29 Solution to Q6 from 9_GroupingAndCase_Solutions.sql - which solution do you like better? Comparing a CASE solution and a COUNT solution
• 28:28 Bonus topic - using EXCEPT and INTERSECT to compare result sets from different ways to write a query
• 31:43 Demo on CTEs, Subqueries, and Derived tables begin: setting up sample data
• 34:00 An example problem query – why HAVING isn’t enough and we need to get more complex
• 36:21 Introduction to Common Table Expressions (CTEs)
• 40:03 More with CTEs: you may use multiples, and they may refer to a prior CTE * 41:50 Derived table example
• 43:43 Correlated subquery example
• 47:54 Subquery in the select example
• 51:40 Setup for next week’s homework, 11_CTEsSubqueriesDerivedTables_Homework.sql

This time, we talk through sample solutions for the previous week, then give an introduction to making code reusable using functions. We’ll include big-picture information on why functions are controversial in SQL Server and how to be successful with them.

Sample solutions are in 12_CTEsSubqueriesDerivedTables_Solutions.sql

Follow along with the demo using 13_ScalarUDFs_Demo.sql

Try your hand at the homework with 14_ScalarUDFs_Homework.sql

We’ll chat through solutions for the Scalar User Defined Function (UDFs) challenges, then I’ll show how Table Valued Functions (TVFs) put the “fun” in functions. No, really, they do! We’ll also cover a bit of CROSS APPLY as we discuss TVFs.

Sample solutions are in 15_ScalarUDFs_Solutions.sql

Follow along with the demo using 16_TVFs_Demo.sql

Try your hand at the homework with 17_TVFs_Homework.sql

We’ll wrap up our discussion of functions by working through the homework, then it’s time for another favorite: Windowing Functions!

Sample solutions are in 18_TVFs_Solutions.sql

Follow along with the demo using 19_WindowFunctions_Demo.sql

Try your hand at the homework with 20_WindowFunctions_Homework.sql

In our last session, we’ll go through our homework on window functions, then do a gentle introduction to Stored Procedures. Want to try the stored procedure homework yourself before seeing the solutions? Totally fine, you can pause the video at that point.

Sample solutions for Windowing Functions are in 21_WindowFunctions_Solutions.sql

Follow along with the demo using 22_StoredProcedures_Demo.sql

Try your hand at the homework with 23_StoredProcedures_Homework.sql

Sample solutions for Stored Procedures are in 24_StoredProcedures_Solutions.sql

• Unlocking Solutions to SQL Woes: Troubleshooting Common Student Queries

SQL Troubleshooting: Common Student Queries Answered

Mastering SQL is essential for students in database-related courses, yet the journey is often marked by challenges. This blog addresses common troubleshooting queries encountered by students, offering insightful solutions to enhance their efficiency in navigating through assistance with your SQL homework . As we explore the intricacies of SQL troubleshooting, our aim is to provide students with the tools and knowledge needed to overcome hurdles and excel in their database-related endeavors. Join us in unraveling the complexities of SQL, transforming challenges into opportunities for growth and mastery.

SQL syntax errors can be perplexing for students. In this section, we dissect the most common mistakes, providing clear explanations and practical examples. By unraveling the intricacies of syntax errors, students gain a solid foundation for troubleshooting and correcting these issues in their SQL code. To enhance comprehension, we present real-world examples of SQL syntax errors and guide students through the step-by-step process of rectifying them. These practical insights not only deepen understanding but also instill confidence in handling syntax-related challenges independently.

A stable database connection is paramount for successful SQL operations. Here, we explore the reasons behind common connection issues and offer practical troubleshooting techniques. From authentication problems to network issues, students will gain the skills needed to diagnose and resolve connection challenges effectively. Security is a top priority in database management. In this segment, we provide valuable tips on securing database connections to prevent unauthorized access and ensure stable performance. Students will learn best practices for maintaining the integrity and confidentiality of their databases.

Efficient SQL queries are essential for optimal database performance. We guide students in identifying performance bottlenecks and understanding the factors that impact query speed. By recognizing these bottlenecks, students can take targeted actions to enhance the overall performance of their SQL queries. Building on the understanding of performance bottlenecks, we delve into advanced techniques for optimizing SQL queries. From indexing strategies to query restructuring, students will gain valuable insights into making their queries more efficient and responsive.

Data integrity is crucial for the reliability of any database. This section focuses on identifying common data integrity issues within SQL databases. By recognizing anomalies and inconsistencies, students can take proactive measures to maintain accurate and reliable data. To ensure data integrity, we provide practical techniques for validating and verifying data within SQL databases. Students will learn how to implement validation checks, reducing the risk of data errors and improving the overall quality of their databases.

SQL joins can be challenging for students, often leading to errors and confusion. In this section, we break down the complexities of joins, offering clear explanations and troubleshooting tips. Students will gain confidence in working with different types of joins in their SQL statements.

SQL Syntax Errors: Decoding the Language

Embarking on the journey of SQL programming often leads students through a maze of intricate code structures, where the slightest misstep can result in syntax errors. In this section, we unravel the complexities of SQL syntax mistakes, providing a clear roadmap for students to navigate through the labyrinth of code. From misplaced semicolons to misused keywords, we delve into common pitfalls, demystifying each one. Fear not the challenges of SQL syntax errors; instead, embrace the opportunity to deepen your understanding. Our expert guidance will lead you through the intricacies of rectifying these errors, ensuring that you not only fix the problem at hand but also gain valuable insights for future coding endeavors. With clarity and precision, we empower you to master the art of SQL syntax, transforming each stumbling block into a stepping stone on your path to SQL proficiency. Theory comes to life when met with practical application, and SQL is no exception. In this segment, we bridge the gap between theory and real-world application by providing a hands-on exploration of SQL syntax errors. Through a series of practical examples, we showcase the diverse ways in which syntax mistakes can manifest and the corresponding solutions to rectify them. Each example serves as a puzzle to solve, guiding you through the troubleshooting process step by step. By immersing yourself in these real-world scenarios, you not only grasp the theoretical concepts but also develop the problem-solving skills essential for any SQL practitioner. Join us on this journey as we transform abstract knowledge into practical wisdom, empowering you to overcome SQL syntax challenges with confidence and competence.

As we navigate the intricacies of SQL syntax, it becomes evident that mastering the language involves not only understanding theoretical concepts but also being adept at resolving practical challenges. Our exploration of common syntax mistakes is not just a theoretical exercise; it's a hands-on approach to fortifying your SQL skills.

Within the realm of SQL programming, every semicolon, every keyword holds significance. A seemingly trivial oversight can lead to syntax errors that impede the execution of your queries. Through our practical examples, we provide a bridge between the theoretical underpinnings of SQL syntax and the pragmatic application in real-world scenarios. Each example is meticulously crafted to mirror the challenges students often encounter, transforming abstract concepts into tangible problem-solving experiences.

By immersing yourself in these scenarios, you not only rectify syntax errors but also cultivate a mindset of resilience and adaptability. The journey from identifying an error to implementing a solution is a transformative process, where you evolve from a novice grappling with syntax complexities to a proficient coder capable of navigating SQL challenges with finesse.

Our commitment is not just to troubleshoot syntax errors but to equip you with the skills to prevent them. Understanding the nuances of SQL syntax empowers you to write cleaner, more efficient code. You'll gain insights into the structure and logic of the language, allowing you to express your database queries with precision.

This section serves as a comprehensive guide to decoding the intricacies of SQL syntax, emphasizing the practical aspect of troubleshooting common mistakes. Embrace the challenges as opportunities to enhance your skills and deepen your understanding of SQL programming. Whether you are a student venturing into the world of databases or a seasoned practitioner refining your skills, our journey through common syntax mistakes and practical solutions is designed to elevate your SQL proficiency. Step into the realm of SQL with confidence, knowing that each stumbling block is a stepping stone toward mastery.

Database Connection Issues: Bridging the Gap

In the intricate realm of database management, encountering connection issues can be perplexing for students grappling with SQL complexities. This segment serves as your guide through the labyrinth of troubleshooting, shedding light on the diverse challenges that may impede database connections. From decoding connection timeouts to addressing authentication errors, we unravel the intricacies of common issues, offering practical solutions to pave the way for a seamless connection experience. Whether you're a novice or an adept database enthusiast, this exploration aims to empower you with essential troubleshooting techniques, ensuring you overcome prevalent connection hurdles with confidence.

Security and stability take center stage in the landscape of database management. This section unveils indispensable best practices for fortifying your database connections, guaranteeing not only the confidentiality of your data but also the unwavering stability of your system. Dive into the intricacies of safeguarding your database against potential threats, encompassing unauthorized access and data breaches. We illuminate encryption methods, user authentication protocols, and network security measures, providing a comprehensive guide to constructing a robust and stable database environment. Whether you're a student immersed in a project or a professional overseeing critical databases, these tips arm you with the knowledge needed to establish and perpetuate secure, stable connections within the ever-evolving domain of database management.

In the dynamic world of database management, troubleshooting connection issues is an essential skill for students navigating the intricacies of SQL. Understanding the root causes of these problems is pivotal for a seamless experience. This segment explores the common challenges that may arise during database connections, offering practical insights into their origins and implications. From connection timeouts to authentication errors, we provide guidance to empower both novices and experienced enthusiasts with troubleshooting techniques, ensuring a smooth navigation through potential hurdles.

Security and stability are paramount in database management. This section introduces best practices for securing database connections and maintaining system stability. Learn how to fortify your database against threats such as unauthorized access and data breaches. We delve into encryption methods, user authentication protocols, and network security measures to equip you with a comprehensive understanding of creating a robust and stable database environment. Whether you're a student working on a project or a professional managing critical databases, these tips will provide the knowledge to establish and maintain secure, stable connections in the ever-evolving landscape of database management.

Query Optimization: Speeding Up Your SQL Game

In the intricate landscape of SQL, where the efficiency of database queries is paramount, mastering the art of identifying performance bottlenecks becomes a crucial skill. The journey begins with a deep dive into the factors that can significantly impact the speed and responsiveness of your SQL queries. Unveiling the mysteries behind sluggish performance, this section serves as a compass, guiding you through the labyrinth of potential bottlenecks that may be hindering your database's optimal operation. Whether it's an issue with indexing, poorly structured queries, or database design flaws, understanding the root causes is the first step towards effective resolution. Once armed with this knowledge, we embark on a transformative exploration of advanced techniques for query optimization. Elevate your SQL prowess by delving into the intricacies of optimization strategies that go beyond the basics. From refining query structures to leveraging indexing intelligently, this section unveils the arsenal of tools at your disposal. Witness the magic of optimizing SQL queries as we demystify the complexities and empower you to make your database interactions more efficient, responsive, and ultimately, a testament to your SQL expertise. Whether you're a novice or an experienced SQL enthusiast, this comprehensive guide equips you with the insights and techniques needed to not only identify performance bottlenecks but to transcend them, ensuring your SQL queries operate at peak performance levels.

The journey through identifying performance bottlenecks and mastering query optimization is a nuanced exploration that delves into the very core of SQL efficiency. As we unravel the intricacies of database performance, it becomes evident that the factors affecting query speed are multifaceted. From poorly designed indexes to suboptimal database configurations, each element plays a role in the overall performance tapestry. This section acts as a troubleshooter's guide, providing a roadmap to pinpoint these bottlenecks and alleviate their impact. It's not just about recognizing the symptoms but understanding the underlying issues that lead to sluggish queries. By gaining this comprehensive understanding, you're not merely fixing performance issues; you're building a foundation for a more robust and streamlined database.

The narrative seamlessly transitions into the realm of advanced techniques for query optimization, elevating your SQL proficiency to new heights. Optimization is an art that goes beyond the conventional approaches, and here, we unveil the secrets of making your SQL queries not just run, but sprint. As we navigate through the nuances of query structures, indexing strategies, and database design principles, you'll discover how seemingly small adjustments can have a profound impact on overall performance. This section is more than a guide; it's an invitation to explore the intricate dance between your SQL queries and the underlying database architecture. Embrace techniques that transform your queries from mere lines of code into efficient orchestrators of data retrieval.

Picture yourself confidently navigating the challenges of a large dataset, effortlessly retrieving information at the speed of thought. The techniques outlined in this guide empower you to optimize queries with precision, allowing your database to respond promptly to the demands of your applications. Whether you're troubleshooting existing performance issues or proactively seeking ways to enhance your SQL efficiency, this section provides actionable insights that transcend the boundaries of conventional optimization wisdom.

Data Integrity Challenges: Ensuring Accuracy

In the realm of SQL databases, ensuring data integrity is paramount. This section delves into the art of spotting common data integrity issues that may lurk within your database, shedding light on the culprits that can jeopardize the reliability of your SQL queries. Uncover the nuances of identifying discrepancies, inconsistencies, and irregularities that may compromise the accuracy of your data. Gain insights into how these issues manifest in SQL queries and grasp the profound impact they can have on the overall functionality of your database. This segment serves as a guiding beacon, equipping you with the skills to meticulously examine your database for any signs of data integrity breaches.

Once armed with the knowledge of identifying data integrity issues, the journey continues with a strategic focus on implementing robust data validation techniques. Navigate through effective methods that not only address existing problems but also serve as preventive measures for maintaining data integrity in your SQL databases. Discover the tools and practices that act as guardians of your data, ensuring that every piece of information stored aligns with predefined rules and standards. From validation constraints to error-checking mechanisms, this segment provides a comprehensive roadmap for fortifying your database against potential threats, making it resilient to data integrity challenges. Embrace the power of proactive data validation, and witness the transformation of your SQL databases into bastions of accuracy and reliability.

Building on the foundation of identifying data integrity issues, mastering the implementation of data validation techniques becomes a pivotal step in fortifying the robustness of your SQL databases. Dive deeper into the intricacies of validation constraints, exploring how these serve as gatekeepers to ensure that only accurate and conforming data finds its way into your database tables. Learn to construct and enforce rules that define the permissible data formats, types, and ranges, thereby shielding your database from potential anomalies.

Moreover, this segment provides a holistic approach to error-checking mechanisms, unveiling the tools and methodologies that act as vigilant sentinels against data inconsistencies. From input validation routines to automated scripts, discover the arsenal of techniques that aids in the continuous monitoring and validation of data entries. By implementing these advanced data validation strategies, you not only rectify existing issues but also establish a proactive line of defense, mitigating the risk of future integrity challenges.

A key aspect emphasized in this section is the symbiotic relationship between data validation and SQL queries. Gain insights into how well-validated data directly enhances the accuracy and reliability of SQL queries, promoting seamless interaction with your database. By incorporating validation techniques into your SQL workflow, you not only ensure the correctness of the results retrieved but also contribute to the overall optimization of query performance.

In essence, this segment acts as a comprehensive guide, offering practical insights into the intricate process of implementing data validation techniques. It empowers you to proactively manage data integrity, providing a safeguard against potential pitfalls that might undermine the efficacy of your SQL databases. As you navigate through the nuances of data validation, envision your database not merely as a repository of information but as a stronghold of precision and dependability.

Handling Joins and Subqueries: Mastering Complexity

In the intricate landscape of SQL, navigating through join challenges is a skill that can elevate your database proficiency. Unravel the complexities of SQL joins with our insightful guide, providing hands-on tips for troubleshooting common join-related problems. Whether you're dealing with inner joins, outer joins, or self joins, understanding how to overcome hurdles in the joining process is essential for creating seamless and efficient queries. Dive deep into practical scenarios, where we dissect join-related issues and guide you through step-by-step solutions. By the end of this section, you'll emerge with a newfound confidence in handling join challenges, ensuring your SQL queries operate with precision and efficiency.

Delve into the world of subqueries, an often-misunderstood aspect of SQL that can pose challenges for even the most seasoned database enthusiasts. In this section, we unravel the intricacies of subqueries, offering a comprehensive understanding of their purpose and usage in SQL statements. Gain insights into common issues that may arise when incorporating subqueries and equip yourself with troubleshooting techniques to overcome them seamlessly. Whether you're dealing with correlated or non-correlated subqueries, mastering the art of subquery utilization is crucial for enhancing the depth and sophistication of your SQL queries. Unveil the power of subqueries and elevate your SQL proficiency to new heights.

Navigating the complexities of SQL joins is an essential skill for any database professional. As you delve into the world of inner joins, outer joins, and self joins, understanding how to troubleshoot common problems in the joining process becomes paramount. Our guide provides practical insights and tips, taking you through various scenarios where join-related issues may arise. Whether you're faced with mismatched columns, ambiguous table aliases, or performance bottlenecks, we demystify these challenges, empowering you to create queries that run seamlessly and efficiently. By the end of this section, you'll emerge as a proficient navigator of join complexities, ready to tackle any SQL join with confidence.

Subqueries are a powerful but often intricate feature in SQL, and understanding their nuances is key to mastering advanced query construction. In this section, we shed light on the purpose and usage of subqueries, unraveling their complexities to provide you with a comprehensive understanding. Discover how subqueries can enhance the depth of your SQL statements and learn to identify and troubleshoot common issues that may arise. Whether it's dealing with correlated subqueries that reference outer queries or non-correlated subqueries that operate independently, our guide equips you with the knowledge and techniques needed to seamlessly incorporate subqueries into your SQL arsenal. Unveil the true potential of subqueries and take your SQL proficiency to the next level.

SQL troubleshooting doesn't need to be a daunting task. This comprehensive guide empowers you with the knowledge to navigate through the intricacies of SQL challenges, making you well-equipped to tackle common student queries and overcome hurdles in your database homework. Embrace every error as an opportunity for learning and skill enhancement. With the insights gained from this guide, you'll not only resolve issues efficiently but also elevate your overall proficiency in SQL. Embrace the journey of troubleshooting, and watch how each challenge becomes a stepping stone towards mastering the art of SQL.

As you embark on the journey of SQL troubleshooting, remember that each encountered error is a valuable opportunity to enhance your skills and deepen your understanding of database management. Armed with the knowledge obtained from this comprehensive guide, you gain a strategic advantage in addressing common student queries and navigating the intricacies of your database homework.

SQL, with its nuances and complexities, becomes a tool in your hands rather than an obstacle. The troubleshooting insights provided here serve as a roadmap, guiding you through the maze of potential challenges. Whether it's deciphering syntax errors, resolving database connection issues, optimizing queries for better performance, ensuring data integrity, or mastering the intricacies of joins and subqueries, you're equipped to handle each aspect with confidence.

It's crucial to recognize that in the world of database management, errors are not setbacks but stepping stones. Each challenge you encounter is an opportunity to refine your problem-solving skills and reinforce your grasp of SQL concepts. Approach every issue with curiosity and a willingness to learn, and you'll find yourself not only overcoming hurdles but also growing into a proficient SQL practitioner.

So, embrace the troubleshooting process as a vital part of your SQL learning journey. As you implement solutions and witness the resolution of issues, you're not just fixing problems — you're sculpting your expertise. Remember, the most accomplished SQL professionals are those who have faced and conquered challenges head-on, using them as catalysts for continuous improvement.

In conclusion, SQL troubleshooting is not merely about fixing errors; it's about evolving into a skilled problem solver. Armed with the insights from this guide, navigate the world of SQL challenges with confidence, turning each stumbling block into a stepping stone towards mastering the intricacies of database management. Your journey in SQL troubleshooting is not just a process; it's a transformation, refining you into a proficient and resilient database professional.

Post a comment...

Unlocking solutions to sql woes: troubleshooting common student queries submit your homework, attached files.

• How it works
• Homework answers

SQL Assignment Help

Structured Query Language is a powerful computer language for database management. The language is often taught at various levels in learning institutions. All the solutions you need in SQL are available here from our experts.

SQL project problems encountered by programmers:

• many SQL language concepts are difficult to grasp;
• difficulties in debugging applications;
• all SQL projects are time-consuming and demand great precision.

Arranging the contents in an SQL project can be quite cumbersome because of the many language elements that have to be keyed in. Wrong input of the language elements can make the SQL project null and finding what caused the problem can be super difficult. When doing SQL projects, you may do not know which language elements to use. A problem in an SQL assignment may not require the use of computer software to solve it. Much of it may require manual writing of the language elements. This may not be easy for the writer, but we are here to give you a solution to do SQL assignments. Our team of experts will handle your SQL assignment, and you can be sure you will get a high score for your SQL assignment.

Find quick SQL solutions with our help:

• Get working programs designed by expert SQL programmers;
• Learn how to accomplish SQL tasks through original samples;
• Quick assistance with completely custom SQL projects.

If you're asking yourself “Where can I get help to do my SQL assignment?” relax; our company has the best programming experts to ensure that all the SQL assignments are done with the best quality. We provide you with great custom SQL assignments developed from scratch by our professional programming experts, who have full knowledge of all types of SQL programming language, deadline-oriented to deliver your SQL project solutions on time. Our degree-holding experts are aware of all programming languages related to qt. This ensures that the SQL assignments we develop for you are of high quality.

Assignment Expert provides professional and outstanding service with:

• Available representatives of online support (chat and email) 24/7;
• Discounts for regular customers;
• Secure payment methods and 100% confidentiality;
• SQL assignment help for professionals all over the world.

Assignment Expert's services can successfully write codes of different programming languages, including SQL projects. SQL projects from our programming specialists are always correct and precise. Our experts are ready to help anyone do assignments, no matter your specializations or deadlines, as we value our work and expertise. To program everything that you need is our aim, and we always strive for excellence.

• Programming
• Engineering

Who Can Help Me with My Assignment

There are three certainties in this world: Death, Taxes and Homework Assignments. No matter where you study, and no matter…

How to Finish Assignments When You Can’t

Crunch time is coming, deadlines need to be met, essays need to be submitted, and tests should be studied for.…

How to Effectively Study for a Math Test

Numbers and figures are an essential part of our world, necessary for almost everything we do every day. As important…

• Skip to navigation
• Skip to main content
• Skip to secondary content
• Skip to footer

SQL Studies

• Study and Reference Materials
• sp_SrvPermissions
• sp_DBPermissions
• sp_AzSQLDBPermissions
• sp_AzSYNDBPermissions
• Blogging ideas
• SQL Homework
• The Blame Game

Category Archives: SQL Homework

Sql homework – september 2023 – time for temporal tables..

September 5, 2023 by Kenneth Fisher

Recently I needed to use temporal tables. And not just for the job history table in a managed instance. In … Continue reading

Category: Microsoft SQL Server , SQL Homework , SQLServerPedia Syndication | Tags: Microsoft SQL Server , SQL Homework , temporal table

SQL Homework – August 2023 – Have the best practices changed while you weren’t looking?

August 1, 2023 by Kenneth Fisher

It’s been a bit since I did one of these so let’s go back to the basics. Best practices. We … Continue reading

Category: Microsoft SQL Server , SQL Homework , SQLServerPedia Syndication | Tags: best practices , Microsoft SQL Server , SQL Homework

SQL Homework – December 2022 – What’s new in SQL Server?

December 1, 2022 by Kenneth Fisher

SQL Server 2022 has been released! Long live SQL Server! No really, I’ve still got a while before retirement. I … Continue reading

Category: Documentation , Microsoft SQL Server , SQL Homework , SQLServerPedia Syndication | Tags: Documentation , Microsoft SQL Server , SQL Homework

SQL Homework – November 2022 – Getting ready for the next time you have to look for a job.

November 1, 2022 by Kenneth Fisher

Back in July I suggested that you get your resume up to date. It’s something I’d wish I’d kept up … Continue reading

Category: SQL Homework , SQLServerPedia Syndication | Tags: job search , Microsoft SQL Server , SQL Homework

SQL Homework – October 2022 – Replication part 2

October 4, 2022 by Kenneth Fisher

In August we started taking a look at replication. We learned some of the terms used, and set up a … Continue reading

Category: Microsoft SQL Server , Replication , SQL Homework , SQLServerPedia Syndication | Tags: Microsoft SQL Server , Replication , SQL Homework

SQL Homework – September 2022 – Query window challenge

September 1, 2022 by Kenneth Fisher

I had every intention of doing Replication Part 2 this month but had a thought I liked better. I will … Continue reading

Category: Microsoft SQL Server , SQL Homework , SQLServerPedia Syndication , T-SQL | Tags: Microsoft SQL Server , Scripting , SQL Homework , T-SQL

SQL Homework – August 2022 – Replication part 1

August 2, 2022 by Kenneth Fisher

You’ll hear a lot of people complain about replication. Including me. That said, replication is an amazing tool. There really … Continue reading

SQL Homework – July 2022 – Update your resume.

July 5, 2022 by Kenneth Fisher

Resumes have been on my mind a lot recently. I’ve been thinking that I wish I’d kept mine up to … Continue reading

Category: Collation , SQL Homework , SQLServerPedia Syndication | Tags: collation , Microsoft SQL Server , SQL Homework

SQL Homework – June 2022 – SQL Agent Jobs continued.

June 2, 2022 by Kenneth Fisher

A few years ago I gave you homework to create a SQL Agent Job and play with it a little … Continue reading

Category: Collation , Microsoft SQL Server , SQL Homework , SQLServerPedia Syndication | Tags: collation , Microsoft SQL Server , SQL Homework

SQL Homework – May 2022 – Books on Line

May 5, 2022 by Kenneth Fisher

No one knows everything. Ask any senior level person in IT and they’ll tell you that they spend plenty of … Continue reading

Category: Documentation , Microsoft SQL Server , SQL Homework , SQLServerPedia Syndication , T-SQL | Tags: BOL , Documentation , Microsoft SQL Server , SQL Homework

Follow me via RSS

Follow me via Email

Enter your email address to follow this blog and receive notifications of new posts by email.

Email Address:

Follow me on Twitter

• January 2024
• December 2023
• September 2023
• August 2023
• December 2022
• November 2022
• October 2022
• September 2022
• August 2022
• February 2022
• December 2021
• November 2021
• October 2021
• September 2021
• August 2021
• January 2021
• December 2020
• November 2020
• October 2020
• September 2020
• August 2020
• February 2020
• January 2020
• December 2019
• November 2019
• October 2019
• September 2019
• August 2019
• February 2019
• January 2019
• December 2018
• November 2018
• October 2018
• September 2018
• August 2018
• February 2018
• January 2018
• December 2017
• November 2017
• October 2017
• September 2017
• August 2017
• February 2017
• January 2017
• December 2016
• November 2016
• October 2016
• September 2016
• August 2016
• February 2016
• January 2016
• December 2015
• November 2015
• October 2015
• September 2015
• August 2015
• February 2015
• January 2015
• December 2014
• November 2014
• October 2014
• September 2014
• August 2014
• February 2014
• January 2014
• December 2013
• November 2013
• October 2013
• September 2013
• August 2013
• February 2013
• January 2013
• December 2012
• November 2012
• October 2012
• September 2012
• August 2012
• Azure Data Studio
• Bad idea Cowboy hat
• Batch Files
• Book Review
• Central Management Server
• Certification
• Connection Strings
• Database Mail
• Distributed Transaction Coordinator
• Documentation
• Dynamic SQL
• Extended Events
• Impersonation
• Managed Instance
• Microsoft SQL Server
• Partitioning
• Performance
• Policy Based Management
• Problem Resolution
• Query Plans
• Referential Integrity
• Replication
• SQL Agent Jobs
• SQL Database
• SQL Hangout
• SQL Judo's Monthly DBA Challenge
• SQL Services
• SQLServerPedia Syndication
• System Databases
• System Functions and Stored Procedures
• T-SQL Tuesday
• Transactions
• Uncategorized
• Entries feed
• Comments feed
• WordPress.com

• Already have a WordPress.com account? Log in now.
• Subscribe Subscribed
• Report this content
• View site in Reader
• Manage subscriptions
• Collapse this bar

Homework #1 - SQL Last Updated: Jan 26, 2024

The first homework is to construct a set of SQL queries for analyzing a dataset that will be provided to you. For this, you will look into MusicBrainz data . This homework is an opportunity to: (1) learn basic and certain advanced SQL features, and (2) get familiar with using two full-featured DBMSs, SQLite and DuckDB , that can be useful for you in the future.

This is a single-person project that will be completed individually (i.e., no groups).

• Release Date: Jan 22, 2024
• Due Date: Feb 02, 2024 @ 11:59pm

Specification

The homework contains 10 questions in total and is graded out of 100 points. For each question, you will need to construct a SQL query that fetches the desired data. Your answer for each question should contain only one statement. For Q2, Q3, Q4 you are required to use both SQLite and DuckDB , and for the other questions you are free to use either. It will likely take you approximately 8-10 hours to complete the questions.

Placeholder Folder

Create the placeholder submission folder with the empty SQL files that you will use for each question:

After filling in the queries, you can compress the folder by running the following command:

The -j flag lets you compress all the SQL queries in the zip file without path information. The grading scripts will not work correctly unless you do this.

Instructions

Then follow the instructions below to install SQLite and DuckDB.

You will first need to install SQLite on your development machine.

Make sure that you are using at least SQLite version 3.25! Older releases (prior to 2019) will not support the SQL features that you need to complete this assignment.

• Ubuntu Linux: Please follow the instructions .
• Mac OS X: On Mac OS Leopard or later, you don't have to! It comes pre-installed. You can upgrade it, if you absolutely need to, with Homebrew .

Then follow these instructions to load the dataset:

Please follow the instructions to install DuckDB version 0.9.2 for the command line environment.

Check the schema

Get familiar with the schema (structure) of the tables (what attributes do they contain, what are the primary and foreign keys). Run the .schema $TABLE_NAME command on the sqlite3 terminal for each table. The output should look like the example below for each table.

Contains details for an area. For example, this is a row from the table:

For us, the important field is name (e.g., "Great Neck").

Contains details of an artist. For example, this is a row from the table:

For us, the important fields are name (e.g., "Michael Jackson") and area (e.g., 222).

artist_alias

Contains alternate names for the artists. For example, this is a row from the table:

artist_credit

Contains lists of artists. For example, this is a row from the table:

artist_credit_name

Contains mappings from artist credits to artists. For example, this is a row from the table:

artist_type

Contains details of an artist type. For example, this is a row from the table:

Contains details for a gender type. For example, this is a row from the table:

Contains details for a language type. For example, this is a row from the table:

Contains details for a medium. For example, this is a row from the table:

For us, the important fields are release (e.g., 287750) and type (e.g., 8).

medium_format

Contains details for a medium format. For example, this is a row from the table:

The table contains music releases. For example, this is a row from the table:

release_info

The table contains the detailed information of a release. For example, this is a row from the table:

release_status

Contains the details of a release status. For example, this is a row from the table:

Contains the details of a work. For example, this is a row from the table:

Contains the details of a work type. For example, this is a row from the table:

Sanity Check

Count the number of rows in the table

Construct the SQL Queries

It's time to start constructing the SQL queries and put them into the placeholder files. You can start with using SQLite.

Q1 [0 points]

Q2 [10 points], q3 [10 points], q4 [10 points], q5 [10 points].

Your first row should look like this: Marilyn Manson|Get Your Gunn|1994-6-9

Q6 [10 points]

Your first row should look like this: 2001|Aachener Studentenorchester|ASO-Konzert WS 2000/2001|ASO-Konzert SS 2001

Q7 [10 points]

Q8 [10 points], q9 [15 points], q10 [15 points].

The first output row should look like this: Will Long│3│Canada,Japan,[Worldwide]

Grading Rubric

Each submission will be graded based on whether the SQL queries fetch the expected sets of tuples from the database. Only one statement is allowed in each SQL query. Note that your SQL queries will be auto-graded by comparing their outputs (i.e. tuple sets) to the correct outputs. For your queries, the order of the output columns is important; their names are not.

Late Policy

See the late policy in the syllabus.

We use the Autograder from Gradescope for grading in order to provide you with immediate feedback. After completing the homework, you can submit your compressed folder submission.zip (only one file) to Gradescope:

• https://www.gradescope.com/courses/674039

Important: Use the Gradescope course code announced on Piazza.

We will be comparing the output files using a function similar to diff . You can submit your answers as many times as you like.

Collaboration Policy

• Every student has to work individually on this assignment.
• Students are allowed to discuss high-level details about the project with others.
• Students are not allowed to copy the contents of a white-board after a group meeting with other students.
• Students are not allowed to copy the solutions from another colleague.

WARNING: All of the code for this project must be your own. You may not copy source code from other students or other sources that you find on the web. Plagiarism will not be tolerated. See CMU's Policy on Academic Integrity for additional information.

ME 274: Basic Mechanics II

Homework h6.i - sp24.

DISCUSSION THREAD

Discussion and hints

The above shows the results of simulations on this problem for two frequencies of excitation, ω. The upper plot where ω is less than the natural frequency, ω n , and the lower plot where ω is greater than ω n . In each plot, the RED curve is the forcing F(t) and the BLUE curve is the particular solution of the EOM, x P (t). As can be seen from these simulation results:

• The response for ω < ω n has the steady-state response of x P (t) moving in phase with the forcing F(t). That is, during the time that the block is displaced to the right, the force is acting to the right. Conversely, during the time that the block is displaced to the left, the force is acting to the left.
• The response for ω > ω n has the steady-state response of x P (t) moving 180° out of phase with the forcing F(t). That is, during the time that the block is displaced to the right, the force is acting to the left, and during the time that the block is displaced to the left, the force is acting to the right.

In your analysis for this problem, you will be determining the natural frequency ω n  of the system, along with the steady-state response. Be sure to check your answer in the end: does it demonstrate the phase relations seen in the above simulation results?

Any questions?? Please ask/answer questions regarding this homework problem through the "Leave a Comment" link above.

92 thoughts on “Homework H6.I - Sp24”

Once you have the EOM, you can use the equation for x_p(t) to find the second derivative. Then you can sub these two equations into the EOM and solve for the constants.

Is this the method of undetermined coefficients essentially?

Yes. You just have to find all the forces that act on the direction of acceleration times mass and then substitute your x=Asin(wt)+Bcos(wt) (with derivatives) into the corresponding x's to find your constants. These constants can then be substituted back into your original x equation.

Will omega in this case be the given omega from the problem statement?

Yes, the omega from the problem statement is the omega you should use in order to solve.

What does this mean exactly? I am just confused if we use the resonsant frequency or the frequency given in the problem statement.

From my understanding, you need to use both the natural resonance frequency found through your EOM that you derive and the omega given to you in the problem statement.

Yes. The omega given will be the one in the particular solution formula.

There is two omegas, Wn is the natural one found by sqrt(k/m), the second is used to work with the applied force

Partially, to find the general solution we can use that, but here we are only interested by xp(t) which we get from the formula found in the book (or solving for the general solution)

If the natural frequency and the forcing frequency are the same is that when it can be considered in phase?

When this happens, I believe they are in phase and when they are equal the affect it has is that the two are in resonance - which increases the amplitude of the oscillator

When omega = omega_n, the system is in resonance. At resonance, the forcing and response are 90° out of phase.

When the natural frequency and the forcing frequency are the same, the system has resonant frequency, meaning that the amplitude is maximized. If the system is undamped then this does mean that the system is in phase, but if there is a dampener then there can be a phase shift.

not necessarily. the two can have the same frequency but that does not mean they will act constructively, a phase shift could be present due to delay in one of them

When subbing x_p(t) and the second derivative of x_p(t) into your standard EOM, the coefficient before x will be w_n^2 making it the method to solve for A and B. You can separate the coefficients into coswt and sinwt as well. Plugging those values back into x_p(t) will give your answer for c.

When deciding for the direction of the force of the springs, is there an instance when all three would be pointing in the same direction?

When determining the direction of the spring forces, envision the block in a position corresponding to positive x (here, to the right). With the block displaced to the right, the spring on the left side is stretched - that spring then will pull on the block to the left. Also, the springs on the right would be compressed, and pushing on the block to the left. Therefore, the direction of all three spring forces will be to the left.

For this problem, I set up my FBD and used F_x to find my EOM. This was pretty straightforward. To find the particular solution, we can look in the textbook for the x_p equation.

When solving for A, solve for natural frequency using sqrt(k/m) and sub it into the equation.

For this problem, an easy way to find the EOM is to sum the forces in X. If you're unsure of how to find a particular solution, I highly recommend watching an example video on this. EX 6.C.1 is very straightforward in this process.

In this problem, all the spring forces are acting to the left, as you can imagine that the block is moving to the right, and the springs resist that motion.

The problem statement asks for the particular solution. If it were to ask for the entire solution, you would also have to solve the homogeneous EOM (the left side = 0), like we've been doing for the last few homework assignments.

Also, because the forces we are using are sine waves, the particular solution should also just be a sine wave.

A helpful example of a similar problem is problem 6.C.2. Looking over this problem helped me to solve for x_p

The force balance equation is set up the same as previous problems with the springs resisting the motion, and the only thing added is the force in the positive direction.

In part C I found it helpful to use x = Asin(wt) +Bcos(wt) and then using that to solve for the particular solution.

Same, finding the second derivative and plugging it into the equation above was helpful to find xp.

It is always safe to use this formula when there is no dashpot since a system with no dashpot will always have two imaginary roots.

I found 6.C.2 to be extremely helpful when finishing up this problem.

This was useful when visualizing what is happening

I used Fx Equations as my first step. The FBD was the block and since the surface is smooth, w there is no friction to account for. Then, you’re left with just the forces of the springs and F.

Imagine the block displaced to the right (positive x) when assessing spring force direction. As the left spring stretches and the right ones compress, all spring forces pull the block to the left. I found that the simplest way to find the EOM was to sum the forces in the X direction.

For a shortcut on this problem, calculate the damping ratio and notice what that means about the system. From here, you can essentially skip all of the tedious deriving and substituting and use the equation provided on page 411 of the lecturebook to find x_p(t), only having to find the value of w_n along the way.

The approach to find the EOM is the same as previous homeworks, except this time we have a F(t) acting on the block, which should not be neglected. Additionally, to find the xp(t), referring to 6.C.1 helps.

When we solve for the complementary solution, should we simply leave the initial conditions as Xo and Vo, since they are not given in the problem? Or, if the system starts at rest, both of these constants would be zero and the system would gain energy from work done by the force.

I believe Xo is 0, because "x = 0 m designate the state at which the springs are unstretched". You should not need Vo to derive the particular solution.

The particular solution should only have a sign component because the right side of the force response component is a sine wave without a cosine component.

To find the particular solution it is helpful to reference the forced response 1 review slide in the daily schedule!

Solving for the constants in the particular solution is reminiscent of when we got two equations from one velocity or acceleration equation by dividing it into i and j components. In this case you are doing the same thing just with sinwt and coswt instead of i and j.

The final derivation for the particular solution equation can be found in the textbook and it is really helpful with this problem.

The setup of the force balance equation remains consistent with previous problems, incorporating the resistance from the springs opposing the motion. The addition here is the external force acting in the positive direction.

Remember to substitute given numbers into the EOM and particular solution equations. For example omega is 15 rad/s and Fo is 150 N.

I found the book very helpful when attempting to solve this problem because it clearly explains that when solving for an undamped forced response B will be 0. Additionally, it provided a useful equation to find A.

If you’re confused about this hw I found page 411 on the lecture book very helpful

If you need to look at an example similarly solved to this problem, I found example 6.C.2 very helpful.

For this problem I used example 6C1 to help explain the particular solution qualifications. A FBD made it pretty straight forward for developing the EOM.

Setting up your system to the right, you will get that your forces of the spring are in the opposite direction of the force which then further helps

I found lecture book example 6.C.1 very helpful for guidance on this homework problem. Overall it is pretty straightforward if you get the FBD correct. When drawing the forces on the FBD keep in mind how a spring acts when it is compressed or stretched. For example, if you assume the block's motion is to the right, the spring on the left will be stretched therefore its force will be pulling the block to the left. As for the two springs on the right of the block, they will be compressed so they will be pushing the block to the left.

Since the springs are unstretched at x=0, x(0)=0. This will be helpful to know when solving for the particular solution in part C.

When solving for x dot, and you solve for your particular solution. How would the F(t) factor in when solving for the coefficient S?

Since we are not given initial conditions we do not need to do this. However when solving for xdot since you are deriving in function of t, F(t) would be derived in function of t and appended to ccos + ssin

I'd highly recommend looking at example 6.C.1 in the lecture book, as it outlines how you'd get your particular solution from your EOM by showing how exactly you'd solve for the constants A and B via substituting back into the EOM.

In the FBD, it is important to remember the direction of the spring force (which is opposite to the direction of motion). From this, the EOM can easily be found. To find the particular solution, example 6.C.1 is helpful.

Some helpful tips you can pull from example 6.C.1 in the book is that the spring force is in the opposite direction of the motion, the spring is unstretched so x(0)=0, and to find the EOM use the sum of the forces in the x direction.

To find the coefficients after the particular solution/time derivatives have been substituted into the EOM, you can easily match up the coefficients of the left and right side of the equation. This is very similar to Example 6.C.2 without damping in the lecture book.

When solving this problem make sure that you define the positive directions for the rotations of each disk and for the sum of forces in x for the bar. Check to see if this is consistent with your Euler and Newton equations or else your coefficients in front of X double dot and X might not have the same signs in the EOM.

This problem is fairly simple. Pay attention to the direction of your spring force as it should be opposite of the motion of the block.

This confused me at first, but I came to the same conclusion. If the block starts moving right the springs on the right will compress and the spring on the left will extend, resulting in all of the spring forces opposing the motion going left.

Does the fact that there are two springs acting together on one side change the spring constant on that side for the sum of forces?

In this case it would not, you would just add the separate spring components as if they are two forces acting in the same direction.

Make sure not to confuse w and w_n when solving this problem. I found that pages 410 and 411 were helpful in solving this problem, along with example 6.C.2

One big thing to remember is that the springs will be acting in the same direction, which will help your signs equation to ensure that there are no negative values.

To start (after FBDs), I recommend summing forces in the x direction. It's an easy way to find the EOM. You will then have to use the method of undetermined coefficients to find the particular solution. Examples 6.C.1 and 6.C.2 were useful to me when solving this problem.

I found the recorded example 6.C.1 very valuable for simplifying the steps to this problem. Make sure you maintain the coefficient values you gather from your EOM to stay in line in this problem and reduce calculation error.

Is part c found using Is undetermined coefficients?

I was successful using undetermined coefficients! There are a few ways to solve but I found it to work.

I'm not really sure what you mean by "ls", but yes part C can be solved using the method of undetermined coefficients to find the particular solution. Example 6.C.1 is helpful to look at if you are confused

Using 6C1 and 2 was very helpful. Once the FBD are settled and you pick which direction the block is traveling (which springs are stretching and compressing), the problem becomes relatively straight forward. Also note that regarding frequency, omegan = omega making the system in resonance.

Parts (a) and (b) are found through the same procedure we have been using for a couple weeks, then example 6.C.1 shows how to solve for the particular solution at the timestamp 3:20.

Once you make your FBD and construct Newton/ Euler equations, the rest is what is essentially the method of undetermined coefficients.

When setting up the FBD, make sure that the spring forces all act in the same direction

For this problem it would be helpful to acknowledge that there is no damper meaning we expect no "c" term in our EOM. This is relatively straightforward as we draw a FBD and solve using summations in the x direction. Then to solve for the particular solution, take the double derivative of Acos(wt) + Bsin(wt) and notice A=0 as the position at t=0 is x=0. Solving for B accordingly by setting it equal to 150sin(15t) which will get you your coefficient to write the particular solution.

I also found 6C1 for setting up the problem with the added force, esp since the given force is the same

A nice thing to remember with problems like these is that there is no dashpot! What that means is that without any further math necessary, we can remove the B component of the particular solution. This makes the simplification much easier.

Does it matter which direction the applied force is pointing in the FBD?

No as long as you assume forces consistently you will simply get negative answers, showing you assumed wrong.

When solving this problem I found it useful to reduce the M term in the coefficient of X double dot. This allowed the numbers to stay relatively simple and allow the entire problem to be solved without using a calculator, expect for the final answer.

I found it helpful to set up the FDB and then used Fx to find the EOM.

The equation for force balance is established similarly to previous problems where the springs counteract the movement, and the only addition is the force in the positive direction

In order to derive the single differential equation of motion, it is extremely helpful to create a free body diagram of the system, and then set up the force equation in the x direction.

In order to solve for the particular solution of the system, the coefficient of both A and B need to be solved. Fortunately, the system is undampened, which gives us important information about one of the two coefficients.

Another helpful hint to solve for the particular solution in part C of the problem, specifically to solve for the coefficient of A, is to think of a method to set EOM equation and the particular solution equal to each other. This will eliminate some of the variables and make it simpler to solve for the coefficient of A.

One thing that helped me was to use example 6. C.2 from the book to help me solve the problem. It is was very similar

For this problem it is helpful to divide out the ms in the initial EOM equationa and then you will find that the term in front of x is the natural frequency squared.

For the EOM, does it need to be in terms of x, or can it be in it's non derived way (mx**+cx*+kx=f(t))?

In order to solve this problem, I firstly found the EOM with the Newton equation for the block in the x direction. Then, I solved the differential equation with the method of undefined coefficients and used the starting conditions to determine a particular solution.

I referenced example 6.C.2, but modified it to not include the dashpot, allowing the derivations for the particular solution aspect of the problem to be very similar to this hw problem, as it will only involve forces related to the springs to help find my cosine and sin components

Leave a Reply Cancel reply

You must be logged in to post a comment.

Purdue University

Microelectronic Rework Since 1986

For regions not listed on this page, please contact:

MIDAS TECHNOLOGY, INC. 400 West Cummings Park STE 6400, Woburn, MA 01801-6533 Phone +1 (781) 938-0069 • FAX +1 (781) 938-0160 Email: SALES

North America

Asia & pacific.

• Europe & Mideast

Europe/Mideast

Ask the pros.

Advanced SQL Practice: 10 Exercises with Solutions

• sql practice

As SQL proficiency continues to be in high demand for data professionals and developers alike, the importance of hands-on practice cannot be emphasized enough. Read on to delve into the world of advanced SQL and engage in practical exercises to enhance your skills.

This article provides you with a collection of ten challenging SQL practice exercises specifically for those seeking to enhance their advanced SQL skills. The exercises cover a selection of SQL concepts and will help you refresh your advanced SQL knowledge. Each exercise is accompanied by a detailed solution, allowing you to test your knowledge and gain a deeper understanding of complex SQL concepts. The exercises come from our advanced SQL practice courses . If you want to see more exercises like this, check out these courses:

• Window Functions Practice Set
• 2021 Monthly SQL Practice Sets - Advanced
• 2022 Monthly SQL Practice Sets - Advanced

Find out how you can practice advanced SQL with our platform.

Let’s get started.

Practicing Your Way to SQL Proficiency

Practice is an integral component in mastering SQL; its importance cannot be overstated. The journey to becoming proficient in advanced SQL requires dedication, perseverance, and a strong commitment to continuous practice. By engaging in regular advanced SQL practice, individuals can sharpen their skills, expand their knowledge, and develop a deep understanding of the intricacies of data management and manipulation.

Advanced SQL exercises serve as invaluable tools, challenging learners to apply their theoretical knowledge in practical scenarios and further solidifying their understanding of complex concepts. With each session of dedicated SQL practice, you can discover efficient techniques and gain the confidence needed to tackle real-world data challenges.

Let’s go over the exercises and their solutions.

Advanced SQL Practice Exercises

We’ll present various advanced SQL exercises that cover window functions , JOINs, GROUP BY, common table expressions (CTEs), and more.

Section 1: Advanced SQL JOIN Exercises

In the following advanced SQL exercises, we’ll use a sportswear database that stores information about clothes, clothing categories, colors, customers, and orders. It contains five tables: color , customer , category , clothing , and clothing_order . Let's look at the data in this database.

The color table contains the following columns:

• id stores the unique ID for each color.
• name stores the name of the color.
• extra_fee stores the extra charge (if any) added for clothing ordered in this color.

In the customer table, you'll find the following columns:

• id stores customer IDs.
• first_name stores the customer's first name.
• last_name stores the customer's last name.
• favorite_color_id stores the ID of the customer's favorite color (references the color table).

The category table contains these columns:

• id stores the unique ID for each category.
• name stores the name of the category.
• parent_id stores the ID of the main category for this category (if it's a subcategory). If this value is NULL , it denotes that this category is a main category. Note: Values are related to those in the id column in this table.

The clothing table stores data in the following columns:

• id stores the unique ID for each item.
• name stores the name of that item.
• size stores the size of that clothing: S, M, L, XL, 2XL, or 3XL.
• price stores the item's price.
• color_id stores the item's color (references the color table).
• category_id stores the item's category (references the category table).

The clothing_order table contains the following columns:

• id stores the unique order ID.
• customer_id stores the ID of the customer ordering the clothes (references the customer table).
• clothing_id stores the ID of the item ordered (references the clothing table).
• items stores how many of that clothing item the customer ordered.
• order_date stores the date of the order.

Let’s do some advanced SQL exercises that focus on JOINs .

Exercise 1: List All Clothing Items

Display the name of clothing items (name the column clothes ), their color (name the column color ), and the last name and first name of the customer(s) who bought this apparel in their favorite color. Sort rows according to color, in ascending order.

Solution explanation:

We want to display the column values from three different tables ( clothing , color , and customer ), including information on which customer ordered a certain item (from the clothing_order table). Therefore, we need to join these four tables on their common columns.

First, we select from the clothing_order table (aliased as co ) and join it with the clothing table (aliased as cl ). We join the tables using the primary key column of the clothing table ( id ) and the foreign key column of the clothing_order table ( clothing_id ); this foreign key column links the clothing and clothing_order tables.

Next, we join the color table (aliased as col ) with the clothing table (aliased as cl ). Here we use the primary key column of the color table ( id ) and the foreign key column of the clothing table ( color_id ).

Finally, we join the customer table (aliased as cus ) with the clothing_order table (aliased as co ). The foreign key of the clothing_order table ( customer_id ) links to the primary key of the customer table ( id ).

The ON clause stores the condition for the JOIN statement. For example, an item from the clothing table with an id of 23 is joined with an order from the clothing_order table where the clothing_id value equals 23.

Follow this article to see more examples on JOINing three (or more) tables . And here is how to LEFT JOIN multiple tables .

Exercise 2: Get All Non-Buying Customers

Select the last name and first name of customers and the name of their favorite color for customers with no purchases.

Here we need to display customers’ first and last names from the customer table and their favorite color name from the color table. We must do it only for customers who haven’t placed any orders yet; therefore, we require information from the clothing_order table. So the next step is to join these three tables.

First, we join the customer table (aliased as cus ) with the color table (aliased as col ). To do that, we use the following condition: the primary key column of the color table ( id ) must be equal to the foreign key column of the customer table ( favorite_color_id ). That lets us select the favorite color name instead of its ID.

Here is how to ensure that we list only customers who haven’t placed any orders yet:

• We LEFT JOIN the clothing_order table (aliased as o ) with the customer table (aliased as cus ) to ensure that all rows from the customer table (even the ones with no match) are listed.
• In the WHERE clause, we define a condition to display only the rows with the customer_id column from the clothing_order table equal to NULL (meaning only the customers whose IDs are not in the clothing_order table will be returned).

There are different types of JOINs , including INNER JOIN , LEFT JOIN , RIGHT JOIN , and FULL JOIN . You can learn more by following the linked articles.

Exercise 3: Select All Main Categories and Their Subcategories

Select the name of the main categories (which have a NULL in the parent_id column) and the name of their direct subcategory (if one exists). Name the first column category and the second column subcategory.

Each category listed in the category table has its own ID (stored in the id column); some also have the ID of their parent category (stored in the parent_id column). Thus, we can link the category table with itself to list main categories and their subcategories.

The kind of JOIN where we join a table to itself is colloquially called a self join . When you join a table to itself, you must give different alias names to each copy of the table. Here we have one category table aliased as c1 and another category table aliased as c2 .

We select the name from the category table (aliased as c1 ) and ensure that we list only main categories by having its parent_id column equal to NULL in the WHERE clause. Next, we join the category table (aliased as c1 ) with the category table (aliased as c2 ). The latter one provides subcategories for the main categories. Therefore, in the ON clause, we define that the parent_id column of c2 must be equal to the id column of c1 .

Read this article to learn more about self joins .

The exercises in this section have been taken from our course 2021 Monthly SQL Practice Sets - Advanced . Every month we publish a new SQL practice course in our Monthly SQL Practice track; every odd-numbered month, the course is at an advanced level. The advanced SQL practice courses from 2021 have been collected in our 2021 Monthly SQL Practice Sets - Advanced course. Check it out to find more JOIN exercises and other advanced SQL challenges.

Section 2: Advanced GROUP BY Exercises

In the following advanced SQL exercises, we’ll use a sportsclub database that stores information about runners and running events. It contains three tables: runner , event , and runner_event . Let's look at the data in this database.

The runner table contains the following columns:

• id stores the unique ID of the runner.
• name stores the runner's name.
• main_distance stores the distance (in meters) that the runner runs during events.
• age stores the runner's age.
• is_female indicates if the runner is male or female.

The event table contains the following columns:

• id stores the unique ID of the event.
• name stores the name of the event (e.g. London Marathon, Warsaw Runs, or New Year Run).
• start_date stores the date of the event.
• city stores the city where the event takes place.

The runner_event table contains the following columns:

• runner_id stores the ID of the runner.
• event_id stores the ID of the event.

Let’s do some advanced SQL exercises that focus on GROUP BY .

Exercise 4: Organize Runners Into Groups

Select the main distance and the number of runners that run the given distance ( runners_number ). Display only those rows where the number of runners is greater than 3.

Solution explanation :

We want to get the count of runners for each distance that they run. To do that, we need to group all runners by distance and use the COUNT() aggregate function to calculate how many runners are in each distance group.

We select the main_distance column and GROUP BY this column. Now when we use the COUNT() aggregate functions, it is going to give us the number of runners that match each main_distance value.

The GROUP BY clause is used to group rows from a table based on one or more columns. It divides the result set into subsets or groups, where each group shares the same values in the specified column(s). This allows us to perform aggregate functions (such as SUM() , COUNT() , AVG() , etc.) on each group separately.

Here are the most common GROUP BY interview questions .

To display only the groups with more than three runners, we use a HAVING clause that filters the values returned by the COUNT() aggregate function.

The HAVING clause is often used together with the GROUP BY clause to filter the grouped data based on specific conditions. It works similarly to the WHERE clause, but it operates on the grouped data rather than individual rows. Check out this article to learn more about the HAVING clause .

Exercise 5: How Many Runners Participate in Each Event

Display the event name and the number of club members that take part in this event (call this column runner_count ). Note that there may be events in which no club members participate. For these events, the runner_count should equal 0.

Here we want to display the event name from the event table and the number of participants from the runner table. The event and runner tables are linked by a many-to-many relation; to join these tables, we also need the runner_event table that relates events and runners.

First, we select from the event table. Then, we LEFT JOIN it with the runner_event table, which is further LEFT JOINed with the runner table. Why do we use the LEFT JOIN here? Because we want to ensure that all events (even the ones with no participants) are displayed.

We select the event name and the count of all participants; therefore, we need to GROUP BY the event name to get the count of participants per event. Please note that we use COUNT(runner_id) instead of COUNT(*) . This is to ensure that we display zero for events with no participants (i.e. for events that do not link to any runner_id ). You can read more about different variants of the COUNT() function here .

Exercise 6: Group Runners by Main Distance and Age

Display the distance and the number of runners there are for the following age categories: under 20, 20–29, 30–39, 40–49, and over 50. Use the following column aliases: under_20 , age_20_29 , age_30_39 , age_40_49 , and over_50 .

This is similar to Exercise 4 – we want to know the number of runners per distance value. So we select the main_distance column and GROUP BY this column. Then, we use several COUNT() aggregate functions to get the number of runners per distance. However, here we need to further divide the runners according to their age.

The CASE WHEN statement comes in handy here, as it can be used to evaluate conditions and return different values based on the results of those conditions. We can pass it as an argument to the COUNT() aggregate function to get the number of runners fulfilling a given condition. Let’s see how that works.

This CASE WHEN statement returns id only when a runner’s age is greater than or equal to 20 and less than 30. Otherwise, it returns NULL . When wrapped in the COUNT() aggregate function, it returns the count of runners fulfilling the condition defined in the CASE WHEN statement.

To get the number of runners for each of the five age groups, we need to use as many COUNT() functions and CASE WHEN statements as we have age groups. You can read about counting rows by combining CASE WHEN and GROUP BY here .

Section 3: Advanced Window Functions Exercises

In the following advanced SQL exercises, we’ll use a Northwind database for an online shop with numerous foods. It contains six tables: customers , orders , products , categories , order_items , and channels . Let's look at the data in this database.

The customers table has 15 columns:

• customer_id stores the ID of the customer.
• email stores the customer’s email address.
• full_name stores the customer’s full name.
• address stores the customer’s street and house number.
• city stores the city where the customer lives.
• region stores the customer’s region (not always applicable).
• postal_code stores the customer’s ZIP/post code.
• country stores the customer’s country.
• phone stores the customer’s phone number.
• registration_date stores the date on which the customer registered.
• channel_id stores the ID of the channel through which the customer found the shop.
• first_order_id stores the ID of the first order made by the customer.
• first_order_date stores the date of the customer’s first order.
• last_order_id stores the ID of the customer’s last (i.e. most recent) order.
• last_order_date stores the date of the customer’s last order.

The orders table has the following columns:

• order_id stores the ID of the order.
• customer_id stores the ID of the customer who placed the order.
• order_date stores the date when the order was placed.
• total_amount stores the total amount paid for the order.
• ship_name stores the name of the person to whom the order was sent.
• ship_address stores the address (house number and street) where the order was sent.
• ship_city stores the city where the order was sent.
• ship_region stores the region in which the city is located.
• ship_postalcode stores the destination post code.
• ship_country stores the destination country.
• shipped_date stores the date when the order was shipped.

The products table has the following columns:

• product_id stores the ID of the product.
• product_name stores the name of the product.
• category_id stores the category to which the product belongs.
• unit_price stores the price for one unit of the product (e.g. per bottle, pack, etc.).
• discontinued indicates if the product is no longer sold.

The categories table has the following columns:

• category_id stores the ID of the category.
• category_name stores the name of the category.
• description stores a short description of the category.

The order_items table has the following columns:

• order_id stores the ID of the order in which the product was bought.
• product_id stores the ID of the product purchased in the order.
• unit_price stores the per-unit price of the product. (Note that this can be different from the price in the product’s category; the price can change over time and discounts can be applied.)
• quantity stores the number of units bought in the order.
• discount stores the discount applied to the given product.

The channels table has the following columns:

• channel_name stores the name of the channel through which the customer found the shop.

Let’s do some advanced SQL exercises that focus on window functions.

Exercise 7: List the Top 3 Most Expensive Orders

Create a dense ranking of the orders based on their total_amount . The bigger the amount, the higher the order should be. If two orders have the same total_amount , the older order should go higher (you'll have to add the column order_date to the ordering). Name the ranking column rank . After that, select only the orders with the three highest dense rankings . Show the rank, order_id , and total_amount .

Let’s start with the first part of the instruction. We want to create a dense ranking of orders based on their total_amount (the greater the value, the higher the rank) and their order_date value (the older the date, the higher the rank). Please note that the rank value may be duplicated only when total_amount and order_date columns are both equal for more than one row.

To do that, we use the DENSE_RANK() window function. In its OVER() clause, we specify the order: descending for total_amount values and ascending for order_date values. We also display the order_id and total_amount columns from the orders table.

Until now, we listed all orders along with their dense rank values. But we want to see only the top 3 orders (where the rank column is less than or equal to 3). Let’s analyze the steps we take from here:

• We define a Common Table Expression (CTE) using this SELECT statement – i.e. we use the WITH clause followed by the CTE’s name and then place the SELECT statement within parentheses.
• Then we select from this CTE, providing the condition for the rank column in the WHERE clause.

You may wonder why we need such a complex syntax that defines a CTE and then queries it. You may say that we could set the condition for the rank column in the WHERE clause of the first SELECT query. Well, that’s not possible because of the SQL query order of execution.

We have to use the Common Table Expression here because you can’t use window functions in the WHERE clause. The order of operations in SQL is as follows:

• FROM , JOIN
• Aggregate functions
• Window functions

You may only use window functions in SELECT and ORDER BY clauses. If you want to refer to window functions in the WHERE clause, you must place the window function computation in a CTE (like we did in our example) or in a subquery and refer to the window function in the outer query.

Follow this article to learn more about CTEs and recursive CTEs .

To give you some background on the available ranking functions , there are three functions that let you rank your data: RANK() , DENSE_RANK() , and ROW_NUMBER() . Let’s see them in action.

The RANK() function assigns the same rank if multiple consecutive rows have the same value. Then, the next row gets the next rank as if the previous rows had distinct values. Here, the ranks 1,1,1 are followed by 4 (as if it was 1,2,3 instead of 1,1,1 ).

The DENSE_RANK() function also assigns the same rank if multiple consecutive rows have the same value. Then, the next row gets the next rank one greater than the previous one. Here, 1,1,1 is followed by 2.

The ROW_NUMBER() function assigns consecutive numbers to each next row without considering the row values.

Here is an article on how to rank data . You can also learn more about differences between SQL’s ranking functions .

Exercise 8: Compute Deltas Between Consecutive Orders

In this exercise, we're going to compute the difference between two consecutive orders from the same customer.

Show the ID of the order ( order_id ), the ID of the customer ( customer_id ), the total_amount of the order, the total_amount of the previous order based on the order_date (name the column previous_value ), and the difference between the total_amount of the current order and the previous order (name the column delta ).

Here we select the order ID, customer ID, and total amount from the orders table. The LAG() function fetches the previous total_amount value. In the OVER() clause, we define the LAG() function separately for each customer and order the outcome by an order date. Finally, we subtract the value returned by the LAG() function from the total_amount value for each row to get the delta.

The previous_value column stores null for the first row, as there are no previous values. Therefore, the delta column is also null for the first row. The following delta column values store the differences between consecutive orders made by the same customer.

It is worth mentioning that a delta represents the difference between two values. By calculating the delta between daily sales amounts, we can determine the direction of sales growth/decline on a day-to-day basis.

Follow this article to learn more about calculating differences between two rows . And here is how to compute year-over-year differences .

Exercise 9: Compute the Running Total of Purchases per Customer

For each customer and their orders, show the following:

• customer_id – the ID of the customer.
• full_name – the full name of the customer.
• order_id – the ID of the order.
• order_date – the date of the order.
• total_amount – the total spent on this order.
• running_total – the running total spent by the given customer.

Sort the rows by customer ID and order date.

A running total refers to the calculation that accumulates the values of a specific column or expression as rows are processed in a result set. It provides a running sum of the values encountered up to the current row. A running total is calculated by adding the current value to the sum of all previous values. This can be particularly useful in various scenarios, such as tracking cumulative sales, calculating running balances or analyzing cumulative progress over time.

Follow this article to learn more about computing a running total . And here is an article about computing running averages .

We select customer ID, order ID, order date, and order total from the orders table. Then, we join the orders table with the customers table on their respective customer_id columns so we can display the customer's full name.

We use the SUM() window function to calculate the running total for each customer separately ( PARTITION BY orders.customer_id ) and then order ascendingly by date ( ORDER BY orders.order_date ).

Finally, we order the output of this query by customer ID and order date.

Section 4: Advanced Recursive Query Exercises

In the following advanced SQL exercises, we’ll use a website database that stores information about students and courses. It contains three tables: student , course , and student_course . Let's look at the data in this database.

The student table contains the following columns:

• id stores the unique ID number for each student.
• name stores the student's name.
• email stores the student's email.
• invited_by_id stores the ID of the student that invited this student to the website. If the student signed up without an invitation, this column will be NULL.

The course table consists of the following columns:

• id stores the unique ID number for each course.
• name stores the course's name.

The student_course table contains the following columns:

• id stores the unique ID for each row.
• student_id stores the ID of the student.
• course_id stores the ID of the course.
• minutes_spent stores the number of minutes the student spent on the course.
• is_completed is set to True when the student finishes the course.

The exercises in this section have been taken from our Window Functions Practice Set . In this set, you will find more window function exercises on databases that store retail, track competitions, and website traffic.

Let’s do some advanced SQL exercises that focus on recursive queries.

Exercise 10: Find the Invitation Path for Each Student

Show the path of invitations for each student (name this column path ). For example, if Mary was invited by Alice and Alice wasn't invited by anyone, the path for Mary should look like this: Alice->Mary .

Include each student's id , name , and invited_by_id in the results.

This exercise requires us to create a custom value for the path column that contains the invitation path for each customer. For example, Ann Smith was invited by Veronica Knight , who in turn was invited by Karli Roberson ; hence, we get the path column as Karli Roberson->Veronica Knight->Ann Smith for the name Ann Smith .

As you may notice, we need a recursion mechanism to dig down into the invitation path. We can write a recursive query by defining it with the WITH RECURSIVE statement, followed by the query name.

The content of the hierarchy recursive query is as follows:

• We select the id , name , and invited_by_id columns from the student table. Then, we use the CAST() function to cast the name column type to the TEXT data type, ensuring smooth concatenation (with -> and the following names) in the main query. The WHERE clause condition ensures that only students who haven’t been invited are listed by this query.
• The UNION ALL operator combines the result sets of two or more SELECT statements without removing duplicates. Here the queries on which UNION ALL is performed have the same sets of four columns; the result set of one is appended to the results set of another.
• In the next SELECT statement, we again select the id , name , and invited_by_id columns from the student table. Then, we concatenate the path column (that comes from the hierarchy recursive query as defined in the first SELECT statement) with the -> sign and the student name. To accomplish this concatenation, we select from both the student table and the hierarchy recursive query.(This is where the recursive mechanism comes into play.) In the WHERE clause, we define that the invited_by_id column of the student table is equal to the id column of the hierarchy recursive query, so we get the student name who invited the current student; on the next iteration, we get the name of the student who invited that student, and so on.

This is called a recursive query, as it queries itself to work its way down the invitation path.

Advancing One Query at a Time

The advanced SQL exercises presented in this article provide a comprehensive platform for honing your SQL skills, one query at a time. By delving into window functions, JOINs , GROUP BY , and more, you have expanded your understanding of complex SQL concepts and gained hands-on experience in solving real-world data challenges.

Practice is the key to mastering SQL skills. Through consistent practice, you can elevate your proficiency and transform your theoretical knowledge into practical expertise. This article showcased exercises from our courses; you can discover more exercises like this by enrolling in our:

Sign up now and get started for free! Good luck!

You may also like

How Do You Write a SELECT Statement in SQL?

What Is a Foreign Key in SQL?

Enumerate and Explain All the Basic Elements of an SQL Query