SQL : Cara Menghapus Tabel di SQL

create database LAT2
use LAT2

create table mahasiswa
(
nim integer,
nama varchar(30),
tgl_lahir datetime,
alamat varchar(30),
telp numeric(15)
)

select *from mahasiswa

insert into mahasiswa
    values('00124','thirafi athala','04-1990-16','pancoran','99547715')
insert into mahasiswa
    values('00234','pratiwi restiwi','03-1991-20','bandung','33444444')
insert into mahasiswa
    values('00311','georges femon','01-1990-01','singapura','36199567')
insert into mahasiswa
    values('00444','rahardi fernando','12-1989-23','maladewa','44567212')
insert into mahasiswa
    values('00512','ciklasidasi','02-1988-12','malaysia','44332233')
insert into mahasiswa
    values('00666','didik dwi setiawan','03-2000-21','denpasar','44567890')
insert into mahasiswa
    values('00777','ngurah adi nata dwipa','08-1998-22','tabanan','23412233')

drop table mahasiswa

SQL : Menambahkan Kolom pada Tabel

create database pegawai
use pegawai

create table pegawai
(
NIP varchar(10),
Nama varchar(50),
Alamat varchar(50),
Telepon integer,
Golongan varchar(4)
)


alter table pegawai add Status varchar(10)
alter table pegawai add Jkel char(1)

SQL : Contoh Penggunaan Klausa "between" di SQL

 create database latihan
use latihan

create table barang
(
kode_brg varchar(5),
nama_brg varchar(15),
jumlah integer,
harga integer,
asal_supplier varchar(20)
)

insert into barang
    values('01','Mie','10','1000','Denpasar')
insert into barang
    values('02','Gula','30','7000','Banyuwangi')
insert into barang
    values('03','Keju','25','10000','Jakarta')
insert into barang
    values('04','Susu','20','35000','Jakarta')
insert into barang
    values('05','Kopi','80','9000','Denpasar')
insert into barang
    values('06','Teh','15','2000','Surabaya')
insert into barang
    values('07','Roti','40','5000','Bandung')

select nama_brg from barang where jumlah between 10 and 50
select nama_brg from barang where harga between 2000 and 5000

SQL : Contoh Penggunaan Klausa "and" di SQL

create database latihan
use latihan

create table barang
(
kode_brg varchar(5),
nama_brg varchar(15),
jumlah integer,
harga integer,
asal_supplier varchar(20)
)

insert into barang
    values('01','Mie','10','1000','Denpasar')
insert into barang
    values('02','Gula','30','7000','Banyuwangi')
insert into barang
    values('03','Keju','25','10000','Jakarta')
insert into barang
    values('04','Susu','20','35000','Jakarta')
insert into barang
    values('05','Kopi','80','9000','Denpasar')
insert into barang
    values('06','Teh','15','2000','Surabaya')
insert into barang
    values('07','Roti','40','5000','Bandung')

Select * from barang where jumlah>10 and asal_supplier = 'Jakarta'
Select *from barang where harga< 5000 and asal_supplier='bandung'

SQL : Contoh Penggunaan Klausa Like di SQL

create database latihan
use latihan

create table barang
(
kode_brg varchar(5),
nama_brg varchar(15),
jumlah integer,
harga integer,
asal_supplier varchar(20)
)

insert into barang
    values('01','Mie','10','1000','Denpasar')
insert into barang
    values('02','Gula','30','7000','Banyuwangi')
insert into barang
    values('03','Keju','25','10000','Jakarta')
insert into barang
    values('04','Susu','20','35000','Jakarta')
insert into barang
    values('05','Kopi','80','9000','Denpasar')
insert into barang
    values('06','Teh','15','2000','Surabaya')
insert into barang
    values('07','Roti','40','5000','Bandung')

select nama_brg from barang where nama_brg like '%u'
select nama_brg from barang where nama_brg like 'r%'
select harga, nama_brg from barang where nama_brg like '%e%'

Contoh Penggunaan Update di SQL

create database guru
use guru

create table teacher
(
NIP varchar(10),
Nama varchar(20),
Alamat varchar(20),
Jenis_Kelamin varchar(10),
No_Telepon varchar(15)
)

insert into teacher
    values('211','Adi','Jalan Dalung','Laki-Laki','03618686594')

insert into teacher
    values('212','Andi','Jalan Buntu','Laki-Laki','03618496792')

insert into teacher
    values('213','Nida','jalan tersesat','perempuan','03612323467')

update teacher
set Nama='Yanto'
where NIP='211'

update teacher
set nama = 'raya',
alamat = 'jl. padang luwih'
where nip = '213

select * from teacher

SQL : Contoh Penggunaan Constraint Check

create database toko
use toko

create table barang
(
kode_barang char (5) constraint pk_kode_brg primary key,
nama_brg varchar (30),
harga integer,
jumlah integer check (jumlah>=0 and jumlah<=100)
)

insert into barang
    values('001','lemari','800000','51')
insert into barang
    values('002','komputer','1000000','101')