数据库第8章编程题2

10-1 查询选修某两门课程的学生（MSSQL)

本题目要求编写SQL语句，
检索出 sc表中至少选修了’C001’与’C002’课程的学生学号。

提示：MSSQLServer 评测SQL语句。

表结构:

请在这里写定义表结构的SQL语句。例如：

-- 学生选课成绩表
CREATE TABLE  sc  (  
   sno  char(4) NOT NULL,  -- 学生学号
   cno  char(4) NOT NULL,  -- 选修课程号
   grade  decimal(4,1) DEFAULT NULL,
  PRIMARY KEY ( sno , cno )
)

表样例

请在这里给出上述表结构对应的表样例。例如

sc表：

输出样例:

请在这里给出输出样例。例如：

select sno 学号
from sc
where cno in('C001' , 'C002')
group by sno 
having count(cno)>=2

 10-2 查询比“网络工程”专业所有学生年龄都小的学生姓名

本题目要求编写SQL语句，
查询比“网络工程”专业所有学生年龄都小的学生姓名。

表结构:

请在这里写定义表结构的SQL语句。例如：

CREATE TABLE `stu` (
  `sno` char(4) NOT NULL,
  `sname` char(8) NOT NULL,
  `sex` tinyint(1) DEFAULT NULL,
  `mno` char(2) DEFAULT NULL,
  `birdate` datetime DEFAULT NULL,
  `memo` text,
  PRIMARY KEY (`sno`),
  KEY `fk_stu_mno` (`mno`),
  CONSTRAINT `fk_stu_mno` FOREIGN KEY (`mno`) REFERENCES `major` (`mno`)
);
CREATE TABLE `major` (
  `mno` char(2) NOT NULL,
  `mname` varchar(20) NOT NULL,
  PRIMARY KEY (`mno`)
)

表样例

请在这里给出上述表结构对应的表样例。例如

stu表：

major表：

输出样例:

请在这里给出输出样例。例如：

 

select sname
from stu
where birdate>
(
    select max(birdate)
    from stu
    where mno=
    (
        select mno
        from major
        where mname='网络工程'
    )
)

 10-3 查询成绩最高的前三名同学

本题目要求编写SQL语句，
查询‘C语言’课程成绩最高的前三名同学。

提示：使用"limit n"语句实现 "top n" 。

表结构:

请在这里写定义表结构的SQL语句。例如：

CREATE TABLE `stu` (
  `sno` char(4) NOT NULL,
  `sname` char(8) NOT NULL,
  `sex` tinyint(1) DEFAULT NULL,
  `mno` char(2) DEFAULT NULL,
  `birdate` datetime DEFAULT NULL,
  `memo` text,
  PRIMARY KEY (`sno`)
);
CREATE TABLE `cou` (
  `cno` char(4) NOT NULL,
  `cname` varchar(30) NOT NULL,
  `credit` smallint(6) DEFAULT NULL,
  `ptime` char(5) DEFAULT NULL,
  `teacher` char(10) DEFAULT NULL,
  PRIMARY KEY (`cno`)
);
CREATE TABLE `sc` (
  `sno` char(4) NOT NULL,
  `cno` char(4) NOT NULL,
  `grade` decimal(6,1) DEFAULT NULL,
  PRIMARY KEY (`sno`,`cno`),
  CONSTRAINT `fk_sc_cno` FOREIGN KEY (`cno`) REFERENCES `cou` (`CNO`),
  CONSTRAINT `fk_sc_sno` FOREIGN KEY (`sno`) REFERENCES `stu` (`sno`)
);

表样例

请在这里给出上述表结构对应的表样例。例如

stu表：

cou表：

sc表：

输出样例:

请在这里给出输出样例。例如：

select stu.sname as 姓名,sc.grade as 成绩
from stu
join sc on sc.sno=stu.sno
join cou on sc.cno=cou.cno
where cou.cname='C语言'
order by grade desc
limit 3

 10-4 查询S001学生选修而S003学生未选修的课程

本题目要求编写SQL语句，
检索出 sc表中学号为S001的学生选修的而S003学号学生未选修的课程号。

提示：MySQL不允许使用 except语句。

表结构:

请在这里写定义表结构的SQL语句。例如：

CREATE TABLE `sc` (
  `sno` char(4) NOT NULL,  -- 学号
  `cno` char(4) NOT NULL,  -- 课程号
  `grade` decimal(6,1) DEFAULT NULL,  -- 成绩
  PRIMARY KEY (`sno`,`cno`)
);

表样例

请在这里给出上述表结构对应的表样例。例如

sc表：

输出样例:

请在这里给出输出样例。例如：

 

SELECT 
    cno 课程号
FROM 
    sc
WHERE 
    sno = 'S001' and cno not IN(SELECT cno FROM sc WHERE sno = 'S003' )

 10-5 查询各专业学生的平均成绩

本题目要求编写SQL语句，
统计各专业的学生选课的平均成绩，如果某专业尚未有任何学生选修课程或成绩为空时，平均分计为0。输出结果集按照major表中的mno升序排序。

表结构:

请在这里写定义表结构的SQL语句。例如：

CREATE TABLE `major` (
  `mno` char(2) NOT NULL,
  `mname` varchar(20) NOT NULL,
  PRIMARY KEY (`mno`)
);
CREATE TABLE `stu` (
  `sno` char(4) NOT NULL,  -- 学号
  `sname` char(8) NOT NULL,  -- 姓名
  `sex` tinyint(1) DEFAULT NULL,  -- 性别
  `mno` char(2) DEFAULT NULL,  -- 专业号
  `birdate` datetime DEFAULT NULL,  -- 出生日期
  `memo` text,  --  备注
  PRIMARY KEY (`sno`),
    CONSTRAINT `fk_stu_mno` FOREIGN KEY (`mno`) REFERENCES `major` (`mno`)
);
CREATE TABLE `sc` (
  `sno` char(4) NOT NULL,
  `cno` char(4) NOT NULL,
  `grade` decimal(6,1) DEFAULT NULL,
  PRIMARY KEY (`sno`,`cno`),
  CONSTRAINT `fk_sc_sno` FOREIGN KEY (`sno`) REFERENCES `stu` (`sno`)
);

表样例

请在这里给出上述表结构对应的表样例。例如

major表：

stu表：

sc表：

输出样例:

请在这里给出输出样例。例如：

 

select mname 专业,ifnull(avg(grade),0)平均成绩
from major left outer join(
select mno,grade from stu,sc 
where stu.sno=sc.sno)as a on major.mno=a.mno
group by major.mno
order by major.mno;