Alter table mahasiswa add(alamat varchar(30));
Alter table mahasiswa add telp varchar (13) after nama;
Alter table mahasiswa modify alamat varchar(100);
Alter table mahasiswa rename to mahasiswa_uad;
Alter table mahasiswa drop telp;
Update mahasiswa set nim=’09018001’, nama=’ari’ where nim=’09018001’;
Delete from mahasiswa where....;
Seelct * from buku where tahun_terbit between ‘2000’ and ‘2002’;
รจ data buku tahun terbit
2000-2002.
1.
Semua data dosen yang ber NIP
diawali dgn ‘600’:
Select *
from Dosen where nip like ‘%600’;
2.
Nim mhs yg nilai u/ kode kul=’IT100’ > 80:
Select nim
from nilai where nilai>80 and kode_kul=’IT100’;
3.
Kode makul,
ruang, jam dan hari u/ makul yg diajarkan oleh dosen nip 60040497
Select kode_kul,
ruang, jam, hari from mengajar where nip=’60040497’;
4.
Hny kode kul
& nma kul u/ makul sem=8
5.
Data nilai yg
ditampilkan urut nim
6.
Data nilai yg
ditampilkan per kode kul
7.
Hny 5 baris
pertama dari table mhs urut tgl lhir
Select *
from mhs order by tgl_lahir limit 5;
8.
Rata nilai
makul dgn kode kul IT100
Select avg(nilai)
from nilai where kode_kul=’IT100’’;
9.
Hitung brp
jml baris/record pd table mhs
Select count(*)
from mahasiswa;
10.
Hitung jml sks
total dlm table makul
Select sum(sks)
from makul;
11.
Nilai maks
dari makul dgn kode kul IT100
Select max(nilai)
from Nilai where kode_kul IT100
12.
Nim dan nilai
min dari mhs dgn kode kul IT100
Select nim,
min(nilai) from nilai where kode_kul=IT100
13.
Hitung jml
record/baris mhs yg dpt nilai>80 u/ makul dgn kode kul IT100
Select count(*)
from nilai where nilai>80 and kode kul IT100
14.
Nim, nama,
nilai mhs yg m’ambil makul dgn kode kul IT100
Select nim,
nama, nilai from mhs left join nilai on mhs.nim=nilai.nim where kode_kul IT100
Atau
Select m.nim,
m.nama, n.nilai from mhs as m left join nilai as n on m.nim=n.nim where kode
kul IT100
15.
Nma dos yg
mngajar makul dgn kode kul IT100
Select d.namados
from dosen as d left join mengajar as m on d.nip=m.nip where kodekul IT100
Atau
Select d.namados
from dosen d, mengajar m where d.nip=m.nip and m.kodekul IT100
16.
Nma makul,
kode makul, ruang, jam, hari yg di ajarkan oleh dosen dgn nip 60040497
Select m.namakul,
m.kodekul, g.ruang, g.jam, g.hari from makul as m left join mengajar as g on
m.kodekul=g.kodekul where g.nip 60040497
17.
Tot sks yg di
ajarkan oleh dosen berinisial dewi
Select sum(m.sks)
frm dosen d, makul m, mengajar g where m.kodekul=g.kodekul and d.nip=g.nip and
d.namados ‘dewi’
18.
Nama makul
dgn rata2 >80
1.
cari data mhs(nim, nama, nilai)
yang nilainya melebihi rata2 makul ny
a.
select
avg(nilai) from nilai where kode kul IT100
b.
select a.nim,
a.nama, b.nilai from mhs a, nilai b where a.nim=b.nim and b.nilai(select avg(nilai)
from nilai where kodekul IT100
2.
cari
data nilai yg nilainy = nilai terbesar IT100
select
a.nim, b.nama, b.nilai from mhs a, nilai b where a.nim=b.nim and
b.nilai=(select max(nilai) from nilai where kodekul IT100);
3.
Cari data mhs
yg ambil kuliah IT100
a.
Select a.nim,
a.nama from mhs a, ambil b where a.nim=b.nim and b.kodekul=IT100
atau
b.
Select nim,
nama from mhs where nim in(select nim from ambil where kodekul IT100)
4.
Carilah
siapa2 saja mhs yg tdk ikut kul IT100
Select nim,
nama from mhs where nim not in(select nim from ambil where kodekul IT100)
5.
Menghitung sp2
sja/jmlh mhs yg nilainy terbesar IT100
Select count(*)
from nilai where nilai=(select max(nilai) from nilai where kodekul IT100)
