Skip to content

Instantly share code, notes, and snippets.

View hidayat365's full-sized avatar

Nur Hidayat hidayat365

63 followers · 5 following
View GitHub Profile
@hidayat365
hidayat365 / tuning1.sql
Last active January 25, 2024 23:36
Simple SQL Query Performance Tuning using INDEX
E:\xampp\mysql\bin>mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.36 MySQL Community Server (GPL)
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
@hidayat365
hidayat365 / kulgram-inventory-quiz.sql
Last active May 18, 2023 02:35
script sql untuk tantangan membuat query kartu stok
-- tabel master barang
create table items (
id int auto_increment not null,
code varchar(50) not null,
name varchar(200) not null,
primary key (id),
constraint ak_items unique key (code)
);
-- tabel transaksi barang masuk
@hidayat365
hidayat365 / garansi.sql
Created December 17, 2013 14:30
MySQL, menggunakan query untuk menentukan apakah masa garansi sudah habis atau belum berdasarkan tanggal hari ini dan tanggal akhir masa garansi.
mysql> use test ;
Database changed
mysql> -- --------------------------------------
mysql> -- create table contoh
mysql> -- --------------------------------------
mysql> create table garansi (
-> id int auto_increment primary key,
-> awal_garansi date null,
-> akhir_garansi date null
@hidayat365
hidayat365 / union-all.sql
Last active May 13, 2022 02:56
Contoh Penggunaan UNION ALL
D:\xampp\mysql\bin>mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.16 MySQL Community Server (GPL)
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
@hidayat365
hidayat365 / sqlquery.txt
Created October 2, 2013 02:33
Menghitung nilai Minimum, Maksimum, Rata-rata, dan Total menggunakan SQL query
D:\xampp\mysql\bin>mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.16 MySQL Community Server (GPL)
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
@hidayat365
hidayat365 / PostgreSQL-Hierarchical-Query.sql
Created March 2, 2022 07:46
-- ----------------------------
-- Table structure for member
-- ----------------------------
DROP TABLE IF EXISTS "public"."member";
CREATE TABLE "public"."member" (
"id" int4 NOT NULL,
"no_urut" int4,
"parent_id" int4,
"name" varchar(255) COLLATE "pg_catalog"."default"
)
@hidayat365
hidayat365 / datediff.sql
Created July 1, 2013 09:50
MySQL: Menghitung selisih tanggal menggunakan fungsi DATEDIFF
Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.
D:\xampp\mysql\bin>mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.16 MySQL Community Server (GPL)
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
@hidayat365
hidayat365 / kulgram-sql-join.sql
Last active November 3, 2021 04:49
Script praktek dan latihan SQL JOIN di KulGram PHP Indonesia
create table students (
id serial primary key,
code varchar(20) not null,
name varchar(200) not null
);
create table courses (
id serial primary key,
code varchar(20) not null,
name varchar(200) not null
@hidayat365
hidayat365 / generated_column.sql
Last active November 3, 2021 02:21
Generated Column Example
-- master table
create table transactions (
id serial primary key,
code varchar(60) not null unique,
date integer not null,
value decimal(15,2) default 0,
remarks text
);
-- details table without generated column
@hidayat365
hidayat365 / crosstab3.sql
Last active October 11, 2021 06:56
MySQL Crosstab Example, Mengubah Row Menjadi Kolom hanya dengan satu perintah SQL saja via @berthojoris
mysql> use test;
Database changed
mysql> ---------------------------------
mysql> -- buat table dan sample datanya
mysql> ---------------------------------
mysql> create table tbl_data as
-> select '1' ID ,'001' NOPEL, 'INQUIRY' KATEGORI union all
-> select '2' ID ,'002' NOPEL, 'COMPLAINT' KATEGORI union all
-> select '3' ID ,'003' NOPEL, 'REQUEST' KATEGORI union all