Kelir…

17/05/2008

Penggunaan query pada database apotik : jawaban

Filed under: Kuliah — mrtosa @ 12:24

1.
select to_char(j.tgl_penjualan, ‘MM’) as nomer,
to_char(j.tgl_penjualan, ‘MONTH’) as bulan,
to_char(j.tgl_penjualan, ‘YYYY’) as tahun,
sum(j.total_penjualan) as pemasukan,
sum(b.total_harga) as pengeluaran,
sum(j.total_penjualan) – sum(b.total_harga) as selisih,
decode(sign(sum(j.total_penjualan) – sum(b.total_harga)), -1, ‘Rugi’, 1, ‘Untung’, 0, ‘Impas’) “UNTUNG/RUGI”
from penjualan j, pembelian b
where to_char(j.tgl_penjualan, ‘YYYY’) = ‘2008’
and to_char(b.tanggal_pembelian, ‘YYYY’) = ‘2008’
and to_char(j.tgl_penjualan, ‘YYYY’) = to_char(b.tanggal_pembelian, ‘YYYY’)
group by to_char(j.tgl_penjualan, ‘MM’),
to_char(j.tgl_penjualan, ‘MONTH’),
to_char(j.tgl_penjualan, ‘YYYY’)
order by to_char(j.tgl_penjualan, ‘MM’)

2.
select p.nama_pegawai, s.hari_shift, s.waktu_shift
from jadwaljaga j, shiftjaga s, pegawai1 p,
(select nq.id, nq.jumlah
from (select j.id_pegawai as id, count(id_pegawai) as jumlah
from jadwaljaga j
group by j.id_pegawai) nq
where nq.jumlah = (select min(nq.jumlah)
from (select j.id_pegawai as ID,
count(id_pegawai) as jumlah
from jadwaljaga j
group by j.id_pegawai) nq
)
) nq
where j.id_pegawai = p.id_pegawai
and j.id_shift = s.id_shift
and nq.id = j.id_pegawai

3.
select * from
(select x.id_obat, o.nama_obat, sum(x.sisa) as jumlah,
s.jumlah_stock – sum (x.sisa) + sum(y.sisa) as sisa
from
(select o.id_obat, sum(rj.jumlah) as sisa
from rincianpenjualan rj, obat o
where rj.id_obat = o.id_obat
group by o.id_obat)x,
(select o.id_obat, sum(rb.jumlah) as sisa
from rincianpembelian rb, obat o
where rb.id_obat = o.id_obat
group by o.id_obat)y, stockobat s, obat o
where x.id_obat = y.id_obat
and s.id_obat = x.id_obat
and o.id_obat = s.id_obat
group by x.id_obat, s.jumlah_stock, o.nama_obat)
where rownum < 6
order by jumlah desc

4.
select pembeli, total_penjualan
from penjualan
where to_char(tgl_penjualan,’MM-YYYY’) = ’01-2008′
group by total_penjualan, pembeli
having total_penjualan > (select avg(total_penjualan) from penjualan where to_char(tgl_penjualan,’MM-YYYY’) = ’01-2008′)

5.
select o.nama_obat, s.kadaluarsa, (s.jumlah_stock+sum(y.sisa)-sum(x.sisa)) as sisa
from obat o,stockobat s,
(select o.id_obat, sum(rj.jumlah) as sisa
from rincianpenjualan rj, obat o
where rj.id_obat = o.id_obat
group by o.id_obat)x,
(select o.id_obat, sum(rb.jumlah) as sisa
from rincianpembelian rb, obat o
where rb.id_obat = o.id_obat
group by o.id_obat)y
where s.id_obat = o.id_obat
and x.id_obat = s.id_obat
and y.id_obat = s.id_obat
and s.kadaluarsa < sysdate+7
and s.kadaluarsa > sysdate
group by o.nama_obat, s.kadaluarsa, s.jumlah_stock

6.
select p.nama_pegawai, a.jumlah_absensi, b.jumlah_shift*4 as jumlah_shift, a.jumlah_absensi/(b.jumlah_shift*4)*100 as persentase
from
(select p.id_pegawai as id, count(ab.id_absensi) as jumlah_absensi
from absensi ab, pegawai1 p
where ab.id_pegawai = p.id_pegawai
and to_char(ab.tgl_absensi,’MM’) = ’01’
and to_char(ab.tgl_absensi,’YYYY’) = ‘2008’
group by p.id_pegawai) a,
(select p.id_pegawai as id, count(j.id_shift) as jumlah_shift
from pegawai1 p, shiftjaga s, jadwaljaga j
where p.id_pegawai = j.id_pegawai
and j.id_shift = s.id_shift
group by p.id_pegawai
)b, pegawai1 p
where b.id = a.id
and p.id_pegawai = a.id
and a.jumlah_absensi / (b.jumlah_shift*4)*100 < 80

7.
select nama_pegawai, notelp_pegawai from pegawai1 where notelp_pegawai like ‘0856%4%’

8.
select * from(
select nama_supplier, id_supplier, count(id_supplier) as banyak
from(
select s.nama_supplier, s.id_supplier
from supplier s, pembelian p
where s.id_supplier = p.id_supplier)
group by id_supplier, nama_supplier
order by (banyak) desc)
where rownum < 2

9.
select nq2.nama as nama_pegawai, nq2.jumlah as Jumlah_Pembayaran
from (
select nq.nama as nama, sum(nq.jumlah) as jumlah
from (
select p.nama_pegawai as nama, j.total_penjualan as jumlah
from pegawai1 p, penjualan j, rincianpenjualan rj, obat o, golonganobat go
where p.id_pegawai = j.id_pegawai
and j.id_penjualan = rj.id_penjualan
and o.id_obat = rj.id_obat
and o.kode_gol = go.kode_gol
and o.kode_gol like ‘A’
) nq
group by nq.nama
order by sum(nq.jumlah) desc
) nq2
where nq2.jumlah=(
select max(nq2.jumlah)
from (
select nq.nama as nama, sum(nq.jumlah) as jumlah
from (
select p.nama_pegawai as nama, j.total_penjualan as jumlah
from pegawai1 p, penjualan j, rincianpenjualan rj, obat o, golonganobat go
where p.id_pegawai = j.id_pegawai
and j.id_penjualan = rj.id_penjualan
and o.id_obat = rj.id_obat
and o.kode_gol = go.kode_gol
and o.kode_gol like ‘A’
) nq
group by nq.nama
order by sum(nq.jumlah) desc
) nq2
)

10.
select o.nama_obat,(s.jumlah_stock-t.total_jual) as sisa_stock
from obat o, stockobat s,
(select id_obat,sum(jumlah) as total_jual
from rincianpenjualan
group by id_obat) t
where s.id_obat = o.id_obat and t.id_obat = s.id_obat
union
select o.nama_obat,s.jumlah_stock as sisa_stock
from stockobat s, obat o
where o.id_obat not in (select distinct id_obat from rincianpenjualan)
and o.id_obat = s.id_obat;

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: