数据库实验统计查询和组合查询

迷南。 2023-06-12 14:30 252阅读 0赞

商品（编号，品名，进价，库存，售价，厂商编号）  
顾客（卡号，姓名，电话，积分）  
厂商（编号，厂址，名称、电话）  
销售（顾客卡号，商品编号，数量，日期）  
根据上面基本表的信息完成下列查询。  
1 查询积分最高的顾客姓名  
2 查询销售数量1000以上的商品的厂商电话。  
3 查询生产商品5种以上的厂商信息。  
4 查询没有顾客买的商品信息。

create table 商品
    (编号 bigint,
     品名 nchar(30),
     进价 float,
     库存 int,
     售价 float,
     厂商编号 bigint,
     primary key(编号)
    );
    
    create table 顾客
    (卡号 bigint,
     姓名 nchar(15),
     电话 bigint,
     积分 float,
     primary key(卡号)
    );
    
    create table 厂商
    (编号 bigint,
     厂址 nchar(30),
     名称 nchar(30),
     电话 bigint,
     primary key(电话),
    );
    
    create table 销售
    (顾客卡号 bigint,
     商品编号 bigint,
     数量 int,
     日期 nchar(20),
    );
    
    
    
    alter table 商品
    add constraint fksc1
    check(进价 between 0 and 200)
    
    alter table 商品
    add constraint fksc2
    check(售价 between 20 and 300)
    
    alter table 顾客
    add constraint fksc3
    check(积分 between 0 and 5000)
    
    alter table 商品
    add constraint fksc4
    check(库存 between 0 and 5000)
    
    alter table 销售
    add constraint fksc5
    foreign key(商品编号) references 商品(编号)
    
    
    
    insert into 商品(编号,品名,进价,库存,售价,厂商编号)
    values(101,'伊利牛奶',88,2000,100,3541);
    insert into 商品(编号,品名,进价,库存,售价,厂商编号)
    values(102,'蒙牛牛奶',90,2000,120,3542);
    insert into 商品(编号,品名,进价,库存,售价,厂商编号)
    values(103,'旺旺牛奶',98,2000,150,3543);
    insert into 商品(编号,品名,进价,库存,售价,厂商编号)
    values(104,'旺旺雪饼',67,2000,150,3543);
    insert into 商品(编号,品名,进价,库存,售价,厂商编号)
    values(105,'旺旺仙贝',87,2000,150,3543);
    insert into 商品(编号,品名,进价,库存,售价,厂商编号)
    values(106,'旺旺qq糖',78,2000,150,3543);
    insert into 商品(编号,品名,进价,库存,售价,厂商编号)
    values(107,'旺旺小小酥',65,2000,150,3543);
    insert into 商品(编号,品名,进价,库存,售价,厂商编号)
    values(108,'旺旺泡芙',102,2000,150,3543);
    
    insert into 顾客(卡号,姓名,电话,积分)
    values(1702043,'王博',13709999999,89);
    insert into 顾客(卡号,姓名,电话,积分)
    values(1702044,'李卫',15033333333,121);
    insert into 顾客(卡号,姓名,电话,积分)
    values(1702045,'李帅',18004444444,105);
    
    insert into 厂商(编号,厂址,名称,电话)
    values(3541,'内蒙古','伊利乳品',14888888888);
    insert into 厂商(编号,厂址,名称,电话)
    values(3542,'内蒙古','蒙牛乳品',15044555555);
    insert into 厂商(编号,厂址,名称,电话)
    values(3543,'台湾','旺旺乳品',16099999999);
    
    insert into 销售(顾客卡号,商品编号,数量,日期)
    values(1702043,103,1234,'20190203');
    insert into 销售(顾客卡号,商品编号,数量,日期)
    values(1702044,104,1432,'20190204');
    insert into 销售(顾客卡号,商品编号,数量,日期)
    values(1702045,105,678,'20190205');
    insert into 销售(顾客卡号,商品编号,数量,日期)
    values(1702045,106,435,'20190206');
    insert into 销售(顾客卡号,商品编号,数量,日期)
    values(1702045,107,512,'20190207');
    insert into 销售(顾客卡号,商品编号,数量,日期)
    values(1702045,102,1111,'20190208');
    
    
    --一
    select 姓名
    from 顾客
    where 积分 in
        (select MAX(积分)
         from 顾客
        )
        
    --二
    select distinct 电话
    from 厂商
    where 厂商.编号 in
        (select 厂商编号
         from 商品
         where 商品.编号 in
                (select 商品编号
                 from 销售
                 group by 商品编号
                 having SUM(数量)>1000
                )
        )
    
    --三
    select *
    from 厂商
    where 编号 in
        (select 厂商编号
         from 商品
         group by 厂商编号
         having COUNT(*)>5
        )
        
    --四 ①嵌套查询
    
    select *
    from 商品
    where 编号 not in
        (select 商品编号
         from 销售
        )
    
    --四 ②组合查询
    
    select * 
    from 商品
    where 编号 in
        (select 编号
    	 from 商品
    	 except
    	 select 商品编号
    	 from 销售
        )