Skip to content

Instantly share code, notes, and snippets.

View generated_column_pgsql.sql
-- 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 / generated_column.sql
Created May 8, 2020
Generated Column Example
View generated_column.sql
-- master table
create table transactions (
id int auto_increment 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 / db_size.sql
Last active Jul 31, 2019
MySQL Database and Table Size Query
View db_size.sql
SELECT table_schema "DB Name",
Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size (MB)"
FROM information_schema.tables
GROUP BY table_schema;
SELECT table_name
, round(((table_rows) / 1024 / 1024), 2) as "Row Count (mil)"
, round(((data_length) / 1024 / 1024), 2) as "Data Size (MB)"
, round(((index_length) / 1024 / 1024), 2) as "Index Size (MB)"
, round(((data_length + index_length) / 1024 / 1024), 2) as "Table Size (MB)"
@hidayat365
hidayat365 / fk-basic.sql
Last active Jun 23, 2019
Dasar penggunaan Foreign Key (FK) di database relasional (RDBMS)
View fk-basic.sql
Nurs-MacBook-Pro:~ nurhidayat$ /Applications/XAMPP/bin/mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 307
Server version: 10.1.38-MariaDB Source distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
@hidayat365
hidayat365 / validate_pairs.php
Last active Jan 29, 2019
Tantangan PHP Group: Validasi Input Array Mencari data Pairs
View validate_pairs.php
<?php
function my_var_dump($prefix, $data) {
echo $prefix . ": ";
array_walk($data, function($val,$key) {
echo json_encode($val) . ' ';
});
echo "\n";
}
function validate_pairs($data, $sum) {
View dss.sql
-- students table
create table murid (
id int auto_increment primary key,
nisn varchar(20) not null,
nama varchar(100) not null
);
-- kesalahan table
create table kesalahan (
id int auto_increment primary key,
@hidayat365
hidayat365 / grouping.sql
Created Aug 31, 2017
Grouping by transactions using custom group
View grouping.sql
-- ---------------------------------------
-- Generate Data
-- ---------------------------------------
CREATE table trans_history AS
SELECT 'TRANS-A' AS transactions, '2017-08-30 08:00:00' AS history UNION ALL
SELECT 'TRANS-A' AS transactions, '2017-08-30 08:10:00' AS history UNION ALL
SELECT 'TRANS-A' AS transactions, '2017-08-30 08:20:00' AS history UNION ALL
SELECT 'TRANS-A' AS transactions, '2017-08-30 08:30:00' AS history UNION ALL
SELECT 'TRANS-A' AS transactions, '2017-08-30 08:40:00' AS history UNION ALL
SELECT 'TRANS-A' AS transactions, '2017-08-30 08:50:00' AS history UNION ALL
@hidayat365
hidayat365 / sqlserver-tuning.sql
Last active Feb 3, 2021
Queries for SQL Server Performance Tuning
View sqlserver-tuning.sql
DBCC FREEPROCCACHE;
-- -----------------------
-- Find Long Running Query
-- Execute the query inside target database
-- -----------------------
SELECT st.text
, qp.query_plan
, qs.*
FROM (
@hidayat365
hidayat365 / kulgram-inventory-quiz.sql
Last active May 8, 2020
script sql untuk tantangan membuat query kartu stok
View kulgram-inventory-quiz.sql
-- 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 / kulgram-sql-join.sql
Last active Apr 12, 2021
Script praktek dan latihan SQL JOIN di KulGram PHP Indonesia
View kulgram-sql-join.sql
create table students (
id integer auto_increment primary key,
code varchar(20) not null,
name varchar(200) not null
);
create table courses (
id integer auto_increment primary key,
code varchar(20) not null,
name varchar(200) not null