.::: Query to check table, column, compare schema database, structure table in EDB PostgreSQL :::.

 

Untuk membandingkan struktur tabel antara dua database teguhth dan aisyah di SQL Server, kita bisa menggunakan query yang membandingkan schema (skema tabel), kolom, tipe data, dan properti lainnya.

A. Query to check table & system table

1. INFORMATION_SCHEMA

## from teguhth akses teguhth
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_CATALOG = 'teguhth' AND TABLE_SCHEMA = 'public';
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'public';

## from teguhth akses aisyahdb
SELECT * FROM dblink('dbname=aisyahdb host=10.10.10.9 user=admin password=xxx',
    'SELECT table_catalog, table_schema, table_name, table_type
     FROM information_schema.tables WHERE table_schema = ''public''')
AS t(table_catalog text, table_schema text, table_name text, table_type text);
 

.::: Query to check table, column, compare schema database, structure table in MariaDB MySQL :::.

 

Untuk membandingkan struktur tabel antara dua database teguhth dan aisyah di SQL Server, kita bisa menggunakan query yang membandingkan schema (skema tabel), kolom, tipe data, dan properti lainnya.

A. Query to check table & system table

1. INFORMATION_SCHEMA

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'teguhth';
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'aisyahdb';

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'teguhth';
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'aisyahdb';
 

 

.::: Script EXPDP Backup Daily Database Oracle 19C with Record to Table with Duration with insert client table :::.

 

correlation https://teguhth.blogspot.com/2025/02/script-expdp-backup-daily-database.html

A. Create Backup with record table
1. Create DATA_PUMP_DIR backup directory

CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS '/home/oracle/backup';
GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO aisyah;

2. create schema (dbatools)

.::: Script Backup Full All Database Using Record to Table include start, finish & duration backup Using insert table in PostgreSQL EDB :::.

 

correlation https://teguhth.blogspot.com/2025/02/script-backup-full-all-database-using.html

1. create database & table
CREATE TABLE backuplogduration (
    host VARCHAR(255) NOT NULL,
    backuptime TIMESTAMP NOT NULL,
    start TIMESTAMP NOT NULL,
    finish TIMESTAMP NOT NULL,
    duration VARCHAR(255) NOT NULL,
    dbname VARCHAR(255) NOT NULL,
    type VARCHAR(255) NOT NULL,
    size_mb BIGINT NOT NULL,
    size BIGINT NOT NULL,
    filename VARCHAR(255) NOT NULL
);
 

.::: Script Backup Daily Multiple Database, Backup FULL, Backup LOG, Backup Incremental with Record Table with start, finish, duration backup logs and using insert table client in MariaDB MySQL MarinaDB :::.

 

correlation https://teguhth.blogspot.com/2025/02/script-backup-daily-multiple-database.html

1. Create database & table for save backup log

create database dbatools;
CREATE TABLE backuplogduration  (
    host VARCHAR(255) NOT NULL,
    backuptime  DATETIME NOT NULL,
    start DATETIME NOT NULL,
    finish DATETIME NOT NULL,
    duration VARCHAR(255) NOT NULL,
    dbname VARCHAR(255) NOT NULL,
    type VARCHAR(255) NOT NULL,
    size_mb BIGINT NOT NULL,
    size BIGINT NOT NULL,
    filename VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
 

.::: How to configure Transparent Data Encryption (TDE), Monitoring, Remove TDE, remove certificate, test backup & Restore in SQL Server :::.

 

correlation with https://teguhth.blogspot.com/2024/06/how-to-configure-transparent-data.html

A. Create TDE

1. Create Master Key

USE Master;
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='TDEpassword!';
GO
 
2. Create Certificate protected by master key
CREATE CERTIFICATE TDE_Cert_source
WITH
SUBJECT='Database_Encryption Source';
GO
 

.::: Create Script Backup Daily Full, Differential, Log SQL Server Using T-SQL with Record to Table with Size Backup and using insert table client and create store procedure sp_backup_full, sp_backup_log, exec sp_backup_diff :::.

 

1. enable sqlcmd

EXEC sp_configure 'show advanced options', 1;
        RECONFIGURE;
        EXEC sp_configure 'xp_cmdshell', 1;
        RECONFIGURE;
        
2. create table record

use dbatools
go

CREATE TABLE BackupLogStatus (
    BackupDate DATETIME,
    DatabaseName NVARCHAR(255),
    BackupType NVARCHAR(50),
    Status NVARCHAR(50),
    BackupFile NVARCHAR(500),
    BackupSizeMB BIGINT  -- Ukuran file dalam byte
);

.::: Query to check table, column, compare schema database, structure table in SQL Server :::.

 

Untuk membandingkan struktur tabel antara dua database teguhth dan aisyah di SQL Server, kita bisa menggunakan query yang membandingkan schema (skema tabel), kolom, tipe data, dan properti lainnya.

A. Query to check table & system table

1. INFORMATION_SCHEMA
select * from teguhth.INFORMATION_SCHEMA.tables
select * from aisyah.INFORMATION_SCHEMA.tables

select * from teguhth.INFORMATION_SCHEMA.COLUMNS
select * from aisyah.INFORMATION_SCHEMA.COLUMNS
 

.::: Create Script Backup Daily Full, Differential, Log SQL Server Using T-SQL with Record to Table with Size Backup and create store procedure sp_backup_full, sp_backup_log, exec sp_backup_diff :::.

 

1. enable sqlcmd

EXEC sp_configure 'show advanced options', 1;
        RECONFIGURE;
        EXEC sp_configure 'xp_cmdshell', 1;
        RECONFIGURE;
        
2. create table record

CREATE TABLE BackupLogStatus (
    BackupDate DATETIME,
    DatabaseName NVARCHAR(255),
    BackupType NVARCHAR(50),
    Status NVARCHAR(50),
    BackupFile NVARCHAR(500),
    BackupSizeMB BIGINT  -- Ukuran file dalam byte
);

.::: Create Script Backup Daily Full, Differential, Log SQL Server Using T-SQL with Record to Table and create store procedure sp_backup_full, sp_backup_log, exec sp_backup_diff :::.

 

1. Create Table BackupLogStatus

CREATE TABLE BackupLogStatus (
    BackupDate DATETIME,
    DatabaseName NVARCHAR(255),
    BackupType NVARCHAR(50),
    Status NVARCHAR(50),
    BackupFile NVARCHAR(500)   
);
 

.::: Create Script Backup Daily Full, Differential, Log SQL Server Using T-SQL & Include delete file Folder :::.

 

1. Backup Full

--- ### Script Begin ### ---
DECLARE @DatabaseName NVARCHAR(255)
DECLARE @BackupPath NVARCHAR(500)
DECLARE @BackupFile NVARCHAR(500)

-- Set lokasi penyimpanan backup (ubah sesuai kebutuhan)
SET @BackupPath = 'C:\BackupDaily\'  

-- Cursor untuk mendapatkan semua database kecuali sistem database
DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')  -- Hindari backup database sistem

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @DatabaseName  

WHILE @@FETCH_STATUS = 0  
BEGIN  
    -- Set nama file backup
    SET @BackupFile = @BackupPath + @DatabaseName + '_'+ FORMAT(GETDATE(), 'yyyyMMdd_HHmmss') + '.bak'

    -- Perintah Backup
    PRINT 'Backing up: ' + @DatabaseName  
    BACKUP DATABASE @DatabaseName  
    TO DISK = @BackupFile  
    WITH INIT, COMPRESSION, FORMAT, STATS = 10  

    FETCH NEXT FROM db_cursor INTO @DatabaseName  
END  

-- Tutup cursor
CLOSE db_cursor  
DEALLOCATE db_cursor  

--- ### Script End ### ---
 

.::: Deploy MariaDB Audit filter by only Create, Insert, Update, Delete :::.

 

 correlation https://teguhth.blogspot.com/2022/08/enable-server-audit-in-mariadb-mysql.html

A. Prepare Filter
1. Create
grep -iE "create " server_audit.log | grep -ivE "select|SHOW|INSERT"

2. Insert
grep -iE "insert into|insert ignore" server_audit.log | grep -ivE "select|CREATE|SHOW"

3. Update
grep -iE "UPDATE .*SET" server_audit.log | grep -ivE "select|CREATE"

4. Delete
grep -i "delete" server_audit.log | grep -ivE "select|CREATE|SHOW|INSERT" 

.::: Backup Database MariaDB with encripted -aes-256-cbc dan restore mariadb database :::.

 

1. backup enkripsi

mysqldump -uroot -pxxx teguhth | openssl enc -aes-256-cbc -md sha1 -salt -out teguhthenc.sql.enc

mysqldump -uroot -pxxx teguhth | openssl enc -pass pass:abcde -aes-256-cbc -md sha1 -salt -out teguhthenc.sql.enc

mysqldump -uroot -pxxx teguhth | openssl enc -pass pass:abcde -aes-256-cbc -md sha1 -salt -out teguhthenc.sql.enc | gzip -c  > teguhthenc.gz.sql.enc

.::: Script Backup Database MariaDB from Master & Check from MaxScale :::.

 

1. Check maxscale
[root@ha01 test]# ip a | grep -i 10.10
    inet 10.10.10.15/24 brd 10.10.10.255 scope global noprefixroute ens33
    inet 10.10.10.110/32 scope global ens33
[root@ha01 test]#

maxctrl list servers

.::: Restore SQL Script Using SQL CMD in MSSQL SQL Server :::.

 

1. check servername
select @@servername,@@version
 

2. Restore script

sqlcmd -S MSSQL -U ha -P ha1 -d teguhth_sqlcmd -i C:\tmp\teguhth_sqlcmd.sql
 

.::: Add Sensor Microsoft SQL V2, Sensor Factory PRTG to Monitoring CPU Memory SQL Server :::.

 

continue from https://teguhth.blogspot.com/2024/03/script-check-cpu-usage-memory-usage.html

A. Configuration to PRTG
1. create file query
 

2. copy query to directory
C:\Program Files (x86)\PRTG Network Monitor\Custom Sensors\sql\mssql

.::: Install & Configuration Virtual IP Using Keepalived :::.

 

1. Install keepalived
yum install keepalived -y

[root@teguhth01 ~]# yum install keepalived -y

[root@teguhth02 ~]# yum install keepalived -y

[root@teguhth01 ~]# systemctl enable keepalived
Created symlink from /etc/systemd/system/multi-user.target.wants/keepalived.service to /usr/lib/systemd/system/keepalived.service.
[root@teguhth01 ~]# systemctl start keepalived
[root@teguhth01 ~]#

.::: Script running command Linux using Remote include Restart Shutdown sample for maintenance MariaDB & MaxScale :::.

 

1. Data IP LAB
IP Maxscale : 10.10.10.15
IP Master : 10.10.10.31
IP Slave : 10.10.10.32

2. enable access root without password

yum install -y sshpass
ssh-keygen -t rsa
sshpass -p root ssh-copy-id -p22 -o 'StrictHostKeyChecking=no' root@10.10.10.15
sshpass -p root ssh-copy-id -p22 -o 'StrictHostKeyChecking=no' root@10.10.10.31
sshpass -p root ssh-copy-id -p22 -o 'StrictHostKeyChecking=no' root@10.10.10.32

3. test

ssh root@10.10.10.15 "hostname"
ssh root@10.10.10.31 "hostname"
ssh root@10.10.10.31 "hostname"

.::: Script Backup Daily Multiple Database, Backup FULL, Backup LOG, Backup Incremental with Record Table with start, finish, duration backup logs in MariaDB MySQL MarinaDB :::.

 correlation +update from https://teguhth.blogspot.com/2024/07/script-backup-full-differential.html
https://teguhth.blogspot.com/2025/01/script-backup-daily-backup-full-backup.html

1. Create database & table for save backup log

create database dbatools;
CREATE TABLE backuplogduration  (
    host VARCHAR(255) NOT NULL,
    backuptime  DATETIME NOT NULL,
    start DATETIME NOT NULL,
    finish DATETIME NOT NULL,
    duration VARCHAR(255) NOT NULL,
    dbname VARCHAR(255) NOT NULL,
    type VARCHAR(255) NOT NULL,
    size_mb BIGINT NOT NULL,
    size BIGINT NOT NULL,
    filename VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
 

.::: Script EXPDP Backup Daily Database Oracle 19C with Record to Table with Duration :::.

 

A. Create Backup with record table
1. Create DATA_PUMP_DIR backup directory

CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS '/home/oracle/backup';
GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO aisyah;

2. create schema (dbatools)

CREATE USER dbatools IDENTIFIED BY dbatools;
GRANT CONNECT, RESOURCE,oem_monitor TO dbatools;
ALTER USER dbatools QUOTA UNLIMITED ON USERS;

-- Memberikan hak akses dasar kepada user dbatools
GRANT CONNECT, RESOURCE,oem_monitor TO dbatools;

-- Memberikan hak akses untuk melakukan dbatoolsistrasi
GRANT CREATE SESSION TO dbatools;
GRANT UNLIMITED TABLESPACE TO dbatools;
GRANT DBA TO dbatools;

.::: Script Backup Full All Database Using Record to Table include start, finish & duration backup in PostgreSQL EDB :::.

 

correlation https://teguhth.blogspot.com/2024/02/script-backup-full-all-database-in.html
https://teguhth.blogspot.com/2025/01/script-backup-full-all-database-using.html

1. create database & table
CREATE TABLE backuplogduration (
    host VARCHAR(255) NOT NULL,
    backuptime TIMESTAMP NOT NULL,
    start TIMESTAMP NOT NULL,
    finish TIMESTAMP NOT NULL,
    duration VARCHAR(255) NOT NULL,
    dbname VARCHAR(255) NOT NULL,
    type VARCHAR(255) NOT NULL,
    size_mb BIGINT NOT NULL,
    size BIGINT NOT NULL,
    filename VARCHAR(255) NOT NULL
);
 

.::: Script Shell to collect differential time using epoch & human time in Centos 8 :::.

 

A. Script dengan time

1. Pengurangan dalam format human time

[root@teguhth temp]# cat start_finish_time.sh
#!/bin/bash

# Menyimpan waktu mulai dalam format manusiawi
datex=$(date "+%Y-%m-%d %H:%M:%S.%N")
epoch_start=$(date +%s%N)
echo "trial time"

# Menyimpan waktu selesai dalam format manusiawi
datey=$(date "+%Y-%m-%d %H:%M:%S.%N")
epoch_end=$(date +%s%N)

.::: Script EXPDP Backup Daily Database Oracle 19C with Record to Table :::.

 

A. Create Backup with record table
1. Create DATA_PUMP_DIR backup directory

CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS '/home/oracle/backup';
GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO aisyah;

2. create schema (dbatools)

CREATE USER dbatools IDENTIFIED BY dbatools;
GRANT CONNECT, RESOURCE,oem_monitor TO dbatools;
ALTER USER dbatools QUOTA UNLIMITED ON USERS;

-- Memberikan hak akses dasar kepada user dbatools
GRANT CONNECT, RESOURCE,oem_monitor TO dbatools;

-- Memberikan hak akses untuk melakukan dbatoolsistrasi
GRANT CREATE SESSION TO dbatools;
GRANT UNLIMITED TABLESPACE TO dbatools;
GRANT DBA TO dbatools;
 

.::: Shell Script to create,insert data CRUD Oracle Database :::.

 

1. check version
sh check_version.sh
 
2. insert data
sh create_insert.sh

3. script check version

[oracle@teguhth blog]$ cat check_version.sh
#!/bin/bash

# Konfigurasi koneksi database
vlogin="aisyah/hanin@//10.10.10.19:1521/tgh"

# Menjalankan query SQL dan menyimpan output ke file
sqlplus -s "$vlogin" <<EOF > output_instance_info.txt
SET HEADING ON
SET FEEDBACK OFF
SET PAGESIZE 100
SET LINESIZE 200

.::: Convert Query select PostgreSQL EDB to Excel CSV :::.

 

1. query table pembelian

SELECT pg_read_file('/etc/hostname') as hostname,VERSION() AS versi;
select * from pembelian;
 

2. Ekspor Data ke File CSV
COPY pembelian TO '/convert/pembelianedb.csv' WITH CSV HEADER;
 

.::: Create table PostgreSQL EDB Support jsonb_build_object,jsonb_agg & Output Json :::.

 

A. Create table with ouput json

1. run query to ouput JSON_OBJECT

-- Query untuk tabel barang
SELECT
    jsonb_build_object(
        'KODE_BARANG', KODE_BARANG,
        'NAMA_BARANG', NAMA_BARANG,
        'SATUAN_BARANG', SATUAN_BARANG,
        'STOK_BARANG', STOK_BARANG
    ) AS barang_json
FROM barang;

.::: Create table MSSQL SQL Server Support JSON_QUERY,JSON PATH & Output Json :::.

 

 

A. Create table with ouput json

1. run query to ouput JSON_OBJECT
-- Barang
SELECT
    JSON_QUERY((
        SELECT
            KODE_BARANG AS 'KODE_BARANG',
            NAMA_BARANG AS 'NAMA_BARANG',
            SATUAN_BARANG AS 'SATUAN_BARANG',
            STOK_BARANG AS 'STOK_BARANG'
        FOR JSON PATH
    )) AS barang_jsonarr
FROM barang;
 

.::: Create table MariaDB MySQL Support JSON_OBJECT,JSON_ARRAYAGG & Output Json :::.

 

A. Create table with ouput json

1. run query to ouput JSON_OBJECT

SELECT
    JSON_OBJECT(
        'KODE_BARANG', KODE_BARANG,
        'NAMA_BARANG', NAMA_BARANG,
        'SATUAN_BARANG', SATUAN_BARANG,
        'STOK_BARANG', STOK_BARANG
    ) AS barang_json
FROM barang;

SELECT
    JSON_OBJECT(
        'KODE_CUSTOMER', KODE_CUSTOMER,
        'NAMA_CUSTOMER', NAMA_CUSTOMER,
        'ALAMAT_CUSTOMER', ALAMAT_CUSTOMER,
        'KOTA_CUSTOMER', KOTA_CUSTOMER,
        'TELEPON_CUSTOMER', TELEPON_CUSTOMER
    ) AS customer_json
FROM customer;

SELECT
    JSON_OBJECT(
            'KODE_SUPLIER', KODE_SUPLIER,
            'NAMA_SUPLIER', NAMA_SUPLIER,
            'ALAMAT_SUPLIER', ALAMAT_SUPLIER,
            'KOTA_SUPLIER', KOTA_SUPLIER,
            'TELEPON_SUPLIER', TELEPON_SUPLIER
        )
    ) AS suplier_json
FROM suplier;
 

.::: Script Backup Full All Database Using Record to Table in PostgreSQL EDB :::.

 

correlation https://teguhth.blogspot.com/2024/02/script-backup-full-all-database-in.html

1. create database & table
create database dbatools;
CREATE TABLE backuplogs (
    host VARCHAR(255) NOT NULL,
    date TIMESTAMP NOT NULL,
    dbname VARCHAR(255) NOT NULL,
    backup VARCHAR(255) NOT NULL,
    size_mb BIGINT NOT NULL,
    size BIGINT NOT NULL,
    filename VARCHAR(255) NOT NULL
);

2. run sh backup_full_daily_with_record.sh

.::: Script Backup Daily Multiple Database, Backup FULL, Backup LOG, Backup Incremental with Record Table backup logs in MariaDB MySQL MarinaDB :::.

 

correlation +update from https://teguhth.blogspot.com/2024/07/script-backup-full-differential.html

1. Create database & table for save backup log
create database dbatools;
CREATE TABLE backuplogs (
    host VARCHAR(255) NOT NULL,
    date DATETIME NOT NULL,
    dbname VARCHAR(255) NOT NULL,
    backup VARCHAR(255) NOT NULL,
    size_mb BIGINT NOT NULL,
    size BIGINT NOT NULL,
    filename VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

2. put script sh backup_full_daily_with_record.sh

.::: Convert Query select MariaDB MarinaDB MySQL to Excel CSV include using script scheduler :::.

A. Using cmd MariaDB

1. check query using normal query

select * from pembelian;

 

2. create folder

mkdir /convert
chmod 777 /convert
 

.::: Test Insert, select Data using Maxscale include maxscale router to slave/ master :::.

 

1. Create table;
CREATE TABLE `hostname_tbl` (
   `host` varchar(100) DEFAULT NULL,
   `created_date` datetime DEFAULT NULL,
   `dbinfo` varchar(8000) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

2. check maxscale list servers

[root@ha01 ~]#  maxctrl list servers
┌─────────────────┬─────────────┬──────┬─────────────┬─────────────────┬─────────┬─────────────────────────────┐
│ Server          │ Address     │ Port │ Connections │ State           │ GTID    │ Monitor                     │
├─────────────────┼─────────────┼──────┼─────────────┼─────────────────┼─────────┼─────────────────────────────┤
├ gtid01-v31      │ 10.10.10.31 │ 3306 │ 0           │ Master, Running │ 0-1-121 │ MariaDB-Monitor-gtid        │
├─────────────────┼─────────────┼──────┼─────────────┼─────────────────┼─────────┼─────────────────────────────┤
│ gtid02-v32      │ 10.10.10.32 │ 3306 │ 0           │ Slave, Running  │ 0-1-121 │ MariaDB-Monitor-gtid        │
├─────────────────┼─────────────┼──────┼─────────────┼─────────────────┼─────────┼─────────────────────────────┤
│ gtid03-v33      │ 10.10.10.33 │ 3306 │ 0           │ Slave, Running  │ 0-1-121 │ MariaDB-Monitor-gtid        │
└─────────────────┴─────────────┴──────┴─────────────┴─────────────────┴─────────┴─────────────────────────────┘
[root@ha01 ~]#

.::: Query Convert select to insert table in SQL Server MSSQL, MariaDB MySQL, PostgreSQL EDB, Oracle :::.

 

A. MariaDB
    
1. Query basic

select * from pembelian;

2. query to convert select to insert
SELECT CONCAT(
    'INSERT INTO pembelian (KODE_PEMBELIAN, KODE_BARANG, KODE_CUSTOMER, TANGGAL_PEMBELIAN, JUMLAH_PEMBELIAN) VALUES (',
    QUOTE(KODE_PEMBELIAN), ', ',
    QUOTE(KODE_BARANG), ', ',
    QUOTE(KODE_CUSTOMER), ', ',
    QUOTE(TANGGAL_PEMBELIAN), ', ',
    JUMLAH_PEMBELIAN, ');'
) AS insert_query
FROM pembelian;

.::: Create Table & Insert data using suport Arab & Thailand Character in PostgreSQL EDB :::.

 

Berikut adalah contoh tabel PostgreSQL dan skrip untuk menyisipkan data yang mendukung tulisan Arab, Thailand, Rusia, dan Jepang.

PostgreSQL mendukung berbagai jenis karakter (multilingual) melalui penggunaan encoding UTF-8. Pastikan database Anda menggunakan encoding UTF-8.

Langkah 1: Buat Database dengan Encoding UTF-8
Pastikan database sudah menggunakan UTF-8. Anda dapat memeriksa atau membuatnya dengan:

CREATE DATABASE multilingual_db
WITH ENCODING 'UTF8'
LC_COLLATE='en_US.UTF-8'
LC_CTYPE='en_US.UTF-8'
TEMPLATE template0;

.::: Create Table & Insert data using suport Arab & Thailand Character in MSSQL :::.

 

Untuk membuat tabel di SQL Server yang mendukung tulisan Arab, Thailand, Rusia, dan Jepang, kita perlu menggunakan tipe data NVARCHAR untuk kolom teks. Berikut adalah contoh implementasinya:

1. Membuat Tabel

CREATE TABLE MultibahasaTabel (
    ID INT IDENTITY(1,1) PRIMARY KEY,      -- Kolom ID sebagai Primary Key
    Nama NVARCHAR(100),                   -- Kolom Nama untuk teks multibahasa
    Deskripsi NVARCHAR(255)               -- Kolom Deskripsi untuk teks panjang
);
 

.::: Create Table & Insert data using suport Arab & Thailand Character in MariaDB MySQL :::.

Untuk membuat tabel di MariaDB yang mendukung tulisan Arab dan Thailand, Anda perlu memastikan bahwa kolom teks menggunakan charset dan collation yang mendukung karakter multibahasa, seperti utf8mb4. Charset utf8mb4 mendukung seluruh karakter Unicode, termasuk karakter Arab dan Thailand.

Berikut adalah contoh tabel dan data insert-nya:

Langkah 1: Membuat Database dengan Charset utf8mb4
Pastikan database menggunakan charset yang mendukung tulisan Arab dan Thailand.

CREATE DATABASE multidb CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE multidb;

 

CREATE DATABASE multilingual_db
CHARACTER SET latin1
COLLATE latin1_swedish_ci;
USE multilingual_db;

Langkah 2: Membuat Tabel
Tabel ini akan mendukung karakter multibahasa pada kolom arabic_text dan thai_text.

CREATE TABLE multilingual_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    arabic_text TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
    thai_text TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
    description VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci
);

.::: All Index MariaDB MySQL, Check Index, Size Index, Type Index ALL, ref, eq_ref,PRIMARY, DERIVED, UNION :::.

 

Dari tabel yang Anda lampirkan, berikut adalah penjelasan masing-masing kolom dalam EXPLAIN query MariaDBMySQL

select_type Menunjukkan tipe query, seperti PRIMARY, DERIVED, UNION, dll.
table Nama tabel atau hasil intermediate (seperti derived tables atau union).
type Menunjukkan tipe join atau akses data (ALL, ref, eq_ref, dll).

Berikut contoh query sesuai dengan nilai di kolom select_type dan type

1. PRIMARY + ALL
Query utama yang melakukan full table scan.

SELECT * FROM barang;

explain SELECT * FROM barang;
 

.::: Script Daily Backup Database Oracle using expdp :::.

 

1. create dba directory

https://teguhth.blogspot.com/2024/12/how-to-backup-and-restore-table.html

su - oracle
mkdir -p /home/oracle/backup
mkdir -p  /u01/backup/daily

CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS '/home/oracle/backup';
GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO aisyah;

CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS '/home/oracle/backup';
GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO ADMIN;

CREATE OR REPLACE DIRECTORY BACKUP_DAILY AS '/u01/backup/daily';
GRANT READ, WRITE ON DIRECTORY BACKUP_DAILY TO aisyah;
 

.::: Sample Data Warehouse in SQL Server :::.

Untuk menjadikan query ini sebagai skema data warehouse, tabel-tabel operasional tersebut perlu diubah menjadi tabel fakta dan tabel dimensi.

Skema Data Warehouse
Tabel Dimensi
Dimensi Barang: Mengandung detail tentang barang.
Dimensi Suplier: Mengandung detail tentang suplier.
Dimensi Customer: Mengandung detail tentang customer.
Dimensi Waktu: Mengandung detail waktu untuk analisis.
Tabel Fakta
Fakta Transaksi: Menggabungkan data dari pembelian dan pasok untuk menyimpan fakta transaksi.

correlation https://teguhth.blogspot.com/2019/04/study-kasus-praktis-belajar-query.html

A. Struktur Tabel Dimensi dan Fakta
1. Dimensi Barang

CREATE TABLE dim_barang (
    barang_id INT IDENTITY(1,1) PRIMARY KEY,
    kode_barang CHAR(6) UNIQUE,
    nama_barang VARCHAR(25),
    satuan_barang VARCHAR(20)
);

.::: Sample Data Mart in SQL Server :::.

 

correlation https://teguhth.blogspot.com/2019/04/study-kasus-praktis-belajar-query.html

1. Create table for datamart base on

create table dim_barang(
KODE_BARANG char(6) not null ,
NAMA_BARANG varchar(25),
SATUAN_BARANG varchar(20),
STOK_BARANG decimal(4),
primary key (KODE_BARANG));

create table dim_suplier(
KODE_SUPLIER char(5) not null,
NAMA_SUPLIER varchar(30),
ALAMAT_SUPLIER varchar(30),
KOTA_SUPLIER varchar(15),
TELEPON_SUPLIER varchar(15),
primary key(KODE_SUPLIER));

create table dim_customer(
KODE_CUSTOMER char(6),
NAMA_CUSTOMER varchar(30),
ALAMAT_CUSTOMER varchar(30),
KOTA_CUSTOMER varchar(15),
TELEPON_CUSTOMER varchar(15),
primary key(KODE_CUSTOMER));