Bright Tutorials Bright Tutorials
CBSE Class 12 Informatics Practices Question 56 of 79

Database Query using SQL — Question 14

Back to all questions
14
Question

Question 14

Consider the given table Faculty and answer the questions that follow:

Table: FACULTY

F_IDF_NameL_NameHire_dateSalary
102AmitMishra1998-10-1210000
103NitinVyas1994-12-248000
104RakshitSoni2001-05-1814000
105RashmiMalhotra2004-09-1111000
106SulekhaSrivastava2006-06-0510000

(a) To display the details of those Faculty members whose salary is higher than 12000.

(b) To display the details of Faculty members whose salary is in the range of 8000 to 12000 (both values included).

(c) Count the number of different ids from faculty.

(d) Count the number of faculty members getting salary as 10000.

(e) Display details of those faculty members whose names start with S.

(f) Display all records in descending order of Hire date.

(g) Find the maximum and the minimum salary.

(h) Select CONCAT(F_Name, L_Name) from FACULTY;

(i) Select Month(Hire_date) from FACULTY;

Answer

(a)

SELECT * FROM FACULTY WHERE SALARY > 12000;
Output
+------+---------+--------+------------+--------+
| F_ID | F_NAME  | L_NAME | HIRE_DATE  | SALARY |
+------+---------+--------+------------+--------+
|  104 | RAKSHIT | SONI   | 2001-05-18 |  14000 |
+------+---------+--------+------------+--------+

(b)

SELECT * FROM FACULTY WHERE SALARY BETWEEN 8000 AND 12000;
Output
+------+---------+------------+------------+--------+
| F_ID | F_NAME  | L_NAME     | HIRE_DATE  | SALARY |
+------+---------+------------+------------+--------+
|  102 | AMIT    | MISHRA     | 1998-10-12 |  10000 |
|  103 | NITIN   | VYAS       | 1994-12-24 |   8000 |
|  105 | RASHMI  | MALHOTRA   | 2004-09-11 |  11000 |
|  106 | SULEKHA | SRIVASTAVA | 2006-06-05 |  10000 |
+------+---------+------------+------------+--------+

(c)

SELECT COUNT(DISTINCT F_ID) FROM FACULTY;
Output
+----------------------+
| COUNT(DISTINCT F_ID) |
+----------------------+
|                    5 |
+----------------------+

(d)

SELECT COUNT(*) FROM FACULTY WHERE SALARY = 10000;
Output
+----------+
| COUNT(*) |
+----------+
|        2 |
+----------+

(e)

 SELECT * FROM FACULTY WHERE F_NAME LIKE 'S%';
Output
+------+---------+------------+------------+--------+
| F_ID | F_NAME  | L_NAME     | HIRE_DATE  | SALARY |
+------+---------+------------+------------+--------+
|  106 | SULEKHA | SRIVASTAVA | 2006-06-05 |  10000 |
+------+---------+------------+------------+--------+

(f)

SELECT * FROM FACULTY ORDER BY HIRE_DATE DESC;
Output
+------+---------+------------+------------+--------+
| F_ID | F_NAME  | L_NAME     | HIRE_DATE  | SALARY |
+------+---------+------------+------------+--------+
|  106 | SULEKHA | SRIVASTAVA | 2006-06-05 |  10000 |
|  105 | RASHMI  | MALHOTRA   | 2004-09-11 |  11000 |
|  104 | RAKSHIT | SONI       | 2001-05-18 |  14000 |
|  102 | AMIT    | MISHRA     | 1998-10-12 |  10000 |
|  103 | NITIN   | VYAS       | 1994-12-24 |   8000 |
+------+---------+------------+------------+--------+

(g)

SELECT MAX(SALARY), MIN(SALARY) FROM FACULTY;
Output
+-------------+-------------+
| MAX(SALARY) | MIN(SALARY) |
+-------------+-------------+
|       14000 |        8000 |
+-------------+-------------+

(h)

Output
+------------------------+
| CONCAT(F_Name, L_Name) |
+------------------------+
| AMITMISHRA             |
| NITINVYAS              |
| RAKSHITSONI            |
| RASHMIMALHOTRA         |
| SULEKHASRIVASTAVA      |
+------------------------+

(i)

Output
+------------------+
| Month(Hire_date) |
+------------------+
|               10 |
|               12 |
|                5 |
|                9 |
|                6 |
+------------------+