PTA经典练习查询题——都是简单简单的Oracle题目

1.检索出所有成绩为空的学号，课号。
select sno,cno from score where grade is null

2.查看1998年出生的学生信息，查询结果以学号升序排列。
select sno 学号,sname 姓名,pnum 身份证号码 from student where extract(year from birth)=1998 order by sno

 3.本题目要求编写SQL语句，查询sh_goods表，先按商品分类category_id升序排列，对于相同分类的商品再按商品价格price降序排列 。
select name,category_id,price from sh_goods order by category_id asc, price desc

 4. 检索出sh_goods表中每项keyword对应的商品数量，统计所得商品数量对应的字段名称依据输出样例设置
select keyword,count(keyword) goodscount from sh_goods group by keyword order by keyword asc

 5.获取sh_goods表中每个分类下商品的最高价格
select category_id,max(price) max_price from sh_goods group by category_id

 6. 查询sh_goods表中商品库存stock的最高和最低值
select max(stock) stock1,min(stock) stock2 from sh_goods

 7. 在sh_goods表中查询不同商品分类（category_id）下商品数量大于2的商品的平均价格。
select category_id,avg(price) average from sh_goods  group by category_id having count(category_id)>2

 8.sh_goods表中，查询5星好评的商品库存增加850后的值，以及这些商品进行75折促销后的价格。
select name,price old_price,stock old_stock,price*0.75 new_price,stock+850 new_stock from sh_goods where score=5

 9. 查询sh_goods表中用户评分score在前20%的商品名称
select top 2 name from sh_goods order by score desc

 10.询sh_goods表中价格在2000到6000元之间的商品编号、名称和价格，注意此范围包括2000元和6000元的商品
select id,name,price from sh_goods where price>=2000 and price<=6000

 11.查询goods表中价格为NULL的商品信息
select id,name,price from goods where price is null

 12. 获取goods表中商品名称含有“pad”的商品
select id,name,price from goods where name like'%pad%'

 13.查询sh_goods表中商品分类编号为3，并且用户评分为5星的商品信息
select id,name,price from sh_goods where category_id=3 and score=5.00

 14.查询sh_goods表中用户评分为4.5或者价格小于10元的商品。
select name,price,score from sh_goods where score=4.50 or price<10

 15.在Teacherr表中查询出所有教师所在部门编号，并消除重复记录。
select DISTINCT DepartmentID from teacher

 16.查询Teacher表中所有教师信息,按教师编号升序排列
select * from Teacher order by TeacherID asc

 17.询Student表中出出生日期最大（即年龄最小）的学生姓名及出生日期
select StudentName,Birth from Student where Birth in (select max(Birth) from Student)

 18.查询Class表中学生人数大于5人的班级编号
select ClassID from Class where StudentNum>5

 19.查询Grade表中课程“Dp010001”的最高分
select max(Grade) max_grade from Grade where CourseID='Dp010001'

 20.查询Grade表中课程“Dp010004”的学生学号和成绩，并按成绩降序排列，成绩相同按学号升序排列
select StudentID,Grade from Grade where CourseID='Dp010004' order by Grade desc,StudentID asc;