SQL> -- [3.11]
SQL> -- a. For each agent taking an order, list the product and the
SQL> -- total quantity ordered by all customers from that agent.
SQL>
SQL> SELECT aid, pid, SUM(qty) TotalQuantity
2 FROM orders
3 GROUP BY pid, aid
4 ORDER BY aid, pid;
AID PID TOTALQUANTITY
--- --- -------------
a01 p01 3000
a02 p02 400
a03 p03 1000
a03 p04 600
a03 p05 2400
a03 p07 600
a04 p05 500
a05 p03 800
a05 p06 400
a05 p07 800
a06 p01 1800
AID PID TOTALQUANTITY
--- --- -------------
a06 p03 600
12 rows selected.
SQL>
SQL> -- b. We say that a customer x orders a product y in an average quantity
SQL> -- A if A is for all rows with = x and
SQL> -- = y. Is it possible in a single SQL statement to retrieve
SQL> -- values of customers who order all the products that they
SQL> -- receive in average quantities by (by product) of at least 300?
SQL>
SQL>
SQL> SELECT cid
2 FROM (SELECT cid, MIN(avgqty) minavgqty
3 FROM (SELECT cid, pid, AVG(qty) avgqty --this table averages the
4 FROM orders --quantities of each product
5 GROUP BY cid, pid) --ordered, by customer
6 GROUP BY cid) --this table extracts the smallest average for each customer
7 WHERE minavgqty >= 300; --this table reports only customers with large minimum averages
CID
----
c001
c002
c003
c004
c006
SQL>
SQL> -- c. Get values of agents not taking orders from any customer in
SQL> -- Duluth for any product in Dallas.
SQL>
SQL> SELECT aid
2 FROM agents
3 MINUS
4 SELECT o.aid
5 FROM orders o, customers c, products p
6 WHERE o.cid = c.cid AND o.pid = p.pid AND
7 c.city = 'Duluth' AND
8 p.city = 'Dallas';
AID
---
a02
a03
SQL>
SQL> -- d. Get values of agents who order at least one common product
SQL> -- for each customer who is based in Duluth or Kyoto.
SQL>
SQL> -- This following query will be executed per-agent, returning only
SQL> -- the agents where it returns results:
SQL> -- ORDERS[pid,cid] DIVIDEBY
SQL> -- (CUSTOMERS WHERE city IN ('Duluth','Kyoto'))[cid]SQL>
SQL> SELECT aid FROM agents a
2 WHERE EXISTS(
3 SELECT pid FROM products p --product must be purchased by all custormers
4 WHERE NOT EXISTS ( --hence there must not exist a customer who
5 SELECT * FROM customers c --is in the city and does not order (through
6 WHERE city IN ('Duluth','Kyoto') AND7 NOT EXISTS(SELECT * FROM orders
8 WHERE cid = c.cid AND
9 pid = p.pid AND --the same agent)
10 aid = a.aid)));
no rows selected
SQL>
SQL> -- If we change the product for order# 1017, we can correctly
SQL> -- receive a result.
SQL>
SQL> UPDATE orders
2 SET pid = 'p01'
3 WHERE ordno ='1017';
1 row updated.
SQL>
SQL> SELECT aid FROM agents a
2 WHERE EXISTS(
3 SELECT pid FROM products p
4 WHERE NOT EXISTS (
5 SELECT * FROM customers c
6 WHERE city IN ('Duluth','Kyoto') AND7 NOT EXISTS(SELECT * FROM orders
8 WHERE cid = c.cid AND
9 pid = p.pid AND
10 aid = a.aid)));
AID
---
a06
SQL>
SQL> UPDATE orders
2 SET pid = 'p03'
3 WHERE ordno ='1017';
1 row updated.
ALTERNATIVE [modeled closely on example 3.5.3]
select distinct aid from ( --this table will contain common products ordered by same agent
select o.pid , o.aid, o.cid from orders o where
not exists (select * from customers c where c.city in ('Duluth', 'Koyto') and not exists(select * from orders x where c.cid = x.cid and x.aid=o.aid and
x.pid=o.pid))) ;
SQL>
SQL> -- e. Get values of customers who make orders only through agent
SQL> -- a03 or a05.
SQL>
SQL> SELECT cid
2 FROM customers c
3 WHERE NOT EXISTS(SELECT DISTINCT(aid)
4 FROM orders
5 WHERE cid = c.cid AND
6 aid NOT IN('a03','a05'));CID
----
c002
c003
SQL>
SQL> -- f. Get values of products that are ordered by all customers
SQL> -- in Dallas.
SQL>
SQL> -- How to accomplish this DIVIDE operation?
SQL> -- ORDERS[pid, cid] DIVIDEBY (CUSTOMERS WHERE city = 'Dallas')[cid]
SQL>
SQL> -- Translation of the following query:
SQL> -- Select the product if there is no customer in Dallas where no
SQL> -- order that links the customer to the product can be found.
SQL> -- Or easier yet:
SQL> -- Select the product if we can't find a single Dallas customer
SQL> -- that hasn't ordered that product.
SQL> SELECT pid FROM products p
2 WHERE
3 NOT EXISTS (SELECT * FROM customers c
4 WHERE city = 'Dallas' AND
5 NOT EXISTS(SELECT * FROM orders o
6 WHERE o.cid = c.cid AND
7 o.pid = p.pid));
no rows selected
SQL>
SQL> -- g. Find agents with the highest (percent commission),
SQL> -- using the max set function.
SQL>
SQL> SELECT aid
2 FROM agents
3 WHERE percent = (SELECT MAX(percent) FROM agents);
AID
---
a03
SQL>
SQL> -- h. In the table, delete the row with the agent named Gray,
SQL> -- print out the resulting table in full, then put Gray back, using
SQL> -- the Insert statement.
SQL>
SQL> DELETE FROM agents
2 WHERE aname = 'Gray';
1 row deleted.
SQL>
SQL> INSERT INTO agents (aid, aname, city, percent) VALUES ('a04','Gray','New York',6);1 row created.
SQL>
SQL> -- i. Use the Update statement to change Gray's to 11. Then
SQL> -- change it back.
SQL>
SQL> UPDATE agents
2 SET percent = 11
3 WHERE aname = 'Gray';
1 row updated.
SQL>
SQL> UPDATE agents
2 SET percent = 6
3 WHERE aname = 'Gray';
1 row updated.
SQL>
SQL> -- j. Use a single Update statement to raise the prices of all products
SQL> -- warehoused in Duluth or Dallas by 10%. Then restore the original
SQL> -- values by rerunning the procedure that you originally used to
SQL> -- create and load the products table.
SQL>
SQL> SELECT * FROM products;
PID PNAME CITY QUANTITY PRICE
--- ------------- -------------------- ---------- ----------
p01 comb Dallas 111400 .5
p02 brush Newark 203000 .5
p03 razor Duluth 150600 1
p04 pen Duluth 125300 1
p05 pencil Dallas 221400 1
p06 folder Dallas 123100 2
p07 case Newark 100500 1
7 rows selected.
SQL>
SQL> UPDATE products
2 SET price = price * 1.1
3 WHERE city IN ('Duluth','Dallas');5 rows updated.
SQL>
SQL> SELECT * FROM products;
PID PNAME CITY QUANTITY PRICE
--- ------------- -------------------- ---------- ----------
p01 comb Dallas 111400 .55
p02 brush Newark 203000 .5
p03 razor Duluth 150600 1.1
p04 pen Duluth 125300 1.1
p05 pencil Dallas 221400 1.1
p06 folder Dallas 123100 2.2
p07 case Newark 100500 1
7 rows selected.
SQL> -- Out of band process: using sql*loader to reload the table.
SQL> SELECT * FROM products;
PID PNAME CITY QUANTITY PRICE
--- ------------- -------------------- ---------- ----------
p01 comb Dallas 111400 .5
p02 brush Newark 203000 .5
p03 razor Duluth 150600 1
p04 pen Duluth 125300 1
p05 pencil Dallas 221400 1
p06 folder Dallas 123100 2
p07 case Newark 100500 1
7 rows selected.
SQL>
SQL> -- k. Write an SQL query to retrieve values for customers who
SQL> -- place at least one order, but only through agent a04. On the
SQL> -- same line with each , your query should list the total
SQL> -- dollar amount of orders placed.
SQL>
SQL> SELECT c.cid, SUM(dollars) TotalAmt
2 FROM customers c, orders o
3 WHERE c.cid = o.cid AND
4 NOT EXISTS(SELECT cid
5 FROM orders
6 WHERE cid = c.cid AND aid <> 'a04')
7 GROUP BY c.cid;
no rows selected
SQL>
SQL> -- Note: if agent a03 was used, this query would return results.
SQL>
SQL> -- l. Write an SQL query to get and values of agents
SQL> -- who take orders from all customers who live in Duluth. The
SQL> -- values should be reported in order by decreasing . (Note
SQL> -- that if is not retrieved in the select list, we cannot
SQL> -- order by these values.)
SQL>
SQL> -- Select the agent if we cannot find a customer in Duluth that hasn't
SQL> -- placed an order with said agent.
SQL>
SQL> SELECT aid, percent FROM agents a
2 WHERE NOT EXISTS(SELECT * FROM customers c
3 WHERE city = 'Duluth' AND
4 NOT EXISTS(SELECT * FROM orders o
5 WHERE o.cid = c.cid AND
6 o.aid = a.aid))
7 ORDER BY percent DESC;
AID PERCENT
--- ----------
a06 5
SQL>
SQL> -- m. Write an SQL query to get values of products ordered by at
SQL> -- least one customer who lives in the same city as the agent
SQL> -- taking the order.
SQL>
SQL> SELECT DISTINCT o.pid
2 FROM orders o, customers c, agents a
3 WHERE o.cid = c.cid AND o.aid = a.aid AND
4 c.city = a.city;
PID
---
p06
p07
SQL>
SQL> -- [3.12]
SQL> -- a. In the following query, show how the answer is built up; that is,
SQL> -- show all Subquery sets of elements created, then write out the
SQL> -- final answer you would expect.
SQL>
SQL> -- select city from customers where discnt >=all
SQL> -- (select discnt from customers where city = 'Duluth')
SQL> -- union select city from agents where percent >any
SQL> -- (select percent from agents where city like 'N%');
SQL>
SQL> -- First, the database retrieves the discnt field for all customer
SQL> -- tuples where the city is Duluth.
SQL> SELECT discnt FROM customers WHERE city = 'Duluth';
DISCNT
----------
10
8
SQL>
SQL> -- Now, the database much choose the city for the customers
SQL> -- that have at least this discnt value (10). There may be a
SQL> -- customer with a higher discount, but not from Duluth.
SQL> -- SELECT city FROM customers WHERE discnt >= ( ... )
SQL> -- becomes equivalent to
SQL> -- SELECT city FROM customers WHERE discnt >= 10;
SQL> SELECT city FROM customers WHERE discnt >= 10;
CITY
--------------------
Duluth
Dallas
SQL>
SQL> -- This result is UNION'd with the second part, which we will now
SQL> -- calculate.
SQL> SELECT percent FROM agents WHERE city LIKE 'N%';
PERCENT
----------
6
6
6
SQL> -- Apparently all the agents in a city starting with the letter 'N'
SQL> -- have a percent commission of 6.
SQL> -- This makes
SQL> -- SELECT city FROM agents WHERE percent >any (...)
SQL> -- equivalent to
SQL> -- SELECT city FROM agents WHERE percent > 6;
SQL> SELECT city FROM agents WHERE percent > 6;
CITY
--------------------
Tokyo
SQL>
SQL> -- Therefore we would expect our final query result to be:
SQL> -- CITY
SQL> -- ====
SQL> -- Duluth
SQL> -- Dallas
SQL> -- Tokyo
SQL>
SQL> -- Let's see.
SQL> SELECT city FROM customers WHERE discnt >=ALL
2 (SELECT discnt FROM customers WHERE city = 'Duluth')
3 UNION SELECT city FROM agents WHERE percent >any
4 (SELECT percent FROM agents WHERE city LIKE 'N%');
CITY
--------------------
Dallas
Duluth
Tokyo
SQL>
SQL> -- We were correct!
SQL>
SQL> -- b. In the following query, explain in words how SQL arrives at the
SQL> -- answer, then write out the final answer you would expect.
SQL>
SQL> -- select cid, pid, sum(qty) from orders
SQL> -- where dollars >= 500.00
SQL> -- group by cid, pid having count(qty) > 1;
SQL>
SQL> -- First, the orders table is scanned for all orders where the dollar
SQL> -- amount is greater than or equal to 500 dollars.
SQL> -- Next, the records are grouped into (cid,pid) pairs, and for each pair
SQL> -- the sum of the qty's of all the orders having that (cid,pid) pair
SQL> -- is calculated, as well as the number of orders that have the
SQL> -- (cid,pid) pair. If that (cid,pid) pair does not have at least 2
SQL> -- distinct orders, it is discarded. Finally, the number of orders
SQL> -- is discarded, but the sum of the qty remains.
SQL>
SQL> -- Let's examine this logic with the actual recordset.
SQL> SELECT cid, pid, qty, dollars FROM orders ORDER BY cid,pid;
CID PID QTY DOLLARS
---- --- ---------- ----------
c001 p01 1000 450
c001 p01 1000 450
c001 p02 400 180
c001 p03 600 540
c001 p04 600 540
c001 p05 500 450
c001 p06 400 720
c001 p07 800 720
c002 p03 1000 880
c002 p03 800 704
c003 p05 1200 1104
CID PID QTY DOLLARS
---- --- ---------- ----------
c003 p05 1200 1104
c004 p01 1000 460
c006 p01 1000 500
c006 p01 800 400
c006 p07 600 600
16 rows selected.
SQL>
SQL> -- Here we can see that the many of the tuples will be discarded because
SQL> -- their amount is not >=500. Now, the two aggregates can be calculated
SQL> -- in any order, but it is easier for us to first strip out the (cid,pid)
SQL> -- pairs that only have one order, then we only have to calculate the
SQL> -- total quantity for the remaining. As we can see, many pairs have
SQL> -- only a single order: ('c001','p03'),('c001','p04'),('c001','p06'),SQL> -- ('c001','p07'),('c006','p01'),('c006','p07'); those all can beSQL> -- removed.
SQL> -- Now, we are left with this:
SQL> -- CID PID QTY DOLLARS
SQL> -- ---- --- ---------- ----------
SQL> -- c002 p03 1000 880
SQL> -- c002 p03 800 704
SQL> -- c003 p05 1200 1104
SQL> -- c003 p05 1200 1104
SQL>
SQL> -- We can discard the dollars column and then collapse each (cid,pid)
SQL> -- pair and sum up the qty.
SQL> -- CID PID SUM(QTY)
SQL> -- ==== === ========
SQL> -- c002 p03 1800
SQL> -- c003 p05 2400
SQL>
SQL> -- Let's see if we're correct!
SQL> SELECT cid, pid, SUM(qty)
2 FROM orders
3 WHERE dollars >= 500.00
4 GROUP BY cid, pid
5 HAVING COUNT(qty) > 1;
CID PID SUM(QTY)
---- --- ----------
c002 p03 1800
c003 p05 2400
SQL>
SQL> -- Indeed we are.
SQL>
SQL> -- [3.15]
SQL> -- Recall the definition of outer join from Section 2.10. Consider
SQL> -- the (ordinary) join query:
SQL>
SQL> -- select a.aname, a.aid, sum(x.dollars)
SQL> -- from agents a, orders x
SQL> -- where a.aid = x.aid group by a.aid, a.aname;
SQL>
SQL> -- a. Rewrite this query in Basic SQL to implement an outer join (this
SQL> -- will require a union of three Subqueries). An agent and
SQL> -- should appear even if the agent has no orders, and
SQL> -- should appear (grouped by ) even if there is no
SQL> -- corresponding in listed. Supply constant values,
SQL> -- nulls for , and for sum() when no proper value
SQL> -- exists in a column (or blanks and zeros, if you cannot use a
SQL> -- null constant on your system).
SQL>
SQL> SELECT a.aname, a.aid, SUM(o.dollars)
2 FROM agents a, orders o
3 WHERE a.aid = o.aid
4 GROUP BY a.aid, a.aname
5 UNION
6 SELECT aname, aid, 0
7 FROM agents
8 WHERE aid NOT IN (SELECT aid FROM orders)
9 UNION
10 SELECT NULL, aid, SUM(dollars)
11 FROM orders
12 WHERE aid NOT IN (SELECT aid FROM agents)
13 GROUP BY aid;
ANAME AID SUM(O.DOLLARS)
------------- --- --------------
Brown a03 4228
Gray a04 450
Jones a02 180
Otasi a05 2144
Smith a01 1400
Smith a06 1400
6 rows selected.
SQL>
SQL> -- b. To test your answer to part (a), add a new row to ,
SQL> -- (1027,'jun','c001','a07','p01',1000,450.00), and a new row to
SQL> -- , ('a08','Beowulf','London',8), and then execute theSQL> -- query. When you are satisfied, delete the two rows you just
SQL> -- added.
SQL>
SQL> INSERT INTO orders (ordno,month,cid,aid,pid,qty,dollars)
2 VALUES (1027,'jun','c001','a07','p01',1000,450.00);
1 row created.
SQL> INSERT INTO agents (aid,aname,city,percent)
2 VALUES ('a08','Beowulf','London',8);1 row created.
SQL>
SQL>
SQL> SELECT a.aname, a.aid, SUM(o.dollars)
2 FROM agents a, orders o
3 WHERE a.aid = o.aid
4 GROUP BY a.aid, a.aname
5 UNION
6 SELECT aname, aid, 0
7 FROM agents
8 WHERE aid NOT IN (SELECT aid FROM orders)
9 UNION
10 SELECT NULL, aid, SUM(dollars)
11 FROM orders
12 WHERE aid NOT IN (SELECT aid FROM agents)
13 GROUP BY aid;
ANAME AID SUM(O.DOLLARS)
------------- --- --------------
Beowulf a08 0
Brown a03 4228
Gray a04 450
Jones a02 180
Otasi a05 2144
Smith a01 1400
Smith a06 1400
a07 450
8 rows selected.
SQL>
SQL> -- Indeed, the query works as expected.
SQL>
SQL> DELETE FROM orders WHERE ordno = 1027;
1 row deleted.
SQL> DELETE FROM agents WHERE aid = 'a08';
1 row deleted.
SQL>
SQL> spool off