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

AND O.PURCHASE_AMOUNT= (SELECT MAX(PURCHASE_AMOUNT)

FROM ORDERS C WHERE C.ORDER_DATE=O.ORDER_DATE);




5. Demonstrate the DELETE operation by removing salesman with id 1000. All his orders must also be deleted.

DELETE FROM SALESMAN WHERE SALESMAN_ID=1000; 




SET SERVEROUTPUT ON;
DECLARE
 v_row_count NUMBER;
BEGIN
 DELETE FROM SALESMAN
 WHERE Salesman_id = 1001;
 v_row_count := SQL%ROWCOUNT;
 DBMS_OUTPUT.PUT_LINE('Number of rows deleted: ' || v_row_count);
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

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