Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- drop database if exists dbmobil;
- create database dbmobil;
- use dbmobil;
- create table tblmobil(
- noplat varchar(8) PRIMARY KEY,
- mobil varchar(30),
- tarifperjam double
- );
- create table tblpenyewa(
- noid int PRIMARY KEY,
- nama varchar(20),
- alamat varchar(30)
- );
- create table tblsewa(
- noplat varchar(8),
- FOREIGN KEY (noplat) REFERENCES tblmobil(noplat),
- noid int,
- FOREIGN KEY (noid) REFERENCES tblpenyewa(noid),
- 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, 'Debora',' 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' );
- -- NOMOR 1
- SELECT * FROM tblmobil;
- SELECT * FROM tblpenyewa;
- SELECT * FROM tblsewa;
- -- NOMOR 2
- SELECT concat(tblmobil.mobil,' [',tblmobil.noplat,']') as Mobil, concat(tblpenyewa.nama,' [',tblpenyewa.alamat,']') as Penyewa
- FROM tblmobil,tblpenyewa,tblsewa
- WHERE tblmobil.noplat=tblsewa.noplat and tblpenyewa.noid=tblsewa.noid;
- -- NOMOR 3
- SELECT concat(tblmobil.mobil,' [',tblmobil.noplat,']') as Mobil, concat(tblpenyewa.nama,' [',tblpenyewa.alamat,']') as Penyewa
- FROM tblmobil,tblpenyewa,tblsewa
- WHERE tblmobil.noplat=tblsewa.noplat and tblpenyewa.noid=tblsewa.noid
- AND (tblmobil.mobil='Avanza' or tblmobil.mobil='Xenia');
- -- NOMOR 4
- SELECT concat(tblmobil.mobil,' [',tblmobil.noplat,']') as Mobil, concat(tblpenyewa.nama,' [',tblpenyewa.alamat,']') as Penyewa
- FROM tblmobil,tblpenyewa,tblsewa
- WHERE tblmobil.noplat=tblsewa.noplat and tblpenyewa.noid=tblsewa.noid
- AND (tblmobil.mobil not like '%Terios%' and tblmobil.mobil not like '%Innova%');
- -- NOMOR 5
- SELECT concat(tblpenyewa.nama,' [',tblpenyewa.alamat,']') as Penyewa,concat(tblmobil.mobil,' [',tblmobil.noplat,']') as Mobil,tblsewa.jamsewa as Jam
- FROM tblmobil,tblpenyewa,tblsewa
- WHERE tblmobil.noplat=tblsewa.noplat and tblpenyewa.noid=tblsewa.noid
- AND(tblsewa.jamsewa>5);
- -- NOMOR 6
- SELECT concat(tblpenyewa.nama,' [',tblpenyewa.alamat,']') as Penyewa,count(tblpenyewa.nama) as JumlahPenyewa
- FROM tblmobil,tblpenyewa,tblsewa
- WHERE tblmobil.noplat=tblsewa.noplat and tblpenyewa.noid=tblsewa.noid
- AND (tblpenyewa.nama='Vania');
- -- NOMOR 7
- SELECT concat(tblpenyewa.nama,' [',tblpenyewa.alamat,']') as Penyewa,count(tblpenyewa.nama) as JumlahPenyewa,
- SUM(tblsewa.jamsewa*tblsewa.biayaperjam) as totalbayar
- FROM tblmobil,tblpenyewa,tblsewa
- WHERE tblmobil.noplat=tblsewa.noplat and tblpenyewa.noid=tblsewa.noid
- AND (tblpenyewa.nama='Debora');
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement