UH SQL Dasar Mengelola Tabel

Isikan lah sebuah record ke dalam tabel barang seperti tampilan berikut : KODE_BARANG NAMA_BARANG SATUAN_BARANG STOK_BARANG KPR-1 KULKAS BUAH 20

• insert into barang values('KPR-01','KULKAS','BUAH','20');
• insert into_barang values('KPR-01','KULKAS','BUAH','20');
• insert barang_values('KPR-01','KULKAS','BUAH','20');
• insert into barang values(KPR-01,KULKAS,BUAH,20);
• insert into barang values(KPR-01,KULKAS,BUAH,20)

Salah satu contoh data manipulation language

• ALTER
• TABLE
• CHAT
• SELECT
• REPORT

Ubah lah tabel barang pada kolom nama barang dari TELEVISI menjadi MONITOR

• SQL> update to barang set nama_barang='MONITOR' where nama_barang='TELEVISI';
• SQL> update from barang set nama_barang='MONITOR' where nama_barang='TELEVISI';
• SQL> update barang set nama_barang='MONITOR' where nama_barang='TELEVISI';
• SQL> update table barang set nama_barang='MONITOR' where nama_barang='TELEVISI';
  
• SQL>delete from barang set nama_barang='MONITOR' where nama_barang='TELEVISI';

Hapuslah pada field MONITOR pada kolom nama_barang pada tabel barang

• SQL> delete from barang where NAMA_BARANG='MONITOR';
• SQL> delete from table barang where NAMA_BARANG='MONITOR';
• SQL>delete on barang where NAMA_BARANG='MONITOR';
• SQL>delete on table barang where NAMA_BARANG='MONITOR';
• SQL> delete from barang where NAMA_BARANG='MONITOR

Cari kode_brg untuk semua barang yg berhasil dijual oleh lebih dari seorang salesman :

• Select kode_brg From Pengiriman Order By kode_brg Having Sum(*)>1
• Select kode_brg From Pengiriman Grup By kode_brg Having Sum(kode_sales)>1
• Select kode_brg From Pengiriman Order By kode_brg Having Count(*)>1
• Select kode_brg From Pengiriman Group By kode_brg Having Count(kode_sales)>1
• Select kode_brg From Pengiriman Group By kode_brg Having Count(kode_sales)>=1

Cari nama salesman yg berlokasi di Jakarta dan status lebih besar dari 30

• Select Salesman From nama-sales Where kota=’Jakarta’ and status>30
• Select Salesman From nama-sales Where kota=’Jakarta’ or status>30
• Select nama_sales From Salesman Where kota=’Jakarta’ or status>30
• Select nama_sales From Salesman Where kota=’Jakarta’ and status>30
• Select Salesman From namasales Where kota=’Jakarta’ or status>30

Ubah harga barang dengan kode P2 menjadi 1.5 kali dari harga lama :

• Update Barang Set harga=1.5*harga Where kode_brg=’P2’
• Replace Barang Set harga=1.5*harga Where kode_brg=’P2’
• Replace Barang Where kode_brg=’P2’ Set harga=1.5*harga
• Update Barang Where kode_brg=’P2’ Set harga=1.5*harga
• Insert Barang Where kode_brg=’P2’ Set harga=1.5*harga

Tampilkan employee id,last name,salary dan salary di kalikan 10% dengan kolom alias “Gaji Baru”
SQL> select employee_id,last_name,salary,salary*0.1 as "Gaji Baru" from table employees;

• SQL> select employee_id,last_name,salary,salary*0.1 as "Gaji Baru" to employees;
• SQL> select employee_id,last_name,salary,salary*0.1 as "Gaji Baru" on employees;
• SQL> select employee_id,last_name,salary,salary*0.1 as "Gaji Baru" from employees;
• SQL> select employee_id,last_name,salary,salary*0.1 as "Gaji Baru" from employees;
• SQL> select employeeid,lastname,salary,salary*0.1 as "Gaji Baru" from employees;

Hitung lah jumlah karyawan yang Tanggal masuk nya antara 20-09-1988 s/d 06-02-2000

• SQL> select to count(last_name) from employees where hire_date between '20-09-1988' and '06-02-2000';
• SQL> select to table count(last_name) from employees where hire_date between '20-09-1988' and '06-02-2000';
• SQL> select from count(last_name) from employees where hire_date between '20-09-1988' and '06-02-2000';
• SQL> select count(last_name) from employees where hire_date between '20-09-1988' and '06-02-2000';
• SQL> select count(last_name) from employees where hire_date between '20-09-1988' or '06-02-2000';

Tampilkan last name,salary,dan panjang karakter dengan kolom alias “JML KARAKTER” berdasarkan huruf awal yang di input kan oleh user

• select last_name,salary,length(last_name) as "JML KARAKTER" from employees where last_name like '&_%';
• select table last_name,salary,length(last_name) as "JML KARAKTER" from employees where last_name like '&_%';
• select from last_name,salary,length(last_name) as "JML KARAKTER" from employees where last_name like '&_%';
• select on last_name,salary,length(last_name) as "JML KARAKTER" from employees where last_name like '&_%';
• select table last_name,length(last_name) as "JML KARAKTER" from employees where last_name like '&_%';