Tugas 7/8 Basis Data SMK TELKOM JAKARTA

---

Contain Spoiler!

Contact me to learn more!

---

YOU FACE THE SON OF DEATHWING

---

---

CREATE DATABASE SUPPLIER_PART;

USE SUPPLIER_PART;

CREATE TABLE IF NOT EXISTS SUPPLIER (

`S#` char(3) PRIMARY KEY,SName char(30) NOT NULL, 

CITY char(15) NOT NULL, STATUS char(15) NOT NULL );

CREATE TABLE IF NOT EXISTS PART (

`P#` char(3) PRIMARY KEY,PName char(15) NOT NULL,COLOUR char(15) NOT NULL,

WEIGHT int(3) NOT NULL,CITY char(15) NOT NULL );

CREATE TABLE IF NOT EXISTS SUPPLIER_PART (

`S#` char(3),`P#` char(3),QTY int(4),

FOREIGN KEY (`S#`) REFERENCES SUPPLIER(`S#`) ON DELETE CASCADE ON UPDATE CASCADE,

FOREIGN KEY (`P#`) REFERENCES PART(`P#`) ON DELETE CASCADE ON UPDATE CASCADE );

INSERT IGNORE INTO SUPPLIER VALUES("S1","SMITH","LONDON","30");

INSERT IGNORE INTO SUPPLIER VALUES("S2","BLAKE","PARIS","40");

INSERT IGNORE INTO SUPPLIER VALUES("S3","JONES","ATHENS","20");

INSERT IGNORE INTO SUPPLIER VALUES("S4","RUDOLF","ROME","10");

INSERT IGNORE INTO SUPPLIER VALUES("S5","GORDON","AMSTERDAM","35");

INSERT IGNORE INTO PART VALUES("P1","NUT","BLUE",10,"PARIS");

INSERT IGNORE INTO PART VALUES("P2","CAM","RED",20,"LONDON");

INSERT IGNORE INTO PART VALUES("P3","COG","YELLOW",30,"ATHENS");

INSERT IGNORE INTO PART VALUES("P4","SCREW","RED",40,"ROME");

INSERT IGNORE INTO PART VALUES("P5","BOLT","GREEN",50,"LONDON");

/* Hanya untuk mempermudah melakukan copas */
ALTER TABLE SUPPLIER_PART ADD UNIQUE KEY `dent_Supplier_PART` (
`S#`,`P#`
);
ALTER TABLE SUPPLIER_PART ADD UNIQUE KEY `dent_Supplier_PART` (
SNumber,PNumber
);

INSERT IGNORE INTO SUPPLIER_PART VALUES("S1","P1", 400);

INSERT IGNORE INTO SUPPLIER_PART VALUES("S1","P2", 200);

INSERT IGNORE INTO SUPPLIER_PART VALUES("S1","P3", 100);

INSERT IGNORE INTO SUPPLIER_PART VALUES("S1","P4", 500);

INSERT IGNORE INTO SUPPLIER_PART VALUES("S2","P2", 200);

INSERT IGNORE INTO SUPPLIER_PART VALUES("S2","P3", 300);

INSERT IGNORE INTO SUPPLIER_PART VALUES("S3","P4", 100);

INSERT IGNORE INTO SUPPLIER_PART VALUES("S4","P1", 200);

INSERT IGNORE INTO SUPPLIER_PART VALUES("S4","P2", 300);

INSERT IGNORE INTO SUPPLIER_PART VALUES("S4","P3", 400);

INSERT IGNORE INTO SUPPLIER_PART VALUES("S4","P4", 300);

INSERT IGNORE INTO SUPPLIER_PART VALUES("S4","P5", 500);

 /* Tampilkan no dan nama supplier yang kotanya di london */

select `S#`,Sname from supplier where CITY = "London";

/* Tampilkan no, nama dan status supplier yang namanya terdapat huruf 'S' */

select `S#`,Sname,status from supplier where Sname LIKE "%S%";

/* Tampilkan no,nama dan kota supplier yang statusnya diatas 30 */

select `S#`,Sname,CITY from supplier where STATUS > 30;

/* Tampilkan no dan nama part yang warnanya terdapat huruf R */

SELECT `P#`,PName from PART WHERE COLOUR LIKE "%R%";

/* Tampilkan no, nama dan kota dari part yang beratnya kurang dari 30 */

SELECT `P#`,PName,CITY from PART WHERE WEIGHT < 30;

/* Tampilkan no dan nama part yang dimulai dengan huruf C */

SELECT `P#`,PName from PART WHERE PName LIKE "C%";

/* Tampilkan nama supplier dan nama part yang kuantiti lebih dari 350 */

SELECT SUPPLIER.SName, part.PName FROM SUPPLIER_PART

JOIN PART ON PART.`P#` = SUPPLIER_PART.`P#`

JOIN SUPPLIER ON SUPPLIER.`S#` = SUPPLIER_PART.`S#`

WHERE SUPPLIER_PART.QTY > 350;

/* Tampilkan no,nama supplier dan no, nama part yang kotanya di ROME */

SELECT SUPPLIER.`S#`,SUPPLIER.Sname,PART.`P#`,PART.PName FROM SUPPLIER_PART

JOIN PART ON PART.`P#` = SUPPLIER_PART.`P#`

JOIN SUPPLIER ON SUPPLIER.`S#` = SUPPLIER_PART.`S#`

WHERE SUPPLIER.CITY = "ROME";

/* Tampilkan no, nama dan status supplier yang mensupplai part berwarna merah */

SELECT SUPPLIER.`S#`,SUPPLIER.Sname,SUPPLIER.STATUS FROM SUPPLIER_PART

JOIN PART ON PART.`P#` = SUPPLIER_PART.`P#`

JOIN SUPPLIER ON SUPPLIER.`S#` = SUPPLIER_PART.`S#`

WHERE PART.COLOUR = "RED";

/* Tampilkan no, nama, berat dari part yang disuplai oleh BLAKE */

SELECT PART.`P#`,PART.PName,PART.WEIGHT FROM SUPPLIER_PART

JOIN PART ON PART.`P#` = SUPPLIER_PART.`P#`

JOIN SUPPLIER ON SUPPLIER.`S#` = SUPPLIER_PART.`S#`

WHERE SUPPLIER.SName = "BLAKE";