Program

File

Computer Science
with Python Class- XII
Submitted to:
Submitted by:
Ms. Neha Garg Lavish
Bhandari
(H.O.D. Computer Science) Class
XII-A
Roll No. :
 Contents
S. NO. QUESTIONS T. SIGN
1. Write a program to check a number whether it is a
palindrome or Armstrong number.
2. Write a program to display ASCII code of a
character and vice versa.
3. Write a program to print Fibonacci Series to nth
Term
4. Write a program to print Factorial of a number
5. Write a program to enter the numbers and perform
Linear search, Binary search on a list in the form of
menu driven programming.
6. Write a program that checks for presence of a value
inside a dictionary and prints its keys with ignore
case.
7. Write a program to enter the numbers and perform
Bubble sort, Insertion sort on a list in the form of
menu driven programming.
8. Write a program using math module that receives
two number and return the result of the following
operations (sqrt, pow, gcd).
9. Write a program to calculate:
a) Area of circle [A=πr2]
b) Area of square [A=a*a]
c) Area of rectangle [A=l*b]
10. Write a program to count and display number of
words in a text file.
11. Program to read and display the text file content
line by line with each word separated by “#”
12. Write a program to copy all the lines that not
contain the character ‘a’ in a file and write it to
another file.
13. Write a Program to read the contents of a text file
and display the size of file, total number of
consonants, uppercase, vowels and lowercase
characters.
14. Program to create binary file having Roll no and
Name and perform the following operations:
a. Append data
b. Delete a record
c. Update a record
d. Search a record
e. Display all records
15. Write a program to perform the following
operations with .csv file having empno, name,
salary:
a) Append data
b) Delete a record
c) Update a record
d) Search a record
e) Display all records
16. Simulating Dice using Random module -- Write a
program using python, to generate random number
1-6, simulating a dice
17. Write a program to implement PUSH/POP
operations on Stack using list.
18. Phishing - common word occurring-- Write an
application program to take 10 sample phishing
email, and find the most common word occurring
using List
19. Facebook using Dictionary-- Write an application
program to create profile of users using dictionary,
also let the users to search, delete and update their
profile like face book application.
20. Employee Details– Interfacing with MySQL
a. Write an application program to interface
python with SQL database for employee’s data
insertion.
b. Write an application program to interface
python with SQL database for employee’s data
deletion.
c. Write an application program to interface
python with SQL database for employee’s data
updation.
d. Write an application program to interface
python with SQL database for employee’s data
 display.
e. Write an application program to interface
python with SQL database for employee’s data
searching by applying a condition (maximum salary
of a teacher)
21. MySQL Commands:
1. CREATE DATABASE
2. OPEN COMMAND
3. CREATE TABLE and DESCRIBE
4. INSERT COMMAND & display using SELECT
STATEMENT
5. UPDATE COMMAND
6. SELECTION USING DISTINCT KEYWORD
7. PROJECTION USING WHERE CLAUSE
8. SORTING RESULTS BY ORDER BY
9. DATE FUNCTIONS
10. GROUP BY CLAUSE
11. HAVING CLAUSE
12. ALTER COMMAND
13. JOINS
14. NATURAL JOIN

1. WRITE A PROGRAM TO CHECK A NUMBER

WHETHER IT IS A PALINDROME OR ARMSTRONG
NUMBER.

def is_palindrome(number):
# Convert the number to a string to make it easier to check for
palindromes
number_str = str(number)
# Compare the string with its reverse to check for palindromes
return number_str == number_str[::-1]
def is_armstrong(number):
# Calculate the number of digits in the number
num_digits = len(str(number))
# Initialize a variable to store the sum of the nth powers of its
digits
sum_of_powers = 0
temp = number
while temp > 0:
digit = temp % 10
sum_of_powers += digit ** num_digits
temp //= 10
# Check if the number is an Armstrong number
return number == sum_of_powers

# Input from the user

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

if is_palindrome(number):
print(f"{number} is a palindrome.")
else:
print(f"{number} is not a palindrome.")

if is_armstrong(number):
print(f"{number} is an Armstrong number.")
else:
print(f"{number} is not an Armstrong number.")

OUTPUT:

2. WRITE A PROGRAM TO DISPLAY ASCII CODE OF A

CHARACTER AND VICE VERSA.

def char_to_ascii(character):
"""Convert a character to its ASCII code."""
if len(character) == 1:
return ord(character)
 else:
return None

def ascii_to_char(ascii_code):
"""Convert an ASCII code to its corresponding character."""
if 0 <= ascii_code <= 127:
return chr(ascii_code)
else:
return None

while True:
print("Choose an option:")
print("1. Convert character to ASCII code")
print("2. Convert ASCII code to character")
print("3. Quit")

choice = input("Enter your choice (1/2/3): ")

if choice == "1":
character = input("Enter a character: ")
ascii_value = char_to_ascii(character)
if ascii_value is not None:
print(f"The ASCII code of '{character}' is {ascii_value}")
else:
print("Invalid input. Please enter a single character.")
elif choice == "2":
try:
ascii_code = int(input("Enter an ASCII code (0-127): "))
character = ascii_to_char(ascii_code)
if character is not None:
print(f"The character with ASCII code {ascii_code} is
'{character}'")
else:
print("Invalid input. Please enter a valid ASCII code (0-
127).")
except ValueError:
print("Invalid input. Please enter a valid integer.")
elif choice == "3":
print("Goodbye!")
break
else:
print("Invalid choice. Please enter 1, 2, or 3.")

OUTPUT:
3. WRITE A PROGRAM TO PRINT FIBONACCI SERIES TO
NTH TERM.

def fibonacci(n):
fib_series = [0, 1]
while len(fib_series) < n:
next_term = fib_series[-1] + fib_series[-2]
fib_series.append(next_term)
return fib_series[:n]

n = int(input("Enter the value of 'n' for the Fibonacci series: "))

if n <= 0:
print("Please enter a positive integer.")
else:
result = fibonacci(n)
print(f"Fibonacci Series up to the {n}th term:")
print(result)

OUTPUT:
4. WRITE A PROGRAM TO PRINT FACTORIAL OF A
NUMBER.

def factorial(n):
"""Calculate the factorial of a number 'n'."""
if n == 0:
return 1
elif n < 0:
return None # Factorial is not defined for negative numbers
else:
result = 1
for i in range(1, n + 1):
result *= i
return result

num = int(input("Enter a non-negative integer to calculate its factorial:

"))
if num < 0:
print("Factorial is not defined for negative numbers.")
else:
result = factorial(num)
if result is not None:
print(f"The factorial of {num} is {result}")
else:
print("Factorial is not defined for negative numbers.")

OUTPUT:
5. WRITE A PROGRAM TO ENTER THE NUMBERS AND
PERFORM LINEAR SEARCH, BINARY SEARCH ON A LIST
IN THE FORM OF MENU DRIVEN PROGRAMMING.

def linear_search(arr, target):

"""Perform a linear search to find the target in the list."""
for i in range(len(arr)):
if arr[i] == target:
return i
return -1

def binary_search(arr, target):

"""Perform a binary search to find the target in a sorted list."""
left, right = 0, len(arr) - 1
while left <= right:
mid = (left + right) // 2
if arr[mid] == target:
return mid
elif arr[mid] < target:
left = mid + 1
else:
right = mid - 1
return -1

def main():
numbers = []

while True:
print("\nMenu:")
print("1. Add a number to the list")
print("2. Perform linear search")
print("3. Perform binary search")
print("4. Quit")

choice = input("Enter your choice (1/2/3/4): ")

if choice == "1":
num = int(input("Enter a number to add to the list: "))
[Link](num)
print(f"{num} added to the list.")
elif choice == "2":
target = int(input("Enter the number to search for using linear
search: "))
index = linear_search(numbers, target)
if index != -1:
print(f"{target} found at index {index}.")
else:
print(f"{target} not found in the list.")
elif choice == "3":
[Link]()
target = int(input("Enter the number to search for using binary
search: "))
index = binary_search(numbers, target)
if index != -1:
print(f"{target} found at index {index}.")
else:
 print(f"{target} not found in the list.")
elif choice == "4":
print("Goodbye!")
break
else:
print("Invalid choice. Please enter 1, 2, 3, or 4.")

main()

OUTPUT:

6. WRITE A PROGRAM THAT CHECKS FOR PRESENCE OF A

VALUE INSIDE A DICTIONARY AND PRINTS ITS KEYS WITH
IGNORE CASE.
def find_keys_by_value(d, value):
"""Find keys in a dictionary that have the given value (case-
insensitive)."""
keys = []
for key, val in [Link]():
if isinstance(val,str) and [Link]()==[Link]():
[Link](key)
return keys
# Sample dictionary
sample_dict = {
"Name": "John",
"Age": 30,
"Country": "USA",
"Occupation": "Engineer",
}

search_value = input("Enter a value to search for (case-insensitive): ")

keys_with_value = find_keys_by_value(sample_dict, search_value)

if keys_with_value:
print(f"The value '{search_value}' is associated with the following
keys (case-insensitive):")
for key in keys_with_value:
print(key)
else:
print(f"The value '{search_value}' was not found in the dictionary.")

OUTPUT:

7. WRITE A PROGRAM TO ENTER THE NUMBERS AND

PERFORM BUBBLE SORT, INSERTION SORT ON A LIST IN
THE FORM OF MENU DRIVEN PROGRAMMING.

def bubble_sort(arr):
n = len(arr)
for i in range(n - 1):
for j in range(0, n - i - 1):
if arr[j] > arr[j + 1]:
arr[j], arr[j + 1] = arr[j + 1], arr[j]

def insertion_sort(arr):
for i in range(1, len(arr)):
key = arr[i]
j = i - 1
while j >= 0 and key < arr[j]:
arr[j + 1] = arr[j]
 j -= 1
arr[j + 1] = key

numbers = []

while True:
print("\nMenu:")
print("1. Enter a number")
print("2. Bubble Sort")
print("3. Insertion Sort")
print("4. Display List")
print("5. Exit")

choice = input("Enter your choice (1/2/3/4/5): ")

if choice == '1':
number = int(input("Enter a number: "))
[Link](number)
elif choice == '2':
bubble_sort(numbers)
print("List sorted using Bubble Sort.")
elif choice == '3':
insertion_sort(numbers)
print("List sorted using Insertion Sort.")
elif choice == '4':
if not numbers:
print("List is empty.")
else:
print("List:", numbers)
elif choice == '5':
print("Exiting the program.")
break
else:
print("Invalid choice. Please enter a valid option (1/2/3/4/5).")

OUTPUT:
8. WRITE A PROGRAM USING MATH MODULE THAT
RECEIVES TWO NUMBER AND RETURN THE RESULT OF
THE FOLLOWING OPERATIONS (SQRT, POW, GCD).

import math

num1 = eval(input("Enter the first number: "))

num2 = eval(input("Enter the second number: "))

sqrt_result = [Link](num1)
pow_result = [Link](num1, num2)
gcd_result = [Link](int(num1), int(num2))

print(f"Square root of {num1} is: {sqrt_result}")

print(f"{num1} raised to the power {num2} is: {pow_result}")
print(f"Greatest Common Divisor of {int(num1)} and {int(num2)} is:
{gcd_result}")

OUTPUT:

9. WRITE A PROGRAM TO CALCULATE:

a) AREA OF CIRCLE [A=ΠR2]

b) AREA OF SQUARE [A=A*A]
c) AREA OF RECTANGLE [A=L*B]

#save it in a file named circle

import math

def calculate_area(radius):
return [Link] * radius**2

#save it in a file named square

def calculate_area(side_length):
return side_length**2

#save it in a file named rectangle

def calculate_area(length, width):
return length * width

import circle
import square
import rectangle

while True:
print("\nMenu:")
print("1. Calculate the area of a circle")
print("2. Calculate the area of a square")
print("3. Calculate the area of a rectangle")
print("4. Quit")

choice = input("Enter your choice (1/2/3/4): ")

if choice == '1':
radius = eval(input("Enter the radius of the circle: "))
area = circle.calculate_area(radius)
print(f"The area of the circle is: {area}")
elif choice == '2':
side_length = eval(input("Enter the side length of the square: "))
area = square.calculate_area(side_length)
print(f"The area of the square is: {area}")
elif choice == '3':
length = eval(input("Enter the length of the rectangle: "))
width = eval(input("Enter the width of the rectangle: "))
area = rectangle.calculate_area(length, width)
print(f"The area of the rectangle is: {area}")
elif choice == '4':
print("Exiting the program.")
break
else:
print("Invalid choice. Please enter a valid option (1/2/3/4).")

OUTPUT:
10. WRITE A PROGRAM TO COUNT AND DISPLAY
NUMBER OF WORDS IN A TEXT FILE.
def count_words_in_file(file_name):
with open(file_name, 'r') as file:
text = [Link]()
words = [Link]() # Split the text into words using spaces
num_words = len(words)
return num_words

file_name = input("Enter the name of the text file: ")

num_words = count_words_in_file(file_name)
print(f"Number of words in the file: {num_words}")

OUTPUT:
11. PROGRAM TO READ AND DISPLAY THE TEXT FILE
CONTENT LINE BY LINE WITH EACH WORD SEPARATED
BY “#”

def display_content_with_separator(file_name):
with open(file_name, 'r') as file:
for line in file:
words = [Link]() # Split the line into words
formatted_line = "#".join(words) # Join words with "#"
print(formatted_line)

file_name = input("Enter the name of the text file: ")

display_content_with_separator(file_name)

OUTPUT:
12. WRITE A PROGRAM TO COPY ALL THE LINES THAT
NOT CONTAIN THE CHARACTER ‘A’ IN A FILE AND WRITE
IT TO ANOTHER FILE.

def copy_lines_without_a(input_file, output_file):

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

input_file = input("Enter the name of the source file: ")

output_file = input("Enter the name of the target file: ")

copy_lines_without_a(input_file, output_file)

OUTPUT:

13. WRITE A PROGRAM TO READ THE CONTENTS OF A

TEXT FILE AND DISPLAY THE SIZE OF FILE, TOTAL
NUMBER OF CONSONANTS, UPPERCASE, VOWELS AND
LOWERCASE CHARACTERS.
OUTPUT:
14. PROGRAM TO CREATE BINARY FILE HAVING ROLL NO
AND NAME AND PERFORM THE FOLLOWING
OPERATIONS:

a. APPEND DATA
b. DELETE A RECORD
c. UPDATE A RECORD
d. SEARCH A RECORD
e. DISPLAY ALL RECORDS
import pickle

file_name = 'student_records.bin'

def append_data():
roll_number = int(input("Enter Roll Number: "))
name = input("Enter Name: ")

records = []
try:
with open(file_name, 'rb') as file:
records = [Link](file)
except FileNotFoundError:
pass

[Link]({'roll_number': roll_number, 'name': name})

with open(file_name, 'wb') as file:

[Link](records, file)

print("Record appended successfully.")

def delete_record():
roll_number = int(input("Enter Roll Number to delete: "))

try:
with open(file_name, 'rb') as file:
records = [Link](file)
except FileNotFoundError:
print("No records to delete.")
return

new_records = [record for record in records if record['roll_number'] !=

roll_number]

with open(file_name, 'wb') as file:

[Link](new_records, file)

print("Record deleted successfully.")

def display_records():
try:
with open(file_name, 'rb') as file:
records = [Link](file)
print("\nStudent Records:")
for record in records:
print(f"Roll Number: {record['roll_number']}, Name:
{record['name']}")
except FileNotFoundError:
print("No records found.")

# Menu loop
while True:
print("\nMenu:")
 print("1. Append Data")
print("2. Delete Record")
print("3. Display Records")
print("4. Exit")

choice = input("Enter your choice (1-4): ")

if choice == '1':
append_data()
elif choice == '2':
delete_record()
elif choice == '3':
display_records()
elif choice == '4':
print("Exiting the program. Goodbye!")
break
else:
print("Invalid choice. Please enter a number between 1 and 4.")

OUTPUT:
15. WRITE A PROGRAM TO PERFORM THE FOLLOWING
OPERATIONS WITH .CSV FILE HAVING EMPNO, NAME,
SALARY:

a) APPEND DATA
b) DELETE A RECORD
c) UPDATE A RECORD
d) SEARCH A RECORD
e) DISPLAY ALL RECORDS

import csv

def append_record(file_name, empno, name, salary):

with open(file_name, 'a', newline='') as file:
writer = [Link](file)
[Link]([empno, name, salary])

def delete_record(file_name, target_empno):

records = []

with open(file_name, 'r', newline='') as file:

reader = [Link](file)
for row in reader:
empno, name, salary = row
if empno != target_empno:
[Link]([empno, name, salary])

with open(file_name, 'w', newline='') as file:

writer = [Link](file)
[Link](records)

def update_record(file_name, target_empno, new_name, new_salary):

records = []

with open(file_name, 'r', newline='') as file:

reader = [Link](file)
for row in reader:
empno, name, salary = row
if empno == target_empno:
name = new_name
salary = new_salary
[Link]([empno, name, salary])

with open(file_name, 'w', newline='') as file:

writer = [Link](file)
[Link](records)

def search_record(file_name, target_empno):

with open(file_name, 'r', newline='') as file:
reader = [Link](file)
 for row in reader:
empno, name, salary = row
if empno == target_empno:
return empno, name, salary
return None

def display_all_records(file_name):
with open(file_name, 'r', newline='') as file:
reader = [Link](file)
for row in reader:
empno, name, salary = row
print(f"Emp No: {empno}, Name: {name}, Salary: {salary}")

file_name = "employee_records.csv"

while True:
print("\nMenu:")
print("1. Append Record")
print("2. Delete Record")
print("3. Update Record")
print("4. Search Record")
print("5. Display All Records")
print("6. Exit")

choice = input("Enter your choice (1/2/3/4/5/6): ")

if choice == '1':
empno = input("Enter Employee Number: ")
name = input("Enter Name: ")
salary = input("Enter Salary: ")
append_record(file_name, empno, name, salary)
elif choice == '2':
target_empno = input("Enter Employee Number to delete: ")
delete_record(file_name, target_empno)
elif choice == '3':
target_empno = input("Enter Employee Number to update: ")
new_name = input("Enter New Name: ")
new_salary = input("Enter New Salary: ")
update_record(file_name, target_empno, new_name, new_salary)
elif choice == '4':
target_empno = input("Enter Employee Number to search: ")
result = search_record(file_name, target_empno)
if result:
empno, name, salary = result
print(f"Record found - Emp No: {empno}, Name: {name}, Salary:
{salary}")
else:
print("Record not found.")
elif choice == '5':
display_all_records(file_name)
elif choice == '6':
print("Exiting the program.")
break
else:
print("Invalid choice. Please enter a valid option (1/2/3/4/5/6).")

OUTPUT:
16. SIMULATING DICE USING RANDOM MODULE -- WRITE
A PROGRAM USING PYTHON, TO GENERATE RANDOM
NUMBER 1-6, SIMULATING A DICE.

import random

def roll_dice():
return [Link](1, 6)

while True:
input("Press Enter to roll the dice...")
result = roll_dice()
print(f"You rolled a {result}")

play_again = input("Roll again? (yes/no): ")

if play_again != 'yes':
print("Thanks for playing!")
break

OUTPUT:
17. WRITE A PROGRAM TO IMPLEMENT PUSH/POP
OPERATIONS ON STACK USING LIST.

# Initialize an empty stack using a list

stack = []

# Function to perform PUSH operation

def push(item):
[Link](item)
print(f"Pushed {item} onto the stack")

# Function to perform POP operation

def pop():
if not stack:
print("Stack is empty. Cannot pop.")
else:
item = [Link]()
print(f"Popped {item} from the stack")

# Menu-driven program to interact with the stack

while True:
print("\nStack Operations:")
print("1. PUSH")
print("2. POP")
print("3. Display Stack")
print("4. Quit")

choice = input("Enter your choice (1/2/3/4): ")

if choice == '1':
item = input("Enter an item to PUSH onto the stack: ")
push(item)
elif choice == '2':
pop()
elif choice == '3':
if not stack:
print("Stack is empty.")
 else:
print("Stack:", stack)
elif choice == '4':
print("Exiting the program.")
break
else:
print("Invalid choice. Please enter a valid option (1/2/3/4).")

OUTPUT:
18. PHISHING - COMMON WORD OCCURRING-- WRITE AN
APPLICATION PROGRAM TO TAKE 10 SAMPLE
PHISHING EMAIL, AND FIND THE MOST COMMON WORD
OCCURRING USING LIST.

# Sample phishing emails

phishing_emails = [
"Dear User, your account has been compromised. Please click the link to
reset your password.",
"Congratulations! You've won a free iPhone. Click the link to claim
your prize.",
"Urgent: Your bank account needs verification. Click the link to update
your information.",
"Your package has arrived. Click the link for tracking details.",
 "Important: Your account will be suspended if you don't verify your
identity. Click the link to verify.",
"You've been selected for a job interview. Click the link to confirm
your attendance.",
"Your account has been locked due to suspicious activity. Click the
link to unlock it.",
"You've won a $1,000 gift card. Click the link to redeem your prize.",
"Please confirm your email address by clicking the link.",
"Your payment has been received. Click the link for order details.",
]

# Tokenize the emails into words and count their frequencies

word_frequency = {}

for email in phishing_emails:

email = [Link]() # Convert to lowercase to ensure case-
insensitive counting
words = [Link]()

for word in words:

word = [Link]('.,?!()\'"') # Remove common punctuation marks
if word:
if word in word_frequency:
word_frequency[word] += 1
else:
word_frequency[word] = 1

# Find the most common word

most_common_word = max(word_frequency, key=word_frequency.get)
frequency = word_frequency[most_common_word]

print(f"The most common word in the phishing emails is '{most_common_word}'

with a frequency of {frequency} times.")

OUTPUT:

19. FACEBOOK USING DICTIONARY-- WRITE AN

APPLICATION PROGRAM TO CREATE PROFILE OF
USERS USING DICTIONARY, ALSO LET THE USERS TO
SEARCH, DELETE AND UPDATE THEIR PROFILE LIKE
FACE BOOK APPLICATION.

# Dictionary to store user profiles with unique usernames as keys

user_profiles = {}
def create_profile():
username = input("Enter your username: ")
if username in user_profiles:
print("Username already exists. Try a different one.")
else:
name = input("Enter your name: ")
age = input("Enter your age: ")
city = input("Enter your city: ")
user_profiles[username] = {'Name': name, 'Age': age, 'City': city}
print("Profile created successfully!")

def search_profile():
username = input("Enter the username to search for: ")
if username in user_profiles:
print("Profile found:")
profile = user_profiles[username]
print(f"Username: {username}")
for key, value in [Link]():
print(f"{key}: {value}")
else:
print("Profile not found.")

def delete_profile(username):
if username in user_profiles:
del user_profiles[username]
print("Profile deleted successfully!")
else:
print("Profile not found.")

def update_profile(username):
if username in user_profiles:
print("Current profile information:")
profile = user_profiles[username]
for key, value in [Link]():
print(f"{key}: {value}")

print("\nEnter new information (leave blank to keep current):")

name = input("Enter your name: ")
age = input("Enter your age: ")
city = input("Enter your city: ")

if name:
profile['Name'] = name
if age:
profile['Age'] = age
if city:
profile['City'] = city

print("Profile updated successfully!")

else:
print("Profile not found.")

while True:
print("\nMenu:")
print("1. Create Profile")
 print("2. Search Profile")
print("3. Delete Profile (your own)")
print("4. Update Profile (your own)")
print("5. Exit")

choice = input("Enter your choice (1/2/3/4/5): ")

if choice == '1':
create_profile()
elif choice == '2':
search_profile()
elif choice == '3':
username = input("Enter your username to delete your profile: ")
delete_profile(username)
elif choice == '4':
username = input("Enter your username to update your profile: ")
update_profile(username)
elif choice == '5':
print("Exiting the program.")
break
else:
print("Invalid choice. Please enter a valid option (1/2/3/4/5).")

OUTPUT:
 Q20. EMPLOYEE TABLE

(A) WRITE AN APPLICATION PROGRAM TO INTERFACE

PYTHON WITH SQL DATABASE FOR EMPLOYEE’S DATA
INSERTION
(B) WRITE AN APPLICATION PROGRAM TO INTERFACE
PYTHON WITH SQL DATABASE FOR EMPLOYEE’S DATA
DELETION.
(C) WRITE AN APPLICATION PROGRAM TO INTERFACE
PYTHON WITH SQL DATABASE FOR EMPLOYEE’S DATA
UPDATION.

(D) WRITE AN APPLICATION PROGRAM TO INTERFACE

PYTHON WITH SQL DATABASE FOR EMPLOYEE’S DATA
DISPLAY.
(E) WRITE AN APPLICATION PROGRAM TO INTERFACE
PYTHON WITH SQL DATABASE FOR EMPLOYEE’S DATA
SEARCHING BY APPLYING A CONDITION (MAXIMUM
SALARY OF A TEACHER)

21. MY SQL COMMANDS

CREATE DATABASE:
import [Link]

# Connect to the MySQL server

conn = [Link](
host="localhost",
user="root",
password="123456"
)

# Create a cursor object

cursor = [Link]()

# Create a new database

[Link]("CREATE DATABASE abcde")

# Close the cursor and connection

[Link]()
[Link]()

CREATE TABLE and DESCRIBE:

# Connect to the database
conn = [Link](
host="localhost",
user="root",
password="123456",
database="abcde"
)

cursor = [Link]()

# Create a sample table

[Link]("CREATE TABLE employees (id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255), salary INT)")

# Describe the table structure

[Link]("DESCRIBE employees")

# Fetch and print the table structure

for row in [Link]():
print(row)

# Close the cursor and connection

[Link]()
[Link]()
INSERT COMMAND & display using SELECT STATEMENT:
# Connect to the database
conn = [Link](
host="localhost",
user="root",
password="123456",
database="abcde"
)

cursor = [Link]()

# Insert sample data

[Link]("INSERT INTO employees (name, salary) VALUES ('John',
50000)")
[Link]("INSERT INTO employees (name, salary) VALUES ('Alice',
60000)")

# Commit the changes

[Link]()

# Select and display data

[Link]("SELECT * FROM employees")

# Fetch and print the data

for row in [Link]():
print(row)

# Close the cursor and connection

[Link]()
[Link]()

UPDATE COMMAND:
# Connect to the database
conn = [Link](
host="localhost",
user="root",
password="123456",
database="abcde"
)

cursor = [Link]()

# Update data
[Link]("UPDATE employees SET salary = 55000 WHERE name = 'John'")

# Commit the changes

[Link]()

# Select and display updated data

[Link]("SELECT * FROM employees")

# Fetch and print the data

for row in [Link]():
print(row)

# Close the cursor and connection

[Link]()
[Link]()

SELECTION USING DISTINCT KEYWORD:

# Connect to the database
conn = [Link](
host="localhost",
user="root",
password="123456",
database="abcde"
)

cursor = [Link]()

# Insert more sample data

[Link]("INSERT INTO employees (name, salary) VALUES ('Sarah',
60000)")
[Link]("INSERT INTO employees (name, salary) VALUES ('John',
55000)")

# Select distinct names

[Link]("SELECT DISTINCT name FROM employees")

# Fetch and print the data

for row in [Link]():
print(row)

# Close the cursor and connection

[Link]()
[Link]()

PROJECTION USING WHERE CLAUSE:

PROJECTION USING WHERE CLAUSE:
# Connect to the database
conn = [Link](
host="localhost",
 user="root",
password="123456",
database="abcde"
)

cursor = [Link]()

# Select employees with a salary greater than 55000

[Link]("SELECT name, salary FROM employees WHERE salary > 55000")

# Fetch and print the data

for row in [Link]():
print(row)

# Close the cursor and connection

[Link]()
[Link]()

SORTING RESULTS BY ORDER BY:

# Connect to the database
conn = [Link](
host="localhost",
user="root",
password="123456",
database="abcde"
)

cursor = [Link]()

# Select and sort employees by salary in descending order

[Link]("SELECT name, salary FROM employees ORDER BY salary DESC")

# Fetch and print the data

for row in [Link]():
print(row)

# Close the cursor and connection

[Link]()
[Link]()
DATE FUNCTIONS:
# Connect to the database
conn = [Link](
host="localhost",
user="root",
password="123456",
database="abcde"
)

cursor = [Link]()

# Insert sample data with dates

[Link]("INSERT INTO employees (name, salary) VALUES ('Emma',
55000)")
[Link]("INSERT INTO employees (name, salary) VALUES ('Oliver',
60000)")

# Select and display employees hired in the last 30 days

[Link]("SELECT name, salary FROM employees WHERE DATE(NOW()) -
DATE(hire_date) <= 30")

# Fetch and print the data

for row in [Link]():
print(row)

# Close the cursor and connection

[Link]()
[Link]()

GROUP BY CLAUSE:
# Connect to the database
conn = [Link](
host="localhost",
user="root",
password="123456",
database="abcde"
)

cursor = [Link]()

# Select and group employees by salary

[Link]("SELECT salary, COUNT(*) FROM employees GROUP BY salary")

# Fetch and print the data

for row in [Link]():
print(row)

# Close the cursor and connection

cursor close()
conn close()
HAVING CLAUSE:
# Connect to the database
conn = [Link](
host="localhost",
user="root",
password="123456",
database="abcde"
)

cursor = [Link]()

# Select and group employees by salary, having at least two employees with
the same salary
[Link]("SELECT salary, COUNT(*) FROM employees GROUP BY salary
HAVING COUNT(*) >= 2")

# Fetch and print the data

for row in [Link]():
print(row)

# Close the cursor and connection

[Link]()
[Link]()

ALTER COMMAND:
# Connect to the database
conn = [Link](
host="localhost",
user="root",
password="123456",
database="abcde"
)

cursor = [Link]()

# Add a new column to the table

[Link]("ALTER TABLE employees ADD COLUMN hire_date DATE")

# Update the hire_date for a specific employee

[Link]("UPDATE employees SET hire_date = '2023-10-01' WHERE name =
'Emma'")
# Close the cursor and connection
[Link]()
[Link]()

JOINS:
# Connect to the database
conn = [Link](
host="localhost",
user="root",
password="123456",
database="abcde"
)

cursor = [Link]()

# Create another table

[Link]("CREATE TABLE departments (id INT AUTO_INCREMENT PRIMARY
KEY, name VARCHAR(255))")
[Link]("INSERT INTO departments (name) VALUES ('HR')")
[Link]("INSERT INTO departments (name) VALUES ('IT')")

# Join employees and departments

[Link]("SELECT [Link], [Link], [Link]
FROM employees INNER JOIN departments ON [Link] = [Link]")

# Fetch and print the data

for row in [Link]():
print(row)

# Close the cursor and connection

[Link]()
[Link]()

NATURAL JOIN:
# Connect to the database
conn = [Link](
host="localhost",
user="root",
password="123456",
database="abcde"
)

cursor = [Link]()

# Perform a natural join between employees and departments

[Link]("SELECT [Link], [Link], [Link]
FROM employees NATURAL JOIN departments")

# Fetch and print the data

for row in [Link]():
print(row)

# Close the cursor and connection

[Link]()
[Link]()