konsep teknologi informasi bsyefani.staff.gunadarma.ac.id/downloads/files/53216/...sql –operator...

K O N S E P S I S T E M I N F O R M A S I B

P E RT E M U A N 3 - S Q L Q U E RY

OBJECTIVES

• Struktur SQL Query – Pertemuan M3

• Operator Aritmatika

• Penggunaan Kolom Alias

• Operator pembanding & Operasi Himpunan

• Operator Boolean

• Pencarian String

• Penggunaan Distinct

• Fungsi Agregat

SQL – OPERATOR ARITMATIKA


• Pada ekspresi SQL dengan tipe data Number dan Date dapat digunakan operator

aritmatika.

Operator Precedence Deskripsi

+ Tambah

- Kurang

* Kali

/ Bagi


• Contoh: (jika memiliki kolom pada tabel employees) seperti di bawah ini


• Contoh:

Jika menggunakan statement :

SELECT LAST_NAME, SALARY, SALARY+300

FROM EMPLOYEES;


• Contoh: (Hasil)


• Contoh 2 : (Jika menggunakan dua operator sekaligus)

SELECT LAST_NAME, SALARY, 12*SALARY+100

FROM EMPLOYEES;

SELECT LAST_NAME, SALARY, 12*(SALARY+100)

FROM EMPLOYEES;


• Contoh 2 : Hasil Statement 1


• Contoh 2 : Hasil Statement 2


• Contoh 2 : Hasil Berbeda karena USING PARENTHESES

SQL ALIASES

SQL – ALIASES

• SQL aliases are used to temporarily rename a table or a column

heading.

• SQL aliases are used to give a database table, or a column in a table,

a temporary name.

• Basically aliases are created to make column names more readable.

SQL – ALIASES

• SQL Alias Syntax for Columns

SELECT column_nameAS alias_name

FROM table_name;

SQL – ALIASES

• Contoh: Kasus 1

• (Jika ingin membuat last_name menjadi name, dan

commission_pct menjadi comm)

SQL – ALIASES

• Contoh:

SELECT last_name AS name,

commission_pct AS comm FROM employees;

SQL – ALIASES

• Contoh: Hasil (Jika ingin membuat last_name menjadi name, dan

commission_pct menjadi comm)

SQL – ALIASES

• Contoh 2: Kasus 2

• (Jika ingin membuat last_name menjadi name, dan kolom salary

dikalikan 12 menjadi Annual Salary)

SQL – ALIASES

• Contoh 2: (Jika ingin membuat last_name menjadi name, dan

kolom salary dikalikan 12 menjadi Annual Salary)

SELECT last_name “Name”, salary*12 “Annual Salary”

FROM employees;

SQL – ALIASES

• Contoh 2: Hasil (Jika ingin membuat last_name menjadi name,

dan kolom salary dikalikan 12 menjadi Annual Salary)

SQL – ALIASES

• SQL Alias Syntax for Tables

SELECT column_name(s) FROM table_name AS

alias_name;

SQL – ALIASES

• Contoh for Table Columns:

• The following SQL Statement selects all the orders from the

Customer with CustomerID=4 (Around the Horn).

• We use the “Customers” and “Orders” tables, and give them

tables aliases of “c” and “o” respectively (Here we have used

aliases to make the SQL shorter).

SQL – ALIASES

• Tabel Customers

CustomerI

D

CustomerNam

e

ContactNam

e

Address City PostalCode Country

2 Ana Trujillo

Emparedados y

helados

Ana Trujillo Avda. de la

Constitución

2222

México D.F. 05021 Mexico

3 Antonio

Moreno

Taquería

Antonio

Moreno

Mataderos 2312 México D.F. 05023 Mexico

4 Around the

Horn

Thomas

Hardy

120 Hanover

Sq.

London WA1 1DP UK

SQL – ALIASES

• Tabel Orders

OrderID CustomerID EmployeeID OrderDate ShipperID

10354 58 8 1996-11-14 3

10355 4 6 1996-11-15 1

10356 86 6 1996-11-18 2

SQL – ALIASES

• Contoh Statement: (with aliases)

SELECT o.OrderID, o.OrderDate, c.CustomerName

FROM Customers AS c, Orders AS o

WHERE c.CustomerName="Around the Horn" AND

c.CustomerID=o.CustomerID;

SQL – ALIASES

• Contoh Statement: (without aliases)

SELECT Orders.OrderID, Orders.OrderDate,

Customers.CustomerName

FROM Customers, Orders

WHERE Customers.CustomerName="Around the Horn"

AND Customers.CustomerID=Orders.CustomerID;

SQL SELECT DISTINCT

SQL SELECT DISTINCT

• The SELECT DISTINCT statement is used to return only distinct(diferent) values.

• In a table, a column may contain many duplicate values; and sometimesyou only want to list the different (distinct) values.

• The DISTINCT keyword can be used to return only distinct (different)values.

SQL SELECT DISTINCT

• SQL SELECT DISTINCT Syntax

SELECT DISTINCT column_name,

column_name FROM table_name;

SQL SELECT DISTINCT

• Contoh : (Kasus 1)

SQL SELECT DISTINCT


• The following SQL statement selects only the distinct values from

the "City" columns from the "Customers" table

SQL SELECT DISTINCT

• Contoh : (Kasus 2) Tabel Customer

CustomerID CustomerName ContactName Address City PostalCode Country

1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany

2 Ana Trujillo

Emparedados y

helados


Constitución 2222


3 Antonio Moreno

Taquería

Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico

4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK

5 Berglunds snabbköp Christina Berguvsvägen 8 Luleå S-958 22 Sweden

SQL SELECT DISTINCT


SELECT DISTINCT City FROM

Customers;

SQL – Operasi Pembanding

SQL – OPERATOR PEMBANDING

• Berikut ini Operator pembanding dalam SQL

Operator Meaning

= Equal to

> Greater than

>= Greater than or Equal to

< Less than

<= Less than or equal to

<> Not Equal to


• Contoh: (Jika ingin menampilkan salary kurang dari sama dengan

3000

Last_name salary

Matos 2600

Vargas 2500

Jose 3100

Santos 3500

Marquez 3100


• Contoh: Statement

SELECT last_name, salary FROM

employees WHERE salary <= 3000;


• Contoh: Hasil


• Operator Pembanding Kondisi lainnya (Other Comparison

Conditions)

Operator Meaning

BETWEEN … AND …. Between two values (inclusive)

IN (set) Match any of a list of values

LIKE Match a character pattern

IS NULL Is a null value

SQL BETWEEN


• The SQL BETWEEN Operator

• The BETWEEN operator selects values within a range.

• The values can be numbers, text, or dates.


• The SQL BETWEEN Syntax

SELECT column_name(s)

FROM table_name

WHERE column_name BETWEEN value1AND value2;


• Example ofThe SQL BETWEEN

• Below is a selection from the ‘Products’ table

ProductI

D

ProductName SupplierID CategoryI

D

Unit Price

1 Chais 1 1 10 boxes x 20BAGS 18

2 Chang 1 1 24 - 12 oz bottles 19

3 Aniseed Syrup 1 2 12 - 550 ml bottles 10

4 Chef Anton's Cajun Seasoning 1 2 48 - 6 oz jars 22

5 Chef Anton's Gumbo Mix 1 2 36 boxes 21.35

http://www.w3schools.com/sql/sql_between.asp#36501312



• Kasus :

• The following SQL statement selects all products with a price

BETWEEN 10 and 20



• Answer:

SELECT * FROM Products

WHERE Price BETWEEN 10 AND 20;



• Kasus :

• Bagaimana untuk menampilkan products diluar dari range 10 dan 20

??



• Answer:


WHERE Price NOT BETWEEN 10 AND 20;



• Kasus :

• The following SQL statement selects all products with a price

BETWEEN 10 and 20, but products with a CategoryID of 1,2, or 3

should not be displayed?



• Answer:


WHERE (Price BETWEEN 10 AND 20)

AND NOT CategoryID IN (1,2,3);



• Kasus:

• The following SQL statement selects all products with a

ProductName beginning with any of the letter BETWEEN ‘C’ and

‘M’?



• Answer:


WHERE ProductName BETWEEN 'C' AND 'M';

SQL IN

SQL IN

• The IN operator allows you to specify multiple values in a WHERE

clause.

• SQL IN Syntax


FROM table_name

WHERE column_name IN (value1,value2,...);

SQL IN

• Example : Below is a selection from the ‘Customers’ table

CustomerI

D

CustomerName ContactName Address City PostalCode Country


2 Ana Trujillo

Emparedados y helados


Constitución 2222


3 Antonio Moreno

Taquería

Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico


5 Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden

SQL IN

• Case:

• The Following SQL Statement selects all customers with a

City of ‘London’ or ‘Berlin’?

SQL IN

• Answer:

SELECT * FROM Customers

WHERE City IN ('London‘, ‘Berlin’);

SQL LIKE Operator

SQL LIKE

• The LIKE operator is used in a WHERE clause to search for a

specified patern in a column.

• Gunakan kondisi LIKE untuk melakukan pencarian sebagian nilai

string.

• Kondisi pencarian dapat menggunakan symbol karakter berikut:

• % : Menunjukkan nol/kosong atau sembarang beberapa karakter.

• _ : menunjukkan sembarang 1 karakter

SQL LIKE

• The SQL LIKE Syntax


FROM table_name

WHERE column_name LIKE pattern;

SQL LIKE

• Example:

SQL LIKE

• Example:

• Kasus 1:

• Bagaimana menampilkan first_nama yang memiliki huruf depan S?

SQL LIKE

• Example:

• Answer:

SQL LIKE

• Example:

• Kasus 2

• Bagaimana menampilkan nama terakhir yang huruf keduanya

mengandung huruf o?

SQL LIKE

• Example:

• Answer:

SQL LIKE

• Exercise:CustomerID CustomerName ContactName Address City PostalCode Country


2 Ana Trujillo

Emparedados y

helados


Constitución

2222


3 Antonio Moreno

Taquería

Antonio

Moreno

Mataderos 2312 México D.F. 05023 Mexico


5 Berglunds

snabbköp

Christina

Berglund

Berguvsvägen 8 Luleå S-958 22 Sweden

SQL LIKE

• Exercise:

1. How to selects all customers with a City starting with the letter “s”?

2. How to selects all customers with a City ending with the letter “s”?

3. How to selects all customers with a Country containing the pattern

“land”?

4. How to selects all customers with Country NOT containing the

pattern “land’?

SQL – USING THE NULL CONDITIONS

• Example:

SQL Logical Conditions

LOGICAL CONDITIONS

• The AND, OR, & NOT operators are used to filter records based

on more than one condition.

Operator Arti

AND Returns TRUE, jika kedua kondisi adalah TRUE

OR Returns TRUE, jika salah satu kondisi adalah TRUE

NOT Returns TRUE, jika kondisi tersebut adalah False

LOGICAL CONDITIONS

• The SQL AND & OR Operators

• The AND operator display a record if both the first condition

AND the second condition are true.

• The OR operator displays a record if either the first condition OR

the second condition is true.

LOGICAL CONDITIONS

• Example : Below is a selection from the “Customers” table

Custom

erID

CustomerName ContactName Address City PostalCode Country


2 Ana Trujillo Emparedados y

helados

Ana Trujillo Avda. de la Constitución

2222


3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico


5 Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden

LOGICAL CONDITIONS

• Example :

• Kasus 1

• How to Selects all customers from country “Germany”

AND the city “Berlin”, in the Customers table?

LOGICAL CONDITIONS

• Example :

• Answer 1


WHERE Country='Germany'

AND City='Berlin';

LOGICAL CONDITIONS

• Example :

• Kasus 2

• How to selects all customers from the city ‘Berlin” OR

“Munchen”, in the Customers table?

LOGICAL CONDITIONS

• Example :

• Answer 2


WHERE City='Berlin'

OR City='München';

LOGICAL CONDITIONS

• Example 3:

• Kasus 3

• How to selects all customers from the country “Germany”

AND the city must be equal to “Berlin” OR “Munchen”, in

the Customers table?

LOGICAL CONDITIONS

• Example 3:

• Answer 3


WHERE Country='Germany'

AND (City='Berlin' OR City='München');

LOGICAL CONDITIONS

• Using the NOT Operator

LOGICAL CONDITIONS

• Hasil:

SQL – OPERASI PEMBANDING

• Rules of Precedence

Order Evaluated Operators

1 Arithmetic Operators

2 Concatenation operator

3 Comparison conditions

4 IS [NOT] NULL, LIKE, [NOT] IN

5 [NOT] BETWEEN

6 NOT logical condition

7 AND logical condition

8 OR logical condition

SQL – OPERASI PEMBANDING

• Example of Rules of Precedence

FUNGSI AGREGAT

FUNGSI AGREGAT

• Fungsi Agregat dalam SQL adalah fungsi yang menerima kumpulan

atau koleksi dan mengembalikan nilai tunggal sebagai hasilnya,

seperti: Jumlah data, nilai minimum, nilai maximum dan nilai rata-

rata.

• Fungsi ini digunakan bersama dengan pernyataan SELECT.

JENIS FUNGSI AGREGAT

• Standar ISO mendefinisikan lima jenis fungsi agregat, yaitu:

Fungsi Penjelasan

SUM Digunakan untuk menghitung total nilai dari kolom tertentu

COUNT Digunakan untuk menghitung jumlah record

AVG Digunakan untuk menampilkan nilai rata-rata dari suatu kolom

MAX Digunakan untuk menampilkan nilai tertinggi dari suatu kolom

MIN Digunakan untuk menampilkan nilai terendah dari suatu kolom


• Example :

• Berikut ini adalah latihan dalam menggunakan fungsi agregat.

• Buatlah tabel buku dengan struktur tabel sebagai berikut : -> Next

Slide


• Example :


• Example : Kita lihat deskripsi dari tabel buku.


• Example : Masukkan data sebagai berikut


• Example : Jika ditampilkan dari tabel buku

mysql> SELECT * FROM buku;+-----------+------------------------+------------------+---------------+-------+-----------+------+----------------+| kode_buku | judul_buku | pengarang | penerbit | tahun | kategori | harga| tgl_inventaris |+-----------+------------------------+------------------+---------------+-------+-----------+------+----------------+| B0001 | Harry Potter | JK Rowling | British Press | 2013 | Fiksi | 50000| 2015-03-01 || B0002 | Sistem Basis Data | Abdul Kadir | Andi Offset | 2013 | Buku Teks | 40000| 2015-03-01 || B0003 | Sistem Basis Data | Fathansyah | ITB Press | 2013 | Buku Teks | 30000| 2015-03-01 || B0004 | Prophet Muhammad | Amir Abdullah | Madina Press | 2014 | Biografi | 45000| 2015-03-01 || B0005 | Ketika Cinta Bertasbih | Habiburahaman ES | Madina Press | 2014 | Fiksi | 75000| 2015-02-01 || B0006 | Pemrograman Basis Data | Abdul Kadir | Andi Offset | 2015 | Buku Teks | 67000| 2015-02-01 |+-----------+------------------------+------------------+---------------+-------+-----------+------+----------------+


• Example : Implementasi Fungsi Agregat

1. Fungsi COUNT

Digunakan untuk menghitung jumlah record.

Example: Bagaimana menghitung jumlah jenis buku di dalam tabel

buku?



1. Fungsi COUNT



1. Fungsi COUNT

Bagaimana cara menghitung jumlah record tabel buku dengan nama

kolom jum_rec?



1. Fungsi COUNT



1. Fungsi COUNT

Bagaimana menghitung jumlah record untuh tahun 2013 yang diberi

nama jum_rec?



1. Fungsi COUNT



2. Fungsi Agregat SUM

Fungsi SUM digunakan untuk menghitung total nilai dari kolom

tertentu.




Bagaiamana untuk menghitung total harga dari kolom harga dengan

nama kolom menjadi total_harga?




Bagaimana untuk menghitung total harga untuk tahun 2013 dari

kolom kolom harga dengan nama kolom menjadi total_harga?



3. Fungsi Agregat MAX

Digunakan untuk menampilkan nilai tertinggi dari suatu kolom.




Example:

Bagaimana cara menampilkan harga tertinggi dari kolom harga dan

ditampilkan dengan nama kolom harga_tertinggi?




Bagaimana untuk menampilkan harga tertinggi untuk tahun 2013?




Bagaimana untuk menampilkan harga tertinggi untuk tahun 2013 dan

ditampilkan dengan nama harga_tertinggi?



4. Fungsi Agregat MIN

Digunakan untuk menampilkan nilai terendah dari suatu kolom

Example:

Bagaimana untuk menampilkan nilai terendah dengan nama kolom

harga_terendah?




Bagaimana menampilkan harga terendah pad atahun 2013?



5. Fungsi Agregat AVG

Digunakan untuk menampilkan nilai rata-rata dari suatu kolom.

Bagaimana untuk menampilkan nilai rata rata dari harga dalam tabel

buku dengan nama kolom harga_rerata?




Bagaimana menampilkan rata rata harga pada tahun 2013 dengan

nama kolom harga_rerata dari tabel buku?

SUMBER

• Solichin, Achmad. MySQL 5 : Dari Pemula Hingga Akhir. Buku Komputer Gratis. 2010.

• SQL, Basis Data 1, Chapter 2, PENS ITS

• [URL] : http://www.w3schools.com/sql

• [URL] : http://fairuzelsaid.com/

http://www.w3schools.com/sql

http://fairuzelsaid.com/

konsep teknologi informasi bsyefani.staff.gunadarma.ac.id/downloads/files/53216/...sql –operator...

Documents