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

Best MySQL Training institute in Jaipur

Comments

  1. This article highlights the importance of seeking the right academic guidance when coursework becomes demanding. Many students choose simulation assignment help to better understand complex models, software applications, and analytical techniques. Expert support makes difficult concepts easier to grasp while improving assignment quality. It also helps students strengthen problem-solving skills, meet deadlines, and gain greater confidence in completing simulation-based academic tasks successfully.

    ReplyDelete

Post a Comment