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
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