===================================
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//mengaktifkan untk menmbah
insert into tbLogin(id,name,password,status)values(7,'mili','123456',1)
set Identity_insert tbLogin off//menonaktifkan untuk menambah
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)
====================================
New
=> insert into mahasiswa2
Select nama,alamat,GETDATE() as date from mahasiswa
where id between 1 and 5
blum dipindahin ke blog satunya
====================================
blog datanya terkonkesi dengan
http://phpsance.blogspot.com/2018/01/kumpulan-query.html
QUERY
1.DB null ganti jam dan terlanjur end balance
