Solution 3

 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
 -- Try to find the number of accidents for the specified car model
 SELECT COUNT(DISTINCT P.reportno) INTO v_accident_count
 FROM PARTICIPATED P
JOIN CAR C ON P.Regno = C.Regno
 WHERE C.model = v_model;
 IF v_accident_count = 0 THEN
 DBMS_OUTPUT.PUT_LINE('No accidents found for the model: ' || v_model);
 ELSE
 DBMS_OUTPUT.PUT_LINE('Total accidents for model ' || v_model || ': ' || v_accident_count);
 END IF;
EXCEPTION
 WHEN NO_DATA_FOUND THEN
 DBMS_OUTPUT.PUT_LINE('No data found for the model: ' || v_model);
 WHEN OTHERS THEN
 DBMS_OUTPUT.PUT_LINE('An unexpected error occurred.');
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