Untitled
05/02/2024 -------------------------------------------------------- group by: group by clause is used for grouping data. the group by clause groups rows that have same value. the group by clause is used with aggregate function(count(),min(),max(),avg(),sum()) syntax: select column() from tablename group by column; select count(*),salary from employeeinfo group by salary; select max(id),salary from employeeinfo group by salary; select min(id),salary from employeeinfo group by salary; select count(*),address from employeeinfo group by address; select min(salary),address from employeeinfo group by address; select count(*),avg(salary),address from employeeinfo group by address; ---- when we use where and group by statement in single query then the sequence is 1.where 2.group by select count(*),address from employeeinfo where salary>20000 group by address; ------------------------------------------------------------------------------------------------------- when we use group by clause,order by clause and where clause at a one time then the squence is 1 where 2 group by 3.order by select count(*),address from employeeinfo where salary>20000 group by address order by address ; assignment: From the following table, write a SQL query to count the number of cities in each country. Return country ID and number of cities. Sample table : locations +-------------+------------------------------------------+-------------+---------------------+-------------------+------------+ | LOCATION_ID | STREET_ADDRESS | POSTAL_CODE | CITY | STATE_PROVINCE | COUNTRY_ID | +-------------+------------------------------------------+-------------+---------------------+-------------------+------------+ | 1000 | 1297 Via Cola di Rie | 989 | Roma | | IT | | 1100 | 93091 Calle della Testa | 10934 | Venice | | IT | | 1200 | 2017 Shinjuku-ku | 1689 | Tokyo | Tokyo Prefecture | JP | | 1300 | 9450 Kamiya-cho | 6823 | Hiroshima | | JP | | 1400 | 2014 Jabberwocky Rd | 26192 | Southlake | Texas | US | | 1500 | 2011 Interiors Blvd | 99236 | South San Francisco | California | US | | 1600 | 2007 Zagora St | 50090 | South Brunswick | New Jersey | US | | 1700 | 2004 Charade Rd | 98199 | Seattle | Washington | US | | 1800 | 147 Spadina Ave | M5V 2L7 | Toronto | Ontario | CA | | 1900 | 6092 Boxwood St | YSW 9T2 | Whitehorse | Yukon | CA | | 2000 | 40-5-12 Laogianggen | 190518 | Beijing | | CN | | 2100 | 1298 Vileparle (E) | 490231 | Bombay | Maharashtra | IN | | 2200 | 12-98 Victoria Street | 2901 | Sydney | New South Wales | AU | | 2300 | 198 Clementi North | 540198 | Singapore | | SG | | 2400 | 8204 Arthur St | | London | | UK | | 2500 | Magdalen Centre, The Oxford Science Park | OX9 9ZB | Oxford | Oxford | UK | | 2600 | 9702 Chester Road | 9629850293 | Stretford | Manchester | UK | | 2700 | Schwanthalerstr. 7031 | 80925 | Munich | Bavaria | DE | | 2800 | Rua Frei Caneca 1360 | 01307-002 | Sao Paulo | Sao Paulo | BR | | 2900 | 20 Rue des Corps-Saints | 1730 | Geneva | Geneve | CH | | 3000 | Murtenstrasse 921 | 3095 | Bern | BE | CH | | 3100 | Pieter Breughelstraat 837 | 3029SK | Utrecht | Utrecht | NL | | 3200 | Mariano Escobedo 9991 | 11932 | Mexico City | Distrito Federal, | MX | +-------------+------------------------------------------+-------------+---------------------+-------------------+------------+ select count(CITY) , COUNTRY_ID from locations group by COUNTRY_ID ; ------------------------------------------------------------------------------------------------------------------------------- Q2. 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 | | | 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 | | | 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 | | 118 | Guy | Himuro | GHIMURO | 515.127.4565 | 2006-11-15 | PU_CLERK | 2600.00 | 0.00 | 114 | 30 | | 119 | Karen | Colmenares | KCOLMENA | 515.127.4566 | 2007-08-10 | PU_CLERK | 2500.00 | 0.00 | 114 | 30 | | 120 | Matthew | Weiss | MWEISS | 650.123.1234 | 2004-07-18 | ST_MAN | 8000.00 | 0.00 | 100 | 50 | | 121 | Adam | Fripp | AFRIPP | 650.123.2234 | 2005-04-10 | ST_MAN | 8200.00 | 0.00 | 100 | 50 | | 122 | Payam | Kaufling | PKAUFLIN | 650.123.3234 | 2003-05-01 | ST_MAN | 7900.00 | 0.00 | 100 | 50 | | 123 | Shanta | Vollman | SVOLLMAN | 650.123.4234 | 2005-10-10 | ST_MAN | 6500.00 | 0.00 | 100 | 50 | | 124 | Kevin | Mourgos | KMOURGOS | 650.123.5234 | 2007-11-16 | ST_MAN | 5800.00 | 0.00 | 100 | 50 | | 125 | Julia | Nayer | JNAYER | 650.124.1214 | 2005-07-16 | ST_CLERK | 3200.00 | 0.00 | 120 | 50 | | 126 | Irene | Mikkilineni | IMIKKILI | 650.124.1224 | 2006-09-28 | ST_CLERK | 2700.00 | 0.00 | 120 | 50 | | 127 | James | Landry | JLANDRY | 650.124.1334 | 2007-01-14 | ST_CLERK | 2400.00 | 0.00 | 120 | 50 | | 128 | Steven | Markle | SMARKLE | 650.124.1434 | 2008-03-08 | ST_CLERK | 2200.00 | 0.00 | 120 | 50 | | 129 | Laura | Bissot | LBISSOT | 650.124.5234 | 2005-08-20 | ST_CLERK | 3300.00 | 0.00 | 121 | 50 | | 130 | Mozhe | Atkinson | MATKINSO | 650.124.6234 | 2005-10-30 | ST_CLERK | 2800.00 | 0.00 | 121 | 50 | | +-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+ Q3.From the following table, write a SQL query to calculate the average salary of employees who receive a commission percentage for each department. Return department id, average salary. Sample table: employees +-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+ | EMPLOYEE_ID | FIRST_NAME | LAST_NAME | EMAIL | PHONE_NUMBER | HIRE_DATE | JOB_ID | SALARY | COMMISSION_PCT | MANAGER_ID | DEPARTMENT_ID | +-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+
Leave a Comment