SCRIPT SQL UTS BASIS DATA II

SCRIPT SQL UTS BASIS DATA II

Wahyu Surya Wisnugraha

19.1.03.02.0070

2C

create database penjualan;

use penjualan;

create table barang (

id_barang int(3) PRIMARY KEY AUTO_INCREMENT,

nama_barang varchar(20),

harga_barang int(6),

satuan varchar(10));

 

create table kasir (

id_penjualan int(4) PRIMARY KEY AUTO_INCREMENT,

id_barang int(3),

jumlah int(2),

harga_barang int(10));

 

ALTER TABLE kasir ADD FOREIGN KEY (id_barang) REFERENCES barang(id_barang)

ON UPDATE CASCADE ON DELETE SET NULL;

 

INSERT INTO barang VALUES (1,"Keyboard",50000,"Pcs");

INSERT INTO barang VALUES (NULL,"Mouse",25000,"Pcs");

 

INSERT INTO kasir VALUES (1,1,5,100000);

INSERT INTO kasir VALUES (NULL,3,2,30000);

 

select * from kasir;

select * from barang;

 

UPDATE barang SET nama_barang = "Papan ketik" WHERE id_barang = 1;

 

select * from barang;

 

DELETE FROM barang WHERE id_barang = 1;

 

select * from barang;

Select * From barang b JOIN kasir k ON b.id_barang = k.id_barang;

 

SELECT * FROM barang WHERE id_barang IN(SELECT id_barang FROM kasir WHERE harga_barang>25000);