目录
实现集合减运算,查询选修课程c++而没有选修课程java 的学生的编号
利用集合交运算,查询既选修课程c++又选修课程java的学生的编号
查询本周过生日的学生,假设现在是 2020-12-22 00:00:00.000000
成绩有重复的情况下,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
实现集合减运算,查询选修课程c++而没有选修课程java 的学生的编号
表结构:
请在这里写定义表结构的SQL语句。例如:
CREATE TABLE `students` ( `sid` bigint(20) NOT NULL, `sname` char(10) CHARACTER SET utf8mb4 DEFAULT NULL, `email` char(20) CHARACTER SET utf8mb4 DEFAULT NULL, `grade` char(10) CHARACTER SET utf8mb4 DEFAULT NULL, PRIMARY KEY (`sid`) ); CREATE TABLE `teachers` ( `tid` bigint(20) NOT NULL, `tname` char(10) CHARACTER SET utf8mb4 DEFAULT NULL, `email` char(20) CHARACTER SET utf8mb4 DEFAULT NULL, `salary` smallint(6) DEFAULT NULL, PRIMARY KEY (`tid`) ); CREATE TABLE `courses` ( `cid` bigint(20) NOT NULL, `cname` char(10) CHARACTER SET utf8mb4 DEFAULT NULL, `hour` char(10) CHARACTER SET utf8mb4 DEFAULT NULL, PRIMARY KEY (`cid`) ); CREATE TABLE `choices` ( `no` char(10) CHARACTER SET utf8mb4 NOT NULL, `sid` bigint(20) DEFAULT NULL, `tid` bigint(20) DEFAULT NULL, `cid` bigint(20) DEFAULT NULL, `sorce` int(11) DEFAULT NULL, PRIMARY KEY (`no`), KEY `FK_CHOICES_STUDENTS_idx` (`sid`), KEY `FK_CHOICES_TEACHERS_idx` (`tid`), KEY `FK_CHOICES_COURESE_idx` (`cid`), CONSTRAINT `FK_CHOICES_COURESE` FOREIGN KEY (`cid`) REFERENCES `courses` (`cid`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK_CHOICES_STUDENTS` FOREIGN KEY (`sid`) REFERENCES `students` (`sid`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK_CHOICES_TEACHERS` FOREIGN KEY (`tid`) REFERENCES `teachers` (`tid`) ON DELETE NO ACTION ON UPDATE NO ACTION );
表样例
请在这里给出上述表结构对应的表样例。例如
choices
表:
courses
表:输出样例:
请在这里给出输出样例。例如:
select ch.sid from choices ch join courses c on ch.cid=c.cid where c.cname ='c++' and ch.sid not in (select ch.sid from choices ch join courses c on ch.cid=c.cid where c.cname ='java')
利用集合交运算,查询既选修课程c++又选修课程java的学生的编号
表结构:
请在这里写定义表结构的SQL语句。例如:
CREATE TABLE `students` ( `sid` bigint(20) NOT NULL, `sname` char(10) CHARACTER SET utf8mb4 DEFAULT NULL, `email` char(20) CHARACTER SET utf8mb4 DEFAULT NULL, `grade` char(10) CHARACTER SET utf8mb4 DEFAULT NULL, PRIMARY KEY (`sid`) ); CREATE TABLE `teachers` ( `tid` bigint(20) NOT NULL, `tname` char(10) CHARACTER SET utf8mb4 DEFAULT NULL, `email` char(20) CHARACTER SET utf8mb4 DEFAULT NULL, `salary` smallint(6) DEFAULT NULL, PRIMARY KEY (`tid`) ); CREATE TABLE `courses` ( `cid` bigint(20) NOT NULL, `cname` char(10) CHARACTER SET utf8mb4 DEFAULT NULL, `hour` char(10) CHARACTER SET utf8mb4 DEFAULT NULL, PRIMARY KEY (`cid`) ); CREATE TABLE `choices` ( `no` char(10) CHARACTER SET utf8mb4 NOT NULL, `sid` bigint(20) DEFAULT NULL, `tid` bigint(20) DEFAULT NULL, `cid` bigint(20) DEFAULT NULL, `sorce` int(11) DEFAULT NULL, PRIMARY KEY (`no`), KEY `FK_CHOICES_STUDENTS_idx` (`sid`), KEY `FK_CHOICES_TEACHERS_idx` (`tid`), KEY `FK_CHOICES_COURESE_idx` (`cid`), CONSTRAINT `FK_CHOICES_COURESE` FOREIGN KEY (`cid`) REFERENCES `courses` (`cid`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK_CHOICES_STUDENTS` FOREIGN KEY (`sid`) REFERENCES `students` (`sid`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK_CHOICES_TEACHERS` FOREIGN KEY (`tid`) REFERENCES `teachers` (`tid`) ON DELETE NO ACTION ON UPDATE NO ACTION );
表样例
请在这里给出上述表结构对应的表样例。例如
courses
表:
choices
表:
输出样例:
请在这里给出输出样例。例如:
select ch.sid from choices ch join courses c on ch.cid=c.cid where ch.sid not in (select ch.sid from choices ch join courses c on ch.cid=c.cid where c.cname <>'java' ) and c.cname ='c++'
查询本周过生日的学生,假设现在是 2020-12-22 00:00:00.000000
表结构:
CREATE TABLE `course` ( `CId` varchar(10) DEFAULT NULL, `Cname` varchar(10) CHARACTER SET utf8mb4 DEFAULT NULL, `TId` varchar(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `sc` ( `SId` varchar(10) DEFAULT NULL, `CId` varchar(10) DEFAULT NULL, `score` decimal(18,1) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `student` ( `SId` varchar(10) DEFAULT NULL, `Sname` varchar(10) CHARACTER SET utf8mb4 DEFAULT NULL, `Sage` datetime(6) DEFAULT NULL, `Ssex` varchar(10) CHARACTER SET utf8mb4 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `teacher` ( `TId` varchar(10) DEFAULT NULL, `Tname` varchar(10) CHARACTER SET utf8mb4 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
表样例
Course
表:
Sc
表:
Student
表:
Teacher
表:输出样例:
请在这里给出输出样例。例如:
select * from student where WEEK("2020-12-22 00:00:00.000000")=WEEK(Sage)
查询各学生的年龄,只按年份来算
提示:请使用SELECT语句作答。
表结构:
请在这里写定义表结构的SQL语句。例如:
CREATE TABLE `student` ( `SId` varchar(10) DEFAULT NULL, `Sname` varchar(10) CHARACTER SET utf8mb4 DEFAULT NULL, `Sage` datetime(6) DEFAULT NULL, `Ssex` varchar(10) CHARACTER SET utf8mb4 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
表样例
请在这里给出上述表结构对应的表样例。例如
Course
表:
Sc
表:
student
表:
Teacher
表:输出样例:
select sname,(2020-year(Sage)) age from student
成绩有重复的情况下,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
提示:请使用SELECT语句作答。
表结构:
请在这里写定义表结构的SQL语句。例如:
CREATE TABLE `sc` ( `SId` varchar(10) DEFAULT NULL, `CId` varchar(10) DEFAULT NULL, `score` decimal(18,1) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `student` ( `SId` varchar(10) DEFAULT NULL, `Sname` varchar(10) CHARACTER SET utf8mb4 DEFAULT NULL, `Sage` datetime(6) DEFAULT NULL, `Ssex` varchar(10) CHARACTER SET utf8mb4 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `course` ( `CId` varchar(10) DEFAULT NULL, `Cname` varchar(10) CHARACTER SET utf8mb4 DEFAULT NULL, `TId` varchar(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `teacher` ( `TId` varchar(10) DEFAULT NULL, `Tname` varchar(10) CHARACTER SET utf8mb4 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
输出样例:
请在这里给出输出样例。例如:
SId Sname Sage Ssex score CId 51 aaa 2017-12-25 00:00:00 000000 女 66.6 06 select student.*,sc.score,sc.CId from course, sc, teacher,student where teacher.TId = course.TId and teacher.Tname = '张三' and course.CId = sc.CId and student.SId = sc.SId and sc.score in ( select max( score ) from course,sc,teacher,student where teacher.TId = course.TId and teacher.Tname = '张三' and course.CId = sc.CId and student.SId = sc.SId )
按各科平均成绩从低到高和及格率的百分数从高到低顺序:
表结构:
CREATE TABLE
course
(CId
varchar(10) DEFAULT NULL,Cname
varchar(10),TId
varchar(10) DEFAULT NULL
);CREATE TABLE
sc
(SId
varchar(10) DEFAULT NULL,CId
varchar(10) DEFAULT NULL,score
decimal(18,1) DEFAULT NULL
);表样例
course
表:
sc
表:输出样例:
select cid as 课程号, coalesce(avg(score),0) as 平均成绩, 100*sum(case when coalesce(score,0)>=60 then 1 else 0 end)/count(*) as 及格百分数 from sc group by cid order by 及格百分数 desc,平均成绩