Question 1 – EMP table (mix of select + update)
Table EMP in database OFFICE(EmpID, EmpName, Salary, Dept).
python
import [Link] as mysql
myconn = [Link](_________________________________) #1
cursor = [Link]()
[Link](_________________________________) #2
data = ____________________________________________ #3
for rec in data:
print(rec)
_____________________________________________ #4
[Link]()
a. In #1, write the parameters and values to connect to database OFFICE using user “root” at host
“localhost”.
b. In #2, write a query to fetch EmpName and Salary from EMP for employees of Dept = 'HR', in
descending order of Salary.
c. In #3, write code to fetch only the first two records from the result set.
d. Suppose instead of #2 you want to increase Salary of all HR employees by 5000. Write the UPDATE
statement for #2 and the appropriate statement for #4 so that the change becomes permanent.
Question 2 – BOOK table (insert + limited fetch)
Table BOOK in database LIBRARY(BookID, Title, Author, Price).
python
import [Link] as mysql
con = [Link](_________________________________) #1
cur = [Link]()
[Link](_________________________________) #2
row = ____________________________________________ #3
print(row)
_____________________________________________ #4
[Link]()
a. Complete #1 to connect to database LIBRARY on localhost with user “root” and password
“python”.
b. For #2, write an INSERT statement to add one new book record in BOOK.
c. Assume that before the INSERT, #2 was a SELECT * FROM BOOK ORDER BY Price ASC. What should
be written in #3 to fetch a single record at a time from the result set?
d. For the actual INSERT in #2, write the statement for #4 to save the new record permanently in
BOOK.
Question 3 – CUSTOMER table (where + fetchall)
Table CUSTOMER in database SALES(CustID, CustName, City, CreditLimit).
python
import [Link] as mysql
conn = [Link](_________________________________) #1
curs = [Link]()
[Link](_________________________________) #2
result = ____________________________________________ #3
for row in result:
print(row)
_____________________________________________ #4
[Link]()
a. Complete #1 with the parameter names and values needed to connect to database SALES.
b. In #2, write a SELECT statement to fetch CustName and CreditLimit of customers living in 'DELHI'
with CreditLimit greater than 75000.
c. In #3, write code to fetch all records from the result set at once.
d. If instead of SELECT you wish to DELETE customers from 'DELHI' whose CreditLimit is less than
20000, write the DELETE for #2 and the corresponding statement for #4 to make the deletion
permanent.
Question 4 – COURSE table (ordering + fetchmany)
Table COURSE in database COLLEGE(CourseID, CourseName, Fees, Duration).
python
import [Link] as mysql
db = [Link](_________________________________) #1
cr = [Link]()
[Link](_________________________________) #2
records = ____________________________________________ #3
for rec in records:
print(rec)
_____________________________________________ #4
[Link]()
a. Fill #1 to connect to database COLLEGE with suitable arguments.
b. Write #2 to select CourseName and Fees from COURSE where Duration = '6 Months', ordered by
CourseName in ascending order.
c. Write #3 so that only the next three records are fetched from the result set in one call.
d. Suppose you want to change the Fees of course 'BCA' to 55000 instead of the SELECT in #2. Write
the UPDATE statement for #2 and the statement for #4 to save this change permanently.
Question 5 – FLIGHT table (different column set + commit)
Table FLIGHT in database TRAVEL(FlightNo, Source, Destination, Fare).
python
import [Link] as mysql
connection = [Link](_________________________________) #1
cur = [Link]()
[Link](_________________________________) #2
data = ____________________________________________ #3
for rec in data:
print(rec)
_____________________________________________ #4
[Link]()
a. Complete #1 to connect to database TRAVEL on localhost using appropriate arguments.
b. In #2, write a SELECT statement to fetch FlightNo, Source and Destination for flights whose Fare is
between 4000 and 8000, ordered by Fare in ascending order.
c. In #3, write a statement to fetch all remaining records from the result set.
d. If instead of the SELECT you want to INSERT a new record into FLIGHT, write the INSERT statement
for #2 and the statement for #4 that ensures the insertion is permanently stored.