CBSE Class 12 Informatics Practices
Question 66 of 79
Database Query using SQL — Question 24
Back to all questions 24
Question Consider the following table and answer the questions that follow:
Table: TEACHER
| ID | Name | Department | Hiredate | Category | Gender | Salary |
|---|---|---|---|---|---|---|
| 1 | Tanya Nanda | SocialStudies | 1994-03-17 | TGT | F | 25000 |
| 2 | Saurabh Sharma | Art | 1990-02-12 | PRT | M | 20000 |
| 3 | Nandita Arora | English | 1980-05-16 | PGT | F | 30000 |
| 4 | James Jacob | English | 1989-10-16 | TGT | M | 25000 |
| 5 | Jaspreet Kaur | Hindi | 1990-08-01 | PRT | F | 22000 |
| 6 | Disha Sehgal | Math | 1980-03-17 | PRT | F | 21000 |
| 8 | SonaliMukherje | Math | 1980-11-17 | TGT | F | 24500 |
Write the command/output for the following:
(a) To display all information about the teacher of PGT category.
(b) To list the names of female teachers of Hindi department.
(c) To list names, departments and date of hiring of all the teachers in ascending order of date of joining.
(d) To count the number of teachers in English Department.
(e) Display the department and hire date of all the female teachers whose salary is more than 25000.
(f) Display the list of teachers whose name starts with J.
(g) SELECT COUNT(*) FROM TEACHER WHERE Category = 'PGT';
(h) SELECT AVG(Salary) FROM TEACHER GROUP BY Gender;
(a)
SELECT *
FROM TEACHER
WHERE CATEGORY = 'PGT' ;+----+---------------+------------+------------+----------+--------+--------+
| ID | NAME | DEPARTMENT | HIREDATE | CATEGORY | GENDER | SALARY |
+----+---------------+------------+------------+----------+--------+--------+
| 3 | NANDITA ARORA | ENGLISH | 1980-05-16 | PGT | F | 30000 |
+----+---------------+------------+------------+----------+--------+--------+
(b)
SELECT Name
FROM TEACHER
WHERE Department = 'Hindi' AND Gender = 'F';+---------------+
| Name |
+---------------+
| JASPREET KAUR |
+---------------+
(c)
SELECT NAME, DEPARTMENT, HIREDATE
FROM TEACHER
ORDER BY HIREDATE;+------------------+---------------+------------+
| NAME | DEPARTMENT | HIREDATE |
+------------------+---------------+------------+
| DISHA SEHGAL | MATH | 1980-03-17 |
| NANDITA ARORA | ENGLISH | 1980-05-16 |
| SONALI MUKHERJEE | MATH | 1980-11-17 |
| JAMES JACOB | ENGLISH | 1989-10-16 |
| SAURABH SHARMA | ART | 1990-02-12 |
| JASPREET KAUR | HINDI | 1990-08-01 |
| TANYA NANDA | SOCIALSTUDIES | 1994-03-17 |
+------------------+---------------+------------+
(d)
SELECT COUNT(*)
FROM TEACHER
WHERE DEPARTMENT = 'ENGLISH';+----------+
| COUNT(*) |
+----------+
| 2 |
+----------+
(e)
SELECT DEPARTMENT, HIREDATE
FROM TEACHER
WHERE GENDER = 'F' AND SALARY > 25000;+------------+------------+
| DEPARTMENT | HIREDATE |
+------------+------------+
| ENGLISH | 1980-05-16 |
+------------+------------+
(f)
SELECT Name
FROM TEACHER
WHERE Name LIKE 'J%';+---------------+
| Name |
+---------------+
| JAMES JACOB |
| JASPREET KAUR |
+---------------+
(g)
+----------+
| COUNT(*) |
+----------+
| 1 |
+----------+
(h)
+-------------+
| AVG(Salary) |
+-------------+
| 24500.0000 |
| 22500.0000 |
+-------------+