Government of Karnataka

DEPARTMENT OF SCHOOL EDUCATION

(PRE-UNIVERSITY)

SECOND YEAR P U C

COMPUTER SCIENCE (NEW SYLLABUS)

1
 List of practical problems - II PU Course

PART – A
Python
1) Write a python program using a function to print n fibonacci numbers.
2) Write a menu driven program in python to find factorial and sum of natural of n Numbers using
function.
3) Write a python program using user defined function to calculate interest amount using simple interest
method and compound interest method and find the difference of interest amount between the two
methods.
4) Write a Python Program to create a text file and to read a text file and display the number of vowels,
consonants, uppercase and lowercase characters in the file.
5) Write a python code to find the number of lines, number of words and number of characters in a text
file.
6) Write a python program to create and to read records in binary file with student name and marks of
six subjects.
7) Write a python program to copy the records of the students having percentage 90 and above from the
binary file into another file.
8) Write a python program using function to sort the elements of a list using bubble sort method.
9) Write a python program using function to sort the elements of a list using selection sort method.
10) Write a python program using function to sort the elements of a list using insertion sort method.
11) Write a python program using function to search an element in a list using linear search method.
12) Write a python program using function to search an element in a list using binary search method.
13) Write a python program to add and display elements from a stack using list.
14) Write a python program to add and display elements from a queue using list.

PART B
MYSQL

B1) Create a table with the following fields and enter 10 records into the table.
Entity Name: marks

Attribute name Type Size Constraints

Rollno Int 5
Sname Varchar 15 Not null
Lang_mks Int 3 Between 0 and 100
Eng_mks Int 3 Between 0 and 100
Sub1_mks Int 3 Between 0 and 100
Sub2_mks Int 3 Between 0 and 100
Sub3_mks Int 3 Between 0 and 100
Sub4_mks Int 3 Between 0 and 100

2
(i) List all the records
(ii) Display the description of the table
(iii) Add the new attributes total and percent
(iv) Calculate total and percentage of marks for all the students
(v) List the students whose percentage of marks is more than 60%.
(vi) List the students whose percentage is between 60 % and 85%.
(vii) Arrange the students based on percentage of marks from highest to lowest.

B2) Create a table for house hold Electricity bill with the following fields and enter 10 records.
Entity Name: BESCOM
Attributename Type Size Constraint
RRNO Varchar 10 Primary key
CUSTNAME Varchar 25 Not null
BILLDATE DATE
UNITS INT 4

1. View the structure of table.

2. List all the records
3. Add a new field for bill amount in the name of billamt.
4. Compute the bill amount for each consumer as per the following rules.
a. MINIMUM Amount Rs. 100
b. For first 100 units Rs 7.50/Unit
c. For the above 100 units Rs. 8.50/Unit
5. Display the maximum, minimum and total bill amount.
6. List all the bills generated in a sorted order based on RRNO.

B3) Create a table with the following details and enter 10 records into the table.

Entity Name: student

Attribute name Type Size Constraint

Rollno int 5 Primary key
Sname Varchar 15 Not null
DOB date
Gender char 1
Combn Varchar 5
Class Char 6

(i) List all the students

(ii) List the students who are in BASC and CEBA combination.
(iii) List only the combination by removing duplicate values.
(iv) List the students alphabetically.
(v) List the students alphabetically and class-wise.
(vi) List the students who born in the month of June of any year.
(vii) Count the number of students Gender-wise.

3
B4) Create a table with following fields and enter 10 records into the table.

Entity Name: Library

Attributename Type Size Constraint

Title Varchar 75 Not null
Author Varchar 60
Year int 4
Category Varchar 25
Price float 7,2
Qty Int 4

(i) List all the books

(ii) Calculate Amount by altering table with a new column ‘Amount’
(iii) List the records of all those books price is between 400 and 900.
(iv) List those records with no value in the attribute year .
(v) List the names of the authors whose name starts with ‘C’ or ‘D’.
(vi) List Title, year, category with category has word ‘science’.
(vii) List all those records whose year of publication is 2010 onwards with price is less than 750.

4
 PRACTICAL EXAMINATION
Sl. No Program Marks
1 Writing 1 Program from PART-A (Python) 6 Marks
Writing 1 Program from PART-B
2 6 Marks
(SQL)
3 Execution of any one program of examiner’s choice 8 Marks
4 Practical record 6 Marks
Viva-voce (Four Questions related to the program
5 4 Marks
written by the student in Exam)
Total 30 Marks

PRACTICAL MARKS DISTRIBUTION

PART-A Writing One Python program 06

PART-B Writing One SQL Program (Commands) 06

Execution of any one program of examiner’s choice 08

Viva-Voce (2-Internal, 2-External)

(Questions related to the program written by the student in Practical 04
Exam)
Attendance
90% - 100% 06
80% - 90 % 05
Practical Record (Marks)
70% - 80% 04
60% - 70% 03
Below 60% 00
TOTAL  30

5
 PART A
PYTHON

A1. Write a python program using a function to print fibonacci series up to n numbers

# Fibonacci numbers
def fibo(n):
num1 = 0
num2 = 1
next_number = num2
count = 2
print(num1," ", num2, end=" ")
while count <n:
print(next_number, end=" ")
count += 1
num1, num2 = num2, next_number
next_number = num1 + num2
n = int(input("enter the Limit"))
fibo(n)
print()

OUTPUT:

enter the Limit 10

0 1 1 2 3 5 8 13 21 34 55 89

6
A2. Write a Menu driven program in python to find factorial, and sum of natural Numbers using a
function

# Python program to find factorial of given number and Sum of natural numbers
def fact(n):
return 1 if (n==1 or n==0) else n * fact(n - 1);
def sum(n):
return 0 if (n==0) else n+sum(n-1);
num = int(input("Enter any number : "))

print("1-To find the factorial, 2-To find the sum 3-Exit")

opt=int(input("Enter the option 1-3 : "))
if (opt==1):
print("Factorial of ",num,"is : ",fact(num))
else:
if(opt==2):
print("Sum of ",num,"is : ",sum(num))
else:
print(" ")

OUTPUT:

Enter any number : 5

1-To find the factorial, 2-To find the sum 3-Exit
Enter the option 1-3 : 1
Factorial of 5 is : 120

Enter any number : 5

1-To find the factorial, 2-To find the sum 3-Exit
Enter the option 1-3 : 2
Sum of 5 is : 15

7
A3. Write a python program using user defined function to calculate interest amount using simple
interest method and compound interest method and find the difference of interest amount
between the two methods.

# Simple and Compound Interest

# Reading principal amount, rate and time
def simpInt(principle,time,rate):
si = float(principle*time*rate/100)
return si
def compInt(principle,time,rate):
ci = float(principle * ((1+rate/100)**time - 1))
return ci
principle = float(input('Enter amount: '))
time = float(input('Enter time: '))
rate = float(input('Enter rate: '))
si=simpInt(principle,time,rate)
ci=compInt(principle,time,rate)
print('Simple interest is Rs. %8.2f' % si)
print('Compound interest is Rs. %8.2f' % ci)
diffint=ci-si;
print("Difference is Rs. %8.2f " % diffint)

OUTPUT:

Enter amount: 45000

Enter time: 3
Enter rate: 12.5
Simple interest is Rs. 16875.00
Compound interest is Rs. 19072.27
Difference is Rs. 2197.27

8
A4. Write a Python Program to read a text file and display the number of
vowels, consonants, uppercase and lowercase characters in the file

# Python Program to create a text file and to read a text file and display the number of
# vowels, consonants, uppercase and lowercase characters in the file
def count_characters(file_name):
vowels = "aeiouAEIOU"
vowel_count = 0
consonant_count = 0
uppercase_count = 0
lowercase_count = 0

with open(file_name, "r") as file:

text = file.read()

for char in text:

if char.isalpha():
if char in vowels:
vowel_count += 1
else:
consonant_count += 1

if char.isupper():
uppercase_count += 1
elif char.islower():
lowercase_count += 1

print("Vowels:" , vowel_count)
print("Consonants: ", consonant_count)
print("Uppercase characters: ",uppercase_count)
print("Lowercase characters: ", lowercase_count)

def create_text_file(file_name, content):

with open(file_name, "w") as file:
file.write(content)

file_name = "sample.txt"
content = input("enter few sentences to create a text file with content: ")
create_text_file(file_name, content)

count_characters(file_name)

OUTPUT:

enter few sentences to create a text file with content: This program is TO Create a FILE
Vowels: 11
Consonants: 15
Uppercase characters: 8
Lowercase characters: 18

9
A5) Write a python code to count the number of lines, number of words and number of characters
in a text file.

# Python program to count number of lines,

# Words and character in textfile
def count_text_file(file_name):
line_count = 0
word_count = 0
char_count = 0

with open(file_name, "r") as file:

for line in file:
line_count += 1
word_count += len(line.split())
char_count += len(line)

print("Lines: ", line_count)

print("Words: ", word_count)
print("Characters: ", char_count)

def create_text_file(file_name, content):

with open(file_name, "w") as file:
file.write(content)

file_name = "sample.txt"
content = """Hello Students
This is a sample text file.
It contains multiple lines."""
create_text_file(file_name, content)

count_text_file(file_name)

OUTPUT:

Lines: 3
Words: 12
Characters: 70

10
A6) Write a python program to create and to read records in binary file with student name and
marks of six subjects.
import pickle
while True:
print(''' 1. Create Binary File.
2. Display the File.
3. Exit''')
a=int(input('choose a command (1-2,3-exit): '))
if a==1:
f=open('student.dat','wb')
x=int(input('How many student: '))
for i in range(x):
name=input('Name: ')
english=int(input('English Mark: '))
lan=int(input("Language Marks"))
phy=int(input('Physics Mark: '))
chem=int(input('Chemistry Mark: '))
maths=int(input('Maths Mark: '))
cs=int(input('CS Mark: '))
t=[name,english,lan,phy,chem,maths,cs]
pickle.dump(t,f)
f.close()
elif a==2:
f=open('student.dat','rb')
try:
while True:
p=pickle.load(f)
print(p)
except:
f.close()
if a>2:
break

OUTPUT:
1. Create Binary File.
2. Display the File.
3. Exit
choose a command (1-2, 3-exit): 2
['Bharath', 77, 88, 99, 77, 88, 99]
['Guru', 77, 88, 99, 77, 88, 99]
['Manjula', 88, 99, 88, 99, 88, 88]
['Rekha', 99, 88, 99, 99, 88, 88]
['Uday', 66, 77, 66, 77, 66, 77]
1. Create Binary File.
2. Display the File.
3. Exit
choose a command (1-2,9-exit):3

11
A7. Write a python program to copy the records of the students having percentage 90 and above
from the binary file into another file.
# program to create and copy records to copy records with percentage 90 and above into another file.

import pickle
while True:
print(''' 1. Create Binary File.
2. Display the main File.
3. Create new file wirh >90
4. Exit''')
a=int(input('choose a command (1-2,9-exit): '))
if a==1:
f=open('student.dat','wb')
o=open('stuii.dat','wb')
x=int(input('How many student: '))
for i in range(x):
name=input('Name: ')
english=int(input('English Mark: '))
lan=int(input("Language Marks"))
phy=int(input('Physics Mark: '))
chem=int(input('Chemistry Mark: '))
maths=int(input('Maths Mark: '))
cs=int(input('CS Mark: '))
total=phy+chem+cs+maths+english+lan
per=(total / 600) * 100
t=[name,english,lan,phy,chem,maths,cs,total,per]
g=[name,english,lan,phy,chem,maths,cs,total,per]
pickle.dump(t,f)
if per>=90:
pickle.dump(g,o)
f.close()
o.close()
elif a==2:
f=open('student.dat','rb')
try:
while True:
p=pickle.load(f)
print(p)
except:
f.close()
elif a==3:
print("Studets with > 90 Marks")
f=open('stuii.dat','rb')
try:
while True:
o=pickle.load(f)
print(o)
except:
f.close()

12
 else:
break

OUTPUT:

1. Create Binary File.

2. Display the main File.
3. Create new file wirh >90
4. Exit
choose a command (1-2,9-exit): 2
['Bharath', 77, 88, 99, 88, 77, 77, 506, 84.33333333333334]
['Guru', 88, 77, 88, 77, 88, 77, 495, 82.5]
['Manjula', 88, 77, 88, 88, 77, 77, 495, 82.5]
['Rekha', 99, 88, 99, 88, 99, 88, 561, 93.5]
['Uday', 99, 99, 99, 99, 99, 99, 594, 99.0]
1. Create Binary File.
2. Display the main File.
3. Create new file wirh >90
4. Exit
choose a command (1-2,9-exit): 3
Studets with > 90 Marks
['Rekha', 99, 88, 99, 88, 99, 88, 561, 93.5]
['Uday', 99, 99, 99, 99, 99, 99, 594, 99.0]
1. Create Binary File.
2. Display the main File.
3. Create new file wirh >90
4. Exit
choose a command (1-2,9-exit):4

13
A8. Write a python program using function to sort the elements of a list using bubble sort method

# program using function to sort the elements of list using bubble sort method
def bubble_sort(arr):
n = len(arr)
for i in range(n):
for j in range(0, n-i-1):
if arr[j] > arr[j+1]:
arr[j], arr[j+1] = arr[j+1], arr[j]

def input_list():
arr = []
n = int(input("Enter the number of elements in the list: "))
for i in range(n):
element = int(input("Enter element "))
arr.append(element)
return arr

arr = input_list()
print("Original list:", arr)

bubble_sort(arr)
print("Sorted list:", arr)

OUTPUT:

Enter the number of elements in the list: 5

Enter element 34
Enter element 56
Enter element 23
Enter element 12
Enter element 34
Original list: [34, 56, 23, 12, 34]
Sorted list: [12, 23, 34, 34, 56]

14
A9. Write a python program using function to sort the elements of a list using selection sort
method

# Selection sort in Python

# Selection sort in Python
def selection_sort(arr):
n = len(arr)
for i in range(n):
min_index = i
for j in range(i+1, n):
if arr[j] < arr[min_index]:
min_index = j
arr[i], arr[min_index] = arr[min_index], arr[i]

def input_list():
arr = []
n = int(input("Enter the number of elements in the list: "))
for i in range(n):
element = int(input("Enter element "))
arr.append(element)
return arr

arr = input_list()

print("Original list:", arr)

selection_sort(arr)
print("Sorted list:", arr)

OUTPUT:

Enter the number of elements in the list: 5

Enter element 32
Enter element 45
Enter element 67
Enter element 34
Enter element 23
Original list: [32, 45, 67, 34, 23]
Sorted list using insertion sort method: [23, 32, 34, 45, 67]

15
A10. Write a python program using function to sort the elements of a list using insertion sort
method

# python program using function to sort the elements of list using insertion sort method
def insertion_sort(arr):
for i in range(1, len(arr)):
key = arr[i]
j=i-1
while j >= 0 and key < arr[j]:
arr[j + 1] = arr[j]
j -= 1
arr[j + 1] = key

def input_list():
arr = []
n = int(input("Enter the number of elements in the list: "))
for i in range(n):
element = int(input("Enter element "))
arr.append(element)
return arr

arr = input_list()
print("Original list:", arr)

insertion_sort(arr)
print("Sorted list using insertion sort method:", arr)

OUTPUT:

Enter the number of elements in the list: 5

Enter element 55
Enter element 43
Enter element 23
Enter element 43
Enter element 65
Original list: [55, 43, 23, 43, 65]
Sorted list using insertion sort method: [23, 43, 43, 55, 65]

16
A11. Write a python program using function to search an element in a list using linear search
method

# Search function with parameter list name

# and the value to be searched - Linear Search

def linear_search(arr, target):

for index, element in enumerate(arr):
if element == target:
return index
return -1
def input_list():
arr = []
n = int(input("Enter the number of elements in the list: "))
for i in range(n):
element = int(input("Enter element "))
arr.append(element)
return arr

arr = input_list()
target = int(input("Enter the element to search for: "))

result = linear_search(arr, target)

if result != -1:
print(target, " Element found at index ", result)
else:
print(target, " Element not found in the list.")

OUTPUT:

Enter the number of elements in the list: 5

Enter element 23
Enter element 4
Enter element 5
Enter element 6
Enter element 45
Enter the element to search for: 55
55 Element not found in the list.

17
A12. Write a python program using function to search an element in a list using binary search
method.

def binary_search(arr, target):

low, high = 0, len(arr) - 1
while low<= high:
mid = (low + high) // 2
if arr[mid] == target:
return mid
elif arr[mid] < target:
left = mid + 1
else:
right = mid - 1
return -1

def input_list():
arr = []
n = int(input("Enter the number of elements in the list: "))
for i in range(n):
element = int(input("Enter the elements in ascending order "))
arr.append(element)
return arr

arr = input_list()
target = int(input("Enter the element to search for: "))

result = binary_search(arr, target)

if result != -1:
print(target, "Element found at index ", result)
else:
print(target, "Element not found in the list.")

OUTPUT:

Enter the number of elements in the list: 5

Enter the elements in ascending order 12
Enter the elements in ascending order 23
Enter the elements in ascending order 34
Enter the elements in ascending order 454
Enter the elements in ascending order 656
Enter the element to search for: 34
34 Element found at index 2

18
A13. Write a python program to add and display elements from a stack using list

# initial empty stack

stack = []
#PUSHing the elements into a stack
print("initially stack is empty :",stack)

stack.append('x')
stack.append('y')
stack.append('z')

print("After PUSHING stack is :")

print(stack)

#POPing the elements from a stack

print('After POPped from stack: ')
print(stack.pop())
print(stack.pop())
print(stack.pop())

print('\nmy_stack after elements are poped:')

print(stack)

OUTPUT:

initially stack is empty : []

After PUSHING stack is :
['x', 'y', 'z']
After POPped from stack:
z
y
x

my_stack after elements are poped:

[]

19
A14. Write a python program to add and display elements from a queue using list

# python program to add and display elements from a queue using list
# Create Queue and perform insert and delete

import queue

# Display the elements in the queue

def display_queue(q):
print("Queue elements are:", end=" ")

while not q.empty():

element = q.get()
print(element, end=" ")
print('\n Queue size after REMOVE is ', q.qsize())

# Create a Queue object

q = queue.Queue()

# Add elements to the queue using put()

q.put(10)
q.put(20)
q.put(30)
print('Queue size after INSERT is ', q.qsize())

# Display the elements in the queue

display_queue(q)

OUTPUT:

Queue size after INSERT is 3

Queue elements are: 10 20 30
Queue size after REMOVE is 0

20
 PART B
MYSQL

B1) Create a table with the following fields and enter 10 records into the table.
Entity Name: marks

Attribute name Type Size Constraints

Rollno Int 5
Sname Varchar 15 Not null
Lang_mks Int 3 Between 0 and 100
Eng_mks Int 3 Between 0 and 100
Sub1_mks Int 3 Between 0 and 100
Sub2_mks Int 3 Between 0 and 100
Sub3_mks Int 3 Between 0 and 100
Sub4_mks Int 3 Between 0 and 100

CREATE TABLE marks (

Rollno INT(5),
Sname VARCHAR(15) NOT NULL,
Lang_mks INT(3) CHECK (Lang_mks BETWEEN 0 AND 100),
Eng_mks INT(3) CHECK (Eng_mks BETWEEN 0 AND 100),
Sub1_mks INT(3) CHECK (Sub1_mks BETWEEN 0 AND 100),
Sub2_mks INT(3) CHECK (Sub2_mks BETWEEN 0 AND 100),
Sub3_mks INT(3) CHECK (Sub3_mks BETWEEN 0 AND 100),
Sub4_mks INT(3) CHECK (Sub4_mks BETWEEN 0 AND 100)
);

Data to be entered: (Values are indicative)

Rollno Sname Lang_mks Eng_mks Sub1_mks Sub2_mks Sub3_mks Sub4_mks

1010 RAJ 89 97 98 99 86 95
1026 KIRAN 67 62 72 86 72 62
1042 ANAND 78 87 92 82 72 76
1250 RAM 72 86 72 62 87 68
5212 VIJAYA 46 58 86 92 72 62
3622 MANOJ 86 56 62 86 52 64
1948 REEHAN 63 68 52 56 96 76
1482 KAJOL 49 54 48 76 62 55
1947 KUMAR 98 98 99 100 97 99
1951 REEMA 82 72 62 98 73 64

INSERT INTO marks VALUES(1010, 'RAJ', 89, 97, 98, 99, 86, 95)
INSERT INTO marks VALUES (1026, 'KIRAN', 67, 62, 72, 86, 72, 62)
INSERT INTO marks VALUES (1042, 'ANAND', 78, 87, 92, 82, 72, 76)
INSERT INTO marks VALUES (1250, 'RAM', 72, 86, 72, 62, 87, 68)
INSERT INTO marks VALUES (5212, 'VIJAYA', 46, 58, 86, 92, 72, 62)
INSERT INTO marks VALUES (3622, 'MANOJ', 86, 56, 62, 86, 52, 64)
INSERT INTO marks VALUES (1948, 'REEHAN', 63, 68, 52, 56, 96, 76)
INSERT INTO marks VALUES (1482, 'KAJOL', 49, 54, 48, 76, 62, 55)
INSERT INTO marks VALUES (1947, 'KUMAR', 98, 98, 99, 100, 97, 99)

21
(i) List all the records
select * from marks;
(ii) Display the description of the table
describe marks;
(iii) Add the new attributes total and percent
alter table marks add(total int(3), percent float(5,3));

(iv) Calculate total and percentage of marks for all the students
update marks set total = lang_mks+eng_mks+sub1_mks+sub2_mks+sub3_mks+sub4_mks;

update marks set percent = total/600*100;

(v) List the students whose percentage of marks is more than 60%.
select sname, percent from marks where percent >=60;

(vi) List the students whose percentage is between 60% and 85%.
select sname, percent from marks where percent between 60 and 85;

(vii) Arrange the students based on percentage of marks from highest to lowest.
select sname, percent from marks order by percent desc;

22
B2) Create a table for house hold Electricity bill with the following fields and enter 10 records.
Entity Name: BESCOM
Attributename Type Size Constraint
RRNO Varchar 10 Primary key
CUSTNAME Varchar 25 Not null
BILLDATE DATE
UNITS INT 4

CREATE TABLE BESCOM (

RRNO VARCHAR(10) PRIMARY KEY,
CUSTNAME VARCHAR(25) NOT NULL,
BILLDATE DATE,
UNITS INT(4)
);

Data to be entered: (Values are indicative)

RRNO CUSTNAME BILLDATE UNITS

E1120 RAJ 2024-05-5 250
E2210 KIRAN 2024-03-26 178
E1450 ANAND 2024-04-15 56
E2126 RAM 2024-05-8 782
E1562 MANJULA 2024-05-2 562
E6221 MANOJ 2024-05-18 72
E5822 REEHAN 2024-02-19 92
E1692 KAJOL 2024-03-25 73
E6721 KUMAR 2024-07-14 589
E2682 REEMA 2024-05-11 100

INSERT INTO BESCOM VALUES('E1120', 'RAJ', '2024-05-05', 250)

INSERT INTO BESCOM VALUES ('E2210', 'KIRAN', '2024-03-26', 178)
INSERT INTO BESCOM VALUES ('E1450', 'ANAND', '2024-04-15', 56)
INSERT INTO BESCOM VALUES ('E2126', 'RAM', '2024-05-08', 782)
INSERT INTO BESCOM VALUES ('E1562', 'MANJULA', '2024-05-02', 562)
INSERT INTO BESCOM VALUES ('E6221', 'MANOJ', '2024-05-18', 72)
INSERT INTO BESCOM VALUES ('E5822', 'REEHAN', '2024-02-19', 92)
INSERT INTO BESCOM VALUES ('E1692', 'KAJOL', '2024-03-25', 73)
INSERT INTO BESCOM VALUES ('E6721', 'KUMAR', '2024-07-14', 589)
INSERT INTO BESCOM VALUES ('E2682', 'REEMA', '2024-05-11', 100);

7. View the structure of table.

describe bescom;

8. List all the records

Select * from bescom;

9. Add a new field for bill amount in the name of billamt.

ALTER TABLE BESCOM ADD BILLAMT FLOAT(7,2);

10. Compute the bill amount for each consumer as per the following rules.
a. MINIMUM Amount Rs. 100
b. For first 100 units Rs 7.50/Unit
c. For the above 100 units Rs. 8.50/Unit
(i) UPDATE BESCOM SET BILLAMT = 100 + UNITS * 7.50 WHERE UNITS <= 100;

23
(ii) UPDATE BESCOM SET BILLAMT = 100 + (100 * 7.50) + (UNITS - 100) * 8.50 WHERE
UNITS > 100;

11. Display the maximum, minimum and total bill amount.

SELECT MAX(BILLAMT), min(billamt), avg(billamt), sum(billamt) FROM BESCOM;

12. List all the bills generated in a sorted order based on RRNO.
select rrno from bescom order by rrno;

24
B3) Create a table with the following details and enter 10 records into the table.

Entity Name: student

Attribute name Type Size Constraint

Rollno int 5 Primary key
Sname Varchar 15 Not null
DOB date
Gender char 1
Combn Varchar 5
Class Char 6

CREATE TABLE student (

Rollno int(5) PRIMARY KEY,
Sname VARCHAR(15) NOT NULL,
dob date,
Gender CHAR(1),
Combn VARCHAR(5),
Class varCHAR(6)
);

Data to be entered: ( Values are indicative)

RollNo Sname DOB Gender Combn Class

insert into student values (3739, 'Uday', '2004-9-12', 'M', 'PCMC','2B');

insert into student values (1001, 'Raj Kumar', '2005-5-21', 'M', 'BASC','2A');
insert into student values (1005, 'Kiran', '2004-11-15', 'M', 'PCMC','2B');
insert into student values (1042, ‘Anand', '2005-12-22', 'M', ‘CEBA','2C');
insert into student values (1250, 'Ram', '2004-6-18', 'M', 'PCMC','2A');
insert into student values (5212, 'Vijaya', '2007-7-28', 'F', 'PCMC','2A');
insert into student values (1029, 'Bharath', '2005-1-12', 'M', 'BASC','2B');
insert into student values (2152, 'Rekha', '2006-6-8', 'F', 'CEBA','2C');
insert into student values (1948, 'Reehan', '2005-4-17', 'M', 'CEBA','2C');
insert into student values (2443, 'Manjula', '2005-8-15', 'F', 'PCMC','2B');

(i) List all the students

select * from student;
(ii) List only those students who are in BASC and CEBA combination.
select * from student where combn='BASC' or combn='CEBA';
(iii) List only the combination by removing duplicate values.
select distinct(combn) from student;
(iv) List the students alphabetically.
select sname from student order by sname;
(v) List the students alphabeticallyclass-wise.
select sname, class from student group by class order by sname;

(vi) List the students who born in the month of June of any year.
SELECT * FROM student WHERE MONTH(dob) = 6;

(vii) Count the number of students Gender-wise.

SELECT gender, COUNT(*) FROM student GROUP BY gender;

25
B4) Create a table with following fields and enter 10 records into the table.

Entity Name: Library

Attributename Type Size Constraint

Title Varchar 75 Not null
Author Varchar 60
Year int 4
Category Varchar 25
price float 7,2
Qty Int 4

CREATE TABLE Library (

Title VARCHAR(75) NOT NULL,
Author VARCHAR(60),
Year INT(4),
Category VARCHAR(25),
Price FLOAT(7,2),
Qty INT(4)
);

Data to be entered: (Values are indicative)

Title Author Year Category Price Qty
The Data Science Handbook Darshan 2019 Data science 750 12
Introduction to Computer Programs Harshavardhan null Computer Science 700 6
Computer Science Text book Class 12 Reeta Sahoo 2019 Textbook 450 4
A book on AI Sagar 2016 AI 200 7
Robots and Automation Dushyanth 2018 Digital Technology 750 9
AI 2041 Chen & Lee 2021 AI 1000 2
Computer Hardware and Software Chethan 2000 Computer science 500 7
The Future of Work Dev Kumar 2018 Digital Technology 750 4
Healthcare and AI Eshwar 2019 AI 950 9
Introduction to Data Science Dravid 2014 Data Science 400 5

INSERT INTO Library VALUES('The Data Science Handbook', 'Darshan', 2019, 'Data science',
750.00, 12)
INSERT INTO Library VALUES ('Introduction to Computer Programs', 'Harshavardhan',
NULL, 'Computer Science', 700.00, 6)
INSERT INTO Library VALUES ('Computer Science Text book Class 12', 'Reeta Sahoo', 2019,
'Textbook', 450.00, 4)
INSERT INTO Library VALUES ('A book on AI', 'Sagar', 2016, 'AI', 200.00, 7)
INSERT INTO Library VALUES ('Robots and Automation', 'Dushyanth', 2018, 'Digital
Technology', 750.00, 9)
INSERT INTO Library VALUES ('AI 2041', 'Chen & Lee', 2021, 'AI', 1000.00, 2)
INSERT INTO Library VALUES ('Computer Hardware and Software', 'Chethan', 2000,
'Computer science', 500.00, 7)
INSERT INTO Library VALUES ('The Future of Work', 'Dev Kumar', 2018, 'Digital
Technology', 750.00, 4)
INSERT INTO Library VALUES ('Healthcare and AI', 'Eshwar', 2019, 'AI', 950.00, 9)
INSERT INTO Library VALUES ('Introduction to Data Science', 'Dravid', 2014, 'Data Science',
400.00, 5);

(i) List all the books

select * from library;
26
(ii) Calculate Amount by altering table by adding a new column ‘Amount’
(a) alter table library add(amount float(7,2));
(b) update library set amount = price * qty;

(iii) List the records of all those books price is between 400 and 900.
select price from library where price between 400 and 900;

(iv) List those records with no value in the attribute year .

select title, year from library where year is NULL;

(v) List the names of the authors whose name starts with letter ‘C’ or ‘D’.
select Author from library where author like 'C%' or author like 'D%';

(vi) List Title, year and category from the table library with category field has word ‘science’.
SELECT Title, Year, Category FROM Library WHERE Category LIKE '%science%';

(vii) List all those records whose year of publication is 2010 onwards with book price is less than
Rs.750.
SELECT year, price FROM Library WHERE Year >= 2010 AND Price < 750;

-oOo-

27