1. CREATE TABLE Suplier(
SuplierID char (3),
CompanyName varchar (25),
Address varchar (50)
);
2. CREATE TABLE Product(
ProductID char (4),
ProductName varchar (25),
SuplierID char (3)
);
3. CREATE TABLE Costumer(
CostumerID char (5),
CompanyName varchar (25),
Address varchar (50)
);
4. CREATE TABLE Orders(
OrderID char (3),
CostumerID char (5),
EmployeeID char (3),
Orderdate date
);
5. CREATE TABLE Employee(
EmployeeID char (3),
LastName varchar (15),
FirstName varchar (10),
TitleofCourtesy varchar (3),
Birthdate date
);
6. ALTER TABLE `suplier` ADD `phone` CHAR( 12 ) NOT NULL AFTER `SuplierID`
7. ALTER TABLE `costumer` ADD `phone` CHAR( 12 ) NOT NULL AFTER `Address`
8. ALTER TABLE `Employee` ADD `Address` VARCHAR( 50 ) NOT NULL AFTER `Birthdate`
9. ALTER TABLE Suplier
ADD CONSTRAINT SuplierID PRIMARY KEY (SuplierID)
10. ALTER TABLE Product
ADD CONSTRAINT ProductID PRIMARY KEY (ProductID)
ADD FOREIGN KEY (SuplierID) REFERENCES Suplier (SuplierID)
11. 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'
);
12. 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'
);
13. 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', ' '
);
14. 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', ' ', ' '
);
15. INSERT INTO `tugas_sql`.`orders` (
`OrderID` ,
`CostumerID` ,
`EmployeeID` ,
`Orderdate`
)
VALUES (
'101', '10008', '206', '1-maret-2004'
),
(
'102', '10009', '207', '2-maret-2004'
);
16. 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 ;
17. 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 ;
18. DELETE product WHERE ProductName = "Sikat Lantai"
19. UPDATE `tugas_sql`.`suplier` SET `phone` = '' WHERE `suplier`.`SuplierID` = '103';
20. SELECT * FROM `employee`
21. SELECT CompanyName, Address FROM `costumer`
22. SELECT * FROM `employee`WHERE TitleofCourtesy = 'mr'
23. SELECT CompanyName, Address FROM `costumer`
0 komentar :
Posting Komentar