VARIOUS TYPES OF TABLES

1)

mysql> create database student1;

mysql> use student1;

mysql> create table student(name varchar(30),regno int,address varchar(30),mail_id varchar(30),ph_no int)type=Innodb;

mysql> create table student1(name varchar(30),regno int,address varchar(30),mail_idvarchar(30),ph_noint)type=Isam;

mysql> create table student2(name varchar(30),regno int not null primary key,address varchar(30),mail_id varchar(30),ph_no int)type=heap max_rows=100;

mysql> create table student3(name varchar(30),regno int not null auto_increment primary key,address varchar(30),mail_id varchar(30),ph_noint)type=heapmax_rows=100;

mysql> create table student4(name varchar(30),regno int auto_increment primary key,address varchar(30),mail_id varchar(30),ph_noint)type=mergeunion=(student1,student2)insert_method=last;

2)

mysql> insert into student values('pream',15,'gandhi street','pream_2008@gmail.com',2436879);

mysql> insert into student values('pandi',12,'richi street','pandi_2008@gmail.com',24814228);

mysql> insert into student values('raji',21,'ram street','raji_2008@gmail.com',24822398);

mysql> insert into student values('rathi',20,'rose garden','rathi_2008@gmail.com',2468729);

mysql> insert into student values('dinesh raj',20,'angel garden','dinesh_2008@gmail.com',22232901);

mysql> insert into student1 values('priya raj',20,'angel garden','priya_2008@gmail.com',22232901);

mysql> insert into student1 values('preetha raj',20,'angel garden','preetha_2008@gmail.com',22232901);

mysql> insert into student1 values('praveen raj',20,'angel garden','praveen_2008@gmail.com',22232901);

mysql> insert into student1 values('rajini',20,'angel garden','rajini_2008@gmail.com',22232901);

mysql> insert into student1 values('ranjitha',20,'angel garden','ranjitha_2008@gmail.com',22232901);

mysql> insert into student2 values('ramraj',22,'rose garden','ramraj_2008@gmail.com',2246721);

mysql> insert into student2 values('rajeshwari',23,'rose garden','raji_2008@gmail.com',2246721);

mysql> insert into student2 values('santha',24,'rose garden','santha_2008@gmail.com',2246721);

mysql> insert into student2 values('rajathi',25,'rose garden','rajathi_2008@gmail.com',2246721);

Query OK, 1 row affected (0.00 sec)

mysql> insert into student2 values('renu',26,'rose garden','renu_2008@gmail.com',2246721);

mysql> insert into student3 values('fari',20,'green garden','fari_2008@gmail.com',2862430);

mysql> insert into student3 values('viji',21,'green garden','viji_2008@gmail.com',2862430);

mysql> insert into student3 values('paru',22,'green garden','paru_2008@gmail.com',2862430);

mysql> insert into student3 values('suji',23,'green garden','suji_2008@gmail.com',2862430);

mysql> insert into student3 values('dinu',24,'green garden','dinu_2008@gmail.com',2862430);

3)

mysql> select * from student;

mysql> select * from student1;

mysql> select * from student2;

mysql> select * from student3;

4)

mysql> select * from student where name regexp'^pre';

5)

 mysql> select * from student1 where name regexp'tha$';

6)

mysql> select * from student where name regexp'^pre|tha$';

7)

mysql> select * from student1 where name like '%raj';

8)

mysql> select * from student1 where name regexp'raj';

9)

mysql> create table class1(logid int auto_increment primary key,logts datetime,entry varchar(30));

mysql> create table class2(logid int auto_increment primary key,logts datetime,entry varchar(30));

mysql> create table log(logid int auto_increment primary key,logts datetime,entry varchar(30))type=m

erge union=class1,class2)insert_method=last;

Read More

INTEGRITY CONSTRAINT

(1)

mysql> create database aca;

mysql> use aca;

mysql> create table nonaca(regno int,name varchar(10),nonaca_id int,credit int,co_ordinator varchar(30),mark int);

 (ii)

mysql> create table result(regno int,nonaca_mark int,activity_id int,status varchar(6));

 (2)

mysql> alter table nonaca add primary key(regno);

 (3)

mysql> alter table result add constraint fk1 foreign key(regno)references nonaca(regno);

 (4)

mysql> desc nonaca;

mysql> desc result;

(5)

mysql> insert into nonaca values(101,'bala',10,2,'charan',80);

mysql> insert into nonaca values(102,'naveen',11,3,'karthick',70);

mysql> insert into nonaca values(103,'suri',12,2,'suresh',85);

mysql> insert into nonaca values(104,'harini',13,1,'hari',90);

mysql> insert into nonaca values(105,'priyan',14,2,'ravi',65);

mysql> insert into result values(101,80,1,'pass');

mysql> insert into result values(102,90,2,'pass');

mysql> insert into result values(103,35,3,'fail');

mysql> insert into result values(104,40,4,'fail');

mysql> insert into result values(105,60,5,'pass');

 (6)

mysql> select*from nonaca;

mysql> select*from result;

 (7)

   mysql> alter table nonaca modify name varchar(200);

 (8)

mysql> select * from nonaca where credit > 2 and  credit < 4;

 (9)

mysql> select * from nonaca where credit > 2  or credit < 4;

 (10)

mysql> delete from nonaca where credit>4 and credit<6;

 (11)

 (12)

mysql> alter table result rename coestatement;

 (13)

mysql> alter table coestatement drop column status;

 (14)

mysql> desc coestatement;

 (15)

mysql> alter table nonaca drop primary key;

mysql> desc nonaca;

mysql> alter table coestatement drop primary key;

mysql> desc coestatement;

(16)

mysql> describe nonaca;

mysql> describe coestatement;

Read More

STRING ,DATE ,TIME,MATHEMATICAL AND NUMERICAL FUCTION IN MYSQL

(1)

 (a)

mysql> select char_length('I MCA');

mysql> select character_length('A SECTION');

(b)

mysql> select concat('I MCA A','A SECTION');

(c)

mysql> select concat_ws('=','I MCA','A SECTION');

(d)

mysql> select format(123456.1278912,2);

(e)

mysql> select lcase('I MCA');

 (f)

mysql> select locate('SEC','A SECTION');

mysql> select position('SEC'IN'A SECTION');

 (g)

mysql> select repeat('II MCA',3);

 (h)

mysql> select replace('II MCA','MCA','MBA');

(i)Reverse a String

mysql> select reverse('A SECTION');

 (j)

mysql> select substring('A SECTION',3);

 (2) (a)

mysql> select curdate();

(b

mysql> select curtime();

(c)

mysql> select datediff('2008-8-2','2008-7-6');

 (d)

mysql> select date_add('2008-7-6',interval 25 day);

(e)

mysql> select dayname('2008-08-01');

 (f)

mysql> select dayofmonth('2008-06-04');

 (g)

mysql> select dayofweek('2008-06-08');

 (h)

mysql> select dayofyear('2008-01-22');

 (I)

mysql> select hour('18:46:15');

 (j)

mysql> select minute('18:46:15');

 (k

mysql> select monthname('2008-01-22');

 (l)

mysql> select now();

 (3)      (a)

mysql> select abs(-60);

mysql> select sin(60),cos(60),tan(60);

mysql> select acos(60),atan(60),asin(60);

mysql> select ceil(6.78),degrees(pi()),exp(-1);

mysql> select floor(6.69),format(6789012.987654,2);

mysql> select log(5),log(2,290),log(10,610);

mysql> select mod(123,10),degrees(pi()),pow(5,2);

mysql> select radians(60),rand(60),round(60.98);

mysql> select sign(-160),sqrt(269),tan(60);

mysql> select truncate(4.981,1),truncate(451.156,-1);

 (4)      (a)

mysql> select 0=1,5<4.5,4.9<=4.9,5>2,2>=2,8 between 8 and 12,greatest('S','O','A');

Read More

QUERYING MYSQL

 (1)

mysql> create database st;

mysql> use st;

 (2)

mysql> create table dept(deptno int primary key,deptname varchar(30),schoolnamevarchar(30));

mysql> create table emp(empno int primary key,deptno int,name varchar(30),design varchar(30),salary int,specialization varchar(30)references emp(empno));

(3)

Read More

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

Read More