Jawaban KUIS Prak. SBD

CREATE TABLE tbl_barang(
    kode_brg VARCHAR(10) PRIMARY KEY,
    nama_brg VARCHAR(50),
    harga number
);

CREATE TABLE tbl_pegawai(
    kode_pegawai VARCHAR(10) PRIMARY KEY,
    nama_pegawai VARCHAR(50),
    alamat VARCHAR(50),
    no_tlp VARCHAR(20)
);


CREATE TABLE tbl_transaksi(
    kode_transaksi VARCHAR(10) PRIMARY KEY,
    kode_brg VARCHAR(10),
    kode_pegawai VARCHAR(10),
    FOREIGN KEY (kode_brg) REFERENCES tbl_barang,
    FOREIGN KEY (kode_pegawai) REFERENCES tbl_pegawai,
    tanggal date,
    qty number,
    grandtotal number
);

DROP TABLE tbl_barang;
DROP TABLE tbl_pegawai;
DROP TABLE tbl_transaksi;

INSERT INTO tbl_barang VALUES('A001','Buku','5000');
INSERT INTO tbl_barang VALUES('A002','Pulpen','4000');
INSERT INTO tbl_barang VALUES('A003','Novel','55000');
INSERT INTO tbl_barang VALUES('A004','Pensil','2000');
INSERT INTO tbl_barang VALUES('A005','Komik','15000');

INSERT INTO tbl_pegawai VALUES('P001','Shelly','Jakarta','5237591');
INSERT INTO tbl_pegawai VALUES('P002','Fiona','Bandung','5475627');
INSERT INTO tbl_pegawai VALUES('P003','Effendy','Surabaya','757434');
INSERT INTO tbl_pegawai VALUES('P004','Ezra','Jakarta','659832');
INSERT INTO tbl_pegawai VALUES('P005','Ricad','Surabaya','7059345');

INSERT INTO tbl_transaksi VALUES('T001','A001','P002','5 Nov 2012','5','25000');
INSERT INTO tbl_transaksi VALUES('T002','A001','P004','1 Nov 2012','2','10000');
INSERT INTO tbl_transaksi VALUES('T003','A003','P003','5 Oct 2012','1','55000');
INSERT INTO tbl_transaksi VALUES('T004','A005','P002','25 Sep 2012','1','15000');
INSERT INTO tbl_transaksi VALUES('T005','A005','P001','5 Nov 2012','2','30000');

SELECT * FROM tbl_barang;
SELECT * FROM tbl_pegawai;
SELECT * FROM tbl_transaksi;

//Cara 1 Menampilkan data bulan ini:

SELECT t.kode_transaksi, t.tanggal,(b.harga*t.qty) AS grandtotal
FROM tbl_transaksi t
JOIN tbl_barang b
ON (t.kode_brg = b.kode_brg)
WHERE tanggal LIKE '%NOV%';

//Cara 2 Menampilkan data bulan ini:

SELECT kode_transaksi, t.tanggal,(b.harga*t.qty) AS grandtotal
FROM tbl_transaksi t
JOIN tbl_barang b
ON (t.kode_brg = b.kode_brg)
WHERE tanggal BETWEEN '1 Nov 2012' AND '30 Nov 2012';