Informatics Practices
Write a query to find out the number of students in each Stream in STUDENT table.
SQL Queries
3 Likes
Answer
The Student table is as follows:
| ROLLNO | NAME | GENDER | MARKS | DOB | MOBILE_NO | STREAM |
|---|---|---|---|---|---|---|
| 1 | RAJ KUMAR | M | 93 | 2000-11-17 | 9586774748 | SCIENCE |
| 2 | DEEP SINGH | M | 98 | 1996-08-22 | 8988886577 | COMMERCE |
| 3 | ANKIT SHARMA | M | 76 | 2000-02-02 | NULL | SCIENCE |
| 4 | RADHIKA GUPTA | F | 78 | 1999-12-03 | 9818675444 | HUMANITIES |
| 5 | PAYAL GOEL | F | 82 | 1998-04-21 | 9845639990 | VOCATIONAL |
| 6 | DIKSHA SHARMA | F | 80 | 1999-12-17 | 9897666650 | HUMANITIES |
| 7 | GURPREET KAUR | F | NULL | 2000-01-04 | 7560875609 | SCIENCE |
| 8 | AKSHAY DUREJA | M | 90 | 1997-05-05 | 9560567890 | COMMERCE |
| 9 | SHREYA ANAND | F | 70 | 1999-10-08 | NULL | VOCATIONAL |
| 10 | PRATEEK MITTAL | M | 75 | 2000-12-25 | 9999967543 | SCIENCE |
SELECT STREAM, COUNT(*) AS NUMBER_OF_STUDENTS
FROM STUDENT
GROUP BY STREAM;
Output
+------------+--------------------+
| STREAM | NUMBER_OF_STUDENTS |
+------------+--------------------+
| SCIENCE | 4 |
| COMMERCE | 2 |
| HUMANITIES | 2 |
| VOCATIONAL | 2 |
+------------+--------------------+
Answered By
3 Likes
Related Questions
What is the difference between sysdate() and now() functions ?
Consider two fields—B_date, which stores the birth date, and J_date, which stores the joining date of an employee. Write commands to find out and display the approximate age of an employee as on joining date.
Consider the given table Faculty and answer the questions that follow:
Table: FACULTY
F_ID F_Name L_Name Hire_date Salary 102 Amit Mishra 1998-10-12 10000 103 Nitin Vyas 1994-12-24 8000 104 Rakshit Soni 2001-05-18 14000 105 Rashmi Malhotra 2004-09-11 11000 106 Sulekha Srivastava 2006-06-05 10000 (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;
Consider the following EMP and DEPT tables:
Table: EMP
EmpNo EmpName City Designation DOJ Sal Comm DeptID 8369 SMITH Mumbai CLERK 1990-12-18 800.00 NULL 20 8499 ANYA Varanasi SALESMAN 1991-02-20 1600.00 300.00 30 8521 SETH Jaipur SALESMAN 1991-02-22 1250.00 500.00 30 8566 MAHADEVAN Delhi MANAGER 1991-04-02 2985.00 NULL 20 Table: DEPT
DeptID DeptName MgrID Location 10 SALES 8566 Mumbai 20 PERSONNEL 9698 Delhi 30 ACCOUNTS 4578 Delhi 40 RESEARCH 8839 Bengaluru Write the SQL command to get the following:
(a) Show the minimum, maximum and average salary of managers.
(b) Count the number of clerks in the organization.
(c) Display the designation-wise list of employees with name, salary and date of joining.
(d) Count the number of employees who are not getting commission.
(e) Show the average salary for all departments with more than 5 working people.
(f) List the count of employees grouped by DeptID.
(g) Display the maximum salary of employees in each department.
(h) Display the name of employees along with their designation and department name.
(i) Count the number of employees working in ACCOUNTS department.