dbms_lab_manual - Posts

Exercise 1:

select * from emp where hiredate < ’01-jan-1981’;

select * from emp where job = ‘CLERK’ or job = ‘ANALYST’ order by job desc;

select deptno ,count() from emp group by deptno having count() >= 2;

select empno,ename,sal, to_char(hiredate,'MONTH DD,YYYY') from emp;

Create trigger Sal_upd before update on Emp for each row
call Modsalpr (:old.empno, :old.ename, :old.sal)
/
create procedure Modsalpr (empno in number, ename in varchar2, sal in number) as
begin
insert into Sal_Mod values(empno, ename, sal, sysdate); end;
/
Trigger Created

Exercise 2:

select * from emp where comm > sal;

select ename from emp where ename like ‘S%’ and length (ename) = 5;

Update the salaries by 10%, of employees working under the mgrs 7369,7890,7654,7900. update emp set
sal=sal+sal*0.10 where mgr in ( 7369,7890,7654,7900);

select * from emp where sal in (select max(sal) from emp where deptno in (select d.deptno from dept d
where d.dname = 'sales’'));

Create or replace trigger Dept_del before delete on Dept
for each row declare
v_empcount number(5); begin
select count(empno) into v_empcount from Emp where deptno=:old.deptno; if v_empcount > 0 then
raise_application_error( num=>-20000, msg=>'Can not delete Dept as Employees are there in this Dept');
end if;
end;
/
Trigger created

Exercise 3:

db.createCollection(‘Empoyee’);

Insert into 10 records

db.employee.find().sort({ename:1});

db.employee.find({},{designation:’Manager’});

db.employee.find({"eno":{$in:[104,106,107]}}).sort({salary:-1});

db.employee.find({"salary":{ $gt:20000,$ lt:30000}});

db.employee.find({"ename":"Olivia Taylor"},{_id:0,"address.city":1});

db.employee.updateMany({designation:'Manager'},{$inc:{"salary":6000}});

db.employee.updateOne({"ename":"Olivia Taylor"},{$set:{"phone":1445465698}});

db.employee.deleteOne({"ename":"Olivia Taylor"},{$set:{"age":1}});

Exercise 4:

Use book_store
db.createCollection(‘book1’)
db.createCollection(‘stock1’)
db.createCollection(‘customer1’)
db.createCollection(‘order1’)
db.createCollection(‘orderlist1’)

db.book1.insertOne({ISBN:’3140-2000’,title:’OperatingSystem’,author:’Tom’,price:560.00,pubyear:2000})
db.book1.insertOne({ISBN:’3140-2001’,title:’Data Structures’, author:’Jerry’, price:250.00,pubyear:2005})
db.book1.insertOne({ISBN:’3140-2002’,title:’DBMS’,author:’Harry’, price:300.00,pubyear:2011})
db.book1.insertOne({ISBN:’3140-
2003’,title:’C++Programming’,author:’Sarah’,price:200.00,pubyear:2013})
db.book1.insertOne({ISBN:’3140-2004’,title:’C Programming’,author:’John’, price:350.00,pubyear:2018})

db.stock1.insertOne({{ISBN:’3140-
2000’,stock_quantity:30,reorder_level:20,reorder_quantity:10})
db.stock1.insertOne({{ISBN:’3140-
2001’,stock_quantity:40,reorder_level:30,reorder_quantity:20})
db.stock1.insertOne({{ISBN:’3140-
2002’,stock_quantity:50,reorder_level:30,reorder_quantity:20})
db.stock1.insertOne({{ISBN:’3140-
2003’,stock_quantity:60,reorder_level:30,reorder_quantity:20})
db.stock1.insertOne({{ISBN:’3140-
2004’,stock_quantity:40,reorder_level:20,reorder_quantity:10})

db.customer1.insertOne({custid:100,custname:’Tom’,custaddress:’M.G.Road’ })
db.customer1.insertOne({custid:101,custname:’Jerry’,custaddress:’Mullai nagar’ })
db.customer1.insertOne({custid:102,custname:’Harry’,custaddress:’Hanumath nagar’ })
db.customer1.insertOne({custid:103,custname:’Sarah’,custaddress:’Basava nagar’ })
db.customer1.insertOne({custid:104,custname:’John’,custaddress:’Vinayak nagar’ })

db.order1.insertOne({orderno:1,custid:100,orderdate:’12-10-2021’})
db.order1.insertOne({orderno:2,custid:101,orderdate:’11-12-2021’})
db.order1.insertOne({orderno:3,custid:102,orderdate:’14-01-2022’})
db.order1.insertOne({orderno:4,custid:103,orderdate:’21-03-2022’})
db.order1.insertOne({orderno:5,custid:104,orderdate:’30-04-2022’})

db.orderlist1.insertOne({orderno:1,ISBN:’3140-2000’,quantity:10,totalprice:5600,shipdate:’22- 10-2021’})
db.orderlist1.insertOne({orderno:2,ISBN:’3140-2001’,quantity:10,totalprice:2500,shipdate:’21- 12-2021’})
db.orderlist1.insertOne({orderno:3,ISBN:’3140-2002’,quantity:10,totalprice:3000,shipdate:’24- 01-2022’})
db.orderlist1.insertOne({orderno:4,ISBN:’3140-2003’,quantity:10,totalprice:2000,shipdate:’29- 03-2022’})

1.db.book1.find({title:/^C/})

db.orderlist1.aggregate([
{ $lookup:{from:'book1',localField:'ISBN',foreignField:'ISBN',as:'book_res'}}, {$ unwind:' $book_res'}, {$ project:{bookname:' $book_res.title',publicationyear:'$ book_res.pubyear',authorname:' $book_r es.author',price :'$ book_res.price',_id : 0}}
])
db.orderlist1.aggregate([
{ $lookup:{from:'book1',localField:'ISBN',foreignField:'ISBN',as:'book_res'}}, {$ unwind:' $book_res'}, {$ project:{ISBN:'$book_res.ISBN',_id : 0}}
])
db.order1.aggregate([
{ $lookup:{from:'customer1',localField:'custid',foreignField:'custid',as:'cust_res'}}, {$ unwind : ' $cust_res'}, {$ project: {custname: '$cust_res.custname',orderdate:1,orderno:1,_id:0}}
])

Exercise 5:

Create table Branch ( branchid varchar2(16) primary key, bname varchar2(20), locationvarchar2(15),assets
number(15,2));

Create table Account (accno varchar2(20) primary key, accdate date, branchid varchar2(16) references
Branch(branchid), balance number(10,2));

Create table customer (custid varchar2(20) primary key, accno varchar2(20) references
Account(accno),cname varchar2(20), address varchar2(100));

Create table empl(empno number(5),empname varchar2(30),address varchar2(100),pay
number(10,2),qualification varchar2(20));

insert into Branch values('CAN1BR001','CANKGPURA','KGPURABLR',18000000); insert into Branch
values('CAN1BR002','CANKASNGR','KARTURINAGARBLR',9000000);
insert into Branch values('CAN1BR003','CANINDNGR','INDIRANAGARBLR',60000000);
insert into Branch values('CAN1BR004','CANMGROAD','MGROADBLR',78000000); insert into Branch
values('CAN1BR005','CANKRPURA','KRPURABLR',18000000);

insert into Account values('CA000010010001','20-MAY-18','CAN1BR002','50000');
insert into Account values('CA000010010022','19-MAY-18','CAN1BR002','40000');
insert into Account values('CA000010010007','20-MAY-18','CAN1BR002','50000');
insert into Account values('CA000010010801','07-MAY-18','CAN1BR002','35000');
insert into Account values('CA000010010093','20-APR-18','CAN1BR002','60000');

insert into customer values('000002O12142','CA000010010001','Tom','45,7th
cross,Tippasandra,Bangalore');
insert into customer values('000001O87310','CA000010010022','Jerry','101,4th main,Vignannagar,
Bangalore');
insert into customer values('000002O00009','CA000010010007',’Jack','22,2nd cross,KR Puram, Bangalore');
insert into customer values('000002O11142','CA000010010801','Sarah','#7,11th Main,Tippasandra,
Bangalore');
insert into customer values('000001077821','CA000010010093',’jenifer’ ,'8, Krishna apartments, HAL Main
road, Bangalore');

insert into empl values(100, ‘Tom’,'45,7th cross, Tippasandra, Bangalore',10000,'B.COM');
insert into empl values(101,'Jerry','101,4th main, Vignan nagar, Bangalore',40000,'BCA');
insert into empl values(102, 'Sarah','22,2nd cross, KR Puram, Bangalore',25000,'BSc');
insert into empl values(103, 'Thomas','#7,11th Main,Tippasandra, Bangalore',22000,'BCA');
insert into empl values(104, ‘Tim','8, Krishna apartments, HAL Main road,Bangalore',40000,'MCA');

Create or replace function Dispasset (brname in varchar2) Return number is
Assetstot number(15,2); Begin
Select assets into Assetstot from Branch where bname=brname; Return (Assetstot);
End;
/
Function created.

SQL>Select Dispasset('CANKASNGR') from dual;

Create or replace function CalcTax (iempno in number) Return number is
Resulttax number(10,2); Payval number(10,2); Begin
Select pay into Payval from empl where empno=iempno; If Payval <10000 then Resulttax :=0;
Elsif Payval between 10001 and 20000 then Resulttax := Payval *20/100; Elsif Payval between 20001 and
50000 then Resulttax := Payval *35/100; Elsif Payval > 50000 then Resulttax := Payval *40/100; end if;
Return (Resulttax); End;
Function created.
SQL >Select CalcTax(101) from dual;

Exercise 6:

Create table Branch ( branchid varchar2(16) primary key, bname varchar2(20),
locationvarchar2(15),assets number(15,2));

Create table Account (accno varchar2(20) primary key, accdate date, branchid varchar2(16)
references Branch(branchid), balance number(10,2));

Create table customer (custid varchar2(20) primary key, accno varchar2(20) references
Account(accno),cname varchar2(20), address varchar2(100));

Create table empl(empno number(5),empname varchar2(30),address varchar2(100),pay
number(10,2),qualification varchar2(20));

insert into Branch values('CAN1BR001','CANKGPURA','KGPURABLR',18000000);
insertinto Branchvalues('CAN1BR002','CANKASNGR','KARTURINAGARBLR',9000000);
insert into Branch values('CAN1BR003','CANINDNGR','INDIRANAGARBLR',60000000);
insert into Branch values('CAN1BR004','CANMGROAD','MGROADBLR',78000000);
insert into Branch values('CAN1BR005','CANKRPURA','KRPURABLR',18000000);

insert into customer values('000002O12142','CA000010010001','Tom','45,7th
cross,Tippasandra,Bangalore');
insert into customer values('000001O87310','CA000010010022','Jerry','101,4th
main,Vignannagar, Bangalore');
insert into customer values('000002O00009','CA000010010007',’Jack','22,2nd cross,KR
Puram, Bangalore');
insert into customer values('000002O11142','CA000010010801','Sarah','#7,11th
Main,Tippasandra, Bangalore');
insert into customer values('000001077821','CA000010010093',’jenifer’ ,'8, Krishna
apartments, HAL Main road, Bangalore');

Create or replace function Accttot (brname in varchar2) Return number is
Assetsnum number(5); Begin
Select count(A.Accno) into Assetsnum from Account A, Branch B where B.bname=brname
and A.branchid=B.branchid;
Return (Assetsnum); End;
/
Function created.
Select Accttot('CANKASNGR') from dual;
Create or replace function BrDeposit (brname in varchar2)
Return number is deposittot number(10,2); Begin
Select sum(A.balance) into deposittot from Account A, Branch B where B.bname=brname and
A.branchid=B.branchid;
Return (deposittot); End;
/
Function created.
SQL> Select BrDeposit('CANKASNGR') from dual;

Exercise 7:

Create table Courses(ccode varchar2(15) primary key, name varchar2(20),duration number(5),fee number(7));

Create table Students(rollno number(5) primary key, ccode varchar2(15) references Courses(ccode),name
varchar2(20),dj date, phone number(14)) ;

Create table Payments(rollno number(5) references Students(rollno), dp date, amount number(7));

insert into Courses values ('C00PY1','Python Fundamentals',30,10000);
insert into Courses values ('C00PY2','Python Advances',50,18000);
insert into Courses values ('C00JV1','Java Basics',40,12000);
insert into Courses values ('C00JV2','Java Advances',70,28000);

Insert into Students values (00001,'C00PY1',’Tom’,'19-Jan-20','9111378960');
Insert into Students values (00002,'C00PY1',’Jerry','18-Jan-20','8886527311');
Insert into Students values (00003,'C00PY2',’Sarah’,'12-Jan-20','7312856668');
Insert into Students values (00004,'C00JV1','Thomas','16-Jan-20','9991236896');
Insert into Students values (00005,'C00JV2',’Tim’,'21-Jan-20','9991236897');

Insert into Payments values (00001,'19-Jan-20',6000);
Insert into Payments values (00002,'19-Jan-20',8000);
Insert into Payments values (00003,'18-Jan-20',12000);
Insert into Payments values (00004,'16-Jan-20',12000);
Insert into Payments values (00005,'21-Jan-20',15000);

Create or replace function Feeremains (irollno in number)
Return number is feepending number(10,2);
Begin
select C.fee-P.amount into feepending from Courses C, Payments P , Students S where S.rollno=P.rollno and
S.ccode=C.ccode and P.rollno= irollno;
Return (feepending); End;
/
Function created.
Select Feeremains(00002) from dual;
Create or replace function Feecollected (iccode in varchar)
Return number is feereceived number(7);
Begin
select sum(P.amount) into feereceived from Courses C, Payments P, Students S where S.rollno=P.rollno and
S.ccode=C.ccode and C.ccode=iccode; return(feereceived);
end;
/
Function created.
select Feecollected('C00PY1') from dual;