Solution 4

 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_input NUMBER := 5;
result_area NUMBER;
BEGIN
result_area := calculate_area(radius_input);
DBMS_OUTPUT.PUT_LINE('The area of a circle with radius ' || radius_input || ' is: ' || result_area); 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