Database Schema
CREATE TABLE learners(
learner_id VARCHAR(10),
full_name VARCHAR(10),
birth_date DATETIME,
gender VARCHAR(10)
);
INSERT INTO learners VALUES
('01', 'Zhao Lei', '1990-01-01', 'Male'),
('02', 'Qian Dian', '1990-12-21', 'Male'),
('03', 'Sun Feng', '1990-05-20', 'Male'),
('04', 'Li Yun', '1990-08-06', 'Male'),
('05', 'Zhou Mei', '1991-12-01', 'Female'),
('06', 'Wu Lan', '1992-03-01', 'Female'),
('07', 'Zheng Zhu', '1989-07-01', 'Female'),
('08', 'Wang Ju', '1990-01-20', 'Female');
CREATE TABLE subjects(
subject_id VARCHAR(10),
subject_name VARCHAR(10),
instructor_id VARCHAR(10)
);
INSERT INTO subjects VALUES
('01', 'Chinese', '02'),
('02', 'Mathematics', '01'),
('03', 'English', '03');
CREATE TABLE instructors(
instructor_id VARCHAR(10),
instructor_name VARCHAR(10)
);
INSERT INTO instructors VALUES
('01', 'Zhang San'),
('02', 'Li Si'),
('03', 'Wang Wu');
CREATE TABLE grades(
learner_id VARCHAR(10),
subject_id VARCHAR(10),
mark DECIMAL(18,1)
);
INSERT INTO grades VALUES
('01', '01', 80), ('01', '02', 90), ('01', '03', 99),
('02', '01', 70), ('02', '02', 60), ('02', '03', 80),
('03', '01', 80), ('03', '02', 80), ('03', '03', 80),
('04', '01', 50), ('04', '02', 30), ('04', '03', 20),
('05', '01', 76), ('05', '02', 87),
('06', '01', 31), ('06', '03', 34),
('07', '02', 89), ('07', '03', 98);
Query Challenges
- Find learners who scored higher in subject '01' than in subject '02'
- Get learners with average marks above 60
- Show enrollment count and total marks per learner
- Count instructors with surname 'Li'
- Find learners who haven't taken any course from 'Zhang San'
- Get learners enrolled in both subjects '01' and '02'
- Find learners with all subject marks below 60
- Identify learners who haven't enrolled in all subjects
- Find learners sharing at least one subject with learner '01'
- Get learners with identical subject enrollment as learner '01'
- Calculate average marks for subjects taught by 'Zhang San'
- List learners not enrolled in any of 'Zhang San's courses
- Find learners with two or more failing marks
- Generate subject statistics (max, min, avg, pass rate)
- Rank instructors by their average teaching performance
- Get 2nd and 3rd highest scorers per subject
- Calculate percentage distribution across mark ranges per subject
- Rank learners by average marks
- Find top 3 performers per subject
- Count male and female learners
- Find learners with 'Feng' in their name
- Identify duplicate names with same gender
- Get learners born in 1990
- Find top scorer among 'Zhang San's students
- Identify learners enrolled in all subjects
- Calculate current age for each learner
- Find learners with birthdays this week
- Find learners with birthdays next week
- Find learners with birthdays this month
- Find learners with birthdays next month
Solutions
SELECT l.learner_id, l.full_name
FROM learners l
WHERE EXISTS (
SELECT 1 FROM grades g1
WHERE g1.learner_id = l.learner_id AND g1.subject_id = '01'
AND g1.mark > (
SELECT g2.mark FROM grades g2
WHERE g2.learner_id = l.learner_id AND g2.subject_id = '02'
)
);
SELECT learner_id, AVG(mark) AS avg_mark
FROM grades
GROUP BY learner_id
HAVING AVG(mark) > 60;
SELECT l.learner_id, l.full_name,
COUNT(DISTINCT g.subject_id) AS subject_count,
COALESCE(SUM(g.mark), 0) AS total_marks
FROM learners l
LEFT JOIN grades g ON l.learner_id = g.learner_id
GROUP BY l.learner_id, l.full_name;
SELECT COUNT(*) AS li_count
FROM instructors
WHERE instructor_name LIKE 'Li%';
SELECT learner_id, full_name
FROM learners
WHERE learner_id NOT IN (
SELECT DISTINCT g.learner_id
FROM grades g
JOIN subjects s ON g.subject_id = s.subject_id
JOIN instructors i ON s.instructor_id = i.instructor_id
WHERE i.instructor_name = 'Zhang San'
);
SELECT l.learner_id, l.full_name
FROM learners l
WHERE EXISTS (SELECT 1 FROM grades WHERE learner_id = l.learner_id AND subject_id = '01')
AND EXISTS (SELECT 1 FROM grades WHERE learner_id = l.learner_id AND subject_id = '02');
SELECT l.learner_id, l.full_name
FROM learners l
WHERE NOT EXISTS (
SELECT 1 FROM grades g
WHERE g.learner_id = l.learner_id AND g.mark >= 60
);
SELECT l.learner_id, l.full_name
FROM learners l
WHERE (SELECT COUNT(*) FROM grades g WHERE g.learner_id = l.learner_id) <
(SELECT COUNT(*) FROM subjects);
SELECT DISTINCT l.learner_id, l.full_name
FROM learners l
JOIN grades g ON l.learner_id = g.learner_id
WHERE g.subject_id IN (SELECT subject_id FROM grades WHERE learner_id = '01')
AND l.learner_id != '01';
SELECT l.learner_id, l.full_name
FROM learners l
WHERE (
SELECT COUNT(*) FROM grades
WHERE learner_id = l.learner_id AND subject_id IN
(SELECT subject_id FROM grades WHERE learner_id = '01')
) = (
SELECT COUNT(*) FROM grades WHERE learner_id = '01'
) AND l.learner_id != '01';
SELECT AVG(g.mark) AS avg_mark
FROM grades g
JOIN subjects s ON g.subject_id = s.subject_id
JOIN instructors i ON s.instructor_id = i.instructor_id
WHERE i.instructor_name = 'Zhang San';
SELECT full_name
FROM learners
WHERE learner_id NOT IN (
SELECT DISTINCT g.learner_id
FROM grades g
JOIN subjects s ON g.subject_id = s.subject_id
JOIN instructors i ON s.instructor_id = i.instructor_id
WHERE i.instructor_name = 'Zhang San'
);
SELECT l.learner_id, l.full_name, AVG(g.mark) AS avg_mark
FROM learners l
JOIN grades g ON l.learner_id = g.learner_id
WHERE l.learner_id IN (
SELECT learner_id
FROM grades
WHERE mark < 60
GROUP BY learner_id
HAVING COUNT(*) >= 2
)
GROUP BY l.learner_id, l.full_name;
SELECT s.subject_id, s.subject_name,
MAX(g.mark) AS highest,
MIN(g.mark) AS lowest,
AVG(g.mark) AS average,
SUM(CASE WHEN g.mark >= 60 THEN 1 ELSE 0 END) / COUNT(*) AS pass_rate
FROM subjects s
JOIN grades g ON s.subject_id = g.subject_id
GROUP BY s.subject_id, s.subject_name;
SELECT i.instructor_id, AVG(g.mark) AS avg_performance
FROM instructors i
JOIN subjects s ON i.instructor_id = s.instructor_id
JOIN grades g ON s.subject_id = g.subject_id
GROUP BY i.instructor_id
ORDER BY avg_performance DESC;
SELECT learner_id, subject_id, mark, subject_rank
FROM (
SELECT learner_id, subject_id, mark,
RANK() OVER (PARTITION BY subject_id ORDER BY mark DESC) AS subject_rank
FROM grades
) ranked
WHERE subject_rank IN (2, 3);
SELECT g.subject_id, s.subject_name,
SUM(CASE WHEN g.mark BETWEEN 85 AND 100 THEN 1 ELSE 0 END) / COUNT(*) AS excellent_pct,
SUM(CASE WHEN g.mark BETWEEN 70 AND 85 THEN 1 ELSE 0 END) / COUNT(*) AS good_pct,
SUM(CASE WHEN g.mark BETWEEN 60 AND 70 THEN 1 ELSE 0 END) / COUNT(*) AS satisfactory_pct,
SUM(CASE WHEN g.mark < 60 THEN 1 ELSE 0 END) / COUNT(*) AS poor_pct
FROM grades g
JOIN subjects s ON g.subject_id = s.subject_id
GROUP BY g.subject_id, s.subject_name;
SELECT learner_id, avg_mark,
RANK() OVER (ORDER BY avg_mark DESC) AS performance_rank
FROM (
SELECT learner_id, AVG(mark) AS avg_mark
FROM grades
GROUP BY learner_id
) avg_data;
SELECT subject_id, learner_id, mark, performance_rank
FROM (
SELECT subject_id, learner_id, mark,
RANK() OVER (PARTITION BY subject_id ORDER BY mark DESC) AS performance_rank
FROM grades
) ranked
WHERE performance_rank <= 3;
SELECT gender, COUNT(*) AS learner_count
FROM learners
GROUP BY gender;
SELECT learner_id, full_name
FROM learners
WHERE full_name LIKE '%Feng%';
SELECT full_name, gender, COUNT(*) AS duplicate_count
FROM learners
GROUP BY full_name, gender
HAVING COUNT(*) > 1;
SELECT learner_id, birth_date
FROM learners
WHERE YEAR(birth_date) = 1990;
SELECT l.full_name, g.mark
FROM grades g
JOIN subjects s ON g.subject_id = s.subject_id
JOIN instructors i ON s.instructor_id = i.instructor_id
JOIN learners l ON g.learner_id = l.learner_id
WHERE i.instructor_name = 'Zhang San'
ORDER BY g.mark DESC
LIMIT 1;
SELECT learner_id
FROM grades
GROUP BY learner_id
HAVING COUNT(DISTINCT subject_id) = (SELECT COUNT(*) FROM subjects);
SELECT learner_id, YEAR(CURDATE()) - YEAR(birth_date) -
(DATE_FORMAT(CURDATE(), '%m%d') < DATE_FORMAT(birth_date, '%m%d')) AS age
FROM learners;
SELECT learner_id
FROM learners
WHERE WEEKOFYEAR(birth_date) = WEEKOFYEAR(CURDATE());
SELECT learner_id
FROM learners
WHERE WEEKOFYEAR(birth_date) = WEEKOFYEAR(DATE_ADD(CURDATE(), INTERVAL 1 WEEK));
SELECT learner_id
FROM learners
WHERE MONTH(birth_date) = MONTH(CURDATE());
SELECT learner_id
FROM learners
WHERE MONTH(birth_date) = MONTH(DATE_ADD(CURDATE(), INTERVAL 1 MONTH));