PTA-数据库作业题（二）

前言

所有的题目都是课后作业题，仅供学习参考使用

---

刚做完的自学作业，这一部分涉及有视图以及查询，针对PTA通过问题也会提到一些。

一、题目

10-1 查询xscj表中的学号，姓名，计算机三项信息，结果按计算机成绩的降序排列。

select 学号,姓名,计算机
from xscj
order by 计算机 desc

稍微提一下，order by 默认升序，desc表示降序排列，asc表示升序排列，查找时按照在其后的优先级进行查找。

10-2 查询图书表中各出版社图书的数目，结果按图书数目降序排序

select 出版社,count(*) as 图书数目
from 图书
group by 出版社
order by 图书数目 desc

10-3 查询图书的条形码，书名，出版社和出版日期，要求结果按出版社升序排列，出版社相同的数据按出版日期降序排列

select 条形码,书名,出版社,出版日期
from 图书
order by 出版社 asc,出版日期 desc

10-4 查询选修了“0000008”课程的学生的学号及其成绩，查询结果按分数降序排列

select sno,score
from sc
where cno=0000008
order by score desc

10-5 查询成绩不及格的学生学号、课号和成绩，并按成绩降序排列

select sno,cno,score
from sc
where score < 60
order by score desc

10-6 查询信息学院的男生信息，查询结果按出生日期升序排序，出生日期相同的按生源地降序排序

select *
from students
where sdept ='信息学院' and ssex='男'
order by bday asc,bplace desc

10-7 检索出被学生选修的课程编号，结果按着课程编号降序排列

select distinct cno
from score
order by cno desc

10-8 查询商品表，先按商品分类升序排列，再按商品价格降序排列

select name,category_id,price
from sh_goods
order by category_id asc,price desc

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

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

当聚合函数和非聚合函数同时使用的时候，需要使用 group by，其后跟非聚合函数即可，否则无意义

10-10 查询课程“Dp010004”的学生学号和成绩，并按成绩降序排列，成绩相同按学号升序排列

select StudentID,Grade
from Grade
where CourseID ='Dp010004'
order by Grade desc,StudentID asc

10-11 查询zgda表中的工号，姓名，性别，职称4项信息，显示结果时首先按照女性在前的顺序，如果性别相同则按照职称的升序排列。

select 工号,姓名,性别,职称
from zgda
order by 性别	desc,职称 asc

10-12 查询图书表中的图书的条形码和书名，要求结果按条形码升序排序

select 条形码,书名
from 图书
order by 条形码 asc

10-13 在读者表中查询全部读者信息，要求女性在前男性在后，同为女性读者的按账号升序排列

select *
from 读者
order by 性别 desc,账号 asc

10-14 在员工表中查询所有男性员工的编号，姓名和入职日期，结果按员工编号升序排列

select 员工编号,姓名,入职日期
from 员工
where 性别='男'
order by 员工编号 asc

10-15 在顾客表中查询青岛的顾客编号，公司名称和电话，结果按顾客编号升序排列

select 顾客编号,公司名称,电话
from 顾客
where 城市='青岛'
order by 顾客编号 asc

10-16 检索所有选修了40008课的学生学号和成绩，查询结果按学号升序排列

select sno as 学号, grade as 成绩
from score 
where cno='40008'
order by sno asc

10-17 查看2010年的销售记录，查询结果以销售日期升序排列

select *
from recorder
where year(sale_date)='2010'
order by sale_date asc

year 函数可以直接使用，之前的作业题里有显示如何表示时间

10-18 查询马齐的购物情况，查询结果以商品编号升序排列

select good.gid '商品编号',good.gname '商品名称',good.price '单价',recorder.quantity '购买数量',
recorder.sale_date '购买日期'
from customer,good,recorder
where customer.cid=recorder.cid and
recorder.gid=good.gid and customer.cname='马齐'
order by good.gid asc;

这道题真的很磨人，上面是 PTA通过的答案，下面是我自己练习是时候写的

select gid as 商品编号,gname as 商品名称,price as 单价,count(recorder.quantity) as 购买数量
from customer,recorder,good
where recorder.gid=good.gid and recorder.cid=customer.cid
and customer.cname='马齐'
order by gid asc

我写的这部分，PTA报错显示的是“运行时错误”，经过对比后可以看到其实不需要使用聚合函数，直接select recorder.quantity 就行

10-19 查看1998年出生的学生信息，查询结果以学号升序排列。

select sno as 学号,sname as 姓名,pnum as 身份证号码
from student
where year(birth)='1998'
order by sno asc

10-20 统计每个学生的选课门数和考试总成绩，并按选课门数升序排列

select sno as 学号,count(*) as 选课门数,sum(score) as 考试总成绩
from sc
group by sno 
order by count(*) asc

10-21 统计各系的老师人数，并按人数升序排序

select tdept as 系别,count(*) as 教师人数
from teachers
group by tdept
order by count(*) asc

10-22 查询所有教师信息,按教师编号升序排列

select *
from Teacher
order by TeacherID asc

10-23 创建视图，包含商品打折前后的价格信息

CREATE
VIEW
view_goods
AS
select id,name,price as old_price,(price*0.5) as new_price
from sh_goods

SQL CREATE VIEW 语法
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

10-24 创建视图，包含拥有属性值个数大于1的商品信息

CREATE VIEW view_goods_2 AS
Select id,name 
From sh_goods 
Where id In (Select goods_id 
             From sh_goods_attr_value 
             Group By goods_id 
             Having Count(id)>1);

先创建视图，再根据要求select

10-25 创建一个有关男会员信息的视图customer_male，字段名分别是会员编号,会员姓名,性别,电话。

CREATE VIEW customer_male AS
SELECT cid as 会员编号,cname as 会员姓名,sex as 性别,phone as 电话
FROM customer
WHERE sex ='男'

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

CREATE VIEW good_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

1、这部分时间用的是between... and...
因为题目显示的是timestamp格式，即YYYY-MM-DD HH:II:SS这个格式，如2010-04-01 00:00:00这种格式的SQL语句。这样我们就要用到两种方法，一种就是我上面写的答案“between and”;
另一种就是 sale_date >='2010-04-01 00:00:00' and sale_date <= '2010-07-31 00:00:00'
但我在PTA上用第二种时显示内部错误，所以就用了第一种。这个我也不清楚是什么原因，有知道的大佬可以了评论告诉我

这道题一开始我写的是 count(quantity)，但是PTA运行结果显示“答案错误”，后来查了下发现sum()函数和count()函数的区别在于sum(*)对符合条件的记录的数值列求和；count（*）对符合条件的结果个数（或者记录）求和。
SUMMARY
1）求和用累加sum(),求行的个数用累计count()
2）数据库中对空值的处理：sum()不计算，count()认为没有此项；

---

总结

我在做作业的过程中，有很多的小细节错误，比如忽略了创建视图直接select等等，但总体来说PTA的sql练习题逻辑比较简单，但需要注重的点比较多