Untitled
unknown
plain_text
a year ago
7.1 kB
52
Indexable
06/02/2024
--------------------------------------------------------------------------------------------------------------------------------------------------------
group by clause example
From the following table, write a SQL query to count the number of employees worked under each manager . Return manager ID and number of employees.
Sample table: employees
+-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+
| EMPLOYEE_ID | FIRST_NAME | LAST_NAME | EMAIL | PHONE_NUMBER | HIRE_DATE | JOB_ID | SALARY | COMMISSION_PCT | MANAGER_ID | DEPARTMENT_ID |
+-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+
| 100 | Steven | King | SKING | 515.123.4567 | 2003-06-17 | AD_PRES | 24000.00 | 0.00 | 0 | 90 |
| 101 | Neena | Kochhar | NKOCHHAR | 515.123.4568 | 2005-09-21 | AD_VP | 17000.00 | 0.00 | 100 | 90 |
| 102 | Lex | De Haan | LDEHAAN | 515.123.4569 | 2001-01-13 | AD_VP | 17000.00 | 0.00 | 100 | 90 |
| 103 | Alexander | Hunold | AHUNOLD | 590.423.4567 | 2006-01-03 | IT_PROG | 9000.00 | 0.00 | 102 | 60 |
| 104 | Bruce | Ernst | BERNST | 590.423.4568 | 2007-05-21 | IT_PROG | 6000.00 | 0.00 | 103 | 60 |
| 105 | David | Austin | DAUSTIN | 590.423.4569 | 2005-06-25 | IT_PROG | 4800.00 | 0.00 | 103 | 60 |
| 106 | Valli | Pataballa | VPATABAL | 590.423.4560 | 2006-02-05 | IT_PROG | 4800.00 | 0.00 | 103 | 60 |
| 107 | Diana | Lorentz | DLORENTZ | 590.423.5567 | 2007-02-07 | IT_PROG | 4200.00 | 0.00 | 103 | 60 |
| 108 | Nancy | Greenberg | NGREENBE | 515.124.4569 | 2002-08-17 | FI_MGR | 12008.00 | 0.00 | 101 | 100 |
| 109 | Daniel | Faviet | DFAVIET | 515.124.4169 | 2002-08-16 | FI_ACCOUNT | 9000.00 | 0.00 | 108 | 100 |
| 110 | John | Chen | JCHEN | 515.124.4269 | 2005-09-28 | FI_ACCOUNT | 8200.00 | 0.00 | 108 | 100 |
| 111 | Ismael | Sciarra | ISCIARRA | 515.124.4369 | 2005-09-30 | FI_ACCOUNT | 7700.00 | 0.00 | 108 | 100 |
| 112 | Jose Manuel | Urman | JMURMAN | 515.124.4469 | 2006-03-07 | FI_ACCOUNT | 7800.00 | 0.00 | 108 | 100 |
| 113 | Luis | Popp | LPOPP | 515.124.4567 | 2007-12-07 | FI_ACCOUNT | 6900.00 | 0.00 | 108 | 100 |
| 114 | Den | Raphaely | DRAPHEAL | 515.127.4561 | 2002-12-07 | PU_MAN | 11000.00 | 0.00 | 100 | 30 |
| 115 | Alexander | Khoo | AKHOO | 515.127.4562 | 2003-05-18 | PU_CLERK | 3100.00 | 0.00 | 114 | 30 |
| 116 | Shelli | Baida | SBAIDA | 515.127.4563 | 2005-12-24 | PU_CLERK | 2900.00 | 0.00 | 114 | 30 |
| 117 | Sigal | Tobias | STOBIAS | 515.127.4564 | 2005-07-24 | PU_CLERK | 2800.00 | 0.00 | 114 | 30 |
|
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Having clause:-
--------------
having clause is used for allow to filter query result based on aggregate function(sum(),avg(),min(),max(),count()) and grouping(group by).
imp point:-
1.having clause is used to filter data according to condition
2.having clause is used only select clause.
3.having clause is used with group by clause
4.having clause is used after group by clause
syntax:
select column,function(column) from tablename group by column having condition;
exa:select count(*) ,salesman_id from customer group by salesman_id having count(*)>2 ;
if we use group by ,having, where and order by clause in one query;
so sequnce is:
1.where
2.group by
3.having
4.order by
Select count(emp_id) , mn_id from employee group by mn_id ;
Select count(emp_id) , mn_id from employee group by mn_id having count(emp_id) between 1 and 5;
select count(*) ,salesman_id from customer where customer_id <3010 group by salesman_id having count(*)>=2 order by salesman_id desc ;
-----------------------------------------------------------------------------------------------------------------------------------------------------
difference between where and having clause
where:-1.where caluse is used to filter the data from the table in the specific condition.
2. where clause cannot cointain aggregate function.
3.where clause can be used without group by clause.
4.where clause is used with select,update,delete.
5.where clause is used before group by clause.
having:-1. having clause is uesd for filter the records from the group based on the given condition.
2. having clause contain aggregate function.
3. having clause cannot be used without group by clause.
4. having clause is only used with select clause.
5. having clause is used after group by clause.
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
assignment:
Q1. From the following table, write a SQL query to find the maximum
order (purchase) amount in the range 2000 - 6000 (Begin and end values are included.)
by combination of each customer and order date.
Return customer id, order date and maximum purchase amount.
Sample table: orders
ord_no purch_amt ord_date customer_id salesman_id
---------- ---------- ---------- ----------- -----------
70001 150.5 2012-10-05 3005 5002
70009 270.65 2012-09-10 3001 5005
70002 65.26 2012-10-05 3002 5001
70004 110.5 2012-08-17 3009 5003
70007 948.5 2012-09-10 3005 5002
70005 2400.6 2012-07-27 3007 5001
70008 5760 2012-09-10 3002 5001
70010 1983.43 2012-10-10 3004 5006
70003 2480.4 2012-10-10 3009 5003
70012 250.45 2012-06-27 3008 5002
70011 75.29 2012-08-17 3003 5007
70013 3045.6 2012-04-25 3002 5001
Editor is loading...
Leave a Comment