Solotion 6

1 Retrieve details of all books in the library–id, title, name of publisher, authors, number of copies in each branch, etc.


 select b.book_id,b.title,b.name,a.author_name,c.no_of_copies,l.branch_id from BOOK1 B,AUTHORP A,B_COPIES C,LIBRARY L where B.book_id=A.book_id and B.book_id=C.book_id and L.branch_id=C.branch_id;


2.Get the particulars of borrowers who have borrowed more than 3 books, but from Jan 2017 to Jun2017

select card_no from B_LENDING where date_out between '01-jan-17'and'01-jul-17'group by card_no having count(*)>3;


3.Delete a book in BOOK table. Update the contents of other tables to reflect this data manipulation operation.


delete from BOOK1 where book_id=3; 


4.Create a view of all books and its number of copies that a recurrently available in the Library.


create view v_books as select b.book_id,b.title,c.no_of_copies from BOOK1 b,B_COPIES c,LIBRARY L where b.book_id=c.book_id and c.branch_id=l.branch_id;



PL/SQL implicit cursor
create table CUSTOM
(
ID int primary key,
name varchar(20),
age int,
address varchar(20),
salary number(6)
);
 insert into CUSTOM values(1,'Ramesh',23,'Allahabad','20000');
insert into CUSTOM values(2,'Suresh',22,'Kanpur','22000');
 insert into CUSTOM values(3,'Mahesh',24,'Ghaziabad','24000'); 
insert into CUSTOM values(4,'Chandan',25,'Noida','26000');
 insert into CUSTOM values(5,'Alex',21,'Paris','28000');
insert into CUSTOM values(6,'Sunita',20,'Delhi','30000');
 select * from CUSTOM;
DECLARE
total_rows number(2); BEGIN
UPDATE CUSTOM
SET salary=salary+5000;
IF sql%notfound THEN dbms_output.put_line('no customers applied');
ELSIF sql%found THEN total_rows := sql%rowcount;
dbms_output.put_line(total_rows||'customers updated'); END IF;
END;
/






Comments

Post a Comment

Popular posts from this blog

Solutions 5

 1.Demonstrate how you add a textbook to the database and make this book be adapted bysomedepartment. insert into book values (1, 1,555); Insert into text values('666','ada','tata','clen henry'); 2.Produce list of textbooks (include Course#, Book-ISBN, Book-title) in the alphabeticalorder for courses offered by the CS department that use more than two books. select c.course,b.book_isbn,t.book_title from course c ,text t,book b where c.course=b.course and b.book_isbn=t.book_isbn and c.dept ='computer science' group by c.course,b.book_isbn,t.book_title order by t.book_title; 3.List any department that has its adopted books published by a specific publisher. Select c.dept, t.publisher from course3 c,texts3 t,book b where c.course=b.course and b.book_isbn=t.book_isbn andt.publisher=’pearson’ group by c.dept, t.publisher; Write PL/SQL program to demonstrate user defined exception handling. DECLARE var_dividend NUMBER :=24; var_divisor NUMBER :=0; v...
Read more

Solution 2

 1. Count the customers with grades above Bangalore’s average SELECT GRADE,COUNT(DISTINCT CUSTOMER_ID) FROM CUSTOMER GROUP BY GRADE HAVING GRADE>(SELECT AVG(GRADE) FROM CUSTOMER WHERE CITY='BANGALORE'); 2.Find the name and numbers of all salesman who had more than one customer SELECT SALESMAN_ID, NAME FROM SALESMAN S WHERE (SELECT COUNT(*) FROM CUSTOMER C WHERE C.SALESMAN_ID=S.SALESMAN_ID) > 1; 3.List all the salesman and indicate those who have and don’t have customers in their cities (Use UNION operation.) SELECT S.SALESMAN_ID, S.NAME, C.CUST_NAME, S.COMMISSION FROM SALESMAN S, CUSTOMER C WHERE S.CITY = C.CITY UNION SELECT S.SALESMAN_ID, S.NAME, 'NO MATCH', S.COMMISSION FROM SALESMAN S WHERE CITY NOT IN (SELECT CITY FROM CUSTOMER) ORDER BY 1 ASC; 4. Create a view that finds the salesman who has the customer with the highest order of a day. CREATE VIEW V_SALESMAN AS SELECT O.ORDER_DATE, S.SALESMAN_ID, S.NAME FROM SALESMAN S,ORDERS O WHERE S.SALESMAN_ID = O.SAL...
Read more

Solutions 1

ii. Demonstrate the usage of following clauses for the above relation a. Where c. Having b. Order By d. Group By A) SELECT * FROM STUDENT WHERE marks1 > 80; B) SELECT * FROM STUDENT ORDER BY name ASC; SELECT * FROM STUDENT ORDER BY name DESC; C) SELECT class, AVG(marks1) as avg_marks1 FROM STUDENT GROUP BY class HAVING AVG(marks1) > 80; D) SELECT class, COUNT(*) as student_count FROM STUDENT GROUP BY class iii. Demonstrate the usage of following clauses for the above relation a. Sum c. Count e. Between b. Avg d. Like f. Max & Min a)SELECT SUM(marks1) as total_marks1 FROM STUDENT; b) SELECT AVG(marks2) as average_marks2 FROM STUDENT; c) SELECT COUNT(*) as total_students FROM STUDENT; d) SELECT * FROM STUDENT WHERE name LIKE 'A%'; e) SELECT * FROM STUDENT WHERE marks3 BETWEEN 75 AND 90; f) SELECT MAX(marks3) as highest_marks3, MIN(marks3) as lowest_marks3 FROM STUDENT; SET SERVEROUTPUT ON; BEGIN  UPDATE STUDENT SET M1=95  WHERE REGNO = 'S001';  DBMS_OUTPUT.PUT_...
Read more