ST. JOSEPH’S CONVENT HIGH SCHOOL (C.B.S.

E)CHITTARANJAN
SESSION:-2021-22
***********************************************************************************************
1. Write SQL query to create a database Employee.
2. Write SQL query to open database Employee.
3. Write SQL query to create following Table name empl.

4. Write SQL query to show the structure of table.

5. Write SQL query to Insert 10 records same as it is in image.
6. Write SQL query to display all the records from table empl.
7. Write SQL query to display EmpNo and EName of all employees from the table empl.
8. Write SQL query to display Ename, Sal, and Sal added with comm from table empl.
9. Write SQL query to display Ename, Sal, and deptno who are not getting commission from table empl.
10. Write SQL query to display Eno, Ename ,Sal, and Sal*12 as Annual Salary whose commission is not NULL from table
11. Write SQL query to display the details of employees whose name have only four letters.
12. Write SQL query to display name, job title and salary of employee who do not have manager.
13. Write SQL query to display the name of employee whose name contains “A” as third letter.
14. Write SQL query to display the name of departments. Each department should be displayed once (DISTINCT).
15. Write SQL query to display the name and salary of those employees whose salary is between 35000 and 40000.
(BETWEEN)
16. Write SQL query to display tables data according to ascending order of sal.
17. Write SQL query to change EName MITA by MIRA.
18. Write SQL query to delete records whose deptno=10.
19. Write SQL query to add a new column Phno.
20. Write SQL query to delete entire table IF EXISTING.
21. Queries for Aggregate functions- SUM( ), AVG( ), MIN( ), MAX( ), COUNT( ) .
22. TEXT FUNCTIONS ( TRIM FUNCTION, SUBSTR FUNCTION, LEFT FUNCTION, RIGHT FUNCTION
, MID FUNCTION )
23. MATH FUNCTIONS ( POWER (), ROUND (), MOD () ).
24. DATE FUNCTIONS NOW (), DATE (), MONTH (), MONTHNAME (), YEAR (), DAY (), DAYNAME ()
25. Querying and manipulating data using Group by, Having, Order by.
26. Create a table DEPT and show Cartesian Product, JOIN (Cartesian Join, Equi Join,
Natural Join)

(Python MySql Connectivity)

27. Program to connect with database and store record of employee and display records.
28: Program to connect with database and update the employee record of entered empno.
29. Program to connect with database and search employee number in table employee and display record, if empno not
found display appropriate message.
30. Program to connect with database and delete the employee record of entered empno.
Teacher’s Signature
1. Write SQL query to create and show a database Employee.

2. Write SQL query to open database Employee.

3. Write SQL query to create following Table name empl.

create table empl(empno int(4), ename char(20),job char(10), mgr int(4), hiredate
date,sal int(4), comm int(3), deptno int(2));
4. Write SQL query to show the structure of table.

5. Write SQL query to Insert 10 records same as it is in image.

Write Insert Query for 10 times.

6. Write SQL query to display all the records from table empl.
Ans: select * from empl;
 7. Write SQL query to display EmpNo and EName of all employees from the table empl.
Ans: select EmpNo, EName from empl;

8. Write SQL query to display Ename, Sal, and Sal added with comm from table empl.
Ans: select ename,sal,sal+comm from empl;

9. Write SQL query to display Ename, Sal, and deptno who are not getting commission from table
Ans:- select ename,sal,deptno from empl where comm IS NULL;

10. Write SQL query to display Eno, Ename ,Sal, and Sal*12 as Annual Salary whose commission is
not NULL from table empl.
Ans:- select empno,ename,sal,sal*12 "Annal Salary" from empl where comm IS NOT NULL;
 11. Display the details of employees whose name have only four letters.
Ans: select * from empl where EName like “ ____ ‟;

12. Display name, job title and salary of employee who do not have manager.
Ans: select EName, job, sal from empl where mgr = NULL;

13. Display the name of employee whose name contains “A”as fourth letter.
Ans: select EName from empl where EName like “_ _ _A%”

14. Display the name of departments. Each department should be displayed once.
Ans: SELECT DISTINCT(Dept) FROM EMPLOYEE;

15. Display the details of all employee whose annual salary is between 25000 to 40000.
Ans: select * from empl where sal between 25000 and 40000;
 16. Write SQL query to display tables data according to ascending order of sal.
Ans: select * from empl order by sal asc;

17. Write SQL query to change EName MITA by MIRA.

Ans:- update empl set ename="MIRA" where ename="MITA";

18. Write SQL query to delete records whose comm=0.

Ans:- delete from empl where comm=0;
 19. Write SQL query to add a new column Phno.
Ans:- alter table empl add phno int(10);

20. Write SQL query to delete entire table IF EXISTING.

Ans:- drop table IF EXIST empl;

***********************
21. Queries for Aggregate functions- SUM( ), AVG( ), MIN( ), MAX( ), COUNT( )
a. Display total salary of all employee listed in empl.

b. Find the average salary of the employees in empl table.

Solution:- SELECT avg(salary) FROM EMPL;

c. Find the minimum salary of job SALEMAN in EMPL table.

Solution:- SELECT min(sal) FROM EMPL WHERE job=’SALESMAN’;

d. Find the maximum salary of job SALEMAN in EMPL table.

Solution:- SELECT max(sal) FROM EMPL WHERE job=’SALESMAN’;

e. Find the total number of Employee from empl table.

Solution:- SELECT count(empno) from empl;
22. TEXT FUNCTIONS

Display name of employee in lower case and upper case;

Display the position of the string ‘LE’ in field job TRIM FUNCTION
of table EMPL.

SUBSTR FUNCTION LENGTH FUNCTION

LEFT FUNCTION RIGHT FUNCTION MID FUNCTION

23. MATH FUNCTIONS

MOD Function Power Function Round Function

Square Root Function

24. DATE / TIME FUNCTION

25. Querying and manipulating data using Group by, Having, Order by.
a) Display department no, job and no. of employee in particular job Group by department no,
job from empl.

b) Display the jobs where the number of employee is less than 3.

26. Create a table DEPT and show Cartesian Product, JOIN (Cartesian Join, Equi Join, Natural
Join)

a) Cartesian Product
b) Write query to join two tables empl and dept on the basis of deptno field. (Equi join)

c) Natural Join

d) Left Join

e) Right join
27: Program to connect with database and store record of employee and display records.
Source Code:-
import mysql.connector as mycon
con = mycon.connect(host='127.0.0.1',user='root',password="root")
cur = con.cursor()
cur.execute("create database if not exists company")
cur.execute("use company")
cur.execute("create table if not exists employee(empno int, name varchar(20), dept
varchar(20),salary int)")
con.commit()
choice=None
while choice!=0:
print("1. ADD RECORD ")
print("2. DISPLAY RECORD ")
print("0. EXIT")
choice = int(input("Enter Choice :"))
if choice == 1:
e = int(input("Enter Employee Number :"))
n = input("Enter Name :")
d = input("Enter Department :")
s = int(input("Enter Salary :"))
query="insert into employee values({},'{}','{}',{})".format(e,n,d,s)
cur.execute(query)
con.commit()
print("## Data Saved ##")
elif choice == 2:
query="select * from employee"
cur.execute(query)
result = cur.fetchall()
print("%10s"%"EMPNO","%20s"%"NAME","%15s"%"DEPARTMENT", "%10s"%"SALARY")
for row in result:
print("%10s"%row[0],"%20s"%row[1],"%15s"%row[2],"%10s"%row[3])
elif choice==0:
con.close()
print("## Bye!! ##")
else:
print("## INVALID CHOICE ##")
28: Program to connect with database and update the employeerecord of entered empno.
Source Code:-
import mysql.connector as mycon
con = mycon.connect(host='127.0.0.1',user='root',password="root", database="company")
cur = con.cursor()
print("#"*40)
print("EMPLOYEE UPDATION FORM")
print("#"*40)
print("\n\n")
ans='y'
while ans.lower()=='y':
eno = int(input("ENTER EMPNO TO UPDATE :"))
query="select * from employee where empno={}".format(eno)
cur.execute(query)
result = cur.fetchall()
if cur.rowcount==0:
print("Sorry! Empno not found ")
else:
print("%10s"%"EMPNO","%20s"%"NAME", "%15s"%"DEPARTMENT", "%10s"%"SALARY")
for row in result:
print("%10s"%row[0],"%20s"%row[1],"%15s"%row[2],"%10s"%row[3])
choice=input("\n## ARE YOUR SURE TO UPDATE ? (Y) :")
if choice.lower()=='y':
print("== YOU CAN UPDATE ONLY DEPT AND SALARY ==")
print("== FOR EMPNO AND NAME CONTACT ADMIN ==")
d = input("ENTER NEW DEPARTMENT,(LEAVE BLANK IF NOT WANT TO CHANGE )")
if d=="":
d=row[2]
try:
s = int(input("ENTER NEW SALARY,(LEAVE BLANK IF NOT WANT TO CHANGE ) "))
except:
s=row[3]
query="update employee set dept='{}',salary={} where empno={}".format(d,s,eno)
cur.execute(query)
con.commit()
print("## RECORD UPDATED ## ")
ans=input("UPDATE MORE (Y) :")
29. Program to connect with database and search employee number in table employee
and display record, if empno not found display appropriate message.
Source Code:-
import mysql.connector as mycon
con = mycon.connect(host='127.0.0.1',user='root',password="root", database="company")
cur = con.cursor()
print("#"*40)
print("EMPLOYEE SEARCHING FORM")
print("#"*40)
print("\n\n")
ans='y'
while ans.lower()=='y':
eno = int(input("ENTER EMPNO TO SEARCH :"))
query="select * from employee where empno={}".format(eno)
cur.execute(query)
result = cur.fetchall()
if cur.rowcount==0:
print("Sorry! Empno not found ")
else:
print("%10s"%"EMPNO", "%20s"%"NAME","%15s"%"DEPARTMENT", "%10s"%"SALARY")
for row in result:
print("%10s"%row[0],"%20s"%row[1],"%15s"%row[2],"%10s"%row[3])
ans=input("SEARCH MORE (Y) :")
30. Program to connect with database and delete the employeerecord of entered empno.
Source Code:-
import mysql.connector as mycon
con = mycon.connect(host='127.0.0.1',user='root',password="root", database="company")
cur = con.cursor()
print("#"*40)
print("EMPLOYEE DELETION FORM")
print("#"*40)
print("\n\n")
ans='y'
while ans.lower()=='y':
eno = int(input("ENTER EMPNO TO DELETE :"))
query="select * from employee where empno={}".format(eno)
cur.execute(query)
result = cur.fetchall()
if cur.rowcount==0:
print("Sorry! Empno not found ")
else:
print("%10s"%"EMPNO","%20s"%"NAME", "%15s"%"DEPARTMENT", "%10s"%"SALARY")
for row in result:
print("%10s"%row[0],"%20s"%row[1],"%15s"%row[2],"%10s"%row[3])
choice=input("\n## ARE YOUR SURE TO DELETE ? (Y) :")
if choice.lower()=='y':
query="delete from employee where empno={}".format(eno)
cur.execute(query)
con.commit()
print("=== RECORD DELETED SUCCESSFULLY! ===")
ans=input("DELETE MORE ? (Y) :")