DATABASE

 use latihan2

create table pet (

nama varchar (20) not null,

pemilik varchar (20),

spesies varchar (20),

jeniskelamin varchar (1),

tanggallahir date,

tanggalmeninggal date)

insert into pet(nama,pemilik,spesies,jeniskelamin,tanggallahir)

values ('ketty','dian','kucing','p','2009-03-03')

insert into pet(nama,pemilik,spesies,jeniskelamin,tanggallahir)

values ('blacky','andi','anjing','l','2010-03-17')

insert into pet(nama,pemilik,spesies,jeniskelamin,tanggallahir)

values ('betty','andi','burung','p','2009-02-04')

insert into pet(nama,pemilik,spesies,jeniskelamin,tanggallahir,tanggalmeninggal)

values ('bunny','arnold','kelinci','p','2013-01-02','2013-03-11')

insert into pet(nama,pemilik,spesies,jeniskelamin,tanggallahir)

values ('frank','andi','anjing','p','2008-07-03')

insert into pet(nama,pemilik,spesies,jeniskelamin,tanggallahir)

values ('sanca','budi','ular','l','2011-06-02')

insert into pet(nama,pemilik,spesies,jeniskelamin,tanggallahir)

values ('belang','tuti','kucing','p','2009-07-30')

select *from pet

update pet set tanggallahir='2012-08-31'

where tanggallahir='2011-06-02'

select *from pet

where nama='sanca'

delete from pet where nama='ketty'

select *from pet

where tanggallahir>='2012-01-01'

select spesies,jeniskelamin from pet

where(spesies='anjing' or spesies='kucing') and jeniskelamin='p'

select *from pet

where spesies='ular' or spesies='burung'

select *from pet

where spesies='anjing'and jeniskelamin='l' or jeniskelamin='p'

------------------------------------------------------------------------------------------------------------------------------------


create database MDP

use MDP

create table tblSQL(
Nomor char(2),
Nama Varchar(25),
Gaji numeric,
Kota Varchar(20),
KodePos char(5)
)

insert into tblSQL values ('01','Irwan','100000000','Palembang','12345')
insert into tblSQL values ('02','David','200000000','Papua Barat','65432')
insert into tblSQL values ('03','Very','300000000','Bandung','30122')
insert into tblSQL values ('04','Haryono','400000000','Aceh','13655')
insert into tblSQL values ('05','Darwin','500000000','Bali','11223')
insert into tblSQL values ('06','Erwin','600000000','Palembang','30123')



select nomor,gaji from tblSQL order by nama desc

select* from tblSQL where Kota = 'Palembang'

select* from tblSQL where Kota = 'Palembang' and KodePos = '30123'


alter table tblSQL  add telepon varchar(20)

alter table tblSQL drop column KodePos

update tblSQL set Kota='Palembang' where telepon = '0711'
select*from tblSQL
drop table tblSQL

drop database MDP


-------------------------------------------------------------------------------------------------------------------
create database penjualan_brg
use penjualan_brg

create table Barang (
    Kode_Brg    char(10) primary key,
    nama_Brg    varchar(10),
    Harga        int
    )
   
select*from Barang
insert into Barang values ('B001','Buku',5000)
insert into Barang values ('B002','Pena',2000)
insert into Barang values ('B003','Pensil',1000)
insert into Barang values ('B004','Penggaris',3000)
drop table Barang

create table Juall(
    Nomor    char(5),
    Nama    varchar(5),
    Kode_Brg    char(10) references Barang(Kode_Brg),
    Jumlah    integer
    )
select*from Juall

insert into Juall values ('001','Budi','B001',10)
insert into Juall values ('001','Budi','B002',20)
insert into Juall values ('001','Budi','B003',10)
insert into Juall values ('002','Toni','B001',50)
insert into Juall values ('002','Toni','B003',30)
insert into Juall values ('003','Toni',null,70)
drop table Juall

--1jumlah record brg
select COUNT(Jumlah) as 'jumlah record' from juall

select MAX(harga) as 'nilai maksimum' from barang

select AVG(Jumlah) as 'rata-rata' from juall

select MIN(harga) as 'Nilai Minimum' from barang

select COUNT(kode_barang) 'Total Barang' from juall

--group by
--1.tampilkan nomor serta nilai maksimum yg ada pada tabel jual
select nomor,MAX(Jumlah) AS Nilai_Maksimum
from juall
group by nomor

--2nomor serta nilai rata-rata pd tabel jual
select nomor,AVG(jumlah) AS Rata_Rata
From Juall
Group by nomor

--3.nomor &nama serta nilai min pd tabel jual
select nomor,Nama,MIN(Jumlah) AS Nilai_Minimum
from Juall
group by nomor,nama

--4.
select nama,nomor, sum(jumlah) AS Total_Barang
from Juall
group by nama,nomor

select nama,nomor,sum(jumlah)as total_barang
from juall
group by nama,nomor
having sum(jumlah)>=50