Question 13 - Past Year - 5 Mark Questions - Chapter 9 - Structured Query Language

Transcript

This question asked in CBSE SQP 2021 Write SQL commands for the following queries (i) to (v) based on the relations Teacher and Posting given below: Table: TEACHER T_ID Name Age Department Date_of_Join Salary Gender 1 Jugal 34 Computer Sc 10/01/2017 12000 M 2 Sharmila 31 History 24/03/2008 20000 F 3 Sandeep 32 Mathematics 12/12/2016 30000 M 4 Sangeeta 35 History 01/07/2015 40000 F 5 Rakesh 42 Mathematics 05/09/2007 25000 M 6 Shyam 50 History 27/06/2008 30000 M 7 Shiv Om 44 Computer Sc 25/02/2017 21000 M 8 Shalakha 33 Mathematics 31/07/2018 20000 F Table: POSTING P_ID Department Place 1 History Agra 2 Mathematics Raipur 3 Computer Sc Delhi Question 13 (i) To show all information about the teacher of History department. Answer: To show all information about the teacher of the History department, the WHERE clause followed by the condition ie., DEPARTMENT=’History’ should be used in the query. QUERY: SELECT * FROM TEACHER WHERE DEPARTMENT='History'; Question 13 (ii) To list the names of female teachers who are in Mathematics department. Answer: To list the names of female teachers who are in the Mathematics department, the WHERE clause followed by the condition ie., GENDER=’F’ AND DEPARTMENT=’Mathematics’ should be used in the query. QUERY: SELECT NAME FROM TEACHER WHERE GENDER='F' AND DEPARTMENT='Mathematics'; Question 13 (iii) To list the names of all teachers with their date of joining in ascending order. Answer: To list the names of all teachers with their date of joining in ascending order, the ORDER BY clause followed by the column name ie., DATE_OF_JOIN should be used in the query. QUERY: SELECT NAME FROM TEACHER ORDER BY DATE_OF_JOIN; Question 13 (iv) To display teacher’s name, salary, age for male teachers only. Answer: To display teacher’s name, salary, age for male teachers only, the WHERE clause followed by the condition ie., GENDER=’M’ should be used in the query. QUERY: SELECT NAME,SALARY,AGE FROM TEACHER WHERE GENDER='M'; Question 13 (v) To display name, bonus for each teacher where bonus is 10% of salary. Answer: To display name, bonus for each teacher where bonus is 10% of salary, the statement salary*0.1 AS BONUS should be used in the query. QUERY: SELECT NAME,SALARY*0.1 AS BONUS FROM TEACHER;