数据库实验嵌套查询和连接查询

﹏ヽ暗。殇╰゛Y 2023-05-29 13:30 3阅读 0赞

题目：  
商品（编号，品名，进价，库存，售价，厂商编号）  
顾客（卡号，姓名，电话，积分）  
厂商（编号，厂址，名称、电话）  
销售（顾客卡号，商品编号，数量，日期）  
根据上面基本表的信息完成下列查询。  
1 查询积分100以上的顾客买的商品名称和价格  
2 查询比“伊利乳品”所有产品都贵的商品价格  
3 查询库存不足100的商品名称和厂商名称及电话

要求：  
1 第一个查询要求分别用连接查询和嵌套查询完成，给出查询命令、初始数据、执行果，比较不同方法的效率。  
2 其它查询要求给出查询命令、初始数据、执行结果，说明你选择连接查询或嵌套查询的原因

具体操作：

create database student
    on primary(
    	Name='student.mdf',
    	Filename='d:\student.mdf',
    	Size=5mb,
    	Maxsize=100mb,
    	Filegrowth=10%
    )
    log on(
    	Name='student.ldf',
    	Filename='d:\student.ldf',
    	Size=5,
    	Maxsize=unlimited,
    	Filegrowth=1mb
    )
    
    
    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),
     primary key(顾客卡号),
    );
    
    
    
    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 1000)
    
    alter table 销售
    add constraint fksc5
    foreign key(商品编号) references 商品(编号)
    
    
    
    insert into 商品
    values(101,'伊利牛奶',88,205,100,3541);
    insert into 商品
    values(102,'蒙牛牛奶',90,98,120,3542);
    insert into 商品
    values(103,'旺旺牛奶',98,434,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,101,5,'20190203');
    insert into 销售
    values(1702044,102,5,'20190204');
    insert into 销售
    values(1702045,103,5,'20190205');
    
    
    select * from 商品
    
    select * from 顾客
    
    select * from 厂商
    
    select * from 销售
    
    --第一题 嵌套查询
    
    select 品名'商品名称',售价'价格' 
    from 商品
    where 编号 in 
    	(select 商品编号 
    	 from 销售
    	 where 顾客卡号 in
    		(select 卡号 
    		 from 顾客
    		 where 积分 > 100)
    	)
    	
    --第一题 连接查询①
    
    select 品名'商品名称',售价'价格' 
    from 商品,销售,顾客
    where 积分>100 
    and 顾客.卡号=销售.顾客卡号
    and 销售.商品编号=商品.编号
    
    --第一题 连接查询②
    
    select 品名'商品名称',售价'价格' 
    from 销售 join 顾客
    on 顾客.卡号=销售.顾客卡号
    join 商品
    on 销售.商品编号=商品.编号
    and 积分>100 
    
    --第二题
    
    select 售价'商品价格'
    from 商品
    where 售价 > 
    	(select MAX(售价) 
    	 from 商品
    	 where 厂商编号 in	
    		(select 编号 
    		 from 厂商
    		 where 名称='伊利乳品')
    	 )
    
    --第三题
    
    select 品名'商品名称',名称'厂商名称',电话
    from 商品,厂商
    where 厂商编号=厂商.编号
    and 库存 < 100