DIBACA DULU PALING ATAS GES :v

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;