"h1sy@m l@zu@rdy" (^-^)

Resume SQL1 Pertemuan 2

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:


Membatasi BarisBaris
yang Dipilih„h Membatasi barisbarisyang dikembalikan dengan
menggunakan klausa WHERE :„h Klausa WHERE setelah klausa FROM.







Membatasi BarisBaris
yang Dipilih Anda bisa membatasi barisbaris yang dihasilkan dari query dengan menggunakan klausa WHERE.Suatu klausa WHERE berisi suatu kondisi yang harus terpenuhi, dan tepat setelah klausa FROM. Jika kondisinya benar, baris yang memenuhi kondisi dikembalikan.
Dalam sintak: WHERE membatasi query ke barisbaris yang memenuhi kondisi condition susunan nama nama kolom, ekspresiekspresi,konstantakonstanta dan operator pembanding
Klausa WHERE dapat membandingkan nilai nilai dalam kolomkolom,nilai nilai literal, ekspresiekspresi
aritmatika atau fungsifungsi (functions). Klausa WHERE terdiri dari 3 bagian :
 Nama kolom
 Kondisi pembanding
 Nama kolom, konstanta atau daftar nilai nilai







STIKOM SURABAYA
ABU HISAM PRATAMA
D3 - Manajemen Informatika