39
5
Write SQL commands for the following queris (i) to (v) based on the relations Teacher and
Posting given below:
Table : Teacher
T_ID
Name
Salary
Gender
Age
12000
1
Jugal
34
20000
2
Sharmila
31
30000
M
3
Department Date_of_join
Computer
10/01/2017
Sc
History
24/03/2008
Mathematics
12/12/2016
01/07/2015
History
05/09/2007
Mathematics
History
27/06/2008
Computer
25/02/2017
40000
F
Sandeep 32
Sangeeta 35
Rakesh 42
4
25000
M
01
30000
M
6
Shyam
50
21000
7
Shiv Om
SC
31/07/2018
20000
Shalakha
33
Mathematics
Place
Agra
Table : Posting
P_ID Department
1
History
2
Mathematics
3
Computer Science
Raipur
Delhi
i) To show all information about the teacher of History department.
ii) To list the names of female teachers who are in Mathematics department.
iii) To list the names of all teachers with their date of joining in ascending order.
iv) To display teacher's name, salary, age for male teachers only.
v) To display name, bonus for each teacher where bonus is 10% of salary.
Answers
Answer:
39
5
Write SQL commands for the following queris (i) to (v) based on the relations Teacher and
Posting given below:
Table : Teacher
T_ID
Name
Salary
Gender
Age
12000
1
Jugal
34
20000
2
Sharmila
31
30000
M
3
Department Date_of_join
Computer
10/01/2017
Sc
History
24/03/2008
Mathematics
12/12/2016
01/07/2015
History
05/09/2007
Mathematics
History
27/06/2008
Computer
25/02/2017
40000
F
Sandeep 32
Sangeeta 35
Rakesh 42
4
25000
M
01
30000
M
6
Shyam
50
21000
7
Shiv Om
SC
31/07/2018
20000
Shalakha
33
Mathematics
Place
Agra
Table : Posting
P_ID Department
1
History
2
Mathematics
3
Computer Science
Raipur
Delhi
i) To show all information about the teacher of History department.
ii) To list the names of female teachers who are in Mathematics department.
iii) To list the names of all teachers with their date of joining in ascending order.
iv) To display teacher's name, salary, age for male teachers only.
v) To display name, bonus for each teacher where bonus is 10% of salary.