QUERY DATABASE
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`
Comments
Post a Comment