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_LINE('UPDATE MA FOR REGNO S001 TO 95' );
 COMMIT;
 END;
 /


1[b]
2. DISPLAY EMP-ID & EMP NAME WHOSE SALARY LIES BETWEEN10,000 AND 50,000.

SELECT EMPID,
ENAME FROM EMPLOYEE
WHERE SALARY BETWEEN 10000 AND 50000;



3. LIST EMP NAME & SALARY FOR ALL EMPLOYEES WORKING FOR CS DEPT

SELECT E.ENAME, E.SALARY
FROM EMPLOYEE E, DEPARTMENT D
WHERE E.DEPTNO = D.DEPTNO AND D.DNAME = 'CS';


4. DISPLAY EMP NAME & DEPT NAME FOR ALL MANAGERS


SELECT E.ENAME, D.DNAME, D.MANAGER_ID
FROM EMPLOYEE E,DEPARTMENT D
WHERE E.DEPTNO = D.DEPTNO;



SET SERVEROUTPUT ON;
DECLARE
 v_empid INT := 106;
 v_ename VARCHAR(100) := 'Frank';
 v_age INT := 29;
 v_salary INT := 36000;
 v_deptno INT := 1; -- Assuming Frank is in the CS Dept
BEGIN
 INSERT INTO Employee VALUES (v_empid, v_ename, v_age, v_salary, v_deptno);
 COMMIT; -- Commit the transaction
 DBMS_OUTPUT.PUT_LINE('Employee inserted successfully.');
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