SQLite SELECT Query
Introduction
The SELECT statement is used to make a simple query from a database or a complicated query against some criteria. A SELECT statement does not make any changes to the database.
A query or SELECT statement is a command which gives instructions to a database to produce certain information(s) from the table in its memory. The SELECT command starts with the keyword SELECT followed by a space and a list of comma separated columns. A * character can be used to select all the columns of a table. The table name comes after the FROM keyword and a white-space.
Syntax:
SELECT [ALL | DISTINCT] result [FROM table-list] [WHERE expr] [GROUP BY expr-list] [HAVING expr] [compound-op select]* [ORDER BY sort-expr-list] [LIMIT integer [(OFFSET|,) integer]]
Parameters:
| Clause | Operation perform | Input Value |
|---|---|---|
| WHERE | Used to restrict | Expression or condition |
| DISTINCT | Used to restrict | List of columns |
| FROM | Joins | List of tables |
| GROUP BY | Used to restrict | List of columns |
| ORDER BY | List of columns | |
| HAVING | Used to restrict | Expression or condition |
| LIMIT | Used to restrict | Integer value |
| OFFSET | Used to restrict | Integer value |
Here is the sample table employees.
Examples:
Retrieving all data:
If you want to retrieve all the rows from the locations table the following SQL can be used.
SELECT * FROM locations;
Relational Algebra Expression:
Relational Algebra Tree:
Here is the result.
sqlite> SELECT * FROM locations; location_id street_address postal_code city state_province country_id ----------- -------------- ----------- ---------- -------------- ---------- 1000 1297 Via Cola 989 Roma IT 1100 93091 Calle de 10934 Venice IT 1200 2017 Shinjuku- 1689 Tokyo Tokyo Prefectu JP 1300 9450 Kamiya-ch 6823 Hiroshima JP 1400 2014 Jabberwoc 26192 Southlake Texas US 1500 2011 Interiors 99236 South San California US 1600 2007 Zagora St 50090 South Brun New Jersey US 1700 2004 Charade R 98199 Seattle Washington US 1800 147 Spadina Av M5V 2L7 Toronto Ontario CA 1900 6092 Boxwood S YSW 9T2 Whitehorse Yukon CA 2000 40-5-12 Laogia 190518 Beijing CN 2100 1298 Vileparle 490231 Bombay Maharashtra IN 2200 12-98 Victoria 2901 Sydney New South Wale AU 2300 198 Clementi N 540198 Singapore SG 2400 8204 Arthur St London UK 2500 Magdalen Centr OX9 9ZB Oxford Oxford UK 2600 9702 Chester R 9629850293 Stretford Manchester UK 2700 Schwanthalerst 80925 Munich Bavaria DE 2800 Rua Frei Canec 01307-002 Sao Paulo Sao Paulo BR 2900 20 Rue des Cor 1730 Geneva Geneve CH 3000 Murtenstrasse 3095 Bern BE CH 3100 Pieter Breughe 3029SK Utrecht Utrecht NL 3200 Mariano Escobe 11932 Mexico Cit Distrito Feder MX
Here in the above example the * have been used to indicate ALL the records from the locations table.
if FROM clause not used:
An expression can only be executed:
sqlite> SELECT 6+15; 6+15 ---------- 21 or sqlite> SELECT 5*15; 5*15 ---------- 75 or sqlite> SELECT 5+2-3*4/6; 5+2-3*4/6 ---------- 5
Select specific columns:
The SELECT statement can be used to retrieve specific columns. The column names follow the SELECT word.
If you want to retrieve the columns street_address and city from the location table the following SQL can be used.
SELECT street_address, city FROM locations;
Relational Algebra Expression:
Relational Algebra Tree:
Here is the result.
sqlite> SELECT street_address, city FROM locations; street_address city -------------------------------------------------- ---------- 1297 Via Cola di Rie Roma 93091 Calle della Testa Venice 2017 Shinjuku-ku Tokyo 9450 Kamiya-cho Hiroshima 2014 Jabberwocky Rd Southlake 2011 Interiors Blvd South San 2007 Zagora St South Brun 2004 Charade Rd Seattle 147 Spadina Ave Toronto 6092 Boxwood St Whitehorse 40-5-12 Laogianggen Beijing 1298 Vileparle (E) Bombay 12-98 Victoria Street Sydney 198 Clementi North Singapore 8204 Arthur St London Magdalen Centre, The Oxford Science Park Oxford 9702 Chester Road Stretford Schwanthalerstr. 7031 Munich Rua Frei Caneca 1360 Sao Paulo 20 Rue des Corps-Saints Geneva Murtenstrasse 921 Bern Pieter Breughelstraat 837 Utrecht Mariano Escobedo 9991 Mexico Cit
Select columns using alias:
In the FROM list with a select statement, one or more table names can be included separated by a comma. The tables using followed by the FROM clause have an optional alias name that can be used to mention individual column names in the result.Where the table name in full can be used to qualify columns of a table, the aliases are not used.
sqlite> SELECT regions.region_name FROM regions; region_name ------------------------- REGION_NAME Europe Americas Asia Middle East and Africa or sqlite> SELECT z.region_name FROM regions z; region_name ------------------------- REGION_NAME Europe Americas Asia Middle East and Africa
Here in the above, the two SELECT statements are identical, the latter uses a table alias z for regions.
Renaming column names :
Sometimes we use the column name as short form against a keyword for that column. So the return value for that column will appear in short from, and sometimes we required to display it in full name. It can be done by using the AS clause followed the column name or any expression with the select statement.
If you want to retrieve the region_name column from the regions table with the customize column head "Name of the Region" the following SQL can be used. If the customize heading contain more than one words then it will be within a quote unquote unless it can be used independently.
Here is the example.
SELECT region_name AS "Name of the Region" FROM regions;
Here is the result.
Name of the Region ------------------------- REGION_NAME Europe Americas Asia Middle East and Africa
Here in the following example, the customize heading contain more than one words and it used without quote unquote, and this statement occurs an error.
sqlite> SELECT region_name AS Name of the Region FROM regions; Error: near "of": syntax error
Limit Output:
When we want to retrieve a limited amount of data from a large database we can use the LIMIT clause to limit the data amount returned by the statement.
If we want to retrieve the first ten records from the locations table, the following SQL can be used.
SELECT * FROM locations LIMIT 10;
Here is the result.
location_id street_address postal_code city state_province country_id ------------------------- -------------------- ----------- ---------- -------------- ---------- 1000 1297 Via Cola di Rie 989 Roma IT 1100 93091 Calle della Te 10934 Venice IT 1200 2017 Shinjuku-ku 1689 Tokyo Tokyo Prefectu JP 1300 9450 Kamiya-cho 6823 Hiroshima JP 1400 2014 Jabberwocky Rd 26192 Southlake Texas US 1500 2011 Interiors Blvd 99236 South San California US 1600 2007 Zagora St 50090 South Brun 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
If we want to retrieve five records skipping the first four from the locations table, the following SQL can be used.
SELECT * FROM locations LIMIT 4,5;
Here is the results.
sqlite> SELECT * FROM locations LIMIT 4,5; location_id street_address postal_code city state_province country_id ------------------------- ------------------- ----------- ---------- -------------- ---------- 1400 2014 Jabberwocky Rd 26192 Southlake Texas US 1500 2011 Interiors Blvd 99236 South San California US 1600 2007 Zagora St 50090 South Brun New Jersey US 1700 2004 Charade Rd 98199 Seattle Washington US 1800 147 Spadina Ave M5V 2L7 Toronto Ontario CA
Select using OFFSET clause:
The OFFSET clause after LIMIT specifies how many rows to skip at the beginning of the result set.
Here is the example.
SELECT * FROM locations LIMIT 5 OFFSET 4;
Here is the result is same as the previous result.
sqlite> SELECT * FROM locations LIMIT 5 OFFSET 4; location_id street_address postal_code city state_province country_id ------------------------- ------------------- ----------- ---------- -------------- ---------- 1400 2014 Jabberwocky Rd 26192 Southlake Texas US 1500 2011 Interiors Blvd 99236 South San California US 1600 2007 Zagora St 50090 South Brun New Jersey US 1700 2004 Charade Rd 98199 Seattle Washington US 1800 147 Spadina Ave M5V 2L7 Toronto Ontario CA
WHERE clause
WHERE is a powerful filter A WHERE clause can let you take exactly the piece of the data you want. It sets the conditions for the SELECT, and the query will return only those rows that match the conditions. It provides you with a great degree of control over the conditions to impose restrictions on the dataset returned by a SELECT with which to include (or exclude) rows in (or from) the result. It is used both to limit the number of rows returned and to indicate a relationship used to join two tables together.
If you only wanted to see cities from Canada, the following SQL can be used.
SELECT * FROM locations WHERE country_id='CA';
Relational Algebra Expression:
Relational Algebra Tree:
Here is the result for the above statement.
sqlite> SELECT * FROM locations WHERE country_id='CA'; location_id street_address postal_code city state_province country_id ----------- --------------- ----------- ---------- -------------- ---------- 1800 147 Spadina Ave M5V 2L7 Toronto Ontario CA 1900 6092 Boxwood St YSW 9T2 Whitehorse Yukon CA
Here some operators can be used with WHERE clause. Here are the operators below-
| operator | description |
| = | Equal |
| <> | Not equal* |
| > | Greater than |
| < | Less than |
| >= | Greater than or equal |
| <= | Less than or equal |
Here is a sample table agents.
AGENT_CODE AGENT_NAME WORKING_AREA COMMISSION PHONE_NO COUNTRY ---------- ---------- ------------ ---------- ------------ ---------- A007 Ramasundar Bangalore 0.15 077-25814763 A003 Alex London 0.13 075-12458969 A008 Alford New York 0.12 044-25874365 A011 Ravi Kumar Bangalore 0.15 077-45625874 A010 Santakumar Chennai 0.14 007-22388644 A012 Lucida San Jose 0.12 044-52981425 A005 Anderson Brisban 0.13 045-21447739 A001 Subbarao Bangalore 0.14 077-12346674 A002 Mukesh Mumbai 0.11 029-12358964 A006 McDen London 0.15 078-22255588 A004 Ivan Torento 0.15 008-22544166 A009 Benjamin Hampshair 0.11 008-22536178
If you only wanted to see agent_code, agent_name, and commission from the agents who gets the commission less than .15% , the following SQL can be used.
SELECT * FROM agents WHERE commission<.15;
Here is the result for the above statement.
sqlite> SELECT agent_code, agent_name, commission FROM agents WHERE commission<.15; AGENT_CODE AGENT_NAME COMMISSION ---------- ---------------------------------------- ---------- A003 Alex 0.13 A008 Alford 0.12 A010 Santakumar 0.14 A012 Lucida 0.12 A005 Anderson 0.13 A001 Subbarao 0.14 A002 Mukesh 0.11 A009 Benjamin 0.11
If you only wanted to see agent_code, agent_name, and commission from the agents who gets the commission more than .12% , the following SQL can be used.
SELECT agent_code, agent_name, commission
FROM agents
WHERE commission>.12;
Here is the result for the above statement.
sqlite> SELECT agent_code, agent_name, commission ...> FROM agents WHERE commission>.12; AGENT_CODE AGENT_NAME COMMISSION ---------- ---------- ---------- A007 Ramasundar 0.15 A003 Alex 0.13 A011 Ravi Kumar 0.15 A010 Santakumar 0.14 A005 Anderson 0.13 A001 Subbarao 0.14 A006 McDen 0.15 A004 Ivan 0.15
If you only want to see cities from Canada, the following SQL can be used.
SELECT *
FROM locations
WHERE country_id='CA';
Relational Algebra Expression:
Relational Algebra Tree:
Here is the result for the above statement.
sqlite> SELECT * FROM locations WHERE country_id='CA'; location_id street_address postal_code city state_province country_id ----------- --------------- ----------- ---------- -------------- ---------- 1800 147 Spadina Ave M5V 2L7 Toronto Ontario CA 1900 6092 Boxwood St YSW 9T2 Whitehorse Yukon CA
WHERE with AND and OR
You can using AND and OR with WHERE clause for filter the rows from the tables.
Here is the sample table employees:
If you want to see the employee_id, first_name, last_name, job_id, manager_id and departments who belongs to the department_id 80, the following SQL can be used.
SELECT employee_id, first_name, last_name,job_id, manager_id,department_id
FROM employees
WHERE department_id=80;
Relational Algebra Expression:
Relational Algebra Tree:
Here is the result.
Sample Output:
employee_id first_name last_name job_id manager_id department_id ----------- ---------- ---------- ---------- ---------- ------------- 145 John Russell SA_MAN 100 80 146 Karen Partners SA_MAN 100 80 147 Alberto Errazuriz SA_MAN 100 80 148 Gerald Cambrault SA_MAN 100 80 149 Eleni Zlotkey SA_MAN 100 80 150 Peter Tucker SA_REP 145 80 151 David Bernstein SA_REP 145 80 .........
Here in the another example, if you want to see the employee_id, first_name, last_name, job_id, manager_id and departments who belongs to the department_id 80, and must be under the manager whose ID is 100, the following SQL can be used.
SELECT employee_id, first_name, last_name,job_id, manager_id,department_id
FROM employees
WHERE department_id=80
AND manager_id=100;
Relational Algebra Expression:
Relational Algebra Tree:
Here is the result.
employee_id first_name last_name job_id manager_id department_id ----------- ---------- ---------- ---------- ---------- ------------- 145 John Russell SA_MAN 100 80 146 Karen Partners SA_MAN 100 80 147 Alberto Errazuriz SA_MAN 100 80 148 Gerald Cambrault SA_MAN 100 80 149 Eleni Zlotkey SA_MAN 100 80
Now, lets find those employees, whose job ID may be CLERK or may be in the department which ID is 80 and must be under the manager who is the owner of the ID 147.
Here is the statement.
SELECT employee_id, first_name, last_name,job_id, manager_id,department_id
FROM employees
WHERE job_id='SH_CLERK'
OR department_id=80
AND manager_id=147;
Relational Algebra Expression:
Relational Algebra Tree:
Here is the result of the above statement.
employee_id first_name last_name job_id manager_id department_id ----------- ---------- ---------- ---------- ---------- ------------- 162 Clara Vishney SA_REP 147 80 163 Danielle Greene SA_REP 147 80 164 Mattea Marvins SA_REP 147 80 165 David Lee SA_REP 147 80 166 Sundar Ande SA_REP 147 80 167 Amit Banda SA_REP 147 80 180 Winston Taylor SH_CLERK 120 50 181 Jean Fleaur SH_CLERK 120 50 182 Martha Sullivan SH_CLERK 120 50 183 Girard Geoni SH_CLERK 120 50 184 Nandita Sarchand SH_CLERK 121 50 185 Alexis Bull SH_CLERK 121 50 .........
The anove result shows that, the 'SA_REP' job_id also appear along with 'SH_CLERK', because the implimentation of OR clause and manager_id 147 appear for only those departments that have the ID 80.
The AND operator has a higher precedence than the OR operator, which means it gets evaluated first. So, in effect, the query really looks like the below:
SELECT employee_id, first_name, last_name,job_id, manager_id,department_id
FROM employees
WHERE job_id='SH_CLERK'
OR (department_id=80 AND manager_id=147);
Relational Algebra Expression:
Relational Algebra Tree:
Which selects all employees for 'SH_CLERK' and only those employees for department no 80 who have under the manager whose id is 147. The parentheses can be used to clarify the query and actually get the high rollers we wanted.
DISTINCT
The DISTINCT keyword is used to select only unique items from the result set or it can be said that, it eliminates the duplicates rows from the result of the query.
If you want to get the unique department_id and manager_id combination , the following SQL can be used.
SELECT DISTINCT department_id, manager_id
FROM employees;
Relational Algebra Expression:
Relational Algebra Tree:
Here is the result.
department_id manager_id
------------- ----------
90
90 100
60 102
60 103
100 101
100 108
30 100
30 114
50 100
50 120
50 121
50 122
50 123
50 124
80 100
80 145
80 146
80 147
80 148
80 149
149
10 101
20 100
20 201
40 101
70 101
110 101
110 205
The above result shows only the unique combination of department_id and manager_id have appeared. Now, if we add the job_id column with above query, look what will happen.
SELECT DISTINCT department_id, manager_id,job_id
FROM employees;
Relational Algebra Expression:
Relational Algebra Tree:
Here is the output.
department_id manager_id job_id
------------- ---------- ----------
90 AD_PRES
90 100 AD_VP
60 102 IT_PROG
60 103 IT_PROG
100 101 FI_MGR
100 108 FI_ACCOUNT
30 100 PU_MAN
30 114 PU_CLERK
50 100 ST_MAN
50 120 ST_CLERK
50 121 ST_CLERK
50 122 ST_CLERK
50 123 ST_CLERK
50 124 ST_CLERK
80 100 SA_MAN
80 145 SA_REP
80 146 SA_REP
80 147 SA_REP
80 148 SA_REP
80 149 SA_REP
149 SA_REP
50 120 SH_CLERK
50 121 SH_CLERK
50 122 SH_CLERK
50 123 SH_CLERK
50 124 SH_CLERK
10 101 AD_ASST
20 100 MK_MAN
20 201 MK_REP
40 101 HR_REP
70 101 PR_REP
110 101 AC_MGR
110 205 AC_ACCOUNT
Here in the above result, the unique combination made by three columns and a naturally number of rows become changed, and here more rows appear than before.
ORDER BY
The ORDER BY clause is used to sort the returned data set. The ORDER BY clause is followed by the column on which we do the sorting. The default direction for ORDER BY is ascending; results are ordered from smallest amount to greatest. To specify the direction of the ORDER BY, use the DESC or ASC keyword:
If you want to sort the result according to salary for those employees which department no. is below 50, the following SQL can be used.
SELECT employee_id, first_name, last_name,job_id, salary,department_id
FROM employees
WHERE department_id<50
ORDER BY salary;
Relational Algebra Expression:
Relational Algebra Tree:
Here is the result.
employee_id first_name last_name job_id salary department_id ----------- ---------- ---------- ---------- ---------- ------------- 119 Karen Colmenares PU_CLERK 2500 30 118 Guy Himuro PU_CLERK 2600 30 117 Sigal Tobias PU_CLERK 2800 30 116 Shelli Baida PU_CLERK 2900 30 115 Alexander Khoo PU_CLERK 3100 30 200 Jennifer Whalen AD_ASST 4400 10 202 Pat Fay MK_REP 6000 20 203 Susan Mavris HR_REP 6500 40 114 Den Raphaely PU_MAN 11000 30 201 Michael Hartstein MK_MAN 13000 20
If you want to sort the result according to salary in descending order for those employees which department no. is below 50 , the following SQL can be used.
SELECT employee_id, first_name, last_name,job_id, salary,department_id
FROM employees
WHERE department_id<50
ORDER BY salary DESC;
Relational Algebra Expression:
Relational Algebra Tree:
Here is the result.
employee_id first_name last_name job_id salary department_id ----------- ---------- ---------- ---------- ---------- ------------- 201 Michael Hartstein MK_MAN 13000 20 114 Den Raphaely PU_MAN 11000 30 203 Susan Mavris HR_REP 6500 40 202 Pat Fay MK_REP 6000 20 200 Jennifer Whalen AD_ASST 4400 10 115 Alexander Khoo PU_CLERK 3100 30 116 Shelli Baida PU_CLERK 2900 30 117 Sigal Tobias PU_CLERK 2800 30 118 Guy Himuro PU_CLERK 2600 30 119 Karen Colmenares PU_CLERK 2500 30
The result of the query can be ordered by more than one column. Rows with the same value for the first column of the ORDER BY are further ordered by the additional column(s):
Here is the example.
If you want to order the result firstly by department_id column in ascending order and after that, which departments are same they will be ordered themselves descendingly according to the salary, the following SQL can be used.
SELECT employee_id, first_name, last_name,job_id, salary,department_id
FROM employees
WHERE department_id<50
ORDER BY department_id,salary DESC;
Relational Algebra Expression:
Relational Algebra Tree:
Here is the result.
employee_id first_name last_name job_id salary department_id ----------- ---------- ---------- ---------- ---------- ------------- 200 Jennifer Whalen AD_ASST 4400 10 201 Michael Hartstein MK_MAN 13000 20 202 Pat Fay MK_REP 6000 20 114 Den Raphaely PU_MAN 11000 30 115 Alexander Khoo PU_CLERK 3100 30 116 Shelli Baida PU_CLERK 2900 30 117 Sigal Tobias PU_CLERK 2800 30 118 Guy Himuro PU_CLERK 2600 30 119 Karen Colmenares PU_CLERK 2500 30 203 Susan Mavris HR_REP 6500 40
It seems to the above result that, depertment_id 20 and 30 have more than one rows, and they have been arranged descendingly according to the salary, that is the greatest salary will come first.
ORDER BY with DISTINCT
If you want to get the unique department_id, manager_id and job_id combination by an order according to department ascendingly, the following SQL can be used.
SELECT DISTINCT department_id, manager_id,job_id
FROM employees ORDER BY department_id;
Relational Algebra Expression:
Relational Algebra Tree:
Here is the output.
department_id manager_id job_id
------------- ---------- ----------
10 101 AD_ASST
20 100 MK_MAN
20 201 MK_REP
30 100 PU_MAN
30 114 PU_CLERK
40 101 HR_REP
50 100 ST_MAN
50 120 ST_CLERK
50 121 ST_CLERK
50 122 ST_CLERK
50 123 ST_CLERK
50 124 ST_CLERK
50 120 SH_CLERK
50 121 SH_CLERK
50 122 SH_CLERK
50 123 SH_CLERK
50 124 SH_CLERK
60 102 IT_PROG
60 103 IT_PROG
70 101 PR_REP
80 100 SA_MAN
80 145 SA_REP
80 146 SA_REP
80 147 SA_REP
80 148 SA_REP
80 149 SA_REP
90 AD_PRES
90 100 AD_VP
100 101 FI_MGR
100 108 FI_ACCOUNT
110 101 AC_MGR
110 205 AC_ACCOUNT
149 SA_REP
GROUP BY
The GROUP BY clause is used to combine database records with identical values into a single record. It is often used with the aggregation functions.
The GROUP BY clause is used to aggregate data into a single row where the value of one or more specified columns is repeated. It is used to combine database records with identical values into a single record. It is often used with the aggregation functions. The GROUP BY clause takes a list of expressions, usually column names from the result, and aggregates data for each expression. The job of the GROUP BY clause is to compute aggregates over an entire result, and can also split that result into groups of rows with specified values, and compute aggregates on each group, in a single step.
The GROUP BY clause sits in between the WHERE clause and the SELECT clause. It gets the output from WHERE and splits it into groups of rows that share a common value (or values) for a specific column (or columns). These groups are then passed to the SELECT clause.
The grouping process performed by two steps. First, the GROUP BY expression arranged the table rows into different groups. Once the groups are defined, the SELECT header defines how those groups are flattened down into a single row. The resulting table will have one row for each group.
If you want to find the total salary for each job_id, the following SQL can be used.
SELECT job_id as Designation ,SUM(salary) "Total Salary"
FROM employees
GROUP BY job_id;
Here is the result
Designation Total Salary ----------- ------------ AC_ACCOUNT 8300 AC_MGR 12000 AD_ASST 4400 AD_PRES 24000 AD_VP 34000 FI_ACCOUNT 39600 FI_MGR 12000 HR_REP 6500 IT_PROG 28800 MK_MAN 13000 MK_REP 6000 PR_REP 10000 PU_CLERK 13900 PU_MAN 11000 SA_MAN 61000 SA_REP 250500 SH_CLERK 64300 ST_CLERK 55700 ST_MAN 36400
If you want to find the total number of employees against each designation, the following SQL can be used.
SELECT job_id as Designation ,COUNT(*) "Number of Employees"
FROM employees
GROUP BY job_id;
Here is the result
Designation Number of Employees ----------- ------------------- AC_ACCOUNT 1 AC_MGR 1 AD_ASST 1 AD_PRES 1 AD_VP 2 FI_ACCOUNT 5 FI_MGR 1 HR_REP 1 IT_PROG 5 MK_MAN 1 MK_REP 1 PR_REP 1 PU_CLERK 5 PU_MAN 1 SA_MAN 5 SA_REP 30 SH_CLERK 20 ST_CLERK 20 ST_MAN 5
GROUP BY with WHERE
If you want to find the total salary for each job_id which belongs to the department that ID number is below 80 , the following SQL can be used.
SELECT job_id as Designation ,SUM(salary) "Total Salary"
FROM employees
WHERE department_id<80
GROUP BY job_id;
Here is the result
Designation Total Salary ----------- ------------ AD_ASST 4400 HR_REP 6500 IT_PROG 28800 MK_MAN 13000 MK_REP 6000 PR_REP 10000 PU_CLERK 13900 PU_MAN 11000 SH_CLERK 64300 ST_CLERK 55700 ST_MAN 36400
GROUP BY and ORDER BY
If you want to find the total number of employees against each designation by an order of highest to the smallest number of employees, the following SQL can be used.
SELECT job_id as Designation ,COUNT(*) "Number of Employees"
FROM employees
GROUP BY job_id
ORDER BY COUNT(*) DESC;
Here is the result
Designation Number of Employees ----------- ------------------- SA_REP 30 SH_CLERK 20 ST_CLERK 20 FI_ACCOUNT 5 IT_PROG 5 PU_CLERK 5 SA_MAN 5 ST_MAN 5 AD_VP 2 AC_ACCOUNT 1 AC_MGR 1 AD_ASST 1 AD_PRES 1 FI_MGR 1 HR_REP 1 MK_MAN 1 MK_REP 1 PR_REP 1 PU_MAN 1
HAVING
The HAVING clause functionally is identical to the WHERE clause. It is used to restrict records based on the summary value of a grouping. .HAVING clauses should only contain filter expressions that depend on the GROUP BY output. The main difference between the WHERE clause and the HAVING clause is, the HAVING clause is processed after the GROUP BY and SELECT clauses, allowing to filter rows based on the results of any GROUP BY aggregate while the WHERE clause can only reference expressions that do not contain aggregate functions.
Example:
If you want to find the total number of employees against the designations which contain on or above 5 employees, the following SQL can be used.
SELECT job_id as Designation ,COUNT(*) "Number of Employees"
FROM employees
GROUP BY job_id
HAVING COUNT(*)>=5;
Here is the result
Designation Number of Employees ----------- ------------------- FI_ACCOUNT 5 IT_PROG 5 PU_CLERK 5 SA_MAN 5 SA_REP 30 SH_CLERK 20 ST_CLERK 20 ST_MAN 5
HAVING with ORDER BY
If you want to find the total number of employees against the designations which contain on or above 5 employees, and the result comes by an order of largest to samalles employee number, the following SQL can be used.
SELECT job_id as Designation ,COUNT(*) "Number of Employees"
FROM employees
GROUP BY job_id
HAVING COUNT(*)>=5
ORDER BY COUNT(*) DESC;
Here is the result
Designation Number of Employees ----------- ------------------- SA_REP 30 SH_CLERK 20 ST_CLERK 20 FI_ACCOUNT 5 IT_PROG 5 PU_CLERK 5 SA_MAN 5 ST_MAN 5
HAVING with WHERE
If you want to find the total salary required for a month, which is on or above one lacs, for the designation that holding the ID is below 80, by an order of largest to smallest salary the following SQL can be used.
SELECT job_id as Designation ,SUM(salary) "Total Salary"
FROM employees
WHERE department_id<80
GROUP BY job_id
HAVING SUM(salary)>=10000
ORDER BY SUM(salary);
Here is the result
Designation Total Salary ----------- ------------ PR_REP 10000 PU_MAN 11000 MK_MAN 13000 PU_CLERK 13900 IT_PROG 28800 ST_MAN 36400 ST_CLERK 55700 SH_CLERK 64300
Previous:
Create, Alter, Drop index
Next:
SQLite Operators Introduction
- New Content published on w3resource:
- HTML-CSS Practical: Exercises, Practice, Solution
- Java Regular Expression: Exercises, Practice, Solution
- Scala Programming Exercises, Practice, Solution
- Python Itertools exercises
- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework
- Angular - JavaScript Framework
- Vue - JavaScript Framework
- Jest - JavaScript Testing Framework