Are you ready to dive into the world of database management and take your SQL skills to the next level? LabEx, a leading online platform for programming education, has curated a collection of 10 exciting SQL challenges that will put your problem-solving abilities to the test.
From managing database indexes and modifying table definitions to executing complex queries and creating stored functions, these challenges cover a wide range of SQL concepts and techniques. Whether you're a beginner looking to build a solid foundation or an experienced developer seeking to sharpen your skills, this collection has something for everyone.
1. Database Management and SQL Self-Join (Challenge) 🗃️
In this challenge, you'll learn how to generate a database, create a table, and insert data. Then, you'll execute a self-join query to determine the province associated with a city. The final result will display the city and its corresponding province.
2. City Names and Respective Countries (Challenge) 🌍
This challenge focuses on executing an equi-join query to combine city and country tables and retrieve the city names with their respective full country names. The goal is to create a script that limits the results to ten records and abbreviates the table names as ci (city) and co (country).
3. Modify Table Definition 📝
In this challenge, you'll create a database named Challenge01 and modify the table definition of the student table within the database.
4. Managing Database Indexes in MySQL (Challenge) 🔍
Your task in this challenge is to add a course index to the given database. You'll need to import the data from the provided script into MySQL and add an index to the title field of the course table with the index name ix_title. The final result is a script file named addIndex.sql saved in the ~/project directory.
5. Top 5 Countries by Land Area (Challenge) 🌎
In this challenge, you'll query the land area information of countries from the country table to find the top 5 countries with the largest land area. The goal is to retrieve values from the Name and SurfaceArea columns.
6. Search for Favorite Courses (Challenge) 🎓
This challenge involves querying and creating a new table called favorite to store the favorite courses of LabEx users. The table will contain the user_name, course_name, and study_time for each user's favorite course. Your objective is to find the courses with the longest study time for each user in the usercourse table and store them in the favorite table.
7. Change Default Value for Countrylanguage Table (Challenge) 🌐
To complete this challenge, you'll need to change the default value for the IsOfficial field in the countrylanguage table. Specifically, you'll need to modify the default value to T. You'll import the world.sql script into a MySQL database and create a SQL script called changeDefault.sql.
8. Salary Range for Teachers by Department (Challenge) 🏫
In this challenge, you'll use MySQL built-in functions to query the maximum and minimum salary of teachers in each department of a university. You'll import the data from a provided script and write a script to perform the queries. The results should include the department name, maximum salary, and minimum salary for each department.
9. Check the Maximum Value Again 🔍
This challenge involves creating a stored function in MySQL to determine the maximum value, and then using CallableStatement in Java to call this stored function. You'll also be importing data from a script, setting up a Java project, and using the MySQL driver. The purpose of this lab is to test your understanding and implementation of stored functions and how to use them in conjunction with Java to perform database operations.
10. Modify Student Table Attributes (Challenge) 📊
To complete this challenge, you'll need to modify the attributes of the student table in the edusys database. Your task is to change the name attribute to student_name and output the updated structure of the student table to the command line. You'll use the ALTER statement to accomplish this task, and the resulting script must be saved as alterStudent.sql.
Dive into these SQL challenges and unlock your true potential as a database management expert! 💪 Good luck, and happy coding!
Want to Learn More?
- 🌳 Learn the latest MySQL Skill Trees
- 📖 Read More MySQL Tutorials
- 💬 Join our Discord or tweet us @WeAreLabEx
Top comments (0)
Some comments may only be visible to logged-in visitors. Sign in to view all comments.