数据库作业-sql语句查询 - 物流园资讯移动站

数据库作业-sql语句查询

2024-11-10 18:29

数据库作业-sql语句查询

建表的插入数据的表

-- 1. 查询全部商品信息。 SELECt * from goods

-- 2．查询类别为“cn002”的所有商品信息。 SELECt * from goods JOIN category on goods.categoryno = category.categoryno where goods.categoryno ="cn002" -- 3. 查询类别为“cn001”和“cn002”对应的所有商品信息。 select * from category join goods on category.categoryno=goods.categoryno where category.categoryno like 'cn00[1-2]'; -- 4．查询类别为“cn005”且进价大于 20 的商品信息。 SELECt * from goods WHERe goods.categoryno="cn005" and goods.inprice >20 -- 5．查询库存数量小于 10 的商品号，供应商号，商品名和数量。 SELECt g.goodsno,g.goodsname,g.supplierno,g.number from goods as g where g.number <10 -- 6．查询有购买记录学生的学号。 SELECt DISTINCT s.sno from salebill as s

-- 7．查询计算机专业“IT”学生的学号，姓名和年龄 SELECt s.sno,s.sname,2022-s.birthyear as age from student as s WHERe s.major ='IT' -- 8．查询年龄在 22 到 24 岁学生的学号，姓名，学院，专业和年龄 SELECt s.sno,s.sname,s.college,s.major,2022-s.birthyear as age from student as s where 2022-s.birthyear BETWEEN 22 and 24

-- 9．查询姓张的同学的学号，姓名，学院。 SELECt s.sno,s.sname,s.college from student as s where s.sname like "张%" -- 10．查询库存数量在 20 以上的商品信息，并按数量的降序排列。 SELECt * from goods as g where g.number >20 order by g.number desc -- 11．查询库存数量在 10 以内的商品编号、商品名称、类别号、数量，按类别升序库存数量降序排列结果。 SELECt g.goodsno,g.goodsname,g.categoryno,g.number from goods as g where g.number BETWEEN 6 and 13 ORDER BY g.categoryno ,g.number desc

-- 12．查询库存数量排名前三的商品信息。 SELECt * from goods as g order by g.number DESC limit 3 -- 13. 查询类别为“cn001”库存数量最大的商品，显示商品号，商品名和数量 SELECt * from goods as g order by g.number DESC limit 1 -- 14．查询管理信息系统专业“MIS”的学生人数。 SELECt count(*) from student as s where s.major ="MIS" -- 15．按学院统计各个学院，年龄在 20 岁以上的学生人数。 SELECt s.college,count(*) from student as s where 2022-s.birthyear >20 GROUP BY s.college -- 16．统计 2018 年各商品的销售总量。 SELECt g.goodsname,sum(s.number) as 销售总量 from goods as g join salebill as s on g.goodsno = s.goodsno where s.happentime between '2018/01/01' and '2018/12/31' GROUP BY s.goodsno -- 17．查询学生人数在 3 人以上的学院及学生人数。

SELECt s.college,count(*) from student as s GROUP BY s.college HAVINg count(s.college) >3 -- 18．查询 2019 年商品购买总数在 5 以上的学生的学号和购买总数。 SELECt student.sno,count(*) as 购买总数 from student join salebill on student.sno=salebill.sno where salebill.happentime between '2019/01/01' and '2019/12/31' group by student.sno HAVINg 购买总数>5 -- 19．查询咖啡类商品的平均售价。 SELECt goods.goodsname,avg(goods.saleprice)as 平均售价 from goods join category on goods.categoryno=category.categoryno where category.categoryname ="咖啡" -- 20．查询与张小红同学院的学生的学号、姓名、学院。 SELECt s.sno,s.sname,s.college from student as s where s.college in ( SELECt student.college from student where student.sname = "张小红") and s.sname!="张小红" -- 21．查询所有商品的销售情况，包括商品号，商品名，学生姓名，销售时间，销售数量，按销售日期降序排列。

select goods.goodsno,goodsname,student.sname,happentime,salebill.number from goods join salebill on goods.goodsno=salebill.goodsno join student on salebill.sno=student.sno order by happentime desc

-- 22．查询购买了商品的学生学号、姓名、学院。 SELECt DISTINCT s.sno,s.sname,s.college from student as s join salebill as b on s.sno= b.sno

--23 查询购买了咖啡类商品的学生的学号、姓名、学院 select SNO,SName,College from Student where SNO in (select SNO from SaleBill where GoodsNO in (select GoodsNO from Goods where GoodsName like '%咖啡%'))

--24查询没有出售过的商品号，商品名，类别名、售价和数量。（连接查询和子查询） select Goods.GoodsNO,GoodsName,Categoryname,Saleprice,number from Goods join Category on Goods.CategoryNO=Category.CategoryNO where Goods.GoodsNO not in( select Goods.GoodsNO from Goods join SaleBill on Goods.GoodsNO=SaleBill.GoodsNO ) --25 查询购买了商品号为“gn001”或“gn002”商品的学号，姓名和学院。 select Student.SNO,SName,college from Goods join SaleBill on Goods.GoodsNO=SaleBill.GoodsNO join Student on Student.SNO=SaleBill.SNO where Goods.GoodsNO like 'gn00[1-2]'; --26. 查询购买了商品号为“gn001”和“gn002”商品的学号，姓名和学院。 select SNO,SName,College from Student where SNO in (select SNO from SaleBill where GoodsNO='GN001') and Student.SNO in(select SNO from SaleBill where GoodsNO='GN002') --27. 查询各学院年龄最大的学生，显示学号，姓名和学院。（相关子查询和派生表） select SNO,SName,College from Student s where (YEAR(getdate())-YEAR(birthyear))=(select MAX(YEAR(getdate())-YEAR(birthyear)) from student where s.college = student.college)

--28. 统计每个商品的销售总数，要求显示商品号，商品名和销售总数。

select Goods.GoodsNO,GoodsName,sum(SaleBill.Number) as 销售总数 from Goods,SaleBill where Goods.GoodsNO = SaleBill.GoodsNO group by Goods.GoodsNO,GoodsName

以上就是本篇文章【数据库作业-sql语句查询】的全部内容了，欢迎阅览！文章地址：http://sjzytwl.xhstdz.com/news/5538.html
资讯企业新闻行情企业黄页同类资讯首页网站地图返回首页物流园资讯移动站 http://mip.xhstdz.com/ , 查看更多