Data

 i. Demonstrate how you a. Update the damage amount for the car with a specific Reg. no in the accident with report number12to 25000. UPDATE PARTICIPATED SET DMG_AMT=25000 WHERE REPORTNO=12; ii.Add a new accident to the database. Insert into accident values(55,'02-jan-2002','bankapur'); iii.Find the total number of people who owned cars that were involved in accident sin 2002. Select count(a.d_id) as num_of_accident from person a,participated pa,accident b where a.d_id=pa.d_id and b.reportno=pa.reportno and acc_date like '%02'; iv. Find the total number of accidents in which cars belonging to a specific model were involved. Select count(p.reportno) as sum_of_accident from accident a,participated p,car c where a.reportno=p.reportno andp.regno=c.regno and model='toyota'; Write PL/SQL program demonstrate exception handling for the above query v. DECLARE  v_model VARCHAR2(50) := 'Toyota'; -- Change model as needed  v_accident_count NUMBER; BEGIN ...

 1.Give the details of the authors who have 2or more books in the catalog and the price of the books is greater than the average price of the books in the catalog. Select * from author where author_id in(select author_id from catalog where price>(select avg(price)fromcatalog) group by author_id having count(*)>=2); 2.Find the author of the book, which has maximum sales. select a.name from author a,catalog c,order o where a.author_id=c.author_id and o.book_id= c.book_id and o.quantity=(select max(quantity) from order); 3.Demonstrate how you increase the price of books published by a specific publisherby10% update catalog set price=1.1*price where publisher_id=2002; select*from catalog; Write PL/SQL program illustrates how to create and call a function.👇 SET SERVEROUTPUT ON; CREATE OR REPLACE FUNCTION calculate_area(radius NUMBER) RETURN NUMBER IS pi CONSTANT NUMBER := 3.14159; area NUMBER; BEGIN area := pi * radius * radius; RETURN area; END; / DECLARE radius_inp...

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 ( I...

 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...

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_...