PRACTICAL FILE
docx
keyboard_arrow_up
School
University of California, Santa Cruz *
*We aren’t endorsed by this school
Course
3
Subject
Information Systems
Date
Jun 14, 2024
Type
docx
Pages
16
Uploaded by ColonelOwlPerson28
PROGRAM NO 1
Problem Statement(Question-1):
1. Create a products table (Pno, Pname, Rate, QOH) in MySQL and insert data. Implement the
following SQL commands on the products table:
Source Code:
create database Q;
use Q;
create table products
(Pno int,
Pname varchar(30),
Rate int,
QOH int);
INSERT INTO products VALUES(1,'MAGGIE',30,4);
INSERT INTO products VALUES(2,'OREO',25,5);
INSERT INTO products VALUES(3,'LAYS',20,3);
INSERT INTO products VALUES(4,'SUGAR',120,2);
INSERT INTO products VALUES(5,'TATA SALT',120,3);
COMMIT;
select*from products; Output:
Problem Statement(Question-1):
GROUP BY and find the min, max, sum, count and average
Source Code:
select Rate,COUNT(*)
from products
GROUP BY Rate;
SELECT COUNT(Rate) FROM products WHERE Rate=120;
SELECT MIN(Rate) FROM products;
SELECT MAX(Rate) FROM products;
SELECT SUM(Rate) FROM products;
SELECT AVG(Rate) FROM products;
Output:
Problem Statement(Question-1):
DELETE to remove tuple(s)
Source Code:
DELETE FROM products where Pname='MAGGIE';
Output:
Problem Statement(Question-1):
ORDER By to display data in ascending / descending order
Source Code:
select*from products
order by Rate DESC;
select*from products
order by Rate ASC;
Output:
Problem Statement(Question-1):
UPDATE table to modify data
Source Code:
update products
set Pname='SUGAR',Rate=50
where Pno=1;
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Output:
Problem Statement(Question-1):
ALTER table to add new attributes / modify data type / drop attribute
Source Code:
alter table products
add Cname varchar(15);
Output:
Source Code:
alter table products
drop column QOH;
Output:
PROGRAM 3
3. Write SQL Commands for the following on the basis of the given table GRADUATE:
SOURCE CODE FOR THE TABLE:
SOURCE CODE
Create table GRADUATE
(SLNo INTEGER,
NAME Varchar(25),
STIPEND INTEGER,
SUBJECT VARCHAR(30),
AVERAGE INTEGER,
RANK INTEGER);
Insert into GRADUATE values(1,'KARAN',400,'PHYSICS',68,1);
Insert into GRADUATE values(2,'RAJ',450,'CHEMISTRY',68,1);
Insert into GRADUATE values(4,'DIVYA',350,'CHEMISTRY',63,1);
Insert into GRADUATE values(5,'GAURAV',500,'PHYSICS'70,1);
Insert into GRADUATE values(6,'MANAV',400,'CHEMISTRY',55,2);
Insert into GRADUATE values(7,'VARUN',250,'MATHS',64,1);
Insert into GRADUATE values(8,'LIZA',450,'COMPUTER',68,1);
Insert into GRADUATE values(9,'PUJA',500,'PHYSICS',62,1);
Insert into GRADUATE values(8,'NISHA',300,'COMPUTER',57,2);
OUTPUT: (i)
List the names of those students who have obtained rank 1 sorted by NAME.
SOURCE CODE:
SELECT NAME FROM GRADUATE WHERE RANK=1 ORDER BY NAME;
OUTPUT:
(ii)
Display a list of all those names whose AVERAGE is greater than 65.
SOURCE CODE
: SELECT NAME FROM GRADUATE WHERE AVERAGE>65;
OUTPUT
:
(iii) Display the names of those students who have opted COMPUTER as a SUBJECT with an
AVERAGE of more than 60.
SOURCE CODE:
SELECT NAME FROM GRADUATE WHERE SUBJECT=’COMPUTER’ AND AVERAGE>60;
OUTPUT
: (IV)
List the names of all the students in alphabetical order.
SOURCE CODE
: SELECT NAME FROM GRADUATE ORDER BY NAME;
OUTPUT:
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
PROGRAM 4
4. Write a menu driven program in Python to connect to a MySQL database-company, and
performs add, modify, delete operations on a table employee. Also it should give options to
view all or selective records from the table.
#insert def insert():
import mysql.connector
import random
db=mysql.connector.connect(host='localhost',user='root',password='SHIKHAR',database='comp
any')
mycursor=db.cursor()
empno=input("Enter Employee No.:")
name=input("Enter Employee Name:")
job=input("Enter Employee job:")
salary=input("Enter Employee salary:") query="insert into employee(empno,ename,job,salary) values('{}','{}','{}','{}')".format(empno,name,job,salary)
mycursor.execute(query)
db.commit()
# View Employee
def view():
db=mysql.connector.connect(host='localhost',user='root',password='SHIKHAR',database='comp
any')
mycursor=db.cursor()
no=input("Employee No")
name=input("Employee name:")
job=input("job")
query="select * from employee "
rec=()
if len(no)!=0:
query=query + "where empno=%s"
rec=rec+(no,)
if len(name)!=0:
query=query+ "where ename=%s"
rec=rec+(name,)
if len(job)!=0:
query= query +"where job=%s"
rec=rec+(job,)
qry=(query)
mycursor.execute(qry,rec)
i=mycursor.fetchall()
num=mycursor.rowcount
for t in i:
if num!=0:
print ('Record Found')
print ("Employee details
......
")
print ("Employee code:",t[0])
print ("Employee name:",t[1])
print ("Employee job:",t[2])
print ("Employee salary:",t[3]) else:
print("Record not found!!")
db.commit()
# Delete Employee
def delete():
db=mysql.connector.connect(host='localhost',user='root',password='SHIKHAR',database='comp
any')
mycursor=db.cursor()
empno=input("Enter Employee Code to be delete from company:")
qry=("delete from employee where empno=%s")
rec=(empno,)
mycursor.execute(qry,rec)
num=mycursor.rowcount
if num!=0:
print("Record Deleted!!")
else:
print("Invalid Data ")
db.commit() #update
def update():
print("\nWhich Data Should be Updated
......
")
print("1.Employee Job")
print("2.Employee Salary")
c=int(input("Select your Choice:"))
db=mysql.connector.connect(host='localhost',user='root',password='SHIKHAR',database='comp
any')
mycursor=db.cursor()
if (c==1):
code=input('Enter Code of Employee to be Updated:')
name=input("Enter New Employee Job:")
data=(name,code)
q=('update employee set job=%s where empno=%s')
mycursor.execute(q,data) print('Record Updated
.....
')
db.commit()
elif (c==2):
code=int(input('Enter Code of Employee to be Updated:'))
add=input("Enter Employee salary:")
q=('update Employee set salary=%s where empno=%s')
data=(add,code)
mycursor.execute(q,data)
print('\nRecord Updated
.....
')
db.commit()
else :
print("Invalid Input!!")
import mysql.connector
db=mysql.connector.connect(host='localhost',user='root',password='SHIKHAR')
mycursor=db.cursor()
mycursor.execute("USE company")
mycursor.execute("create table if not exists employee(empno char(5) primary key,ename varchar(20),job varchar(10),salary varchar(10))")
db.commit
while True :
print("~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~")
print("1.Insert Employee Record")
print("2.View Employee Record")
print("3.Delete Employee Record")
print("4.Update Employee Record")
print("~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~")
b=int(input("Enter your choice:"))
if b==1:
insert()
elif b==2:
view()
elif b==3:
delete()
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
elif b==4:
update()
else:
print("Invalid input!! Try again") INSERT
VIEW ALL
DELETE
UPDATE
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
PROGRAM 5
5. Write a random number generator that generates random numbers between 1 and 6 (simulates
a dice).
Source Code
import random
print ("A random number from list is : ",end="") print (random.choice([1,2,3,4,5,6]))
Output
PROGRAM 6
Create a module lengthconversion.py that stores functions for various length conversions
"""Length conversion module"""
def mietokm(mile):
"""To convert miles to kms"""
return mile*1.609
def kmtomile(km):
"""To convert kms to miles"""
return km*0.62
def feettoinches(feet):
"""To convert feet to inches"""
return feet*12
def inchestofeet (inch):
"""To convert inches to feets"""
return inch*0.0833
MILE= '1.6093 kilometer'
FEET= '12 inches'
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
PROGRAM 7
Create a module massconversion.py that stores functions for various mass conversions
"""Mass conversion module"""
def kgtotonne(kg):
"""To convert kgs to tonnes"""
return kg*0.001
def tonnetokg(tonne):
"""To convert tonnes to kgs"""
return tonne*1000
def kgtopound(kg):
"""To convert kgs to pounds"""
return kg*2.20462
def poundtokg(pound):
"""To convert pounds to kgs"""
return pound*0.453592
KG="0.0001 TONNES"
POUND="0.453952 KGS"