i am trying to combine the PL/SQL and the SQL commands in manipulating the database. am i doing it correctly? any help and corrections?

enter image description hereuse PL/SQL with SQL commands in manipulating the database (PL/SQL structure, Data types, Variable, DBMS Output and Conditions) and this is my codes and what i have understandenter image description here

CREATE TABLE Grades(
    STUDENT_ID INT NOT NULL PRIMARY KEY, 
    SName VARCHAR2(50),
    Grades NUMBER,
    Top_Average NUMBER(3)
);

INSERT INTO Grades(STUDENT_ID, SName, Grades, Top_Average)VALUES(1001,'Carmina',75);
INSERT INTO Grades(STUDENT_ID, SName, Grades, Top_Average)VALUES(1002,'Danielle',90,100);
INSERT INTO Grades(STUDENT_ID, SName, Grades, Top_Average)VALUES(1003,'Sophia',80,100);
INSERT INTO Grades(STUDENT_ID, SName, Grades, Top_Average)VALUES(1004,'Zoe',85,100);
INSERT INTO Grades(STUDENT_ID, SName, Grades, Top_Average)VALUES(1005,'Carlo',78,100);

SELECT * FROM Grades;

DECLARE
    Grades NUMBER:= 75;
    Top_Average NUMBER:= 100;
   
BEGIN
    dbms_output.put_line('The sum of ' || Grades ||' and ' || Top_Average || ' is ' || (Grades + Top_Average));
    dbms_output.put_line('The difference of ' || Grades ||' and ' || Top_Average || ' is ' || (Grades - Top_Average));
    dbms_output.put_line('The product of ' || Grades ||' and ' || Top_Average || ' is ' || (Grades * Top_Average));
    
if Top_Average = grades then
    dbms_output.put_line('The quotient of ' || Grades ||' and ' || Top_Average|| ' is ' || ' FAILED');
    
ELSE
    dbms_output.put_line('The quotient of ' || Grades ||' and ' || Top_Average || ' is ' || (Grades / Top_Average)); 
end if;   
end; 
/

Answer

The sample code for me does not run. It gives

INSERT INTO Grades(STUDENT_ID, SName, Grades, Top_Average)VALUES(1001,'Carmina',75)
Error at Command Line : 8 Column : 59
Error report -
SQL Error: ORA-00947: not enough values
00947. 00000 -  "not enough values"

The reason is that you have 4 columns in your statement but only 3 values in the “values” section for the first insert statement. Either remove the “top_average” column or add a value.

However, that issue has nothing to do with the error you are getting. The screenshot gives it away. You are selecting the following 3 lines

DECLARE
    Grades NUMBER:= 75;
    Top_Average NUMBER:= 100;

and then running that as a statement. If you select a number of lines in sqldeveloper it will try to run those selected lines only as a statement. It’s not a complete statement so it fails.

To solve it. Put your cursor in before or in the pl/sql block and run the statement. It runs without errors but it doesn’t display anything because the set serveroutput on is missing before the block.

Here is the properly formatted code

• Properly ident pl/sql
• changed keywords to upper case, identifiers to lowercase (for readability)
• renamed variables in pl/sql – see comment.

DROP TABLE grades;

CREATE TABLE grades(
    student_id INT NOT NULL PRIMARY KEY, 
    sname VARCHAR2(50),
    grades NUMBER,
    top_average NUMBER(3)
);

INSERT INTO grades(student_id, sname, grades) VALUES (1001,'Carmina',75);
INSERT INTO grades(student_id, sname, grades,top_average) VALUES (1002,'Danielle',90,100);
INSERT INTO grades(student_id, sname, grades,top_average) VALUES (1003,'Sophia',80,100);
INSERT INTO grades(student_id, sname, grades,top_average) VALUES (1004,'Zoe',85,100);
INSERT INTO grades(student_id, sname, grades,top_average) VALUES (1005,'Carlo',78,100);

SELECT * FROM grades;

set serveroutput on size 999999
--clear screen
DECLARE
-- do NOT name your variables the same as your column names. It's confusing. Instead prefix with "l_" (local variable) 
    l_grades NUMBER:= 75;
    l_top_average NUMBER:= 100;
BEGIN
  dbms_output.put_line('The sum of ' || l_grades ||' and ' || l_top_average || ' is ' || (l_grades + l_top_average));
  dbms_output.put_line('The difference of ' || l_grades ||' and ' || l_top_average || ' is ' || (l_grades - l_top_average));
  dbms_output.put_line('The product of ' || l_grades ||' and ' || l_top_average || ' is ' || (l_grades * l_top_average));  
  IF l_top_average = l_grades THEN
    dbms_output.put_line('The quotient of ' || l_grades ||' and ' || l_top_average|| ' is ' || ' FAILED');
  ELSE
    dbms_output.put_line('The quotient of ' || l_grades ||' and ' || l_top_average || ' is ' || (l_grades / l_top_average)); 
  END IF;   
END; 
/

Table GRADES created.


1 row inserted.


1 row inserted.


1 row inserted.


1 row inserted.


1 row inserted.


STUDENT_ID SNAME                                                  GRADES TOP_AVERAGE
---------- -------------------------------------------------- ---------- -----------
      1001 Carmina                                                    75            
      1002 Danielle                                                   90         100
      1003 Sophia                                                     80         100
      1004 Zoe                                                        85         100
      1005 Carlo                                                      78         100

The sum of 75 and 100 is 175
The difference of 75 and 100 is -25
The product of 75 and 100 is 7500
The quotient of 75 and 100 is .75


PL/SQL procedure successfully completed.