10道sql题

最新推荐文章于 2024-10-12 06:00:00 发布

雨夜偷渡客

最新推荐文章于 2024-10-12 06:00:00 发布

阅读量45

点赞数

文章标签： sql 数据库

本文链接：https://blog.csdn.net/za1478520/article/details/132742152

版权

1 表结构与数据


DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `cno` char(4) NOT NULL,
  `cname` varchar(16) NOT NULL,
  `tno` char(4) DEFAULT NULL,
  PRIMARY KEY (`cno`),
  KEY `fk_course_tno_teacher_tno` (`tno`),
  CONSTRAINT `fk_course_tno_teacher_tno` FOREIGN KEY (`tno`) REFERENCES `teacher` (`tno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('c001', 'J2SE', 't002');
INSERT INTO `course` VALUES ('c002', 'Java Web', 't002');
INSERT INTO `course` VALUES ('c003', 'SSH', 't001');
INSERT INTO `course` VALUES ('c004', 'Oracle', 't001');
INSERT INTO `course` VALUES ('c005', 'SQL SERVER 2005', 't003');
INSERT INTO `course` VALUES ('c006', 'C#', 't003');
INSERT INTO `course` VALUES ('c007', 'JavaScript', 't002');
INSERT INTO `course` VALUES ('c008', 'DIV+CSS', 't001');
INSERT INTO `course` VALUES ('c009', 'PHP', 't003');
INSERT INTO `course` VALUES ('c010', 'EJB3.0', 't002');

-- ----------------------------
-- Table structure for sc
-- ----------------------------
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc` (
  `scno` int(11) NOT NULL AUTO_INCREMENT,
  `sno` char(4) DEFAULT NULL,
  `cno` char(4) DEFAULT NULL,
  `score` double DEFAULT NULL,
  PRIMARY KEY (`scno`),
  KEY `fk_sc_sno` (`sno`),
  KEY `fk_sc_cno` (`cno`),
  CONSTRAINT `fk_sc_cno` FOREIGN KEY (`cno`) REFERENCES `course` (`cno`),
  CONSTRAINT `fk_sc_sno` FOREIGN KEY (`sno`) REFERENCES `student` (`sno`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of sc
-- ----------------------------
INSERT INTO `sc` VALUES ('1', 's001', 'c001', '64');
INSERT INTO `sc` VALUES ('2', 's002', 'c001', '78.4');
INSERT INTO `sc` VALUES ('3', 's003', 'c001', '79.9');
INSERT INTO `sc` VALUES ('4', 's004', 'c001', '62.9');
INSERT INTO `sc` VALUES ('5', 's001', 'c002', '85.9');
INSERT INTO `sc` VALUES ('6', 's003', 'c002', '70.9');
INSERT INTO `sc` VALUES ('7', 's003', 'c004', '39.9');
INSERT INTO `sc` VALUES ('8', 's001', 'c004', '62');
INSERT INTO `sc` VALUES ('9', 's004', 'c004', '48');
INSERT INTO `sc` VALUES ('10', 's008', 'c003', '58');
INSERT INTO `sc` VALUES ('11', 's008', 'c002', '45');
INSERT INTO `sc` VALUES ('12', 's008', 'c001', '34');
INSERT INTO `sc` VALUES ('13', 's008', 'c001', '61');
INSERT INTO `sc` VALUES ('14', 's002', 'c004', '86');

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `sno` char(4) NOT NULL,
  `sname` varchar(16) DEFAULT NULL,
  `sage` int(11) DEFAULT NULL,
  `ssex` char(1) DEFAULT '男',
  PRIMARY KEY (`sno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('s001', '张三', '39', '男');
INSERT INTO `student` VALUES ('s002', '李四', '44', '男');
INSERT INTO `student` VALUES ('s003', '吴鹏', '46', '男');
INSERT INTO `student` VALUES ('s004', '琴沁', '37', '女');
INSERT INTO `student` VALUES ('s005', '王丽', '37', '女');
INSERT INTO `student` VALUES ('s006', '李波', '42', '男');
INSERT INTO `student` VALUES ('s007', '刘玉', '42', '男');
INSERT INTO `student` VALUES ('s008', '萧蓉', '38', '女');
INSERT INTO `student` VALUES ('s009', '陈萧晓', '40', '女');
INSERT INTO `student` VALUES ('s010', '陈美', '20', '女');
INSERT INTO `student` VALUES ('s011', '陈美妹', '46', '男');
INSERT INTO `student` VALUES ('s012', '赵力', '20', '男');

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `tno` char(4) NOT NULL,
  `tname` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`tno`),
  UNIQUE KEY `tname` (`tname`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('t006', null);
INSERT INTO `teacher` VALUES ('t001', '刘阳');
INSERT INTO `teacher` VALUES ('t004', '刘阳阳');
INSERT INTO `teacher` VALUES ('t005', '张三');
INSERT INTO `teacher` VALUES ('t003', '胡明星');
INSERT INTO `teacher` VALUES ('t002', '谌燕');

2 SQL题


-- 1、查询两门以上不及格课程的同学的学号及其平均成绩
-- 子查询 可以 
select sno,avg(score) from sc 
	where sno in 
(select sno from sc where score<60 
	group by sno having count(cno) >0) 
	group by sno

-- 2、查询没有学全所有课的同学的学号、姓名；
select sno,sname from student where sno in(
select sno from sc GROUP BY sno having COUNT(DISTINCT cno)<
(select count(*) from course)
)

select s.sno,s.sname from student s,sc where s.sno=sc.sno 
 group by s.sno having count(distinct sc.cno)<(select count(*) from course )
-- 3、查询至少学过学号为“s001”同学所有课的其他同学学号和姓名；
select cno from sc where sno='s001' 
-- 筛选出学过这些课程的人
select * from sc ,(select cno from sc where sno='s001' ) c 
	where sc.cno =c.cno and sc.sno!='s001'
		group by sc.sno 
	having count(DISTINCT sc.cno)>=(select count(*) from sc where sno='s001')
-- 4、查询所有的课程成绩小于60 分的同学的学号、姓名；
-- 如果最大的成绩都小于60，肯定所有的都小于60
select s.sno,s.sname,max(score) from sc,student s where sc.sno=s.sno 
	group by s.sno having max(score)<60 
	
-- 5、查询课程编号为c001 且课程成绩在80 分以上的学生的学号和姓名；
select DISTINCT s.sno,s.sname from sc,student s where sc.sno=s.sno
	and sc.cno='c001' and score>=80

-- 6、查询出只选修了一门课程的全部学生的学号和姓名
select s.sno,s.sname from student s,sc where s.sno=sc.sno 
 group by sc.sno having count(DISTINCT sc.cno)=1
-- 7、查询课程名称为“Oracle”，且分数低于60 的学生姓名
select s.sname from student s,sc,course c WHERE 
s.sno=sc.sno and sc.cno =c.cno 
and c.cname="Oracle" and sc.score<60
-- 8、查询任何一门课程成绩在80 分以上的姓名；
select  s.sname from student s,sc where s.sno=sc.sno
 group by s.sno having max(score)>80
-- 9、删除学习“谌燕”老师课的SC 表记录；
delete from sc where 
cno in(
select cno from course c,teacher t 
where c.tno=t.tno and t.tname="谌燕")
-- 10、查看学生张三参加考试的全部科目的编号和对应名称，相同的科目只显示一次。
select DISTINCT c.cno,c.cname from student s,sc,course c where 
s.sno=sc.sno and sc.cno=c.cno and s.sname="张三"