Tugas SQL (Basis Data)

Pembuatan tabel -
CREATE TABLE Suplier (SuplierID char (3),CompanyName varchar (25),Address varchar (50));

CREATE TABLE Product (ProductID char (4),ProductName varchar (25),SuplierID char (3));

CREATE TABLE Costumer (CostumerID char (5),CompanyName varchar (25),Address varchar (50));

CREATE TABLE Orders (OrderID char (3),CostumerID char (5),EmployeeID char (3),Orderdate date);

CREATE TABLE Employee (EmployeeID char (3),LastName varchar (15),FirstName varchar (10),TitleofCourtesy varchar (3),Birthdate date);


Penambahan Tabel
ALTER TABLE  `suplier` ADD  `phone` CHAR( 12 ) NOT NULL AFTER  `SuplierID`

ALTER TABLE  `costumer` ADD  `phone` CHAR( 12 ) NOT NULL AFTER  `Address`

ALTER TABLE  `Employee` ADD  `Address` VARCHAR( 50 ) NOT NULL AFTER  `Birthdate`

ALTER TABLE Suplier ADD CONSTRAINT SuplierID PRIMARY KEY (SuplierID)

ALTER TABLE Product ADD CONSTRAINT ProductID PRIMARY KEY (ProductID),ADD FOREIGN KEY (SuplierID) REFERENCES Suplier (SuplierID);

Memasukan Data -
INSERT INTO  `tugas_sql`.`suplier` (`SuplierID` ,`phone` ,`CompanyName` ,`Address`)
VALUES ('102',  '021-45446454',  'PT Adi Swadaya',  'Jl. Wijaya 2 No 14'),('103',  '021-78945645',  'TokyoTraders',  'Jl. Thamrin kav 10-11');

INSERT INTO  `tugas_sql`.`product` (`ProductID` ,`ProductName` ,`SuplierID`)
VALUES ('1002', 'Buku Tulis', '102'),('1007', 'Kursi Tamu', '103'),('1008', 'Sikat Lantai', '103'),('1009', 'Meja Komputer', '103');

INSERT INTO  `tugas_sql`.`costumer` (`CostumerID` ,`CompanyName` ,`Address` ,`phone`)
VALUES ('10008', 'PT Adi Sarana', 'Jl.Kby Lama 10', '021-78954532'),('10009', 'PT Bangun Sentra', 'Jl.Ciputat Raya 12', ' ');

INSERT INTO  `tugas_sql`.`employee` (`EmployeeID` ,`LastName` ,`FirstName` ,`TitleofCourtesy` ,`Birthdate` ,`Address`)
VALUES ('205', ' ', 'Budiman', 'Mr', '2-maret-1970', 'Jl. Kebangusan 10'),('206', 'Subagyo', 'Joko', 'Mr', ' ', ' '),('207', ' ', 'Tina', 'Ms', ' ', ' ');

INSERT INTO  `tugas_sql`.`orders` (`OrderID` ,`CostumerID` ,`EmployeeID` ,`Orderdate`)
VALUES ('101', '10008', '206', '1-maret-2004'),('102', '10009', '207', '2-maret-2004');

Mengubah Data yang telah ada -
UPDATE  `tugas_sql`.`costumer` SET  `Address` =  'Jl. Pasar Minggu 10' WHERE  `costumer`.`CostumerID` =  '10008' AND  `costumer`.`CompanyName` = 'PT Adi Sarana' AND  `costumer`.`Address` =  'Jl.Kby Lama 10' AND  `costumer`.`phone` =  '021-78954532' LIMIT 1 ;

UPDATE  `tugas_sql`.`employee` SET  `Birthdate` =  '1068-03-14' WHERE  `employee`.`EmployeeID` =  '206' AND  `employee`.`LastName` =  'Subagyo' AND  `employee`.`FirstName` =  'Joko' AND  `employee`.`TitleofCourtesy` =  'Mr' AND  `employee`.`Birthdate` =  '0000-00-00' AND  `employee`.`Address` =  ' ' LIMIT 1 ;

Menghapus Data "Sikat Lantai"
DELETE product WHERE ProductName = "Sikat Lantai"

Mengubah no telepon pelanggan yang ber - ID "103"
UPDATE  `tugas_sql`.`suplier` SET  `phone` =  '' WHERE  `suplier`.`SuplierID` =  '103';

Menampilkan Semua Data di Tabel Pekerja
SELECT * FROM `employee`

Menampilkan "Nama perusahaan dan alamat" di Tabel Pelanggan
SELECT CompanyName, Address FROM `costumer`

Menampilkan Semua Data di Tabel Pekerjayang bergelar "mr"
SELECT * FROM `employee`WHERE TitleofCourtesy = 'mr'

SELECT CompanyName, Address FROM `costumer`