Kumpulan Query

Cobalah membaca skrip saya dengan cepat.
Kecepatan membuat sketsa untuk mempermudah pemahaman dimasa yang akan datang
Itulah tujuan Query saya..

Jika tidak ada SQL SERVER 2008 nya
Silahkan Download terlebih dahulu  di Menu Download yaa..

Langkat – langkahnya adalah sebagai berikut :
1.aktifkan mysql_d nya melalui start/program/phptriad/mysql/mysql_d
2.start/run
3.dirun ketik cmd
4.cd \
5.cd apache\mysql\bin
6.mysql
7.show databases;//untuk melihat semua database yag telah dibuat
8.create database stmik;//untuk membuat database stmik
9.use stmik;//mengedit database stmik
10.create table mahasiswa(
-nim varchar(10),
-nama varchar(50),
-jurusan varchar(30),
-angkatan varchar(4));//untuk membuat table mahasiswa dengan 4 field/kolom
-insert into mahasiswa values(‘0711482′,’efori’,’TI’,’2007′);//untuk mengisi table
-select *from mahasiswa;//untuk melihat isi table
-delete from mahasiswa where nip=’0711482′; //menghapus isi tabel dengan nip=0711482
-upade mahasiswa set nama=’efori buulolo’ where nip=’0711482′;//mengedit nama
-desc mahasiswa;//untuk melihat gambaran table
-show tables;//untuk melihat table dalam satu database;
insert into barang (no,nama) values ('1','sance');
Contoh saya:
use sance;
select * from mahasiswa;
create table mahasiswa1(no int primary key,nama varchar(20) );
insert into mahasiswa1 values('1','sance');
insert into mahasiswa1 values('2','andi');
select * from mahasiswa1 where no='2';
delete from mahasiswa1 where nama='andi';
update mahasiswa1 set nama ='sance2' where no='2';
select * from mahasiswa1;


create database sance2;
create database sance3;
use sance2;
create table mahasiswa1(no int primary key,nama varchar(20));
create table mahasiswa2(no int primary key,nama varchar(20));
create table mahasiswa3(no int primary key,nama varchar(20));
select * from mahasiswa1;
insert into mahasiswa1 values('1','sance');
insert into mahasiswa1 values('2','andi');
insert into mahasiswa1 values ('3','babakan wanajaya');

insert into mahasiswa1 values ('4','balabala');
insert into mahasiswa1 values ('5','bk');
select * from mahasiswa1 where no='1';
update mahasiswa1 set nama='abdurrahman sance' where no='1';
select * from mahasiswa1 where no='2';
update mahasiswa1 set nama='abdurrahman assidiq' where no='2';
select * from mahasiswa1 where no='3';
update mahasiswa1 set nama='abdurahman' where no='3';
select * from mahasiswa1 where no='4';
update mahasiswa1 set nama='abrurahmana' where no='4';
select * from mahasiswa1;
delete from mahasiswa1 where no='5';
delete from mahasiswa1 where no='4';
delete from mahasiswa1 where no='3';
delete from mahasiswa1 where no='2';
delete from mahasiswa1 where no='1';
alter table mahasiswa add jumlah_barang2 integer;
untuk melihat database yang disimpan  :
Clik kann database kemudian properties -> file

Lihat https://efori.wordpress.com/2011/02/25/cara-membuat-database-di-mysql/
http://achulblog.blogspot.co.id/2012/05/cara-insert-update-delete-database-sql.html
alter table mahasiswa add bebelak varchar(20);
alter table mahasiswa add belakqang int;

select * from ItemLedgerEntry
select SUM (Quantity) from ItemLedgerEntry
select CustomerCode,SUM (Quantity) from ItemLedgerEntry Where Postingdate <= '2015-05-07' group by CustomerCode
select * from EntryType
select * from item;
select CustomerCode,Postingdate,SUM (Quantity) from ItemLedgerEntry where postingdate <= '2015-05-07'
group by CustomerCode,PostingDate;

select * from items where ItemCode='J111300002'
select * from item where ItemCode='J111300002'
select * from ItemZonePrice where ItemCode='J111300002'
select * from ItemLedgerEntry where EntryType in('7','15')
select * from ItemLedgerEntry where EntryType between 7 and 15
select * from EntryType left join ItemLedgerEntry on
EntryType.EntryID=ItemLedgerEntry.EntryType
select EntryType,CustomerCode  from ItemLedgerEntry
select SUM(Quantity) from ItemLedgerEntry
select CustomerCode,Createdby,SUM(EntryType) from ItemLedgerEntry where
PostingDate <='2016-02-26' group by CustomerCode,Createdby
select CustomerCode from ItemLedgerEntry


<?php
$host="localhost";
$user="root";
$password="";
$db="dt";
$koneksi=mysql_connect($host,$user,$password);
mysql_select_db($db,$koneksi);

$sql="select * from siswa";
$tampil=mysql_query($sql);
while($data=mysql_fetch_array($tampil))
{
echo "<p>Nama  :</p>"; 
}

?>

select * from DeliveryOrder
select * from DeliveryOrderDetail
select * from ItemLedgerEntry

select * from PurchaseReceipt
select * from PurchaseReceiptDetail
select * from ItemLedgerEntry


DOSM-CK-GCVV00-25
select * from DeliveryOrder where DocumentNO='DOSM-CK-GCVV00-25'
select * from DeliveryOrderDetail where DeliveryOrderTransNo='DO-CK-GCVV00-28'
select itemCode,SUM(Quantity) from DeliveryOrder
inner join DeliveryOrderDetail
on  DeliveryOrder.TransactionNo=DeliveryOrderDetail.DeliveryOrderTransNo
where DocumentNo='DOSM-CK-GCVV00-25'
Group By ItemCode


select * from ItemLedgerEntry where DocumentNo='DOSM-CK-GCVV00-25'
select SUM(Quantity) from ItemLedgerEntry where DocumentNo='DOSM-CK-GCVV00-25'

select SUM(Quantity) from DeliveryOrderDetail  where DocumentNo='DOSM-CK-GCVV00-25'

Select COUNT(TransactionNo) From ItemLedgerEntry
Where Quantity = 1




select * from EntryType
select * from ItemLedgerEntry where EntryType ='7'

89322
select Count(EntryType) from ItemLedgerEntry where EntryType='7'
select SUM(EntryType) from ItemLedgerEntry where EntryType='7'


DeliveryOrder => pengiriman barang
Jumlah Quantity ,percustomer,sampai ndg tanggal 31 Desedmber

select * from DeliveryOrder where PostingDate >='2014-06-25'

select CustomerCode ,SUM(Quantity) from ItemLedgerEntry where entrytype ='15' and postingdate <='2016-12-31'group by customercode
select * from ItemLedgerEntry
===================
1.
-pegiriman barng DeleveryOrder EI =1
-Quantiti percustomer (group by CustmerCode)
-sampy 31 des 2016
============================================
select CustomerCode,SUM(Quantity) from ItemLedgerEntry where
EntryType='1' and PostingDate <='2016-12-31' group by CustomerCode
select * from EntryType
select * from ItemLedgerEntry where Postingdate='2016-12-31'


=======================
---no1.done
select CustomerCode,SUM(Quantity) from ItemLedgerEntry where
EntryType='1' and PostingDate <='2016-12-31' group by CustomerCode
select * from DeliveryOrder
======================
--No2.
--group by Customer jmlah Quantity dari 2015
select * from Entrytype
DeliveryReceipt EI=2
select * from ItemLedgerEntry where ItemCode='ZK200'
select * from deliveryOrderdetail where ItemCode='ZK200'
select * from ItemLedgerEntry
--done
select CustomerCode,SUM(Quantity) From ItemLedgerEntry where
EntryType='2' and ItemCode='ZK200' and PostingDate >='2015-01-01'group by CustomerCode

===============================

--no 3
--SELECT TOP 5 NmTable.Kd_Brg,Sum(NmTable.Qty) as Tot_Qty FROM NmTable group by Kd_Brg order by Tot_Qty
SELECT TOP 5 NmTable.Kd_Brg,Sum(NmTable.Qty) as Tot_Qty FROM NmTable
group by Kd_Brg order by Tot_Qty
select top 5
select * from salesorderheader where TransDate='2014-06-21'

SELECT barang.`nama_barang`,SUM(jumlah) AS 'total_terjual'
FROM detail_trans_jual,barang
WHERE detail_trans_jual.`kode_barang`=barang.`kode_barang`
GROUP BY (nama_barang)
ORDER BY (total_terjual) DESC LIMIT 5;


SELECT CustomerCode, COUNT(CustomerCode) AS GrandTotal
From salesorderheader
INNER JOIN fromCustomerCode ON transaksi_detail.'Kd_barang'=catalog.'Kd_barang'
GROUP BY Kd_barang
ORDER BY total_terjual DESC LIMIT 5
select FromCustomerCode,SUM(TotalReceivable) as 'TotalReceivable' from SalesOrderHeader group by fromCustomerCode
order by (TotalReceivable) DESC LIMIT 5;

==============================
--pengiriman barang = Delivery order =>buat table pengirimn
--jumlah quantity   = DeliveryOrderDetail
--dikirim percustomer = DeliveryOrder
--sampai tanggal 31 Des 2016 <  ===DeliveryOrder

select * from DeliveryOrderDetail
select FromCustomerCode,Sum(Quantity) from DeliveryOrder inner join DeliveryOrderdetail
on DeliveryOrder.TransactionNo=DeliveryOrderDetail.TransactionNo where PostingDate <= '2016-12-31'
group by FromCustomerCode 
=====
--1
select FromCustomerCode,SUM(Quantity) from DeliveryOrder
inner join DeliveryOrderDetail
on  DeliveryOrder.TransactionNo=DeliveryOrderDetail.DeliveryOrderTransNo
where PostingDate < '2017-01-01'
Group By FromCustomerCode
--1 done
----
ve
select * from Deliveryreceipt where TransactionNo='DOR-BF-K-VVBM-1'
select * from DeliveryReceiptDetail where ItemCode='ZK200'
select FromCustomerCode,Sum(Quantity) from DeliveryReceipt inner join
DeliveryReceiptDetail on DeliveryReceipt.TransactionNo=DeliveryReceiptDetail.DeliveryReceiptTransNo
where ItemCode='ZK200' and PostingDate between '2014-12-31' and '2016-1-1'
 group by FromCustomerCode
--no 2.

--penerima barang--
--ZK200
--Percustomer--
--Quantity--
-- > dari tahun 2015

select * from PurchaseReceipt where TransactionNo='LPB-BF-GVV00-1'
select * from PurchaseReceiptDetail where PurchaseReceiptTransNo='LPB-BF-GVV00-1'
select * from ItemLedgerEntry

select CustomerCode,SUM(Quantity) from PurchaseReceipt inner join
PurchaseReceiptDetail on purchaseReceipt.TransactionNo=PurchaseReceiptDetail.PurchaseReceiptTransNo
where ItemCode='ZK200' and PostingDate between '2014-12-31' and '2016-1-1'
group by CustomerCode
--done no 2
======================
--no 3
select * from SalesOrderHeader Where TransactionNo='20140620-K-VVMM3-00001' --penjualan
select * from SalesOrderDetail where itemCode='PN-SS'

select * from SalesInvoiceHeader where TransactionNo='2014-06-21 00:00:00.000'
select * from SalesReturnHeader
---barang nama  detail
--quantity   detail
-- tahun 2016  header

===================
--No 3
SELECT top 5 ItemCode, SUM(Quantity) AS total
From SalesOrderHeader
INNER JOIN SalesOrderDetail ON SalesOrderHeader.TransactionNo=SalesOrderDetail.SalesOrderTransNo
where PostingDate between '2015-12-31' and '2017-1-1'
GROUP BY itemCode
ORDER BY total DESC


select * from EntryType


select * from ItemLedgerEntry
select SUM(Quantity) from ItemLedgerEntry

select CustomerCode, SUM(Quantity) from itemledgerentry where PostingDate <= '2016-12-31'
Group By CustomerCode


select CustomerCode, PostingDate, SUM(Quantity) from itemledgerentry where PostingDate <= '2016-12-31'
Group By CustomerCode, PostingDate

select CustomerCode ,SUM(Quantity) from ItemLedgerEntry where entrytype ='15' and postingdate <='2016-12-31'group by customercode

select customercode,sum (quantity) from itemledgerentry where

--1
select FromCustomerCode,SUM(Quantity) from DeliveryOrder
inner join DeliveryOrderDetail
on  DeliveryOrder.TransactionNo=DeliveryOrderDetail.DeliveryOrderTransNo
where PostingDate < '2017-01-01'
Group By FromCustomerCode
--1 done

--2

select FromCustomerCode,Sum(Quantity) from DeliveryReceipt inner join
DeliveryReceiptDetail on DeliveryReceipt.TransactionNo=DeliveryReceiptDetail.DeliveryReceiptTransNo
where ItemCode='ZK200' and PostingDate between '2015-01-01' and '2016-01-01'
 group by FromCustomerCode
---3

SELECT top 5 ItemCode, SUM(Quantity) AS total
From SalesOrderHeader
INNER JOIN SalesOrderDetail ON SalesOrderHeader.TransactionNo=SalesOrderDetail.SalesOrderTransNo
where PostingDate between '2016-01-01' and '2017-1-1'
GROUP BY itemCode
ORDER BY total DESC



select * from Items where ItemCode = 'J111300002'
select * from ItemByLocation Where ItemID = '95073'
==>cek unit price

xxxxx
select * from Item Where ItemCode = 'J111300002'
select * from SpecialItem where ItemID = '95073'


--1
select FromCustomerCode,SUM(Quantity) from DeliveryOrder
inner join DeliveryOrderDetail
on  DeliveryOrder.TransactionNo=DeliveryOrderDetail.DeliveryOrderTransNo
where PostingDate < '2017-01-01'
Group By FromCustomerCode
--1 done

--2

select FromCustomerCode,Sum(Quantity) from DeliveryReceipt inner join
DeliveryReceiptDetail on DeliveryReceipt.TransactionNo=DeliveryReceiptDetail.DeliveryReceiptTransNo
where ItemCode='ZK200' and PostingDate between '2015-01-01' and '2016-01-01'
 group by FromCustomerCode
---3

SELECT top 5 ItemCode, SUM(Quantity) AS total
From SalesOrderHeader
INNER JOIN SalesOrderDetail ON SalesOrderHeader.TransactionNo=SalesOrderDetail.SalesOrderTransNo
where PostingDate between '2016-01-01' and '2017-1-1'
GROUP BY itemCode
ORDER BY total DESC






For Sql
A
AA
AAA
AAAA
AAAAA
AAAA
AAA
AA
A

Declare @A Varchar(8000)
Declare @B INT
Declare @C INT
Declare @D Varchar(8000)
Declare @F INT
Set @A = ''
Set @B = 1
set @C = 9
set @D = ''
set @F = 1
While(@B <= 10)
   Begin
   set @D = @D + 'A'
   Select @D
   Set @B = @B + 1
   End

While(@F <= 10)
while(@C >=1)
Begin 
      Set @A = @A + @D     
select substring(@A,1,@C)
Set @F = @F + 1
        Set @C = @C -1
End
=======================================

A
AA
AAA
AAAA
AAAAA
AAAAAA

Declare @A Varchar(8000)
Declare @B INT
Set @A = ''
Set @B = 1

While(@B <= 10)
Begin
Set @A = @A + 'A'
select @A
Set @B = @B + 1
End

============================================
A
AA
AAA
AA
A

Declare @B INT
Declare @D Varchar(8000)
Set @B = 1
set @D = ''
--@B 1+9=10
While(@B <= 9)
   Begin
   set @D = @D + 'A'
   Select @D
   Set @B = @B + 1
   End

--D=10A-A=9A
while(@B >=1)
Begin 
      Set @D = @D + 'A'   
select substring(@D,1,@B)
Set @B = @B -1
End
====================================
Nambahin Dan Nilai 0


select Nama,isnull(sum(jumlah),0) from dbtess
group by Nama
Order by sum(jumlah)
========

select B.CustomerCode,ISNULL(SUM(C.Quantity),0) as Quantity From AdjustmentHeader A
left join Customer B   ON B.CustomerCode=A.CustomerCode
left join AdjustmentDetail C ON C.AdjustmentTransactionNo=A.TransactionNo
Where A.FinaReleasedDate between '2016-12-01' and '2017-01-01'
Group by B.CustomerCode,A.FinareleasedDate
Order by ISNULL(SUM(C.Quantity),0) Desc

=====================================

8 January 2018

create database dbSance
use dbSance
create table tbLogin(
id int primary key identity(1,1),
name varchar (50),
password varchar(50),
status smallint,
)

insert into tbLogin(name,password,status)values('sance','123456',1)
insert into tbLogin(name,password,status)values('shin','123456',2)
insert into tbLogin(name,password,status)values('mila','123456',2)
insert into tbLogin(name,password,status)values('mili','123456',1)

Select * from tbLogin
Select * from tbLogin
order by id Desc
set identity_insert tbLogin on//mengaktipkan add data
insert into tbLogin(id,name,password,status)values(7,'mili','123456',1)
set Identity_insert tbLogin off//menonaktipkan add data

insert into tbLogin(id,name,password,status)values(6,'mili','123456',1)

=====================================

17 january 2018

--merename column
sp_rename 'tbInsert.[id]','id_name','column'
--menambah column
alter table tbInsert add ket varchar(50) 
--merubah type data column
alter table tbInsert alter column ket int;
--menghapus colum
alter table tbInsert Drop column ket

--mereset nilai identity/mengembalikan nilai identity ke 1

DBCC CHECKIDENT('tb_data', RESEED, 0)

=====================================

QUERY

1.DB NUL Ganti jam dan End balance