数据库作业-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 Category join Goods on
Category.CategoryNO=Goods.CategoryNO
where Category.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 join Category on
Category.CategoryNO=Goods.CategoryNO
where Goods.Inprice > 20 and Category.CategoryNO='cn005';
--5．查询库存数量小于 10 的商品号，供应商号，商品名和数量。
select Category.CategoryNO,Supplier.supplierno,Goods.GoodsName,Goods.Number from
Category join Goods on Category.CategoryNO=Goods.CategoryNO
join Supplier on Goods.supplierno=Supplier.supplierno
where Goods.Number >10;
--6．查询有购买记录学生的学号。
select Student.SNO from
Student join SaleBill on Student.SNO=SaleBill.SNO;

--7．查询计算机专业“IT”学生的学号，姓名和年龄
select Student.SNO,Student.SName
from Student
where Student.College='cs' and Student.Major='it';

-- 8．查询年龄在 22 到 24 岁学生的学号，姓名，学院，专业和年龄
select SName,College,Major,2022-Birthyear as age from Student
where 2022-Birthyear like '2[2-4]';

--9． 查询姓张的同学的学号，姓名，学院
select SNO,Sname,College from Student where SName like '张%';

--10．查询库存数量在 20 以上的商品信息，并按数量的降序排列
select * from Goods join Category on
Category.CategoryNO=Goods.CategoryNO
where Goods.Number>20
order by Goods.Number desc;

--11．查询库存数量在 10 以内的商品编号、商品名称、类别号、数量，按类别升序库
--存数量降序排列结果。
select Goods.GoodsNO,Goods .GoodsName,Category.CategoryNO,Goods.Number
from Goods join Category on
Category.CategoryNO=Goods.CategoryNO
where Goods.Number<10
order by Category.CategoryNO ,Goods.Number desc;
--12．查询库存数量排名前三的商品信息。
select top 3 *
from Goods join Category on
Category.CategoryNO=Goods.CategoryNO
order by Goods.Number;
--13. 查询类别为“cn001”库存数量最大的商品，显示商品号，商品名和数量
select * from goods;
select top 1 *
from Goods join Category on
Category.CategoryNO=Goods.CategoryNO
where Category.CategoryNO='cn001'
order by Goods.Number desc;
--14．查询管理信息系统专业“MIS”的学生人数。
select count(*) as Major from Student
where Major='mis';
--15．按学院统计各个学院，年龄在 20 岁以上的学生人数。
select College,count((2022-Student.Birthyear)) age from Student
where 2022-Student.Birthyear>20
group by College
;
--16．统计 2018 年各商品的销售总量。
select SaleBill.GoodsNO,sum(SaleBill.number) from Goods join SaleBill on SaleBill.GoodsNO=Goods.GoodsNO
where Happentime between '2018/01/01' and '2018/12/31'
group by SaleBill.GoodsNO
;

--17．查询学生人数在 3 人以上的学院及学生人数。
select college,count(college)as number from Student
group by (college)
having count(college)>3;
--18．查询 2019 年商品购买总数在 5 以上的学生的学号和购买总数。
select student.sno,COUNT(salebill.goodsno)as sum1 from student join salebill on student.sno=salebill.sno
where Happentime between '2019/01/01' and '2022/12/31'
group by student.sno
having COUNT(salebill.goodsno)>1
order by sum1
;
--19．查询咖啡类商品的平均售价
select avg(Goods.Saleprice)as avg_price from Goods join Category on Goods.CategoryNO=Category.CategoryNO
where Category.CategoryName='咖啡';

--20_1 查询与张小红同学院的学生的学号、姓名、学院 知道张小红的学院
select SNO,SName,College from student
where Student.College='acc';
--不知道张小红的学院
select SNO,SName,College from Student where College in
(select College from Student where SName = '张小红') and 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 Student.SNO,SName,College from Student join SaleBill on Student.SNO=SaleBill.SNO
group by student.sno,sname,college;

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