建表的插入數(shù)據(jù)的表數(shù)據(jù)庫作業(yè)-sql建表和插入數(shù)據(jù)_快樂的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. 查詢?nèi)可唐沸畔ⅰ?br> SELECT * from goods

-- 2．查詢類別為“cn002”的所有商品信息。
SELECT * from goods JOIN category on goods.categoryno = category.categoryno where goods.categoryno ="cn002"
-- 3. 查詢類別為“cn001”和“cn002”對應(yīng)的所有商品信息。
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．查詢庫存數(shù)量小于 10 的商品號，供應(yīng)商號，商品名和數(shù)量。
SELECT g.goodsno,g.goodsname,g.supplierno,g.number from goods as g where g.number <10
-- 6．查詢有購買記錄學(xué)生的學(xué)號。
SELECT DISTINCT s.sno ?from salebill as s

-- 7．查詢計算機專業(yè)“IT”學(xué)生的學(xué)號，姓名和年齡
SELECT s.sno,s.sname,2022-s.birthyear as age from student as s WHERE s.major ='IT'
-- 8．查詢年齡在 22 到 24 歲學(xué)生的學(xué)號，姓名，學(xué)院，專業(yè)和年齡
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． 查詢姓張的同學(xué)的學(xué)號，姓名，學(xué)院。
SELECT s.sno,s.sname,s.college from student as s where s.sname like "張%"
-- 10．查詢庫存數(shù)量在 20 以上的商品信息，并按數(shù)量的降序排列。
SELECT * from goods as g where g.number >20 order by g.number desc
-- 11．查詢庫存數(shù)量在 10 以內(nèi)的商品編號、商品名稱、類別號、數(shù)量，按類別升序庫存數(shù)量降序排列結(jié)果。
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．查詢庫存數(shù)量排名前三的商品信息。
SELECT ?* from goods as g order by g.number DESC limit 3
-- 13. 查詢類別為“cn001”庫存數(shù)量最大的商品，顯示商品號，商品名和數(shù)量
SELECT ?* from goods as g order by g.number DESC limit 1
-- 14．查詢管理信息系統(tǒng)專業(yè)“MIS”的學(xué)生人數(shù)。
SELECT count(*) from student as s where s.major ="MIS"
-- 15．按學(xué)院統(tǒng)計各個學(xué)院，年齡在 20 歲以上的學(xué)生人數(shù)。
SELECT s.college,count(*) from student as s where 2022-s.birthyear >20 GROUP BY s.college
-- 16．統(tǒng)計 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．查詢學(xué)生人數(shù)在 3 人以上的學(xué)院及學(xué)生人數(shù)。

SELECT s.college,count(*) from student ?as s GROUP BY s.college HAVING count(s.college) >3
-- 18．查詢 2019 年商品購買總數(shù)在 5 以上的學(xué)生的學(xué)號和購買總數(shù)。
SELECT student.sno,count(*) as 購買總數(shù) 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 購買總數(shù)>5
-- 19．查詢咖啡類商品的平均售價。
SELECT goods.goodsname,avg(goods.saleprice)as 平均售價 from goods join category on goods.categoryno=category.categoryno ?where category.categoryname ="咖啡"
-- 20．查詢與張小紅同學(xué)院的學(xué)生的學(xué)號、姓名、學(xué)院。
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．查詢所有商品的銷售情況，包括商品號，商品名，學(xué)生姓名，銷售時間，銷售數(shù)量，按銷售日期降序排列。

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．查詢購買了商品的學(xué)生學(xué)號、姓名、學(xué)院。
SELECT DISTINCT s.sno,s.sname,s.college from student as s join salebill ?as b on s.sno= b.sno?

--23 查詢購買了咖啡類商品的學(xué)生的學(xué)號、姓名、學(xué)院
select SNO,SName,College from Student where SNO in
(select SNO from SaleBill where GoodsNO in?
(select GoodsNO from Goods where GoodsName like '%咖啡%'))

--24查詢沒有出售過的商品號，商品名，類別名、售價和數(shù)量。（連接查詢和子查詢）
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”商品的學(xué)號，姓名和學(xué)院。
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”商品的學(xué)號，姓名和學(xué)院。
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. 查詢各學(xué)院年齡最大的學(xué)生，顯示學(xué)號，姓名和學(xué)院。（相關(guān)子查詢和派生表）
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. 統(tǒng)計每個商品的銷售總數(shù)，要求顯示商品號，商品名和銷售總數(shù)。

select Goods.GoodsNO,GoodsName,sum(SaleBill.Number) as 銷售總數(shù) from Goods,SaleBill?
where Goods.GoodsNO = SaleBill.GoodsNO group by Goods.GoodsNO,GoodsName

--29.查詢 2019 年商品購買總數(shù)在 5 以上的學(xué)生的姓名、學(xué)院和購買總數(shù)。
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. 找出供應(yīng)商品總數(shù)在 50 以上的供應(yīng)商號，供應(yīng)商名，供應(yīng)商地址，供應(yīng)總數(shù)，
--結(jié)果按地址分組，并按供應(yīng)總數(shù)的降序排列
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;文章來源地址http://www.zghlxwxcb.cn/news/detail-423011.html

到了這里，關(guān)于數(shù)據(jù)庫作業(yè)-sql語句查詢的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！