首页 > 数据库 >面试之数据库SQL编写实战案例

面试之数据库SQL编写实战案例

时间：2023-06-08 18:34:55浏览次数：63  
标签：INSERT 数据库 id 面试 score VALUES student SQL INTO


好多同学在面试的过程中会碰到关于SQL查询的相关案例，让手写SQL语句，直接蒙蔽的不少哦，下面以几个面试题为例，谈谈SQL的编写，有题有答案，大家可以看看参考一下：

数据库的基本准备：

/*
Navicat MySQL Data Transfer
Source Server         : localhost
Source Server Version : 50168
Source Host           : localhost:3306
Source Database       : test_exam
Target Server Type    : MYSQL
Target Server Version : 50168
File Encoding         : 65001
Date: 2020-08-21 16:26:33
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `class`
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `caption` varchar(32) NOT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of class
-- ----------------------------
INSERT INTO `class` VALUES ('1', '三年二班');
INSERT INTO `class` VALUES ('2', '三年三班');
INSERT INTO `class` VALUES ('3', '一年二班');
INSERT INTO `class` VALUES ('4', '二年九班');
-- ----------------------------
-- Table structure for `course`
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `cname` varchar(32) NOT NULL,
  `teacher_id` int(11) NOT NULL,
  PRIMARY KEY (`cid`),
  KEY `fk_course_teacher` (`teacher_id`),
  CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('1', '生物', '1');
INSERT INTO `course` VALUES ('2', '物理', '2');
INSERT INTO `course` VALUES ('3', '体育', '3');
INSERT INTO `course` VALUES ('4', '美术', '2');
-- ----------------------------
-- Table structure for `score`
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) NOT NULL,
  `course_id` int(11) NOT NULL,
  `num` int(11) NOT NULL,
  PRIMARY KEY (`sid`),
  KEY `fk_score_student` (`student_id`),
  KEY `fk_score_course` (`course_id`),
  CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`),
  CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES ('1', '1', '1', '10');
INSERT INTO `score` VALUES ('2', '1', '2', '9');
INSERT INTO `score` VALUES ('5', '1', '4', '66');
INSERT INTO `score` VALUES ('6', '2', '1', '8');
INSERT INTO `score` VALUES ('8', '2', '3', '68');
INSERT INTO `score` VALUES ('9', '2', '4', '99');
INSERT INTO `score` VALUES ('10', '3', '1', '77');
INSERT INTO `score` VALUES ('11', '3', '2', '66');
INSERT INTO `score` VALUES ('12', '3', '3', '87');
INSERT INTO `score` VALUES ('13', '3', '4', '99');
INSERT INTO `score` VALUES ('14', '4', '1', '79');
INSERT INTO `score` VALUES ('15', '4', '2', '11');
INSERT INTO `score` VALUES ('16', '4', '3', '67');
INSERT INTO `score` VALUES ('17', '4', '4', '100');
INSERT INTO `score` VALUES ('18', '5', '1', '79');
INSERT INTO `score` VALUES ('19', '5', '2', '11');
INSERT INTO `score` VALUES ('20', '5', '3', '67');
INSERT INTO `score` VALUES ('21', '5', '4', '100');
INSERT INTO `score` VALUES ('22', '6', '1', '9');
INSERT INTO `score` VALUES ('23', '6', '2', '100');
INSERT INTO `score` VALUES ('24', '6', '3', '67');
INSERT INTO `score` VALUES ('25', '6', '4', '100');
INSERT INTO `score` VALUES ('26', '7', '1', '9');
INSERT INTO `score` VALUES ('27', '7', '2', '100');
INSERT INTO `score` VALUES ('28', '7', '3', '67');
INSERT INTO `score` VALUES ('29', '7', '4', '88');
INSERT INTO `score` VALUES ('30', '8', '1', '9');
INSERT INTO `score` VALUES ('31', '8', '2', '100');
INSERT INTO `score` VALUES ('32', '8', '3', '67');
INSERT INTO `score` VALUES ('33', '8', '4', '88');
INSERT INTO `score` VALUES ('34', '9', '1', '91');
INSERT INTO `score` VALUES ('35', '9', '2', '88');
INSERT INTO `score` VALUES ('36', '9', '3', '67');
INSERT INTO `score` VALUES ('37', '9', '4', '22');
INSERT INTO `score` VALUES ('38', '10', '1', '90');
INSERT INTO `score` VALUES ('39', '10', '2', '77');
INSERT INTO `score` VALUES ('40', '10', '3', '43');
INSERT INTO `score` VALUES ('41', '10', '4', '87');
INSERT INTO `score` VALUES ('42', '11', '1', '90');
INSERT INTO `score` VALUES ('43', '11', '2', '77');
INSERT INTO `score` VALUES ('44', '11', '3', '43');
INSERT INTO `score` VALUES ('45', '11', '4', '87');
INSERT INTO `score` VALUES ('46', '12', '1', '90');
INSERT INTO `score` VALUES ('47', '12', '2', '77');
INSERT INTO `score` VALUES ('48', '12', '3', '43');
INSERT INTO `score` VALUES ('49', '12', '4', '87');
INSERT INTO `score` VALUES ('52', '13', '3', '87');
-- ----------------------------
-- Table structure for `student`
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `gender` char(1) NOT NULL,
  `class_id` int(11) NOT NULL,
  `sname` varchar(32) NOT NULL,
  PRIMARY KEY (`sid`),
  KEY `fk_class` (`class_id`),
  CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '男', '1', '理解');
INSERT INTO `student` VALUES ('2', '女', '1', '钢蛋');
INSERT INTO `student` VALUES ('3', '男', '1', '张三');
INSERT INTO `student` VALUES ('4', '男', '1', '张一');
INSERT INTO `student` VALUES ('5', '女', '1', '张二');
INSERT INTO `student` VALUES ('6', '男', '1', '张四');
INSERT INTO `student` VALUES ('7', '女', '2', '铁锤');
INSERT INTO `student` VALUES ('8', '男', '2', '李三');
INSERT INTO `student` VALUES ('9', '男', '2', '李一');
INSERT INTO `student` VALUES ('10', '女', '2', '李二');
INSERT INTO `student` VALUES ('11', '男', '2', '李四');
INSERT INTO `student` VALUES ('12', '女', '3', '如花');
INSERT INTO `student` VALUES ('13', '男', '3', '刘三');
INSERT INTO `student` VALUES ('14', '男', '3', '刘一');
INSERT INTO `student` VALUES ('15', '女', '3', '刘二');
INSERT INTO `student` VALUES ('16', '男', '3', '刘四');
-- ----------------------------
-- Table structure for `teacher`
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `tid` int(11) NOT NULL AUTO_INCREMENT,
  `tname` varchar(32) NOT NULL,
  PRIMARY KEY (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('1', '张磊老师');
INSERT INTO `teacher` VALUES ('2', '李平老师');
INSERT INTO `teacher` VALUES ('3', '刘海燕老师');
INSERT INTO `teacher` VALUES ('4', '朱云海老师');
INSERT INTO `teacher` VALUES ('5', '李杰老师');
#1、查询所有的课程的名称以及对应的任课老师姓名
SELECT
    course.cname,
    teacher.tname
FROM
    course
INNER JOIN teacher ON course.teacher_id = teacher.tid;
#2、查询学生表中男女生各有多少人
SELECT
    gender 性别,
    count(1) 人数
FROM
    student
GROUP BY
    gender;
#3、查询物理成绩等于100的学生的姓名
SELECT
    student.sname
FROM
    student
WHERE
    sid IN (
        SELECT
            student_id
        FROM
            score
        INNER JOIN course ON score.course_id = course.cid
        WHERE
            course.cname = '物理'
        AND score.num = 100
    );
#4、查询平均成绩大于八十分的同学的姓名和平均成绩
SELECT
    student.sname,
    t1.avg_num
FROM
    student
INNER JOIN (
    SELECT
        student_id,
        avg(num) AS avg_num
    FROM
        score
    GROUP BY
        student_id
    HAVING
        avg(num) > 80
) AS t1 ON student.sid = t1.student_id;
#5、查询所有学生的学号，姓名，选课数，总成绩(注意：对于那些没有选修任何课程的学生也算在内)
SELECT
    student.sid,
    student.sname,
    t1.course_num,
    t1.total_num
FROM
    student
LEFT JOIN (
    SELECT
        student_id,
        COUNT(course_id) course_num,
        sum(num) total_num
    FROM
        score
    GROUP BY
        student_id
) AS t1 ON student.sid = t1.student_id;
#6、 查询姓李老师的个数
SELECT
    count(tid)
FROM
    teacher
WHERE
    tname LIKE '李%';
#7、 查询没有报李平老师课的学生姓名(找出报名李平老师课程的学生，然后取反就可以)
SELECT
    student.sname
FROM
    student
WHERE
    sid NOT IN (
        SELECT DISTINCT
            student_id
        FROM
            score
        WHERE
            course_id IN (
                SELECT
                    course.cid
                FROM
                    course
                INNER JOIN teacher ON course.teacher_id = teacher.tid
                WHERE
                    teacher.tname = '李平老师'
            )
    );
#8、 查询物理课程比生物课程高的学生的学号(分别得到物理成绩表与生物成绩表，然后连表即可)
SELECT
    t1.student_id
FROM
    (
        SELECT
            student_id,
            num
        FROM
            score
        WHERE
            course_id = (
                SELECT
                    cid
                FROM
                    course
                WHERE
                    cname = '物理'
            )
    ) AS t1
INNER JOIN (
    SELECT
        student_id,
        num
    FROM
        score
    WHERE
        course_id = (
            SELECT
                cid
            FROM
                course
            WHERE
                cname = '生物'
        )
) AS t2 ON t1.student_id = t2.student_id
WHERE
    t1.num > t2.num;
#9、 查询没有同时选修物理课程和体育课程的学生姓名(没有同时选修指的是选修了一门的，思路是得到物理+体育课程的学生信息表，然后基于学生分组，统计count(课程)=1)
SELECT
    student.sname
FROM
    student
WHERE
    sid IN (
        SELECT
            student_id
        FROM
            score
        WHERE
            course_id IN (
                SELECT
                    cid
                FROM
                    course
                WHERE
                    cname = '物理'
                OR cname = '体育'
            )
        GROUP BY
            student_id
        HAVING
            COUNT(course_id) = 1
    );
#10、查询挂科超过两门(包括两门)的学生姓名和班级(求出<60的表，然后对学生进行分组，统计课程数目>=2)
SELECT
    student.sname,
    class.caption
FROM
    student
INNER JOIN (
    SELECT
        student_id
    FROM
        score
    WHERE
        num < 60
    GROUP BY
        student_id
    HAVING
        count(course_id) >= 2
) AS t1
INNER JOIN class ON student.sid = t1.student_id
AND student.class_id = class.cid;
#11、查询选修了所有课程的学生姓名(先从course表统计课程的总数，然后基于score表按照student_id分组，统计课程数据等于课程总数即可)
SELECT
    student.sname
FROM
    student
WHERE
    sid IN (
        SELECT
            student_id
        FROM
            score
        GROUP BY
            student_id
        HAVING
            COUNT(course_id) = (SELECT count(cid) FROM course)
    );
#12、查询李平老师教的课程的所有成绩记录
SELECT
    *
FROM
    score
WHERE
    course_id IN (
        SELECT
            cid
        FROM
            course
        INNER JOIN teacher ON course.teacher_id = teacher.tid
        WHERE
            teacher.tname = '李平老师'
    );
#13、查询全部学生都选修了的课程号和课程名(取所有学生数，然后基于score表的课程分组，找出count(student_id)等于学生数即可)
SELECT
    cid,
    cname
FROM
    course
WHERE
    cid IN (
        SELECT
            course_id
        FROM
            score
        GROUP BY
            course_id
        HAVING
            COUNT(student_id) = (
                SELECT
                    COUNT(sid)
                FROM
                    student
            )
    );
#14、查询每门课程被选修的次数
SELECT
    course_id,
    COUNT(student_id)
FROM
    score
GROUP BY
    course_id;
#15、查询之选修了一门课程的学生姓名和学号
SELECT
    sid,
    sname
FROM
    student
WHERE
    sid IN (
        SELECT
            student_id
        FROM
            score
        GROUP BY
            student_id
        HAVING
            COUNT(course_id) = 1
    );
#16、查询所有学生考出的成绩并按从高到低排序（成绩去重）
SELECT DISTINCT
    num
FROM
    score
ORDER BY
    num DESC;
#17、查询平均成绩大于85的学生姓名和平均成绩
SELECT
    sname,
    t1.avg_num
FROM
    student
INNER JOIN (
    SELECT
        student_id,
        avg(num) avg_num
    FROM
        score
    GROUP BY
        student_id
    HAVING
        AVG(num) > 85
) t1 ON student.sid = t1.student_id;
#18、查询生物成绩不及格的学生姓名和对应生物分数
SELECT
    sname 姓名,
    num 生物成绩
FROM
    score
LEFT JOIN course ON score.course_id = course.cid
LEFT JOIN student ON score.student_id = student.sid
WHERE
    course.cname = '生物'
AND score.num < 60;

#19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名

SELECT
    sname
FROM
    student
WHERE
    sid = (
        SELECT
            student_id
        FROM
            score
        WHERE
            course_id IN (
                SELECT
                    course.cid
                FROM
                    course
                INNER JOIN teacher ON course.teacher_id = teacher.tid
                WHERE
                    teacher.tname = '李平老师'
            )
        GROUP BY
            student_id
        ORDER BY
            AVG(num) DESC
        LIMIT 1
    );

以上是整理的面试题中关于SQL编写的相关案例，希望大家能认真分析，练习一下，希望对大家面试有所帮助！


标签：INSERT,数据库,id,面试,score,VALUES,student,SQL,INTO
From： https://blog.51cto.com/u_16147814/6442183

相关文章

  • SQL 编程思想：一切皆关系
    在计算机领域有许多伟大的设计理念和思想，例如：在Unix中，一切皆文件。在面向对象的编程语言中，一切皆对象。关系数据库同样也有自己的设计思想：在SQL中，一切皆关系。关系模型关系模型（Relationalmodel）由E.F.Codd博士于1970年提出，以集合论中的关系概念为基础；无论是现实世界中的实......
  • 2023春招：Javaweb面试锦囊
    cookie和session的区别？（必会）存储位置不同cookie存放在客户端电脑，是一个磁盘文件。Ie浏览器是可以从文件夹中找到。session是存放在服务器内存中的一个对象。chrome浏览器进行安全处理，只能通过浏览器找到。Session是服务器端会话管理技术，并且session就是cookie实现的。......
  • 2023春季招聘面试集锦：MYSQL数据库高频面试题
    mysql索引的数据结构，各自优劣索引的数据结构和具体存储引擎的实现有关，在MySQL中使用较多的索引有Hash索引，B+树索引等，InnoDB存储引擎的默认索引实现为：B+树索引。对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大......
  • SQLServer查看端口号和修改端口号
    sqlserver默认的端口号是14331、查看端口号程序中找到SQLServerManagementStudio，连接sqlserver数据库，新建查询，运行execsys.sp_readerrorlog0,1,'listening'; 2、修改端口号所有程序中找到SQLServer配置管理器SQLServer网络配置--MSSQLSERVER的协议，TCP/IP，双击找到T......
  • web基础漏洞-sql注入
    1、介绍sql注入，是用户提交的数据，服务端接收后未经过严格检查，将其直接拼接到sql语句中，交给sql应用执行，超出了开发者预期的sql语句结构，从而造成危害。2、详解(1)从数据来说，任何前端提交的数据，都有可能成为sql注入的参数。包括get、post、及其它请求方法，包括url、请求头部和请求体......
  • 记录--7 个沙雕又带有陷阱的 JS 面试题
    这里给大家分享我在网上总结出来的一些知识，希望对大家有所帮助为了保证的可读性，本文采用意译而非直译。在JS面试中，经常会看到一些简单而又沙雕的题目，这些题目包含一些陷阱，但这些在我们规范的编码下或者业务中基本不会出现。有些面试官就是这样，不专注于制定代码的标准和规范......
  • 一文读懂大厂面试的JAVA基础（集合，面向对象特性，反射，IO,容器）
    整理了操作系统，计算机网络，以及JVM的高频面试题目，对于面试大厂的Android以及后端开发岗位，可以说的是十分必要的部分就是JAVA语言的基础，在整体的内容上我认为有以下的几个部分，我发现任何的学习都是先建立框架体系，再逐个击破，针对Java的基础中包括：（1）Java语言的面向对象的特性（2）Java语言......
  • Java面试题查缺补漏习题，锁的升级，动态代理
    之前我们总结了Java面试题目中的关于计算机网络，操作系统，以及JVM虚拟机，以及Java的相关特性。今天又看了很多面试的视频，对面试的题目进行一下仔细的补充。1.对称加密与非对称加密的区别：非对称加密和对称加密在加密和解密过程、加密解密速度、传输的安全性上都有所不同，具体介绍如下：......
  • 一文读懂大厂面试的计算机网络面试题目（超详细整理）（TCP/IP,OSI，HTTP协议）
    对于大厂的面试来说，掌握基本的计算机网络知识十分必要，但是说实话就单单是博主觉得，看书去复习，是最好的“安眠药”，哈哈哈，所以具有针对性的去学习更加的有效果，所以直接看大厂的高频面试题，快速建立知识结构体系。以下的一些是博主通过博览众多平台的博客推文进行的汇总：1.计算机网络OS......
  • Gauss （openGauss轻量版）数据库安装配置
    1、修改操作系统配置为了在防火墙开启的状态下，确保openGauss轻量版的正常使用。用户需要将轻量版相关的服务、协议、IP以及端口添加到主机的防火墙白名单中。目前仅支持在防火墙关闭的状态下进行安装。修改/etc/selinux/config文件中的“SELINUX“值为“disabled“。a.使用......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99