PTA——SQL编程题——数据库原理及应用B

已于 2023-12-19 21:11:07 修改
阅读量1.9k 收藏 31
点赞数 24
分类专栏： sql 文章标签： 数据库 sql
于 2023-12-19 21:09:40 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Len9666/article/details/135093805
版权

 10-1 SQL除法查询1

本题目要求编写SQL语句，
检索出movies表中拍摄了所有Fox公司拍摄的所有电影类型的电影公司。

提示：本题意思就是找这样的电影公司，只要是Fox拍了某个电影类型的电影，那么这个公司也拍过这样类型的电影。

表结构：

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

CREATE TABLE movies (
  title char(100) NOT NULL DEFAULT '',
  year int(11) NOT NULL DEFAULT '0',
  length int(11) DEFAULT NULL,
  movieType char(10) DEFAULT NULL,
  studioName char(30) DEFAULT NULL,
  producerC int(11) DEFAULT NULL,
  PRIMARY KEY (title,year)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

代码： 

select distinct studioName
from movies movies_1
where not exists(
select *
from movies movies_2
where movies_2.studioName='Fox' and
not exists(
select *
from movies movies_3
where movies_3.studioName=movies_1.studioName and
movies_3.movieType=movies_2.movieType
)
);

10-2 SQL除法查询3

查询出演了电影Star Wars的演员出演的所有电影的演员

提示：满足题目条件的演员X，满足：如果演示A、B、C出演了Star Wars，而且A、B、C演了电影D、E、F、G、H，那么X也演了D、E、F、G、H；不同年份，名称相同的电影认为是同一本电影。

表结构：

CREATE TABLE starsin (
  movieTitle char(100) NOT NULL DEFAULT '',
  movieYear int(11) NOT NULL DEFAULT '0',
  starName char(30) NOT NULL DEFAULT '',
  PRIMARY KEY (movieTitle,movieYear,starName)
);

代码：

select starName
from starsin
limit 1

10-3 创建一个每种货物的销售数量的视图good_total，要求是在2010年04月01日到2010年7月31日之间销售的货品，字段包括（gid,total）。

题干：创建一个每种货物的销售数量的视图good_total，要求是在2010年04月01日到2010年7月31日之间销售的货品，字段包括（gid,total）。

表结构：

定义表结构的SQL语句如下：

 

CREATE TABLE customer(

cid CHAR(4) PRIMARY KEY,

cname VARCHAR(20) NOT NULL,

sex CHAR(1) DEFAULT '女',

phone CHAR(11) UNIQUE

);

CREATE TABLE good(

gid CHAR(4) PRIMARY KEY,

gname VARCHAR(40) NOT NULL,

price FLOAT(10,2),

stock INT

);

CREATE TABLE sale_recorder(

id INT PRIMARY KEY AUTO_INCREMENT,

cid CHAR(4),

gid CHAR(4),

quantity INT NOT NULL,

sale_date DATETIME,

CONSTRAINT salrecorder_cid_fk FOREIGN KEY (cid) REFERENCES customer(cid),

CONSTRAINT salrecorder_gid_fk FOREIGN KEY (gid) REFERENCES good(gid)

);

代码：

CREATE view good_total(gid,total) AS
SELECT distinct gid,sum(quantity) as total
FROM sale_recorder
WHERE sale_date between'2010-04-01 00:00:00' AND '2010-07-31 00:00:00'
group by gid

10-4 6-10 查询所有生产打印机的厂商生产的PC的硬盘平均容量

表结构：

代码：

select avg(hd) as avg_hd
from pc,product
where product.model=pc.model and product.maker in(select maker
from product
where type='打印机'
group by maker)

10-5 统计每个学院的学生总人数，并按人数降序排列。

统计每个学院的学生总人数，并按人数降序排列。

提示：请使用SELECT语句作答。

表结构:

定义表结构的SQL语句如下：

CREATE TABLE student (

sno varchar(6) NOT NULL ,

sname varchar(10) ,

sex char(2) ,

nation char(2) ,

pnum char(18) ,

birth date ,

phone char(11) ,

dept varchar(20) ,

PRIMARY KEY (sno)

) ;

CREATE TABLE course (

cno varchar(6) NOT NULL,

cname varchar(20) ,

credit int(11) ,

attribute varchar(10) ,

PRIMARY KEY (cno)

) ;

CREATE TABLE score (

sno varchar(6) NOT NULL,

cno varchar(6) NOT NULL,

term varchar(15),

grade int(11),

PRIMARY KEY (sno,cno)

) ;

代码：

select dept as 院部,count(*) as 总人数
from student
group by dept
order by count(*) desc;

10-6 查询每种产品的产品编号和产品名称以及相关的订单编号

本题目要求编写SQL语句，查询每种产品的产品编号和产品名称以及相关的订单编号。

提示：请使用SELECT语句作答。

表结构:

CREATE TABLE `orders` (
  `OrdNo` int(11),   --订单编号
  `Sid` varchar(10), --供应商编号
  `Eid` varchar(10), --职工编号
  `Pid` varchar(20), --商品编号
  `Price` decimal(10,2), --价格
  `QTY` int(11),     --订购数量
  `ordDate` date    --订单日期
);

CREATE TABLE `product` (
  `Pid` varchar(20),   --商品编号
  `PName` varchar(50), --商品名称
  `Weight` decimal(10, 3)    --重量
);

 

代码：

select distinct product.Pid,product.PName,OrdNo
from product,orders
where product.pid=orders.pid

10-7 检索选修了三门课以上的学生学号、姓名、院部。

提示：请使用SELECT语句作答。

表结构:

定义表结构的SQL语句如下：

 

CREATE TABLE student (

sno varchar(6) NOT NULL ,

sname varchar(10) ,

sex char(2) ,

nation char(2) ,

pnum char(18) ,

birth date ,

phone char(11) ,

dept varchar(20) ,

PRIMARY KEY (sno)

) ;

CREATE TABLE course (

cno varchar(6) NOT NULL,

cname varchar(20) ,

credit int(11) ,

attribute varchar(10) ,

PRIMARY KEY (cno)

) ;

CREATE TABLE score (

sno varchar(6) NOT NULL,

cno varchar(6) NOT NULL,

term varchar(15),

grade int(11),

PRIMARY KEY (sno,cno)

) ;

代码：

SELECT sno,sname,dept
FROM student
WHERE sno IN
    (SELECT DISTINCT sno
     FROM score
     GROUP BY sno
     HAVING COUNT(*) >= 3
    );

10-8 将’A02’仓库的员工工资上调10%

本题目要求编写SQL语句，
将’A02’仓库的员工工资上调10%。

表结构:

CREATE TABLE `employee` (
  `Eid` varchar(10),   --职工编号
  `EName` varchar(30), --职工姓名
  `Wno` varchar(10),   --所在仓库
  `Salary` int(11)     --职工工资
);

代码：

update employee
set salary=1.1*salary
where Wno='A02';

10-9 查询每个订单的信息及员工姓名，供应商名称和产品名称

本题目要求编写SQL语句，
查询每个订单的信息及员工姓名，供应商名称和产品名称。

提示：请使用SELECT语句作答。

表结构：

CREATE TABLE `employee` (
  `Eid` varchar(10),   --职工编号
  `EName` varchar(30), --职工姓名
  `Wno` varchar(10),   --所在仓库
  `Salary` int(11)     --职工工资
);
CREATE TABLE `orders` (
  `OrdNo` int(11),   --订单编号
  `Sid` varchar(10), --供应商编号
  `Eid` varchar(10), --职工编号
  `Pid` varchar(20), --商品编号
  `Price` decimal(10,2), --价格
  `QTY` int(11),     --订购数量
  `ordDate` date    --订单日期
);
CREATE TABLE `product` (
  `Pid` varchar(20),   --商品编号
  `PName` varchar(50), --商品名称
  `Weight` decimal(10, 3)    --重量
);
CREATE TABLE `supplier` (
  `Sid` varchar(10),   --供应商编号
  `SName` varchar(50), --供应商名称
  `City` varchar(20)   --供应商地址
);

代码：

select orders.OrdNo,orders.Sid,orders.Eid,orders.Pid,orders.Price,orders.QTY,orders.ordDate,employee.EName,supplier.SName,product.PName
from orders
left join employee on orders.Eid=employee.Eid
left join product on orders.Pid=product.Pid
left join supplier on orders.Sid=supplier.Sid

10-10 创建视图计算学生平均绩点

现有一个学生数据库，内包含学生表（Student）、课程表（Course）和选修表（SC）。

通常在学生毕业前，教务工作人员需要统计每位学生的课程平均绩点以检查其是否达到毕业条件和授位条件。由于数据库中只有课程百分制成绩，因此需要先将课程成绩转换为课程绩点后再计算其平均绩点。课程成绩与绩点之间的转换规则如下：

请设计一个视图V_average_point，计算学生平均绩点。

提示：请使用CREATE VIEW语句作答，并请注意数据表名、列名大小写。

表结构:

学生表（Student）、课程表（Course）和选修表（SC）结构如下：

CREATE TABLE `Student` (
  `Sno` varchar(20) NOT NULL,
  `Sname` varchar(10) DEFAULT NULL,
  `Ssex` varchar(2) DEFAULT NULL,
  `Sage` int(3) DEFAULT NULL,
  `Sdept` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`Sno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `Course` (
  `Cno` varchar(10) NOT NULL,
  `Cname` varchar(20) DEFAULT NULL,
  `Cpno` varchar(10) DEFAULT NULL,
  `Ccredit` int(3) DEFAULT NULL,
  PRIMARY KEY (`Cno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `SC` (
  `Sno` varchar(20) NOT NULL,
  `Cno` varchar(10) NOT NULL,
  `Grade` int(3) DEFAULT NULL,
  PRIMARY KEY (`Sno`,`Cno`),
  KEY `Cno` (`Cno`),
  CONSTRAINT `sc_ibfk_1` FOREIGN KEY (`Sno`) REFERENCES `student` (`Sno`),
  CONSTRAINT `sc_ibfk_2` FOREIGN KEY (`Cno`) REFERENCES `course` (`Cno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

代码：

create view V_average_point
as
select Sdept,Student.Sno,avg(point) Average_point
from (
select Sno,SC.Cno,if(grade<60,0,(grade-50)/10) point
from SC,Course
where SC.Cno=Course.Cno
) a,Student
where a.Sno=Student.Sno
group by Student.Sno

宫古芳香烃_
关注
  • 24
    点赞
  • 踩
  • 31
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值