1.

AIM: Write a python program to Read a text file line by line and display
each word separated by a #.

ALGORITHM::

1. Open the text file for reading.

2. Initialize an empty string variable to store the result.
3. Read the file line by line in a loop:
4. Split the line into words using space as a delimiter.
5. Initialize an empty string variable to store the line result.
6. Iterate through the words in the line
7. Add each word to the line result.
8. Add '#' after each word except the last word in the line.
9. Add the line result to the result string.
10. Add a newline character to the result after processing each line.
11. Close the file.
12. Display the result.

PROGRAM:

def process_text_file(file_path):
result = ""

try:
with open(file_path, 'r') as file:
for line in file:
words = line.split()
line_result = ""

for word in words:

line_result += word
if word != words[-1]:
line_result += "#"

result += line_result + "\n"

except FileNotFoundError:
print("File not found")
return

print(result)

file_path = "sample.txt"
process_text_file(file_path)

RESULT: The above program has been executed sucessfully.

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

INPUT
Hello world
This is a sample text file
Read and display words
Separated by #

OUTPUT

Hello#world
This#is#a#sample#text#file
Read#and#display#words
Separated#by#
====================================================
2.

AIM: Write a python program to Read a text file and display the number of
vowels/consonants/uppercase/lowercase characters in the file.

ALGORITHM:

1. Open the text file for reading.

2. Initialize counters for vowels, consonants, uppercase, and lowercase
characters.
3. Read the file character by character in a loop:
4. Check if the character is a vowel (A, E, I, O, U, or their lowercase
counterparts).
5. Check if the character is an uppercase letter.
6. Check if the character is a lowercase letter.
7. If any of the above conditions are met, increment the corresponding
counter.
8. Close the file.
9. Display the counts of vowels, consonants, uppercase, and lowercase
characters.

PROGRAM:

def count_characters_in_file(file_path):
vowels = consonants = uppercase = lowercase = 0

try:
with open(file_path, 'r') as file:
content = file.read()
for char in content:
if char.isalpha():
if char.lower() in "aeiou":
vowels += 1
else:
consonants += 1
if char.isupper():
uppercase += 1
if char.islower():
lowercase += 1

except FileNotFoundError:
print("File not found")
return

print("Vowels:", vowels)
print("Consonants:", consonants)
print("Uppercase characters:", uppercase)
print("Lowercase characters:", lowercase)

file_path = "sample.txt"
count_characters_in_file(file_path)

RESULT: The above program has been executed sucessfully.

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

INPUT
Hello World!
This is a Sample Text File with 123 numbers.
The quick brown fox jumps over the lazy dog.

OUTPUT

Vowels: 45
Consonants: 70
Uppercase characters: 20
Lowercase characters: 95

================================================
3. AIM: Write a python program to remove all the lines that contain the
character 'a' in a file and write it to another file.

ALGORITHM:
1. Open the source file for reading.
2. Open the destination file for writing.
3. Read the source file line by line in a loop.
4. Check if the line contains the character 'a'. If it does not, write it to the
destination file.
5. Close the source and destination files.

PROGRAM:

def remove_lines_with_char_a(input_file, output_file):

try:
with open(input_file, 'r') as source_file, open(output_file, 'w') as dest_file:
for line in source_file:
if 'a' not in line.lower():
dest_file.write(line)

except FileNotFoundError:
print("File not found")
return

input_file = "source.txt" # Replace with the source file path

output_file = "filtered.txt" # Replace with the destination file path
remove_lines_with_char_a(input_file, output_file)

RESULT: The above program has been executed sucessfully.

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

INPUT
This is a sample text.
It contains several lines.
Some of these lines have the letter 'a'.
Others do not.
We are removing lines with 'a'.
OUTPUT
Others do not.

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

4. AIM: Write a python program to 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.

ALGORITHM:

1. Open a binary file for writing.

2. Write records with name and roll number separated by a delimiter to the
binary file.
3. Close the binary file.
4. Open the binary file for reading.
5. Input the roll number to be searched.
6. Read records from the binary file one by one
7. Read a line from the file, split it using the delimiter, and check if the roll
number in the record matches the input roll number.
8. If a match is found, display the name and break from the loop.
9. Close the binary file.
10. If no match is found, display an appropriate "Not found" message.
PROGRAM:

# Function to create a binary file with name and roll number

def create_binary_file(file_name, records):
with open(file_name, 'w') as binary_file:
for name, roll in records:
binary_file.write(f"{name},{roll}\n")

# Function to search for a given roll number and display the name

def search_and_display_name(file_name, search_roll):

try:
with open(file_name, 'r') as binary_file:
for line in binary_file:
name, roll = line.strip().split(',')
roll = int(roll)
if roll == search_roll:
print("Name: " , name , " Roll Number: " , roll )
break
else:
print("Roll number not found")

except FileNotFoundError:
print("File not found")

if __name__ == "__main__":
file_name = "student_data.txt"
records = [("Alice", 101), ("Bob", 102), ("Charlie", 103), ("David", 104)]

create_binary_file(file_name, records)

search_roll = 103
search_and_display_name(file_name, search_roll)
RESULT: The above program has been executed sucessfully.

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

INPUT
The program is set to search for the roll number 103.

OUTPUT
Name: Charlie, Roll Number: 103

5. AIM: Write a python program to Create a binary file with roll number, name
and marks. Input a roll number and update the marks.

ALGORITHM:

Algorithm: Student Records CSV Management

1. Start
2. Define file_name = "student_data.csv"
3. Define initial records as list of lists:
[[101,"Alice",90], [102,"Bob",85], [103,"Charlie",88], [104,"David",92]]
4. Define search_roll = 103 and new_marks = 95

5. Create CSV File:

a. Open file_name in write mode
b. Create CSV writer object
c. Write each record from records list to file
d. Close file

6. Update Marks:
a. Open file_name in read mode
b. Read all records into a temporary list
c. For each record:
i. If roll number matches search_roll:
- Update marks to new_marks
ii. Add record to updated list
d. Open file_name in write mode
e. Write all updated records back to file
f. Close file

7. Display Records:
a. Open file_name in read mode
b. For each row in file:
i. Print roll number, name, and marks
c. Close file

8. End

program:

import csv

# Create CSV file with records

def create_file(file_name, records):
with open(file_name, 'w', newline='') as file:
writer = csv.writer(file)
for record in records:
writer.writerow(record)

# Update marks for a roll number

def update_marks(file_name, search_roll, new_marks):
try:
records = []
with open(file_name, 'r', newline='') as file:
reader = csv.reader(file)
for row in reader:
roll = int(row[0])
if roll == search_roll:
records.append([roll, row[1], new_marks])
else:
records.append(row)
with open(file_name, 'w', newline='') as file:
writer = csv.writer(file)
writer.writerows(records)
except FileNotFoundError:
print("File not found")

# Display all records

def display_records(file_name):
try:
with open(file_name, 'r', newline='') as file:
for row in csv.reader(file):
print(row[0], row[1], row[2])
except FileNotFoundError:
print("File not found")

# Main program
file_name = "student_data.csv"
records = [[101, "Alice", 90], [102, "Bob", 85], [103, "Charlie", 88], [104, "David",
92]]
search_roll = 103
new_marks = 95

create_file(file_name, records)
update_marks(file_name, search_roll, new_marks)
display_records(file_name)

RESULT: The above program has been executed sucessfully.

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

INPUT
The program is set to update the marks for roll number 103 to
95.
OUTPUT
Roll number not found
Marks for Charlie have been updated to 95
Roll number not found

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

6. AIM: Write a python program for a random number generator that generates
random numbers between 1 and 6 (simulates a dice).

ALGORITHM:

1. Import the random module to access random number generation

functions.
2. Use the random.randint() function to generate a random integer between
1 and 6 (inclusive).
3. Display the generated random number.

PROGRAM:

import random

def roll_dice():
random_number = random.randint(1, 6)
return random_number

# Main program
while True:
random_number = roll_dice()
print("You rolled:", random_number)
again = input("Roll again? (y/n): ")
if again.lower() != 'y':
print("Goodbye!")
break
============================

RESULT: The above program has been executed sucessfully.

INPUT
There is no specific input required for this program.

OUTPUT
You rolled: 3 # The number may vary as it's random

7. AIM: Write a python program for creating a CSV file by entering user-id and
password, reading and searching for the password for a given user-id

ALGORITHM:

1. Import the csv module to work with CSV files.

2. Create a CSV file for writing and write user-id and password pairs to it.
3. Close the CSV file.
4. Open the CSV file for reading.
5. Input a user-id to search for.
6. Read the CSV file row by row and check if the user-id matches the input.
7. If a match is found, display the corresponding password.
8. Close the CSV file.
9. If no match is found, display an appropriate "User not found" message.

PROGRAM:

import csv
# Function to create a CSV file with user-id and password
def create_csv_file(file_name, user_data):
with open(file_name, 'w', newline='') as csv_file:
writer = csv.writer(csv_file)
writer.writerow(["User-ID", "Password"])
for user_id, password in user_data:
writer.writerow([user_id, password])

# Function to search for a password for a given user-id

def search_password(file_name, search_user_id):
try:
with open(file_name, 'r') as csv_file:
lines = csv_file.readlines()
for line in lines[1:]: # Skip the header row ("User-ID,Password")
user_id, password = line.strip().split(',')
if user_id == search_user_id:
return password
return None
except FileNotFoundError:
print("File not found")
return None

# Main program
if __name__ == "__main__":
file_name = "user_data.csv"
user_data = [("user1", "password1"), ("user2", "password2"), ("user3",
"password3")]

create_csv_file(file_name, user_data)

search_user_id = "user2"
password = search_password(file_name, search_user_id)

if password is not None:

print("Password for", search_user_id, ":", password)
else:
print("User", search_user_id, "not found")
RESULT: The above program has been executed sucessfully.

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

INPUT

The program is set to search for the password associated with the
user-id "user2."

OUTPUT

Password for user2: password2

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

8. AIM: Write a python program to implement a stack using list.

ALGORITHM:
1. Initialize
o Empty list stack and variable top = None.
2. Menu Loop
o Show options: Push, Pop, Peek, Display, Exit.
o Take user input.
3. Operations
o Push: Add item to stack, update top.
o Pop: Remove last item if stack not empty, else "Underflow".
o Peek: Return last item if exists, else "Underflow".
o Display: Print stack top to bottom.
o Exit: Stop the program.
PROGRAM:

def isEmpty(stk):
if stk == []:
return True
else:
return False

def Push(stk,item):
stk.append(item)
top = len(stk) - 1

def Pop(stk):
if isEmpty(stk):
return "UnderFlow"
else:
item = stk.pop()
if len(stk) == 0:
top = None
else:
top = len(stk) - 1
return item

def Peek(stk):
if isEmpty(stk):
return "underflow"
else:
top = len(stk) -1
return stk[top]

def Display(stk):
if isEmpty(stk):
print("Stack Empty")
else:
top = len(stk) -1
print(stk[top],"<--top")
for a in range(top -1 , -1 ,-1):
print(stk[a])

Stack = []
top = None

while True:
print("STACK OPERATIONS")
print("1. Push")
print("2. Pop")
print("3. Peek")
print("4. Display Stack")
print("5. Exit")

ch = int(input("enter ur choice :"))

if ch == 1:
item = int(input("enter item "))
Push(Stack ,item)
elif ch == 2:
item = Pop(Stack)
if item == "Underflow":
print("Underflow ")
else:
print("Popped item is " , item)
elif ch == 3:
item = Peek(Stack)
if item == "underflow":
print("underflow")
else:
print("Topmost item is " , item)
elif ch == 4:
Display(Stack)
elif ch == 5:
break
else:
print("Invalid Choice")
RESULT: The above program has been executed sucessfully.

Input: User selects operation (1-5) + number if pushing.

s
Output:

 Push: (item added).

 Pop/Peeks: Returns item or "Underflow".
 Display: Shows stack (top to bottom) or "Stack Empty".
 Exit: Stops program.

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

9. AIM: Write a python program for factorial of a natural number using

recursion .

ALGORITHM:

1. Create a function factorial that takes an integer n as an argument.

2. Check if n is 0 or 1. If so, return 1, as 0! and 1! are both equal to 1.
3. If n is greater than 1, recursively call the factorial function with n-1 and
multiply the result by n.
4. Return the result.

PROGRAM:

def factorial(n):
if n == 0 or n == 1:
return 1
else:
return n * factorial(n - 1)

n = 5 # Input value for which we want to calculate the factorial

result = factorial(n)
print( "The factorial of ", n , " is " , result")

RESULT: The above program has been executed sucessfully.

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

INPUT
The program is set to calculate the factorial of n = 5

OUTPUT
The factorial of 5 is 120

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

10. Aim : Write a python program for checking if a string is a palindrome

using a recursive function .

Algorithm:

1. Create a recursive function is_palindrome that takes a string s as an

argument.
2. In the base case, check if the length of the string is 0 or 1. If so, return True
because a single character or an empty string is a palindrome.
3. In the recursive case, check if the first and last characters of the string are
the same.
4. If they are, continue the recursion with the substring that excludes these
characters.
5. If the first and last characters are not the same, return False.
6. Continue the recursion until the base case is reached.
7. Return True if the string is a palindrome and False if it's no
Program:

def is_palindrome(s):
# Base case: If the string is empty or has only one character, it's a palindrome
if len(s) <= 1:
return True

# Recursive case: Check if the first and last characters are the same
if s[0] == s[-1]:
return is_palindrome(s[1:-1])
else:
return False

if __name__ == "__main__":
input_str = "racecar" # Input value to check if it's a palindrome
if is_palindrome(input_str):
print(f"{input_str} is a palindrome")
else:
print(f"{input_str} is not a palindrome")

Result: The above program has been executed sucessfully.

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

INPUT
The program is set to check if the string "racecar" is a palindrome

OUTPUT
racecar is a palindrome

=============================================
11. Aim : Write a program to search an element in a list and display the
frequency of element present in list and their location using Linear search.

Algorithm:

1. Start
2. Initialize variables:
3. arr: The input list to search in.
4. target: The element to search for in the list.
5. count: Initialize a variable to keep track of the frequency of the element
(set to 0).
6. locations: Initialize an empty list to store the indices where the element is
found.
7. For each element at index i in the list arr: a. If arr[i] is equal to target: i.
Increment count by 1. ii. Append i to the locations list.
8. If count is greater than 0: a. Print "Element target was found count times
in the list." b. Print "Locations of target in the list: locations."
9. Otherwise, if count is 0: a. Print "Element not found in the list."
10. End

Program:

def linear_search(arr, target):

count = 0
locations = []

for i in range(len(arr)):
if arr[i] == target:
count += 1
locations.append(i)

return count, locations

# Input values
input_list = [3, 5, 2, 6, 8, 3, 4, 3, 9]
element_to_search = 3

# Perform linear search

frequency, locations = linear_search(input_list, element_to_search)

# Output
print("Element {element_to_search} was found " , frequency , " times in the
list.")
if frequency > 0:
print("Locations of {element_to_search} in the list: " , locations)
else:
print("Element not found in the list.")

Result : The above prgram has been executed sucessfully.

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

Input :
Input values: 3
input_list: The list in which you want to search for an element.
element_to_search: The element you want to search for in the list.

Output: :
Element 3 was found 3 times in the list.
Locations of 3 in the list: [0, 5, 7]

============================================
12. Aim: Write a program to pass list to a function and double the odd values
and half even values of a list and display list element after changing

Algorithm:

1. Start
2. Initialize variables:
3. input_list: The list of numbers to be processed.
4. output_list: An empty list to store the modified values.
5. Create a function modify_list that accepts a list as a parameter.
a. Initialize an empty list result to store the modified values.
b. For each element num in the input list:
6. If num is odd (num % 2 != 0), double it and append to result.
7. If num is even (num % 2 == 0), halve it and append to result.
8. Return the result list.
9. Call the modify_list function with input_list as the argument and store the
result in output_list.
10. Display the modified list output_list.
11. End

Program :

def modify_list(input_list):
result = []
for num in input_list:
if num % 2 != 0: # Odd number
result.append(num * 2)
else:
result.append(num // 2) # Even number (integer division)
return result

# Input values
input_list = [2, 5, 8, 11, 14, 17]

# Call the modify_list function to modify the list

output_list = modify_list(input_list)

# Display the modified list

print("Modified List:", output_list)

Result:The above program has been executed successfully.

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

Input : : input_list: The list of numbers [2, 5, 8, 11, 14, 17].

Output: :
Modified List: [1, 10, 4, 22, 7, 34]

13. Aim: Write a program to find if a given number is prime number.

Algorithm:

1. Start
2. Input a number n from the user.
3. Validate Input:
o If n is not a valid integer, display an error message and Stop.
4. Check if n is less than 2:
o If true, n is not prime. Display "Not a Prime Number" and Stop.
5. Loop from i = 2 to i <= square root of n:
o If n is divisible by i (i.e., n % i == 0):
 n is not prime. Display "Not a Prime Number" and Stop.
6. If loop completes without finding a divisor:
o n is prime. Display "Prime Number".
7. Stop

Program :

def is_prime(n):
# Numbers less than 2 are not prime
if n < 2:
return False
# Check for divisibility from 2 to square root of n
for i in range(2, int(n ** 0.5) + 1):
if n % i == 0:
return False
return True

# Main program
try:
num = int(input("Enter a number to check: "))
if is_prime(num):
print(num , " is a Prime Number")
else:
print( num , " is not a Prime Number")
except ValueError:
print("Please enter a valid integer!")
Result:The above program has been executed sucessfully.

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

Input: Integer number (e.g., 7)

Output:

 Prime: "7 is a Prime Number"

 Not prime: "8 is not a Prime Number"
 Invalid input: "Please enter a valid integer!"

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

14. Aim: Write a program to pass a string to a function and count how many
vowels are present in the string.

Algorithm:

1. Start
2. Initialize a variable vowel_count to 0 to keep track of the count of vowels.
3. Input a string from the user and store it in a variable input_string.
4. Create a function count_vowels that accepts a string as a parameter:
a. Initialize vowel_count to 0 within the function.
b. For each character char in the string: i. Convert char to lowercase to
ensure case-insensitive matching. ii. Check if char is a vowel ('a' or
'A') using if and elif statements. iii. If char is a vowel, increment
vowel_count by 1.
c. Return vowel_count.
5. Call the count_vowels function with the input_string as the argument and
store the result in vowel_count.
6. Display the count of vowels in the string.
7. End

Program :

def count_vowels(input_string):

vowel_count = 0
for char in input_string:
char_lower = char.lower()
if char_lower == 'a':
vowel_count += 1
elif char_lower == 'e':
vowel_count += 1
elif char_lower == 'i':
vowel_count += 1
elif char_lower == 'o':
vowel_count += 1
elif char_lower == 'u':
vowel_count += 1

return vowel_count

# Input a string from the user

input_string = input("Enter a string: ")

# Call the count_vowels function to count vowels

vowel_count = count_vowels(input_string)

# Display the count of vowels in the string

print(f"Number of vowels in the string: {vowel_count}")
Result: The program has been executed successfully.

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

Input : :

Enter a string: Hello, World! This is a Test String.

Output: :
Number of vowels in the string: 8

15. Aim: Write a program to print a pyramid pattern like this :

A
AB
ABC
ABCD
AB C D E

Algorithm:

1. Start
2. Input the number of rows n for the pyramid pattern from the user.
3. Create a loop that iterates from 1 to n to represent the current row:
a. Create an inner loop that iterates from 1 to the current row number:
i. Inside the inner loop, calculate and print the corresponding
alphabet character. You can use the chr() function to convert a
number to its corresponding character.
b. Move to the next line to start a new row.
4. End

Program :

# Input the number of rows for the pyramid pattern

n = int(input("Enter the number of rows for the pyramid pattern: "))

# ASCII value of 'A'

start_char = ord('A')

# Create the pyramid pattern

for i in range(1, n + 1):
for j in range(i):
char = chr(start_char + j)
print(char, end=" ")
print() # Move to the next line

Result: The program has been executed successfully

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

Input : :
The user is prompted to enter the number of rows for the pyramid pattern (e.g.,
5).

Output: :
A
AB
ABC
ABCD
ABCDE

16 Aim : To create two tables for stationary and consumer and execute the
given commands using SQL.

TABLE:STATIONARY

S_ID StationaryN Company Price

ame
DP01 DotPen ABC 10
PL02 Pencil XYZ 6
ER05 Eraser XYZ 7
PL01 Pencil CAM 5
GP02 GelPen ABC 15

TABLE: CONSUMER

C_ID ConsumerName Address S_ID

01 Good Learner Delhi PL01
06 Write Well Mumbai GP02
12 Topper Delhi DP01
15 Write & Draw Delhi PL02
16 Motivation Bangalore PL01

i) To display the details of those Consumers whose Address is Delhi

ii) To display the details of Stationary whose Price is in the range of 8 to

15(Both values included) .

iii) To display the ConsumerName , Address from table Consumer and Company
and Price from table Stationery with their corresponding matching S_ID .
iv) To increase the Price of all Stationary by 2.

v) To display distinct Company from STATIONARY .

CREATE TABLE STATIONARY (S_ID char(5) NOT NULL PRIMARY KEY,

StationaryName char(25), Company char(5), Price int);

INSERT INTO STATIONARY VALUES(“DP01” , “Dot Pen”, “ABC”, 10);

INSERT INTO STATIONERY VALUES(“PL02” , “Pencil”, “XYZ”, 6)

CREATE TABLE CONSUMER (C_ID int , ConsumerName char(25) Address

char(25), S_ID char(5));

Result : The above program has been executed sucessfully.

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

OUTPUT:

i) Select * from consumer where address=”delhi”;

c_id consumername address S_id

1 good learner delhi PL01
12 topper delhi DP02
15 write & draw delhi PL02

ii) select * from stationary where price between 8 and 15;

S_id stationary company price

Dp01 dot pen ABC 10
GP02 gel pen ABC 15
iii) select consumername, address, company, price from stationery, consumer
where stationery.s_id=consumer.s_id;

consumername address company Price

good learner delhi CAM 5
write well mumbai ABC 15
topper delhi ABC 10
write&draw delhi XYZ 6
motivation bangalore CAM 5

iv) update stationery set price=price+2;

select * from stationery;

S_id stationary company Price

DP01 Dot pen ABC 12
PL02 Pencil XYZ 8
ER05 Eraser XYZ 9
PL01 Pencil CAM 7
GP02 Gel pen ABC 17

v) select distinct(company) from stationery;

Company
ABC
XYZ
CAM

17. AIM: To create two tables for item and traders and execute the given
commands using SQL.
TABLE:ITEM

Code IName Qty Price Company TCode

1001 DIGITAL 120 11000 XENTIA T01

PAD 121
1006 LED 70 38000 SANTORA T02
SCREEN
40
1004 CAR GPS 50 2150 GEOKNO T01
SYSTEM W
1003 DIGITAL 160 8000 DIGICLIC T02
CAMERA K
12X
1005 PEN 600 1200 STOREHO T03
DRIVE ME
32GB

TABLE:TRADERS

TCode TName City

T01 ELECTRONICS MUMBAI

SALES
T03 BUSY STORE DELHI
CORP
T02 DISP HOUSE CHENNAI
INC

i) To display the details of all the items in ascending order of item names (i.e
IName)

ii) To display item name and price of all those items, whose price is in the range
of 10000 and 22000 (both values inclusive)

iii) To display the number of items , which are traded by each trader. The
expected output of this query should be
T01 2
T02 2
T03 1
iv) To display the Price , item name(i.e IName) and quantity(i.e Qty) of those
items which have quantity more than 150.

v) To display the names of those traders, who are either from DELHI or from
MUMBAI.

CREATE TABLE ITEM(Code int , IName char(25) , Qty int , Price int , Company
char(25), TCode char(5));

INSERT INTO ITEM VALUES(1001,”DIGITAL PAD 121”,120, 11000,”XENTIA”,

“T01”);

INSERT INTO ITEM VALUES(1006,”LED SCREEN 40”,70, 38000,”SANTORA”,

“T02”);

CREATE TABLE TRADERS(TCode char(5) , TName char(25), City char(20));

INSERT INTO TRADERS VALUES(“T01”,”ELECTRONICS SALES”,”MUMBAI”);

INSERT INTO TRADERS VALUES( “T03”,”BUSY STORE CORP”,”DELHI”);

RESULT: Thus the given program executed successfully.

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

OUTPUT:

i) select * from ITEM order by IName;

Code IName Qty Price Company TCode
1004 CAR GPS 50 2150 GEOKNO T01
SYSTEM W
1003 DIGITAL 160 8000 DIGICLIC T02
CAMERA 12X K
1001 DIGITAL PAD 120 11000 XENTIA T01
121
1006 LED SCREEN 70 38000 SANTORA T02
1005 PEN DRIVE 600 1200 STORE T03
32GB HOME

ii) select IName , Price from ITEM where Price between 10000 and 22000;

IName Price
DIGITAL PAD 121 11000

iii) select TCode , count(*) from ITEM group by TCode;

Tcode Count(*)
T01 2
T02 2
T03 1

iv) select Price , IName , Qty from ITEM where Qty>150;

Price IName Qty

8000 DIGITAL 160
CAMERA 12X
1200 PEN DRIVE 600
32GB

v) select TName from TRADERS where City in (“DELHI”,”MUMBAI”);

TName

ELECTRONICS SALES
BUSY STORE CORP
===================================================

18. AIM: To create two tables for doctor and salary and execute the given
commands using SQL.

TABLE:DOCTOR

ID NAME DEPT SEX EXPERIENCE

101 John ENT M 12

104 Smith ORTHOPEDIC M 5
107 George CARDIOLOGY M 10
114 Lara SKIN F 3
109 K George MEDICINE F 9
105 Johnson ORTHOPEDIC M 10
117 Lucy ENT F 3
111 Bill MEDICINE F 12
130 Morphy ORTHOPEDIC M 15

TABLE: SALARY

ID BASIC ALLOWANCE CONSULTATION

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

i) Display NAME of all doctors who are in “MEDICINE” having more than 10
years experience from table DOCTOR

ii) Display the average salary of all doctors working in “ENT” department using
the tables DOCTOR and SALARY. (Salary=BASIC+ALLOWANCE)
iii) Display minimum ALLOWANCE of female doctors.

iv) Display DOCTOR.ID , NAME from the table DOCTOR and BASIC , ALLOWANCE
from the table SALARY with their corresponding matching ID.

v) To display distinct department from the table doctor.

CREATE TABLE DOCTOR(ID int NOT NULL PRIMARY KEY, NAME char(25) , DEPT
char(25) , SEX char , EXPERIENCE int);

INSERT INTO DOCTOR VALUES(101,”John”, “ENT”,’M’,12);

INSERT INTO DOCTOR VALUES(104,”Smith”, “ORTHOPEDIC”,’M’,5);

CREATE TABLE SALARY(ID int, BASIC int, ALLOWANCE int, CONSULTATION int);

INSERT INTO SALARY VLAUES(101, 12000,1000,300);

INSERT INTO SALARY VLAUES(104, 23000,2300,500);

RESULT: Thus the given program executed successfully.

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

OUTPUT:

i) select NAME from DOCTOR where DEPT=”MEDICINE” and EXPERIENCE >10;

NAME
Bill
ii) select avg(BASIC+ALLOWANCE) “avg salary” from DOCTOR , SALARY where
DOCTOR.ID=SALARY.ID and DEPT=”ENT”;

Avg salary
13000.00

iii) select min(ALLOWANCE) from SALARY, DOCTOR where SEX=’F’

and DOCTOR.ID=SALARY.ID;

min(ALLOWANCE)
1700

iv) select DOCTOR.ID, NAME, BASIC ,ALLOWANCE from DOCTOR,SALARY where

DOCTOR.ID=SALARY.ID;

ID NAME BASIC ALLOWANC

E
101 John 12000 1000
104 Smith 23000 2300
107 George 32000 4000
109 K George 42000 1700
114 Lara 12000 5200
130 Morphy 21700 2600

v) select distinct(DEPT) from DOCTOR;

DEPT
ENT
ORTHOPEDIC
CARDIOLOGY
SKIN
MEDICINE
================================================

19 . AIM: To create two tables for company and customer and execute the given
commands using SQL.

TABLE : COMPANY

TABLE : CUSTOMER

1. To display those company name which are having price less than 30000.
2. To display the name of the companies in reverse alphabetical order.
3. To increase the price by 1000 for those customer whose name starts with ‘S’
4. To add one more column totalprice with decimal (10,2) to the table customer
5. To display the details of company where productname as mobile.

CREATE TABLE COMPANY(cid int(3) , name varchar(15) , city varchar(10),

productname varchar(15));
INSERT INTO COMPANY VALUES(111, ‘SONA’, ‘DELHI’, ‘TV’);

CREATE TABLE CUSTOMER(custid int(3), name varchar(15), price int(10),

qty int(3) , cid int(3));

INSERT INTO CUSTOMER VALUES(101, ‘ROHAN SHARMA’, 70000,

20,222);

RESULT:
Thus the given program executed successfully.

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

OUTPUT: :

1. select name from company where company.cid=customer.

cid and price < 30000;

NAME
NEHA SONI

2. select name from company order by name desc;

NAME
SONY
ONIDA
NOKIA
DELL
BLACKBERRY

3. update customer set price = price + 1000 where name like ‘s%’;

select * from customer;

CUSTID NAME PRICE QTY CID

101 ROHAN SHARMA 70000 20 222
102 DEEPAK KUMAR 50000 10 666
103 MOHAN KUMAR 30000 5 111
104 SAHIL BANSAL 36000 3 333
105 NEHA SONI 25000 7 444
106 SONA AGARWAL 21000 5 333
107 ARUN SINGH 50000 15 666

4. alter table customer add totalprice decimal(10,2);

Select * from customer;

CUSTID NAME PRICE QTY CID TOTALPR

CE
101 ROHAN SHARMA 70000 20 222 NULL
102 DEEPAK KUMAR 50000 10 666 NULL
103 MOHAN KUMAR 30000 5 111 NULL
104 SAHIL BANSAL 36000 3 333 NULL
105 NEHA SONI 25000 7 444 NULL
106 SONA AGARWAL 21000 5 333 NULL
107 ARUN SINGH 50000 15 666 NULL

CUSTID NAME PRICE QTY CID TOTALPRICE

101 ROHAN SHARMA 70000 20 222 NULL

102 DEEPAK KUMAR 50000 10 666 NULL
103 MOHAN KUMAR 30000 5 111 NULL
104 SAHIL BANSAL 36000 3 333 NULL
105 NEHA SONI 25000 7 444 NULL
106 SONA AGGARWAL 21000 5 333 NULL

5. select * from company where productname=’mobile’;

CID NAME CITY PRODUCTNA

ME
222 NOKIA MUMBAI MOBILE
444 SONY MUMBAI MOBILE
555 BLACKBERRY MADRAS MOBILE

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

20. AIM: To create table for teacher and execute the given commands using SQL.

TABLE : TEACHER

No Name Age Department DateofJoi Salary Sex

n
1 Jishnu 34 Computer 10/01/97 12000 M
2 Sharmila 31 History 24/03/98 20000 F
3 Santhosh 32 Maths 12/12/96 30000 M
4 Shanmathi 35 History 01/07/99 40000 F
5 Ragu 42 Maths 05/09/97 25000 M
6 Shiva 50 History 27/02/97 30000 M
7 Shakthi 44 Computer 25/02/97 21000 M
8 Shriya 33 Maths 31/07/97 20000 F

1. To show all information about the teacher of history department.

2. To list the names of female teacher who are in Maths department.
3. To list names of all teachers with their date of joining in ascending order.
4. To count the number of teachers with age>35.
5. To count the number of teachers department wise.

CREATE TABLE TEACHER(No int(2), Name varchar(15), Age int(3) , Department

varchar(15), Dateofjoin varchar(15) , Salary int(7) , Sex char(1));

INSERT INTO TEACHER VALUES(1,’Jishnu’,34,’Computer’,’10/01/97’,12000,’M’);

RESULT: Thus the given program executed successfully.

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

OUTPUT: (OUTPUT WILL COME ON RIGHT SIDE)

1. select * from teacher where Department=’History’;

No Name Age Depart DateofJo Salary Sex

ment in
2 Sharmila 31 History 24/03/9 20000 F
8
4 Shanmathi 35 History 01/07/9 40000 F
9
6 Shiva 50 History 27/02/9 30000 M
7

2. select Name from teacher where Department=’Maths’ and Sex=’F’;

Name
Shriya
3. select Name , Dateofjoin from teacher order by Dateofjoin asc;

Name Dateofjoin
Santhosh 12/12/96
Jishnu 10/01/97
Shakthi 25/02/97
Shiva 27/02/97
Shriya 31/07/97
Ragu 05/09/97
Sharmila 24/03/98
Shanmathi 01/07/99

4. select count(*) from teacher where Age>35;

count(*)
3

5. select Department , count(*) from teacher group by department;

Department count(*)
Computer 2
History 3
Maths 3

21. AIM: To integrate SQL with Python by importing the MySQL module and
extracting data from result set

PROGRAM:
import mysql.connector as sqltor

mycon=sqltor.connect(host=”localhost”, user=”root”, password=”root”,

databse=”trinity”)
if mycon.is_connected( ) = = False:

print(“Error connecting to MySQL database”)

cursor=mycon.cursor( )

cursor.execute(“select * from student”)

data=cursor.rowcount(3)
count=cursor.rowcount

for row in data:

print(row)

mycon.close( )

RESULT: Thus the given program executed successfully.

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

OUTPUT:

(1001, “Vinusha”, 50,70, 80 , “Namakkal”)

(1001, “Aswin”, 54,82, 85 , “Erode”)
(1001, “Bheem”, 90,73, 78 , “Salem”)
22. AIM: To integrate SQL with Python by importing the MySQL module and
extracting data from result set

PROGRAM:

import mysql.connector as sqltor

mycon=sqltor.connect(host=”localhost”, user=”root”, password=”root”,

databse=”trinity”)
if mycon.is_connected( ) = = False:
print(“Error connecting to MySQL database”)
cursor=mycon.cursor( )
cursor.execute(“select * from student”)
data=cursor.fetchone( )
count=cursor.rowcount
print(“Total number of rows retrieved from resultset :”, count)
data=cursor.fetchone( )
count=cursor.rowcount
print(“Total number of rows retrieved from resultset :”, count)
data=cursor.fetchone( )
count=cursor.rowcount
print(“Total number of rows retrieved from resultset :”, count)
data=cursor.fetchmany(3)
count=cursor.rowcount
print(“Total number of rows retrieved from resultset :”, count)

RESULT: Thus the given program executed successfully.

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

OUTPUT:

Total number of rows retrieved from resultset : 1

Total number of rows retrieved from resultset : 2
Total number of rows retrieved from resultset : 5

23. AIM: Integrate SQL with Python by importing the MySQL module to search
an employee using eno , if it is present in table display the record

PROGRAM:

import mysql.connector as mc
mycon=mc.connect(host='localhost',user='root',password='root1',
database='db12')
if mycon.is_connected( ):
print("Py->Sql connected")

eno=int(input("Enter num:"))

mcursor=mycon.cursor( )
mcursor.execute("select * from emp")
allrow=mcursor.fetchall( )

for row in allrow:

if row[0]==eno:
print(row)
mycon.commit( )
mycon.close( )

RESULT: Thus the given program executed successfully.

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

OUTPUT:

Py->Sql connected
Enter num : 103
(103,’Cinu , 43, ‘Namakkal’)

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

24. AIM: To integrate SQL with Python by importing the MySQL module to
search a student using rollno and delete the record.

PROGRAM :

import mysql.connector as mc
mycon=mc.connect(host='localhost',user='root',password='root1',
database='db12')

if mycon.is_connected():
print("Py->Sql connected")

eno=int(input("Enter num:"))
mcursor=mycon.cursor()
mcursor.execute("select * from emp")

allrow=mcursor.fetchall()
for row in allrow:
if row[0]==eno:
mcursor.execute("delete from emp where eno={}".format(eno))
mcursor.execute("select * from emp")
print(mcursor.fetchall())
mycon.commit()
mycon.close()

RESULT:
Thus the given is program executed successfully.

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

OUTPUT:
Py -> sql is connected Enter num : 102 (101,’Anu’,23,’Salem’)
(103,’Cinu’,43,’Namakkal’)
(104, ‘Nishanth’, 46,’Chennai’)
(105, ‘Nanda’, 56, ‘Erode’)