Page 12 - PYTHON-12

Table: CUSTOMER
Customer_id Cust_name City Grade Salesman_id
3002 Sunita Mishra New York 100 5001
3007 Sujata New York 200 5001
3005 Amir Suri California 200 5002
3008 Kiaansh Soni London 300 5002
3004 Chirag Puri Paris 300 5006
3009 Kanika Kapoor Berlin 100 5003
3003 Kamlesh Yadav Moscow 200 5007
3001 Sumit Aneja London 100 5005
(a) To display the details (from both the tables) of the salesmen whose commission is less than 0.15.
(b) To display the details of customers whose grade is in the range of 100 to 400.
(c) To increase the grade by 250 of all the customers who belong to New York.
(d) (i) To display the names and id of customers who belong to Moscow or London.
OR
(ii) To display the cartesian product of these two tables.
Ans. (a) Select * from SALESMAN s, CUSTOMER c where commission <0.15 and s.
Salesman_id=c. Salesman_id;
(b) Select * from CUSTOMER where Grade between 100 and 400;
(c) Update CUSTOMER set Grade = Grade + 250 where City='New York';
(d) (i) Select Cust_name, Customer_id from CUSTOMER where City IN ('Moscow',
'London');
OR
(ii) Select * from SALESMAN, CUSTOMER;
35. A table, named VEHICLE in MOTORSDB database, has the following structure: (4)

Field Type
Vehicle_id Int(8)
Vehicle_Name Varchar(20)
Price Float
Quantity Int(10)
Write the following Python function to perform the specified operation:
Show_data() to display and read only those records in which Quantity more than 100.
Note the following to establish connectivity between Python and MySQL:
Host: localhost
Username: root
Password: motors
Ans. import mysql. connector as mysql
def show_data():
con1=mysql.connect(host="localhost", user="root", password="motors",\
database ="MOTORSDB")
mycursor=con1.cursor()
print("Vehicles having quantity more than 100")
mycursor. execute ("Select * from vehicle where quantity > 100")
data=con1.commit()
for i in data:
print(i)
print()

Appendices A.43