Question

An educational institution is maintaining a database for storing the details of courses being offered. The database includes a table COURSE with the following attributes:
C_ID : Stores the unique ID for each course.
C_NAME : Stores the course’s name.
INSTRUCTOR : Stores the name of the course instructor.
DURATION : Stores the duration of the course in hours.

Write SQL queries for the following:
(i) To add a new record with following specifications: 
C_ID : C106 
C_NAME : Introduction to AI 
INSTRUCTOR : Ms. Preeti 
DURATION : 55 
(ii) To display the longest duration among all courses.
(iii) To count total number of courses run by the institution.
(iv) To display the instructors’ name in lower case.
 

Hint:

Use INSERT INTO to add rows, MAX() for highest value, COUNT() to get total rows, and LOWER() or UPPER() for text formatting in SQL.

Answer 1

(i) SQL query to insert a new course:
To add a new course record, we use the INSERT INTO command with column names and values.
This statement adds the record “Introduction to AI” with C_ID C106, taught by Ms. Preeti, with a duration of 55 hours.

INSERT INTO COURSE (C_ID, C_NAME, INSTRUCTOR, DURATION)
VALUES ('C106', 'Introduction to AI', 'Ms. Preeti', 55);

Explanation: This command clearly specifies the table name COURSE.
It lists the four columns to be filled: C_ID, C_NAME, INSTRUCTOR, and DURATION.
The VALUES clause gives the exact values for each column in the same order.
The new row will be added at the end of the table.
Using proper quotes for text values ensures that string data like C_ID, course name, and instructor name are stored correctly.
(ii) SQL query to find the longest duration:
To find the longest duration among all courses, we use the MAX() function.

SELECT MAX(DURATION) 
FROM COURSE;

Explanation: The MAX() function is an aggregate function that returns the highest value in a column.
Here, it scans the DURATION column and finds the largest number.
It helps the institution identify the course with the maximum time requirement.
(iii) SQL query to count total number of courses:
To get the total count of courses offered, we use COUNT().

SELECT COUNT(*) 
FROM COURSE;

Explanation: COUNT(*) counts all rows in the table, giving the total number of courses.
It is useful for generating reports about how many courses are active in the institution.
This includes the newly added course as well, so the count will be up to date after insertion.
(iv) SQL query to display instructor names in lower case:
To display all instructor names in lower case, we use the LOWER() function.

SELECT LOWER(INSTRUCTOR) 
FROM COURSE;

Explanation: The LOWER() function converts text values to lowercase.
Here, it is applied to the INSTRUCTOR column to standardize the names.
This is useful for formatting reports or when comparing text data without case sensitivity.
It shows how SQL can handle text manipulation easily using built-in string functions.