DATA BASE MANAGMENT
1 CREATE DATABASE
create database practicals;
2 MOVE INTO DATABASE
use practicals;
3 CREATE TABLES EMPLOYEE
create table emp(eno int not null primary key,ename
varchar(15),salary decimal(10,4),deptno int,address
varchar(30) unique);
4 DISPLAY THE LIST OF PRE EXISTING
DATABASES
show databases;
5 DELETE EXISTING DATABASE
drop database student;
6 DISPLAY THE LIST OF PRE EXISTING TABLES
show tables;
7 SHOW DESCRIPTION OF TABLE
desc emp;
8 ADDING NEW COLUMN TO TABLE
alter table emp add phonenum int;
9 REMOVE A COLUMN FROM TABLE
alter table emp phonenum;
10 DROPPING PRIMARY KEY
alter table emp drop primary key;
11 ADDING PRIMARY KEY FOR AN EXISTING
TABLE
alter table emp modify eno int not null primary key;
12 MODIFYING THE DATATYPE OF COLUMN
alter table emp modify ename char(30);
CHANGING THE NAME OF COLUMN
alter table emp change ename empname char(30);
13 REMOVE TABLE FROM DATABASE
drop table sample;(NOTE :NO QUERY PRINTOUT WILL BE
THERE)
14 INSERT RECORDS INTO TABLE
insert into values emp (101,'shiva',34000,10,'shastri
nagar ,delhi');
15 INSERT DATA INTO SPECIFIC COLUMNS
Insert into emp(eno,empname) values(102,'kumar');
DISPLAY CURRENT RECORDS OF EMP TABLE
SELECT * FROM EMP;
16 PROVIDING COLUMN ALIAS
select empname,deptno "department number"
,salary*12 "annual salary" from emp;
17 DISPLAY THE CONTENT BY REMOVING
DUPLICATES IN COLUMNS
18 Select distinct deptno from emp;
USING CLAUSES IN,BETWEEN/AND,ORDER BY,AND,OR
,IS NULL ,IS NOT NULL,LIKE
19 FIND THENAME ANDSALARYOF THOSE EMPLOYEESWHOSE
SALARYIS BETWEEN 23000 AND
40000.
Select empname,salary from emp where salary between 23000 and 40000;
20 FIND THE NAME AND ADDRESS OF EMPLOYEES WORKING IN
DEPTNO 10 &30.
select empname,address from emp where deptno in(10,30);
OR
select empname,address from emp where deptno=10 or deptno=30 ;
21 DISPLAY THE NAME OF THOSE EMPLOYEES WHOSE NAME
STARTS WITH ‘k’.
select empname ,salary from emp where empname like ‘k%’;
22 DISPLAYTHELISTOF EMPLOYEESINDESCENDINGORDEROF
EMPLOYEE CODE.
Select * from emp order by eno desc;
23 DISPLAY DETAILS OF EMPLOYESS WORKING UNDER
DEPARTMRNT 10 or EARNING MORE THAN 50000
Select * from emp where deptno=10 or salary>50000;
24 DISPLAY THE NAMES OF EMPLOYEES WHOSE SALARY
STRUCTURE IS NOT AT DECIDED.
Select empname from emp where salary is null;
25 DISPLAY THE LIST OF EMPLOYEES WHO ARE PAID MONTHLY.
Select * from emp where salary is NOT null;
26 GIVE INCREMENT OF 20% FOR EMPLOYEES WORKING UNDER
DEPTNO 30
update emp set salary=salary+salary*0.2 where deptno=30;
27 DELETE EMPLOYEE WHOSE EMPNO IS 102
Delete from emp where eno=102;
AGGREGATE FUNCTIONS (MIN,MAX,COUNT,SUM)
28 DISPLAY MAXIMUM/MINIMUM EARNED EMPLOYEE
Select min(salary),max(salary) from emp;
29 DISPLAY SUM OF SALARIES AND HOW MANY NO OF EMPLOYEES
EXISTING
Select count(*) ,sum(salary) from emp;
GROUP BY AND HAVING
30 DISPLAY THE DEPARTMENTS AND THEIR SALARY EXPENDITURE
WHICH HAS ONE EMPLOYEE
Select deptno, sum(salary) from emp group by deptno having count(*)<=1;
JOINING OF TABLES: NATURAL,EQUI JOIN,
31 Select * from emp e,department d where [Link]=d. deptno;
32 Select empname,dname from emp e,department d where [Link]=d. deptno;
CARTESIAN PRODUCT OF TABLES
33 Select * from emp,department;
Write a program to connect Python with MySQL using database connectivity and
perform the following operations on data in database
:Fetch,Update and delete the data.
CREATE ATABLE
import [Link]
demodb=[Link](host="localhost", user="root",
passwd="computer", database="EDUCATION")
SOLUTION
democursor=[Link]()
[Link]("CREATE TABLE STUDENT(admn_no int
primarykey, sname varchar(30), gender char(1), DOB date, stream
varchar(15),marks float(4,2))")
[Link]()
B INSERT THE DATA
import [Link]
demodb=[Link](host="localhost", user="root",
passwd="computer", database="EDUCATION")
SOLUTION democursor=[Link]()
[Link]("insert into student values(%s, %s, %s, %s,%s,
%s)", (1245,'Arush','M','2003-10-04','science',67.34))
[Link]( )
[Link]()
C. FETCH THE DATA
import [Link]
demodb=[Link](host="localhost",
user="root", passwd="computer", database="EDUCATION")
democursor=[Link]()
SOLUTION
[Link]("select * from student")
data=[Link](2)
for i in data:
print(i)
'''
data=[Link]()
for i in data:
print(i)
data=[Link]()
for i in data:
print(i)
'''
D. UPDATE THE RECORD
import [Link]
demodb=[Link](host="localhost",
user="root", passwd="computer",
SOLUTION database="EDUCATION")
democursor=[Link]( )
[Link]("update student set marks=55.68 where admn_no=1245")
[Link]( )
[Link]()
E DELETETHE DATA
import [Link]
demodb=[Link](host
="localhost", user="root",
SOLUTI
ON
passwd="computer",
database="EDUCATION")
democursor=[Link]( )
[Link]("delete from student where
admn_no=1245")
[Link]( )
[Link]()