-- Example 3.7.1
select sum(dollars) as totaldollars from orders;
-- Example 3.7.2
select sum(qty) as TOTAL
from orders where pid = 'p03';
-- Example 3.7.3
select count(cid)
from customers;
select count(*)
from customers;
-- Example 3.7.4
select count(distinct city)
from customers;
select count(city)
from customers;
-- Example 3.7.5
select cid from customers
where discnt < (select max(discnt) from customers);
-- Example 3.7.6
select p.pid from products p
where 2 <= (select count(distinct cid) from orders
where pid = p.pid);
-- Example 3.7.7
-- Remove commment markers to use this one
-- insert into customers (cid, cname, city)
-- values ('c007', 'Windix', 'Dallas');
-- Example 3.7.8
select * from customers where discnt <= 10 or discnt > 10;
select * from customers where discnt is null;
-- Example 3.7.9
select avg(discnt) from customers;
-- Example 3.8.1
select pid, aid, sum(qty) as TOTAL from orders
group by pid, aid;
-- Example 3.8.2
select aname, a.aid, pname, p.pid, sum(qty)
from orders x, products p, agents a
where x.pid = p.pid and x.aid = a.aid and x.cid in ('c002', 'c003')
group by a.aid, a.aname, p.pid, p.pname;
-- Example 3.8.3
select pid, aid, sum(qty) as TOTAL from orders
group by pid, aid
having sum(qty) > 1000;
-- Example 3.8.4
select pid from orders
group by pid
having count(distinct cid) >= 2;
-- Example 3.9.1
select c.cname, c.cid, a.aname, a.aid, sum(o.dollars) as casales
from customers c, orders o, agents a
where c.cid = o.cid and o.aid = a.aid
group by c.cname, c.cid, a.aname, a.aid
having sum(o.dollars) >= 900.00
order by casales desc;
-- Example 3.9.2
select cid from customers
where discnt < (select max(discnt) from customers);
-- Example 3.9.3
select max(discnt) from customers;
select distinct discnt from customers c
where discnt >=all (select discnt from customers d
where d.cid <> c.cid);
-- Example 3.9.4
select * from customers where cname like 'A%';
-- Example 3.9.5
select cid from customers where cname not like '__\%%' escape '\';
-- Example 3.9.6
select cid from customers where cname like 'TIP\_%' escape '\';
-- Example 3.9.7
select cid from customers where cname like 'ab\\%' escape '\';
select cid from customers where cname like 'ab\%';