Indahnya Berbagi: Jawaban Soal Basis Data Dalam Bentuk Perintah SQL

No 1.
CREATE DATABASE `penjualanElec` ;

1a.
CREATE TABLE `pelanggan` (
`id_plg` VARCHAR( 6 ) COMMENT 'Identitas Pelanggan',
`nm_plg` VARCHAR( 35 ) COMMENT 'Nama Pelanggan',
`almt_plg` VARCHAR( 75 ) COMMENT 'Alamat Pelanggan',
`tlp_plg` VARCHAR( 12 ) COMMENT 'Telepon Pelanggan',
`handphone` VARCHAR( 12 ) COMMENT 'Nomer Handphone',
PRIMARY KEY ( `id_plg` )
) ;

1b.
CREATE TABLE `kurir` (
`id_kurir` CHAR( 3 ) COMMENT 'Identitas Kurir',
`nm_kurir` VARCHAR( 20 ) COMMENT 'Nama Kurir',
`almt_kurir` VARCHAR( 50 ) COMMENT 'Alamat Kurir',
`tlp_kurir` VARCHAR( 8 ) COMMENT 'Telepon Kurir',
`handphone` VARCHAR( 12 ) COMMENT 'Nomor Handphone',
PRIMARY KEY ( `id_kurir` )
) ;

1c.
CREATE TABLE `barang` (
`id_brg` VARCHAR( 10 ) COMMENT 'Identitas/Kode Barang',
`nm_brg` VARCHAR( 50 ) COMMENT 'Nama Barang',
`sat_brg` VARCHAR( 10 ) COMMENT 'Satuan Barang',
`hrg_sat` INT( 8 ) NULL COMMENT 'Harga Satuan',
PRIMARY KEY ( `id_brg` )
) ;

No 2.
2a.
INSERT INTO `penjualanelec`.`pelanggan` (
`id_plg` ,
`nm_plg` ,
`almt_plg` ,
`tlp_plg` ,
`handphone`
)
VALUES ('C00001', 'Joy', 'Jl. Cileduk Raya    73567876', '08182123443'),
       ('C00002', 'Iwa', 'Jl. Kenanga 1', '67842232', '08553553344'),
       ('C00003', 'Tri', 'Jl. Tegap 9', '98434343', '08174334344'),
       ('C00004', 'Win', 'Jl. Maju 6', '52566455', '08569923443');

2b.
INSERT INTO `penjualanelec`.`kurir` (
`id_kurir` ,
`nm_kurir` ,
`almt_kurir` ,
`tlp_kurir` ,
`handphone`
)
VALUES ('K01', 'Bram', 'Bojonegoro', '75654444', '08171234522'),
       ('K02', 'Guruh', 'Bojonegoro', '34454454', '0812343332'),
       ('K03', 'Hans', 'Cirebon', '25454646', '08124454545'),
       ('K04', 'Kim', 'Jogja', '78674523', '08524543107');


2c.
INSERT INTO `penjualanelec`.`barang` (
`id_brg` ,
`nm_brg` ,
`sat_brg` ,
`hrg_sat`
)
VALUES ('ACAKI01', 'AC AKIRA 1 PK', 'PK', '2500000'),
       ('ACAKI02', 'AC AKIRA 2 PK', 'PK', '4500000'),
       ('ACSNY', 'AC SONY 1 PK', 'PK', '3000000');


No 3.
3a.
SELECT *
FROM `pelanggan`
ORDER BY `nm_plg` DESC
LIMIT 0 , 30

3b.
SELECT *
FROM `pelanggan`
ORDER BY `id_plg` DESC

3c.
DELETE FROM `pelanggan`
WHERE (`pelanggan`.`id_plg`) = 'C00002'

3d.
UPDATE `penjualanelec`.`kurir` SET `nm_kurir` = 'Jerry',
`almt_kurir` = 'Pati',
`tlp_kurir` = '75644346',
`handphone` = '08345678'
WHERE ( `kurir`.`id_kurir` ) = 'K04' ;

3e.
SELECT *
FROM `kurir`
WHERE `almt_kurir` LIKE 'bojonegoro'

3f.
SELECT *
FROM `pelanggan`
WHERE `nm_plg` LIKE '%i%'

3g.
SELECT *
FROM `barang`
WHERE `hrg_sat` >2000000
AND `hrg_sat` <4600000

3h.
SELECT *
FROM `barang`
WHERE `sat_brg` LIKE 'PK'
AND `hrg_sat` >2800000

3i.
SELECT *
FROM `barang`
WHERE `nm_brg` LIKE '%SONY%'
AND `hrg_sat` <3100000

3j.
UPDATE `penjualanelec`.`pelanggan` SET `nm_plg` = 'Ari',
`almt_plg` = 'Jl. Meruya',
`tlp_plg` = '83525525',
`handphone` = '08214586'
WHERE (`pelanggan`.`id_plg` ) = 'C00002';

Terimakasih.