Consider the following tables Item and Customer and answer the questions that follow

Q. Consider the following tables Item and Customer and answer the questions that follow:

Table: Item

 
 
	 
Item_ID
 
	 
ItemName
 
	 
Manufacturer
 
	 
Price
 
 
 
	 
PC01
 
	 
Personal Computer
 
	 
HCL India
 
	 
42000
 
 
 
	 
LC05
 
	 
Laptop
 
	 
HP USA
 
	 
55000
 
 
 
	 
PC03
 
	 
Personal Computer
 
	 
Dell USA
 
	 
32000
 
 
 
	 
PC06
 
	 
Personal Computer
 
	 
Zenith USA
 
	 
37000
 
 
 
	 
LC03
 
	 
Laptop
 
	 
Dell USA
 
	 
57000
 
 
 

Table: Customer

 
 
 
	 
Item_ID
 
	 
CustomerName
 
	 
City
 
 
 
	 
LC03
 
	 
N Roy
 
	 
Delhi
 
 
 
	 
PC03
 
	 
H Singh
 
	 
Mumbai
 
 
 
	 
PC06
 
	 
R Pandey
 
	 
Delhi
 
 
 
	 
LC03
 
	 
C Sharma
 
	 
Chennai
 
 
 
	 
PC01
 
	 
K Agarwal
 
	 
Bengaluru
 
 

 

Assume that the Pandas has been imported as pd.

(a) Create a data frame called dfl for table item.

(b) Create a data frame called dfC for table Customer.

(c) Perform the default join operation on item_ID using two data-frames: dfl and dfC.

(d) Perform the left join operation on item_ID using two data-frames: dfl and dfC.

(e) Perform the right join operation on Item_ID using two data frames: dfi and dfC.

(f) Perform the default operation on Item_ID using two data frames: dfl and dfc with the left index as true.

(g) Perform the outer join operation on item_ID using two data frames: dfl and dfC.

(h) Create a new data frame dfN using data frames: dfl and dfC. The new dataframe data will hold both left index and right index true values.

(i) Arrange the data frame dfN in descending order of Price.

(j) Arrange the Data Frame dfN in descending order of City and Price.

Answer :-

(a)

import mysql.connector as a

db = a.connect(user = 'root', passwd = '0000000000', host = 'localhost', database = 'Path_Walla')
import pandas as pd
dfl = pd.read_sql(f'select * from item', db)

print(dfl)

(b)

import mysql.connector as a

db = a.connect(user = 'root', passwd = '0000000000', host = 'localhost', database = 'Path_Walla')
import pandas as pd
dfC = pd.read_sql(f'select * from Customer', db)

print(dfC)

(c) dfi.set_index('item_iD').join(dfc.set_index('key'))

(i) dfn.sort_values(by=['Price'],ascending=False)

(j)  dfn.sort_values(by=['Price','city'],ascending=False)