Master MySQL Interview Questions: 33 Essential Queries and Answers

This article provides a complete MySQL schema for a simple academic database, populates it with representative data, and presents a collection of typical interview‑style queries with their solutions.

Database schema and data population

The schema consists of five tables: course, grade, score, student, and teacher. All foreign‑key constraints are defined to enforce referential integrity.

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- course table
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `cno`   CHAR(5)   NOT NULL,
  `cname` VARCHAR(10) NOT NULL,
  `tno`   INT(3) DEFAULT NULL,
  PRIMARY KEY (`cno`),
  INDEX `tno`(`tno`),
  CONSTRAINT `course_ibfk_1` FOREIGN KEY (`tno`) REFERENCES `teacher`(`tno`)
    ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='课程表';

INSERT INTO `course` VALUES
  ('3-105','计算机导论','825'),
  ('3-245','操作系统','804'),
  ('6-166','数字电路','856'),
  ('9-888','高等数学','831');

-- grade table
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade` (
  `low`  INT DEFAULT NULL,
  `upp`  INT DEFAULT NULL,
  `rank` CHAR(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='等级表';

INSERT INTO `grade` VALUES
  (90,100,'A'),(80,89,'B'),(70,79,'C'),(60,69,'D'),(0,59,'E');

-- score table
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
  `sno`    INT(3) DEFAULT NULL,
  `cno`    CHAR(5) NOT NULL,
  `degree` DECIMAL(4,1) DEFAULT NULL,
  INDEX `sno`(`sno`),
  INDEX `cno`(`cno`),
  CONSTRAINT `score_ibfk_1` FOREIGN KEY (`sno`) REFERENCES `student`(`sno`)
    ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `score_ibfk_2` FOREIGN KEY (`cno`) REFERENCES `course`(`cno`)
    ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='成绩表';

INSERT INTO `score` VALUES
  (103,'3-245',86.0),(105,'3-245',75.0),(109,'3-245',68.0),
  (103,'3-105',92.0),(105,'3-105',88.0),(109,'3-105',76.0),
  (101,'3-105',64.0),(107,'3-105',91.0),(108,'3-105',78.0),
  (101,'6-166',85.0),(107,'6-166',79.0),(108,'6-166',81.0);

-- student table
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `sno`      INT(3) NOT NULL,
  `sname`    CHAR(8) NOT NULL,
  `ssex`     CHAR(2) NOT NULL,
  `sbirthday` DATETIME DEFAULT NULL,
  `class`    INT(5) DEFAULT NULL,
  PRIMARY KEY (`sno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生表';

INSERT INTO `student` VALUES
  (101,'李军','男','1976-02-20 00:00:00',95033),
  (103,'陆君','男','1974-06-03 00:00:00',95031),
  (105,'匡明','男','1975-10-02 00:00:00',95031),
  (107,'王丽','女','1976-01-23 00:00:00',95033),
  (108,'曾华','男','1977-09-01 00:00:00',95033),
  (109,'王芳','女','1975-02-10 00:00:00',95031);

-- teacher table
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `tno`    INT(3) NOT NULL,
  `tname`  CHAR(4) NOT NULL,
  `tsex`   CHAR(2) NOT NULL,
  `tbirthday` DATETIME DEFAULT NULL,
  `prof`   CHAR(6) DEFAULT NULL,
  `depart` VARCHAR(10) NOT NULL,
  PRIMARY KEY (`tno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='教师表';

INSERT INTO `teacher` VALUES
  (804,'李成','男','1958-12-02 00:00:00','副教授','计算机系'),
  (825,'王萍','女','1972-05-05 00:00:00','助教','计算机系'),
  (831,'刘冰','女','1977-08-14 00:00:00','助教','电子工程系'),
  (856,'张旭','男','1969-03-12 00:00:00','讲师','电子工程系');

SET FOREIGN_KEY_CHECKS = 1;

Typical interview queries and solutions

Retrieve each student's name, gender, and class SELECT sname, ssex, class FROM student; List distinct departments of teachers SELECT DISTINCT depart FROM teacher; Find all scores between 60 and 80

SELECT * FROM score WHERE degree BETWEEN 60 AND 80;

Find scores equal to 85, 86, or 88

SELECT * FROM score WHERE degree IN (85,86,88);

Students in class 95031 or female

SELECT * FROM student WHERE class = 95031 OR ssex = '女';
-- Alternative using UNION (may be more efficient when indexes differ)
SELECT * FROM student WHERE class = 95031
UNION
SELECT * FROM student WHERE ssex = '女';

All students ordered by class descending SELECT * FROM student ORDER BY class DESC; Scores ordered by course number ascending and degree descending

SELECT * FROM score ORDER BY cno ASC, degree DESC;

Count students in class 95031

SELECT COUNT(*) FROM student WHERE class = 95031;

Student number and course number of the highest score

SELECT sno, cno FROM score ORDER BY degree DESC LIMIT 1;

Average score per course

SELECT cno, AVG(degree) AS avg_score FROM score GROUP BY cno;

Average score of courses that start with '3' and have at least five students

SELECT cno, AVG(degree) AS avg_score
FROM score
GROUP BY cno
HAVING COUNT(sno) >= 5 AND cno LIKE '3%';

Student name, course number, and degree

SELECT st.sname, sc.cno, sc.degree
FROM student st
JOIN score sc ON st.sno = sc.sno;

Student name, course name, and degree

SELECT st.sname, co.cname, sc.degree
FROM student st
JOIN score sc ON st.sno = sc.sno
JOIN course co ON co.cno = sc.cno;

Average score of class 95033

SELECT AVG(sc.degree) AS avg_score
FROM score sc
JOIN student st ON sc.sno = st.sno
WHERE st.class = 95033;

Student number, course number, and grade rank (A‑E)

SELECT sc.sno, sc.cno, g.rank
FROM score sc
JOIN grade g ON sc.degree BETWEEN g.low AND g.upp;

Students in course '3-105' whose score exceeds that of student 109 in the same course

SELECT * FROM student
WHERE sno IN (
  SELECT sno FROM score
  WHERE cno = '3-105' AND degree > (
    SELECT degree FROM score WHERE sno = 109 AND cno = '3-105'
  )
);

Students taking more than one course whose score is not the overall maximum

SELECT * FROM score
WHERE sno IN (
  SELECT sno FROM score GROUP BY sno HAVING COUNT(cno) > 1
) AND degree <> (SELECT MAX(degree) FROM score);

All score records higher than student 109's score in course '3-105'

SELECT * FROM score
WHERE degree > (
  SELECT degree FROM score WHERE sno = 109 AND cno = '3-105'
);

Students born in the same year as student 101 (excluding 101)

SELECT sno, sname, sbirthday
FROM student
WHERE YEAR(sbirthday) = (
  SELECT YEAR(sbirthday) FROM student WHERE sno = 101
) AND sno <> 101;

Scores of courses taught by teacher 张旭

SELECT sc.degree
FROM score sc
WHERE sc.cno IN (
  SELECT co.cno
  FROM teacher te
  JOIN course co ON te.tno = co.tno
  WHERE te.tname = '张旭'
);

Names of teachers whose courses have more than five enrolled students

SELECT tname
FROM teacher
WHERE tno IN (
  SELECT co.tno
  FROM score sc
  JOIN course co ON sc.cno = co.cno
  GROUP BY sc.cno
  HAVING COUNT(sc.sno) > 5
);

Score records for courses taught by the Computer Science department

SELECT * FROM score
WHERE cno IN (
  SELECT co.cno
  FROM course co
  JOIN teacher te ON te.tno = co.tno
  WHERE te.depart = '计算机系'
);

Students in '3-105' whose degree exceeds the maximum degree of course '3-245', ordered descending

SELECT * FROM score
WHERE cno = '3-105' AND degree > (
  SELECT MAX(degree) FROM score WHERE cno = '3-245'
)
ORDER BY degree DESC;

All teachers and all students (name, gender, birthday)

SELECT tname AS name, tsex AS sex, tbirthday AS birthday FROM teacher
UNION ALL
SELECT sname, ssex, sbirthday FROM student;

All female teachers and female students (name, gender, birthday)

SELECT tname, tsex, tbirthday FROM teacher WHERE tsex = '女'
UNION ALL
SELECT sname, ssex, sbirthday FROM student WHERE ssex = '女';

Score records where the degree is lower than the average degree of the respective course

SELECT * FROM score
WHERE degree < ANY (
  SELECT AVG(degree) FROM score GROUP BY cno
);

Class numbers that have at least two male students

SELECT class
FROM student
WHERE ssex = '男'
GROUP BY class
HAVING COUNT(*) >= 2;

Students whose name does not start with '王'

SELECT * FROM student WHERE sname NOT LIKE '王%';

Each student's name and age (derived from birthday)

SELECT sname, YEAR(CURDATE()) - YEAR(sbirthday) AS age FROM student;

All student records ordered by class and age descending

SELECT * FROM student
ORDER BY class DESC, YEAR(CURDATE()) - YEAR(sbirthday) DESC;

Names of students who share gender and class with '李军'

SELECT sname
FROM student
WHERE ssex = (SELECT ssex FROM student WHERE sname = '李军')
  AND class = (SELECT class FROM student WHERE sname = '李军');

Scores of male students who took the course '计算机导论'

SELECT sc.sno, sc.cno, sc.degree
FROM score sc
JOIN course co ON sc.cno = co.cno
WHERE co.cname = '计算机导论'
  AND sc.sno IN (SELECT sno FROM student WHERE ssex = '男');