#P1 :TO convert Decimal Number system to any Number System

ct=['0','1','2','3','4','5','6','7','8','9','A','B','C','D','E','F']
print('''Select the number system:
2 for binary
8 for octal
16 for hexadecimal \n''')
d=int(input("Enter your choice :"))
x=int(input("Enter a number in Decimal ="))
y=''
while x>0:
r=x%d
y=ct[r]+y
x=x//d
if d==2:
print("Binary value=",y)
elif d==8:
print("Octal value= ",y)
else:
print("Hexadecimal value=",y)

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

#P2 : Write a program to input the value of x and n and print the sum of the
following series
# x - x²/2! + x³/3! - x⁴/4! - ...... xⁿ/n!

a = int(input("Enter the number: "))

b = int(input("Enter the power: "))
s, f = 0, 1
for x in range(1, (b+1)):
for y in range(1, (x+1)):
f*=y # f=f*y
if x%2==0:
s = s-((a**x)/f)
else:
s = s+((a**x)/f)
f=1
print("Sum = ",s)

----------------------------------------
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

#P3 : Generates Random Number simulates a Dice.

print("A program to generate a random number between 1 and 6.")
import random
a='y'
while a=='y' or a=='Y':
dice=random.randrange(1,7) # a global variable to store a random number
print("The random number generated is: ",dice)
a=input("press 'Y' to Continue or Any Key to exit...... ")
print('-*' * 30)

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

-------------------------------------
poem.txt file

RAIN
Rain, rain, go away
Come again another day
Daddy wants to play
Rain, rain go away
Rain, rain, go away

Come again another day

Mommy wants to play
Rain, rain, go away
Rain, rain, go away
Come again another day

------------------------------------------
# P4 Read a text file line by line and display each word seperatly by a # sign

f=open("poem.txt",'r')
s=f.readlines()
for line in s:
words=line.split()
for word in words:
print(word+"#",end='')
print("")

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

# P5 : READ A TEXT FILE AND DISPLAY THE NUMBER OF

# VOWELS/ CONSONANTS/ UPPERCASE/ LOWERCASE CHARACTERS IN THE FILE.
v=c=u=l=0
vower=['a','e','i','o','u','A','E','I','O','U']
f=open('poem.txt','r')
lines=f.read()
print("The given file is : ")
print("----------------------------")
print(lines)
print('----------------------------')
for char in lines:
if char.isalpha()==True:
if char in vower:
v=v+1
else:
c=c+1
if ord(char)<96:
u=u+1
else:
l=l+1
print("Number of Vower :",v)
print("Number of Consonants :",c)
print("Number of Upper case :",u)
print("Number of Lower case :",l)
f.close()

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
# P6 : remove all the lines containg 'a' and write in a new file.
f=open('poem1.txt','r')
n=open('new_poem1.txt','w')
fn=open('temp_poem1.txt','w')
print("These lines are in the file: ",f.name,'\n')
print(f.read())
print('-'*50,'\n')
f.seek(0)
print("These Lines containg 'a' were Writen To the New file :",n.name,'\n')
s=f.readline()
while 1:
for x in s:
if x=='a':
print(s,end='')
n.write(s)
break
else:
fn.write(s)
s=f.readline()
if not s:
break
print('\n',"-"*50,'\n')
f.close()
fn.close()

f=open('poem1.txt','w')
fn=open('temp_poem1.txt','r')
tem=fn.read()
f.write(tem)
print("These lines not containg 'a' were remain in The Old file: ",f.name)
print(tem)
n.close()
fn.close()
f.close()

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

print("# P7 :Application of seek() and tell()")

fobject=open("testfile.txt","w") # creating a data file
sentence=input("Enter the contents to be written in the file: ")
fobject.write(sentence) # Writing data to the file
fobject.close() # Closing a file
print("\n -------Now reading the contents of the file: -------")
fileobject=open("testfile.txt","r+")
str=fileobject.read()
print(str)
print("\n -------Initially------\n , the position of the file object is:
",fileobject.tell())
fileobject.seek(0)
print("By -> seek(0) file object is at the beginning of the
file:",fileobject.tell())
fileobject.seek(5)
print("By -> seek(5) moving to 5th byte position from the beginning of file")
print("Read from current position of the file object :", fileobject.tell())
str=fileobject.read()
print(str)

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

#P8 and P9 : BINARY FILE HANDLING

import pickle
#Accepting data for Dictionary
def insertRec():
rollno = int(input('Enter roll number : '))
name = input('Enter Name : ')
marks = int(input('Enter Marks : ')) #Creating the dictionary
rec = {'Rollno':rollno,'Name':name,'Marks':marks}
#Writing the Dictionary
f = open('student.dat','ab')
pickle.dump(rec,f)
f.close()

#Reading the records

def readRec():
f = open('student.dat','rb')
while True:
try:
rec = pickle.load(f)
print('Roll Num:',rec['Rollno'],end='\t')
print('Name:',rec['Name'],end='\t')
print('Marks:',rec['Marks'])
except EOFError:
break
f.close()

#Searching a record based on Rollno

def searchRollNo(r):
f = open('student.dat','rb')
flag = False
while True:
try:
rec = pickle.load(f)
if rec['Rollno'] == r:
print('Roll Num:',rec['Rollno'],end='\t')
print('Name:',rec['Name'],end='\t')
print('Marks:',rec['Marks'])
flag = True
except EOFError:
break
if flag == False:
print('No Records found')
f.close()

#Marks Modification for a RollNo

def updateMarks(r,m):
f = open('student.dat','rb')
reclst = []
while True:
 try:
rec = pickle.load(f)
reclst.append(rec)
except EOFError:
break
f.close()
for i in range (len(reclst)):
if reclst[i]['Rollno']==r:
reclst[i]['Marks'] = m
f = open('student.dat','wb')
for x in reclst:
pickle.dump(x,f)
f.close()

#Deleting a record based on Rollno

def deleteRec(r):
f = open('student.dat','rb')
reclst = []
while True:
try:
rec = pickle.load(f)
reclst.append(rec)
except EOFError:
break
f.close()
f = open('student.dat','wb')
for x in reclst:
if x['Rollno']==r:
continue
pickle.dump(x,f)
f.close()
while True:
print('-'*50)
print('Type 1 to insert rec.')
print('Type 2 to display rec.')
print('Type 3 to Search RollNo.')
print('Type 4 to update marks.')
print('Type 5 to delete a Record.')
print('Type 6 to EXIT.')
choice = int(input('\t Enter you choice: '))
if choice == 1:
insertRec()
elif choice == 2:
readRec()
elif choice == 3:
r = int(input('Enter a rollno to search: '))
searchRollNo(r)
elif choice == 4:
r = int(input('Enter a rollno: '))
m = int(input('Enter new Marks: '))
updateMarks(r,m)
elif choice == 5:
r = int(input('Enter a rollno: '))
deleteRec(r)
elif choice == 6:
print('Thanks .. ! ')
break
else:
print('Wrong Choice ')
---------------------------------------------------------

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
theory '''CSV (Comma Separated Values) is a simple file format
used to store tabular data, such as a spreadsheet or database.
A CSV file stores tabular data (numbers and text)
in plain text. Each line of the file is a data record.
Each record consists of one or more fields,
separated by commas. The use of the comma as a field
separator is the source of the name for this file format.

For working CSV files in python, there is an inbuilt

module called csv.'''
---------------------------------------------------
# P10 csv file writing
import csv
fields = ['Name', 'Branch', 'Year', 'CGPA'] # field names
# data rows of csv file
rows = [ ['Nikhil', 'COE', '2', '9.0'],
['Sanchit', 'COE', '2', '9.1'],
['Aditya', 'IT', '2', '9.3'],
['Sagar', 'SE', '1', '9.5'],
['Prateek', 'MCE', '3', '7.8'],
['Sahil', 'EP', '2', '9.1']]
filename = "XIIACS.csv"
with open(filename, 'w') as csvfile:
# creating a csv writer object
csvwriter = csv.writer(csvfile)
# writing the fields
csvwriter.writerow(fields)
# writing the data rows
csvwriter.writerows(rows)

-----------------------------------------------------------

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

# P11 importing Data from CSV File

import csv
filename = "XIIACS.csv"
fields = []
rows = []
# reading csv file
with open(filename, 'r') as csvfile:
# creating a csv reader object
csvreader = csv.reader(csvfile)
# extracting each data row one by one
for row in csvreader:
rows.append(row)
# get total number of rows
print("Total no. of rows: %d"%(csvreader.line_num),' in ',filename)
for row in rows[:]:
# parsing each column of a row
for col in row:
 print("%10s"%col, end=" ")
print()

---------------------------------------------------------

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

# P12 CSV File Create ID Password And Search by ID.

import csv
fields = ['User_ID' , 'Name' , 'Password']
f = open('p11db.csv' , 'w' , newline = '')
wr=csv.writer(f)
wr.writerow(fields)
f.close()

def entering():
f = open('p11db.csv' , 'a' , newline = '')
wr=csv.writer(f)
a='y'
while a=='y' or a=='Y':
print("\t Enter a New Record : ")
i=int(input("Enter a New ID No: "))
n=input("Enter the User Name : ")
p=input("Enter the new password : ")
r=[i,n,p]
wr.writerow(r)
print('any key to exit...')
a=input("press 'y' to continue... ")
f.close()

def readdata():
f = open('p11db.csv' , 'r')
rd=csv.reader(f)
print("\n \t DATA in the CSV File : ")
for i in rd :
print(i)
f.close()

def searchID():
s = input('Enter a rollno to search: ')
f = open('p11db.csv','r')
rd=csv.reader(f)
print("\n \t The CSV File Search : ")
for i in rd :
if i[0]==s:
print(i)
break
else:
print('ID not Found..')
f.close()

while True:
print('\t','-*'*18)
print(' Enter 1 For Create New ID and Password.')
print(' Enter 2 For Read All ID Details.')
print(' Enter 3 For SEARCH password of a ID.')
print(' Enter 4 For EXIT Program..')
choice = int(input('\t Enter you choice: '))
 if choice == 1:
entering()
elif choice == 2:
readdata()
elif choice == 3:
searchID()
elif choice == 4:
print('Thanks .. ! ')
break

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

print("P13 PROGRAM TO IMPLEMENT A STACK Without Function : ")

li=[]
while True:
print("\n1 FOR PUSH : ")
print("2 FOR POP : ")
print("3 FOR DISPLAY : ")
print("4 FOR EXIT : ")
choice=int(input("Enter your choice:"))
if(choice==1):
n=int(input("New number : "))
li.append(n)
elif(choice==2):
print('The Deleted Element : ',li.pop())
elif(choice==3):
print(li)
elif(choice==4):
break
else:
print("Wrong input or invalid... ")
input("for continue press enter : ")

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
P14: Implement a stack
print("PROGRAM TO IMPLEMENT A STACK ..... ")
def push(li):
print(' New Employee details : ')
eid=int(input("Eid number: "))
ename=input("Employee name : ")
new=[eid,ename]
li.append(new)
def popele(li):
if(li==[]):
print("Stack is Empty...")
else:
print("Deleted Employee is:",li.pop())
def display(li):
print(li)
stack=[]
while True:
print("\n1 FOR PUSH : ")
print("2 FOR POP : ")
print("3 FOR DISPLAY : ")
print("4 FOR EXIT : ")
 choice=int(input("Enter your choice:"))
if(choice==1):
push(stack)
elif(choice==2):
popele(stack)
elif(choice==3):
display(stack)
elif(choice==4):
break
else:
print("Wrong input or invalid... ")
input("for continue press enter : ")

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

print("P-15 PROGRAM TO IMPLEMENT A QUEUE of student roll, name & age ")
def enqueue(qq):
print(' New Student details : ')
a=int(input("roll number: "))
b=input("Student name : ")
c=int(input("Age of student : "))
s=[a,b,c]
qq.append(s)
def dequeue(qq):
if(qq==[]):
print("Queue is Empty...")
else:
print("Deleted element is:",q[0])
qq.pop(0)
def display(qq):
print(qq)

q=[]
while True:
print("\n 1 FOR PUSH : ")
print("2 FOR POP : ")
print("3 FOR DISPLAY : ")
print("4 FOR EXIT : ")
choice=int(input("Enter your choice:"))
if(choice==1):
enqueue(q)
elif(choice==2):
dequeue(q)
elif(choice==3):
display(q)
elif(choice==4):
break
else:
print("Wrong input or invalid... ")
input("for continue press enter : ")

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

print('P16: Implementation of selection sort using function.')

def selection_Sort(list2):
flag = 1 #to decide when to swap
n=len(list2)
for i in range(n): # Traverse through all list elements
min = i
 for j in range(i + 1, len(list2)): # the left elements
if list2[j] < list2[min]: # element at j is smaller
min = j
flag = 1
if flag == 1 : # next smallest element is found
list2[min], list2[i] = list2[i], list2[min]
numList=[]
n = int(input("enter number of elements in your list : "))
for a in range(n):
print('ele_no ',a+1,end=' ')
numList.append(int(input("enter : ")))
print('your list is : ',numList)
selection_Sort(numList)
print ('The sorted list is :',numList)

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
# P17 Create and List the Databaes.
import mysql.connector
mydb=mysql.connector.connect(host="localhost",user="root",passwd="davalkusa")
print("Name of the DB object variable : ")
print(mydb)
mycursor=mydb.cursor()
mycursor.execute('create database ex1xii')
mycursor.execute('show databases')
print('MySQL Database List: ')
i=1
for x in mycursor:
print(i,' - ',x)
i+=1

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

# P 18: Insert recorts in the EMP Table for SQL Practical.

import mysql.connector
mydb=mysql.connector.connect(host="localhost",user="root",passwd="kvuc",database="c
sxii")
mycursor=mydb.cursor()
c='y' or 'Y'
while c=='y'or c=='Y':
print('enter a new EMP details : ')
a=int(input("enter your ENO : "))
b=input("enter the Ename : ")
c=input(" enter the JOB : ")
d=int(input("enter the MGR : "))

e=input("enter the DOJ : ")

f=int(input("enter the SAL : "))
g=int(input("enter the COMM : "))
h=int(input("enter the DEPT NO : "))

sc="insert into emp values(%s,%s,%s,%s,%s,%s,%s,%s)"

val=(a,b,c,d,e,f,g,h)
mycursor.execute(sc,val)
mydb.commit()
c=input('To enter more Employee .. prass y')
mydb.commit()
mydb.close()
Output-P18

This Program is used to insert data in SQL practical

================================================================
================================================================

MYSQL Practicals1: using one table or relation EMP.

Write the SQL commands for the Questions.

Q1. To create the given table.

mysql> create table emp(eno int(4), ename varchar(30), job varchar(20), mgr int(4),
doj date, sal float, comm float(6,2),deptno int(2));

Q2. Describe the created table.

mysql> desc emp;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| eno | int(4) | YES | | NULL | |
| ename | varchar(30) | YES | | NULL | |
| job | varchar(20) | YES | | NULL | |
| mgr | int(4) | YES | | NULL | |
| doj | date | YES | | NULL | |
| sal | float | YES | | NULL | |
| comm | float(6,2) | YES | | NULL | |
| deptno | int(2) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+

Q3. To view all the column from the table.

mysql> select * from emp;

+------+---------------+-----------+------+------------+------+---------+--------+
| eno | ename | job | mgr | doj | sal | comm | deptno |
+------+---------------+-----------+------+------------+------+---------+--------+
| 7369 | Sunita Sharma | clerk | 7902 | 1980-01-17 | 2800 | NULL | 20 |
| 7499 | Ashok Singhal | salesman | 7698 | 1981-02-20 | 3600 | 300.00 | 30 |
| 7521 | Rohit Rana | salesman | 7698 | 1981-02-22 | 5250 | 500.00 | 30 |
| 7566 | Jyoti Lamba | Manager | 7839 | 1981-04-02 | 4975 | NULL | 20 |
| 7654 | Martin S. | Salesman | 7698 | 1981-09-28 | 6250 | 1400.00 | 30 |
| 7698 | Binod Goel | Manager | 7839 | 1981-05-01 | 5850 | NULL | 30 |
| 7782 | Chetan Gupta | Manager | 7839 | 1981-06-09 | 2450 | NULL | 10 |
| 7788 | Sudhir Rawat | Analyst | 7566 | 1987-04-19 | 5000 | NULL | 20 |
| 7839 | Kavita Sharma | President | NULL | 1981-11-17 | 5000 | NULL | 10 |
| 7844 | Tushar Tiwari | Salesman | 7698 | 1981-09-08 | 4500 | NULL | 30 |
| 7876 | Anand Rathi | Clerk | 7788 | 1987-05-23 | 6100 | NULL | 20 |
| 7900 | Jaydeep Rana | Clerk | 7698 | 1981-12-03 | 4950 | NULL | 30 |
| 7902 | Sumit Vats | Analyst | 7566 | 1981-12-03 | 3500 | 3600.00 | 20 |
| 7934 | Manoj Kaushik | Clerk | 7782 | 1982-01-23 | 5300 | NULL | 10 |
+------+---------------+-----------+------+------------+------+---------+--------+
14 rows in set (0.00 sec)

Q4.To list all name and employee number from the table.
mysql> select ename,eno from emp;

Q5. To list all name , date of joining and salary of the all employees.
mysql> select doj,sal from emp;

Q6. To display the employee name and the incremented value of salary as salary +
300.
mysql> select ename,sal+300 as 'sal+300' from emp;

Q7.To list the employee name and its annual salary =12 x salary + 100 .
mysql> select ename,sal*12+100 as 'annual salary' from emp;

Q8. Display name and salary where commission is null.

mysql> select * from emp where comm is null;

Q9.To list the distinct department number from the table.

mysql> select distinct(deptno) from emp;

Q10. To display the unique jobs from the table.

mysql> select distinct(job) from emp;

Q11. To list the salary where salary is less than commission.

mysql> select sal from emp where sal<comm;

Q12. To list the salary between 3000 and 7000.

mysql> select sal from emp where sal between 3000 and 4000;

Q13. To list the ename where manager code are in 7902,7566 and 7788.
mysql> select ename from emp where mgr in(7902,7566,7788);

Q14. To list the name starting with 's'.

mysql> select * from emp where ename like 's%';

Q15. To display all the columns in the ascending order of date of joining.
mysql> select * from emp order by doj;

Q16. To list all the columns in the ascending order of department number and
decreasing order of salary
mysql> select * from emp order by deptno , sal desc;

Q17. Display the employee name and job of employees date of joining between 20
February 1981 and 1st Nov 1981.
mysql> select ename ,job from emp where doj between '1981-02-20' and '1981-11-01';

Q18. Display the name and department number of all employees in department 20 and
30 alphabetically order by in name.
mysql> select ename , deptno from emp where deptno in(20,30) order by ename;

Q19. To List all the employees who do not have manager.

mysql> select * from emp where mgr is null;

Q20. To list name and salary of all employees who earn commissionmysql> select
ename ,sal from emp where comm is not null;

Q21. To list the name of all employee where second letter of their name is a.
mysql> select ename from emp where ename like '_a%';
Q22. To list the name and job of all the employees who work in department 20 and
their manager is 7788
mysql> select ename ,job from emp where deptno =20 and mgr=7788;

Q24. To list the department number job and sum of the salaries group by department
number and job.
mysql> select deptno,job,sum(sal) from emp group by deptno,job;

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
MYSQL Practicals2:
Write the output of the following commands.

mysql> select ename, 12*sal + comm from emp where ename='ashok singhal';
+---------------+---------------+
| ename | 12*sal + comm |
+---------------+---------------+
| Ashok Singhal | 43500 |
+---------------+---------------+

mysql> select ename from emp where ename like '%t%t%';

+---------------+
| ename |
+---------------+
| Chetan Gupta |
| Tushar Tiwari |
| Sumit Vats |
+---------------+

mysql> select ename, mgr from emp where mgr is null;

+---------------+------+
| ename | mgr |
+---------------+------+
| Kavita Sharma | NULL |
+---------------+------+

mysql> select avg(comm) from emp;

+-------------+
| avg(comm) |
+-------------+
| 1450.000000 |
+-------------+

mysql> select count(*) from emp where deptno=20;

+----------+
| count(*) |
+----------+
| 5 |
+----------+

mysql> select count(comm) from emp where deptno=20;

+-------------+
| count(comm) |
+-------------+
| 1 |
+-------------+

mysql> select deptno,max(sal) from emp group by deptno having max(sal)>2900;

+--------+----------+
| deptno | max(sal) |
+--------+----------+
| 10 | 5300 |
| 20 | 6100 |
| 30 | 6250 |
+--------+----------+

================================================================
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

P19 and P20: Database handling to create database and table , insert, display,
delete,records

import mysql.connector
import os
mydb=mysql.connector.connect(host="localhost",user="root",passwd="kvuc")

def display_msg():
mycursor=mydb.cursor()
mycursor.execute("select * from stu")
row=mycursor.fetchone()
while row is not None:
print("--->",row)
row=mycursor.fetchone()

def create_table():
try:
mycursor = mydb.cursor()
mycursor.execute("CREATE DATABASE school")
mycursor.execute("use school")
mycursor.execute("CREATE TABLE stu (ROLL INT(2),SNAME VARCHAR(30),DOB
DATE,ADDRES VARCHAR(50))")
print("Table Created")
except:
mycursor = mydb.cursor()
mycursor.execute("use school")
print("Databse or Table Already Created")

def add_msg():
mycursor=mydb.cursor()
ro=input("Enter Stu Roll: ")
sn=input("Enter the Name of Student: ")
dob=input("Enter dob yyyy-mm-dd : ")
ad=input("Enter the Address : ")

sql="INSERT INTO stu(ROLL,SNAME,DOB,ADDRES) VALUES (%s,%s,%s,%s)"

val=(ro,sn,dob,ad)
mycursor.execute(sql,val)
mydb.commit()
print(mycursor.rowcount, "Record inserted.")

def delete_msg():
mycursor=mydb.cursor()
ro=input('Enter roll for Delete the Record')
 sql="DELETE FROM stu WHERE ROLL='%s'"%ro
mycursor.execute(sql)
print('ROLL No : ',ro," record is deleted ")
mydb.commit()

def Main_Menu():
print("Enter 1 : TO ADD NEW Student")
print("Enter 2 : TO DISPLAY STUDENT LIST")
print("Enter 3 : TO DELETE Student from the list")
print("Enter others to Exit")
try:
userInput = input("Please Select An Above Option: ")
if (userInput=='1'):
print("\n")
add_msg()
elif(userInput=='2'):
display_msg()
elif (userInput=='3'):
delete_msg()
else:
print("Enter correct choice. . . ")
ch = input("\nwant to continue Y/N: ")
if(ch == 'Y' or ch=='y'):
Main_Menu()
else:
print("Program going to Exit")
exit("\n! Thanks")
except():
print("Something Wrong in code")

create_table()
Main_Menu()

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
# P21 To view databases & its tables in The MySQL.
import mysql.connector
mydb=mysql.connector.connect(host="localhost",user="root",passwd="kvuc")
mycursor=mydb.cursor()
mycursor.execute('show databases')
print('MySQL Database List: ')
for x in mycursor:
print(x)
print(' \n Enter the Database to view its tables. ')
dbn=input('DataBase Name : ')
sql="use %s"%dbn
mycursor.execute(sql)
mycursor.execute('show tables')
for x in mycursor:
print(x)
t=input('Enter Table : ')
print(" The Content of Table : ",t)
mycursor.execute("desc %s"%t)
for x in mycursor:
print(x[0],end='\t \t')
print('\n','- * - '*20)
mycursor.execute("select * from %s"%t)
for x in mycursor:
 print(x)

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xx

MYSQL Practicals3:

TABLE : doctor
+------+----------------+------------+--------+------------+
| ID | DNAME | DEPT | GENDER | EXPERIENCE |
+------+----------------+------------+--------+------------+
| 101 | JOHN | ENT | M | 12 |
| 104 | SEJAL | ORTHOPEDIC | F | 5 |
| 107 | DOLLY MISHRA | CARDIOLOGY | F | 10 |
| 104 | DEEPACK VISHWA | SKIN | M | 3 |
| 105 | JOHNSAN | MEDICINE | M | 4 |
| 114 | LARA | SKIN | F | 3 |
| 109 | K GEORGE | MEDICINE | F | 9 |
| 117 | LUCKY | ENT | F | 3 |
| 111 | BILL | MEDICINE | F | 12 |
| 130 | MORPHY | ORTHOPEDIC | M | 15 |
+------+----------------+------------+--------+------------+

TABLE : SALARY
+------+-------+-----------+-------------+
| ID | BASIC | ALLOWANCE | CONSULATION |
+------+-------+-----------+-------------+
| 101 | 12000 | 1000 | 300 |
| 104 | 23000 | 2300 | 500 |
| 107 | 32000 | 4000 | 500 |
| 114 | 12000 | 5200 | 100 |
| 109 | 42000 | 1700 | 200 |
| 105 | 18900 | 1690 | 300 |
| 130 | 21700 | 2600 | 300 |
| 130 | 40000 | 3200 | 600 |
+------+-------+-----------+-------------+

Q1. Display NAME OF ALL DOCTORS WHO ARE IN MEDICINE DEPT HAVING MORE THAN 10years
EXPERIENCE.
mysql> select dname from doctor where dept='medicine' and experience>10;
+-------+
| dname |
+-------+
| BILL |
+-------+

Q2. DISPLAY DOCTOR NAME , DEPT AND NET_SALARY = BASIC + ALLOWANCE OF ALL FEMALE
DOCTOR.
mysql> select d.dname, d.dept, s.basic+s.allowance as Net_salary from doctor
d,salary s where d.id=s.id and gender='f';
+--------------+------------+------------+
| dname | dept | Net_salary |
+--------------+------------+------------+
| SEJAL | ORTHOPEDIC | 25300 |
| DOLLY MISHRA | CARDIOLOGY | 36000 |
| LARA | SKIN | 17200 |
| K GEORGE | MEDICINE | 43700 |
+--------------+------------+------------+

Q3. DISPLAY MINIMUM ALLOWANCE OF ALL MALE DOCTOR DEPARTMENT WISE.

mysql> select dept,min(allowance) from doctor,salary where doctor.id =salary.id and
gender='M' group by dept;
+------------+----------------+
| dept | min(allowance) |
+------------+----------------+
| ENT | 1000 |
| MEDICINE | 1690 |
| ORTHOPEDIC | 2600 |
| SKIN | 2300 |
+------------+----------------+

Q4. TO DISPLAY MAXIMUM CONSULATION OF EACH GENDER.

mysql> select gender,max(s.consulation) from doctor d,salary s where d.id=s.id
group by gender;
+--------+--------------------+
| gender | max(s.consulation) |
+--------+--------------------+
| F | 500 |
| M | 600 |
+--------+--------------------+

Q5. Display all information in natural join of more than 4 year experience doctor
in each department.
mysql> select * from doctor d natural join salary s group by dept having
experience>4;
+------+--------------+------------+--------+------------+-------+-----------
+-------------+
| ID | DNAME | DEPT | GENDER | EXPERIENCE | BASIC | ALLOWANCE |
CONSULATION |
+------+--------------+------------+--------+------------+-------+-----------
+-------------+
| 107 | DOLLY MISHRA | CARDIOLOGY | F | 10 | 32000 | 4000 |
500 |
| 101 | JOHN | ENT | M | 12 | 12000 | 1000 |
300 |
| 104 | SEJAL | ORTHOPEDIC | F | 5 | 23000 | 2300 |
500 |
+------+--------------+------------+--------+------------+-------+-----------
+-------------+