网上题目整理

最新推荐文章于 2021-02-02 13:47:57 发布

孤星明月

最新推荐文章于 2021-02-02 13:47:57 发布

阅读量860

点赞数

分类专栏：编程资料

本文链接：https://blog.csdn.net/zctyhj/article/details/49336643

版权

编程资料专栏收录该内容

5 篇文章 0 订阅

订阅专栏

-- ----------------------------------表结构--------------------------------------
-- 学生表tblStudent（编号StuId、姓名StuName、年龄StuAge、性别StuSex）
-- 课程表tblCourse（课程编号CourseId、课程名称CourseName、教师编号TeaId）
-- 成绩表tblScore（学生编号StuId、课程编号CourseId、成绩Score）
-- 教师表tblTeacher（教师编号TeaId、姓名TeaName）
-- -------------------------------------------------------------------------------

1、查询“001”课程比“002”课程成绩高的所有学生的学号；

select a.StuId
from tblScore a ,tblScore b
where a.StuId = b.StuId
and a.CourseId='001'
and b.CourseId='002'
and a.Score>b.Score;

2、查询平均成绩大于60分的同学的学号和平均成绩；

Select StuId,Avg(Score) as AvgScore From tblScore
Group By StuId
Having Avg(Score)>60;

3、查询所有同学的学号、姓名、选课数、总成绩；

select tblstudent.StuId,tblstudent.StuName,COUNT(tblscore.CourseId),sum(tblscore.Score)
FROM tblstudent,tblscore
WHERE tblstudent.StuId = tblscore.StuId
GROUP BY tblstudent.StuId;

4、查询姓“李”的老师的个数；

SELECT COUNT(1),tblteacher.TeaName
FROM tblteacher
WHERE tblteacher.TeaName LIKE '李%';

5、查询没学过“叶平”老师课的同学的学号、姓名；

SELECT * FROM tblstudent WHERE StuId not in(
SELECT DISTINCT StuId FROM tblscore WHERE CourseId IN(
SELECT tblcourse.CourseId FROM tblcourse WHERE TeaId IN
(SELECT tblteacher.TeaId from tblteacher WHERE tblteacher.TeaName = '叶平')
)
);

6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；

SELECT DISTINCT s.StuId,s.StuName
FROM tblstudent s
WHERE s.StuId IN
(SELECT DISTINCT sc.StuId
FROM tblscore sc
WHERE sc.CourseId='001' AND sc.StuId IN
(SELECT DISTINCT sc.StuId
FROM tblscore sc
WHERE sc.CourseId='002')
);

7、查询学过“叶平”老师所教的所有课的同学的学号、姓名；

SELECT * FROM tblstudent WHERE StuId in(
SELECT DISTINCT StuId FROM tblscore WHERE CourseId IN(
SELECT tblcourse.CourseId FROM tblcourse WHERE TeaId IN
(SELECT tblteacher.TeaId from tblteacher WHERE tblteacher.TeaName = '叶平')
)
);

8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；

Select StuId,StuName From tblStudent s1
Where (Select Score From tblScore t1 Where t1.StuId=s1.stuId And t1.CourseId='001')>
(Select Score From tblScore t2 Where t2.StuId=s1.stuId And t2.CourseId='002')

9、查询所有课程成绩小于60分的同学的学号、姓名；

SELECT s.StuId,s.StuName
FROM tblstudent s
WHERE s.StuId IN
(SELECT DISTINCT sc.StuId
FROM tblscore sc
WHERE sc.Score<60);

10、查询没有学全所有课的同学的学号、姓名；

Select StuId,StuName From tblStudent st
Where (Select Count(1) From tblScore sc Where st.StuId=sc.StuId)<
(Select Count(1) From tblCourse);

11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名；

SELECT DISTINCT c.StuId,s.StuName
FROM tblscore c,tblstudent s
WHERE c.CourseId IN(SELECT sc.CourseId
FROM tblscore sc
WHERE sc.StuId = '1001')
AND c.StuId = s.StuId;

12、查询至少学过学号为“1001”同学所有课程的其他同学学号和姓名； --------(这道题目我做不出来，我的理解是选取学生表的学生姓名和学号，学生表的哪些？那些学过某一个同学学过的课程的那一些)
-----------------------------------------------------------------------答案我不能理解这个似乎有歧义！！！

Select StuId,StuName From tblStudent
Where StuId In
(
Select Distinct StuId From tblScore Where CourseId Not In
(Select CourseId From tblScore Where StuId='1001'));

13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩；--------(直说我思考过没有做出来)

14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名；

15、删除学习“叶平”老师课的SC表记录；

16、向SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号“003”课程的同学学号、'002'号课的平均成绩；

17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，按如下形式显示：学生ID,,数据库,企业管理,英语,有效课程数,有效平均分

18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分

SELECT c.CourseName AS 课程ID,MAX(sc.Score) 最高分,MIN(sc.Score) AS 最低分
FROM tblscore sc,tblcourse c
WHERE c.CourseId = sc.CourseId
GROUP BY sc.CourseId;

19、按各科平均成绩从低到高和及格率的百分数从高到低顺序 (百分数后如何格式化为两位小数??)

20、查询如下课程平均成绩和及格率的百分数(用"1行"显示): 企业管理（001），马克思（002），OO&UML （003），数据库（004）

21、查询不同老师所教不同课程平均分从高到低显示

22、查询如下课程成绩第 3 名到第 6 名的学生成绩单：企业管理（001），马克思（002），UML （003），数据库（004）格式：[学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩

23、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]

24、查询学生平均成绩及其名次

SELECT sc.StuId,s.StuName,avg(sc.Score) AS 平均成绩
FROM tblstudent s,tblscore sc
WHERE s.StuId = sc.StuId
GROUP BY sc.StuId
ORDER BY 平均成绩 DESC;

25、查询各科成绩前三名的记录:(不考虑成绩并列情况)

26、查询每门课程被选修的学生数

27、查询出只选修了一门课程的全部学生的学号和姓名

28、查询男生、女生人数

SELECT s.StuSex,COUNT(1) as 人数
FROM tblstudent s
GROUP BY s.StuSex;

29、查询姓“张”的学生名单

SELECT s.StuId,s.StuName
FROM tblstudent s
WHERE s.StuName LIKE '张%';

30、查询同名同性学生名单，并统计同名人数

SELECT s.StuName,s.StuSex,COUNT(1) AS 人数
FROM tblstudent s
GROUP BY s.StuName,s.StuSex;

31、1981年出生的学生名单(注：Student表中Sage列的类型是datetime)

32、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列

33、查询平均成绩大于85的所有学生的学号、姓名和平均成绩

34、查询课程名称为“数据库”，且分数低于60的学生姓名和分数

35、查询所有学生的选课情况；

36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；

37、查询不及格的课程，并按课程号从大到小排列

38、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名；

39、求选了课程的学生人数

40、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩

41、查询各个课程及相应的选修人数

42、查询不同课程成绩相同的学生的学号、课程号、学生成绩

43、查询每门功成绩最好的前两名

44、统计每门课程的学生选修人数（超过10人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

45、检索至少选修两门课程的学生学号

46、查询全部学生都选修的课程的课程号和课程名

47、查询没学过“叶平”老师讲授的任一门课程的学生姓名

48、查询两门以上不及格课程的同学的学号及其平均成绩

49、检索“004”课程分数小于60，按分数降序排列的同学学号 (ok)

50、删除“002”同学的“001”课程的成绩

/*
Navicat MySQL Data Transfer

Source Server         : huangjin
Source Server Version : 50168
Source Host           : localhost:3306
Source Database       : myschool

Target Server Type    : MYSQL
Target Server Version : 50168
File Encoding         : 65001

Date: 2015-10-21 17:03:08
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `tblcourse`
-- ----------------------------
DROP TABLE IF EXISTS `tblcourse`;
CREATE TABLE `tblcourse` (
  `CourseId` varchar(3) NOT NULL,
  `CourseName` varchar(20) NOT NULL,
  `TeaId` varchar(3) NOT NULL,
  PRIMARY KEY (`CourseId`),
  CONSTRAINT `tblcourse_ibfk_1` FOREIGN KEY (`CourseId`) REFERENCES `tblteacher` (`TeaId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of tblcourse
-- ----------------------------
INSERT INTO `tblcourse` VALUES ('001', '企业管理', '002');
INSERT INTO `tblcourse` VALUES ('002', '马克思', '008');
INSERT INTO `tblcourse` VALUES ('003', 'UML', '006');
INSERT INTO `tblcourse` VALUES ('004', '数据库', '007');
INSERT INTO `tblcourse` VALUES ('005', '逻辑电路', '006');
INSERT INTO `tblcourse` VALUES ('006', '英语', '003');
INSERT INTO `tblcourse` VALUES ('007', '电子电路', '005');
INSERT INTO `tblcourse` VALUES ('008', '毛泽东思想概论', '004');
INSERT INTO `tblcourse` VALUES ('009', '西方哲学史', '012');
INSERT INTO `tblcourse` VALUES ('010', '线性代数', '017');
INSERT INTO `tblcourse` VALUES ('011', '计算机基础', '013');
INSERT INTO `tblcourse` VALUES ('012', 'AUTO CAD制图', '015');
INSERT INTO `tblcourse` VALUES ('013', '平面设计', '011');
INSERT INTO `tblcourse` VALUES ('014', 'Flash动漫', '001');
INSERT INTO `tblcourse` VALUES ('015', 'Java开发', '009');
INSERT INTO `tblcourse` VALUES ('016', 'C#基础', '002');
INSERT INTO `tblcourse` VALUES ('017', 'Oracl数据库原理', '010');

-- ----------------------------
-- Table structure for `tblscore`
-- ----------------------------
DROP TABLE IF EXISTS `tblscore`;
CREATE TABLE `tblscore` (
  `StuId` varchar(10) NOT NULL,
  `CourseId` varchar(3) NOT NULL,
  `Score` float DEFAULT NULL,
  PRIMARY KEY (`StuId`,`CourseId`),
  KEY `CourseId` (`CourseId`),
  CONSTRAINT `tblscore_ibfk_1` FOREIGN KEY (`StuId`) REFERENCES `tblstudent` (`StuId`),
  CONSTRAINT `tblscore_ibfk_2` FOREIGN KEY (`CourseId`) REFERENCES `tblcourse` (`CourseId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of tblscore
-- ----------------------------
INSERT INTO `tblscore` VALUES ('1000', '004', '16');
INSERT INTO `tblscore` VALUES ('1000', '014', '75');
INSERT INTO `tblscore` VALUES ('1001', '001', '96');
INSERT INTO `tblscore` VALUES ('1001', '002', '87');
INSERT INTO `tblscore` VALUES ('1001', '003', '90');
INSERT INTO `tblscore` VALUES ('1001', '010', '85');
INSERT INTO `tblscore` VALUES ('1002', '001', '42');
INSERT INTO `tblscore` VALUES ('1002', '002', '87');
INSERT INTO `tblscore` VALUES ('1002', '003', '70');
INSERT INTO `tblscore` VALUES ('1002', '004', '55');
INSERT INTO `tblscore` VALUES ('1002', '010', '65');
INSERT INTO `tblscore` VALUES ('1002', '016', '100');
INSERT INTO `tblscore` VALUES ('1003', '001', '32');
INSERT INTO `tblscore` VALUES ('1003', '003', '70');
INSERT INTO `tblscore` VALUES ('1003', '005', '70');
INSERT INTO `tblscore` VALUES ('1003', '006', '78');
INSERT INTO `tblscore` VALUES ('1003', '010', '85');
INSERT INTO `tblscore` VALUES ('1003', '011', '21');
INSERT INTO `tblscore` VALUES ('1004', '001', '83');
INSERT INTO `tblscore` VALUES ('1004', '002', '87');
INSERT INTO `tblscore` VALUES ('1004', '004', '42');
INSERT INTO `tblscore` VALUES ('1004', '007', '90');
INSERT INTO `tblscore` VALUES ('1005', '001', '23');
INSERT INTO `tblscore` VALUES ('1006', '001', '99');
INSERT INTO `tblscore` VALUES ('1006', '003', '59');
INSERT INTO `tblscore` VALUES ('1006', '004', '70');
INSERT INTO `tblscore` VALUES ('1006', '006', '46');
INSERT INTO `tblscore` VALUES ('1006', '015', '85');
INSERT INTO `tblscore` VALUES ('1007', '002', '87');
INSERT INTO `tblscore` VALUES ('1007', '003', '72');
INSERT INTO `tblscore` VALUES ('1007', '006', '84');
INSERT INTO `tblscore` VALUES ('1007', '011', '85');
INSERT INTO `tblscore` VALUES ('1008', '001', '94');
INSERT INTO `tblscore` VALUES ('1008', '004', '34');
INSERT INTO `tblscore` VALUES ('1008', '006', '32');
INSERT INTO `tblscore` VALUES ('1008', '012', '85');
INSERT INTO `tblscore` VALUES ('1008', '013', '97');
INSERT INTO `tblscore` VALUES ('1009', '001', '96');
INSERT INTO `tblscore` VALUES ('1009', '002', '82');
INSERT INTO `tblscore` VALUES ('1009', '003', '90');
INSERT INTO `tblscore` VALUES ('1009', '008', '92');
INSERT INTO `tblscore` VALUES ('1009', '010', '82');
INSERT INTO `tblscore` VALUES ('1010', '001', '96');
INSERT INTO `tblscore` VALUES ('1010', '002', '87');
INSERT INTO `tblscore` VALUES ('1010', '003', '90');
INSERT INTO `tblscore` VALUES ('1011', '009', '24');
INSERT INTO `tblscore` VALUES ('1012', '003', '30');
INSERT INTO `tblscore` VALUES ('1012', '009', '25');
INSERT INTO `tblscore` VALUES ('1013', '001', '16');
INSERT INTO `tblscore` VALUES ('1013', '002', '37');
INSERT INTO `tblscore` VALUES ('1013', '006', '42');
INSERT INTO `tblscore` VALUES ('1013', '007', '55');
INSERT INTO `tblscore` VALUES ('1013', '012', '34');
INSERT INTO `tblscore` VALUES ('1013', '014', '86');
INSERT INTO `tblscore` VALUES ('1013', '016', '44');

-- ----------------------------
-- Table structure for `tblstudent`
-- ----------------------------
DROP TABLE IF EXISTS `tblstudent`;
CREATE TABLE `tblstudent` (
  `StuId` varchar(10) NOT NULL,
  `StuName` varchar(10) NOT NULL,
  `StuAge` int(10) DEFAULT NULL,
  `StuSex` char(1) NOT NULL,
  PRIMARY KEY (`StuId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of tblstudent
-- ----------------------------
INSERT INTO `tblstudent` VALUES ('1000', '张无忌', '18', '男');
INSERT INTO `tblstudent` VALUES ('1001', '周芷若', '19', '女');
INSERT INTO `tblstudent` VALUES ('1002', '杨过', '19', '男');
INSERT INTO `tblstudent` VALUES ('1003', '赵敏', '18', '女');
INSERT INTO `tblstudent` VALUES ('1004', '小龙女', '17', '女');
INSERT INTO `tblstudent` VALUES ('1005', '张三丰', '18', '男');
INSERT INTO `tblstudent` VALUES ('1006', '令狐冲', '19', '男');
INSERT INTO `tblstudent` VALUES ('1007', '任盈盈', '20', '女');
INSERT INTO `tblstudent` VALUES ('1008', '岳灵珊', '19', '女');
INSERT INTO `tblstudent` VALUES ('1009', '韦小宝', '18', '男');
INSERT INTO `tblstudent` VALUES ('1010', '康敏', '17', '女');
INSERT INTO `tblstudent` VALUES ('1011', '萧峰', '19', '男');
INSERT INTO `tblstudent` VALUES ('1012', '黄蓉', '18', '女');
INSERT INTO `tblstudent` VALUES ('1013', '郭靖', '19', '男');
INSERT INTO `tblstudent` VALUES ('1014', '周伯通', '19', '男');
INSERT INTO `tblstudent` VALUES ('1015', '瑛姑', '20', '女');
INSERT INTO `tblstudent` VALUES ('1016', '李秋水', '21', '女');
INSERT INTO `tblstudent` VALUES ('1017', '黄药师', '18', '男');
INSERT INTO `tblstudent` VALUES ('1018', '李莫愁', '18', '女');
INSERT INTO `tblstudent` VALUES ('1019', '冯默风', '17', '男');
INSERT INTO `tblstudent` VALUES ('1020', '王重阳', '17', '男');
INSERT INTO `tblstudent` VALUES ('1021', '郭襄', '18', '女');

-- ----------------------------
-- Table structure for `tblteacher`
-- ----------------------------
DROP TABLE IF EXISTS `tblteacher`;
CREATE TABLE `tblteacher` (
  `TeaId` varchar(3) NOT NULL,
  `TeaName` varchar(10) NOT NULL,
  PRIMARY KEY (`TeaId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of tblteacher
-- ----------------------------
INSERT INTO `tblteacher` VALUES ('001', '姚明');
INSERT INTO `tblteacher` VALUES ('002', '叶平');
INSERT INTO `tblteacher` VALUES ('003', '叶开');
INSERT INTO `tblteacher` VALUES ('004', '孟星魂');
INSERT INTO `tblteacher` VALUES ('005', '独孤求败');
INSERT INTO `tblteacher` VALUES ('006', '裘千仞');
INSERT INTO `tblteacher` VALUES ('007', '裘千尺');
INSERT INTO `tblteacher` VALUES ('008', '赵志敬');
INSERT INTO `tblteacher` VALUES ('009', '阿紫');
INSERT INTO `tblteacher` VALUES ('010', '郭芙蓉');
INSERT INTO `tblteacher` VALUES ('011', '佟湘玉');
INSERT INTO `tblteacher` VALUES ('012', '白展堂');
INSERT INTO `tblteacher` VALUES ('013', '吕轻侯');
INSERT INTO `tblteacher` VALUES ('014', '李大嘴');
INSERT INTO `tblteacher` VALUES ('015', '花无缺');
INSERT INTO `tblteacher` VALUES ('016', '金不换');
INSERT INTO `tblteacher` VALUES ('017', '乔丹');