SET A

1. Write a Python Program read a text file line by line and display each word separated by a #.

2. Write the code to add only the palindrome words to the stack, palin. Pop and display the contents
of the stack. Display message as “Stack Empty” once all the elements are removed from the
stack.
 SQL QUERIES:
a) To display the C_ID, Supplier name, Supplier Address, Consumer Name and Consumer Address
for every Consumer.
SELECT C_ID,SUPPLIERNAME,SUPPLIERADDRESS,CNAME,CADDRESS FROM
SUPPLIER S,CONSUMER C WHERE [Link]=[Link];
b) To display Consumer details in ascending order of CName
SELECT * FROM CONSUMER ORDER BY CNAME;
c) To display number of Consumers from each city.
SELECT COUNT(*),CCITY FROM CONSUMER GROUP BY CCITY;
d) To display number of supplier name for each city whose count is greater than one.
SELECT COUNT(*),SUPPLIERCITY FROM SUPPLIER GROUP BY SUPPLIERCITY
HAVING COUNT(*)>1;
 SET-B
1. . Write a python program to read text file "[Link]" and display the number of
vowels/consonants/lower case/ upper case characters present in that text file.

a) To display fname, cname, fees from faculty and courses for the faculty whose salary is more than
8000.
SELECT FNAME,CNAME,FEES FROM FACULTY F,COURSES C WHERE
F.F_ID=[Link] AND SALARY >8000;
b) To display the Fname, Lname whose Hiring date is more than 01-01-2000.
SELECT FNAME,LNAME FROM FACULTY WHERE HIRE_DATE > “2000-01-01”;
c) To display Fname whose first letter starts with “R”.
SELECT FNAME FROM FACULTY WHERE FNAME LIKE “R%”;
d) Select COUNT(DISTINCT F_ID) from COURSES;
Count(distinct F_ID)
4
e) Select MIN(Salary) from FACULTY,COURSES where COURSES.F_ID = FACULTY.F_ID;
Min(salary)
10000

SET C
A binary file “[Link] has structure [Bookno,Bname,Author,Price].Write a user
defined function Createfile() that will create a file and add the required details.
Write a function CountRec(book) in python which accepts a book as parameters
and display the details of related books.
a) Create database and create table with the above given database.
 CREATE DATABASE STOCK;
CREATE TABLE PRODUCT(PCODE INT,PTYPE VARCHAR(30),PNAME
VARCHAR(30),PRICE INT,QTY INT);
b) Insert all the data’s given in the table
INSERT INTO PRODUCT VALUES(9007,”NETWORK”,”SWITCH”,55000,7);
c) Now Mr. Singh wants to increment the price of all product by 10 %.Write SQL query to do
the same.
UPDATE PRODUCT SET PRICE=PRICE+PRICE*10/100;
d) Mr. Kumar wants to display the structure of the table Product i.e., name of the attributes and
its data [Link] SQL query to display the same
DESC PRODUCT;
SET D
1. Create a function odd_lines() to read the file [Link] and display only the odd
lines. Count the number of total number of lines.

2. Create a function total_digits() to read the file [Link] and display the sum of
digits with the file.
 a) To display first name,lastname,address and city of all employees living in paris.
SELECT FIRSTNAME,LASTNAME,ADDRESS,CITY FROM EMPLOYEES
WHERE CITY= “PARIS”;
b) To display first name,last name and total salary of all managers from table
employee and table empsalary where total salary is calculated as
salary+benefits.
SELECT FIRSTNAME,LASTNAME,SALARY+BENEFITS AS TOTSAL FROM
EMPLOYEES E,EMPSALARY S WHERE [Link]=[Link] AND
DESIGNATION= “MANAGER”;
c) To display maximum salary of the employee designationwise.
SELECT MAX(SALARY),DESIGNATION FROM EMPLOYEES GROUP BY
DESIGNATION;
d) To display all the details of employee from both tables whose name starts with
“R”.
SELECT * FROM EMPLOYEES NATURAL JOIN EMPSALARY WHERE
FIRSTNAME LIKE “R%”;
SET E
1. Write a Python Program to Create a binary file "[Link]" to store employee number, name,
designation,salary and display the employee name designation who earn a salary above 85000.

(a) To show all information about the teacher of history department

SELECT * FROM TEACHER WHERE DEPARTMENT= “HISTORY”;
(b) To list the names of female teacher who are in Hindi department
SELECT NAME FROM TEACHER WHERE DEPARTMENT= “HINDI” AND SEX= “F”;
(c) To list names of all teachers with their date of joining in ascending order.
SELECT NAME FROM TEACHER ORDER BY DATE OF JOIN;
(d) To display teacher’s Name, Salary, Age for male teacher only
SELECT NAME,SALARY,AGE FROM TEACHER WHERE SEX= “M”;
SET F

a) Display name of all doctors who are in “ORTHOPEDIC” having more than 10 years of
experience from the table DOCTOR.
SELECT NAME FROM DOCTOR WHERE DEPT= “ORTHOPEDIC” AND
EXPERIENCE>10;
b) Display the average salary of all doctors working in “ENT” department (using both tables)
where salary=basic+Allowance.
SELECT AVG(BASIC+ALLOWANCE) FROM DOCTOR D,SALARY S WHERE
[Link]=[Link] AND DEPT= “ENT”;
c) Display the minimum allowance of all female doctors.
SELECT MIN(ALLOWANCE) FROM DOCTOR D,SALARY S WHERE [Link]=[Link] AND
SEX= “F”;
d) Display the highest consultation fee of all doctors departmentwise.
SELECT MAX(CONSULTATION) FROM DOCTOR D,SALARY S WHERE [Link]=[Link]
GROUP BY DEPT;
SET G
Write a Program to perform the following:
 i. To read contents from a text file "[Link]" to display the lines which has the word “or”,
“and” or “to” in a given file.

ii. To read the contents from text file "[Link]" and display those lines with highest number of words in it.

a) List the names of those students who have obtained DIV 1 sorted by NAME.
SELECT NAME FROM GRADUATE WHERE DIV=1 ORDER BY NAME;
b) Display a report, listing NAME, STIPEND, SUBJECT and amount of stipend received in a year
assuming that the STIPEND is paid every month.
SELECT NAME,STIPEND,SUBJECT,STIPEND*12 AS AMOUNT FROM GRADUATE;
c) To count the number of students who are either PHYSICS or COMPUTER SC graduates.
SELECT COUNT(*) FROM GRADUATE WHERE SUBJECT= “PHYSICS” OR SUBJECT=
“COMPUTER SC”;
d) Display Name of the students whose average is more than 65.
SELECT NAME FROM GRADUATE WHERE AVERAGE >65;
SET H
Accept a list of numbers from the user and create a stack that will store only the multiples of 5 to
mult_5.Pop the top most element from the stack. Display the rest of the contents.

a) To display first name,lastname,address and city of all employees living in paris.

SELECT FIRSTNAME,LASTNAME,ADDRESS,CITY FROM EMPLOYEES
WHERE CITY= “PARIS”;
b) To display first name,last name and total salary of all managers from table
employee and table empsalary where total salary is calculated as
salary+benefits.
SELECT FIRSTNAME,LASTNAME,SALARY+BENEFITS AS TOTSAL FROM
EMPLOYEES E,EMPSALARY S WHERE [Link]=[Link] AND
DESIGNATION= “MANAGER”;
c) To display maximum salary of the employee designationwise.
 SELECT MAX(SALARY),DESIGNATION FROM EMPLOYEES GROUP BY
DESIGNATION;
d) To display all the details of employee from both tables whose name starts with
“R”.
SELECT * FROM EMPLOYEES NATURAL JOIN EMPSALARY WHERE
FIRSTNAME LIKE “R%”;
SET I
Write a program to create a file [Link] with the structure
[rollno,name,class,section,average] and read all content to display names of students
who has the highest average among class 12 students.
 a) Display name of all doctors who are in “ORTHOPEDIC” having more than 10 years of
experience from the table DOCTOR.
SELECT NAME FROM DOCTOR WHERE DEPT= “ORTHOPEDIC” AND
EXPERIENCE>10;
b) Display the average salary of all doctors working in “ENT” department (using both tables)
where salary=basic+Allowance.
SELECT AVG(BASIC+ALLOWANCE) FROM DOCTOR D,SALARY S WHERE
[Link]=[Link] AND DEPT= “ENT”;
c) Display the minimum allowance of all female doctors.
SELECT MIN(ALLOWANCE) FROM DOCTOR D,SALARY S WHERE [Link]=[Link] AND
SEX= “F”;
d) Display the highest consultation fee of all doctors departmentwise.
SELECT MAX(CONSULTATION) FROM DOCTOR D,SALARY S WHERE [Link]=[Link]
GROUP BY DEPT;

SET J
Add products to the already existing binary file [Link] with the structure
[prod_id,pname,brand,qty,price].Display the details of the product that must be re-
stocked when the number of products fails below the requirements.
a) List the names of those students who have obtained DIV 1 sorted by NAME.
SELECT NAME FROM GRADUATE WHERE DIV=1 ORDER BY NAME;
b) Display a report, listing NAME, STIPEND, SUBJECT and amount of stipend received in a year
assuming that the STIPEND is paid every month.
SELECT NAME,STIPEND,SUBJECT,STIPEND*12 AS AMOUNT FROM GRADUATE;
c) To count the number of students who are either PHYSICS or COMPUTER SC graduates.
SELECT COUNT(*) FROM GRADUATE WHERE SUBJECT= “PHYSICS” OR SUBJECT=
“COMPUTER SC”;
d) Display Name of the students whose average is more than 65.
SELECT NAME FROM GRADUATE WHERE AVERAGE >65;