Created
November 25, 2012 17:58
-
-
Save Imam86/4144578 to your computer and use it in GitHub Desktop.
Fungsi Transkrip "FUNCTION transkrip(CHAR)" Dengan Output Mata Kuliah Hanya dengan Nilai Terbaik dari KHS
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- | |
-- 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 | |
-- |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment