数据库面试题

# 创建学生表
create table Student_list(`SId` varchar(10),
                     `Sname` varchar(10),
                     `Sage` datetime,
                     `Ssex` varchar(10) CHECK ( `Ssex` IN('男','女')));
DESC Student_list;

# 插入内容
insert into student_list values('01' , '赵雷' , '1990-01-01' , '男');
insert into student_list values('02' , '钱电' , '1990-12-21' , '男');
insert into student_list values('03' , '孙风' , '1990-12-20' , '男');
insert into student_list values('04' , '李云' , '1990-12-06' , '男');
insert into student_list values('05' , '周梅' , '1991-12-01' , '女');
insert into student_list values('06' , '吴兰' , '1992-01-01' , '女');
insert into student_list values('07' , '郑竹' , '1989-01-01' , '女');
insert into student_list values('09' , '张三' , '2017-12-20' , '女');
insert into student_list values('10' , '李四' , '2017-12-25' , '女');
insert into student_list values('11' , '李四' , '2012-06-06' , '女');
insert into student_list values('12' , '赵六' , '2013-06-13' , '女');
insert into student_list values('13' , '孙七' , '2014-06-01' , '女');
insert into student_list values('14','周佳欣','2013-01-30','女');

SELECT * FROM student_list;

# 创建科目表
create table Course(CId varchar(10),
                    Cname nvarchar(10),
                    TId varchar(10));
DESC course;
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
SELECT * FROM course;

# 创建教师表
create table Teacher(TId varchar(10),
                     Tname varchar(10));
DESC Teacher;
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
SELECT * FROM Teacher;

# 创建成绩表
create table SC(SId varchar(10),
                CId varchar(10),
                score decimal(18,1));
DESC SC;
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);
SELECT * FROM sc;

1. 查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数

SELECT s.id, s.name, s1.score AS score01, s2.score AS score02
FROM student s CROSS JOIN course
JOIN sc s1 ON s.id = s1.sid AND s1.cid = '01'
JOIN sc s2 ON s.id = s2.sid AND s2.cid = '02'
WHERE s1.score > s2.score;

    2. 查询同时存在" 01 "课程和" 02 "课程的情况  

SELECT s.id, s.name
FROM student s
WHERE EXISTS (
  SELECT 1
  FROM sc s1
  WHERE s1.sid = s.id AND s1.cid = '01'
) AND EXISTS (
  SELECT 1
  FROM sc s2
  WHERE s2.sid = s.id AND s2.cid = '02'
);

   3. 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )  

SELECT s.id, s.name, s1.score AS score01, s2.score AS score02
FROM student s
LEFT JOIN sc s1 ON s.id = s1.sid AND s1.cid = '01'
LEFT JOIN sc s2 ON s.id = s2.sid AND s2.cid = '02'
WHERE s1.score IS NOT NULL;

 4. 查询不存在" 01 "课程但存在" 02 "课程的情况

SELECT s.id, s.name, s2.score AS score02
FROM student s
LEFT JOIN sc s1 ON s.id = s1.sid AND s1.cid = '01'
INNER JOIN sc s2 ON s.id = s2.sid AND s2.cid = '02'
WHERE s1.score IS NULL;

5. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩  

SELECT id, name, AVG(score) AS avg_score
FROM student CROSS JOIN SC
GROUP BY id, name
HAVING AVG(score) >= 60;

6. 查询在 SC 表存在成绩的学生信息  

SELECT s.*
FROM student s
WHERE EXISTS (
  SELECT 1
  FROM sc
  WHERE sc.sid = s.id
);

7.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )  

SELECT s.id, s.name, COUNT(sc.cid) AS 选课总数, COALESCE(SUM(sc.score), NULL) AS 所有课程的总成绩
FROM student s
LEFT JOIN sc ON s.id = sc.sid
GROUP BY s.id, s.name;

8.查有成绩的学生信息  

SELECT * FROM student WHERE id IN (SELECT sid FROM sc);

9.查询「李」姓老师的数量  

SELECT COUNT(*) FROM teacher WHERE Teacher.Tname LIKE '李%';

10.查询学过「张三」老师授课的同学的信息

SELECT student.* FROM student JOIN sc ON student.sid = sc.sid WHERE sc.tid = (SELECT tid FROM teacher WHERE name = '张三');

11.查询没有学全所有课程的同学的信息  

SELECT Student_list.* FROM student_list
LEFT JOIN score ON Student_list.SId = score.id
GROUP BY Student_list.SId, Student_list.Sname, Student_list.Sage, Student_list.Ssex
HAVING COUNT(DISTINCT score.id) < (SELECT COUNT(id) FROM course);

12.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信  

SELECT s2.*
FROM student s1
JOIN score s3 ON s1.id = s3.id
JOIN student s2 ON s3.id = s2.id
WHERE s1.id = '01';

13.查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息  

SELECT s2.*  
FROM student_list s1  
JOIN score s3 ON s1.SId = s3.id  
JOIN student s2 ON s3.id = s2.id  
WHERE s1.SId = '01' AND s2.id <> '01'  
GROUP BY s2.id, s2.name, s2.address, s2.sex  
HAVING COUNT(DISTINCT s2.id) = (SELECT COUNT(DISTINCT id) FROM score WHERE sid = '01');