PRAKTIKUM BASIS DATA PERTEMUAN 4

Bahasan Tugas Praktikum 3

Soal ada di link ini  
https://www.arifsetiawan.info/2019/10/tugas-3-praktikum-basis-data.html

Jawab : 

use dagang

select * from barang

create table supplier

(

     Kdsupp varchar (30),

     Nmsupp varchar (30),

     Jekel varchar (10),

     Kantor varchar (30),

     Alamat_Kantor varchar(50),

     Telp_Kantor varchar (15),

     HP varchar (15),

     Email varchar (20)

     primary key (Kdsupp)

)

insert into supplier values

('S001','Tino Agustin','Pria','CV. Indah Lestari Jaya','Jl. Halilintar No 20A Semarang','024-66747484','08566456736','Tinoa@gmail.com'),

('S002','Ema Maeda','Wanita','PT. Indoofod Fritolay','Jl. Genuk Krajan 56C','024-99880932','08122573657','Maeda@indofood.com'),

('S003','Joko Santoso','Pria','PT. Unilever', 'Jl. Kuningan Jakarta Pusat','021-24536777','08644778765','Joko@gmail.com'),

('S004','Pambudi','Pria','PT. Philips Indonesia','Jl. Kimangunsar koro No 22-28','021-87677453','08598975877','Pambudi@yahoo.com'),

('S005','Dea Lestari','wanita','UD. Semar Jaya','Jl. Gemah Ripah no 1017','021-3453762','08172366549','Lestari@semar.co.id'),

('S006','Valir','Pria','CV. Indah Lestari Jaya','Jl. Halilintar No 20A Semarang','024-66747484','08566456736','Tinoa@gmail.com'),

('S007','Lunox','Wanita','PT. Indoofod Fritolay','Jl. Genuk Krajan 56C','024-99880932','08122573657','Maeda@indofood.com'),

('S008','Kufra','Pria','PT. Unilever', 'Jl. Kuningan Jakarta Pusat','021-24536777','08644778765','Joko@gmail.com'),

('S009','Dyroth','Pria','PT. Philips Indonesia','Jl. Kimangunsar koro No 22-28','021-87677453','08598975877','Pambudi@yahoo.com'),

('S010','Lylia','wanita','UD. Semar Jaya','Jl. Gemah Ripah no 1017','021-3453762','08172366549','Lestari@semar.co.id'),

('S011','Vale','Pria','CV. Indah Lestari Jaya','Jl. Halilintar No 20A Semarang','024-66747484','08566456736','Tinoa@gmail.com'),

('S012','Harith','Wanita','PT. Indoofod Fritolay','Jl. Genuk Krajan 56C','024-99880932','08122573657','Maeda@indofood.com'),

('S013','Balmond','Pria','PT. Unilever', 'Jl. Kuningan Jakarta Pusat','021-24536777','08644778765','Joko@gmail.com'),

('S014','Zilong','Pria','PT. Philips Indonesia','Jl. Kimangunsar koro No 22-28','021-87677453','08598975877','Pambudi@yahoo.com'),

('S015','Sun','wanita','UD. Semar Jaya','Jl. Gemah Ripah no 1017','021-3453762','08172366549','Lestari@semar.co.id'),

('S016','Alucard','Pria','CV. Indah Lestari Jaya','Jl. Halilintar No 20A Semarang','024-66747484','08566456736','Tinoa@gmail.com'),

('S017','Pharsa','Wanita','PT. Indoofod Fritolay','Jl. Genuk Krajan 56C','024-99880932','08122573657','Maeda@indofood.com'),

('S018','Akai','Pria','PT. Unilever', 'Jl. Kuningan Jakarta Pusat','021-24536777','08644778765','Joko@gmail.com'),

('S019','Gatotkaca','Pria','PT. Philips Indonesia','Jl. Kimangunsar koro No 22-28','021-87677453','08598975877','Pambudi@yahoo.com'),

('S020','Odette','wanita','UD. Semar Jaya','Jl. Gemah Ripah no 1017','021-3453762','08172366549','Lestari@semar.co.id');

select * from supplier

'soal no 3' à 201853172

Tampilkan Nama, Jenis kelamin, HP dari supplier yang beralamat kantor di semarang

select kdsupp, Nmsupp, Jekel, Hp, Alamat_Kantor, email

from supplier

where Alamat_Kantor LIKE '%semarang%';

'soal  no 4' à 201853163

Tampilkan kode, nama, alamat supplier, yang dari perusahaan PT

select kdsupp,Nmsupp,Kantor, Alamat_Kantor

from supplier

where Kantor like 'PT%';

'soal no 5'

Tampilkan Nama, No HP supplier yang bernomor Indosat

0855,0856,0857,0858,0815,0814,0816 --> indosat

0852,082,0811,0812,0813,0821,0822,0823,0851 --> simpati

select nmsupp,hp

from supplier

where

hp like '0855%' or

hp like '0856%' or

hp like '0857%' or

hp like '0858%' or

hp like '0815%' or

hp like '0814%' or

hp like '0816%'

'soal no 7, update' à 201853145

Rubah no HP Joko Santoso menjadi 08165578734

update supplier SET  hp = '08165578734'

where nmsupp = 'Joko Santoso'

select * from supplier where nmsupp = 'Joko Santoso'

'soal no 9 ,dalete' à 201853151

Hapus Supplier Dea Lestari

DELETE from supplier where Nmsupp = 'Dea Lestari'

select * from supplier

‘Soal no 10 ’ à 201853155

Tambahkan 1 field “No Fax Kantor, Isi no fax bebas

ALTER TABLE supplier ADD No_Fax_Kantor varchar (50);

update supplier set No_Fax_Kantor = '087364652'

where Kdsupp = 'S001'