Question 2 - Past Year - 3 Mark Questions - Chapter 9 - Structured Query Language

This question was asked in CBSE SQP 2020

Write SQL queries for (i) to (ii), which are based on the table: STUDENT given in the question no.

Question 2 (i)

To display the records from table STUDENT in alphabetical order as per the name of the student.

Answer:

To display the records from table STUDENT in alphabetical order as per the name of the student, the ORDER BY clause followed by the column_name NAME should be used in the query.

QUERY: SELECT * FROM STUDENT ORDER BY NAME; 

Question 2 (ii)

To display Class, DOB and City whose marks is between 450 and 551.

Answer:

To display the Class, DOB and City of students whose marks are between 450 and 551, the comparison keyword BETWEEN followed by the range ie., 450 AND 551 should be used in the query.  

QUERY:  SELECT NAME,CLASS,DOB,CITY FROM STUDENT WHERE MARKS BETWEEN 450 AND 551;

Question 2 (iii)

To display Name, Class and total number of students who have scored more than 450 marks, class wise.

Answer:

To display Class and total number of students who have scored more than 450 marks, class  wise:

• The COUNT( ) function is used to fetch the total number of students
• To check if marks are greater than 450, we use the relational operator ‘>’ .
• The GROUP BY clause is used to group the records class wise.

QUERY: SELECT CLASS,COUNT(*) FROM STUDENT WHERE MARKS>450 GROUP BY CLASS;