Scribd
Upload
3 views3 pages

SQL 6

The document outlines a program that utilizes NESTED IF, CASE, and CASE expressions to evaluate student grades based on their marks. It includes a database table for students, inserts data, and uses conditional logic to assign grades, remarks, and bonuses. Additionally, it employs NULLIF and COALESCE functions to handle specific conditions and output results.

Uploaded by

sudhaaass
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
3 views3 pages

SQL 6

The document outlines a program that utilizes NESTED IF, CASE, and CASE expressions to evaluate student grades based on their marks. It includes a database table for students, inserts data, and uses conditional logic to assign grades, remarks, and bonuses. Additionally, it employs NULLIF and COALESCE functions to handle specific conditions and output results.

Uploaded by

sudhaaass
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

23b01a3231

[Link] a program that includes the features NESTED IF, CASE and CASE
expression. The program can be extended using the NULLIF and COALESCE
functions.

CREATE TABLE STUDENTS (

​ student_id​ NUMBER PRIMARY KEY,

​ student_name VARCHAR2(50),

​ marks ​ NUMBER

);

INSERT INTO STUDENTS VALUES (101, 'Alice', 92);

INSERT INTO STUDENTS VALUES (102, 'Bob', 85);

INSERT INTO STUDENTS VALUES (103, 'Charlie', 75);

INSERT INTO STUDENTS VALUES (104, 'David', 60);

INSERT INTO STUDENTS VALUES (105, 'Eve', 50);

COMMIT;

DECLARE

v_student_id NUMBER := 101;

​ v_student_name VARCHAR2(50);

​ v_marks ​ NUMBER;

​ v_grade ​ VARCHAR2(10);

​ v_remarks ​ VARCHAR2(50);

​ v_bonus ​ NUMBER;

BEGIN

​ SELECT student_name, marks INTO v_student_name, v_marks

​ FROM STUDENTS

​ WHERE student_id = v_student_id;


23b01a3231
​ IF v_marks >= 90 THEN

​ v_grade := 'A+';

​ ELSIF v_marks >= 80 THEN

​ v_grade := 'A';

​ ELSIF v_marks >= 70 THEN

​ v_grade := 'B';

​ ELSIF v_marks >= 60 THEN

​ v_grade := 'C';

​ ELSE

​ v_grade := 'F';

​ END IF;

​ CASE

​ WHEN v_grade = 'A+' THEN v_remarks := 'Excellent';

​ WHEN v_grade = 'A' THEN v_remarks := 'Very Good';

​ WHEN v_grade = 'B' THEN v_remarks := 'Good';

​ WHEN v_grade = 'C' THEN v_remarks := 'Satisfactory';

​ ELSE v_remarks := 'Needs Improvement';

​ END CASE;

v_bonus := CASE

​ WHEN v_marks >= 90 THEN 1000

​ WHEN v_marks >= 80 THEN 500

​ ELSE 100

​END;

IF NULLIF(v_marks, v_bonus) IS NULL THEN

​ DBMS_OUTPUT.PUT_LINE('Marks and bonus are the same.');


23b01a3231
​ END IF;

v_remarks := COALESCE(v_remarks, 'No Remarks');

DBMS_OUTPUT.PUT_LINE('Student: ' || v_student_name);

DBMS_OUTPUT.PUT_LINE('Marks: ' || v_marks);

DBMS_OUTPUT.PUT_LINE('Grade: ' || v_grade);

DBMS_OUTPUT.PUT_LINE('Remarks: ' || v_remarks);

DBMS_OUTPUT.PUT_LINE('Bonus: ' || v_bonus);

END;

Output:

You might also like