Biology Course Alumni Search

Introduction

In this project, you will learn how to search for students who have taken courses offered by a specific department, in this case, the Biology department. This is a common task that teachers often need to perform when calculating student grades.

👀 Preview

MySQL [edusys]> SOURCE /home/labex/project/multiTableQuery.sql
+-------+--------+-----------+-------+
| ID    | name   | course_id | grade |
+-------+--------+-----------+-------+
| 98988 | Tanaka | BIO-101   | A     |
| 98988 | Tanaka | BIO-301   | NULL  |
+-------+--------+-----------+-------+
2 rows in set (0.002 sec)

🎯 Tasks

In this project, you will learn:

• How to start and access the MySQL database using the sudo command
• How to import a SQL file (edusys.sql) into the MySQL database
• How to write a SQL query that joins multiple tables (student, course, and takes) to retrieve the required information
• How to run the SQL query and display the resulting table

🏆 Achievements

After completing this project, you will be able to:

• Understand how to work with multiple database tables to retrieve specific information
• Write complex SQL queries that involve joining tables and filtering data
• Apply your SQL knowledge to real-world scenarios, such as searching for student records in a course management system

Skills Graph

Start MySQL and Access the Database

In this step, you will learn how to start the MySQL database and access it using the sudo command without any password.

1. Start the MySQL service:

sudo service mysql start

2. Access the MySQL database using the sudo command:

sudo mysql

Now you have successfully started the MySQL database and accessed it.

Import the edusys.sql File

In this step, you will learn how to import the edusys.sql file into the MySQL database.

1. Ensure you are in the MySQL prompt:

MariaDB [(none)]>

2. Import the edusys.sql file:

MariaDB [(none)]> SOURCE /home/labex/project/edusys.sql;

The edusys.sql file has now been imported into the MySQL database.

Write the SQL Query

In this step, you will write the SQL query to search for the student ID, name, course ID, and grade of all students who have taken courses offered by the Biology department.

1. Open the multiTableQuery.sql file and add the following SQL query:

SELECT
    s.ID,
    s.name,
    c.course_id,
    t.grade
FROM
    takes t
    JOIN student s ON t.ID = s.ID
    JOIN course c ON t.course_id = c.course_id
WHERE
    s.dept_name = 'Biology';

This query joins the student, course, and takes tables to retrieve the required information for students who have taken courses in the Biology department.

Run the SQL Query

In this step, you will run the SQL query stored in the multiTableQuery.sql file.

1. Ensure you are in the MySQL prompt:

MariaDB [edusys]>

2. Run the SQL script:

MariaDB [edusys]> SOURCE /home/labex/project/multiTableQuery.sql;

The query will execute, and the resulting table will be displayed with the columns labeled as ID, name, course_id, and grade.

+-------+--------+-----------+-------+
| ID    | name   | course_id | grade |
+-------+--------+-----------+-------+
| 98988 | Tanaka | BIO-101   | A     |
| 98988 | Tanaka | BIO-301   | NULL  |
+-------+--------+-----------+-------+
2 rows in set (0.002 sec)

Summary

Congratulations! You have completed this project. You can practice more labs in LabEx to improve your skills.