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;
var_result NUMBER;
ex_divzero EXCEPTION;
BEGIN
IF var_divisor=0 THENRAISE ex_divzero;
END IF;
var_result :=var_dividend/var_divisor;
DBMS_OUTPUT.PUT_LINE('Result=' || var_result);EXCEPTION WHEN ex_divzero THEN
DBMS_OUTPUT.PUT_LINE ('ERROR YOUR DIVISOR IS ZERO');
END;
/

Comments

Post a Comment

Popular posts from this blog

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