oracle-sql语句练习

落日映苍穹つ 2022-05-14 15:21 378阅读 0赞

1.查询所有speed大于2.8的PC信息

select *
    from pcs
    where speed > 2.8

2.查询购买model为1007的购买记录信息

select *
    from sales
    where model = 1007

3.统计2013-12-20购买记录的数量（count）

select count(*)
    from sales
    where sday=to_date('2013-12-20', 'yyyy-mm-dd')

4.统计2013-12-20购买的总数量(sum)

select sum(quantity) 
    from sales 
    where sday = to_date('2013-12-20', 'yyyy-mm-dd')

5.查询硬盘大小出现在两种以上PC电脑上的硬盘大小

select hd
    from pcs
    group by hd
    having count(*) >= 2;

6.查询速度至少3.00以上的PC models信息

select model
    from pcs
    where speed >= 3.00;

7.查询哪个供应商供应laptops硬盘至少100GB以上的供应商信息  
(1)等值连接

select products.*
    from products, laptops
    where products.model=laptops.model and laptops.hd >= 100;

(2)自然连接(natural join)

select maker, model, ptype
    from products natural join laptops
    where hd >= 100;

(3)内连接

select maker, products.model, ptype
    from products inner join laptops
    on products.model = laptops.model
    where hd >= 100;

(4)自然连接(join using)

select *
    from products join laptops using(model)
    where hd >= 100;

(5)不相关子查询

select *
    from products
    where model in (
            select model
            from laptops
            where hd >= 100
    );

(6)相关子查询

select *
    from products pr
    where exists (
            select model
            from laptops la
            where pr.model = la.model and la.hd >= 100
    );

8.查询供应商B所提供的所有产品的产品编号和产品价格

select model, price
    from pcs
    where model in (
            select model
            from products
            where maker = 'B'
    )
    union
    select model, price
    from laptops
    where model in (
            select model
            from products
            where maker = 'B'
    )
    union
    select model, price
    from printers
    where model in (
            select model
            from products
            where maker = 'B'
    );

9.查找所有的彩色打印机的model numbers

select model
    from printers
    where color = 'TRUE';

10.查找供应laptops但不供应pc的供应商信息

select *
    from products x
    where x.ptype = 'laptop' and 'pc' not in (
            select ptype
            from products y
            where x.maker = y.maker
    );

11.查询具有相同运行速度和内存的PC电脑编号对  
每个pc models对只出现一次，即 (i, j)出现，则 (j, i)不出现  
Find those pairs of PC models that have both the same speed and ram.  
A pair should be listed only once; e.g., list (i, j) but not (j, i)

select x.model, y.model
    from pcs x, pcs y
    where x.model < y.model and x.model <> y.model and x.speed = y.speed and x.ram = y.ram;

12.查询销售三种不同型号PC电脑的供应商  
Find the makers who sell exactly three different models of PC

select maker
    from (
            select distinct *
            from products
            where ptype = 'pc'
    )
    group by maker
    having count(*) >= 3;

13.查询至少提供3种PC运行速度的供应商  
Find the makers of PCs with at least three different speeds

select maker
    from (
            select distinct maker, speed
            from products, pcs
            where products.model = pcs.model
    )
    group by maker
    having count(speed) >= 3;

14.查询提供的PC或laptop运行速度至少是2.80且有两种以上产品的供应商  
Find those makers of at least two different computers (PCs or laptops) with speeds of at least 2.80

select maker
    from(
            select maker, products.model
            from products, pcs
            where products.model = pcs.model and pcs.speed >= 2.80
            union
            select maker, products.model
            from products, laptops
            where products.model = laptops.model and speed >= 2.80
    )
    group by maker
    having count(*) >= 2;

15.查询提供的电脑（PC or laptop）具有最高运行速度的供应商  
Find the maker(s) of the computer(PC or laptop) with the highest available speed

select ac.maker
    from (
            select maker, speed
            from products, pcs
            where products.model = pcs.model
            union
            select maker, speed
            from products, laptops
            where products.model = laptops.model
    ) ac
    where ac.speed = (
            select max(speed)
            from(
                    select maker, speed
                    from products, pcs
                    where products.model = pcs.model
                    union
                    select maker, speed
                    from products, laptops
                    where products.model = laptops.model
            )
    );