konsep teknologi informasi bsyefani.staff.gunadarma.ac.id/downloads/files/53216/...sql –operator...
Post on 22-Dec-2020
6 Views
Preview:
TRANSCRIPT
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
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_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
• 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 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
5 Berglunds snabbköp Christina Berguvsvägen 8 Luleå S-958 22 Sweden
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 value1AND value2;
SQL – OPERATOR PEMBANDING
• 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
SQL – OPERATOR PEMBANDING
• Example ofThe SQL BETWEEN
• Kasus :
• The following SQL statement selects all products with a price
BETWEEN 10 and 20
SQL – OPERATOR PEMBANDING
• Example ofThe SQL BETWEEN
• Answer:
SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;
SQL – OPERATOR PEMBANDING
• Example ofThe SQL BETWEEN
• Kasus :
• Bagaimana untuk menampilkan products diluar dari range 10 dan 20
??
SQL – OPERATOR PEMBANDING
• Example ofThe SQL BETWEEN
• Answer:
SELECT * FROM Products
WHERE Price NOT BETWEEN 10 AND 20;
SQL – OPERATOR PEMBANDING
• Example ofThe 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?
SQL – OPERATOR PEMBANDING
• Example ofThe SQL BETWEEN
• Answer:
SELECT * FROM Products
WHERE (Price BETWEEN 10 AND 20)
AND NOT CategoryID IN (1,2,3);
SQL – OPERATOR PEMBANDING
• Example ofThe SQL BETWEEN
• Kasus:
• The following SQL statement selects all products with a
ProductName beginning with any of the letter BETWEEN ‘C’ and
‘M’?
SQL – OPERATOR PEMBANDING
• Example ofThe SQL BETWEEN
• Answer:
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
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
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 ContactName Address City PostalCode Country
1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
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
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
1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
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
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 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
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
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?
JENIS FUNGSI AGREGAT
• Example : Implementasi Fungsi Agregat
1. Fungsi COUNT
JENIS FUNGSI AGREGAT
• Example : Implementasi Fungsi Agregat
1. Fungsi COUNT
Bagaimana cara menghitung jumlah record tabel buku dengan nama
kolom jum_rec?
JENIS FUNGSI AGREGAT
• Example : Implementasi Fungsi Agregat
1. Fungsi COUNT
JENIS FUNGSI AGREGAT
• Example : Implementasi Fungsi Agregat
1. Fungsi COUNT
Bagaimana menghitung jumlah record untuh tahun 2013 yang diberi
nama jum_rec?
JENIS FUNGSI AGREGAT
• Example : Implementasi Fungsi Agregat
1. Fungsi COUNT
JENIS FUNGSI AGREGAT
• Example : Implementasi Fungsi Agregat
2. Fungsi Agregat SUM
Fungsi SUM digunakan untuk menghitung total nilai dari kolom
tertentu.
JENIS FUNGSI AGREGAT
• Example : Implementasi Fungsi Agregat
2. Fungsi Agregat SUM
Bagaiamana untuk menghitung total harga dari kolom harga dengan
nama kolom menjadi total_harga?
JENIS FUNGSI AGREGAT
• Example : Implementasi Fungsi Agregat
2. Fungsi Agregat SUM
JENIS FUNGSI AGREGAT
• Example : Implementasi Fungsi Agregat
2. Fungsi Agregat SUM
Bagaimana untuk menghitung total harga untuk tahun 2013 dari
kolom kolom harga dengan nama kolom menjadi total_harga?
JENIS FUNGSI AGREGAT
• Example : Implementasi Fungsi Agregat
2. Fungsi Agregat SUM
JENIS FUNGSI AGREGAT
• Example : Implementasi Fungsi Agregat
3. Fungsi Agregat MAX
Digunakan untuk menampilkan nilai tertinggi dari suatu kolom.
JENIS FUNGSI AGREGAT
• Example : Implementasi Fungsi Agregat
3. Fungsi Agregat MAX
Example:
Bagaimana cara menampilkan harga tertinggi dari kolom harga dan
ditampilkan dengan nama kolom harga_tertinggi?
JENIS FUNGSI AGREGAT
• Example : Implementasi Fungsi Agregat
3. Fungsi Agregat MAX
JENIS FUNGSI AGREGAT
• Example : Implementasi Fungsi Agregat
3. Fungsi Agregat MAX
Bagaimana untuk menampilkan harga tertinggi untuk tahun 2013?
JENIS FUNGSI AGREGAT
• Example : Implementasi Fungsi Agregat
3. Fungsi Agregat MAX
Bagaimana untuk menampilkan harga tertinggi untuk tahun 2013 dan
ditampilkan dengan nama harga_tertinggi?
JENIS FUNGSI AGREGAT
• Example : Implementasi Fungsi Agregat
3. Fungsi Agregat MAX
JENIS FUNGSI AGREGAT
• Example : Implementasi Fungsi Agregat
4. Fungsi Agregat MIN
Digunakan untuk menampilkan nilai terendah dari suatu kolom
Example:
Bagaimana untuk menampilkan nilai terendah dengan nama kolom
harga_terendah?
JENIS FUNGSI AGREGAT
• Example : Implementasi Fungsi Agregat
4. Fungsi Agregat MIN
JENIS FUNGSI AGREGAT
• Example : Implementasi Fungsi Agregat
4. Fungsi Agregat MIN
Bagaimana menampilkan harga terendah pad atahun 2013?
JENIS FUNGSI AGREGAT
• Example : Implementasi Fungsi Agregat
4. Fungsi Agregat MIN
JENIS FUNGSI AGREGAT
• Example : Implementasi Fungsi Agregat
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?
JENIS FUNGSI AGREGAT
• Example : Implementasi Fungsi Agregat
5. Fungsi Agregat AVG
JENIS FUNGSI AGREGAT
• Example : Implementasi Fungsi Agregat
5. Fungsi Agregat AVG
Bagaimana menampilkan rata rata harga pada tahun 2013 dengan
nama kolom harga_rerata dari tabel buku?
JENIS FUNGSI AGREGAT
• Example : Implementasi Fungsi Agregat
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/
top related