Search for Favorite Courses

Introduction

In this project, you will learn how to create a "favorite" table in a MySQL database. The "favorite" table will store the courses with the longest study time for each user in the "usercourse" table.

👀 Preview

MariaDB [labex]> select * from favorite limit 10;
+----+-----------+-------------+------------+
| id | user_name | course_name | study_time |
+----+-----------+-------------+------------+
|  2 | user_01   | English     |        101 |
|  3 | user_02   | Chinese     |        102 |
|  4 | user_03   | Chemical    |        103 |
|  5 | user_04   | Physics     |        104 |
|  6 | user_05   | Biology     |        105 |
|  7 | user_06   | Painting    |        106 |
|  8 | user_07   | Music       |        107 |
|  9 | user_08   | Computer    |        108 |
| 10 | user_09   | History     |        109 |
| 11 | user_10   | Math        |        110 |
+----+-----------+-------------+------------+
10 rows in set (0.000 sec)

🎯 Tasks

In this project, you will learn:

• How to access the MySQL database using the sudo command
• How to import data from a SQL script into the MySQL database
• How to create a new table with specific columns using the CREATE TABLE AS statement
• How to join multiple tables to retrieve the necessary data for the "favorite" table

🏆 Achievements

After completing this project, you will be able to:

• Understand how to manage a MySQL database using the command line
• Implement a SQL query to create a new table based on data from multiple existing tables
• Apply the concept of finding the maximum value for each group in a table
• Demonstrate your ability to work with SQL statements to achieve a specific data management task

Access MySQL and Import the Database

In this step, you will learn how to access the MySQL database and import the necessary data.

1. Open the terminal and start the MySQL service using the following command:

sudo service mysql start

2. Access the MySQL client using the following command:

sudo mysql

3. Import the data from the labex_db_info.sql script into MySQL:

SOURCE /home/labex/project/labex_db_info.sql;

This will import the necessary data into the MySQL database.

Create the favorite Table

In this step, you will create the "favorite" table, which will store the courses with the longest study time for each user.

1. Open the searchForFavoriteCourses.sql file.

2. In the searchForFavoriteCourses.sql file, create the "favorite" table with the following columns:

   • id (primary key)
   • user_name (username)
   • course_name (course name)
   • study_time (study time)

create table favorite as
SELECT t1.id, t1.user_name, t2.course_name, t1.study_time
FROM (
         SELECT a.id, a.course_id, a.study_time, b.user_name
         FROM (
                  SELECT t0.id, t0.user_id, t0.course_id, t0.study_time
                  FROM usercourse t0
                  INNER JOIN (
                      SELECT user_id, MAX(study_time) AS max_study_time
                      FROM usercourse
                      GROUP BY user_id
                  ) t_max ON t0.user_id = t_max.user_id AND t0.study_time >= t_max.max_study_time
              ) a LEFT JOIN user b ON a.user_id = b.user_id
) t1 LEFT JOIN course t2 ON t1.course_id = t2.course_id

This query creates the "favorite" table by joining the "usercourse" and "user" tables to get the user name, and then joining the "course" table to get the course name. The table stores the courses with the longest study time for each user.

Verify the favorite Table

In this step, you will verify the contents of the "favorite" table.

1. In the MySQL prompt, run the following command to execute the searchForFavoriteCourses.sql script:

SOURCE /home/labex/project/searchForFavoriteCourses.sql;

2. Execute the following query to display the first 10 rows of the "favorite" table:

MariaDB [labex]> select * from favorite limit 10;
+----+-----------+-------------+------------+
| id | user_name | course_name | study_time |
+----+-----------+-------------+------------+
|  2 | user_01   | English     |        101 |
|  3 | user_02   | Chinese     |        102 |
|  4 | user_03   | Chemical    |        103 |
|  5 | user_04   | Physics     |        104 |
|  6 | user_05   | Biology     |        105 |
|  7 | user_06   | Painting    |        106 |
|  8 | user_07   | Music       |        107 |
|  9 | user_08   | Computer    |        108 |
| 10 | user_09   | History     |        109 |
| 11 | user_10   | Math        |        110 |
+----+-----------+-------------+------------+
10 rows in set (0.000 sec)

This query will display the first 10 rows of the "favorite" table, showing the user name, course name, and study time for each record.

Congratulations! You have successfully created the "favorite" table and verified its contents.

Summary

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