Resume SQL1 Pertemuan 2

SORTING DATA

The ORDER BY Keyword

The ORDER BY keyword is used to sort the result-set by a specified column.
The ORDER BY keyword sort the records in ascending order by default.
If you want to sort the records in a descending order, you can use the DESC keyword.

SQL ORDER BY Syntax

 

  

ORDER BY Example

The "Persons" table:

ORDER BY DESC Example

Now we want to select all the persons from the table above, however, we want to sort the persons descending by their last name.
We use the following SELECT statement:

The result-set will look like this:

The GROUP BY Statement

The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.

SQL GROUP BY Syntax

SQL GROUP BY Example

We have the following "Orders" table:


Now we want to find the total sum (total order) of each customer.
We will have to use the GROUP BY statement to group the customers.
We use the following SQL statement:

The result-set will look like this:

Nice! Isn't it? :)
Let's see what happens if we omit the GROUP BY statement:

The result-set will look like this:

The result-set above is not what we wanted.
Explanation of why the above SELECT statement cannot be used: The SELECT statement above has two columns specified (Customer and SUM(OrderPrice). The "SUM(OrderPrice)" returns a single value (that is the total sum of the "OrderPrice" column), while "Customer" returns 6 values (one value for each row in the "Orders" table). This will therefore not give us the correct result. However, you have seen that the GROUP BY statement solves this problem.

GROUP BY More Than One Column

We can also use the GROUP BY statement on more than one column, like this:

The HAVING Clause

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

SQL HAVING Syntax

 

SQL HAVING Example

We have the following "Orders" table:

Now we want to find if any of the customers have a total order of less than 2000.
We use the following SQL statement:

The result-set will look like this:

Now we want to find if the customers "Hansen" or "Jensen" have a total order of more than 1500.
We add an ordinary WHERE clause to the SQL statement:

The result-set will look like this:



STIKOM SURABAYA
Riko Dwi Christian
D3 - Manajemen Informatika