MySQL> CREATE TABLE EMPLOYEE
(EMPID CHAR(5) PRIMARY KEY, ADHAAR BIGINT, NAME VARCHAR(15), DEPT VARCHAR(10),
SALARY INT, GEN VARCHAR(6), CITY VARCHAR(20));
MySQL>INSERT INTO EMPLOYEE VALUES
(‘E1001’,6523965274569632,’ADITYA’,’IT’,96858,’MALE’,’ROURKELA’),
(‘E1002’,9586965874556932,’MANASHI’,’MGMT’,85858,’FEMALE’,’BBSR’),
(‘E1003’,9589512874569632,’RADHIKA’,’CLERK’,35858,’FEMALE’,’CUTTACK’),
(‘E1004’,8457512874569632,’BIKSAH’,’MGMT’,96899,’MALE’,’ROURKELA’),
(‘E1005’,2857965874569632,’KRITIKA’,’IT’,75658,’FEMALE’,’SMBP’),
(‘E1006’,9576985632548579,’BIDYA’,’CLERK’,25899,’FEMALE’,’ROURKELA’),
(‘E1007’,9576984694686325,’RUDRA’,’MGMT’,85426,’MALE’,’BBSR’),
(‘E1008’,2758463995686325,’SABITA’,’IT’,65426,’FEMALE’,’CUTTACK’),
(‘E1009’,9568763995682754,’RAVI’,’MGMT’,55425,’MALE’,’SMBP’),
(‘E1010’,6666763995687755,’RAGHAV’,’CLERK’,45425,’MALE’,’ROURKELA’);
Q1. To display details of all employee.
MySQL> SELECT * FROM EMPLOYEE;
Q2. To display of city all employee without repetition.
MySQL> SELECT DISTINCT CITY FROM EMPLOYEE;
Q3. To display EMPID, NAME and SALARY of all employee whose salary is more than 80000.
MySQL> SELECT EMPID,NAME, SALARY FROM EMPLOYEE WHERE SALARY>80000;
Q4. To display EMPID, NAME, CITY of all employee who are not belongs to Rourkela.
MySQL> SELECT EMPID, NAME, CITY FROM EMPLOYEE WHERE CITY<>'ROURKELA';
Q5. To display EMPID, NAME and DEPT of all employee whose department is IT.
MySQL> SELECT EMPID,NAME,CITY FROM EMPLOYEE WHERE DEPT='IT';
Q6. To display NAME, DEPT and SALARY of all employee whose department is ‘IT’ and salary more than
80000.
MySQL> SELECT NAME,DEPT,SALARY FROM EMPLOYEE WHERE DEPT='IT' AND SALARY>80000;
Q7. To display NAME and CITY of all employee who are belongs to ‘SMBP’ or ‘CUTTACK’ .
MySQL> SELECT NAME,CITY FROM EMPLOYEE WHERE CITY='CUTTACK' OR CITY='SMBP';
Q8. To display NAME, GEN, CITY of employee who are either from ‘CUTTACK’ or gender is ‘MALE’.
MySQL> SELECT NAME,GEN,CITY FROM EMPLOYEE WHERE GEN='MALE' OR CITY='CUTTACK';
Q9. To display the NAME and CITY of all employee who are not belongs to either ‘ROURKELA’ or’BBSR’.
MySQL> SELECT NAME,CITY FROM EMPLOYEE WHERE NOT(CITY='ROURKELA' OR CITY='BBSR');
Q10. To display EMPID and SALARY of all employee whose salary is between 40000 and 60000.
MySQL> SELECT EMPID, SALARY FROM EMPLOYEE WHERE SALARY BETWEEN 40000 AND 60000;
Q11. To display NAME and CITY of all employee whose name starts with ‘R’;
MySQL> SELECT NAME,CITY FROM EMPLOYEE WHERE NAME LIKE 'R%';
Q12. To display NAME and CITY of all employee whose name starts with ‘I’;
MySQL> SELECT NAME,CITY FROM EMPLOYEE WHERE NAME LIKE '%I';
Q13. To display NAME and CITY of all employee whose third letter of the name is ‘D’;
MySQL> SELECT NAME,CITY FROM EMPLOYEE WHERE NAME LIKE '__D%';
Q14. To display NAME and CITY of all employee whose second last letter of the name is ‘K’.
MySQL> SELECT NAME,CITY FROM EMPLOYEE WHERE NAME LIKE '%K_';
Q15. To display NAME and CITY of all employee who are belongs to ‘SMBP’, ‘CUTTACK’ and ’BBSR’ .
MySQL> SELECT NAME,CITY FROM EMPLOYEE WHERE CITY IN('BBSR','CUTTACK','SMBP');
Q16. To display NAME and CITY of all employee who are not belongs to ‘SMBP’, ‘CUTTACK’ and ’BBSR’ .
MySQL> SELECT NAME,CITY FROM EMPLOYEE WHERE CITY NOT IN('BBSR','CUTTACK','SMBP');
Q17. To display NAME and SALARY of all employee in ascending order of the SALARY.
MySQL> SELECT NAME,SALARY FROM EMPLOYEE ORDER BY SALARY ASC;
Q18. To display NAME and SALARY of all employee in descending order of the NAME.
MySQL> SELECT NAME,SALARY FROM EMPLOYEE ORDER BY NAME DESC;
Q19. To display NAME and SALARY of all ‘MGMT’ employees in descending order of their SALARY.
MySQL> SELECT NAME,SALARY FROM EMPLOYEE WHERE DEPT='MGMT' ORDER BY SALARY;
Q20. To display total numbers of employee working in EMPLOYEE table.
MySQL> SELECT COUNT(*) FROM EMPLOYEE;
Q21. To display total no of city without repetition.
MySQL> SELECT COUNT(DISTINCT CITY) FROM EMPLOYEE;
Q22. To display total sum of salary in EMPLOYEE table.
MySQL> SELECT SUM(SALARY) FROM EMPLOYEE;
Q23. To display total average of salary in EMPLOYEE table.
MySQL> SELECT AVG(SALARY) FROM EMPLOYEE;
Q24. To display minimum salary from employee table.
MySQL> SELECT MIN(SALARY) FROM EMPLOYEE;
Q25. To display maximum salary from employee table.
MySQL> SELECT MAX(SALARY) FROM EMPLOYEE;
Q26. To display DEPT and total no employee working in each department in employee table.
MySQL> SELECT DEPT, COUNT(*) FROM EMPLOYEE GROUP BY DEPT;
Q27. To display DEPT and total no male employee working in each department in employee table.
MySQL> SELECT DEPT, COUNT(*) FROM EMPLOYEE
WHERE GEN='MALE'
GROUP BY DEPT;
Q28. To display DEPT and total no employee working in each department where total count is more than
three(3) in employee table.
MySQL> SELECT DEPT, COUNT(*) FROM EMPLOYEE
GROUP BY DEPT
HAVING COUNT(*)>3;
Q29. To display DEPT and total no female employee working in each department where total count is
more than one(1) in employee table.
MySQL> SELECT DEPT, COUNT(*) FROM EMPLOYEE
WHERE GEN='FEMALE'
GROUP BY DEPT
HAVING COUNT(*)>1;
Q30. To display EMPID, NAME, CITY of all employee who are belongs to ‘CUTTACK’.
MySQL> SELECT EMPID, NAME, CITY FROM EMPLOYEE WHERE CITY='CUTTACK';
Relation: COMPANY Relation: FOOD
Creation of table COMPANY and Inserting records in it.
MySQL> CREATE TABLE COMPANY(ID CHAR(5) PRIMARY KEY,NAME VARCHAR(10) UNIQUE,CITY
VARCHAR(12) NOT NULL);
MySQL> INSERT INTO COMPANY VALUES
('A101','BRITANNIA','KOLKATA')
('A102','BISKFARM','BHUBANESWAR')
('A103','PARLE','JAMSHEDPUR')
('A104','PRIYAGOLD','RANCHI')
('A105','ANMOL','DELHI');
Creation of table FOOD and Inserting records in it.
MySQL> CREATE TABLE FOOD(CODE CHAR(5) PRIMARY KEY,PRODUCT VARCHAR(10) UNIQUE,QTY
SMALLINT,PRICE SMALLINT,ID CHAR(5));
MySQL> INSERT INTO FOOD VALUES
-> ('C1','TOP GOLD',20,35,'A101'),
-> ('C2','MARIE',30,30,'A101'),
-> ('C3','SPICY',60,40,'A102'),
-> ('C4','GOOGLY',40,35,'A102'),
-> ('C5','NAMKIN',10,40,'A103'),
-> ('C6','CHEESE',25,45,'A104'),
-> ('C7','OREO',35,25,'A108');
Q31. To display the schema/structure of relation COMPANY.
MySQL> DESC COMPANY;
Q32. To display NAME, CITY, CODE, PRODUCT, PRICE from company and food.
MySQL> SELECT NAME,CITY,CODE,PRODUCT, PRICE FROM COMPANY NATURAL JOIN FOOD;
Q33. To update CITY as ‘ROURKELA’ of ‘BRITANNIA’ Company.
MySQL>UPDATE COMPANY SET CITY='ROURKELA' WHERE NAME='BRITANNIA';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MySQL> SELECT * FROM COMPANY;
Q34. To delete the row of FOOD whose CODE is C7.
MySQL> DELETE FROM FOOD WHERE CODE='C7';
Query OK, 1 row affected (0.01 sec)
MySQL> SELECT * FROM FOOD;
Q.35. To remove all the rows / data/ elements form FOOD table.
MySQL> TRUNCATE TABLE FOOD;
Query OK, 0 rows affected (0.11 sec)
MySQL> SELECT * FROM FOOD;
Empty set (0.04 sec)
MySQL> CREATE TABLE TEACHER
-> (TID CHAR(4) PRIMARY KEY,NAME VARCHAR(10) NOT NULL,AGE TINYINT,DEPT VARCHAR(10),
DATEOFJOIN DATE, SAL INT, GEN CHAR(6));
MySQL> INSERT INTO TEACHER VALUES('T118','NAVIN',40,'COMPUTER','2010-01-10',12000,'MALE');
MySQL> INSERT INTO TEACHER VALUES
-> ('T107','CHETNA',37,'HISTORY','2008-03-24',20000,'FEMALE'),
-> ('T105','SANDEEP',46,'MATHS','2006-12-12',30000,'MALE'),
-> ('T110','SANGEETA',35,'HISTORY','2010-07-01',25000,'FEMALE'),
-> ('T101','RUDRANSH',42,'MATHS','2004-09-05',40000,'MALE'),
-> ('T121','NEERAJ',38,'PHYSICS','2011-04-01',28000,'MALE');
[Link] display name and age of female teachers in descending order of date of join.
MySQL>SELECT NAME,AGE FROM TEACHER WHERE GEN='FEMALE' ORDER BY DATEOFJOIN DESC;
Q37. To increase the salary by 10% of math department.
MySQL>UPDATE TEACHER SET SAL=SAL+(10*SAL)/100 WHERE DEPT='MATHS';
MySQL> SELECT NAME,DEPT,SAL FROM TEACHER;
Q38. To count the number of male teacher.
SELECT COUNT(*) AS TOTAL_MALE FROM TEACHER WHERE GEN='MALE';
Q39. To add a new column Address with data type varchar(15).
MySQL> ALTER TABLE TEACHER ADD ADDRESS VARCHAR(15);
MySQL> SELECT * FROM TEACHER;
Q40. To remove the column Address from Teacher table.
MySQL> ALTER TABLE TEACHER DROP COLUMN ADDRESS;
MySQl> SELECT * FROM TEACHER;
MYSQL WITH PYTHON INTERFACE
1. WAP in python to create a database “PUBLISHER” in MySQL.
import [Link] as con
mydb=[Link](host="localhost",user="root",passwd="admin")
if mydb.is_connected():
print("Connection established.")
else:
print("Connection not established.")
cur=[Link]()
try:
[Link]("CREATE DATABASE PUBLISHER;")
print("Database created sucessfully.")
except:
print("This database is already exist.")
[Link]()
2. WAP in python to create a table “BOOK” and insert data as per following .
Table Creation:
import [Link] as con
mydb=[Link](host="localhost",user="root",passwd="admin",database="PUBLISHER")
if mydb.is_connected():
print("Connection established.")
else:
print("Connection not established.")
cur=[Link]()
try:
qr="CREATE TABLE BOOK(BOOKID CHAR(5) PRIMARY KEY,NAME VARCHAR(15) NOT NULL,
AUTHOR VARCHAR(15) NOT NULL,PRICE SMALLINT);"
[Link](qr)
print("Table created sucessfully.")
except:
print("This table is already exist.")
[Link]()
Data Insertion:
import [Link] as con
mydb=[Link](host="localhost",user="root",passwd="admin",database="PUBLISHER")
if mydb.is_connected():
print("Connection established.")
else:
print("Connection not established.")
cur=[Link]()
while True:
id=input("Enter BookId:")
nm=input("Enter Book-Name:")
ath=input("Enter Author-Name:")
pr=int(input("Enter Book-Price:"))
qr="INSERT INTO BOOK VALUES('{}','{}','{}',{});".format(id,nm,ath,pr)
[Link](qr)
[Link]("COMMIT")
ch=input("Wants to add more record(y/n):")
if [Link]()=='n':
break
print("Rows inserted sucessfully.")
[Link]()
Output:
Connection established.
Enter BookId:B101
Enter Book-Name:PYTHON
Enter Author-Name:[Link]
Enter Book-Price:576
Wants to add more record(y/n):Y
Enter BookId:B102
Enter Book-Name:PHYSICS
Enter Author-Name:[Link]
Enter Book-Price:958
Wants to add more record(y/n):Y
Enter BookId:B103
Enter Book-Name:CHEMISTRY
Enter Author-Name:[Link]
Enter Book-Price:898
Wants to add more record(y/n):Y
Enter BookId:B104
Enter Book-Name:ACCOUNTS
Enter Author-Name:[Link]
Enter Book-Price:755
Wants to add more record(y/n):Y
Enter BookId:B105
Enter Book-Name:ECONOMICS
Enter Author-Name:[Link]
Enter Book-Price:555
Wants to add more record(y/n):N
Rows inserted sucessfully.
3. WAP in python to fetch all records from BOOK table.
import [Link] as con
mydb=[Link](host="localhost",user="root",passwd="admin",database="PUBLISHER")
cur=[Link]()
[Link]("SELECT * FROM BOOK;")
data=[Link]()
print(data)
[Link]()
Output
[('B101', 'PYTHON', '[Link]', 576), ('B102', 'PHYSICS', '[Link]', 958), ('B103', 'CHEMISTRY',
'[Link]', 898), ('B104', 'ACCOUNTS', '[Link]', 755), ('B105', 'ECONOMICS', '[Link]', 555)]
4. WAP in python to fetch all records individually from BOOK table.
import [Link] as con
mydb=[Link](host="localhost",user="root",passwd="admin",database="PUBLISHER")
cur=[Link]()
[Link]("SELECT * FROM BOOK;")
for row in cur:
print(row)
[Link]()
or
import [Link] as con
mydb=[Link](host="localhost",user="root",passwd="admin",database="PUBLISHER")
cur=[Link]()
[Link]("SELECT * FROM BOOK;")
data=[Link]()
for row in data:
print(row)
[Link]()
Output
('B101', 'PYTHON', '[Link]', 576)
('B102', 'PHYSICS', '[Link]', 958)
('B103', 'CHEMISTRY', '[Link]', 898)
('B104', 'ACCOUNTS', '[Link]', 755)
('B105', 'ECONOMICS', '[Link]', 555)
5. WAP in python to fetch one first row from table BOOK.
import [Link] as con
mydb=[Link](host="localhost",user="root",passwd="admin",database="PUBLISHER")
cur=[Link]()
[Link]("SELECT * FROM BOOK;")
data=[Link]()
print(data)
[Link]()
Output
('B101', 'PYTHON', '[Link]', 576)
6. WAP in python to fetch first three rows from table BOOK.
import [Link] as con
mydb=[Link](host="localhost",user="root",passwd="admin",database="PUBLISHER")
cur=[Link]()
[Link]("SELECT * FROM BOOK;")
data=[Link](3)
count=[Link]
for row in data:
print(row)
print("Total row fetched:",count)
[Link]()
Output:
('B101', 'PYTHON', '[Link]', 576)
('B102', 'PHYSICS', '[Link]', 958)
('B103', 'CHEMISTRY', '[Link]', 898)
Total row fetched: 3
7. WAP in python to print Book name and Book price of all book whose price is more than 700.
import [Link] as con
mydb=[Link](host="localhost",user="root",passwd="admin",database="PUBLISHER")
cur=[Link]()
[Link]("SELECT NAME,PRICE FROM BOOK WHERE PRICE>700;")
data=[Link](3)
count=[Link]
for row in data:
print(row)
print("Total row fetched:",count)
[Link]()
Output
('PHYSICS', 958)
('CHEMISTRY', 898)
('ACCOUNTS', 755)
Total row fetched: 3
8. WAP in python to print Book name ,Author name and price in descending order of their price.
import [Link] as con
mydb=[Link](host="localhost",user="root",passwd="admin",database="PUBLISHER")
cur=[Link]()
[Link]("SELECT NAME,AUTHOR,PRICE FROM BOOK ORDER BY PRICE DESC;")
data=[Link](3)
count=[Link]
for row in data:
print(row)
print("Total row fetched:",count)
[Link]()
Output
('PHYSICS', 958)
('CHEMISTRY', 898)
('ACCOUNTS', 755)
Total row fetched: 3
