Tugas SQL Basis data

Tugas SQL Basisdata:
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`

24.

25.

26.

27.

28.

29.

30.

31.