SQL经典练习题+答案【写在前⾯】
最近学了 sql，然后了些 sql 查询的习题来做⼀下，感觉得到了⼀些提⾼，
所以将题⽬+答案分享⼀下，我使⽤的是 MySQL Workbench 6.3 CE，基本没有问题。        创建模式，表：
CREATE SCHEMA exercise;
USE exercise;
CREATE TABLE Student
(
stu_id    VARCHAR(3) NOT NULL,
stu_name  VARCHAR(4) NOT NULL,
stu_sex  VARCHAR(2) NOT NULL,
stu_birthday            DATETIME NOT NULL,
stu_class  VARCHAR(5)
);
CREATE TABLE Course
(
cou_id  VARCHAR(5) NOT NULL,
cou_name  VARCHAR(10) NOT NULL,
tea_id  VARCHAR(10) NOT NULL
);
CREATE TABLE Score
(
stu_id  VARCHAR(3) NOT NULL,
cou_id  VARCHAR(5) NOT NULL,
sco_degree  NUMERIC(10, 1) NOT NULL
);
CREATE TABLE Teacher
(
tea_id          VARCHAR(3) NOT NULL,
tea_name        VARCHAR(4) NOT NULL,
tea_sex        VARCHAR(2) NOT NULL,
tea_birthday    DATETIME NOT NULL,
tea_prof        VARCHAR(6),
tea_depart      VARCHAR(10) NOT NULL
)
;
INSERT INTO Student (stu_id, stu_name, stu_sex, stu_birthday, stu_class)
VALUES (108, '曾华', '男', '1977-09-01', 95033);
INSERT INTO Student (stu_id, stu_name, stu_sex, stu_birthday, stu_class)
VALUES (105, '匡明', '男', '1975-10-02', 95031);
INSERT INTO Student (stu_id, stu_name, stu_sex, stu_birthday, stu_class)
VALUES (107, '王丽', '⼥', '1976-01-23', 95033);
INSERT INTO Student (stu_id, stu_name, stu_sex, stu_birthday, stu_class)
VALUES (101, '李军', '男', '1976-02-20', 95033);
INSERT INTO Student (stu_id, stu_name, stu_sex, stu_birthday, stu_class)
VALUES (109, '王芳', '⼥', '1975-02-10', 95031);
INSERT INTO Student (stu_id, stu_name, stu_sex, stu_birthday, stu_class)
VALUES (103, '陆君', '男', '1974-06-03', 95031);
INSERT INTO Course (cou_id, cou_name, tea_id)
VALUES ('3-105', '计算机导论', 825);
INSERT INTO Course (cou_id, cou_name, tea_id)
VALUES ('3-245', '操作系统', 804);
VALUES ('3-245', '操作系统', 804);
INSERT INTO Course (cou_id, cou_name, tea_id)
VALUES ('6-166', '数据电路', 856);
INSERT INTO Course (cou_id, cou_name, tea_id)
VALUES ('9-888', '⾼等数学', 100);
INSERT INTO Score (stu_id, cou_id, sco_degree)
VALUES (103, '3-245', 86);
INSERT INTO Score (stu_id, cou_id, sco_degree)
VALUES (105, '3-245', 75);
INSERT INTO Score (stu_id, cou_id, sco_degree)
VALUES (109, '3-245', 68);
INSERT INTO Score (stu_id, cou_id, sco_degree)
VALUES (103, '3-105', 92);
mysql面试题目及答案INSERT INTO Score (stu_id, cou_id, sco_degree)
VALUES (105, '3-105', 88);
INSERT INTO Score (stu_id, cou_id, sco_degree)
VALUES (109, '3-105', 76);
INSERT INTO Score (stu_id, cou_id, sco_degree)
VALUES (101, '3-105', 64);
INSERT INTO Score (stu_id, cou_id, sco_degree)
VALUES (107, '3-105', 91);
游戏power是什么意思INSERT INTO Score (stu_id, cou_id, sco_degree)
VALUES (108, '3-105', 78);
INSERT INTO Score (stu_id, cou_id, sco_degree)
VALUES (101, '6-166', 85);
INSERT INTO Score (stu_id, cou_id, sco_degree)
VALUES (107, '6-166', 79);
INSERT INTO Score (stu_id, cou_id, sco_degree)
VALUES (108, '6-166', 81);
INSERT INTO Teacher (tea_id, tea_name, tea_sex, tea_birthday, tea_prof, tea_depart) VALUES (804, '李诚', '男', '1958-12-02', '副教授', '计算机系');
INSERT INTO Teacher (tea_id, tea_name, tea_sex, tea_birthday, tea_prof, tea_depart) VALUES (856, '张旭', '男', '1969-03-12', '讲师', '电⼦⼯程系');
INSERT INTO Teacher (tea_id, tea_name, tea_sex, tea_birthday, tea_prof, tea_depart) VALUES (825, '王萍', '⼥', '1972-05-05', '助教', '计算机系');
INSERT INTO Teacher (tea_id, tea_name, tea_sex, tea_birthday, tea_prof, tea_depart) VALUES (831, '刘冰', '⼥', '1977-08-14', '助教', '电⼦⼯程系');
Student表如下：
Course表：
Score表：
Teacher表：
题⽬由易到难，题⽬及答案如下：
USE exercise;
-- 1、查询student表中的所有记录的stu_name、stu_sex和stu_class列。SELECT student.stu_name, student.stu_sex, student.stu_class FROM student;
-- 2、查询教师所有的单位即不重复的tea_depart列。
SELECT a_depart
FROM teacher;
-- 3、查询student表的所有记录。
SELECT *
FROM student;
-- 4、查询score表中成绩在60到80之间的所有记录。
SELECT *
FROM score
WHERE sco_degree BETWEEN 60 AND 80;
-- 5、查询score表中成绩为85，86或88的记录。
-- 解法1
SELECT *
FROM score
WHERE sco_degree IN (85, 86, 88);
-- 解法2
SELECT *
FROM score
WHERE sco_degree = 85 OR sco_degree = 86 OR sco_degree = 88;
-- 6、查询student表中“95031”班或性别为“⼥”的同学记录。
SELECT *
FROM student
WHERE stu_class = '95031'
OR stu_sex = '⼥';
-- 7、以stu_class降序查询student表的所有记录。
SELECT * FROM student
ORDER BY student.stu_class DESC;
-- 8、以stu_id升序、sco_degree降序查询score表的所有记录。SELECT *
FROM score
使用nginx还需要tomcat吗ORDER BY stu_id ASC, sco_degree DESC;
-- 9、查询“95031”班的学⽣⼈数。
SELECT COUNT(*) AS class_95031_num
FROM student
WHERE stu_class = '95031';
-- 10、查询score表中的最⾼分的学⽣学号和课程号。
-- 解法1
SELECT stu_id, cou_id
FROM score
WHERE sco_degree = (SELECT MAX(sco_degree)
FROM score);
-- 解法2
SELECT stu_id, cou_id
FROM score
ORDER BY sco_degree DESC LIMIT 1;
-- 11、查询‘3-105’号课程的平均分。
SELECT AVG(sco_degree) AS avg_degree
FROM score
WHERE cou_id = '3-105';
-- 12、查询score表中⾄少有5名学⽣选修的并以3开头的课程的平均分数。
SELECT AVG(sco_degree) AS degree_avg, cou_id
FROM score
WHERE cou_id LIKE '3%'
GROUP BY cou_id
HAVING COUNT(*) >= 5;
-- 13、查询最低分⼤于70，最⾼分⼩于90的stu_id列。
SELECT stu_id -- 或者 SELECT DISTINCT stu_id
FROM score
GROUP BY stu_id
HAVING MIN(sco_degree) > 70
AND MAX(sco_degree) < 90;
-- 14、查询所有学⽣的stu_name、cou_id和sco_egree列。
免费素材视频无水印
SELECT student.stu_name, u_id, score.sco_degree
FROM student INNER JOIN
score ON student.stu_id = score.stu_id;
-
- 15、查询所有学⽣的stu_id、cou_name和sco_degree列。
SELECT score.stu_id, u_name, score.sco_degree
FROM score INNER JOIN
course u_id = u_id;
-- 16、查询所有学⽣的stu_name、cou_name和sco_degree列。
SELECT student.stu_name, u_name, score.sco_degree
FROM student, course, score
WHERE student.stu_id = score.stu_id
u_id = u_id;
-- 17、查询“95033”班所选课程的平均分。
SELECT student.stu_class, AVG(score.sco_degree) AS degree_avg
FROM student INNER JOIN
score ON student.stu_id = score.stu_id
WHERE student.stu_class = '95031';
-- 18、假设使⽤如下命令建⽴了⼀个grade表, 现查询所有同学的stu_name、cou_name和gra_rank列。/*-- 去除注释进⾏创建
CREATE TABLE grade
(
gra_low  NUMERIC(3, 0),
gra_upp  NUMERIC(3),
gra_rank  CHAR(1)sql update从另一个表查出来
);
INSERT INTO grade VALUES(90, 100, 'A');
INSERT INTO grade VALUES(80, 89, 'B');
INSERT INTO grade VALUES(70, 79, 'C');
INSERT INTO grade VALUES(60, 69, 'D');
INSERT INTO grade VALUES(0, 59, 'E');
COMMIT;
位置英文*/
SELECT student.stu_name, u_name, a_rank
FROM score, student, course, grade
WHERE student.stu_id = score.stu_id
u_id = u_id
AND (score.sco_degree a_low a_upp);
-
- 19、查询选修“3-105”课程的成绩⾼于“109”号同学成绩的所有同学的记录
-- 解法1
SELECT *
FROM score
u_id = '3-105'
AND score.sco_degree > (SELECT sco_degree
FROM score
u_id = '3-105' AND score.stu_id = '109');
-- 解法2
SELECT s1.*
FROM score AS s1 INNER JOIN
score AS s2 u_id = s2.cou_id
u_id = '3-105'
AND s1.sco_degree > s2.sco_degree
u_id = '3-105' AND s2.stu_id = '109';
-- 20、查询score中选学⼀门以上课程的同学中分数为⾮最⾼分成绩的记录。
SELECT score.*
FROM score
WHERE score.sco_degree < (SELECT MAX(score.sco_degree) FROM score)
GROUP BY score.stu_id
HAVING COUNT(*) > 1;
-- 21、查询成绩⾼于学号为“109”、课程号为“3-105”的成绩的所有记录。
-
- 解法1
SELECT s1.*
FROM score AS s1 INNER JOIN
score AS s2 u_id = s2.cou_id
u_id = '3-105'
AND s1.sco_degree > s2.sco_degree
u_id = '3-105' AND s2.stu_id = '109';
-- 解法2
SELECT *
FROM score
WHERE score.sco_degree > (SELECT score.sco_degree
FROM score
WHERE score.stu_id = '109' u_id = '3-105');
-- 22、查询和学号为‘108’的同学同年出⽣的所有学⽣的stu_id、stu_name和stu_birthday列。-- 解法1
SELECT s1.stu_id, s1.stu_name, s1.stu_birthday
FROM student AS s1 INNER JOIN
student AS s2 ON s1.stu_id = s2.stu_id
WHERE YEAR(s1.stu_birthday) = YEAR(s2.stu_birthday) AND s2.stu_id = '108';
-- 解法2
SELECT student.stu_id, student.stu_name, student.stu_birthday
FROM student
WHERE YEAR(student.stu_birthday) = (SELECT YEAR(student.stu_birthday)

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。