DATA MANIPULATION LANGUAGE USING MYSQL

 

 (1)

mysql> create database stud;

mysql> use stud;

(2)

mysql> create table stud(regno numeric(3),name varchar(10),sex varchar(1),age numeric(2),m1 numeric(3),m2 numeric(3),m3 numeric(3),total numeric(6),avg numeric(6),grade varchar(1));

 (3

mysql> insert into stud(regno,name,sex,age,m1,m2,m3)values(101,'ram','f',22,9

0,85,75);

mysql> insert into stud(regno,name,sex,age,m1,m2,m3)values(103,'suresh','m',23,8

7,89,56);

mysql> insert into stud(regno,name,sex,age,m1,m2,m3)values(104,'ramesh','f',22,67

,75,56);

mysql> insert into stud(regno,name,sex,age,m1,m2,m3)values(105,'hari','f',20,5

4,56,65);

mysql> insert into stud(regno,name,sex,age,m1,m2,m3)values(106,'ramani','f',21,54

,98,75);

mysql> insert into stud(regno,name,sex,age,m1,m2,m3)values(107,'ravi','m',24,85,9

8,79);

mysql> insert into stud(regno,name,sex,age,m1,m2,m3)values(108,'rahul','m',22,70,6

5,80);

mysql> insert into stud(regno,name,sex,age,m1,m2,m3)values(109,'priya','f',20,71

,43,81);

mysql> insert into stud(regno,name,sex,age,m1,m2,m3)values(110,'naveen','f',24,60,

90,98);

mysql> insert into stud(regno,name,sex,age,m1,m2,m3)values(110,'preethi','f',24,60,

85,76);

(4)

(i) mysql> update stud set total=m1+m2+m3;

mysql> select*from stud;

(ii) mysql> update stud set avg=total/3;

(iii)mysql>update stud set grade='A' where avg between 85 and 89 and m1>50 and m2>50 and m3>50;

mysql> update stud set grade='B' where avg between 75 and 84 and m1>50 and m2>50 and m3>50;

mysql> update stud set grade='C' where avg between 60 and 74 and m1>50 and m2>50 and m3>50;

mysql> update stud set grade='P' where avg between 50 and 59 and m1>50 and m2>50 and m3>50;

 (5)

(i)mysql> select*from stud;

 (ii)mysql> select name,regno from stud;

 (6)

mysql>select*from stud where avg>84;

 (7)

mysql> delete from stud where grade='P';

mysql> select*from stud;

 (8)

mysql> describe stud;

 (9)

(10)

mysql> show tables