UTP BASDAT kelas C

CREATE DATABASE RENTAL_VCD;

use RENTAL_VCD;
CREATE TABLE Costumer
(
    id_costumer char (10) NOT NULL PRIMARY KEY,
    nama_costumer varchar (30),
    alamat_costumer varchar (10),
    noTelp_costumer varchar (20)
)

use RENTAL_VCD;
CREATE TABLE Jenis_Film
(
    id_jenis char (10) NOT NULL PRIMARY KEY,
    nama_jenis varchar (20)
)
SELECT * FROM Jenis_Film;


use RENTAL_VCD;
CREATE TABLE VCD
(
    kode_vcd char(10) NOT NULL PRIMARY KEY,
    judul varchar (35),
    id_jenis char (10) REFERENCES Jenis_Film (id_jenis),
    tahun integer,
    actor varchar (50),
    tarif_sewa integer,
)
SELECT * FROM VCD;

use RENTAL_VCD;
CREATE TABLE Transaksi
(
    kode_transaksi char (10)NOT NULL PRIMARY KEY,
    id_customer char (10) REFERENCES Costumer (id_Costumer),
    kode_vcd char (10) REFERENCES VCD (kode_vcd),
    tgl_sewa datetime,
    tgl_harus_kembali datetime,
    tgl_kembali datetime
)
SELECT * FROM Transaksi;

use RENTAL_VCD;
INSERT INTO Costumer VALUES (‘CS0001′,’Kuwat Slamet’,’Solo’,’0459321457′);
…
15 entry


Use RENTAL_VCD;
INSERT INTO Jenis_Film (id_jenis,nama_jenis) values (‘J01′,’Horror’);
INSERT INTO Jenis_Film (id_jenis,nama_jenis) values (‘J02′,’Drama’);
INSERT INTO Jenis_Film (id_jenis,nama_jenis) values (‘J03′,’Action’);
INSERT INTO Jenis_Film (id_jenis,nama_jenis) values (‘J04′,’Animasi’);

Use RENTAL_VCD;
INSERT INTO VCD Values (‘V00001′,’BOLT’,’J04′,2008,’Miley Cyrus’,5000);
…25 entry




Use RENTAL_VCD;
INSERT INTO Transaksi Values (‘T00001′,’CS0012′,’V00008′,’03/01/09′,’03/02/09′,’03/02/09’);
//format datetime kompQ : bulan/tanggal/tahun…
…30 entry

Jawaban pertanyaan :
1
Use RENTAL_VCD;
SELECT * From Costumer WHERE alamat_Costumer=’Malang’;

2
USE RENTAL_VCD;
Select DISTINCT Costumer.id_Costumer,nama_Costumer,alamat_costumer,noTelp_costumer,tgl_sewa
From Costumer,Transaksi
WHERE Transaksi.id_customer=Costumer.id_costumer;

3
USE RENTAL_VCD;
Select Costumer.id_Costumer,nama_Costumer,tgl_sewa,Judul
From Costumer,Transaksi,VCD,Jenis_Film
WHERE Transaksi.id_customer=Costumer.id_costumer AND Transaksi.kode_vcd=VCD.kode_vcd
AND VCD.id_Jenis=Jenis_Film.id_jenis AND Jenis_Film.nama_jenis=’Horror’;

4
Use RENTAL_VCD;
Select kode_Transaksi,Nama_costumer,tgl_sewa,tgl_harus_kembali,tgl_kembali
From Costumer,Transaksi
Where Transaksi.id_customer=Costumer.Id_Costumer AND
tgl_kembali > tgl_harus_kembali;

5
Use RENTAL_VCD;
SELECT sum(VCD.tarif_sewa)as TOTAL_UANG
FROM Transaksi,VCD
WHERE Transaksi.Kode_VCD=VCD.Kode_VCD;

6
Use RENTAL_VCD;
UPDATE VCD SET VCD.tarif_sewa=5000 FROM VCD,Jenis_Film
WHERE VCD.id_jenis=Jenis_Film.id_jenis AND
Jenis_Film.nama_jenis=’Animasi’;

7
Use RENTAL_VCD;
SELECT * FROM Costumer;
SELECT * FROM Jenis_Film;
SELECT * FROM VCD;
SELECT * FROM Transaksi;

8
SELECT kode_Transaksi,nama_Costumer,Judul,nama_jenis,tgl_sewa,tgl_harus_kembali
FROM Transaksi,VCD,Costumer,Jenis_Film
WHERE Transaksi.id_Customer=Costumer.id_costumer AND
Transaksi.kode_VCD=VCD.Kode_VCD AND
VCD.id_jenis=Jenis_Film.id_Jenis;

DOWNLOAD VERSI TXT
DOWNLOAD DAFTAR2 FILM
DOWNLOAD SOALNYA UTP DB