User:Harsheet Patil/sandbox

-- PROGRAM 1B QUERIES: -- 2.select empid,ename from employee where salary between 50000 and 70000;

3. select ename,salary from employee where deptno in(select deptno from department where dname='IT');

4. select e.ename,d.dname from employee e,department d where e.empid=d.managerid; -- -- PROGRAM 2 QUERIES: -- 1. select * from customer where grade >(select avg(grade) from customer where ccity='CityV');

2.select sname,salesmanid from salesman where salesmanid in(select salesmanid from salesman group by(salesmanid) having count(*)>1);

3.(select s.salesmanid,c.cname from salesman s,customer c where s.scity=c.ccity)union(select distinct s.salesmanid,c.cname from salesman s,customer c where not s.scity=any(select ccity from customer));

4.select salesmanid from orders where purchaseamt in(select max(purchaseamt) from orders group by(orddate));

5.delete from salesman where salesmanid=1; -- -- PROGRAM 3 QUERIES: -- 1.a.update participated set damageamt=3000 where regno=201;

b.insert in all tables;

2. select reportno from accident where adate between '1-jan-23' and '1-apr-23';

select count(driverid) from participated where reportno in(select reportno from accident where adate between '1-jan-23' and '1-apr-23');

3.select count(regno) from participated where regno in(select regno from car where model='model1'); -- -- PROGRAM 4 QUERIES: -- 1.select authorid from cataloog where price>(select avg(price) from catalog group by authorid having count(*)>2);

2. select authorid from cataloog where bookid in(select bookid from orderdetails where quantity in(select max(quantity) from orderdetails));

3. update cataloog set price=1.1*price where publisherid=4; -- -- PROGRAM 5 QUEERIES: -- 1. insert into course values(206,'hin101','hindi'); .................insert into text values(306,'hindi','hindipub','laura'); .......................insert into bookadoption values(206,5,306);

2.SELECT c.courseid, t.bookisbn, t.booktitle FROM course c JOIN bookadoption b ON c.courseid = b.courseid JOIN text t ON b.bookisbn = t.bookisbn WHERE c.dept = 'math' GROUP BY c.courseid, t.bookisbn, t.booktitle HAVING COUNT(*) > 0 ORDER BY t.booktitle;

3.select c.dept from course c,text t,bookadoption b where t.publisher='biopub' and c.courseid=b.courseid and b.bookisbn=t.bookisbn;

PROGRAM 6 QUERIES: -- 1.select b.bookid,b.title,b.publishername,a.authorname,c.noofcopies from book b,bookauthors a,bookcopies c where b.bookid=a.bookid and a.bookid=c.bookid;

2.select cardno from booklending where dateout between '04-apr-19' and '01-jan-2022' group by cardno having count(*)>0;

3. create view allbooks as(select b.bookid,b.title,b.publishername,c.noofcopies from book b,bookcopies c where b.bookid=c.bookid);

4. delete from book where bookid=4; PROGRAM 7 QUERIES: -- 1.select pno from workson where ssn in(select ssn from employee where name='xxxx');

2.update employee set salary=1.1*salary where ssn in(select ssn from workson where pno in(select pno from project where pname='computer'));

3.select sum(salary),max(salary),min(salary),avg(salary) from employee where dno in(select dno from department where dname='analysis'));

4.create view deptlocation as(select d.dname ,dl.dloc from department d,dlocation dl where d.dno=dl.dno);

PROCEDURE CREATION FOR PROGRAM 1 ROW INSERTION

create or replace procedure addnewdept (pdno deptt.dno% type, pdname deptt.dname% type, pdloc deptt.dloc% type) as begin insert into deptt values(pdno,pdname,pdloc); dbms_output.put_line('new row inserted'); end; /

CODE FOR PROGRAM 1 ROW INSERTION

declare dno deptt.dno% type; dname deptt.dname% type; dloc deptt.dloc% type; begin dno:='&dno'; dname:='&dname'; dloc:='&dloc'; addnewdept(dno,dname,dloc); end; /

PROCEDURE CODE PROGRAM 4 CREATE AND CALL A FUNCTION MULTIPLICATION:

create or replace function calculator(a number,b number,op char) return number is begin return(a * b); end; /

CODE FOR PROGRAM 4 CREATE AND CALL A FUNC MULTIPLICATION

declare res number; a number; b number; begin a:='&a'; b:='&b'; res:=calculator(a,b,'*'); dbms_output.put_line('result='||res); end; /

FIRSTLY CREATE A TABLE FOR EXPLICIT CURSOR PROGRAM 7

CREATE TABLE DEPTT(DNO INT,DNAME VARCHAR(10),DLOC VARCHAR(10));

EXPLICIT CURSOR PL SQL PROGRAM 7 declare cursor c1 is select dno from deptt; redno deptt.dno% type; begin open c1; loop fetch c1 into redno; exit when c1% notfound; dbms_output.put_line('values from explicit cursor'||redno); end loop; close c1; end; /

IMPLICIT CURSOR CODE FOR PROGRAM 6 AND 2(ROW COUNT)

FIRSTLY CREATE A TABLE CREATE TABLE DEPTT(DNO INT,DNAME VARCHAR(10),DLOC VARCHAR(10));

PROCEDURE CODE FOR IMPLICIT CURSOR FOR PROGRAM 6 AND 2(ROW COUNT)

declare rc number; begin update deptt set dloc='gdg' where dno=2; rc:=sql % rowcount; dbms_output.put_line('total number of rows affecteed'||rc); if(sql%found) then dbms_output.put_line('implicit cursor'); end if; end; /

EXCEPTION CODE FOR PROGRAM 3 AND 5

declare si number; p number; t number; r number; lowtime exception; begin p:='&p'; t:='&t'; r:='&r'; if (t<2) then raise lowtime; else si:=(p*t*r)/100; dbms_output.put_line('simple interest='||si); end if; exception when lowtime then dbms_output.put_line('Time delered is very low'); end; /

body { background-color:skyblue; } #r        { background-color:#44ff80; border:6px solid black; border-radius:90%; height: 250px; width: 250px; margin: auto; } p         { text-align: center; font-size: 25px; margin: 0; color: white; padding: 100px 50px; }

var date = new Date; var n = date.toDateString; var m=date.toLocaleTimeString; document.write(" Date: ",n," "); document.write(" Time: ",m, " ");