praktikum postgree akhir

create table mahasiswaa
(
nim varchar(10) NOT NULL,
name varchar(40),
tempatlahir varchar (30),
tabggallahir date,
alamat varchar(50),
CONSTRAINT mhsi_key PRIMARY KEY (nim)
);

INSERT INTO mahasiswaa values ('201642008','kiki','surabaya','1999-12-10','lampung');
INSERT INTO mahasiswaa values ('201642009','kiko','sidoarjo','1997-11-11','bandung');
INSERT INTO mahasiswaa values ('201642006','kiku','surabaya','1998-08-09','malang');

SELECT * FROM mahasiswaa

create table jurusan
(
kodejurusan varchar(2),
namajurusan varchar(30),
CONSTRAINT jur_key PRIMARY KEY (kodejurusan)
);

SELECT * FROM jurusan;

INSERT INTO jurusan values ('01','informatika');
INSERT INTO jurusan values ('02','sipil');
INSERT INTO jurusan values ('03','geo');

ALTER TABLE mahasiswaa
add column kodejur varchar(2),
ADD CONSTRAINT jur_fkey FOREIGN KEY (kodejur)
REFERENCES jurusan (kodejurusan);



update mahasiswaa set kodejur ='01' where nim='201642006';
update mahasiswaa set kodejur ='02' where nim='201642009';
update mahasiswaa set kodejur ='03' where nim='201642008';





SELECT * FROM mahasiswaa


select * from mahasiswaa order by nim desc; --besar ke kecil--
select * from mahasiswaa order by nim asc;

select * from mahasiswaa;
select nim,name from mahasiswaa where kodejur in ('01','02','03');













--CONTOH--

create table krs
(
nim varchar (10) NOT NULL,
nilaiuts float,
nilaiuas float,
CONSTRAINT daftarkrs primary key (nim)
);
insert into krs values ('2015420012','22','33');
insert into krs values ('2015420013','44','45');
insert into krs values ('2015420014','55','65');
insert into krs values ('2015420015','75','85');
insert into krs values ('2015420016','75','85');

SELECT * FROM KRS;

create table matkuliah(
kodemk varchar (6) NOT NULL,
namamk varchar (40),
SKS int,
CONSTRAINT kodemk PRIMARY KEY(kodeMK)
);


insert into matkuliah values ('MK4532','SISTEM OPERASI','4');
insert into matkuliah values ('MK6788','SISTEM INFORMASI','2');
insert into matkuliah values ('MK0987','SISTEM BASIS DATA','4');
insert into matkuliah values ('MK3246','ALJABAR','5');
insert into matkuliah values ('MK9879','METODE NUMERIK','3');

SELECT * FROM matkuliah


ALTER TABLE KRS
ADD COLUMN kodemk varchar (30),
ADD CONSTRAINT kodmatkul FOREIGN KEY (kodeMK)
REFERENCES matkuliah (kodeMK);


update KRS set kodemk ='MK4532' where nim='2015420012';
update KRS set kodemk ='MK6788' where nim='2015420013';
update KRS set kodemk ='MK0987' where nim='2015420014';
update KRS set kodemk ='MK3246' where nim='2015420015';
update KRS set kodemk ='MK9879' where nim='2015420016';

SELECT * FROM KRS
SELECT * FROM KRS where nilaiuas between 60 and 100;

SELECT * FROM mahasiswaa order by name asc;

SELECT name as namasiswa, nim as nomer from mahasiswaa;

<--restristing baris menggunakan klausa like
SELECT nim , name from mahasiswaa where nim LIKE '201642008'
select nim , name from mahasiswaa where name like 'k%'
select nim , name from mahasiswaa where name like '%o'
select 'ike ' || 'rok' as "nama"
SELECT name, max(
date_part('year',age(tabggallahir))) || 'tahun' as "usia" from mahasiswaa Group by name, alamat

create function keluar (anyelement, anyelement)returns anyelement as '
declare
	hasil alias for $0;
	hasil1 alias for $1;
	hasil2 alias for $2;
begin
	hasil = hasil1 + hasil2;
	return hasil;
end;
'language plpgsql;
SELECT nim, substr (nim,1,4) as angkatan from mahasiswaa;
SELECT * FROM keluar (2016,4);


create function add_four_values (anyelement, anyelement)returns anyelement as '
declare
	hasil alias for $0;
	hasil1 alias for $1;
	hasil2 alias for $2;
	hasil3 alias for $3;
begin
	hasil = hasil1 + hasil2 + hasil3;
	return hasil;

end;
'language plpgsql;
SELECT * FROM add_four_values (1,2,3)
--inner join pertemuan--
select * from mahasiswaa inner join jurusan ON mahasiswaa.kodejur = jurusan.kodejurusan;
select * from mahasiswaa left join jurusan ON mahasiswaa.kodejur = jurusan.kodejurusan;
select * from mahasiswaa full join jurusan ON mahasiswaa.kodejur = jurusan.kodejurusan;

create function tambah (int, int, int) returns int as 'select $1 + $2 + $3' language 'sql';
select tambah (7,8,9)
---TUGAS UKM----

create table ukm
(
kodemataukm varchar (7) NOT NULL,
namaukm varchar (30),
nilaiukm int,
CONSTRAINT daftar_ukm primary key (kodemataukm)
);

INSERT INTO ukm values ('KM01','futsal','100');
INSERT INTO ukm values ('KM02','basket','30');
INSERT INTO ukm values ('KM03','tenis','90');
SELECT * FROM ukm

update ukm set namaukm ='futsal' where
delete from ukm WHERE kodemataukm = 'KM01'; --dsda--
select avg (nilaiukm) as "rata rata" from ukm;
select upper (namaukm) as "dsda" from ukm;
update ukm set namaukm ='dsda' WHERE kodemataukm = 'KM02';

--lllllll--


create table kp
(
kodekp varchar (10),
namakp varchar (5),
nilaikp float,
nilaikptuntas float,
CONSTRAINT tab_kp primary key(kodekp)
);

INSERT INTO kp VALUES ('KP01','lorem','99','100');
INSERT INTO kp VALUES ('KP02','ipsum','70','99');
INSERT INTO kp VALUES ('KP03','dolor','20','100');
SELECT * FROM kp

ALTER TABLE ukm
ADD COLUMN namakp varchar (10),
ADD CONSTRAINT namkp FOREIGN KEY (namakp)
REFERENCES kp (kodekp);

update
update ukm set namakp ='ipsum' where namaukm='dsda';

update ukm set namakp ='dolor' where kodeukm='KM01';



ALTER TABLE ukm
ADD COLUMN kodekp varchar (10),
ADD CONSTRAINT kodkp FOREIGN KEY (kodekp)
REFERENCES kp (kodekp);


update ukm set kodekp ='KP01' where kodemataukm='KM01';
update ukm set kodekp ='KP02' where kodemataukm='KM02';
update ukm set kodekp ='KP03' where kodemataukm='KM03';

SELECT * FROM ukm



--cara ALTER--

--TUGAS UKM--












delete from krs WHERE nim = '2015420015';
UPDATE krs SET nilaiuts ='100' WHERE nim = '2015420014';
UPDATE krs SET nilaiuas ='100' WHERE nim = '2015420014';


create table matakuliah(
kodemk varchar(6) NOT NULL,
namamk varchar (40),
sks int,
CONSTRAINT matkul primary key(kodemk)
);
insert into matakuliah values ('01','ipa','3');
insert into matakuliah values ('02','ips','4');
insert into matakuliah values ('03','indo','2');
insert into matakuliah values ('04','sejarah','5');

SELECT * FROM matakuliah;

ALTER TABLE KRS
ADD COLUMN kodemk varchar (6),
ADD CONSTRAINT kodmatkul FOREIGN KEY (kodemk)
REFERENCES matakuliah (kodemk);












select avg (nilaiuts) as "nilai rata-rata" from KRS;
select avg (nilaiuas) as "nilai rata-rata" from KRS;

select ('Basis'||'Data');
select lower ('UNITOMO'); --untuk megecilkan font--
select upper ('unitomo'); --utk huruf kapital
select substr ('Unitomo'2,3);
select current date;
select current time;
select age (timestamp '1999-12-16');
select date_part('day', current dae);
select current_date + integer '7'; --tanggal ditambah 7--
select count (*) from mahasiswaa;
select count (nim) from mahasiswaa; --menentukan jumlah baris yg ada isinya--
select max (nilaiuas) from KRS;
select min (nilaiuas) from KRS;
select sum (nilaiuas) from KRS;








////////////////////////
select * from mahasiswaa;
select alamat,nim from mahasiswa
select nim,name from mahasiswaa

UPDATE mahasiswaa SET name ='Suci' WHERE nim = '201642008';
delete from mahasiswaa
DELETE from mahasiswaa WHERE name = 'kiko';
///////////////////////