Question

Write SQL query for the following:
(i) To display sum total of all the values of the Score column, from STUDENTS table.
(ii) To display the first five characters of the Name column from STUDENTS table.
(iii) To display the values of Name column from the STUDENTS table, after removing the trailing spaces.
(iv) To retrieve the lowest score from the Score column of GRADES table.
(v) To increase the fee of all students by 100, in the STUDENTS table. (The name of the column is FEE)

Hint:

Use SUM() and MIN() for calculations, SUBSTRING() and RTRIM() for string formatting, and UPDATE for changing data in place.

Answer 1

(i) Sum of all scores:
To calculate the total of all the values in the Score column of STUDENTS, we use the SUM() function.

SELECT SUM(Score) FROM STUDENTS;

Explanation: SUM() is an aggregate function that adds up all the numeric values in the specified column.
This query will return a single value which is the total score of all students combined.
It is useful for analyzing total marks or performance at a class or batch level.
(ii) Display first five characters of Name:
To extract the first five letters from each value in the Name column, use SUBSTRING().

SELECT SUBSTRING(Name, 1, 5) FROM STUDENTS;

Explanation: The SUBSTRING() function takes three arguments — the column name, start position, and number of characters.
Here, it starts at position 1 and extracts 5 characters.
It is often used to format names or create short forms for reports.
(iii) Remove trailing spaces from Name:
To trim trailing spaces from the Name column values, use the RTRIM() function.

SELECT RTRIM(Name) FROM STUDENTS;

Explanation: RTRIM() removes any spaces to the right (end) of the string.
This is helpful for data cleaning, ensuring names are neat for reports, and avoiding errors in matching or comparisons.
(iv) Retrieve the lowest score from GRADES:
To find the smallest score value in the Score column of the GRADES table, use MIN().

SELECT MIN(Score) FROM GRADES;

Explanation: MIN() is an aggregate function that finds the smallest numeric value.
It helps identify the weakest performance or lowest marks among all students.
(v) Increase fee by 100 for all students:
To add 100 to each student’s FEE value, use the UPDATE statement.

UPDATE STUDENTS SET FEE = FEE + 100;

Explanation: This command updates the FEE column by adding 100 to the existing fee for every row.
It changes the original table data and should be done with care to avoid incorrect values.