Question

Given the following tables:

Write SQL queries for the following:
(i) To display the number of students from each city.
(ii) To find the average age of all students.
(iii) To list the names of students and their grades.
 

Hint:

Use GROUP BY for grouping, AVG() for averages, and JOIN conditions for combining related tables.

Answer 1

(i) SQL query to display the number of students from each city:

SELECT CITY, COUNT(*) 
FROM STUDENTS 
GROUP BY CITY;

Explanation: The `COUNT(*)` function counts the number of records for each city.
`GROUP BY CITY` groups students based on the city value.
(ii) SQL query to find the average age of all students:

SELECT AVG(AGE) 
FROM STUDENTS;

Explanation: The `AVG()` function calculates the average of the AGE column for all rows in the STUDENTS table.
(iii) SQL query to list the names of students and their grades:

SELECT STUDENTS.NAME, GRADES.GRADE
FROM STUDENTS, GRADES
WHERE STUDENTS.S_ID = GRADES.S_ID;

Explanation: This query uses a join condition to match S_ID in STUDENTS with S_ID in GRADES.
It displays the student name and their grade by combining data from both tables.