sql实验代码整理：在数据库supermarket上完成下列操作

1.查询商品种类信息。

select categoryno,categoryname,description from category;

2.查询IT专业所有学生信息。

select * from student WHERE major='IT';

3.查询 MIS专业年龄小于20岁的学生信息。并为MIS列取别名为“信息管理系统”。

SELECT sno, sname, birthyear, ssex, college, major 信息管理系统, weixin

FROM student

WHERE YEAR(CURRENT_DATE()) - birthyear < 20 AND major = 'MIS';

4.查询利润率大于30%的商品编号与商品名。

SELECT goodsno, goodsname, ROUND((saleprice - inprice) / inprice, 2) AS 利润率

FROM goods

WHERE (saleprice - inprice) / inprice > 0.3;

5.查询广州佛山供应的商品信息。

SELECT * FROM goods g join supplier s on g.supplierno = s.supplierno WHERE s.address='广州佛山'

6.查询购买了商品种类为咖啡的MIS专业的学生信息。

SELECT * FROM student WHERE sno in(

SELECT sno from salebill where goodsno in(SELECT goodsno FROM goods g join category c on g.categoryno = c.categoryno and categoryname = '咖啡'))AND major = 'MIS'

7.查询购买了商品种类为咖啡的各专业的学生人数。

SELECT student.major, COUNT(*) AS 人数

FROM (

    SELECT student.major

    FROM salebill

    INNER JOIN student ON salebill.sno = student.sno

    INNER JOIN goods ON salebill.goodsno = goods.goodsno

    INNER JOIN category ON goods.categoryno = category.categoryno

    WHERE category.categoryname = '咖啡'

) AS student

GROUP BY student.major;

8.查询购买各商品种类的各专业的学生人数。

SELECT categoryname, student.major, COUNT(*) AS 人数

FROM salebill

INNER JOIN student ON salebill.sno = student.sno

INNER JOIN goods ON salebill.goodsno = goods.goodsno

INNER JOIN category ON goods.categoryno = category.categoryno

GROUP BY categoryname, student.major;

9.查询从未购买过商品的学生信息。

SELECT * from student WHERE NOT EXISTS(SELECT * from salebill WHERE sno = student.sno);

10.查询与商品编号GN005相同产地的商品编号、商品名。

SELECT goodsno, goodsname

FROM goods

WHERE supplierno IN (

    SELECT DISTINCT supplierno

    FROM supplier

    WHERE address = (

        SELECT address

        FROM supplier s

        JOIN goods g ON s.supplierno = g.supplierno

        WHERE g.goodsno = 'GN005'

));

11.使用派生表查询各供应商的存货量。

SELECT suppliername 供应商名称,s2.sum_number 存货量 from supplier s join( SELECT supplierno,sum(number) sum_number

from goods GROUP BY supplierno) s2 on s.supplierno = s2.supplierno

12.查询售价大于该种类商品售价均值的商品号、商品名。

select GoodsNO, GoodsName from Goods G where SalePrice>(select avg(SalePrice) from goods where CategoryNO=G.CategoryNO)

13.分别用子查询与连接查询查询购买了商品编号为“GN003”和“GN0007”的学生学号与姓名。

SELECT s.sno,s.sname FROM salebill sb JOIN student s on sb.sno = s.sno WHERE sb.goodsno = 'GN003' AND EXISTS(SELECT * from salebill WHERE goodsno = 'GN007' AND sno=sb.sno);

14.查询各校销售额。

SELECT s.college,SUM(sa.number * g.saleprice) as 销售额

FROM student s join salebill sa on s.sno = sa.sno join goods g on sa.goodsno = g.goodsno

GROUP BY s.college;

15.查询购买额前三的校名、专业名。

SELECT s.college,s.major,sum(sa.number * g.saleprice) as amount from student s

join salebill sa on s.sno = sa.sno JOIN goods g on sa.goodsno = g.goodsno

GROUP BY s.college,s.major ORDER BY amount DESC LIMIT 3;

             

16.使用集合查询方式查询生产日期早于2018-1-1或库存量小于30的商品信息。

select * from Goods where ProductTime <'2018-1-1'

union select * from Goods where Number <30