MySQL Practice questions for interview - student table | Set 1
Here’s a sample table with some data and relevant queries for practice.
Sample Table: students
| student_id | name | age | course | total_fees | discount | join_date |
|---|---|---|---|---|---|---|
| 1 | John Doe | 20 | Web Dev | 30000 | 10 | 2024-01-10 |
| 2 | Alice Smith | 22 | Data Science | 50000 | 15 | 2023-12-15 |
| 3 | Bob Johnson | 25 | AI & ML | 60000 | 5 | 2024-03-05 |
| 4 | Carol Baker | 21 | Cybersecurity | 45000 | 20 | 2024-06-20 |
| 5 | Dave Wilson | 23 | Web Dev | 30000 | 0 | 2024-08-15 |
Practice Queries
1. Retrieve all students who joined after January 1, 2024.
SELECT * FROM students
WHERE join_date > '2024-01-01';
2. Calculate the final fees (after discount) for each student.
SELECT name,
total_fees - (total_fees * discount / 100) AS final_fees
FROM students;
3. Find students enrolled in the "Web Dev" course.
SELECT * FROM students
WHERE course = 'Web Dev';
4. Find the average age of students.
SELECT AVG(age) AS average_age
FROM students;
5. List students with discounts greater than 10%.
SELECT * FROM students
WHERE discount > 10;
6. Count the number of students in each course.
SELECT course, COUNT(*) AS student_count
FROM students
GROUP BY course;
7. Find the student with the highest total fees.
SELECT * FROM students
ORDER BY total_fees DESC
LIMIT 1;
8. Retrieve students whose names contain the letter "o".
SELECT * FROM students
WHERE name LIKE '%o%';
9. Find the total revenue generated (sum of all fees after discounts).
SELECT SUM(total_fees - (total_fees * discount / 100)) AS total_revenue
FROM students;
10. Get the list of students sorted by their joining date (oldest first).
SELECT * FROM students
ORDER BY join_date ASC;
11. Retrieve the second-highest total fee in the table.
SELECT MAX(total_fees) AS second_highest_fee
FROM students
WHERE total_fees < (SELECT MAX(total_fees) FROM students);
12. Find students who are younger than 22 years old and have a discount of at least 10%.
SELECT * FROM students
WHERE age < 22 AND discount >= 10;
13. Retrieve courses and their average total fees.
SELECT course, AVG(total_fees) AS average_fees
FROM students
GROUP BY course;
14. List students who joined in the year 2024.
SELECT * FROM students
WHERE YEAR(join_date) = 2024;
15. Find the course with the highest number of enrollments.
SELECT course, COUNT(*) AS enrollments
FROM students
GROUP BY course
ORDER BY enrollments DESC
LIMIT 1;
These queries should help you get comfortable with MySQL operations and prepare you for real-world scenarios. Let me know if you'd like additional examples or explanations!
Thank You ,
AGN HUB TECH & IT SOLUTIONS
Comments
Post a Comment