.::: Create table Inventory Datawarehouse SQL Server Original (Converting script from SQL Studio :::.

A. Create Database & Table 
create database teguhth
go

1. Table barang

-- table barang with index
CREATE TABLE [dbo].[barang](
[KODE_BARANG] [char](6) NOT NULL,
[NAMA_BARANG] [varchar](25) NULL,
[SATUAN_BARANG] [varchar](20) NULL,
[STOK_BARANG] [decimal](4, 0) NULL,
PRIMARY KEY CLUSTERED ([KODE_BARANG] ASC))

.::: How To Add Column, Update, Insert & Delete row table on SQL example MariaDB :::.

1. Check original table Barang

2. Add Column table Barang (add KETERANGAN)

3. update row table ( update/fill column keterangan)

4. delete row table (example delete KODE_BARANG='ELK-99')

5. Modify Column table ( example change ELK02-ket to ELK02-ketvv2)

6. drop column on table (example delete/drop column KETERANGAN)

1. Check original table Barang

syntax

mysql -u root -p

show tables;

desc barang;

select * from barang;

.::: Study Kasus Praktis belajar Query create & insert table Postgress SQL (Create Data Warehouse) include via CLI & GUI :::.

1. Create database using query or GUI

CREATE DATABASE tgh

WITH

OWNER = postgres

ENCODING = 'UTF8'

LC_COLLATE = 'English_United States.1252'

LY_TYPE = 'English_United States.1252'

TABLE_SPACE = pg_default

CONNECT LIMIT = -1 ;

GRANT CREATE, CONNECT ON DATABASE tgh TO postgres;

GRANT TEMPORARY ON DATABASE tgh TO postgres WITH GRANT OPTION;

GRANT TEMPORARY, CONNECT ON DATABASE tgh TO PUBLIC ;

.::: Study Kasus Praktis belajar Query create & insert table Microsoft SQL Server (Create Data Warehouse) :::.

Microsoft SQL Server is a relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications—which may run either on the same computer or on another computer across a network (including the Internet).



A. Management User
1. Loging UsingMicrosoft Studio

.::: How To Mirroring Microsoft SQL Server 2012, 2014, 2016, 2018 & Trouble shoot SQL Server Error 1418, 1475,1478,1412 :::.:::

sample monitoring https://teguhth.blogspot.com/2023/09/monitor-mirroring-sql-server-using-t.html

 

Prerequisites

    For a mirroring session to be established, the partners and the witness, if any, must be running on the same version of SQL Server.

    The two partners, that is the principal server and mirror server, must be running the same edition of SQL Server. The witness, if any, can run on any edition of SQL Server that supports database mirroring.

.::: Create Database, Table and Entry Data, Check Database Size on Microsoft SQL Server 2012 Using TSQL :::.

When working with a command or query window, the language used by SQL Server is TSQL.

Step 1 -Create Database

Create a new database called teguhtrihartodb by entering the following TSQL then pressing the “Execute” button on the top menu.

CREATE DATABASE teguhtrihartodb;

GO

The Messages window on the bottom of the screen should say, ”Command(s) completed successfully.”

.::: How to Create Tablespace & Check Capacity, Add datafile On existing tablespace, Change Autoextend :::.

A tablespace is a container for segments (tables, indexes, etc). A database consists of one or more tablespaces, each made up of one or more data files. Tables and indexes are created within a particular tablespace. Oracle has a limit of 64,000 data files per database.

1. Create new tablespace, create user, grant previledge

[oracle@TeguhLabOracle ~]$ more Teguhdatatgh.sql

CREATE TABLESPACE Teguhdata DATAFILE '$ORACLE_BASE/oradata/tgh/Teguhdata.dbf' size 100M AUTOEXTEND ON;

.::: Change Mirror Server Instance to Single User/Server on SQL Server 2012,2014,2016 :::.

1. Open SQL using SQL Management Studio

2. Open Mirror Server Instance and query and execute
alter database teguhtrihartodb set partner off
go
restore database teguhtrihartodb with recovery
go

3. expand Database on SQL Management Studio

.::: Study Kasus Praktis belajar Query create & insert table MySQL / MariaDB (Create Data Warehouse) :::.

MariaDB merupakan versi pengembangan terbuka dan mandiri dari MySQL. Sejak diakuisisinya MySQL oleh Oracle pada September 2010, Monty Program sebagai penulis awal kode sumber MySQL memisahkan diri dari pengembangan dan membuat versi yang lebih mandiri yakni MariaDB. .


A. Management User
1. Loging Using root

create database teguht;
create user 'teguh' identified by 'triharto';
grant all privileges on teguht.* to 'teguh'@'%' identified by 'triharto' with grant option;
FLUSH PRIVILEGES;

c:\Program Files\MariaDB 10.1\bin>mysql -u root -p
Enter password: ****
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2752
Server version: 10.1.14-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> create database teguht;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create user 'teguh' identified by 'triharto';

.::: Definition : Oracle Database SQL Certified Expert & ID Card & Syllabus :::.

Oracle Database SQL Certified Experts demonstrate the complete set of skills required for working with the powerful SQL programming language and have mastered the key concepts of a relational database. SQL Experts understand how to use the advanced features of SQL in order to query and manipulate data within the database, control privileges at the object and system level, and use advanced querying and reporting techniques. They are able to manipulate large data sets and understand storing and retrieving dates according to different time zones. They are also knowledgeable about the concepts of controlling access and privileges for schema objects.

Retrieving Data Using the SQL SELECT Statement

• List the capabilities of SQL SELECT statements  
• Execute a basic SELECT statement 
• Describe how schema objects work

.::: Study Kasus Praktis belajar SQL create & insert table Setelah Install Oracle Database (Create Data Warehouse):::.

SQL atau Structured Query Language) adalah sebuah bahasa yang digunakan untuk mengakses data dalam basis data relasional. Bahasa ini secara de facto merupakan bahasa standar yang digunakan dalam manajemen basis data relasional. Saat ini hampir semua server basis data yang ada mendukung bahasa ini untuk melakukan manajemen datanya.

anda dapat juga download The Study Case & solution Lab Oracle & SQL Expert 

A. Management User
1. Loging Using system or sys as sysdba

C:\Users\teguh.triharto>sqlplus

.::: How To Using Aljabar(Algebra): union(gabungan), intersection(irisan), distinct (proyeksi), join/inner join/normal join/equal join(=),left outer join and right outer join In SQL :::.

Algebra (from Arabic al-jebr meaning "reunion of broken parts") is the branch of mathematics concerning the study of the rules of operations and relations, and the constructions and concepts arising from them, including terms, polynomials, equations and algebraic structures. Together with geometry, analysis, topology, combinatorics, and number theory, algebra is one of the main branches of pure mathematics. Algebra has numerous usages in daily life and is commonly taught in public schools.

.::: How To Using Query, Sub Query from one table or More in SQL :::.

=== 6. Query  and view ===
Query sebutan atau nama lain dari seleksi data yang merupakan bagian dari DML
Query as Selection data that one of DML


You can download The Study Case & solution Lab Oracle & SQL Expert

 == 6.1 seleksi satu tabel ==
== Selection from One Table ==
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
SELECT   field 1,...,field n, agregation function
FROM     table
WHERE      criteria
GROUP BY field 1,...,field n
HAVING      criteria_agregate_function
ORDER BY field
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

.::: How To Using min, max, avg, sum, count, Group by, order by, Having, where, comparison (=,<>,>,<,>= atau <=), Between, In, like / not like on Grouping, Sorting & Criteria Data In SQL Oracle :::.

=== Pengelompokan, pengurutan dan kriteria Data ===
=== How To Grouping, Sorting & Criteria Data ===

You can download The Study Case & solution Lab Oracle & SQL Expert 

== 5.1 pengelompokan data (group by) dan aggregation function ==
Sintak SQL
== 5.1.1 min() ==

 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
SELECT field 1,...,field n, MIN(name_field)
    FROM TABLE
    GROUP BY field 1,...,field m
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
== 5.1.2 max() ==
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
SELECT field 1,...,field n, MAX(name_field)
    FROM TABLE
    GROUP BY field 1,...,field m
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

.::: How To Insert, Update, Delete Data In DML(Data Manipulation language) SQL :::.

A data manipulation language (DML) is a family of syntax elements similar to a computer programming language used for inserting, deleting and updating data in a database. Performing read-only queries of data is sometimes also considered a component of DML

You can download The Study Case & solution Lab Oracle & SQL Expert 

=== 4.1 Memasukkan Data (Insert Data) ====
Sintak SQL
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
INSERT INTO name_table(field 1,...,Field n)
    VALUE(value_field 1,...,field n)
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

= Check Data Table =
select * from barang;
select * from suplier;
select * from customer;
select * from pasok;
select * from pembelian;

SQL> select * from barang;

.::: How To Create, Modify, Drop, Delete Table In DDL (Data Definition Language) SQL Oracle :::.

Cara Membuat, Memodifikasi, Menghapus Tabel dalam DDL

SQL adalah Structured Query Language. secara garis besar terdiri dari

1. DDL (Data Definition Language)

2. DML (Data Manipulating Language)

3. DCL (Data Control Language)

You can download The Study Case & solution Lab Oracle & SQL Expert 

1. Study Kasus = Inventory

Tabel Barang = Menggambarkan entitas barang dan terdiri atas atribut yang berkaitan dengan entitas barang. Atribut barang meliputi : kode_barang,nama_barang,satuan_barang dan stok barang

Tabel Suplier = Menggambarkan entitas suplier dan terdiri atas atribut atribut yang berkaitan dengan suplier. Atribut suplier meliputi : kode_suplier, nama_suplier, alamat_suplier, kota_suplier dan telepon_suplier

.::: How To Create / Delete User Account In PL / SQL Oracle, Microsoft SQL Server :::.

Question;

1. How to create / delete user in Sql / Oracle?
      <sample user : teguh  , password : triharto >
2. How can I set privilages while create a new user?
    <access to/from SQL>
3. How can I view all forms / tables?
4. How can I list the users already got account in Sql?
5. Check Privilage user ?
6. delete/drop role ?
7. Which is the best web site to learn oracle (for beginners)?

You can download The Study Case & solution Lab Oracle & SQL Expert