1. Query all PC information with speed greater than 2.8

select *
from pcs
where speed > 2.8

2. Query the purchase record information of purchase model 1007

select *
from sales
where model = 1007

3. Statistics on the number of purchase records from December to 20, 2013 (count)

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

4. Statistics of the total number of purchases (sum) from December 20, 2013

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

5. query hard disk size on more than two kinds of PC computer hard disk size.

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

6. PC models with a query speed of at least 3.00 or more

select model
from pcs
where speed >= 3.00;

7. Find out which supplier supplies laptops hard disk at least 100GB or more.
(1) Equivalent connection

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) internal connection

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) Uncorrelated subqueries

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

(6) Relevant sub-queries

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

8. Inquire the product number and price of all products supplied by supplier 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. Find model numbers for all color printers

select model
from printers
where color = 'TRUE';

10. Find vendor information that provides laptops but not PCs

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

11. query the PC computer number with the same running speed and memory.
Each pc models pair appears only once, i. e. (i, j) appears, then (j, i) does not appear.
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. inquiring about the sale of three different types of PC computers suppliers
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. Query vendors that provide at least three PC speeds
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. The query provides a vendor with at least 2.80 PC or laptop speed and two or more products.
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. inquiries provided by computers (PC or laptop) have the highest speed of suppliers.
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
        )
);