EXPERIMENT NO.

Write SQL command to create the student table (e.g. STUD_10) having structure ROLL_NO,
NAME_OF_THE_STUDENT, CLASS (MBA/MCA), GENDER (M=MALE, F=FEMALE), CENTER (KARAD,
KOLHAPUR, SANGALI, PUNE), YEAR, NAME_OF_THE_LAST_COLLEGE_ATTENDED (YMIM/YC/KCT
etc.).

CREATE TABLE STUD_3

ROLL_NO NUMBER(2),

NAME CHAR(10),

CLASS CHAR(10),

GENDER CHAR(10),

CENTER CHAR (15),

YEAR NUMBER (4),

LAST_COLLEGE CHAR (30)

);

DESCRIBE STUD_3

Output :
 EXPERIMENT NO. 2

Write SQL command to add 18 record of different centers with different classes.

INSERT INTO STUD_3(ROLL_NO,NAME,CLASS,GENDER,CENTER,YEAR,LAST_COLLEGE)

VALUES(1,'DIPAK','MCA','MALE','KOLHAPUR','2022','SGM');

INSERT INTO STUD_3(ROLL_NO,NAME,CLASS,GENDER,CENTER,YEAR,LAST_COLLEGE)

VALUES(2,'AKASH','BCA','MALE','SATARA','2022','ACVM');

INSERT INTO STUD_3(ROLL_NO,NAME,CLASS,GENDER,CENTER,YEAR,LAST_COLLEGE)

VALUES(3,'SWARAJ','MCA','MALE','KOLHAPUR','2022','SGM');

INSERT INTO STUD_3(ROLL_NO,NAME,CLASS,GENDER,CENTER,YEAR,LAST_COLLEGE)

VALUES(4,'OMKAR','MCA','MALE','SATARA','2022','SGM');

INSERT INTO STUD_3(ROLL_NO,NAME,CLASS,GENDER,CENTER,YEAR,LAST_COLLEGE)

VALUES(5,'AMIT','MCA','MALE','KOLHAPUR','2022','YMIM');

INSERT INTO STUD_3(ROLL_NO,NAME,CLASS,GENDER,CENTER,YEAR,LAST_COLLEGE)

VALUES(6,'SANKET','MCA','MALE','SATARA','2022','ACVM');

INSERT INTO STUD_3(ROLL_NO,NAME,CLASS,GENDER,CENTER,YEAR,LAST_COLLEGE)

VALUES(7,'SHUBHAM','MCA','MALE','KOLHAPUR','2022','SGM');

INSERT INTO STUD_3(ROLL_NO,NAME,CLASS,GENDER,CENTER,YEAR,LAST_COLLEGE)

VALUES(8,'TEJAS','MCA','MALE','SATARA','2022','SGM');

INSERT INTO STUD_3(ROLL_NO,NAME,CLASS,GENDER,CENTER,YEAR,LAST_COLLEGE)

VALUES(9,'PRATIK','MCA','MALE','KOLHAPUR','2022','ACVM');

INSERT INTO STUD_3(ROLL_NO,NAME,CLASS,GENDER,CENTER,YEAR,LAST_COLLEGE)

 VALUES(10,'SAIRAJ','MCA','MALE','SATARA','2022','SGM');

INSERT INTO STUD_3(ROLL_NO,NAME,CLASS,GENDER,CENTER,YEAR,LAST_COLLEGE)

VALUES(11,'VIRAJ','MCA','MALE','KOLHAPUR','2022','ACVM');

INSERT INTO STUD_3(ROLL_NO,NAME,CLASS,GENDER,CENTER,YEAR,LAST_COLLEGE)

VALUES(12,'KUNAL','MCA','MALE','SATARA','2022','YC');

INSERT INTO STUD_3(ROLL_NO,NAME,CLASS,GENDER,CENTER,YEAR,LAST_COLLEGE)

VALUES(13,'ANIKET','MCA','MALE','KOLHAPUR','2022','ACVM');

INSERT INTO STUD_3(ROLL_NO,NAME,CLASS,GENDER,CENTER,YEAR,LAST_COLLEGE)

VALUES(14,'SANKET','MCA','MALE','SATARA','2022','YC');

INSERT INTO STUD_3(ROLL_NO,NAME,CLASS,GENDER,CENTER,YEAR,LAST_COLLEGE)

VALUES(15,'SARANG','BCA','MALE','KOLHAPUR','2022','SGM');

INSERT INTO STUD_3(ROLL_NO,NAME,CLASS,GENDER,CENTER,YEAR,LAST_COLLEGE)

VALUES(16,'KARAN','MCA','MALE','KOLHAPUR','2022','YC');

INSERT INTO STUD_3(ROLL_NO,NAME,CLASS,GENDER,CENTER,YEAR,LAST_COLLEGE)

VALUES(17,'SANJAY','MCA','MALE','SATARA','2022','SGM');

INSERT INTO STUD_3(ROLL_NO,NAME,CLASS,GENDER,CENTER,YEAR,LAST_COLLEGE)

VALUES(18,'KIRAN','MCA','MALE','KOLHAPUR','2022','ACVM');

Output :

SELECT * FROM STUd_3;

Output :
 EXPERIMENT NO. 3

Implement UPDATE, DELETE SQL command by using Student_rollno table.

1. UPDATE :

UPDATE STUD_3

SET NAME = 'KETAN', YEAR = 2022

WHERE ROLL_NO = 2

Output :

SELECT * FROM STUD_3

Output :
 2. DELETE :

DELETE FROM STUD_3

WHERE ROLL_NO = 5

OUTPUT :

SELECT * FROM STUDENT_003

OUTPUT :

EXPERIMENT NO. 4
Write a SQL query using GROUP BY work in SQL.

i) MIN() ii) MAX() iii) COUNT iv) AVG() v) SUM()

1. MIN() :

SELECT MIN(ROLL_NO)
FROM (STUD_3)
WHERE CLASS='MCA';

OUTPUT :

2. MAX() :

SELECT MAX(ROLL_NO)
FROM (STUD_3)
WHERE CLASS='MCA';

OUTPUT :

3. COUNT() :

SELECT COUNT(ROLL_NO)
FROM (STUD_3)
WHERE CLASS='MCA';

OUTPUT :
4. AVG() :

SELECT AVG(ROLL_NO)

FROM (STUD_3);

OUTPUT :

5. SUM() :
SELECT SUM(ROLL_NO)
FROM (STUD_3);

OUTPUT :
 EXPERIMENT NO. 5
Write SQL subquery for STUD_3

1. SELECT :

SELECT * FROM STUD_3

WHERE ROLL_NO IN (SELECT ROLL_NO

FROM STUD_3

WHERE CLASS = 'MCA');

OUTPUT :

EXPERIMENT NO. 6
Write PL-SQL Block to print your ROLL_NO, NAME, CLASS.

DECLARE

NAME CHAR(30) := 'SURAJ';

ROLL_NO NUMBER(10) := 2545;

CLASS CHAR(10) := 'MCA';

BEGIN

DBMS_OUTPUT.PUT_LINE('Your NAME is ='||NAME);

DBMS_OUTPUT.PUT_LINE('Your ROLL NUMBER is ='||ROLL_NO);

DBMS_OUTPUT.PUT_LINE('Your CLASS is ='||CLASS);

END;

OUTPUT :
 EXPERIMENT NO. 7

Write a PL-SQL by using a while loop to display all even number between 0 to 50 display the sum of numbers.

DECLARE

s number(10) := 0;

a number(2) := 2;

b number(2) := 4;

BEGIN

DBMS_OUTPUT.PUT_LINE('1 to 50 EVEN NUMBERS : ');

WHILE a < 50 LOOP

DBMS_OUTPUT.PUT_LINE(a);

a := a + 2;

s := s + a;

END LOOP;

DBMS_OUTPUT.PUT_LINE(' ');

DBMS_OUTPUT.PUT_LINE('Sum of 0 to 50 EVEN NUMBERS : '||s);

END;

/
OUTPUT :
 EXPERIMENT NO. 8
Write PL-SQL program by using if----- statement.

DECLARE

a NUMBER(3) := 20;

BEGIN

IF(a<30) THEN

DBMS_OUTPUT.PUT_LINE('a is less than 30');

ELSE

DBMS_OUTPUT.PUT_LINE('a is greater than 30');

END IF;

DBMS_OUTPUT.PUT_LINE('Value of a is : '||a);

END;

OUTPUT :
 EXPERIMENT NO. 9

Write PL-SQL greeting of the days by using procedure.

CREATE OR REPLACE PROCEDURE greetings

AS

BEGIN

DBMS_OUTPUT.PUT_LINE('Good Morning..');

END;

OUTPUT :
 EXPERIMENT NO. 10

Write PL-SQL by using function to read 3 numbers and display the minimum amount then (or smaller).

DECLARE

a NUMBER := 80;

b NUMBER := 30;

c NUMBER := 40;

BEGIN

IF a<b AND a<c THEN

DBMS_OUTPUT.PUT_LINE('Smallest number is : '||a);

ELSIF b<a AND b<c THEN

DBMS_OUTPUT.PUT_LINE('Smallest number is : '||b);

ELSE

DBMS_OUTPUT.PUT_LINE('Smallest number is : '||c);

END IF;

END;

OUTPUT :
 EXPERIMENT NO. 11

Write a PL-SQL to implement the cursor.

DECLARE

TOTAL_ROWS NUMBER(2);

BEGIN

UPDATE CUSTOMERS_3

SET SALARY = SALARY + 1000;

IF SQL%NOTFOUND THEN

DBMS_OUTPUT.PUT_LINE('No Customers Updated..');

ELSIF SQL%FOUND THEN

DBMS_OUTPUT.PUT_LINE('Customers Updated'||TOTAL_ROWS);

END IF;

END;

OUTPUT :
 EXPERIMENT NO. 12

Write a PL-SQL to implement exception in PL-SQL.

DECLARE

C_ID CUSTOMERS_3.ID%TYPE := 10;

C_NAME CUSTOMERS_003.NAME%TYPE;

C_AGE CUSTOMERS_003.AGE%TYPE;

BEGIN

SELECT ID, NAME, AGE INTO C_ID, C_NAME, C_AGE FROM CUSTOMERS_003

WHERE ID = C_ID;

DBMS_OUTPUT.PUT_LINE('NAME : '||C_NAME);

DBMS_OUTPUT.PUT_LINE('AGE : '||C_AGE);

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('NO SUCH CUSTOMER..');

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('ERROR..');

END;

OUTPUT :