dbmsl

CRUD - operations

---

connect to mysql database

mysql -h 10.10.12.108 -u t31332 -p

create table

create table studentTable(id int primary key, name varchar(30), roll int(5), mobile int(10), branch varchar(30));

alter column

alter table studentTable modify column mobile bigint(13);

insert query in the table

insert into studentTable values(1, "Sangmeshwar", 31332, 8010965429, "Comp");

update query

update studentTable set branch = "Computer" where id = 1;

alter table to add foreign key

alter table studentTable add foriegn key(id) references classStudent(id) on delete cascade;

delete query

delete from studentTable where id = 1;

copy records from one to another table

insert into stuentTableCopy select * from studentTable;

truncate - delete all records

truncate studentTable;

delete table

drop studentTable

read all the records from table

select * from studentTable;

read all the records with specific condition

select * from studentTable where branch = "Computer";

JOINS - all types

---

natural join - records with some common values (no need explicit condition)

select * from studentTable natural join marksTable;

inner join - records with common values and requires 'ON' clause

select * from studentTable s inner join marks m on s.id = m.id;

left join - records from left table + common records

select * from stduentTable s left join marks m on s.id = m.id;

right join - records from right table + common records

select * from stduentTable s right join marks m on s.id = m.id;

VIEWS

---

creating normal view - single base table

create or replace view v1 as select * from studentTable where branch = "Computer" with check option;

//Insert, update and delete queries are same as simple table queries

creating complex view - multiple base table

create or replace view v2 as select * from studentTable s inner join marksTable m on s.id = m.id;

//Insert, update and delete queries for complex view may not be possible

INDEX

---

create index on the table

create index i1 on studetTable(id);

showing the indexes

show indexes from studetTable;

PROCEDURE

---

create a procedure

delimiter $
create procedure calArea(IN radius float)
-> begin
-> declare radiusBound condition for sqlstate '45000';
-> if(radius<5 || radius>9)
-> then
-> signal sqlstate '45000' set message_text = "the radius is not in the range";
-> else
-> insert into areaTable values(radius, pi() * radius * radius);
-> end if;
-> end $

call calArea(6)$

FUNCTION

---

creating a function

delimiter $
create function calArea(radius float)
-> return float(20);
-> as
-> begin
-> declare radiusBound condition for sqlstate '45000';
-> if(radius>9 || radius<5)
-> then
-> signal sqlstate '45000' set message_text = "radius is not in the range";
-> else insert into areaTable values(radius, pi() * radius * radius);
-> return pi()radiusradius;
-> end if;
-> end $
select calArea(6)$

CURSOR

---

creating a cursor

delimiter $
create procedure clone1(IN rollN int)
-> begin
-> DECLARE c1 CURSOR AS SELECT * FROM studentTable WHERE roll = rollN;
-> OPEN c1;
-> FETCH c1 into rollN;
-> if not exists(select * from studentTable where roll = rollN)
-> then
-> insert into newStudentTable values select * from studentTable where roll = rollN;
-> end if;
-> end $
call clone1(31332)$

TRIGGER

---

creating trigger after update

delimiter $
create trigger uppdateTrigger after update on studentTable for each row
begin
insert into auditStudentTable set name = NEW.name, roll = NEW.roll, branch = NEW.branch, mobile = NEW.mobile;
end;
$

ASSIGNMENT ON MONGODB (JDBC)

---

1. Connect to mongo –
   MongoClient mongo = new MongoClient(“host”, 27017);

2. Connect to database –
   MongoDatabase db = mongo.getDatabase(“t31332db”);

3. Get collection –
   MongoCollection collection = db.getCollection(“student”);

4. Create document –
   Document document = new Document(“name”, “Sangmeshwar”).append(“roll”, 31332);

5. Insert document –
   collection.insertOne(document);

6. Display documents –
   FindIterable iterDoc = collection.find();
   Iterator it = iterDoc.iterator();
   while (it.hasNext()) {
   System.out.println(it.next());
   }

7. Update document –
   collection.updateOne(Filters.eq("roll", 31332));
   Updates.set("fName", fName));

8. Delete documet –
   collection.deleteOne(Filters.eq(“roll”, 31332));

9. Delete collection –
   collection.drop();