Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- DENGAN RAHMAT DARI PADA TUHAN YANG MAHA ESA SAYA BISA MENGERJAKAN TUGAS INI DENGAN LANCAR
- HIYA HIYA HIYAAA
- JANGAN LUPA INI TIDAK DICOPY OR DI DELETE YA KALO GAK ERRORRRR
- DIBACAAAAAAAAAAAAAAAAAAAAA
- DROP DATABASE IF EXISTS dbPenawaranProject;
- CREATE DATABASE dbPenawaranProject;
- USE dbPenawaranProject;
- -- NO.1
- CREATE TABLE tblBarang(
- kodebarang INT PRIMARY KEY,
- namabarang VARCHAR(100)
- );
- CREATE TABLE tblProject(
- noproject INT PRIMARY KEY,
- namaproject VARCHAR(255),
- kepada VARCHAR(100),
- alamat VARCHAR(255),
- tanggalmulai DATETIME,
- tanggalselesai DATETIME
- );
- CREATE TABLE tblPenawaranHarga(
- noproject INT,
- kodebarang INT,
- jumlah INT,
- harga DOUBLE,
- kategori VARCHAR(100),
- FOREIGN KEY(noproject)
- REFERENCES tblProject(noproject)
- ON DELETE CASCADE,
- FOREIGN KEY(kodebarang)
- REFERENCES tblBarang(kodebarang)
- ON DELETE CASCADE
- );
- --NO.2
- INSERT INTO tblBarang VALUES
- (1,'Notebook Acer'),
- (2,'Notebook Asus'),
- (3,'Notebook HP'),
- (4,'Notebook Toshiba'),
- (5,'PC Asus'),
- (6,'PC HP'),
- (7,'PC Acer'),
- (8,'PC MSI'),
- (9,'Projector Benq'),
- (10,'Projector Asus'),
- (11,'Printer Canon'),
- (12,'Printer HP'),
- (13,'Printer Epson'),
- (14,'Scanner Canon'),
- (15,'Scanner HP');
- INSERT INTO tblProject VALUES
- (1,'Project IT Paket 1','Perusahaan A','Semarang','2015-11-12','2016-01-21'),
- (2,'Project IT Paket 2','Perusahaan B','Salatiga','2015-11-10','2016-02-23'),
- (3,'Project IT Paket 2','Perusahaan C','Semarang','2015-11-18','2016-02-26'),
- (4,'Project IT Paket 3','Perusahaan D','Pekalongan','2015-12-02','2016-01-29'),
- (5,'Project IT Paket 4','Perusahaan E','Semarang','2015-12-09','2016-02-15'),
- (6,'Project IT Paket 4','Perusahaan F','Pekalongan','2015-12-07','2016-02-14'),
- (7,'Project IT Paket 1','Perusahaan G','Salatiga','2015-11-26','2016-01-16');
- INSERT INTO tblPenawaranHarga VALUES
- (1,3,1,6000000,'NOTEBOOK'),
- (7,3,7,6000000,'NOTEBOOK'),
- (1,6,5,4500000,'PC'),
- (7,6,2,4500000,'PC'),
- (1,12,5,450000,'PRINTER SCANNER'),
- (7,12,8,450000,'PRINTER SCANNER'),
- (1,15,8,550000,'PC'),
- (7,15,3,550000,'PC'),
- (2,1,2,5000000,'NOTEBOOK'),
- (6,1,1,5000000,'NOTEBOOK'),
- (2,7,1,4000000,'PC'),
- (6,7,1,4000000,'PC'),
- (3,2,2,5000000,'NOTEBOOK'),
- (5,2,6,5000000,'NOTEBOOK'),
- (3,5,4,4000000,'PC'),
- (5,5,3,4000000,'PC'),
- (3,10,3,6000000,'PROJECTOR'),
- (5,10,5,6000000,'PROJECTOR'),
- (4,4,7,6000000,'NOTEBOOK'),
- (4,8,1,5500000,'PC'),
- (4,9,9,400000,'PROJECTOR'),
- (4,14,6,60000,'PRINTER SCANNER');
- -- NO.3
- SELECT CONCAT(tblProject.namaproject,':',tblProject.kepada) AS 'PROJECT_KEPADA' ,
- SUM(tblPenawaranHarga.jumlah*tblPenawaranHarga.harga) AS 'TOTAL_HARGA_PROJECT'
- FROM tblProject
- NATURAL JOIN tblPenawaranHarga
- GROUP BY tblProject.noproject;
- --NO.4
- SELECT tblBarang.namabarang AS NAMABARANG,
- SUM(tblPenawaranHarga.jumlah*tblPenawaranHarga.harga) AS TOTAL
- FROM tblBarang
- NATURAL JOIN tblPenawaranHarga
- GROUP BY tblBarang.kodebarang
- ORDER BY SUM(tblPenawaranHarga.jumlah*tblPenawaranHarga.harga) DESC;
- --NO.5
- SELECT DISTINCT TABLE2.kepada AS PROJECT2 ,
- (SELECT SUM(tblPenawaranHarga.harga*tblPenawaranHarga.jumlah)
- FROM tblPenawaranHarga
- WHERE tblPenawaranHarga.noproject=TABLE2.noproject ) AS TOTAL2,
- TABLE1.kepada AS PROJECT1,
- (SELECT SUM(tblPenawaranHarga.harga*tblPenawaranHarga.jumlah)
- FROM tblPenawaranHarga
- WHERE tblPenawaranHarga.noproject=TABLE1.noproject ) AS TOTAL1,
- CASE
- WHEN (SELECT SUM(tblPenawaranHarga.harga*tblPenawaranHarga.jumlah)
- FROM tblPenawaranHarga
- WHERE tblPenawaranHarga.noproject=TABLE2.noproject )
- >
- (SELECT SUM(tblPenawaranHarga.harga*tblPenawaranHarga.jumlah)
- FROM tblPenawaranHarga
- WHERE tblPenawaranHarga.noproject=TABLE1.noproject )
- THEN CONCAT('Project 2 Prioritas, selisih harga ',
- ((SELECT SUM(tblPenawaranHarga.harga*tblPenawaranHarga.jumlah)
- FROM tblPenawaranHarga
- WHERE tblPenawaranHarga.noproject=TABLE2.noproject )
- -
- (SELECT SUM(tblPenawaranHarga.harga*tblPenawaranHarga.jumlah)
- FROM tblPenawaranHarga
- WHERE tblPenawaranHarga.noproject=TABLE1.noproject ))
- )
- WHEN (SELECT SUM(tblPenawaranHarga.harga*tblPenawaranHarga.jumlah)
- FROM tblPenawaranHarga
- WHERE tblPenawaranHarga.noproject=TABLE1.noproject )
- >
- (SELECT SUM(tblPenawaranHarga.harga*tblPenawaranHarga.jumlah)
- FROM tblPenawaranHarga
- WHERE tblPenawaranHarga.noproject=TABLE2.noproject )
- THEN CONCAT('Project 1 Prioritas, selisih harga ',
- ((SELECT SUM(tblPenawaranHarga.harga*tblPenawaranHarga.jumlah)
- FROM tblPenawaranHarga
- WHERE tblPenawaranHarga.noproject=TABLE1.noproject )
- -
- (SELECT SUM(tblPenawaranHarga.harga*tblPenawaranHarga.jumlah)
- FROM tblPenawaranHarga
- WHERE tblPenawaranHarga.noproject=TABLE2.noproject ))
- )
- ELSE 'BELUM BUAT'
- END AS KETERANGAN
- FROM tblProject AS TABLE1,
- tblProject AS TABLE2,
- tblPenawaranHarga
- WHERE TABLE2.kepada!=TABLE1.kepada;
- --NO.6
- SELECT DISTINCT TABLE3.kepada AS PROJECT3,
- (SELECT SUM(tblPenawaranHarga.harga*tblPenawaranHarga.jumlah)
- FROM tblPenawaranHarga
- WHERE tblPenawaranHarga.noproject=TABLE3.noproject ) AS TOTAL3,
- TABLE2.kepada AS PROJECT2,
- (SELECT SUM(tblPenawaranHarga.harga*tblPenawaranHarga.jumlah)
- FROM tblPenawaranHarga
- WHERE tblPenawaranHarga.noproject=TABLE2.noproject ) AS TOTAL2,
- TABLE1.kepada AS PROJECT1,
- (SELECT SUM(tblPenawaranHarga.harga*tblPenawaranHarga.jumlah)
- FROM tblPenawaranHarga
- WHERE tblPenawaranHarga.noproject=TABLE1.noproject ) AS TOTAL1
- FROM tblProject AS TABLE1,
- tblProject AS TABLE2,
- tblProject AS TABLE3,
- tblPenawaranHarga
- WHERE TABLE2.kepada!=TABLE3.kepada AND
- TABLE1.kepada!=TABLE2.kepada AND
- TABLE1.KEPADA!=TABLE3.kepada;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement