PYTHON PROGRAMS

1. Write a Python program that includes two functions: storeData() and countNT(). These functions are
designed to manage user details in a Text file named lesson.txt. [8]
a. storeUser():
i. This function prompts the user to input some data.
ii. It then stores that data in lesson.txt.
b. checkUser():
i. This function reads the data from lesson.txt.
ii. After fetching data, counts the occurrence of ‘A’, ‘a’, ‘n’ and ‘N’ letters in the file.

2. Consider a scenario where you have a list of products and their corresponding prices stored in a list.
Write a program with separate user-defined functions to perform the following operations: [8]
a. createList: Use this function to insert data in list in the given format.
For example: productPrices = [[‘Laptop’,10],[‘Smartphone’,12],[‘Camera’,8]]
b. storeCSV: Use the list created by the createList function and store that data in a csv file
named electronics.csv.
3. Write a Python program that creates a binary file to store student information, including names,
classes, and roll numbers. Additionally, the program should be capable of displaying all the records
inserted into the file. Create the following functions to perform these operations: [8]
a. storeData(): To store student information
b. showData(): To show details of all the students

4. Write a menu driven program to implement stack using list and perform following functions: [8]
 Push
 Pop
 Display
 Exit
5. Write a menu drive program to perform the following operations into a csv file students.csv. [8]
 Add record
 Display records
 Search record
 Exit
The structure of file content is: [s_id, name, class, percentage]
6. a) Write a python program to read a text file and count the number of vowels and consonants
available in it.
[4]
(first make a file in desktop with any name but txt extension, write some content in it, then use
that same file for this program make sure the path is correct in your code else you will get
errors)

b) Write a program to input a list and interchange first with last, second with second last …. Print the
list in reverse order. [4]
 MySQL QUERIES
1. Consider the tables books and issue write SQL command for given statements: [4]

a. Display the book id, book name from books if second letter of author name is ‘A’.
SELECT BID,BNAME FROM BOOKS WHERE AUNAME LIKE ‘_A%’;
b. Increase the price of all computer books by 70.
UPDATE BOOKS SET PRICE=PRICE+70;
c. Display the book id, book name and quantity issued for all books which have been issued.
SELECT A.BID,A.BNAME,B.QTY_ISSUED FROM BOOKS A, ISSUED B WHERE
A.BID=B.BID;
d. Display the book id, book name, author name, quantity for all books if price is in range of 200 to
400.
SELECT BID, BNAME, AUNAME, QTY FROM BOOKS WHERE QTY BETWEEN 200 AND
400;

2. Observe the following tables customers and orders and write queries given below: [4]
Table - Customer
CustomerI CustomerName City MobileNo
D
C111 Abhishek Ahmedabad 9999999999
C132 Bhavik Anand 7799779977
C135 Chandani Baroda 8856895485
C145 Dhara Ahmedabad 7456879652
C121 Divya Anand 9015123569
Table - Order
OrderID OrderDate OrderAmt CustomerID
O111 2022-04- 1500 C111
15
O112 2022-05- 1800 C121
20
O113 2022-05- 1000 C199
31
O131 2022-06- 1400 C135
12

a. Display CustomerID, CustomerName for customers belonging to Ahmedabad.

SELECT CustomerID, CustomerName FROM CUSTOMER WHERE CITY= ‘Ahmedabad’;
b. Display the order details in descending order of amount.
SELECT * FROM ORDER ORDER BY DESC;
c. Display OrderId, Orderdate , customername and mobileno of customers whose name ends with ‘k’
and name contains letter ‘h’.
 SELECT A.OrderID, A.Orderdate , B.CustomerName, B.MobileNo FROM ORDER A,
CUSTOMER B WHERE B.CustomerName LIKE ‘%h%k’ AND A.OrderID=B.OrderID;
d. Display CustomerID, CustomerName and OrderDate of the customers who placed any orders.
SELECT B.CustomerID, B.CustomerName, A.OrderDate FROM ORDER A, CUSTOMER B
WHERE A.OrderID=B.OrderID;
3. Consider the given tables stationary and distributor, write queries given below. [4]
Table – Stationary
S_ID StationaryName Company Price
DP01 Dot Pen Cello 10
PL02 Pencil DOMS 5
ER05 Erasor DOMS 5
PL01 Pencil Nataraj 4
GP0 Gel Pen Rotomac 8
1
Table: distributor
D_I Distributor City S_ID
D
1 Excellent Stationary Surat PL01
2 Vidhya Stationary Baroda GP02
3 Student Book Shop Surat DP01
4 Pupils Corner Surat Pl02
5 Abhyass Stationary Baroda DP01
a. Display unique cities from distributor
SELECT DISTINCT CITY FROM DISTRIBUTOR;
b. Display maximum and minimum price for each company
SELECT COMPANY, MAX(PRICE) AS MAX_PRICE, MIN(PRICE) AS MIN_PRICE FROM
STATIONARY GROUP BY COMPANY;
c. Display all stationary name, company, distributor along with city
SELECT S.STATIONARYNAME, S.COMPANY, D.DISTRIBUTOR, D.CITY FROM
STATIONARY S, DISTRIBUTOR D WHERE S.S_ID = D.S_ID;
d. Display the stationary details and distributor details which distributor name contains
stationary.
SELECT S.*, D.* FROM STATIONARY S, DISTRIBUTOR D WHERE S.S_ID = D.S_ID
AND D.Distributor LIKE '%Stationary%';

4. Observe the tables given below and write suitable SQL queries for the given questions: [4]
Table: Students
StudentID FirstName LastName Age CourseID
1 John Doe 22 103
2 Jane Smith 25 103
3 Mark Johnson 21 101
4 Emily Davis 23 105
5 Chris Brown 20 101
Table: Courses
CourseID CourseName Instructor CreditHours
101 Math Dr. White 3
102 English Prof. Green 4
103 Science Dr. Turner 3
104 History Prof. Lee 3
105 Computer Science Dr. Smith 4

a. Write an SQL query to retrieve the first and last names of all students.
SELECT FirstName, LastName FROM Students;
b. Write an SQL query to fetch the names and ages of students who are older than 20, ordered
by age in descending order.
SELECT FirstName, LastName, Age FROM Students WHERE Age > 20 ORDER BY Age
DESC;
c. Write an SQL query to display the course name and instructor for all courses along with the
first and last names of students who are enrolled in those courses.
SELECT C.CourseName, C.Instructor, S.FirstName, S.LastName FROM Courses C,
Students S WHERE C.CourseID = S.CourseID;
d. Write an SQL query to find the average credit hours of all courses.
 SELECT AVG(CreditHours) AS AverageCreditHours FROM Courses;

5. Observe the tables given below and write suitable SQL queries for the given questions: [4]
Table: Employees
EmployeeID FirstName LastName Department ProjectID Salary
1 Alice Johnson HR 101 50000
2 Bob Smith IT 105 60000
3 Carol Davis Finance 103 55000
4 David Brown Marketing 104 52000
5 Emma Turner IT 102 65000
Table: Projects
ProjectID ProjectName Department StartDate EndDate
101 HR System HR 2023-01-15 2023-03-15
102 Website Redesign IT 2023-02-01 2023-05-01
103 Financial Audit Finance 2023-03-10 2023-04-30
104 Product Launch Marketing 2023-04-05 2023-06-30
105 Database Upgrade IT 2023-01-20 2023-03-20

a. Write an SQL query to find project names under IT department.

SELECT ProjectName FROM Projects WHERE Department = 'IT';
b. Write an SQL query to fetch the names and salaries of employees in the IT department who
earn more than 60000.
SELECT FirstName, LastName, Salary FROM Employees WHERE Department = 'IT'
AND Salary > 60000;
c. Write an SQL query to display the project names and employee names working under
various projects.
SELECT P.ProjectName, E.FirstName, E.LastName FROM Employees E, Projects P
WHERE E.ProjectID = P.ProjectID;
d. Write an SQL query to find the maximum and minimum salary from Employees.
SELECT MAX(Salary) AS MaxSalary, MIN(Salary) AS MinSalary FROM Employees;

6. Consider the following tables and write queries given below: [4]
Table: Teams
TEAMID TEAM FRANCHISE
T001 Bengal Warriors Birthright Games & Entertainment Private Ltd.
T002 Bengluru Bulls Kosmik Global Media
T003 Dabang Delhi Radha Kapoor Khanna
T004 Gujrata Fortune Giants Gautam Adani
T005 Haryana Steelers JSW Group
T006 Patna Pirates KVS Energy & Sports Ltd.
T007 Jaipur Pink Panthers Abhishek Bachchan & GS Entertainment
Telugu Titans Greenko Group, NED Group, & Core Green
T008
Group

Table: PROKABADDI_STATS
Player_ID Player_Name Matches Raids TeamID
PK001 Pawan Sehrawat 5 43 T008
PK002 Sachin Tanwar 5 43 T006
PK003 Bharat 6 43 T002
PK004 Naveen Kumar 4 42 T003
PK005 Maninder Singh 5 37 T001
PK006 Sonu Narval 5 34 T005

a. Display Player Name, Raids and Team Names of all players whose raids are more than 40.
SELECT PS.Player_Name, PS.Raids, T.TEAM FROM PROKABADDI_STATS PS, Teams
T WHERE PS.Raids > 40 AND PS.TeamID = T.TEAMID;
b. Display the details of players in the descending order of matched played.
SELECT Player_ID, Player_Name, Matches, Raids, TeamID FROM
PROKABADDI_STATS ORDER BY Matches DESC;
c. Display the average raids.
SELECT AVG(Raids) AS AverageRaids FROM PROKABADDI_STATS;
d. Display the details of teams whose name ends with ‘s’
SELECT * FROM Teams WHERE TEAM LIKE '%s';