Question

ResuitsMessages cname city cid 2 Basics Dallas c002 12 3 Allied las c003 8 ACME Duluth c004 8 5 ACME Kyoto c006 0 aid aname city 1a01Smith New York 6 2 a02 Jones Newark 3 a03 Brown Tokyo 7 4a04 Gray New York6 5 a05 Otasi Duluth 5 6 a06 Smith Dallas 5

SQL

3) List aid of Agents that placed an order for customer c003 or customer c006.

4) List pid of Products that were Ordered for customer c003 or customer c006.

5) List the sum in dollars of all orders

Answer 1

Table names are not memtioned i am considering it as follows:

1. customertbl:

2. agenttbl:

ResuitsMessages cname city cid 2 Basics Dallas c002 12 3 Allied las c003 8 ACME Duluth c004 8 5 ACME Kyoto c006 0 aid aname city 1a01Smith New York 6 2 a02 Jones Newark 3 a03 Brown Tokyo 7 4a04 Gray New York6 5 a05 Otasi Duluth 5 6 a06 Smith Dallas 5

producttbl:

pid pname city quantity price 1 p01comb Dallas 111400 0.5 2 p02 brush Newark 203000 0.5 3 p03 razor Duluth 150600 1 p04 pen Duluth 125300 1 5 p05 pencil Dallas 221400 1 6 p06 folder Dallas 123100 2 7 p07 case Newark 100500 1

ordertbl:

ordno month cid aid pid ty dollars 1 1011 an c001 a01 p 1000 450 2 1012 an c00 a0 p1 1000 450 3 1019 feb c001 a02 p02 400 180 4 1017 feb c001 a06 p03 600 540 5 1018 feb c001 a03 p04 600 540 61023 mar c001 a04 p05 500 450 7 1022 mr c001 a05 p06 400 720 8 1025 apr c001 a05 p07 800 720 3

ordno month cid aid pid ty dollars 91013 jac002 a03 p3 1000 880 0 1026 mayc002 a05 p03 800 704 1015 jac003 a03 p05 1200 1104 12 1014 janc003 a03 p05 1200 1104 13 1021 febc004 a06 p01 1000 460 14 1016 jac006 a01 p01 1000 500 15 1020 feb c006 a03 p07 600 600 16 1024 mar c006 a06 p01 800 400

Question 3:

Answer:

SELECT a.*,o.cid
FROM ordertbl o
INNER JOIN agenttbl a
ON a.aid = o.aid
WHERE o.cid = 'c003' OR o.cid = 'c006';

Output:

aid a03 a01 a03 a06 aname city BrownTokyo Smith New York BrownTokyo Smith Dallas cid 7 c003 6 C006 7 c006 5 c006 prcnt

Question 4:

Answer:

SELECT p.id,p.panme,o.cid
FROM ordertbl o
INNER JOIN producttbl p
ON p.pid = o.pid
WHERE o.cid = 'c003' OR o.cid = 'c006';

Output:

pid pname cid p05 po5 peni c003 p01comb C006 P07 case pencil c003 C006 p01comb C006

Question 5:

Answer:

SELECT sum(dollars)
FROM ordertbl o;

Output:

9802