DevOps with Dimas Maryanto

Hai semuanya, di materi kali ini kita akan membahas tentang Basic penggunaan SQL untuk Select statement pada PostgreSQL. Diantaranya yang akan kita bahas yaitu

Format penulisan Select statement
Menampilkan semua data dalam sebuah table
Menampilkan hanya beberapa columns pada sebuah table
Memberikan alias pada suatu column
Menggunakan Special Characters / Escape Characters
Menggunakan Comments
Elimination of duplicate rows.

Ok langsung saja kita bahas materi yang pertama

Format penulisan Select statement

To retrieve data from a table, the table is queried. An SQL SELECT statement is used to do this. The statement is divided into a select list (the part that lists the columns to be returned), a table list (the part that lists the tables from which to retrieve the data), and an optional qualification (the part that specifies any restrictions).

1
2
3
4
5
select 
     * | columns...  
from 
    table_name | tables... (separated by comma)
[where condition]

Contohnya di database hr sekarang saya punya daftar tables seperti berikut:

hr=# \dt
 public | countries    | table | hr
 public | departments  | table | hr
 public | employees    | table | hr
 public | job_history  | table | hr
 public | jobs         | table | hr
 public | locations    | table | hr
 public | regions      | table | hr

Jadi kita bisa pilih salah satu tabelnya contohnya departments maka SQL Query select statemenya seperti berikut:

1
select * from departments;

Menampilkan semua data dalam sebuah table

Untuk menampilkan semua data untuk semua column yang dipilih dalam sebuah tabel kita bisa menggunakan spesial karakter * (asteric / bintang) dalam perintah select seperti berikut:

Maka akan tampil datanya seperti berikut:

dep_id	department_name	manager_id	location_id
10	Administration	200	1700
40	Human Resources	203	2400
50	Shipping	121	1500
60	IT	103	1400
90	Executive	100	1700

Menampilkan semua data pada beberapa columns pada sebuah table

Selain menggunakan * untuk menampikan semua kolom pada tabel tersebut, kita juga bisa sebutkan nama kolomnya. Tapi yang harus kita ketahui dulu adalah nama kolomnya pada table tersebut. kita bisa menggunakan perintah seperti berikut:

hr=# \dt departments
 department_id   | integer               | not null default nextval('departments_department_id_seq'::regclass)
 department_name | character varying(30) | 
 manager_id      | integer               | 
 location_id     | integer               |

Nah jadi kita punya column department_id, department_name, manager_id, dan location_id jadi kita bisa pilih atau semuanya seperti berikut:

Atau

Jika dijalankan maka hasilnya seperti berikut:

department_id	department_name
10	Administration
40	Human Resources
50	Shipping
60	IT
90	Executive

Memberikan alias pada suatu column

Secara default kalau kita melakukan perintah select terhadap suatu table contohnya misalnya departments, di table departments terdiri dari columns department_id, department_name, manager_id, location_id jika saya melakukan perintah select seperti berikut hasilnya:

dep_id	department_name	manager_id	location_id
10	Administration	200	1700
40	Human Resources	203	2400
50	Shipping	121	1500
60	IT	103	1400
90	Executive	100	1700

Dengan menggunakan column alias kita bisa memberikan nama kolomnya sesuai yang kita inginkan, Aturan penamaan variable atau kolom alias, diantaranya:

Menggunakan keyword as dan tanpa as, tidak boleh menggunakan charakter khusus seperti SPACE, HashTag, Dolar dan lain-lain, karakter yang diperbolehkan yaitu huruf, angka, dan underscore. Ini karena dengan menggunakan column alias tersebut nantinya bisa digunakan sebagai variable untuk melakukan ordering.
Diapit menggunakan double quote ", Diperbolehkan bebas karena hanya akan di jadikan sebagai label saja.

Seperti berikut contohnya:

Jika dijalankan hasilnya seperti berikut:

hr=# select  department_id as kode_divisi,
hr-#         department_name nama_department,
hr-#         manager_id as "Kode Manager"
hr-# from departments;
 kode_divisi |   nama_department    | Kode Manager
-------------+----------------------+--------------
         300 | System Analis        |
          10 | Administration       |          200
          20 | Marketing            |          201
          30 | Purchasing           |          114
          40 | Human Resources      |          203
          50 | Shipping             |          121
          60 | IT                   |          103
          70 | Public Relations     |          204
          80 | Sales                |          145
          90 | Executive            |          100
         100 | Finance              |          108
         110 | Accounting           |          205
         120 | Treasury             |

(28 rows)

Menggunakan Special Characters / Escape Characters

Some characters that are not alphanumeric have a special meaning that is different from being an operator. Details on the usage can be found at the location where the respective syntax element is described. This section only exists to advise the existence and summarize the purposes of these characters.

A dollar sign ($) followed by digits is used to represent a positional parameter in the body of a function definition or a prepared statement. In other contexts the dollar sign can be part of an identifier or a dollar-quoted string constant.
Parentheses (()) have their usual meaning to group expressions and enforce precedence. In some cases parentheses are required as part of the fixed syntax of a particular SQL command.
Brackets ([]) are used to select the elements of an array. See Section 8.15 for more information on arrays.
Commas (,) are used in some syntactical constructs to separate the elements of a list.
The semicolon (;) terminates an SQL command. It cannot appear anywhere within a command, except within a string constant or quoted identifier.
The colon (:) is used to select “slices” from arrays. (See Section 8.15.) In certain SQL dialects (such as Embedded SQL), the colon is used to prefix variable names.
The asterisk (*) is used in some contexts to denote all the fields of a table row or composite value. It also has a special meaning when used as the argument of an aggregate function, namely that the aggregate does not require any explicit parameter.
The period (.) is used in numeric constants, and to separate schema, table, and column names.

PostgreSQL also accepts “escape” string constants, which are an extension to the SQL standard. An escape string constant is specified by writing the letter E (upper or lower case) just before the opening single quote. Within an escape string, a backslash character () begins a C-like backslash escape sequence, in which the combination of backslash and following character(s) represent a special byte value

Backslash Escape	Interpretation
`\b`	backspace
`\f`	form feed
`\n`	newline
`\r`	carriage return
`\t`	tab

For example:

Jika di jalankan hasilnya seperti berikut:

hr=# select '*' bintang,
hr-#         E'()' kurung,
hr-#         E'\\' slash,
hr-#         E'baris pertama \n baris kedua' newline,
hr-#         E'awal\t setelah' tabspace;
 bintang | kurung | slash |    newline     |     tabspace
---------+--------+-------+----------------+------------------
 *       | ()     | \     | baris pertama +| awal     setelah
         |        |       |  baris kedua   |
(1 row)

Menggunakan Comments

A comment is a sequence of characters beginning with double dashes and extending to the end of the line, e.g.:

-- This is a standard SQL comment

Alternatively, C-style block comments can be used:

1
2
3
/* multiline comment
 * with nesting: /* nested block comment */
 */

where the comment begins with /* and extends to the matching occurrence of */. These block comments nest, as specified in the SQL standard but unlike C, so that one can comment out larger blocks of code that might contain existing block comments.

A comment is removed from the input stream before further syntax analysis and is effectively replaced by whitespace.

For example:

Elimination of duplicate rows

After the select list has been processed, the result table can optionally be subject to the elimination of duplicate rows. The DISTINCT keyword is written directly after SELECT to specify this:

SELECT DISTINCT select_list ...

Alternatively, an arbitrary expression can determine what rows are to be considered distinct:

SELECT DISTINCT ON (expression [, expression ...]) select_list ...

Here expression is an arbitrary value expression that is evaluated for all rows. A set of rows for which all the expressions are equal are considered duplicates, and only the first row of the set is kept in the output. Note that the “first row” of a set is unpredictable unless the query is sorted on enough columns to guarantee a unique ordering of the rows arriving at the DISTINCT filter. (DISTINCT ON processing occurs after ORDER BY sorting.)

The DISTINCT ON clause is not part of the SQL standard and is sometimes considered bad style because of the potentially indeterminate nature of its results. With judicious use of GROUP BY and subqueries in FROM, this construct can be avoided, but it is often the most convenient alternative.

For examples,Saya mau memangil data dari table employees untuk menampilkan kolom employee_id dan job_id berikut querynya:

select job_id from employees;

Jika saya jalankan maka hasilnya seperti berikut:

hr=# select job_id from employees;

   job_id   
------------
 AD_PRES
 AD_VP
 AD_VP
 IT_PROG
 IT_PROG
 IT_PROG
 FI_MGR
 FI_ACCOUNT
 FI_ACCOUNT
 PU_CLERK
...
(107 rows)

Jika kita mau meng-eliminasi nilai redudansi atau supaya unique datanya kita bisa menggunakan query seperti berikut: