Beyond the Basics: An Exploration and Case Study of Complex Queries in MySQL”:

Abstract

MySQL is a popular open-source relational database management system (RDBMS). It is known for its ease of use and scalability. However, I do prefer it for it’s capabilities to handle complex queries. This article explores the concept of complex queries in MySQL and provides a case study of a school database admin.

Introduction

A complex query is a query that uses more than one parameter and may comprise a combination of several joins across multiple tables and quite a few nested subqueries. Complex queries also frequently involve heavy use of AND and OR clauses.

There are many reasons why you might want to use a complex query in MySQL. For example, you might want to:

• Search for data that meets multiple criteria
• Join data from multiple tables
• Nested subqueries

Case Study

Consider yourself as a Database Administrator at the prestigious learning center called ischool , renowned for its extensive and diverse course offerings. The university operates a MySQL database named “ischool” that stores a wealth of information, from course details to student records. The SQL file can be downloaded here and the original questions are here. (Note: These files are from university of Maryland and are deletable on request)

Your task as a Database Administrator involves handling data requests, generating reports, and maintaining the integrity of the database. Today, you have been assigned four tasks as follows (Try out on your own before checking the codes)

Q.1) Show all class locations

Using the ischool database, write a SELECT query that returns locations of ischool course sections, exactly as shown in the figure below. Filtering is not used, so all 100 rows should be returned.

Note that the result set is sorted by one column.

It is possible that the other two, unsorted, columns may look different on your computer due to indeterminate sorting.

Code :

SELECT L.building_name,L.room_number,C.section_id
FROM locations L
JOIN course_sections C ON L.location_id=C.location_id
ORDER BY L.building_name

Q.2) Aggregate sections by location

Write a SELECT query that returns the total number of course sections hosted in each building, exactly as shown in the figure below. Filter out buildings that only host one or zero course sections.

Note that the result set is sorted by one column

Code :

SELECT L.building_name, COUNT(C.section_id) AS section_count
FROM locations L
JOIN course_sections C ON L.location_id=C.location_id
GROUP  BY L.building_name
HAVING COUNT(C.section_id)>1;

Q.3) Aggregate enrollment by location

Write a SELECT query that returns the total student enrollment of the course sections hosted in each building, exactly as shown in the figure below. Filtering is not used, so all 9 buildings that host classes with any number of enrolled students should be returned. (The reason there are only 9 buildings is because most course sections do not have any registered students yet.)

Note that the result set is sorted by one column

Code :

SELECT L.building_name, COUNT(E.person_id) AS enroll_count
FROM locations L
JOIN course_sections C ON L.location_id=C.location_id
JOIN enrollments E ON C.section_id= E.section_id
GROUP   BY L.building_name
ORDER BY L.building_name;

Q.4) Aggregate both sections and enrollment by location, using a subquery or CTE

Write a SELECT query that returns both the total number of course sections and the total student enrollment of the course sections hosted in each building, exactly as shown in the figure below.

Remember that MySQL does not allow aggregating on different sets of columns in one SELECT statement. Therefore, you must use either a subquery or a CTE to get the desired result set. (Some enrollment counts will show up as zero because some buildings may have several empty course sections.)

Filter out buildings that only host one or zero course sections.

Note that the result set is sorted by one column.

Code :

WITH section_counts AS (
    SELECT L.building_name, COUNT(DISTINCT C.section_id) AS section_count
    FROM locations L
    JOIN course_sections C ON L.location_id = C.location_id 
    GROUP BY L.building_name 
    HAVING COUNT(DISTINCT C.section_id) > 1
),
enrollment_counts AS (
    SELECT L.building_name, COUNT(E.person_id) AS enroll_count
    FROM locations L 
    JOIN course_sections C ON L.location_id = C.location_id 
    JOIN enrollments E ON C.section_id = E.section_id
    GROUP BY L.building_name
) 
SELECT S.building_name, S.section_count, E.enroll_count
FROM section_counts S 
LEFT JOIN enrollment_counts E ON S.building_name = E.building_name 
ORDER BY S.building_name;

Conclusion

This article has explored the concept of complex queries in MySQL and provided a case study of a complex query that can be used to analyze ischool data. Complex queries can be a powerful tool for data analysis. However, they can also be difficult to write. If you are not familiar with MySQL, it is a good idea to start with simple queries and then gradually work your way up to more complex queries.