PTAsql补题（1）

目录

实现集合减运算，查询选修课程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,平均成绩