Operasi Matrik dengan MySQL

Setelah dapet kesulitan dalam pembuatan matrik dengan array… Ternyata ada terpikir suatu ide penyimpanan matrik dalam tabel database beserta beberapa cara melakukan operasi matrik dari database… ternyata cukup keren.

Dalam kesempatan luang nanti, semoga bisa buat tutorialnya disini…

do’ain za….

invert date in BETWEEN with MySQL

Someday i want to check my software and i get some error with my query when selecting data transaction in range date ’2008-12-05′ and  ’2008-12-01′ (descending)… it’s very surprising when the result is empy (zero).

But when selecting when selecting data transaction in range date ’2008-12-01′ and  ’2008-12-05′ (ascending) i got the result…

So, i want to practice little experiment about this case.

I got employee data in table

CREATE TABLE `employee1` (
`ID` int(2) NOT NULL auto_increment,
`EMPNAME` varchar(30) default NULL,
`ATTENDENCE` varchar(10) default NULL,
`DAT` date NOT NULL default ’0000-00-00′,
PRIMARY KEY  (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

insert  into `employee1`(`ID`,`EMPNAME`,`ATTENDENCE`,`DAT`) values (1,’Girish’,'P’,’2008-12-01′),(2,’Komal’,'A’,’2008-12-02′),(3,’Mahendra’,'P’,’2008-12-03′),(4,’Sandeep’,'A’,’2008-12-04′),(5,’Amit’,'P’,’2008-12-05′);

Query 1:

select EMPNAME FROM employee1 WHERE DAT BETWEEN ’2008-12-01′ and ’2008-12-05′

Query with result’s empty:

select EMPNAME FROM employee1 WHERE DAT BETWEEN ’2008-12-05′ and  ’2008-12-01′

The solution when you want to selecting data with range of date, you can use this query

select EMPNAME
FROM employee1
WHERE DAT <= ’2008-12-05′ and DAT >= ’2008-12-01′

or this query

select EMPNAME
FROM employee1
WHERE DAT >= ’2008-12-01′ and DAT <= ’2008-12-05′

The meaning of VIEW, TRIGGER and STORED PROCEDURE in SQL

View :

A SQL View is a virtual table, which is based on SQL SELECT query. Essentially a view is very close to a real database table (it has columns and rows just like a regular table), except for the fact that the real tables store data, while the views don’t. The view’s data is generated dynamically when the view is referenced. A view references one or more existing database tables or other views. In effect every view is a filter of the table data referenced in it and this filter can restrict both the columns and the rows of the referenced tables. [1]

1.A view is a predefined query on one or more tables.
2.Retrieving information from a view is done in the same manner as retrieving from a table.
3.With some views you can also perform DML operations (delete, insert, update) on the base tables.
4.Views don’t store data, they only access rows in the base tables.
5.user_tables, user_sequences, and user_indexes are all views.
6.View Only allows a user to retrieve data.
7.view can hide the underlying base tables.
8.By writing complex queries as a view, we can hide complexity from an end user.
9.View only allows a user to access certain rows in the base tables.

Advantages of Views

• To restrict data access
• To make complex queries easy
• To provide data independence
• To present different views of the same data

Trigger :

A database trigger is procedural code that is automatically executed in response to certain events on a particular table or view in a database. The trigger is mostly used for keeping the integrity of the information on the database. For example, when a new record (representing a new worker) is added to the employees table, new records should be created also in the tables of the taxes, vacations, and salaries. [2]

Stored Procedure :

A stored procedure is a subroutine available to applications accessing a relational database system. Stored procedures (sometimes called a proc, sproc, StoPro, StoredProc, or SP) are actually stored in the database data dictionary.

Typical uses for stored procedures include data validation (integrated into the database) or access control mechanisms. Furthermore, stored procedures are used to consolidate and centralize logic that was originally implemented in applications. Extensive or complex processing that requires the execution of several SQL statements is moved into stored procedures, and all applications call the procedures. One can use nested stored procedures, by executing one stored procedure from within another. The maximum level of nesting is 32. [3]

[1] http://www.sql-tutorial.com/sql-views-sql-tutorial/

[2] http://en.wikipedia.org/wiki/Database_trigger

[3] http://en.wikipedia.org/wiki/Stored_procedure

[4] http://imdjkoch.wordpress.com/2010/06/21/viewsbasic-concepts/

Query[1] Tunggakan Pembayaran Sistem Informasi Pembayaran SPP

Assalaamualaikum

Query mencari tunggakan pembayaran

Pertanyaan pertama saat membuat sistem informasi pembayaran ialah bagaimana mencari tunggakan dari siswa hanya dari data transaksi yang ada, sedangkan tunggakan ialah data yang belum masuk pada data pembayaran (siswa yang menunggak tidak melakukan transaksi pembayaran) terhitung sejak bulan ini(bulan kemarin) ke belakang.

Misalkan bulan sekarang Juli 2010, maka tunggakan ialah bulan sebelumnya yaitu Juni 2010, Mei 2010, dst… ke belakang.

Sehingga cara yang digunakan ialah membentuk data yang harus dibayar (tunggakan) dengan melakukan relasi tahun ajaran, bulan, serta siswa dengan hasil cartesian product dari ketiga tabel tersebut.

Query Rangking MySQL

Terkadang kita membutuhkan suatu nomor urut dari sekumpulan data pada tabel.

Misalkan :

• Adi punya nilai 85
• Budi punya nilai 65
• Andica punya nilai 60
• Dinie punya nilai 85
• Abel nilai 82

Misalkan nilai tertinggi akan mendapatkan nomor urut (rangking) satu serta bila terdapat nilai yang sama akan memiliki nomor rangking yang sama pula.

• Adi punya nilai 85, rangking 1
• Budi punya nilai 65, rangking 3
• Andica punya nilai 60, rangking 4
• Dinie punya nilai 85, rangking 1
• Abel, rangking 2

Menggunakan nilai field alias pada field lain

Terkadang kita membutuhkan nilai dari field alias untuk digunakan pada field lain pada single query….

Tabel data :

 

 

 

 

Experiment query : (ERROR) Bagaimana solusinya ???? (baca lanjutan ya….)

 

 

 

 

 

 

 

 

Solusinya adalah : Gunakan variabel dalam single query anda ! –>> baca truss …

Hitung Balance query MySQL

SQL Dump

CREATE TABLE `accountdeposit` (
`id` int(11) NOT NULL auto_increment,
`memCode` char(9) NOT NULL,
`transCode` char(100) NOT NULL,
`stat` enum(‘D’,'C’) NOT NULL,
`balance` float(9,3) NOT NULL,
`transDate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`author` int(11) NOT NULL,
PRIMARY KEY  (`id`),
KEY `memCode` (`memCode`)
);

insert  into `accountdeposit`(`id`,`memCode`,`transCode`,`stat`,`balance`,`transDate`,`author`) values (5,’0321001′,’Buka Account’,'C’,100000.000,’2009-03-21 00:05:44′,1),(6,’0321001′,’open list 3 Form’,'D’,15000.000,’2009-03-23 10:51:16′,1),(7,’0321001′,’open list 4 Form’,'C’,20000.000,’2009-03-23 13:05:00′,1);

Query:

select transCode,
case when stat=’D’ then (-balance) else 0 end as Debit,
case when stat=’C’ then balance else 0 end as Credit,
(@LB := @LB + if (stat=’D', -balance, balance)) as LastBalance
from (select @LB := 0) as dddd, accountdeposit
where memCode = ’0321001′
group by memcode, transdate
order by transdate;

Hasil :

 

 

 

Thnks.2.Manztiara n NorYahya

Flow Control MySQL – IF

Dump

CREATE TABLE `users` (
`nama` varchar(255) NOT NULL,
`gender` enum(‘L’,'P’) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

insert  into `users`(`nama`,`gender`) values (‘Joe’,'L’),(‘Jane’,'P’);

Query :

SELECT *, CONCAT(IF(gender = “L”, “Pak “, “Bu “), nama )
AS fullName
FROM users

Hasil :

 

 

Thnks.2.Ria Coder

Melihat waktu eksekusi Query MySQL dengan Profiler

Cara mengetahui waktu ekseskusi beberapa query yg pernah kita jalankan salah satunya degan menggunakan perintah profiler

follow this link

Rolling SUM data MySQL – Tabungan

terinspirasi oleh tulisan yaya_retina and special thanks 2 yanto

Rolling SUM ialah suatu teknik penjumlahan yang berkelanjutan dari satu baris record kemudian ditambah kembali dengan data record selanjutnya. Implementasinya seperti buku tabungan waktu kita SD dulu… hehehhe
Ilustrasi ….
+————+———+————-+
| Tanggal | Nominal | Total   |
+————+———+————-+
| 2007-01-31 | 50000 | 50000 |
| 2007-02-28 | 30000 | 80000 | <—– 5000 + 3000 = 8000
| 2007-03-31 | 10000 | 90000 | <—– 8000 + 10000 = 18000
| 2007-04-30 | 60000 | 150000 |<—– dst
| 2007-05-31 | 30000 | 180000 |
| 2008-01-31 | 50000 | 230000 |
| 2008-02-29 | 30000 | 260000 |
| 2008-03-31 | 90000 | 350000 |
| 2008-04-30 | 30000 | 380000 |
| 2008-05-31 | 50000 | 430000 |
+————+———+————-+

Untuk eksprerimen anda butuh beberapa tabel and data berikut :

CREATE TABLE `acbalance` (
`id` int(11) NOT NULL auto_increment,
`accountID` int(5) default NULL,
`acPost` date default NULL,
`balance` double(15,0) default NULL,
PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

/*Data for the table `acbalance` */

insert  into `acbalance`(`id`,`accountID`,`acPost`,`balance`) values

(1,3,’2007-01-31′,50000),(2,3,’2007-02-28′,30000),

(3,3,’2007-03-31′,10000),(4,3,’2007-04-30′,60000),

(5,3,’2007-05-31′,30000),(6,3,’2008-01-31′,50000),

(7,3,’2008-02-29′,30000),(8,3,’2008-03-31′,90000),

(9,3,’2008-04-30′,30000),(10,3,’2008-05-31′,50000);

Query :

select a.accountID, a.acPost, a.balance, sum(b.balance) as LastBalance
from acbalance a join acbalance b on a.acPost>=b.acPost
group by a.accountID,a.acPost,a.balance

Hasil :