Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- drop database if exists db18K10011;
- create database db18K10011;
- use db18K10011;
- create table tblMobil
- (
- noplat varchar(8),
- mobil varchar(30),
- tarifperjam double
- );
- create table tblPenyewa
- (
- noid int,
- nama varchar(20),
- alamat varchar(30)
- );
- create table tblSewa
- (
- noplat varchar(8),
- noid int,
- jamsewa int,
- biayaperjam double
- );
- insert into tblMobil values
- ('H 9234 A', 'Avanza', 25000),
- ('H 9235 A', 'Xenia', 25000),
- ('H 9237 A', 'Ertiga', 30000),
- ('H 9434 A', 'Panther', 35000),
- ('H 9534 A', 'Livina', 30000),
- ('H 9634 A', 'Innova', 35000),
- ('H 9734 A', 'Terios', 27500);
- insert into tblPenyewa values
- (1, 'Denny', 'Jl Mataram 32'),
- (2, 'Linda', 'Jl Soedirman 02'),
- (3, 'Petra', 'Jl A Yani 99'),
- (4, 'Vania', 'Jl Let Jend Soeprapto 153'),
- (5, 'Deborah', 'Jl Katamso 55'),
- (6, 'Benny', 'Jl A Yani 678'),
- (7, 'Yanny', 'Jl Mataram 982'),
- (8, 'Ditta', 'Jl Majapahit 1227'),
- (9, 'Anna', 'Jl DI Panjaitan 552'),
- (10, 'Venda', 'Jl Hasanudin 97');
- insert into tblSewa values
- ('H 9234 A', 8, 7, 25000),
- ('H 9235 A', 9, 6, 25000),
- ('H 9237 A', 4, 3, 30000),
- ('H 9434 A', 1, 5, 35000),
- ('H 9534 A', 5, 8, 30000),
- ('H 9634 A', 6, 5, 35000),
- ('H 9734 A', 6, 6, 27500),
- ('H 9234 A', 1, 10, 25000),
- ('H 9235 A', 4, 3, 25000),
- ('H 9237 A', 2, 2, 30000),
- ('H 9434 A', 5, 5, 35000),
- ('H 9534 A', 9, 1, 30000),
- ('H 9634 A', 7, 1, 35000),
- ('H 9734 A', 4, 8, 27500),
- ('H 9234 A', 6, 9, 25000),
- ('H 9235 A', 7, 7, 25000),
- ('H 9237 A', 4, 8, 30000),
- ('H 9434 A', 7, 4, 35000),
- ('H 9534 A', 7, 2, 30000),
- ('H 9634 A', 9, 9, 35000),
- ('H 9734 A', 7, 2, 27500);
- --no 1
- select * from tblMobil;
- select * from tblPenyewa;
- select * from tblSewa;
- --no 2
- select concat(tblMobil.Mobil,' ','[',tblMobil.noplat,']') as 'Mobil',
- concat(tblPenyewa.nama,' ','[',tblPenyewa.alamat,']') AS 'Penyewa'
- from tblMobil
- natural join tblPenyewa
- natural join tblSewa;
- --no 3
- select concat(tblMobil.Mobil,' ','[',tblMobil.noplat,']') as 'Mobil',
- concat(tblPenyewa.nama,' ','[',tblPenyewa.alamat,']') AS 'Penyewa'
- from tblMobil
- natural join tblPenyewa
- natural join tblSewa
- where tblMobil.Mobil='Avanza' or tblMobil.Mobil='Xenia';
- --no 4
- select concat(tblMobil.Mobil,' ','[',tblMobil.noplat,']') as 'Mobil',
- concat(tblPenyewa.nama,' ','[',tblPenyewa.alamat,']') AS 'Penyewa'
- from tblMobil
- natural join tblPenyewa
- natural join tblSewa
- where tblMobil.Mobil<>'Innova' and tblMobil.Mobil<>'Terios';
- --no 5
- select concat(tblMobil.Mobil,' ','[',tblMobil.noplat,']') as 'Mobil',
- concat(tblPenyewa.nama,' ','[',tblPenyewa.alamat,']') AS 'Penyewa',
- tblSewa.jamsewa as 'Jam'
- from tblMobil
- natural join tblPenyewa
- natural join tblSewa
- where tblSewa.jamsewa > '5';
- --no 6
- select concat(tblMobil.Mobil,' ','[',tblMobil.noplat,']') as 'Mobil',
- concat(tblPenyewa.nama,' ','[',tblPenyewa.alamat,']') AS 'Penyewa',
- count(tblPenyewa.nama) as 'Jumlah Menyewa'
- from tblMobil
- natural join tblPenyewa
- natural join tblSewa
- where tblPenyewa.nama='Vania';
- --no 7
- select concat(tblMobil.Mobil,' ','[',tblMobil.noplat,']') as 'Mobil',
- concat(tblPenyewa.nama,' ','[',tblPenyewa.alamat,']') AS 'Penyewa',
- count(tblPenyewa.nama) as 'Jumlah Menyewa', sum(tblSewa.jamsewa*tblSewa.biayaperjam) as 'Total Bayar'
- from tblMobil
- natural join tblPenyewa
- natural join tblSewa
- where tblPenyewa.nama='Deborah';
- JANCUK
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement