praktikum postgree

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)
);

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

SELECT * FROM jurusan;

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 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');



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');

SELECT * FROM KRS;

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 * 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';
///////////////////////