数据库作业-sql语句查询

建表的插入数据的表数据库作业-sql建表和插入数据_快乐的xiao何的博客-CSDN博客create table supplier( supplierno char(6) primary key, suppliername nvarchar(10), address nvarchar(20), number char(11) )create table category( categoryno char(5) primary key, categoryname varchar(20), descriptions text...https://blog.csdn.net/m0_53967061/article/details/124065839

-- 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

--29.查询 2019 年商品购买总数在 5 以上的学生的姓名、学院和购买总数。
select SName,College,SaleBill.number
from Goods join SaleBill on Goods.GoodsNO=SaleBill.GoodsNO
join Student on SaleBill.SNO=Student.SNO
where (Happentime between '2019/01/01' and '2022/12/31') and SaleBill.number>5
group by SName,College,SaleBill.number;
--30. 找出供应商品总数在 50 以上的供应商号供应商名供应商地址供应总数
--结果按地址分组并按供应总数的降序排列
select supplier.suppliername,Address,sum(goods.number) from
goods join supplier on goods.supplierno=supplier.supplierno
group by Address,suppliername
having sum(goods.Number)>50
order by sum(goods.number) desc;