10-55 查询各专业学生的平均成绩
分数 10 作者 张庆单位 集美大学
本题目要求编写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
表:
输出样例:
请在这里给出输出样例。例如:文章来源:https://www.toymoban.com/news/detail-421350.html
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;
10-57 查询没有选修'C语言'课程的学生
本题目要求编写SQL语句,
检索出没有选修'C语言'课程的学生记录,输出结果集按照学号升序排序。
要求使用not exist语句实现。
表结构:
请在这里写定义表结构的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
sno 学号,sname 姓名
FROM
stu
WHERE NOT exists
(
select * from sc,cou where stu.sno = sc.sno and sc.cno = cou.cno and cname = 'C语言'
)
order by
stu.sno
10-62 查询平均成绩以上的课程
本题目要求编写SQL语句,
查询每个同学超过他选修的平均成绩的课程。
请使用:
1)用相关子查询实现
2)使用派生表实现。
表结构:
请在这里写定义表结构的SQL语句。例如:
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`),
);
表样例
请在这里给出上述表结构对应的表样例。例如
cou
表:
sc
表:
输出样例:
请在这里给出输出样例。例如:
SELECT
sno 学号,cname 课程名,grade 成绩
FROM
cou,sc a
WHERE
cou.cno = a.cno
AND
(a.grade > (SELECT avg(b.grade) FROM sc b WHERE a.sno = b.sno))
10-64 查询成绩最高的前三名同学
本题目要求编写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
sname '姓名',grade '成绩'
FROM
stu,sc,cou
WHERE
(
sc.cno = cou.cno AND cname = 'C语言' AND sc.sno = stu.sno
)
ORDER BY
grade desc
LIMIT
3
10-66 查询选修张老师讲授所有课程的学生
本题目要求编写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`)
);
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
sname
FROM
stu
WHERE NOT exists
(select *
from cou
where not exists
(select *
from sc
where sc.sno=stu.sno and sc.cno=cou.cno) and cou.teacher = '张老师')
10-70 检索部分教师并插入新表
本题目要求编写 Insert语句,
检索所授每门课程平均成绩均大于70分的教师姓名,并将检索的值插入一个新的教师表faculty表
中
表结构:
请在这里写定义表结构的SQL语句。例如:
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`)
);
CREATE TABLE `faculty` (
`tname` char(10) DEFAULT NULL
)
表样例
请在这里给出上述表结构对应的表样例。例如
cou
表:
sc
表:
输出样例:
教师表faculty表
,如下:
INSERT INTO
faculty
SELECT
teacher tname
FROM
cou c1
WHERE
cno
IN
(select cno from sc group by cno having avg(grade) > 70)
GROUP BY
teacher
HAVING
COUNT(*) = (select count(*) from cou c2 where c1.teacher = c2.teacher)
10-72 单表查询:根据运费查询订单信息
在订单表(orders)
中找出运费(Freight)
在10
到50
之间的订单编号(OrderID)
、顾客编号(CustomerID)
和职员编号(EmployeeID)
表结构:
列名 | 数据类型 | 长度 | 主码 | 说明 |
---|---|---|---|---|
OrderID | int | 11 | √ | 订单编号 |
CustomerID | varchar | 5 | 顾客编号 | |
EmployeeID | int | 11 | 员工编号 | |
OrderDate | datetime, | 订购日期 | ||
RequiredDate | datetime, | 预计到达日期 | ||
ShippedDate | datetime, | 发货日期 | ||
ShipVia | int | 11 | 运货商 | |
Freight | decimal | 10,4 | 运费 | |
ShipName | varchar | 40 | 货主姓名 | |
ShipAddress | varchar | 60 | 货主地址 | |
ShipCity | varchar | 15 | 所在城市 | |
ShipRegion | varchar | 15 | 区域 | |
ShipPostalCode | varchar | 10 | 邮政编码 | |
ShipCountry | varchar | 15 | 所在国家 |
表样例
orders
表:
orderid | customerid | employeeid |
---|---|---|
11068 | QUEEN | 8 |
10466 | COMMI | 4 |
10256 | WELLI | 3 |
10485 | LINOD | 4 |
10498 | HILAA | 8 |
10523 | SEVES | 7 |
10577 | TRAIH | 9 |
10501 | BLAUS | 9 |
10590 | MEREP | 4 |
输出样例:
orderid | customerid | employeeid |
---|---|---|
11068 | QUEEN | 8 |
10466 | COMMI | 4 |
10256 | WELLI | 3 |
10485 | LINOD | 4 |
SELECT
orderid,customerid,employeeid
FROM
orders
WHERE
Freight BETWEEN 10 AND 50
order by orderid desc;
10-74 修改学生选课成绩
本题目要求编写UPDATE语句,
在SC
表中修改‘C001’课程的成绩,若成绩小于70分则提高5%,若成绩在70分以上(含70分)则提高4%
表结构:
请在这里写定义表结构的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
表:
输出样例:
修改成绩后,sc
表如下:
UPDATE
sc
SET
grade = case
WHEN sc.grade < 70 THEN grade * 1.05
WHEN sc.grade >= 70 THEN grade * 1.04
ELSE grade
END
WHERE
cno = 'C001';
10-75 添加成绩等级rank字段
本题目要求编写UPDATE语句,
填写SC表中的成绩等级rank字段,按60分以下为E,60-69为D,70-79为C,80-89为B,90及以上为A,其它情况为NULL。
表结构:
请在这里写定义表结构的SQL语句。例如:
CREATE TABLE `sc` (
`sno` char(4) NOT NULL,
`cno` char(4) NOT NULL,
`grade` decimal(6,1) DEFAULT NULL,
`rank` char(255) DEFAULT NULL,
PRIMARY KEY (`sno`,`cno`)
)
表样例
请在这里给出上述表结构对应的表样例。例如
sc
表:
输出样例:
填写rank字段后,sc
表如下:
UPDATE
sc
SET
rank = case
WHEN sc.grade < 60 THEN 'E'
WHEN sc.grade >= 60 and sc.grade < 70 THEN 'D'
WHEN sc.grade >= 70 and sc.grade < 80 THEN 'C'
WHEN sc.grade >= 80 and sc.grade < 90 THEN 'B'
WHEN sc.grade >= 90 THEN 'A'
ELSE NULL
END
10-93 spj-查找在同一个城市的所有工程项目
本题目要求编写SQL语句,在SPJ
数据库中,查找在同一个城市的所有工程项目jname
,输出结果集以城市名city
升序排序, 城市 名相同的再以工程项目jname
升序排序。
提示:请使用SELECT语句作答。
表结构:
请在这里写定义表结构的SQL语句。例如:
CREATE TABLE j ( -- 工程项目表
jno char(3) NOT NULL, -- 工程项目号
jname nvarchar(10) DEFAULT NULL,
city nvarchar(10) DEFAULT NULL,
PRIMARY KEY ( jno )
) ;
CREATE TABLE p ( -- 零件表
pno char(3) NOT NULL, -- 零件号
pname nvarchar(10) DEFAULT NULL,
color nchar(2) DEFAULT NULL,
weight smallint DEFAULT NULL,
PRIMARY KEY ( pno )
) ;
CREATE TABLE s ( -- 供应商表
sno char(3) NOT NULL, -- 供应商号
sname nvarchar(10) DEFAULT NULL,
status char(2) DEFAULT NULL,
city nvarchar(10) DEFAULT NULL,
PRIMARY KEY ( sno )
) ;
CREATE TABLE spj ( -- 零件供应表
sno char(3) NOT NULL,
pno char(3) NOT NULL,
jno char(3) NOT NULL,
qty smallint DEFAULT NULL,
PRIMARY KEY ( sno , pno , jno ),
CONSTRAINT fk_jno FOREIGN KEY ( jno ) REFERENCES j ( jno ),
CONSTRAINT fk_pno FOREIGN KEY ( pno ) REFERENCES p ( pno ),
CONSTRAINT fk_sno FOREIGN KEY ( sno ) REFERENCES s ( sno )
);
表样例
请在这里给出上述表结构对应的表样例。例如
s
表:
p
表:
j
表:
spj
表:
输出样例:
请在这里给出输出样例。例如:
文章来源地址https://www.toymoban.com/news/detail-421350.html
SELECT
city,jname
FROM
j
WHERE
city
IN
(
SELECT
city FROM j
GROUP BY
city
HAVING
COUNT(city)>1
)
ORDER BY
city ASC,jname ASC
到了这里,关于PTA - 数据库合集1的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!