Fungsi Transkrip "FUNCTION transkrip(CHAR)" Dengan Output Mata Kuliah Hanya dengan Nilai Terbaik dari KHS

DUMP DATABASE universitas.sql

--
-- PostgreSQL database dump
--

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;

--
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: 
--

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;


--
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: 
--

COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';


SET search_path = public, pg_catalog;

--
-- Name: transkrip(character); Type: FUNCTION; Schema: public; Owner: imam1096
--

CREATE FUNCTION transkrip(character) RETURNS TABLE(nim character, kode_mk character, nama_mk character, nilai character)
    LANGUAGE plpgsql
    AS $_$
BEGIN
RETURN QUERY SELECT A.nim, A.kode_mk, B.nama_mk, A.nilai FROM khs A JOIN mata_kuliah B ON A.kode_mk=B.kode_mk WHERE A.nim = $1;
END;
$_$;


ALTER FUNCTION public.transkrip(character) OWNER TO imam1096;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: khs; Type: TABLE; Schema: public; Owner: imam1096; Tablespace: 
--

CREATE TABLE khs (
    nim character(9) NOT NULL,
    kode_mk character(9) NOT NULL,
    ta smallint NOT NULL,
    smt character(6) NOT NULL,
    nilai character(1) DEFAULT 'E'::bpchar NOT NULL,
    CONSTRAINT khs_nilai_check CHECK ((((((nilai = 'A'::bpchar) OR (nilai = 'B'::bpchar)) OR (nilai = 'C'::bpchar)) OR (nilai = 'D'::bpchar)) OR (nilai = 'E'::bpchar))),
    CONSTRAINT khs_smt_check CHECK (((smt = 'ganjil'::bpchar) OR (smt = 'genap'::bpchar))),
    CONSTRAINT khs_ta_check CHECK (((ta >= 2000) AND (ta <= 2020)))
);


ALTER TABLE public.khs OWNER TO imam1096;

--
-- Name: mahasiswa; Type: TABLE; Schema: public; Owner: imam1096; Tablespace: 
--

CREATE TABLE mahasiswa (
    nim character(9) NOT NULL,
    nama character(50) NOT NULL
);


ALTER TABLE public.mahasiswa OWNER TO imam1096;

--
-- Name: mata_kuliah; Type: TABLE; Schema: public; Owner: imam1096; Tablespace: 
--

CREATE TABLE mata_kuliah (
    kode_mk character(9) NOT NULL,
    nama_mk character(50) NOT NULL,
    sks smallint NOT NULL,
    kurikulum smallint NOT NULL,
    smt character(6) NOT NULL,
    CONSTRAINT mata_kuliah_kurikulum_check CHECK (((kurikulum >= 2000) AND (kurikulum <= 2020))),
    CONSTRAINT mata_kuliah_sks_check CHECK (((sks >= 1) AND (sks <= 6))),
    CONSTRAINT mata_kuliah_smt_check CHECK (((smt = 'ganjil'::bpchar) OR (smt = 'genap'::bpchar)))
);


ALTER TABLE public.mata_kuliah OWNER TO imam1096;

--
-- Data for Name: khs; Type: TABLE DATA; Schema: public; Owner: imam1096
--

COPY khs (nim, kode_mk, ta, smt, nilai) FROM stdin;
081011124	IPKU 1001	2008	genap 	D
081011124	IPKU 1101	2008	ganjil	C
081011124	TIFS 2204	2008	genap 	B
081011124	IPKU 1101	2009	ganjil	B
\.


--
-- Data for Name: mahasiswa; Type: TABLE DATA; Schema: public; Owner: imam1096
--

COPY mahasiswa (nim, nama) FROM stdin;
081011124	Abdul Rojak                                       
091031335	Nikita Sukanari                                   
091041436	Andi Supratman                                    
101051547	Olga Kurniawan                                    
101051647	Emi Salsabila                                     
\.


--
-- Data for Name: mata_kuliah; Type: TABLE DATA; Schema: public; Owner: imam1096
--

COPY mata_kuliah (kode_mk, nama_mk, sks, kurikulum, smt) FROM stdin;
IPKU 1001	Agama                                             	2	2007	genap 
IPKU 1101	Pancasila                                         	2	2007	ganjil
TIFS 2204	Pemrograman Java                                  	3	2007	genap 
TIFS 2301	Konsep Basis Data                                 	3	2007	ganjil
TIFS 3304	Pemrograman SQL                                   	3	2007	ganjil
\.


--
-- Name: mahasiswa_pkey; Type: CONSTRAINT; Schema: public; Owner: imam1096; Tablespace: 
--

ALTER TABLE ONLY mahasiswa
    ADD CONSTRAINT mahasiswa_pkey PRIMARY KEY (nim);


--
-- Name: mata_kuliah_pkey; Type: CONSTRAINT; Schema: public; Owner: imam1096; Tablespace: 
--

ALTER TABLE ONLY mata_kuliah
    ADD CONSTRAINT mata_kuliah_pkey PRIMARY KEY (kode_mk, smt);


--
-- Name: khs_kode_mk_fkey; Type: FK CONSTRAINT; Schema: public; Owner: imam1096
--

ALTER TABLE ONLY khs
    ADD CONSTRAINT khs_kode_mk_fkey FOREIGN KEY (kode_mk, smt) REFERENCES mata_kuliah(kode_mk, smt);


--
-- Name: khs_nim_fkey; Type: FK CONSTRAINT; Schema: public; Owner: imam1096
--

ALTER TABLE ONLY khs
    ADD CONSTRAINT khs_nim_fkey FOREIGN KEY (nim) REFERENCES mahasiswa(nim);


--
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;


--
-- PostgreSQL database dump complete
--