26.

STACK – DATASTRUCTURE - 2

Aim: To create a stack and implement push, pop, peek and display operations on the
elements of the stack.

Program:

def check_stack_isEmpty(stk):

if stk==[]:

return True

else:

return False

s=[] # An empty list to store stack elements, initially its empty

top = None # This is top pointer for push and pop operation

def main_menu():

while True:

print("Stack Implementation")

print("1 - Push")

print("2 - Pop")

print("3 - Peek")

print("4 - Display")

print("5 - Exit")

ch = int(input("Enter your choice:"))

if ch==1:

el = int(input("Enter the value to push an element:"))

push(s,el)

elif ch==2:

e=pop_stack(s)

if e=="UnderFlow":

print("Stack is underflow!")

else:

print("Element popped:",e)

elif ch==3:

print(peek(s))

elif ch==4:
print(s)

display(s)

elif ch==5:

break

else:

print("Sorry, You have entered invalid option")

def push(stk,e):

stk.append(e)

top = len(stk)-1

def display(stk):

if check_stack_isEmpty(stk):

print("Stack is Empty")

else:

top = len(stk)-1

print(stk[top],"-Top")

for i in range(top-1,-1,-1):

print(stk[i])

def pop_stack(stk):

if check_stack_isEmpty(stk):

return "UnderFlow"

else:

e = stk.pop()

if len(stk)==0:

top = None

else:

top = len(stk)-1

return e

def peek(stk):

if check_stack_isEmpty(stk):

return "UnderFlow"

else:
top = len(stk)-1

return stk[top]

Output:
27. MYSQL – DDL , DML COMMANDS

Aim: Create an Employee Table with the fields Empno, Empname, Desig, Dept,

Age and Place. Enter five records into the table

• Add two more records to the table.

• Modify the table structure by adding one more field namely date of joining.

• Check for Null value in doj of any record.

• List the employees who joined after 2018/01/01.

SQL Queries and Output:

i) Creating table employee

Create table Employee (Empno integer(4) primary key,

Empname varchar(20), Desig varchar(10), Dept varchar(10),

Age integer(2), Place varchar(10));

ii) View Table Structure:

mysql> Desc Employee;

(iii) Inserting Data into Table:

mysql> Insert into employee values(1221, 'Sidharth', 'Officer', 'Accounts', 45, 'Salem');

mysql> Insert into employee values(1222, 'Naveen', 'Manager', 'Admin', 32, 'Erode');

mysql> Insert into employee values(1223, 'Ramesh', 'Clerk', 'Accounts', 33,

'Ambathur');

mysql> Insert into employee values(1224, 'Abinaya', 'Manager', 'Admin', 28, 'Anna
Nagar');

mysql> Insert into employee values(1225, 'Rahul', 'Officer', 'Accounts', 31, 'Anna
Nagar');

(iv) Select all the record:

mysql> select * from Employee;

(v) Adding two more records:

mysql> Insert into employee values(3226, 'Sona', 'Manager', 'Accounts', 42, 'Erode');

mysql> Insert into employee values(3227, 'Rekha', 'Officer', 'Admin', 34, 'Salem');

mysql> select * from Employee;

Adding one more Field:

mysql> Alter table employee add(doj date);

desc employee;
(vii) Inserting date of joining to each employee:

mysql> update employee set doj = '21-03-2010' where empno=1221;

mysql> update employee set doj = '13-05-2012' where empno=1222;

mysql> update employee set doj = '25-10-2017' where empno=1223;

mysql> update employee set doj = '17-06-2018' where empno=1224;

mysql> update employee set doj = '02-01-2018' where empno=1225;

mysql> update employee set doj = '31-12-2017' where empno=3226;

mysql> update employee set doj = '16-08-2015' where empno=3227;

mysql> select * from Employee;

(viii) Checking null value in doj

mysql> select * from emp where empno is null;

Empty set (0.00 sec)

(ix) List the employees who joined after 2018/01/01.

mysql> Select * from emp where doj > '01-01-2018';

28. Create Student table with following fields and enter data as given in the

table below

Data to be entered

Then, query the followings:

(i) List the students whose department is “CSE”.

(ii) List all the students of age 20 and more in ME department.

(iii) List the students department wise.

(iv) Modify the class M2 to M1.

(v) Check for the uniqueness of Register no

SQL Queries and Output:

(1) Creating Table - Student

mysql>Create table Student(Reg_No char(5), Sname varchar(20), Age integer(2),

Dept varchar(10), Class char(3));

Query OK, 0 rows affected (0.51 sec)

View table structure:

mysql> desc Student;

2) Inserting Data into table:

mysql>Insert into Student values ('M1001', 'Harish', 19, 'ME', 'ME1');

mysql>Insert into Student values ('M1002', 'Akash', 20, 'ME', 'ME2');

mysql>Insert into Student values ('C1001', 'Sneha', 20, 'CSE', 'CS1');

mysql>Insert into Student values ('C1002', 'Lithya', 19, 'CSE', 'CS2');

mysql>Insert into Student values ('E1001', 'Ravi', 20, 'ECE', 'EC1');

mysql>Insert into Student values ('E1002', 'Leena', 21, 'EEE', 'EE1');

mysql>Insert into Student values ('E1003', 'Rose', 20, 'ECE', 'EC2');

View all records:

mysql> select * from Student;

(3) Other Queries:

(i) List the students whose department is “CSE”:

mysql> Select * from Student where Dept='CSE';

ii) List all the students of age 20 and more in ME department:

mysql> Select * from Student where Age >=20 and Dept='ME';

(iii) List the no of students belongs to each department:

mysql> Select * from Student Group by Dept Order by Sname;

(iv) Modify the class ME2 to M1:

mysql> Update Student set Class='ME1' where Class='ME2';

Query OK, 1 row affected (0.11 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from Student;

(v) Display all the students who are not belongs to ECE or EEE department.

mysql> Select * from student where dept not in(‘ECE’,’EEE’);

29. MYSQL QUERIES – MOVIE TABLE

Consider the following MOVIE table and write the SQL queries based on it.

a) Display all information from movie.

b) Display the type of movies.

c) Display movieid, moviename, total_eraning by showing the business done by the

movies. Claculate the business done by movie using the sum of productioncost

and businesscost.

d) Display movieid, moviename and productioncost for all movies

with productioncost greater than 150000 and less than 1000000.

e) Display the movie of type action and romance.

f) Display the list of movies which are going to release in February, 2022.

Answers:

a) select * from movie;

b) Display the various type of movies;

c) select movieid, moviename,

productioncost + businesscost "total earning" from movie;

d) select movie_id,moviename, productioncost from movie where

productcost is >150000 and <1000000;

e) select moviename from movie where type ='action' or type='romance';

f) select moviename from movie where month(releasedate)=2;

30.

Write following queries.

a. Write a query to display cube of 5.

b. Write a query to display the number 563.854741 rounding off to the next

hundred.

c. Write a query to display "put" from the word "Computer".

d. Write a query to display today's date into DD.MM.YYYY format.

e. Write a query to display 'DIA' from the word "MEDIA".

f. Write a query to display moviename - type from the table movie.

g. Write a query to display first four digits of productioncost.

h. Write a query to display last four digits of businesscost.

i. Write a query to display weekday of release dates.

j. Write a query to display dayname on which movies are going to be released.

Answers:

a) select pow(5,3);

b) select round(563.854741,-2);
c) select mid(“Computer”,4,3);

d) select concat(day(now()), concat('.',month(now()),concat('.',year(now()))))

"Date";

e)

select right("Media",3);

f) select concat(moviename,concat(' - ',type)) from movie;

g) select left(productioncost,4) from movie;

h)select right
(businesscost,4)from movie;

i)select weekday(releasedate) from movie;

j)select dayname(releasedate) from movie

31. MYSQL - Queries - (Based on Two Tables)

Creating table with the given specification

create table team

-> (teamid int(1),
-> teamname varchar(10), primary key(teamid));

Showing the structure of table using SQL statement:

desc team;

Inserting data:

mqsql> insert into team

-> values(1,'Tehlka');
Show the content of table – team:

select * from team;

Creating another table:

create table match_details

-> (matchid varchar(2) primary key,
-> matchdate date,
-> firstteamid int(1) references team(teamid),
-> secondteamid int(1) references team(teamid),
-> firstteamscore int(3),
-> secondteamscore int(3));
1. Display the matchid, teamid, teamscore whoscored more than 70 in first ining
along with team name.
2. Display matchid, teamname and secondteamscore between 100 to 160.
3. Display matchid, teamnames along with matchdates.
4. Display unique team names
5. Display matchid and matchdate played by Anadhi and Shailab.

Answers:

[1] select match_details.matchid, match_details.firstteamid,

team.teamname,match_details.firstteamscore from match_details, team where
match_details.firstteamid=team.teamid and match_details.firstteamscore>70;

[2] select matchid, teamname, secondteamscore from match_details, team where

match_details.secondteamid=team.teamid and match_details.secondteamscore between
100 and 160;
[3] select matchid,teamname,firstteamid,secondteamid,matchdate from match_details,
team where match_details.firstteamid=team.teamid;

[4] select distinct(teamname) from match_details, team where

match_details.firstteamid=team.teamid;

[5] select matchid,matchdate from match_details, team where

match_details.firstteamid=team.teamid and team.teamname in (‘Aandhi’,’Shailab’);
32. MYSQL – GROUP BY AND ORDER BY QUERIES

Consider the following table stock table to answer the queries:

itemno item dcode qty unitprice stockdate

S005 Ballpen 102 100 10 2018/04/22
S003 Gel Pen 101 150 15 2018/03/18
S002 Pencil 102 125 5 2018/02/25
S006 Eraser 101 200 3 2018/01/12
S001 Sharpner 103 210 5 2018/06/11
S004 Compass 102 60 35 2018/05/10
S009 A4 Papers 102 160 5 2018/07/17

1. Display all the items in the ascending order of stockdate.

2. Display maximum price of items for each dealer individually as per dcode from stock.
3. Display all the items in descending orders of itemnames.
4. Display average price of items for each dealer individually as per doce from stock which
avergae price is more than 5.
5. Diisplay the sum of quantity for each dcode.

[1] select * from stock order by stockdate;

[2] select dcode,max(unitprice) from stock group by code;

[3] select * from stock order by item desc;

[4] select dcode,avg(unitprice) from stock group by dcode having avg(unitprice)>5;

[5] select dcode,sum(qty) from stock group by dcode;

33 - Connectivity – 1

'AIM: Write a MySQL connectivity program in Python to Create a database 'alaya' and
drop the database. Create a table students with the specifications –

ROLLNO integer,

STNAME character(10)

in MySQL and perform the following operations:

Insert two records in it

Display the contents of the table

Perform all the operations with reference to table ‘students’ through MySQL-Python
connectivity under the database smart.

Answers:

import pymysql as ms

#Function to create Database as per users choice

def c_database():

try:
dn=input("Enter Database Name=")

c.execute("create database {}".format(dn))

c.execute("use {}".format(dn))

print("Database created successfully")

except Exception as a:

print("Database Error",a)

#Function to Drop Database as per users choice

def d_database():

try:

dn=input("Enter Database Name to be dropped=")

c.execute("drop database {}".format(dn))

print("Database deleted sucessfully")

except Exception as a:

print("Database Drop Error",a)

#Function to create Table

def c_table():

try:

c.execute('use {}'.format('smart'))

c.execute('create table students(rollno int(3),stname varchar(20))')

print("Table created successfully")

except Exception as a:

print("Create Table Error",a)

#Function to Insert Data

def e_data():

try:

while True:

rno=int(input("Enter student rollno="))

name=input("Enter student name=")

c.execute("use {}".format('smart'))
c.execute("insert into students values({},'{}');".format(rno,name))

db.commit()

choice=input("Do you want to add more record<y/n>=")

if choice in "Nn":

break

except Exception as a:

print("Insert Record Error",a)

#Function to Display Data

def d_data():

try:

c.execute("select * from students")

data=c.fetchall()

for i in data:

print(i)

except Exception as a:

print("Display Record Error",a)

db=ms.connect(host="localhost",user="root",password="password")

c=db.cursor()

while True:

print("MENU\n1. Create Database\n2. Drop Database \n3. Create Table\n4. Insert

Record \n5. Display Entire Data\n6. Exit")

choice=int(input("Enter your choice<1-6>="))

if choice==1:

c_database()

elif choice==2:

d_database()

elif choice==3:

c_table()

elif choice==4:
e_data()

elif choice==5:

d_data()

elif choice==6:

print('Thank you!!!.....')

break

else:

print("Wrong option selected")

OUTPUT:
34. MYSQL – CONECTIVITY – 2
Aim:

import pymysql as py

con=py.connect(host='localhost',user='root', passwd='password',database='school')

cur=con.cursor()

def main():

while True:

print('Menu....')

print('1. Insert record')

print('2. View records')

print('3. Update record')

print('4. Delete record')

print('5. View tables')

print('6. Quit')

ch=int(input('Enter Choice'))

if ch==1:

insertrec()

elif ch==2:

viewrec()

elif ch==3:

updaterec()

elif ch==4:

deleterec()

elif ch==5:

viewtables()

elif ch==6:

print('Thank you!!!...')

con.close()

break

def insertrec():

q='insert into physician values(%s,%s,%s,%s)'

pid=input('Enter Phy_id')
pname=input('Enter name')

ph =input('Enter phone number')

mid = input('Enter Mailid')

data=(pid,pname,ph,mid)

cur.execute(q,data)

print('Record inserted successfully')

con.commit()

def viewrec():

cur.execute("select * from physician")

a=cur.fetchall()

for i in a:

print(i)

def updaterec():

pid=input('Enter the phy_id for updating the record')

mid=input('Enter mail id')

cur.execute("update physician set phy_mail=('%s') where phy_id=('%s')"

%(mid,pid))

print('Record updated successfully')

con.commit()

def deleterec():

pid=input('Enter the phy_id for deleting the record')

cur.execute('delete from physician where phy_id=%s',pid)

print('Record deleted successfully')

con.commit()
def viewtables():

q='show tables'

cur.execute(q)

print('List of tables in your database')

a=cur.fetchall()

for i in a:

print(i)