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

KONSEP SISTEM INFORMASI B P E R T 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

SQL – OPERATOR ARITMATIKA • Pada ekspresi SQL dengan tipe data Number dan Date dapat digunakan operator aritmatika.

Operator Precedence

Deskripsi

+

Tambah

-

Kurang

*

Kali

/

Bagi

SQL – OPERATOR ARITMATIKA • Contoh: (jika memiliki kolom pada tabel employees) seperti di bawah ini

SQL – OPERATOR ARITMATIKA • Contoh: Jika menggunakan statement :

SELECT LAST_NAME, SALARY, SALARY+300 FROM EMPLOYEES;

SQL – OPERATOR ARITMATIKA • Contoh: (Hasil)

SQL – OPERATOR ARITMATIKA • Contoh: (Hasil)

SQL – OPERATOR ARITMATIKA • 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;

SQL – OPERATOR ARITMATIKA • Contoh 2 : Hasil Statement 1

SQL – OPERATOR ARITMATIKA • Contoh 2 : Hasil Statement 2

SQL – OPERATOR ARITMATIKA • 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_name AS alias_name FROM table_name;

SQL – ALIASES • Contoh: Kasus 1

• (Jika ingin membuat last_name commission_pct menjadi comm)

menjadi

name,

dan

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 CustomerNam ContactNam Address D e e

City

2

Ana Trujillo Ana Trujillo Emparedados y helados

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.

UK

London

PostalCode

WA1 1DP

Country

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 sometimes you 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 • Contoh : (Kasus 2) • 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

Ana Trujillo


México D.F.

05021

Mexico

3


Antonio Moreno Mataderos 2312

México D.F.

05023

Mexico

4

Around the Horn

Thomas Hardy

London

WA1 1DP

UK

120 Hanover Sq.

SQL SELECT DISTINCT • Contoh : (Kasus 2)

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

SQL – OPERATOR PEMBANDING • Contoh: (Jika ingin menampilkan salary kurang dari sama dengan 3000 Last_name

salary

Matos

2600

Vargas

2500

Jose

3100

Santos

3500

Marquez

3100

SQL – OPERATOR PEMBANDING • Contoh: Statement

SELECT last_name, salary FROM employees WHERE salary <= 3000;

SQL – OPERATOR PEMBANDING • Contoh: Hasil

SQL – OPERATOR PEMBANDING • 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

SQL – OPERATOR PEMBANDING • The SQL BETWEEN Operator

• The BETWEEN operator selects values within a range. • The values can be numbers, text, or dates.

SQL – OPERATOR PEMBANDING • The SQL BETWEEN Syntax

SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;

SQL – OPERATOR PEMBANDING • Example of The SQL BETWEEN • Below is a selection from the ‘Products’ table ProductI ProductName D

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

SQL – OPERATOR PEMBANDING • Example of The SQL BETWEEN

• Kasus : • The following SQL statement selects all products with a price BETWEEN 10 and 20

SQL – OPERATOR PEMBANDING • Example of The SQL BETWEEN • Answer:

SELECT * FROM Products WHERE Price BETWEEN 10 AND 20;

SQL – OPERATOR PEMBANDING • Example of The SQL BETWEEN • Kasus : • Bagaimana untuk menampilkan products diluar dari range 10 dan 20 ??


SELECT * FROM Products WHERE Price NOT BETWEEN 10 AND 20;

SQL – OPERATOR PEMBANDING • Example of The SQL BETWEEN • 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?


SELECT * FROM Products WHERE (Price BETWEEN 10 AND 20) AND NOT CategoryID IN (1,2,3);

SQL – OPERATOR PEMBANDING • Example of The SQL BETWEEN • Kasus: • The following SQL statement selects all products with a ProductName beginning with any of the letter BETWEEN ‘C’ and ‘M’?


SELECT * FROM Products 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

SELECT column_name(s) 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

1

Alfreds Futterkiste

Maria Anders

Obere Str. 57

Berlin

12209

Germany

2

Ana Trujillo Ana Trujillo Emparedados y helados


México D.F.

05021

Mexico

3


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 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

SELECT column_name(s) 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

1

ContactName

Address

City

PostalCode

Country

Alfreds Futterkiste Maria Anders

Obere Str. 57

Berlin

12209

Germany

2


Ana Trujillo


México D.F.

05021

Mexico

3


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 Berglund

Berguvsvägen 8

S-958 22

Sweden

Luleå

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 CustomerName erID

ContactName

Address

City

PostalCode

Country

1

Alfreds Futterkiste

Maria Anders

Obere Str. 57

Berlin

12209

Germany

2


Ana Trujillo

Avda. de la Constitución México D.F. 05021 2222

Mexico

3


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 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

SELECT * FROM Customers 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

SELECT * FROM Customers 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

SELECT * FROM Customers 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 1 2 3 4 5 6 7 8

Operators Arithmetic Operators Concatenation operator Comparison conditions IS [NOT] NULL, LIKE, [NOT] IN [NOT] BETWEEN NOT logical condition AND logical condition OR logical condition

SQL – OPERASI PEMBANDING • Example of Rules of Precedence

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 ratarata.

• 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

JENIS FUNGSI AGREGAT • Example : • Berikut ini adalah latihan dalam menggunakan fungsi agregat. • Buatlah tabel buku dengan struktur tabel sebagai berikut : -> Next Slide

JENIS FUNGSI AGREGAT • Example :

JENIS FUNGSI AGREGAT • Example : Kita lihat deskripsi dari tabel buku.

JENIS FUNGSI AGREGAT • Example : Masukkan data sebagai berikut

JENIS FUNGSI AGREGAT • 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 | +-----------+------------------------+------------------+---------------+-------+-----------+------+----------------+

JENIS FUNGSI AGREGAT • 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.


2. Fungsi Agregat SUM Bagaiamana untuk menghitung total harga dari kolom harga dengan nama kolom menjadi total_harga?


2. Fungsi Agregat SUM


2. Fungsi Agregat SUM Bagaimana untuk menghitung total harga untuk tahun 2013 dari kolom kolom harga dengan nama kolom menjadi total_harga?


2. Fungsi Agregat SUM


3. Fungsi Agregat MAX Digunakan untuk menampilkan nilai tertinggi dari suatu kolom.


3. Fungsi Agregat MAX Example: Bagaimana cara menampilkan harga tertinggi dari kolom harga dan ditampilkan dengan nama kolom harga_tertinggi?


3. Fungsi Agregat MAX


3. Fungsi Agregat MAX Bagaimana untuk menampilkan harga tertinggi untuk tahun 2013?


3. Fungsi Agregat MAX Bagaimana untuk menampilkan harga tertinggi untuk tahun 2013 dan ditampilkan dengan nama harga_tertinggi?


3. Fungsi Agregat MAX


4. Fungsi Agregat MIN Digunakan untuk menampilkan nilai terendah dari suatu kolom Example: Bagaimana untuk menampilkan nilai terendah dengan nama kolom harga_terendah?


4. Fungsi Agregat MIN


4. Fungsi Agregat MIN Bagaimana menampilkan harga terendah pad atahun 2013?


4. Fungsi Agregat MIN


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?


5. Fungsi Agregat AVG


5. Fungsi Agregat AVG Bagaimana menampilkan rata rata harga pada tahun 2013 dengan nama kolom harga_rerata dari tabel buku?


5. Fungsi Agregat AVG

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/

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

Recommend Documents