SQL QUERIES

Database creation
mysql> create database classes;

Query OK, 1 row affected (0.01 sec)

mysql> use classes;

Database changed

mysql> create table student

-> (rollno int,name char(20),marks int,

-> percent float,dob date);

Query OK, 0 rows affected (0.03 sec)

mysql> insert into student value(15,"deepak",90,60.7,"2006-09-10");

Query OK, 1 row affected (0.06 sec)

mysql> insert into student values(14,"yashraj",80,55.6,"2006-08-

12");

Query OK, 1 row affected (0.02 sec)

mysql> select * from student;

+--------+---------+-------+---------+------------+

| rollno | name | marks | percent | dob |

+--------+---------+-------+---------+------------+

| 15 | deepak | 90 | 60.7 | 2006-09-10 |

| 14 | yashraj | 80 | 55.6 | 2006-08-12 |

+--------+---------+-------+---------+------------+
2 rows in set (0.00 sec)

mysql> insert into student values(17,"veer",null,null,null);

Query OK, 1 row affected (0.01 sec)

mysql> select * from student;

+--------+---------+-------+---------+------------+

| rollno | name | marks | percent | dob |

+--------+---------+-------+---------+------------+

| 15 | deepak | 90 | 60.7 | 2006-09-10 |

| 14 | yashraj | 80 | 55.6 | 2006-08-12 |

| 17 | veer | NULL | NULL | NULL |

+--------+---------+-------+---------+------------+

3 rows in set (0.02 sec)

1-Query to view the contents in the table of database classes

mysql> use classes;

Database changed

mysql> show tables;

+-------------------+

| Tables_in_classes |

+-------------------+

| student |

+-------------------+

1 row in set (0.01 sec)

mysql> select * from student;

+--------+---------+-------+---------+------------+

| rollno | name | marks | percent | dob |

+--------+---------+-------+---------+------------+

| 15 | deepak | 90 | 60.7 | 2006-09-10 |

| 14 | yashraj | 80 | 55.6 | 2006-08-12 |

| 17 | veer | NULL | NULL | NULL |

+--------+---------+-------+---------+------------+

3 rows in set (0.01 sec)

2-Query to Create another table CLASSROOM and add data in
it ,view the contents of the table

mysql> create table CLASSROOM

-> (rollno int,name char(20),marks int,

-> nickname varchar(20));

Query OK, 0 rows affected (0.03 sec)

mysql> insert into CLASSROOM values(18,"nitesh",87,"nikhil");

Query OK, 1 row affected (0.02 sec)

mysql> insert into CLASSROOM values(19,"nikhil",88,"nityam");

Query OK, 1 row affected (0.00 sec)

mysql> show tables;

+-------------------+

| Tables_in_classes |

+-------------------+

| classroom |

| student |

+-------------------+

2 rows in set (0.00 sec)

mysql> select * from CLASSROOM;

+--------+--------+-------+----------+

| rollno | name | marks | nickname |

+--------+--------+-------+----------+

| 18 | nitesh | 87 | nikhil |

| 19 | nikhil | 88 | nityam |

+--------+--------+-------+----------+

2 rows in set (0.00 sec)

3-Query to update the name from student table where roll no=15

mysql> use classes;

Database changed

mysql> update student set name="samita" where rollno=17;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from student;

+--------+---------+-------+---------+------------+

| rollno | name | marks | percent | dob |

+--------+---------+-------+---------+------------+

| 15 | deepak | 90 | 60.7 | 2006-09-10 |

| 14 | yashraj | 80 | 55.6 | 2006-08-12 |

| 17 | samita | NULL | NULL | NULL |

+--------+---------+-------+---------+------------+

3 rows in set (0.00 sec)

4-Query to update the nickname from CLASSROOM table where roll
no=19

mysql> use classes;

Database changed

mysql> update CLASSROOM set nickname="niraj" where rollno=19;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from CLASSROOM;

+--------+--------+-------+----------+

| rollno | name | marks | nickname |

+--------+--------+-------+----------+

| 18 | nitesh | 87 | nikhil |

| 19 | nikhil | 88 | niraj |

+--------+--------+-------+----------+

2 rows in set (0.00 sec)

5- SQL query to view the name , rollno from the student table where
the length of the name is 6 characters

mysql> use classes;

Database changed

mysql> select name,rollno from student

-> where name like"______";

+--------+--------+

| name | rollno |

+--------+--------+

| deepak | 15 |

| samita | 17 |

+--------+--------+

2 rows in set (0.09 sec)

26. Program to implement stack in python using list

def isEmpty(stk): # checks whether the stack is empty or not

if stk==[]:

return True

else:

return False

def Push(stk,item): # Allow additions to the stack

stk.append(item)

top=len(stk)-1

def Pop(stk):

if isEmpty(stk): # verifies whether the stack is empty or not

print("Underflow")

else: # Allow deletions from the stack

item=stk.pop()

if len(stk)==0:

top=None

else:

top=len(stk)

print("Popped item is "+str(item))

def Display(stk):
if isEmpty(stk):

print("Stack is empty")

else:

top=len(stk)-1

print("Elements in the stack are: ")

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

print (str(stk[i]))

# executable code

if __name__ == "__main__":

stk=[]

top=None

Push(stk,1)

Push(stk,2)

Push(stk,3)

Push(stk,4)

Pop(stk)

Display(stk)

Output:

Popped item is 4

Elements in the stack are:

1
27. Program to implement queue in python using list

#Adding elements to queue at the rear end

def enqueue(data):

queue.insert(0,data)

#Removing the front element from the queue

def dequeue():

if len(queue)>0:

return queue.pop()

return ("Queue Empty!")

#To display the elements of the queue

def display():

print("Elements on queue are:");

for i in range(len(queue)):

print(queue[i])

# executable code

if __name__=="__main__":

queue=[]

enqueue(5)

enqueue(6)

enqueue(9)
enqueue(5)

enqueue(3)

print("Popped Element is: "+str(dequeue()))

display()

Output:

Popped item is: 5

Elements on queue are:

6
PYTHON CONNECTIVITY

1.Write a python prog that displays first three row fetched from
student table of my sql database “test”

import mysql.connector as sqltor

mycon=sqltor.connect(host=”localhost”,user=”learner”,passwd=”fas
t”,database=”test”)

if mycon.is_connected()==False:

print(“error containing to MYSQL database”)

cursor=mycon.cursor()

cursor.execute(“select*from student”)

data=cursor.fetchmany(3)

count=cursor.rowcount

for row in data:

print(row)

mycon.close()

OUTPUT :

(101,’ruhani’,decimal(’76.80’),’A’,’A’,’Pending’)

(102,’george’,Decimal(’71.20’),’B’,’A’,’Submitted’)

(103,’simran’,Decimal(’81.20’),’A’,’B’,’Evaluated’)
2.Write a python program that displays the no of employees hired at
the desired date $ time

import datetime

import mysql.connector

cnx = mysql.connector.connect(user='scott', database='employees')

cursor = cnx.cursor()

query = ("SELECT first_name, last_name, hire_date FROM employees

"

"WHERE hire_date BETWEEN %s AND %s")

hire_start = datetime.date(1999, 1, 1)

hire_end = datetime.date(1999, 12, 31)

cursor.execute(query, (hire_start, hire_end))

for (first_name, last_name, hire_date) in cursor:

print("{}, {} was hired on {:%d %b %Y}".format(

last_name, first_name, hire_date))

cursor.close()

cnx.close()

OUTPUT:

..

Wilharm, LiMin was hired on 16 Dec 1999

Wielonsky, Lalit was hired on 16 Dec 1999

Kamble, Dannz was hired on 18 Dec 1999

DuBourdieux, Zhongwei was hired on 19 Dec 1999

Fujisawa, Rosita was hired on 20 Dec 1999

3-Write a program that displays the fist thee rows fetched from
student table of mysql database “test”.Use pymysql to connect with
the database

import pymysql as pym

mycon=pym.connect(“localhost”,”learner”,”fast”,”test”)

cursor=mycon.cursor()

cursor.execute(“select * from student”)

data=cursor.fetchmany(3)

count=cursor.rowcount

for row in data:

print(row)

mycon.close()

output

(101,’ruhani’,decimal(’76.80’),’A’,’A’,’Pending’)

(102,’george’,Decimal(’71.20’),’B’,’A’,’Submitted’)

(103,’simran’,Decimal(’81.20’),’A’,’B’,’Evaluated’)