oracle-sql语句练习-蒲公英云

oracle-sql语句练习

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
        )
);