Question

Using the CAP database handout. Write the relational algebra statement to answer the following question. Then write the SQL statement to answer the same question.

Get aids of agents who place individual orders in dollar value greater than $500 for customers living in Kyoto.

Chanter 2 The Relational Model O'Neil, P., & O'Neil, E (200). Database prineiples programming performance (2d ed.). San Franciscu: Morgan Kaufmann. PRODUCTS pd pname city p0 combl111400C.so p02brushNewark 2030000.S0 pzorDuluth 150600 1.00 CUSTOMERS Cname city discat uanity price c00TipTop Dulurh10.00 c2Basics Dallas 12.00 0Alied Dallas8.00 cACME Du8.00 c006 ACME Kyoto0.00 Duluth 125300 1.00 popeDalilas 2214001.00 p0s folder Dallas 12310 2.00 pO7caseNewark 100500 1.00 GENTS d ananecity percen ORDERS ordnot cid 10c01p011000450.00 1012 jan |こ001 | a011 poll 1000 450.11 1019 fcb4180.00 1017 eb cO0a06p600540.00 1018 feb c00a03 P600540.00 1023marc0p S0045.00 1022mar0a720.00 Smith New York aidpi dollars a2Jones Newark aBrTokyo a0 Gray New York a Ocas Duluth 6 Smith Dallas 1025720.00 1013 | Ian | c002 | a03 | p03 | 1000 | 880.00 1026mayp00 704.00 101S ja c3 512001104.00 1014ja00a03pOS12001104.00 1021 eb c004 ap1000460.00 1016jac06apOl1000500.00 1020 feb c006p00 600.00 1024 ma c0p80400.00 Figure 2.2 The CAP Database Content at a Given Moment)

Answer 1

Dear Student ,

As per the requirement submitted above , kindly find the below solution.

Question :

SQL Query :

Select aid from orders,customers where orders.cid=customers.cid and dollars > 500 and city=’Kyoto’ ;

Explanation :

• Above query will look for orders above 500 for customer living in Kyoto.
• Then it will return cid and match that customer id and return agent aid as aids.

NOTE : PLEASE FEEL FREE TO PROVIDE FEEDBACK ABOUT THE SOLUTION.