Question

Consider the tables given below which are linked with each other and maintains referential integrity :

                        Table: SAP

SAPID	ItemCode	ItemName	ItemStoragelocation
S1001	1001	Receiver	W12-B3-R24
S1002	1002	Transponder	W13-B7-R87
S1003	1003	Battery Bank	W21-B1-R87
S1004	1004	Inverter	W21-B11-R2
S1005	1005	Genset	W22-B15-R16

                        Table: Store

StoreID	ItemCode	Storelocation	ReceivedDate
1201	1001	Hauz Khas	2016/05/20
1202	1002	Rajouri Garden	2016/06/14
1204	1003	Rohini	2016/05/06
1205	1004	Hauz Khaas	2016/07/15
1206	1005	Rajendra Place	2016/05/27

With reference to the above given tables, write commands in SQL for (i) and (ii) and output for (iii) below:

(0 To display the ItemCode, ItemName and ReceivedDate of all the items.

(ii) To display SAPID, ItemName, ItemStoragelocation of all the items whose Receiveddate is after 2^nd May 2016.

(iii) SELECT sa.SAPID,ItemName, STOREID FROM SAP sa, Store st WHERE sa.ItemCode=st.ItemCode AND Storelocation = "Hauz Khas".

Answer 1

(i) Select SAP.ItemCode, ItemName, ReceivedDate

From SAP sa, Store st

Where sa.ItemCode=st.ItemCode;

(ii) Select sa.SAPlD,ItemName,ItemStoragelocation

From SAP sa,Store st

Where sa.ItemCode=st.ItemCocle and Received date >" 2016 / 05 / 02";

SAPID	Item Name	Store ID
S1001	Receiver	1201
S1004	Inverter	1204