PM SHRI KENDRIYA
VIDYALAYA NO.1 CALICUT
CLASS XII
COMPUTER SCIENCE (083)
PRACTICAL FILE
ANEESA N ALI
PGT COMPUTER SCIENCE
LIST OF PROGRAMS
PYTHON PROGRAMS (15 NOS)
1. Linear Search Using Function
2. Prime Or Not Using Function
3. Simple Calculator Using Function
4. Read a text file line by line and display each word separated by a #
5. Read a text file and display the number of vowels/ consonants/
uppercase/ lowercase/ digits/other characters characters in the file
6. Remove all the lines that contain the character 'a' in a file and write it
to another file.
7. Writing and reading in binary file
8. Searching in a binary file
9. Updating in a binary file
10. Writing and reading in csv file
11. To display records having marks greater than 75 from a csv file
12. To display password of a user from a csv file
13. Menu Driven Stack using List
14. Stack Elements from a list
15. Stack Elements from a Dictionary
PYTHON-MYSQL CONNECTIVITY(4 NOS)
16. Creating a table from Python
17. Inserting records into a Table
18. Update records in a Table
19. Delete records from a table
SQL QUERIES (5 NOS)
20. Table Employee
21. Table Products
22. Table HOSPITAL
23. Tables EMP and DEPT
24. Tables Doctor and Salary
PROGRAM 1
Linear Search Using Function
PROGRAM:
def linear_search(L, ele):
for i in range(len(L)):
if L[i] == ele:
return i
return -1
L=eval(input("Enter list of numbers:"))
ele=int(input("Enter value to be searched:"))
index = linear_search(L,ele)
if index != -1:
print("Element found at index:",index,", position:", index+1)
else:
print("Element not found in the list")
OUTPUT:
Enter list of numbers:[56,12,47,23,35]
Enter value to be searched:23
Element found at index: 3 , position: 4
PROGRAM 2
Prime Or Not Using Function
def is_prime(num):
flag=False
if num == 0 or num == 1:
flag = True
elif num > 1:
for i in range(2, num//2+1):
if (num % i) == 0:
flag = True
break
return flag
n = int(input("Enter a number: "))
res=is_prime(n)
if res:
print(n, "is not a prime number")
else:
print(n, "is a prime number")
OUTPUT:
Enter a number: 39
39 is not a prime number
Enter a number: 53
53 is a prime number
PROGRAM 3
Simple Calculator Using Function
def add(n1, n2):
return n1 + n2
def sub(n1, n2):
return n1 - n2
def mul(n1, n2):
return n1 * n2
def div(n1, n2):
return n1 / n2
ch='y'
while [Link]()=='y':
print("Please select operation -\n"
"1. Add\n"
"2. Subtract\n"
"3. Multiply\n"
"4. Divide\n")
OP = int(input("Select operation (1-4): "))
n1 = int(input("Enter first number: "))
n2 = int(input("Enter second number: "))
if OP == 1:
print(n1, "+", n2, "=", add(n1, n2))
elif OP == 2:
print(n1, "-", n2, "=", sub(n1, n2))
elif OP == 3:
print(n1, "*", n2, "=", mul(n1, n2))
elif OP == 4:
print(n1, "/", n2, "=", div(n1, n2))
else:
print("Invalid input")
ch=input("Do you want to continue(y/n?")
OUTPUT:
Please select operation -
1. Add
2. Subtract
3. Multiply
4. Divide
Select operation (1-4): 1
Enter first number: 25
Enter second number: 36
25 + 36 = 61
Do you want to continue(y/n?y
Please select operation -
1. Add
2. Subtract
3. Multiply
4. Divide
Select operation (1-4): 2
Enter first number: 56
Enter second number: 4
56 - 4 = 52
Do you want to continue(y/n?y
Please select operation -
1. Add
2. Subtract
3. Multiply
4. Divide
Select operation (1-4): 3
Enter first number: 58
Enter second number: 7
58 * 7 = 406
Do you want to continue(y/n?n
PROGRAM 4
Read a text file line by line and display each word separated by a #
f=open("[Link]","r")
data = [Link]()
print(data)
[Link](0)
item=[]
a=""
while True:
a=[Link]()
words=[Link]()
for j in words:
[Link](j)
if a =="":
break
print("#".join(item))
[Link]()
OUTPUT:
Class 12
Data file handling
in Python
Class#12#Data#file#handling#in#Python
PROGRAM 5
Read a text file and display the number of vowels/ consonants/
uppercase/ lowercase/ digits/other characters characters in the file.
f = open("[Link]",'r')
data = [Link]()
count_vow = 0;count_con = 0;count_low = 0
count_up = 0;count_digit = 0;count_other = 0
print(“The file contains:”)
print(data)
for ch in data:
if [Link]():
if [Link]():
if ch in 'aeiouAEIOU':
count_vow += 1
else:
count_con += 1
if [Link]():
count_up +=1
if [Link]():
count_low += 1
if [Link]():
count_digit += 1
else:
count_other += 1
print("Digits",count_digit)
print("Vowels: ",count_vow)
print("Consonants: ",count_con-count_vow)
print("Upper Case: ",count_up)
print("Lower Case: ",count_low)
print("other than letters and digit: ",count_other)
[Link]()
OUTPUT:
The file contains:
Class 12
Data file handling
in Python
Digits 2
Vowels: 9
Consonants: 11
Upper Case: 3
Lower Case: 26
other than letters and digit: 7
PROGRAM 6
Remove all the lines that contain the character 'a' in a file and write
it to another file.
fo=open('[Link]','r')
fi=open('[Link]','w')
l=[Link]()
for i in l:
if 'a' in i:
i=[Link]('a','')
[Link](i)
[Link]()
[Link]()
print(“The new file contains:”)
fi=open('[Link]','r')
data=[Link]()
print(data)
OUTPUT:
The new file contains:
Clss 12
Dt file handling
PROGRAM 7
Create a binary file with roll number, name and marks and display
the data in the binary file.
import pickle
f=open("[Link]",'wb')
data=[]
while True:
rno=int(input("Enter Rollno:"))
name=input("Enter Name:")
mrks=int(input("Enter marks:"))
rec=[rno,name,mrks]
[Link](rec)
ch=input("Continue?")
if [Link]()=='N':
break
[Link](data,f)
print("Added")
[Link]()
f=open("[Link]",'rb+')
data=[Link](f)
print(data)
OUTPUT:
Enter Rollno:101
Enter Name:Amal
Enter marks:99
Continue?y
Enter Rollno:102
Enter Name:Bob
Enter marks:85
Continue?n
Added
[[101, 'Amal', 99], [102, 'Bob', 85]]
PROGRAM 8
Create a binary file with name and roll number. Search for a given
roll number and display the name, if not found display appropriate
message.
import pickle
f=open("[Link]",'wb')
data={}
while True:
rno=int(input("Enter Roll No:"))
name=input("Enter Name")
data[rno]=name
ch=input("Continue?")
if [Link]()=='N':
break
[Link](data,f)
print("Added")
[Link]()
f=open("[Link]",'rb')
roll=int(input("Enter roll no to be searched:"))
try:
while True:
rec=[Link](f)
for i in rec:
if roll==i:
print(i,":",rec[i])
except:
[Link]()
OUTPUT:
Enter Roll No:101
Enter Name:Amal
Continue?y
Enter Roll No:102
Enter Name:Bob
Continue?y
Enter Roll No:103
Enter Name:Dany
Continue?n
Added
Enter roll no to be searched:102
102 : Bob
PROGRAM 9
A binary file "[Link]" has structure [employee id, employee name,
designation,salary]. Write a function addemployee() in Python that would
write contents of the file "[Link]" and another function
promoteemployee() to update the designation of those employees whose
salary is above 50000 to Team Leader and display the updated file.
import pickle
def addemployee():
f=open("[Link]",'wb+')
data=[]
while True:
eid=int(input("Enter Empid:"))
ename=input("Enter Name:")
desig=input("Enter designation:")
sal=int(input("Enter salary:"))
rec=[eid,ename,desig,sal]
[Link](rec)
ch=input("Continue?")
if [Link]()=='N':
break
[Link](data,f)
print("Added")
[Link]()
def promoteemployee():
f=open("[Link]",'rb+')
data=[Link](f)
found=0
for rec in data:
try:
if rec[3]>=50000:
rec[2]="Team Leader"
found=1
break
except EOFError:
[Link]()
if found==1:
[Link](0)
[Link](data,f)
print("updated")
[Link](0)
data=[Link](f)
print(data)
else:
print("Record not found")
[Link]()
addemployee()
promoteemployee()
OUTPUT:
Enter Empid:1001
Enter Name:Akash
Enter designation:Project Head
Enter salary:50000
Continue?y
Enter Empid:3002
Enter Name:Basil
Enter designation:Manager
Enter salary:35000
Continue?n
Added
[[1001, 'Akash', 'Project Head ', 50000], [3002, 'Basil', 'Manager', 35000]]
updated
[[1001, 'Akash', 'Team Leader', 50000], [3002, 'Basil', 'Manager', 35000]]
PROGRAM 10
To read and write operation onto a [Link] file having fields as book
number, book name, category and price.
import csv
with open('[Link]','w',newline='') as f:
csv_ob=[Link](f,delimiter=',')
ch='y'
header=['BookNo','BookName','AuthorName','Price']
while [Link]()=='y':
bno=int(input("Enter Book No.: "))
bname=input("Enter Book Name: ")
author=input("Enter Author Name: ")
price=float(input("Enter Price: "))
csv_ob.writerow([bno,bname,author,price])
print("Data added to file..")
ch=input("Want add more record(y/n).....")
with open('[Link]','r',newline='') as f:
csv_ob=[Link](f)
print(“The file contains:”)
for i in csv_ob:
print(i)
OUTPUT:
Enter Book No.: 1234
Enter Book Name: God of Small things
Enter Author Name: Arundhathi Roy
Enter Price: 400
Data added to file..
Want add more record(y/n).....y
Enter Book No.: 4587
Enter Book Name: Kite Runner
Enter Author Name: Khaled Husseini
Enter Price: 500
Data added to file..
Want add more record(y/n).....n
The file contains:
['1234', 'God of Small things', 'Arundhathi Roy', '400.0']
['4587', 'Kite Runner', 'Khaled Husseini', '500.0']
PROGRAM 11
Create a csv file ‘[Link]’ with roll number, name and marks and
display all records having marks greater than 75
import csv
n=int(input("How many records?:"))
rec=[]
with open('[Link]','w',newline='')as f:
fields=['rollno','name','marks']
csv_ob=[Link](f)
csv_ob.writerow(fields)
for k in range(n):
rno=int(input("Enter rollno:"))
nm=input("Enter name:")
mks=int(input("Enter marks:"))
rec=[rno,nm,mks]
csv_ob.writerow(rec)
print("Added")
with open('[Link]','r')as f:
csv_ob=[Link](f)
for i in csv_ob:
if i[2]=='marks':
continue
if int(i[2])>75:
print(i)
OUTPUT:
How many records?:3
Enter rollno:111
Enter name:Amal
Enter marks:90
Enter rollno:112
Enter name:Anay
Enter marks:86
Enter rollno:113
Enter name:Ashish
Enter marks:60
Done
['111', 'Amal', '90']
['112', 'Anay', '86']
PROGRAM 12
Create a CSV file by entering user-id and password, read and search the
password for given userid.
import csv
def create_csv():
with open('user_passwords.csv', 'w', newline='') as file:
csv_ob = [Link](file)
csv_ob.writerow(["user_id", "password"])
ch='y'
while [Link]()=='y':
user_id = input("Enter user-id:")
password = input("Enter password: ")
csv_ob.writerow([user_id, password])
ch=input("Want to continue(y/n)?")
def search_password(user_id):
with open('user_passwords.csv', 'r') as file:
csv_ob = [Link](file)
next(csv_ob) # Skip the header row
for row in csv_ob:
if row[0] == user_id:
return row[1]
return None
create_csv()
u_id = input("Enter user-id to search for: ")
pwd = search_password(u_id)
if pwd is not None:
print("The password for ", u_id,"is ",pwd)
else:
print("No user-id found")
OUTPUT:
Enter user-id:harry
Enter password: 1234asd
Want to continue(y/n)?y
Enter user-id:maichel
Enter password: 147tyh
Want to continue(y/n)?n
Enter user-id to search for: harry
The password for harry is 1234asd
PROGRAM 13
Write a menu driven Python program to implement a stack using list.
def push(stk,elt):
[Link](elt)
def pop(stk):
if stk==[]:
return "underflow"
else:
elt=[Link]()
return elt
def display(stk):
if stk==[]:
print('stack is empty')
else:
top=len(stk)-1
for i in range(top,-1,-1):
print(stk[i])
stk=[]
while True:
print('stack operation\[Link]\[Link]\[Link]\[Link]')
choice=int (input('enter choice:'))
if choice==1:
elt=int(input('enter a number:'))
push(stk,elt)
elif choice==2:
elt=pop(stk)
if elt=="underflow":
print('stack is underflow')
else:
print('popped value:',elt)
elif choice==3:
display(stk)
elif choice==4:
break
else:
print('invalid')
exit()
OUTPUT:
stack operation
[Link]
[Link]
[Link]
[Link]
enter choice:1
enter a number:3
stack operation
[Link]
[Link]
[Link]
[Link]
enter choice:1
enter a number:5
stack operation
[Link]
[Link]
[Link]
[Link]
enter choice:1
enter a number:7
stack operation
[Link]
[Link]
[Link]
[Link]
enter choice:2
popped value: 7
stack operation
[Link]
[Link]
[Link]
[Link]
enter choice:3
5
3
PROGRAM 14
A List contains following record of a Book : [Book Name, Write Name, Price]
Write the following user defined functions to perform given operations on the
stack named “BOOK” :
(i) Push_Rec( ) – To push the record containing Book name and author name of
Books having price > 500 to the stack.
(ii) Pop_Rec( ) – To pop the objects from the stack and display them. Also
display “STACK UNDERFLOW” when there are no elements in the Stack
BOOK = []
def Push_Rec(Book_list):
for b in Book_list:
if b[2] > 500:
[Link]([b[0], b[1]])
def Pop_Rec():
if BOOK==[]:
print("STACK UNDERFLOW")
else:
while BOOK:
print([Link]())
Book_list=[]
n=int(input("Enter how many books:"))
for i in range(n):
BName=input("Enter Book Name:")
WName=input("Enter Author Name:")
Price=int(input("Enter Price:"))
Book_list.append([BName,WName,Price])
Push_Rec(Book_list)
print()
print("The stack contains:")
Pop_Rec()
OUTPUT:
Enter how many books:3
Enter Book Name:Harry Potter
Enter Author Name:J K Rowling
Enter Price:999
Enter Book Name:Geronimo Stilton
Enter Author Name:Elisabetta Dami
Enter Price:1500
Enter Book Name:Alice in Wonderland
Enter Author Name:Lewis Carroll
Enter Price:350
The stack contains:
['Geronimo Stilton', 'Elisabetta Dami']
['Harry Potter', 'J K Rowling']
PROGRAM 15
Write a function in Python, Push(SItem) where , SItem is a dictionary
containing the details of stationary items– {Sname:price}. The function should
push the names of those items in the stack who have price greater than 75.
Also display the count of elements pushed into the stack and display the
elements in the stack.
STK=[]
def Push(SItem):
for sname, price in [Link]():
if price > 75:
[Link](sname) # Push item name onto the stack
print("The count of elements pushed into the stack is:",len(STK))
def Pop():
if STK==[]:
print("STACK UNDERFLOW")
else:
while STK:
print([Link]())
S_items = {}
ch='y'
while [Link]()=='y':
name=input("Enter item name:")
price=int(input("Enter item price:"))
S_items[name]=price
ch=input("Do you want to add more(y/n)?...")
Push(S_items)
print()
print("Elements in the stack:")
Pop()
OUTPUT:
Enter item name:Pen
Enter item price:50
Do you want to add more(y/n)?...y
Enter item name:Notebook
Enter item price:85
Do you want to add more(y/n)?...y
Enter item name:Stapler
Enter item price:120
Do you want to add more(y/n)?...N
The count of elements pushed into the stack is: 2
Elements in the stack:
Stapler
Notebook
PROGRAM 16
Creating a DATABASE table from Python
import [Link]
conn=[Link](host='localhost',user='root',passwd='system')
if conn.is_connected:
print("Successfully connected...")
mycursor=[Link]()
[Link]("create database 12F")
print("\nDatabase Created....")
[Link]("use 12F")
[Link]("create table student(Rollno int primary key, Name
varchar(25),Marks float)")
print("\nTable Student created...")
[Link]("show tables")
print("\nTables in the database are:")
rec=[Link]()
print(rec)
[Link]("desc student")
print("\nThe structure of the table:")
rs=[Link]()
for row in rs:
print(row)
[Link]()
else:
print("Connection not established...")
OUTPUT:
Successfully connected...
Database Created....
Table Student created...
Tables in the database are:
('student',)
The structure of the table:
('Rollno', 'int(11)', 'NO', 'PRI', None, '')
('Name', 'varchar(25)', 'YES', '', None, '')
('Marks', 'float', 'YES', '', None, '')
PROGRAM 17
Inserting records into a Table from Python
import [Link]
conn = [Link]( host="localhost", user="root",
password="system", database="12F")
mycursor = [Link]()
n=int(input(“Enter how many records you want to insert:”))
for i in range(n):
roll=int(input("Enter roll no :"))
name=input("Enter name :")
mks=int(input("Enter marks :"))
val = (roll,name,mks)
query = "INSERT INTO student VALUES (%s,%s,%s)"
[Link](query, val)
print(“Inserted successfully…”)
[Link]()
[Link]("select * from student")
rs=[Link]()
print()
print([Link], "records fetched.")
for row in rs:
print(row)
[Link]()
OUTPUT:
Inserted successfully…
5 records fetched.
(101, 'Aahana', 85.0)
(102, 'Abhinav', 80.0)
(103, 'Adwaith K', 81.0)
(104, 'Adwaith P', 90.0)
(105, 'Akshara', 82.0)
PROGRAM 18
Updating a record in a Table from Python
import [Link]
conn = [Link]( host="localhost",user="root",
password="system", database="12F")
mycursor = [Link]()
[Link]("select * from student")
rs=[Link]()
print([Link], "records fetched.")
for row in rs:
print(row)
roll=int(input("\nEnter roll no whose marks is to be updated :"))
mks=int(input("\nEnter new marks :"))
val=(mks,roll)
query = "UPDATE student SET Marks=%s where rollno=%s"
[Link](query, val)
[Link]()
print(“\nAfter updation:”)
[Link]("select * from student where rollno=%s" %(roll,))
row=[Link]()
print(row)
[Link]()
OUTPUT:
5 records fetched.
(101, 'Aahana', 85.0)
(102, 'Abhinav', 80.0)
(103, 'Adwaith K', 81.0)
(104, 'Adwaith P', 90.0)
(105, 'Akshara', 82.0)
Enter roll no whose marks is to be updated :104
Enter new marks :95
After updation:
(104, 'Adwaith P', 95.0)
PROGRAM 19
Deleting records from a Table from Python
import [Link]
conn = [Link]( host="localhost", user="root",
password="system", database="12F")
mycursor = [Link]()
[Link]("select * from student")
rs=[Link]()
print([Link], "records fetched.")
for row in rs:
print(row)
roll=int(input("\nEnter roll no to be deleted :"))
query = "DELETE FROM student where rollno={}".format(roll)
[Link](query)
[Link]()
print("\nAfter deletion:")
[Link]("select * from student")
rs=[Link]()
print([Link], "records fetched.")
for row in rs:
print(row)
[Link]()
OUTPUT:
5 records fetched.
(101, 'Aahana', 85.0)
(102, 'Abhinav', 80.0)
(103, 'Adwaith K', 81.0)
(104, 'Adwaith P', 95.0)
(105, 'Akshara', 82.0)
Enter roll no to be deleted :105
After deletion:
4 records fetched.
(101, 'Aahana', 85.0)
(102, 'Abhinav', 80.0)
(103, 'Adwaith K', 81.0)
(104, 'Adwaith P', 95.0)
PROGRAM 20
Table: Employee
PROGRAM 21
Table: Hospital
[Link] Name Age Department Dateofadm Charges Sex
1 Arpit 62 Surgery 1998-01-21 300 M
2 Zareena 22 ENT 1997-12-12 250 F
3 Kareem 32 Orthopaedic 1998-02-19 200 M
4 Arun 12 Surgery 1998-01-11 300 M
5 Zubin 30 ENT 1998-01-12 250 M
6 Ketaki 16 ENT 1998-02-24 250 F
7 Ankit 29 Cardiology 1998-02-20 800 F
8 Zareen 45 Gynaecology 1998-02-22 300 F
9 Kush 19 Cardiology 1998-01-13 800 M
10 Shilpa 23 Nuclear Medicine 1998-02-21 400 F
CREATE TABLE HOSPITAL
( SNo INT PRIMARY KEY, Name VARCHAR(50),
Age INT, Department VARCHAR(50),
Dateofadm DATE, Charges INT, Sex CHAR(1)
);
INSERT INTO HOSPITAL VALUES
(1, 'Arpit', 62, 'Surgery', '1998-01-21', 300, 'M'),
(2, 'Zareena', 22, 'ENT', '1997-12-12', 250, 'F'),
(3, 'Kareem', 32, 'Orthopaedic', '1998-02-19', 200, 'M'),
(4, 'Arun', 12, 'Surgery', '1998-01-11', 300, 'M'),
(5, 'Zubin', 30, 'ENT', '1998-01-12', 250, 'M'),
(6, 'Ketaki', 16, 'ENT', '1998-02-24', 250, 'F'),
(7, 'Ankit', 29, 'Cardiology', '1998-02-20', 800, 'F'),
(8, 'Zareen', 45, 'Gynaecology', '1998-02-22', 300, 'F'),
(9, 'Kush', 19, 'Cardiology', '1998-01-13', 800, 'M'),
(10, 'Shilpa', 23, 'Nuclear Medicine', '1998-02-21', 400, 'F');
(a) List the names of all patients admitted after 1998-01-15
SELECT Name
FROM HOSPITAL
WHERE Dateofadm > '1998-01-15';
| Name |
| ------ |
| Kareem |
| Ankit |
| Shilpa |
| Zareen |
| Ketaki |
(b) List the names of female patients who are in the ENT department
SELECT Name
FROM HOSPITAL
WHERE Sex = 'F' AND Department = 'ENT';
| Name |
| ------- |
| Zareena |
| Ketaki |
(c) List the names of all patients with their date of admission in ascending order
SELECT Name, Dateofadm
FROM HOSPITAL
ORDER BY Dateofadm ASC;
| Name | Dateofadm |
| ------- | ---------- |
| Zareena | 1997-12-12 |
| Arun | 1998-01-11 |
| Zubin | 1998-01-12 |
| Kush | 1998-01-13 |
| Arpit | 1998-01-21 |
| Kareem | 1998-02-19 |
| Ankit | 1998-02-20 |
| Shilpa | 1998-02-21 |
| Zareen | 1998-02-22 |
| Ketaki | 1998-02-24 |
(d) Display Patient’s Name, Charges, Age for only female patients
SELECT Name, Charges, Age
FROM HOSPITAL
WHERE Sex = 'F';
| Name | Charges | Age |
| ------- | ------- | --- |
| Zareena | 250 | 22 |
| Ketaki | 250 | 16 |
| Ankit | 800 | 29 |
| Zareen | 300 | 45 |
| Shilpa | 400 | 23 |
(e) Output of SQL Commands
(i) SELECT COUNT(DISTINCT Charges) FROM HOSPITAL;
COUNT(DISTINCT Charges)
5
(ii) SELECT MIN(Age) FROM HOSPITAL WHERE Sex = "F";
MIN(Age)
16
PROGRAM 22
Table: PRODUCTS
PCODE PNAME COMPANY PRICE STOCK MANUFACTURE WARRANTY
P001 TV BPL 10000 200 2018-01-12 3
P002 TV SONY 12000 150 2017-03-23 4
P003 PC LENOVO 39000 100 2018-04-09 2
P004 PC COMPAQ 38000 120 2019-06-20 2
P005 HANDYCAM SONY 18000 250 2017-03-23 3
Create Table Products (
Pcode Varchar(10) Primary Key, Pname Varchar(30),
Company Varchar(30), Price Int, Stock Int,
Manufacture Date, Warranty Int
);
INSERT INTO PRODUCTS VALUES
('P001', 'TV', 'BPL', 10000, 200, '2018-01-12', 3),
('P002', 'TV', 'SONY', 12000, 150, '2017-03-23', 4),
('P003', 'PC', 'LENOVO', 39000, 100, '2018-04-09', 2),
('P004', 'PC', 'COMPAQ', 38000, 120, '2019-06-20', 2),
('P005', 'HANDYCAM', 'SONY', 18000, 250, '2017-03-23', 3);
(a) Show details of all PCs with stock more than 110
SELECT * FROM PRODUCTS
WHERE PNAME = 'PC' AND STOCK > 110;
+-------+-------+---------+-------+-------+-------------+----------+
| PCODE | PNAME | COMPANY | PRICE | STOCK | MANUFACTURE | WARRANTY |
+-------+-------+---------+-------+-------+-------------+----------+
| P004 | PC | COMPAQ | 38000 | 120 | 2019-06-20 | 2 |
+-------+-------+---------+-------+-------+-------------+----------+
(b) List the company which gives warranty of more than 2 years
SELECT DISTINCT COMPANY
FROM PRODUCTS
WHERE WARRANTY > 2;
+---------+
| COMPANY |
+---------+
| BPL |
| SONY |
+---------+
(c) Stock value of BPL company (price × stock)
SELECT PRICE * STOCK AS STOCK_VALUE
FROM PRODUCTS
WHERE COMPANY = 'BPL';
+-------------+
| STOCK_VALUE |
+-------------+
| 2000000 |
+-------------+
(d) Number of products from each company
SELECT COMPANY, COUNT(*) AS NUM_PRODUCTS
FROM PRODUCTS
GROUP BY COMPANY;
+---------+--------------+
| COMPANY | NUM_PRODUCTS |
+---------+--------------+
| BPL | 1 |
| SONY | 2 |
| LENOVO | 1 |
| COMPAQ | 1 |
+---------+--------------+
(e) Count PRODUCTS out of warranty in the year 2020
SELECT COUNT(*)
FROM PRODUCTS
WHERE YEAR(MANUFACTURE) + WARRANTY < 2020;
+----------+
| COUNT(*) |
+----------+
| 2 |
+----------+
(f) Output of given SQL statements
(i) SELECT COUNT(DISTINCT COMPANY) FROM PRODUCT;
+-------------------------+
| COUNT(DISTINCT COMPANY) |
+-------------------------+
| 4 |
+-------------------------+
(ii) SELECT MAX(PRICE) FROM PRODUCT WHERE WARRANTY <= 3;
+------------+
| MAX(PRICE) |
+------ -----+
| 39000 |
+------------+
PROGRAM 23
Table: EMP and DEPT
EMP
EmpNo EmpName City Designation DOJ Sal Comm DeptID
8369 SMITH Mumbai CLERK 1990-12-18 800 NULL 20
8499 ANYA Varanasi SALESMAN 1991-02-20 1600 300 30
8521 SETH Jaipur SALESMAN 1991-02-22 1250 500 30
8566 MAHADEVAN Delhi MANAGER 1991-04-02 2985 NULL 20
DEPT
DeptID DeptName MgrID Location
10 SALES 8566 Mumbai
20 PERSONNEL 9698 Delhi
30 ACCOUNTS 4578 Delhi
40 RESEARCH 8839 Bengaluru
CREATE TABLE EMP (
EmpNo INT PRIMARY KEY, EmpName VARCHAR(30), City VARCHAR(30),
Designation VARCHAR(30), DOJ DATE, Sal DECIMAL(10,2),
Comm DECIMAL(10,2), DeptID INT,
);
CREATE TABLE DEPT (
DeptID INT PRIMARY KEY, DeptName VARCHAR(30),
MgrID INT, Location VARCHAR(30)
);
INSERT INTO EMP (EmpNo, EmpName, City, Designation, DOJ, Sal, DeptID)
VALUES (8369, 'SMITH', 'Mumbai', 'CLERK', '1990-12-18', 800.00, 20);
INSERT INTO EMP VALUES
(8499, 'ANYA', 'Varanasi', 'SALESMAN', '1991-02-20', 1600.00, 300.00, 30);
INSERT INTO EMP VALUES
(8521, 'SETH', 'Jaipur', 'SALESMAN', '1991-02-22', 1250.00, 500.00, 30);
INSERT INTO EMP (EmpNo, EmpName, City, Designation, DOJ, Sal, DeptID)
VALUES (8566, 'MAHADEVAN', 'Delhi', 'MANAGER', '1991-04-02', 2985.00, 20);
INSERT INTO DEPT VALUES
(10, 'SALES', 8566, 'Mumbai'),
(20, 'PERSONNEL', 9698, 'Delhi'),
(30, 'ACCOUNTS', 4578, 'Delhi'),
(40, 'RESEARCH', 8839, 'Bengaluru');
(a) Show the minimum, maximum and average salary of managers.
SELECT MIN(Sal), MAX(Sal), AVG(Sal)
FROM EMP
WHERE Designation = 'MANAGER';
+-------------+--------------+---------------+
| MIN(Sal) | MAX(Sal) | AVG(Sal) |
+-------------+-------------+----------------+
| 2985 | 2985 | 2985.00 |
+--------------+-------------+---------------+
(b) Count the number of clerks in the organization.
SELECT COUNT(*)
FROM EMP
WHERE Designation = 'CLERK';
+---------------+
| COUNT(*) |
+---------------+
| 1 |
+---------------+
(c) Display the designation-wise list of employees with name, salary and date of joining.
SELECT Designation, EmpName, Sal, DOJ
FROM EMP
ORDER BY Designation;
+-------------------+-------------------+----------+-----------------+
| Designation | EmpName | Sal | DOJ |
+------------------+--------------------+---------+------------------+
| CLERK | SMITH | 800 | 1990-12-18 |
| MANAGER | MAHADEVAN | 2985 | 1991-04-02 |
| SALESMAN | ANYA | 1600 | 1991-02-20 |
| SALESMAN | SETH | 1250 | 1991-02-22 |
+------------------+-------------------+----------+------------------+
(d) Count the number of employees who are not getting commission.
SELECT COUNT(*)
FROM EMP
WHERE Comm IS NULL;
+---------------+
| COUNT(*) |
+---------------+
| 2 |
+---------------+
(e) Show the average salary for all departments having salary > 2000.
SELECT DeptID, AVG(Sal)
FROM EMP
GROUP BY DeptID
HAVING AVG(Sal) > 2000;
+-----------+---------------+
| DeptID | AVG(Sal) |
+-----------+---------------+
| 20 | 1892.50 |
+-----------+---------------+
(f) Count of employees group by DeptID
SELECT DeptID, COUNT(*)
FROM EMP
GROUP BY DeptID;
+-----------+---------------+
| DeptID | COUNT(*) |
+-----------+---------------+
| 20 | 2 |
| 30 | 2 |
+-----------+---------------+
(g) Display the maximum salary of employees in each department.
SELECT DeptID, MAX(Sal)
FROM EMP
GROUP BY DeptID;
+-----------+-------------+
| DeptID | MAX(Sal) |
+-----------+-------------+
| 20 | 2985 |
| 30 | 1600 |
+-----------+-------------+
(h) Display the name of employees along with their designation and department name.
SELECT [Link], [Link], [Link]
FROM EMP E,DEPT D
WHERE [Link] = [Link];
+---------------------+-----------------+-----------------+
| EmpName | Designation | DeptName |
+--------------------+------------------+-----------------+
| SMITH | CLERK | PERSONNEL |
| MAHADEVAN | MANAGER | PERSONNEL |
| ANYA | SALESMAN | ACCOUNTS |
| SETH | SALESMAN | ACCOUNTS |
+-------------------+------------------+------------------+
(i) Count the number of employees working in ACCOUNTS department.
SELECT COUNT(*)
FROM EMP E, DEPT D
WHERE [Link] = [Link] AND [Link] = 'ACCOUNTS';
+---------------+
| COUNT(*) |
+---------------+
| 2 |
+---------------+
PROGRAM 24
Table: DOCTOR and SALARY