Tugas SQL (Basis Data)

Tugas SQL Data Basis:

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.

21. SELECT * FROM `employee`

22. SELECT CompanyName, Address FROM `costumer`

23.

24. SELECT * FROM `employee`WHERE TitleofCourtesy = 'mr'

25.

26. SELECT CompanyName, Address FROM `costumer`

27.

28.

29.

30.