COMPUTER SCIENCE-PRACTICAL (2023-24)

1. Write a function that accept a list containing integers randomly. Accept any
number and display the index position at which the number is found in the list.
PROGRAM
def searchElement(marks):
maxrange =int(input("Enter Total Count of
numbers in the list: ")) flag=False
for i in range(0,
maxrange): marks.append (
int (input (" ? ") ) )
print(marks)
number =int(input("Enter number to be
searched ")) for i in range(0,
maxrange):
if marks[i]==number:
print(number,"found at index position ", I , " & " ,
"as list element no" , i+1 ) flag=True
if flag==False:
print( number, "not found in list")
# main program marks=[]
searchElement(marks)

OUTPUT:(write in white page)

Enter Total Count of numbers in the list: 6
? 45
? 67
? 89
? 23
? 12
? 54
[45, 67, 89, 23, 12, 54]
Enter number to be searched 23
23 found at index position 3 & as list element no 4
 2. Write a python menu based program using user defined function to find
summation, subtraction, multiplication and division of values.
PROGRAM CODING :
def ArithmeticOp():
ans='Y'
print("1. Sum of two numbers")
print("2. Subtaction of two
numbers")
print("3. Multiplication of two
numbers") print("4. Division of two
numbers")
while ans.upper()=='Y':
choice=int(input('Enter your choice
: ')) if choice==1 :
a=int(input('Enter first number :'))
b=int(input('Enter second number
:')) c=a+b
print("Sum=",
c) elif choice==2
:
a=int(input('Enter first number
:')) b=int(input('Enter second
number :')) c=a-b
print("Subtraction=",
c) elif choice==3 :
a=int(input('Enter first number
:')) b=int(input('Enter second
number :')) c=a*b
print("Multiplication=",
c) elif choice==4 :
a=int(input('Enter first number :'))
b=int(input('Enter second number
: ')) c=a/b
print("Division=",c)
else :
print("Wrong choice")
ans=input(" Do you want to continue press (Y/N)")
# Function calling
statement
ArithmeticOp( )
 OUTPUT:
1. Sum of two numbers
2. Subtaction of two numbers
3. Multiplication of two numbers
4. Division of two
numbers Enter your
choice : 1
Enter first number
:24 Enter second
number :12 Sum= 36
Do you want to continue press (Y/N)y
Enter your choice :
2 Enter first
number :34 Enter
second number :56
Subtraction= -22
Do you want to continue press (Y/N)y
Enter your choice :
3 Enter first
number :12 Enter
second number :42
Multiplication= 504
Do you want to continue press (Y/N)y
Enter your choice
: 4 Enter first
number :85 Enter
second number : 5
Division= 17.0
Do you want to continue press (Y/N)y
Enter your
choice : 5
Wrong choice
Do you want to continue press (Y/N)n
3. Read a line of text and show the statistic of the line as
a) Total number of uppercase letters
b) Total number of lowercase letters
c) Total number of digits
d) Total number of alphabets/letters
PROGRAM CODING :
# Read a line of text and show the statistic of the line
def statistic(Line):
upper_count=lower_count=digit_count=alpha_coun
t=0 for K in Line:
if K.isupper():
upper_count+
=1 elif
K.islower():
lower_count+
=1 elif
K.isdigit():
digit_count+
=1 if
K.isalpha():
alpha_count+=1
print("Total number of uppercase letters=
",upper_count) print("Total number of lowercase
letters= ",lower_count) print("Total number of digits=
",digit_count)
print("Total number of alphabets/letters=
",alpha_count) # main program starts
L_Text=input("Enter the line of text
: ") statistic(L_Text) # function
called
OUTPUT:
Enter the line of text : 7 Days in a Week and 12
Months in a Year. Total number of uppercase
letters= 4
Total number of lowercase
letters= 23 Total number of
digits= 3
Total number of alphabets/letters= 27
4. Write a random number generator that generates random numbers between 1 & 6
(simulates a dice) and check whether a user won a lottery or not.
PROGRAM CODING :
import
random A=[]
for c in range(6):
Lno=int(input("Enter your lottery number
between(1 - 6) : ")) Rno=random.randint(1,6)
A.append(Rn
o) if
Lno==Rno:
print(" you won the lottery, congratulations ")
else:
print(" SORRY , Try again ")
print("Randomised lottery list is :", A)
 OUTPUT:
Enter your lottery number
between(1 - 6) : 4 SORRY , Try
again
enter your lottery number
between(1 - 6):3 SORRY , Try
again
enter your lottery number
between(1 - 6):2 SORRY , Try
again
enter your lottery number
between(1 - 6):5 SORRY , Try
again
enter your lottery number between(1
- 6):6 you won the lottery 1 prize
, congratulations enter your
lottery number between(1 - 6):4
SORRY , Try again
Randomised lottery list is : [1, 6, 4, 2, 6, 3]
5. Write a function that receives a decimal number and prints the equivalent
number in other number bases I.e., in binary, octal and hexadecimal
equivalents using built-in functions.
PROGRAM CODING :
# conversion between number
system def ConvNum_sys(Num)
:
print("The binary equivalent of the given
number=",bin(N)) print("The octal equivalent of
the given number=",oct(N)) print("The Hexadecimal
equivalent of the given number=",hex(N))
# main program starts
N=int(input("Enter a decimal number to find its binary, octal
and hexadecimal form=")) ConvNum_sys(N) # function called
 OUTPUT:
Enter a number to find its binary, octal and
hexadecimal form=15 The binary equivalent of the
given number= 0b1111
The octal equivalent of the given number= 0o17
The Hexadecimal equivalent of the given number= 0xf
 6. Write a function count () to read and display the contents of the file
“Story.txt”, the size of the file in bytes , total number of vowels and total
number of consonants in that file.
PROGRAM CODING :
# To find the size of the file “Story.txt” in bytes, total number of vowels & total number of
consonants. def count():
file=open(r"STORY.txt","r
") vowels=0
consonants=
0 ch=0
str1=file.read
()
for j in str1:
ch=ch+1
j=j.lower
()
if (j=="a" or j=="e" or j=="i" or j=="o" or
j=="u"): vowels+=1
else:
if j.isalpha():
consonants+
=1
print("The size/length of the file STORY.txt =
" , ch) print("vowels letters count= " , vowels)
print("consonants letters count= " ,
consonants)
# function call
count()
 OUTPUT:
The size/length of the file
STORY.txt = 760 vowels letters
count= 230
consonants letters count= 341
7. Write a Python Program to write roll-no, name and marks of the students in a
class (get from user) & store these details in a file Marks.dat. Also, define a
function ShowData() function to read and display those data from file “Marks.dat
“ on screen.
PROGRAM CODING :
#code to write rollno, name and marks of the students in a data file Marks.dat
def Stu_Marks():
count=int(input("How many students are there in the class : "))
fileout=open("Marks.dat","a")
for i in range(count):
print("Enter details of student",(i+1),"below
:- ") rollno=int(input("Enter rollno: "))
name=input("name : ")
marks=float(input('marks : '))
rec=str(rollno)+","+name+","+str(marks)+
"\n" fileout.write(rec)
fileout.close()
#code to display details of the student’s data in the file Marks.dat
def ShowData():
filein=open("Marks.dat","r"
) for i in filein:
str=filein.read()
print(str)
filein.close()
# function calling statements
Stu_Marks()
ShowData()
OUTPUT:
How many students are there in
the class : 4 Enter details of
student 1 below :-
Enter rollno:
1 name :
Bikash Dash
marks : 410
Enter details of student 2 below :-
Enter rollno: 2
name : Komal
Sharma marks :
405
Enter details of student 3 below :-
Enter rollno: 3
name : Nikita
Sahoo marks :
390
Enter details of student 4 below :-
Enter rollno: 4
name : Alok
Saha marks :
475 1,Bikash
Dash,410.0
2,Komal
Sharma,405.0
3,Nikita
Sahoo,390.0
4,Alok Saha,475.0
 8. Write a Python Program to read a text file “story.txt” line by line and display each
word separated by ‘#’ sign
and also count number of words in data file.
PROGRAM CODING :

""" To See the content of the text file STORY.txt line by line where the words
are separated by '#' & also count the words in the file."""
f=open("STORY.txt", "r") line=" "
WC=0 L=0
while line:
line=f.readline()
S=line.split()
for word in S:
WC=WC+1
print(word, end="#")
print()
L=L+1
print(" Total no of words=", WC) print("
Total no of lines=", L)
f.close()
OUTPUT:
The#Lion#and#the#Mouse#
A#lion#was#once#sleeping#in#the#jungle#when#a#mouse#started#running#up#and#down#his#body#just#
for#fun.#This#disturbed#the#lion’s#sleep,#and#he#woke#up#quite#angry.#
He#was#about#to#eat#the#mouse#when#the#mouse#desperately#requested#the#lion#to#set#him#free.#
“I#promise#you,#I#will#be#of#great#help#to#you#someday#if#you#save#me.”#The#lion#laughed#at#the
# mouse’s#confidence#and#let#him#go.#
One#day,#a#few#hunters#came#into#the#forest#and#took#the#lion#with#them.#They#tied#him#up#agai
nst#a#tree.#
The#lion#was#struggling#to#get#out#and#started#to#whimper.#Soon,#the#mouse#walked#past#and#noti
ced#the#lion#in#trouble.#Quickly,#he#ran#and#gnawed#on#the#ropes#to#set#the#lion#free.#Both#of#th
em#sped#off#into#the#jungle.#
Total no of words= 147 Total
no of lines= 6

9. Write a Python Program defining a function EMPDATA() to get employee data (EmpID,
Dept, EName, Age, Salary) from user and write onto a binary file “EDATA.dat”. The
program should be able to get data from the user and write onto the file as long as the
user wants.
Also, define another function SHOWEMPDATA() to open the existing binary file “EDATA.dat” and display the
stored employees records data ( EmpID, Dept, EName, Age, Salary) from it on screen.
PROGRAM CODING :
# To add data into a binary file “EDATA.dat”. Also read & show on the screen
import pickle def
EMPDATA():
fobj=open("EDATA.dat","wb") ans=''
print("Do you want to enter new data in the file“EDATA.dat”?")
ans=input("Press (y/N) : ")
while ans.upper()=='Y':
EmpID=input("Enter Employee's ID Number: ")
Dept=input("Enter Employee's Department :")
Ename=input("Enter Employee's Name :")
Age = int(input("Enter Employee's age : "))
Salary = float(input("Enter Employee's Salary : "))
rec=[EmpID, Dept ,Ename,Age, Salary]
pickle.dump(rec, fobj)
print("Record data entered.")
print("Do you want to continue data entry in the file?")
ans=input("Press (y/N) : ")
fobj.close()
def SHOWEMPDATA(): # create a function sp="
"
f=open("EDATA.dat","rb") try:
print(" Employees' data are given below: \n" )
while True:
Sp=pickle.load(f) # decoding the data
print(Sp)
except:
f.close()

# call the function

EMPDATA()
SHOWEMPDATA()
 OUTPUT:
Do you want to enter new data in the file“EDATA.dat”?
Press (y/N) : y
Enter Employee's ID Number: A101
Enter Employee's Department :
Sales Enter Employee's Name :
Bikash Dash Enter Employee's age :
26
Enter Employee's Salary : 25000
Enter Employee's ID Number: A102
Enter Employee's Department :
Production Enter Employee's Name :
Hemant Hota
Enter Employee's age : 30
Enter Employee's Salary : 32000
Enter Employee's ID Number: A103
Enter Employee's Department
:Marketing Enter Employee's Name
:Hohit Sahoo Enter Employee's age :
28
Enter Employee's Salary : 28000
Record data entered.
Do you want to continue data entry in
the file? Press (y/N) : n
Employees' data are given below:
['A101', 'Sales', 'Bikash Dash', 26, 25000.0]
['A102', 'Production', 'Hemant Hota', 30, 32000.0]
['A103', 'Marketing', 'Hohit Sahoo', 28, 28000.0]
10. Write a Python Program to write data in a csv file. Write records of students into
student-result.csv. The fields are as following:
Field 1 Data Type

StudentID Integer

Class String

StudentName String

Score Integer

PROGRAM CODING :
from csv import writerdef
Writedata():
#Create Header row First
f = open("student-result.csv","w",newline='\n')
dt = writer(f)
dt.writerow( [ 'Student_ID' , 'Class' , 'StudentName' , 'Score' ] )
f.close()
#Insert individual Student’s Data
f = open("student-result.csv","a",newline='\n')
while True:
st_id= int(input("Enter Student's ID:"))
Class = input("Enter Student's class:")
st_name = input("Enter Student's name:")
st_score = input("Enter score:")
dt = writer(f)
dt.writerow( [ st_id, Class, st_name, st_score ] )
ch=input("Want to insert More records?(y or Y):")
ch=ch.lower()
if ch !='y':
break
print("Record has been added.")
f.close()

# call the function

Writedata ()
 OUTPUT:
Enter Student's ID:101
Enter Student's class:X
Enter Student's name:Alisha Sahoo
Enter score:350
Want to insert More records?(y or
Y):y Record has been added.
Enter Student's ID:102
Enter Student's class:X
Enter Student's name:Binita
Saha Enter score:450
Want to insert More records?(y or
Y):y Record has been added.
Enter Student's ID:103
Enter Student's class:X
Enter Student's name:Chitra
Gupta Enter score:421
Want to insert More records?(y or Y):n
11. Read and display Students name and score from the CSV file student-result.csv with tab
delimiter.
PROGRAM CODING :
# To Read and display Students name and score from the CSV file student-result.csv
import csv
with open("student-result.csv", newline="\n") as f:data =
csv.DictReader(f)
print("Student Name","\t", "Score")
print("*"*25)
for i in data:
print(i['StudentName'], "\t",i['Score'])
OUTPUT:
Student Name Score
*************************
Alisha Sahoo 350
Binita Saha 450
Chitra Gupta 421

Q[12] Write a menu-driven python program to implement stack operation.

def check_stack_isEmpty(stk):
if stk==[]:
return True
else:
return False
s=[] # An empty list to store stack elements, initially its empty.
top = None # This is top pointer for push and pop operation
def main_menu():
while True:
print("Stack Implementation")
print("1 - Push")
print("2 - Pop")
print("3 - Peek")
print("4 - Display")
print("5 - Exit")
ch = int(input("Enter the your choice:"))
if ch==1:
el = int(input("Enter the value to push an element:"))
push(s,el)
elif ch==2:
e=pop_stack(s)
if e=="UnderFlow":
print("Stack is underflow!")
else:
print("Element popped:",e)
elif ch==3:
e=pop_stack(s)
if e=="UnderFlow":
print("Stack is underflow!")
else:
print("The element on top is:",e)
elif ch==4:
display(s)
elif ch==5:
break
else:
print("Sorry, You have entered invalid option")
def push(stk,e):
stk.append(e)
top = len(stk)-1
def display(stk):
if check_stack_isEmpty(stk):
print("Stack is Empty")
else:
top = len(stk)-1
print(stk[top],"-Top")
for i in range(top-1,-1,-1):
print(stk[i])
def pop_stack(stk):
if check_stack_isEmpty(stk):
return "UnderFlow"
else:
e = stk.pop()
if len(stk)==0:
top = None
else:
top = len(stk)-1
return e
def peek(stk):
if check_stack_isEmpty(stk):
return "UnderFlow"
else:
top = len(stk)-1
return stk[top]
main_menu() # Calling main Program.
 return True
string = input("Enter string to check:")

if isPalindrome(string):
print("Yes, the string is a palindrome")
else:
print("No, the string is not a palindrome")

Output:
Enter string to check:madam
Yes, the string is a palindrome
>>>
= RESTART: G:/CN/Term -2 Practical Record programs/Stack program -3 palindrome checking.py
Enter string to check:School
No, the string is not a palindrome
>>>
 Part B: MYSQL (5 sets of SQL queries using one/two tables)
Set [1] Queries (Database Fetching records)
Consider the following MOVIE table and write the SQL queries based on it.
Movie_ID MovieName Type ReleaseDate ProductionCost BusinessCost

M001 The Kashmir Files Action 2022/01/26 1245000 1300000

M002 Attack Action 2022/01/28 1120000 1250000

M003 Looop Lapeta Thriller 2022/02/01 250000 300000

M004 Badhai Do Drama 2022/02/04 720000 68000

M005 Shabaash Mithu Biography 2022/02/04 1000000 800000

M006 Gehraiyaan Romance 2022/02/11 1500000 120000

M007 Pushpa Action 2021/12/17 2500000 500000

a) Display all information from movie.

b) Display the type of movies.
c) Display movieid, moviename, total_eraning by showing the business done by the movies. Claculate the
business done by movie using the sum of productioncost and businesscost.
d) Display movieid, moviename and productioncost for all movies with productioncost greater than 150000
and less than 1000000.
e) Display the movie of type action and Drama.
f) Display the list of movies which are going to release in February, 2022.
Commands :
OUTPUT: (write in white page)
a) SELECT * FROM MOVIE ;
Movie_ID MovieName Type ReleaseDate ProductionCost BusinessCost

M001 The Kashmir Files Action 2022/01/26 1245000 1300000

M002 Attack Action 2022/01/28 1120000 1250000

M003 Looop Lapeta Thriller 2022/02/01 250000 300000

M004 Badhai Do Drama 2022/02/04 720000 68000

M005 Shabaash Mithu Biography 2022/02/04 1000000 800000

M006 Gehraiyaan Romance 2022/02/11 1500000 120000

M007 Pushpa Action 2021/12/17 2500000 500000

b) SELECT DISTINCT FROM MOVIE; OUTPUT :

Type

Action

Thriller

Drama

Biography

Romance
c) SELECT Movie_ID, MovieName, ProductionCost + BusinessCost “Total earning”
FROM MOVIE ; OUTPUT:
Movie_ID MovieName Total earning
M001 The Kashmir Files 2545000
M002 Attack 2370000
M003 Looop Lapeta 550000
M004 Badhai Do 788000
M005 Shabaash Mithu 1800000
M006 Gehraiyaan 1620000
M007 Pushpa 3000000
d) SELECT Movie_ID , MovieName , ProductionCost
FROM MOVIE
WHERE ProductionCost >150000 AND ProductionCost < 1000000 ; OUTPUT:

Movie_ID MovieName ProductionCost

M003 Looop Lapeta 250000

M004 Badhai Do 720000

e) SELECT MovieName
FROM MOVIE OUTPUT:
WHERE Type =’Action’ OR Type =’Drama’ ; MovieName
The Kashmir Files
Attack
Badhai Do
Pushpa
f) SELECT MovieName OUTPUT:
FROM MOVIE
MovieName
WHERE MONTH(ReleaseDate)=2 ; Looop Lapeta
Badhai Do
Shabaash Mithu
Gehraiyaan

Set [2] Queries (Based on Functions)

i. Write a query to display cube of 5. OUTPUT: → POW(5,3)
Command: SELECT POW(5,3) ; 125

ii. Write a query to display the number 563.854741 rounding off to the next hundred.
Command: SELECT ROUND(563.854741,-2); OUTPUT: → ROUND(563.854741,-2)
600

iii. Write a query to display “put” from the word “Computer”.

Command: SELECT SUBSTR(“Computer”,4,3); OUTPUT: → SUBSTR(“Computer”,4,3)
put
iv. Write a query to display today’s date into DD.MM.YYYY format.
Command: SELECT CONCAT(DAY(CURDATE()) , ‘.’ ,MONTH(CURDATE()) , ‘.’ , YEAR(CURDATE())) ;

OUTPUT: → CONCAT(DAY(CURDATE()) , ‘.’ ,MONTH(CURDATE()) , ‘.’ , YEAR(CURDATE()))

5.2.2022

SQRT(625)
v. Write a query to display square root of 625. 25

Command: SELECT SQRT(625) ; OUTPUT: →

Set [3] Queries (USE of DDL & DML Commands)

i. Create a database “Sports” and open it.

Command:
• CREATE DATABASE Sports ;
• USE Sports ;
ii. Create a table “TEAM” with following considerations:
a) It should have a column TeamID for storing an integer value between 1 to 9, which refers to unique
identification of a team.
b) Each TeamID should have its associated name (TeamName), which should be a string of length not
less than 10 characters.
c) Using table level constraint, make TeamID as the primary key.
Command:
• CREATE TABLE TEAM (TeamID int(1), TeamName varchar(10), primary key(teamid));
iii. Show the structure of the table TEAM using a SQL statement.

Command:
• DESCRIBE TEAM ;
OUTPUT
Field Type Null Key Default Extra
TeamID int(1) NO PRI NULL
TeamName varchar(10) YES NULL

iv. As per the preferences of the students four teams were formed as given below.
Insert these four rows in TEAM table:
Row 1: (1, Royal)
Row 2: (2, Toofan)
Row 3: (3, Dream)
Row 4: (4, Dhoom)
Command:
• INSERT INTO TEAM VALUES(1, ‘Royal’);
Output: Query OK, 1 row affected
• INSERT INTO TEAM VALUES(2, ‘Toofan’);
Output: Query OK, 1 row affected
• INSERT INTO TEAM VALUES(3, ‘Dream’);
Output: Query OK, 1 row affected
• INSERT INTO TEAM VALUES(4, ‘Dhoom’);
Output: Query OK, 1 row affected
v. Show the contents of the table TEAM using a DML statement.
• SELECT * FROM TEAM;

Output:

TeamID TeamName
1 Royal
2 Toofan
3 Dream
4 Dhoom

Set [4] Queries (USE of DDL & DML Commands)

i) Create a database StdRecord and open it.
Answers:
• CREATE DATABASE StdRecord;
• USE StdRecord;
ii) Now create another table STUDENTS and insert data as shown below. Choose appropriate data types
and constraints for each attribute.
StudentID StudentNM DOB Stream Class Grade Stipend

S1 Aditya Dash 2004/2/20 Humanities XII B 800

S2 Sonali Kar 2003/3/21 Science XI A 950

S3 Sudipta Saha 2004/2/22 Science XII C 550

S4 Maitree Das 2005/1/23 Commerce XII B 800

S5 Mohit Varma 2003/9/24 Science XI A 900

S6 Gita Sahoo 2005/2/25 Humanities XII B 850

S7 Reena Sharma 2003/12/25 Commerce XI C 550

S8 Nikhil Hota 2004/8/15 Humanities XI C 500

Answers:
• CREATE TABLE STUDENTS
-> (StudentID varchar(2) NOT NULL primary key,
-> StudentNM varchar(25),
-> DOB date,
-> Stream varchar(25),
-> Class varchar(25),
-> Grade varchar(25) CHACK(Grade IN (‘A’,‘B’,‘C’)),
-> Stipend int(4));

• INSERT INTO STUDENTS VALUES(S1,‘Aditya Dash’,‘2004/2/20’,‘Humanities’,‘XII’,‘B’,800);

Output: Query OK, 1 row affected
• INSERT INTO STUDENTS VALUES(S2,‘Sonali Kar’,‘2003/3/21’,‘Science’,‘XI’,‘A’,950);
 Output: Query OK, 1 row affected
• INSERT INTO STUDENTS VALUES(S3,‘Sudipta Saha’,‘2004/2/22’,‘Science’,‘XII’,‘C’,550);
Output: Query OK, 1 row affected
• INSERT INTO STUDENTS VALUES(S4,‘Maitree Das’,‘2005/1/23’,‘Commerce’,‘XII’,‘B’,800);
Output: Query OK, 1 row affected
• INSERT INTO STUDENTS VALUES(S5,‘Mohit Varma’,‘2003/9/24’,‘Science’,‘XI’,‘A’, 900);

Output: Query OK, 1 row affected

• INSERT INTO STUDENTS VALUES(S6,‘Gita Sahoo’,‘2005/2/25’,‘Humanities’,‘XII’,‘B’,850);
Output: Query OK, 1 row affected
• INSERT INTO STUDENTS VALUES(S7,‘Reena Sharma’,‘2003/12/25’,‘Commerce’,‘XI’,‘C’,550);
Output: Query OK, 1 row affected
• INSERT INTO STUDENTS VALUES(S8,‘Nikhil Hota’,‘2004/8/15’,‘Humanities’,‘XI’,‘C’,500);
Output: Query OK, 1 row affected
iii) Display the students record those have grade not ‘C’.

Answer: SELECT * FROM STUDENTS WHERE Grade !=’C’;

Output:
StudentID StudentNM DOB Stream Class Grade Stipend

S1 Aditya Dash 2004/2/20 Humanities XII B 800

S2 Sonali Kar 2003/3/21 Science XI A 950

S4 Maitree Das 2005/1/23 Commerce XII B 800

S5 Mohit Varma 2003/9/24 Science XI A 900

S6 Gita Sahoo 2005/2/25 Humanities XII B 850

iv) Display Name, class, stream of the students getting stipend not more than 600.

Answer: SELECT StudentNM, Class, Stream FROM STUDENTS WHERE Stipend<=600;

OUTPUT: StudentNM Class Stream

Sudipta Saha XII Science

Reena Sharma XI Commerce

Nikhil Hota XI Humanities

v) Display student’s Name, and the stipend paid to commerce students.

Answer: SELECT StudentNM, Stipend FROM STUDENTS WHERE Stream = ‘Commerce’;

OUTPUT: StudentNM Stipend

Maitree Das 800

Reena Sharma 550

vi) List out the students name and stipend amount those names starts with letter ‘M’.

Answer: SELECT StudentNM, Class, Stipend FROM STUDENTS WHERE StudentNM LIKE ‘M%’;
 OUTPUT:
StudentNM Class Stipend

Maitree Das XII 800

Mohit Varma XI 900

vii) Display maximum and minimum stipend of the students from the Table STUDENTS.

Answer: SELECT MAX(Stipend) “Maximum Stipend”, MIN(Stipend) “Minimum Stipend” FROM STUDENTS;

OUTPUT: Maximum Stipend Minimum Stipend

900 900

viii) Display Total amount and average of stipend of all the students from the Table STUDENTS.
Answer: SELECT SUM(Stipend) “Total Stipend”, AVG(Stipend) “Average Stipend” FROM STUDENTS;

OUTPUT: Total Stipend Average Stipend

5900 737.5

ix) Modify the stipend amount and increase it by 100, for all students who get stipend less than 600.

Answer: UPDATE STUDENTS SET Stipend = Stipend + 100 WHERE Stipend < 600;
x) Add one column percentMark of data type decimal to the table STUDENNTS.

Answer: ALTER TABLE STUDENTS ADD (percentMark decimal);

xi) Display the students records by ascending order of their name those have grade ‘B’and ‘A’.

Answer: SELECT * FROM STUDENTS WHERE Grade =’A’ OR Grade =’B’ ORDER BY StudentNM;

OUTPUT: StudentID StudentNM DOB Stream Class Grade Stipend

S1 Aditya Dash 2004/2/20 Humanities XII B 800

S6 Gita Sahoo 2005/2/25 Humanities XII B 850

S4 Maitree Das 2005/1/23 Commerce XII B 800

S5 Mohit Varma 2003/9/24 Science XI A 900

S2 Sonali Kar 2003/3/21 Science XI A 950

xii) Display the number of students are getting stipend more than 500 stream-wise in table STUDENTS.

Answer: SELECT Stream , COUNT(*) FROM STUDENTS GROUP BY Stream HAVING Stipend>500;

OUTPUT: Stream COUNT(*)

Humanities 2
Science 3
Commerce 2