SQL basic queries part 3
Sistem Basis Data
Rizka Wakhidatus Sholikah
Outline
• Aggregate function
• Grouping Data
• HAVING
• Subqueries
• Relational Set Operators
www.its.ac.id INSTITUT TEKNOLOGI SEPULUH NOPEMBER, Surabaya - Indonesia
Aggregate function
• Aggregate function performs a calculation on a set of values and
return a single value
• Aggregate function ignore null values
• They are often used with the GROUP BY clause of the SELECT
statement
www.its.ac.id INSTITUT TEKNOLOGI SEPULUH NOPEMBER, Surabaya - Indonesia
Aggregate function
• There are several aggregate function supporting by DBMS
www.its.ac.id INSTITUT TEKNOLOGI SEPULUH NOPEMBER, Surabaya - Indonesia
COUNT
• COUNT function is used to tally the number of non-null values of an
attribute.
• Syntax
SELECT COUNT (column) FROM table;
• COUNT can also be used with DISTINCT clause
www.its.ac.id INSTITUT TEKNOLOGI SEPULUH NOPEMBER, Surabaya - Indonesia
MIN MAX
• MIN and MAX functions help to find the highest and lowest value
from an attribute
• The MIN and MAX function can also be used with date columns
• Syntax
SELECT MIN(column) FROM table
www.its.ac.id INSTITUT TEKNOLOGI SEPULUH NOPEMBER, Surabaya - Indonesia
SUM
• SUM function computes the total sum for any specified numeric
attribute, using any condition(s)
• Syntax
SELECT SUM(column) FROM table
www.its.ac.id INSTITUT TEKNOLOGI SEPULUH NOPEMBER, Surabaya - Indonesia
AVG
• AVG function format is similar to SUMand is subject to the same
operating restrictions
• Syntax
SELECT AVG(column) FROM table
www.its.ac.id INSTITUT TEKNOLOGI SEPULUH NOPEMBER, Surabaya - Indonesia
Grouping data
• Rows can be grouped into smaller collection quickly and easily using
GROUP BY clause
• Syntax
SELECT columnlist
FROM tablelist
[WHERE conditionlist ]
[GROUP BY columnlist ]
[ORDER BY columnlist [ASC | DESC] ];
www.its.ac.id INSTITUT TEKNOLOGI SEPULUH NOPEMBER, Surabaya - Indonesia
HAVING
• HAVING clause is used because the WHERE keyword cannot be used with
aggregate functions
• This is because the WHERE clause is execute before GROUP BY
• Syntax
SELECT columnlist
FROM tablelist
[WHERE conditionlist ]
[GROUP BY columnlist ]
[HAVING conditionlist ]
[ORDER BY columnlist [ASC | DESC] ];
www.its.ac.id INSTITUT TEKNOLOGI SEPULUH NOPEMBER, Surabaya - Indonesia
HAVING
• Example:
• Suppose that you want to generate a listing of the number of products in
the inventory supplied by each vendor
• However, this time you want to limit the listing to products whose prices average
less than $10
• The query
SELECT V_CODE, COUNT(P_CODE) AS NUMPRODS
FROM PRODUCT
GROUP BY V_CODE
HAVING AVG(P_PRICE) < 10
ORDER BY V_CODE;
www.its.ac.id INSTITUT TEKNOLOGI SEPULUH NOPEMBER, Surabaya - Indonesia
Subqueries
• When retrieving the data, it is often necessary to process data
based on other processed data
• Subquery is a query (SELECT statement) that is embedded or nested
inside another query
• Subquery == nested query == inner query
• The first query in the SQL statement is known as the outer query
• The query inside the SQL statement is known as the inner query
• The inner query is executed first
• The output of inner query is used as the input for the outer query
www.its.ac.id INSTITUT TEKNOLOGI SEPULUH NOPEMBER, Surabaya - Indonesia
Subqueries
• A subquery can return one or more values:
• One single value (one column and one row)
• This subquery is used anywhere a single value is expected, as in the right side of a
comparison expression
• A list of values (one column and multiple rows)
• This type of subquery is used anywhere a list of values is expected, such as when using
the IN clause
• A virtual table (multi columns and multi rows)
• This type of subquery can be
used anywhere a table is expected, such as when using the FROM clause
www.its.ac.id INSTITUT TEKNOLOGI SEPULUH NOPEMBER, Surabaya - Indonesia
WHERE Subqueries
• The most common type of subquery uses an inner SELECT subquery
on the right side of a WHERE comparison expression
• Example:
• Find all products with a price greater than or equal to the average
product price
• Query
SELECT P_CODE, P_PRICE
FROM PRODUCT
WHERE P_PRICE >= (SELECT AVG(P_PRICE) FROM PRODUCT)
www.its.ac.id INSTITUT TEKNOLOGI SEPULUH NOPEMBER, Surabaya - Indonesia
HAVING Subqueries
• The HAVING clause is used to restrict the output f a GROUP BY query by
applying conditional criteria to the grouped rows
• Example:
• List all product with a total quantity sold greater than the average
quantity sold
• Query
SELECT P_CODE, SUM(LINE_UNITS) AS TOTALUNITS
FROM LINE
GROUP BY P_CODE
HAVING SUM(LINE_UNITS) > (SELECT AVG(LINE_UNITS) FROM LINE);
www.its.ac.id INSTITUT TEKNOLOGI SEPULUH NOPEMBER, Surabaya - Indonesia
LATIHAN
1. Tampilkan desc tugas, nama mata kuliah, dan nama dosen dari
tugas yang paling banyak dikerjakan oleh mahasiswa
2. Tampilkan nama mata kuliah dan nama dosen yang memiliki
paling banyak jenis tugas
3. Tampilkan desc tugas, nama mata kuliah dan nama dosen dari
tugas yang memiliki rata-rata nilai 70 sampai 80
4. Tampilkan nama mahasiswa, NRP, dan nilai yang memiliki nilai
dibawah rata-rata pada tugas ‘%car%’.
5. Tampilkan desc tugas, nama mata kuliah dan nama dosen yang
memiliki nilai rata-rata dibawah rata-rata nilai semua tugas
www.its.ac.id INSTITUT TEKNOLOGI SEPULUH NOPEMBER, Surabaya - Indonesia
Thank you
