Check sibling questions

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:

Question 13 Long Answer 5 - image 1.jpg

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

Question 13 Long Answer 5 - image 2.jpg

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 (i) Long Answer 5 - image.jpg

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 (ii) Long Answer 5 - image.jpg

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 (iii) Long Answer 5 - image.jpg

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 (iv) Long Answer 5 - image.jpg

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;

Question 13 (v) Long Answer 5 - image.jpg


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;

Davneet Singh's photo - Co-founder, Teachoo

Made by

Davneet Singh

Davneet Singh has done his B.Tech from Indian Institute of Technology, Kanpur. He has been teaching from the past 12 years. He provides courses for Maths, Science, Social Science, Physics, Chemistry, Computer Science at Teachoo.