Program 11 Create a binary file with name and roll
no. Search for a given roll number and display the
name, if not found display appropriate message.
Output:
Program 12 Write a random number generator that
generates random numbers between 1 and
6(simulates a dice)
Output:
Program 13 Write a python program to implement a
stack using a list data structure.
Stack Concept:
Output:
Queue Concept:
Output:
Program 14 Take a sample of ten phishing e-mails
(or any text file) and find most commonly occurring
word(s)
Output:
Program 15 Read a text file line by line and display
each word separated by a #
Output:
Program 16 Create a student table and insert data.
Implement the following SQL commands on the
student table:
• ALTER table to add new attributes / modify data
type/drop attribute
• UPDATE table to modify data
ANSWER:-
#Switched to a database
mysql> USE GVKCV;
Database changed
#Creating table student
mysql> create table student
-> (ROLLNO INT NOT NULL PRIMARY KEY,
-> NAME CHAR(10),
-> TELUGU CHAR(10),
-> HINDI CHAR(10),
-> MATHS CHAR(10));
Query OK, 0 rows affected (1.38 sec)
#Inserting values into table
mysql> insert into student
-> values(101,"student1",50,51,52),
-> (102,"student2",60,61,62),
-> (103,"student3",70,71,72),
-> (104,"student4",80,81,82),
-> (105,"student5",90,91,92),
-> (106,"student6",40,41,42),
-> (107,"student7",63,64,65);
Query OK, 7 rows affected (0.24 sec)
Records: 7 Duplicates: 0 Warnings: 0
#Adding new attribute computers
mysql> alter table student
-> add (computers char(10));
Query OK, 0 rows affected (1.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
#Describing table
mysql> desc student;
+-----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| ROLLNO | int | NO | PRI | NULL | |
| NAME | char(10) | YES | | NULL | |
| TELUGU | char(10) | YES | | NULL | |
| HINDI | char(10) | YES | | NULL | |
| MATHS | char(10) | YES | | NULL | |
| computers | char(10) | YES | | NULL | |
+-----------+----------+------+-----+---------+-------+
6 rows in set (0.21 sec)
#Modifying the datatype
mysql> alter table student
-> modify column computers varchar(10);
Query OK, 7 rows affected (2.38 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> desc student;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| ROLLNO | int | NO | PRI | NULL | |
| NAME | char(10) | YES | | NULL | |
| TELUGU | char(10) | YES | | NULL | |
| HINDI | char(10) | YES | | NULL | |
| MATHS | char(10) | YES | | NULL | |
| computers | varchar(10) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
6 rows in set (0.11 sec)
#Droping a attribute
mysql> alter table student
-> drop column computers;
Query OK, 0 rows affected (0.93 sec)
Records: 0 Duplicates: 0 Warnings: 0
#Describing table
mysql> desc student;
+--------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| ROLLNO | int | NO | PRI | NULL | |
| NAME | char(10) | YES | | NULL | |
| TELUGU | char(10) | YES | | NULL | |
| HINDI | char(10) | YES | | NULL | |
| MATHS | char(10) | YES | | NULL | |
+--------+----------+------+-----+---------+-------+
5 rows in set (0.14 sec)
#UPDATE DATA TO MODIFY DATA
#ACTUAL DATA
mysql> select *from student;
+--------+----------+--------+-------+-------+
| ROLLNO | NAME | TELUGU | HINDI | MATHS |
+--------+----------+--------+-------+-------+
| 101 | student1 | 50 | 51 | 52 |
| 102 | student2 | 60 | 61 | 62 |
| 103 | student3 | 70 | 71 | 72 |
| 104 | student4 | 80 | 81 | 82 |
| 105 | student5 | 90 | 91 | 92 |
| 106 | student6 | 40 | 41 | 42 |
| 107 | student7 | 63 | 64 | 65 |
+--------+----------+--------+-------+-------+
7 rows in set (0.00 sec)
#UPDATE THE MARKS FOR ATTRIBUTE TELUGU FOR
THE STUDENT101
mysql> UPDATE STUDENT
-> SET TELUGU=99
-> WHERE ROLLNO=101;
Query OK, 1 row affected (0.12 sec)
Rows matched: 1 Changed: 1 Warnings: 0
#DATA IN THE TABLE AFTER UPDATING
mysql> SELECT *FROM STUDENT;
+--------+----------+--------+-------+-------+
| ROLLNO | NAME | TELUGU | HINDI | MATHS |
+--------+----------+--------+-------+-------+
| 101 | student1 | 99 | 51 | 52 |
| 102 | student2 | 60 | 61 | 62 |
| 103 | student3 | 70 | 71 | 72 |
| 104 | student4 | 80 | 81 | 82 |
| 105 | student5 | 90 | 91 | 92 |
| 106 | student6 | 40 | 41 | 42 |
| 107 | student7 | 63 | 64 | 65 |
+--------+----------+--------+-------+-------+
7 rows in set (0.00 sec)
Program 17 : ORDER By to display data in
ascending/descending order.
ANSWER:-
#ORDER BY DESCENDING ORDER
mysql> SELECT *FROM STUDENT
-> ORDER BY HINDI DESC;
+--------+----------+--------+-------+-------+
| ROLLNO | NAME | TELUGU | HINDI | MATHS |
+--------+----------+--------+-------+-------+
| 105 | student5 | 90 | 91 | 92 |
| 104 | student4 | 80 | 81 | 82 |
| 103 | student3 | 70 | 71 | 72 |
| 107 | student7 | 63 | 64 | 65 |
| 102 | student2 | 60 | 61 | 62 |
| 101 | student1 | 99 | 51 | 52 |
| 106 | student6 | 40 | 41 | 42 |
+--------+----------+--------+-------+-------+
7 rows in set (0.05 sec)
#ORDER BY ASCENDING ORDER
mysql> SELECT *FROM STUDENT
-> ORDER BY HINDI ASC;
+--------+----------+--------+-------+-------+
| ROLLNO | NAME | TELUGU | HINDI | MATHS |
+--------+----------+--------+-------+-------+
| 106 | student6 | 40 | 41 | 42 |
| 101 | student1 | 99 | 51 | 52 |
| 102 | student2 | 60 | 61 | 62 |
| 107 | student7 | 63 | 64 | 65 |
| 103 | student3 | 70 | 71 | 72 |
| 104 | student4 | 80 | 81 | 82 |
| 105 | student5 | 90 | 91 | 92 |
+--------+----------+--------+-------+-------+
7 rows in set (0.00 sec)
Program 18 : DELETE to remove tuple(s)
ANSWER:-
#DELETING A TUPLE FROM THE TABLE
mysql> DELETE FROM STUDENT
-> WHERE ROLLNO=101;
Query OK, 1 row affected (0.14 sec)
mysql> SELECT *FROM STUDENT;
+--------+----------+--------+-------+-------+
| ROLLNO | NAME | TELUGU | HINDI | MATHS |
+--------+----------+--------+-------+-------+
| 102 | student2 | 60 | 61 | 62 |
| 103 | student3 | 70 | 71 | 72 |
| 104 | student4 | 80 | 81 | 82 |
| 105 | student5 | 90 | 91 | 92 |
| 106 | student6 | 40 | 41 | 42 |
| 107 | student7 | 63 | 64 | 65 |
+--------+----------+--------+-------+-------+
6 rows in set (0.06 sec)
#ORDER BY BRANCH
#ACTUAL DATA
mysql> SELECT *FROM STUDENT;
+--------+--------+----------+--------+-------+-------+
| ROLLNO | BRANCH | NAME | TELUGU | HINDI | MATHS |
+--------+--------+----------+--------+-------+-------+
| 102 | MPC | student2 | 60 | 61 | 62 |
| 103 | BIPC | student3 | 70 | 71 | 72 |
| 104 | BIPC | student4 | 80 | 81 | 82 |
| 105 | BIPC | student5 | 90 | 91 | 92 |
| 106 | BIPC | student6 | 40 | 41 | 42 |
| 107 | MPC | student7 | 63 | 64 | 65 |
+--------+--------+----------+--------+-------+-------+
6 rows in set (0.00 sec)
Program 19: GROUP BY and find the min, max, sum,
count, and average.
ANSWER:-
mysql> SELECT BRANCH,COUNT(*)
-> FROM STUDENT
-> GROUP BY BRANCH;
+--------+----------+
| BRANCH | COUNT(*) |
+--------+----------+
| MPC | 2|
| BIPC | 4|
+--------+----------+
2 rows in set (0.01 sec)
#e min, max, sum, count and average
mysql> SELECT MIN(TELUGU) "TELUGU MIN MARKS"
-> FROM STUDENT;
+------------------+
| TELUGU MIN MARKS |
+------------------+
| 40 |
+------------------+
1 row in set (0.00 sec)
mysql> SELECT MAX(TELUGU) "TELUGU MAX MARKS"
-> FROM STUDENT;
+------------------+
| TELUGU MAX MARKS |
+------------------+
| 90 |
+------------------+
1 row in set (0.00 sec)
mysql> SELECT SUM(TELUGU) "TELUGU TOTAL MARKS"
-> FROM STUDENT;
+--------------------+
| TELUGU TOTAL MARKS |
+--------------------+
| 403 |
+--------------------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(ROLLNO)
-> FROM STUDENT;
+---------------+
| COUNT(ROLLNO) |
+---------------+
| 6|
+---------------+
1 row in set (0.01 sec)
mysql> SELECT AVG(TELUGU) "TELUGU AVG MARKS"
-> FROM STUDENT;
+-------------------+
| TELUGU AVG MARKS |
+-------------------+
| 67.16666666666667 |
+-------------------+
Program 20:Integrate SQL with Python by importing
the MySQL module.
ANSWER:-
# Connect to the MySQL server
connection = mysql.connector.connect(
host="localhost",
user="username",
password="password",
database="employees"
)
# Create a cursor
cursor = connection.cursor()
# Execute a SQL query
cursor.execute("SELECT * FROM employees")
# Fetch the records
records = cursor.fetchall()
# Iterate through the records and display them
for record in records:
print("ID: ", record[0])
print("Name: ", record[1])
print("Salary: ", record[2])
print("Department: ", record[3])
print("\n")
# Close the cursor and connection
cursor.close()
connection.close()
Program 21:Integrate SQL with Python by importing
the pymysql module
ANSWER:-
import pymysql
def mysqlconnect():
# To connect MySQL database
conn = pymysql.connect(
host='localhost',
user='root',
password = "pass",
db='College',
cur = conn.cursor()
cur.execute("select @@version")
output = cur.fetchall()
print(output)
# To close the connection
conn.close()
# Driver Code
if __name__ == "__main__" :
mysqlconnect()
