toc.dat100600 04000 02000 12253 7460333351 6731 0ustar PGDMP4+ flibrary0libraryDATABASECreate Database "library"; Drop Database "library"; postgres16557 pga_queriesTABLEÊCREATE TABLE "pga_queries" ( "queryname" character varying(64), "querytype" character(1), "querycommand" text, "querytables" text, "querylinks" text, "queryresults" text, "querycomments" text ); DROP TABLE "pga_queries"; librarian16557 pga_queriesACLzREVOKE ALL on "pga_queries" from PUBLIC; GRANT ALL on "pga_queries" to PUBLIC; GRANT ALL on "pga_queries" to "librarian"; 16562 pga_formsTABLETCREATE TABLE "pga_forms" ( "formname" character varying(64), "formsource" text ); DROP TABLE "pga_forms"; librarian16562 pga_formsACLtREVOKE ALL on "pga_forms" from PUBLIC; GRANT ALL on "pga_forms" to PUBLIC; GRANT ALL on "pga_forms" to "librarian"; 16567 pga_scriptsTABLEZCREATE TABLE "pga_scripts" ( "scriptname" character varying(64), "scriptsource" text ); DROP TABLE "pga_scripts"; librarian16567 pga_scriptsACLzREVOKE ALL on "pga_scripts" from PUBLIC; GRANT ALL on "pga_scripts" to PUBLIC; GRANT ALL on "pga_scripts" to "librarian"; 16572 pga_reportsTABLEšCREATE TABLE "pga_reports" ( "reportname" character varying(64), "reportsource" text, "reportbody" text, "reportprocs" text, "reportoptions" text ); DROP TABLE "pga_reports"; librarian 16572 pga_reportsACLzREVOKE ALL on "pga_reports" from PUBLIC; GRANT ALL on "pga_reports" to PUBLIC; GRANT ALL on "pga_reports" to "librarian";  16577 pga_schemaTABLEnCREATE TABLE "pga_schema" ( "schemaname" character varying(64), "schematables" text, "schemalinks" text ); DROP TABLE "pga_schema"; librarian 16577 pga_schemaACLwREVOKE ALL on "pga_schema" from PUBLIC; GRANT ALL on "pga_schema" to PUBLIC; GRANT ALL on "pga_schema" to "librarian";  16583 object_mediaTABLEECREATE TABLE "object_media" ( "media_id" integer NOT NULL, "category" character(8), "media_type" character(10), "object_title" character(60), "author_name" character(30), "publish_company" character(30), "isbn_number" character(25), "purchase_price" money, Constraint "object_media_pkey" Primary Key ("media_id") ); DROP TABLE "object_media"; librarian 16586 pga_layoutTABLEzCREATE TABLE "pga_layout" ( "tablename" character varying(64), "nrcols" smallint, "colnames" text, "colwidth" text ); DROP TABLE "pga_layout"; librarian16586 pga_layoutACLwREVOKE ALL on "pga_layout" from PUBLIC; GRANT ALL on "pga_layout" to PUBLIC; GRANT ALL on "pga_layout" to "librarian"; 16593customer_accountsTABLEöCREATE TABLE "customer_accounts" ( "cust_id" integer NOT NULL, "cust_name" character(30), "cust_phone" character(12), "cust_address" character(100), "cust_email" character(60), Constraint "customer_accounts_pkey" Primary Key ("cust_id") ); DROP TABLE "customer_accounts"; librarian16597 signed_outTABLEYCREATE TABLE "signed_out" ( "media_id" integer, "cust_id" integer, "out_date" date ); DROP TABLE "signed_out"; librarian16600 returned_backTABLEsCREATE TABLE "returned_back" ( "media_id" integer, "cust_id" integer, "out_date" date, "returned_date" date ); DROP TABLE "returned_back"; librarian16557 pga_queries TABLE DATACOPY "pga_queries" FROM stdin; librarian18.dat16562 pga_forms TABLE DATACOPY "pga_forms" FROM stdin; librarian19.dat16567 pga_scripts TABLE DATACOPY "pga_scripts" FROM stdin; librarian20.dat16572 pga_reports TABLE DATACOPY "pga_reports" FROM stdin; librarian21.dat16577 pga_schema TABLE DATACOPY "pga_schema" FROM stdin; librarian22.dat16583 object_media TABLE DATA COPY "object_media" FROM stdin; librarian23.dat16586 pga_layout TABLE DATACOPY "pga_layout" FROM stdin; librarian24.dat16593customer_accounts TABLE DATA%COPY "customer_accounts" FROM stdin; librarian25.dat16597 signed_out TABLE DATACOPY "signed_out" FROM stdin; librarian26.dat16600 returned_back TABLE DATA!COPY "returned_back" FROM stdin; librarian27.dat18.dat100600 04000 02000 10067 7460333351 6375 0ustar 10 Insert into customer_accounts A INSERT INTO customer_accounts VALUES (2,'Bill Smith','555-6789','44 Bottem Drive', 'bsmith@bol.com'); \ 11 Insert into signed_out A INSERT INTO signed_out VALUES (3,1,'April 4, 2001') \ 01 Create table object_media A CREATE TABLE object_media (media_id INTEGER PRIMARY KEY, category CHAR(8), media_type CHAR(10), object_title CHAR(60), author_name CHAR(30), publish_company CHAR(30), isbn_number CHAR(25), purchase_price money); \ \ \ 02 Create table customer_accounts A CREATE TABLE customer_accounts (cust_id INTEGER PRIMARY KEY, cust_name CHAR(30), cust_phone CHAR(12), cust_address CHAR(100), cust_email CHAR(60)); \ \ \ 03 Create table signed_out A CREATE TABLE signed_out (media_id INT, cust_id INT, out_date DATE); \ \ \ 04 Create table returned_back A CREATE TABLE returned_back (media_id INT, cust_id INT, out_date DATE, returned_date DATE) \ \ 05 Insert into object_media A INSERT INTO object_media VALUES (1, 'Web dev', 'Book', 'Programming JavaScript for Netscape 2.0', 'Tim Ritchey', 'New Riders', 'ISBN 1-56205-585-2', '$47.95'); \ \ \ \ 06 Insert into object_media A INSERT INTO object_media VALUES (2, 'Web dev', 'Book','Mastering Perl 5','Eric C. Herrmann','SYBEX','ISBN 0-7821-2200-0','$39.99'); \ \ 07 Insert into object_media A INSERT INTO object_media VALUES (3, 'Delphi', 'Book','Delphi Power Tookkit for Windows','Harold Davis','Ventana','ISBN 1-56604-292-5','$49.95'); \ \ 08 Insert into object_media A INSERT INTO object_media VALUES (4, 'Linux', 'CD-ROM','Mandrake 7.2','Linux Mandrake','Linux Mandrake','','$29.95'); \ \ 09 Insert into customer_accounts A INSERT INTO customer_accounts VALUES (1,'Joe Brown','555-1234','123 Hill Top Road', 'jbrown@coldmail.com'); \ \ 12 Select S SELECT * FROM object_media \ 13 Select S SELECT * FROM object_media WHERE media_type = 'Book' \ 14 Select S SELECT * FROM object_media WHERE media_type = 'Book' AND media_id != (SELECT media_id FROM signed_out) \ 15 Select S SELECT * FROM object_media WHERE media_type = 'Book' AND media_id = (SELECT media_id FROM signed_out) \ 16 Insert A INSERT INTO returned_back VALUES (3,1,'April 4, 2001','April 12, 2001'); \ 17 Delete A DELETE FROM signed_out WHERE media_id = '3' AND cust_id = '1' \ 18 Copy A CREATE TABLE copy_object_media AS SELECT * FROM object_media \ 19 Alter A ALTER TABLE copy_object_media RENAME TO delete_object_media \ 20 Drop A DROP TABLE delete_object_media \ 21 Insert into A INSERT INTO signed_out VALUES (2,1,'April 14, 2001'); \ 22 Insert into A INSERT INTO signed_out VALUES (3,2,'April 15, 2001') \ 23 Select S SELECT m.object_title, c.cust_name FROM object_media m, customer_accounts c, signed_out s WHERE m.media_id = s.media_id AND c.cust_id = s.cust_id \ 25 Left outer join S SELECT m.object_title, s.out_date FROM object_media m LEFT OUTER JOIN signed_out s ON m.media_id = s.media_id \ 26 Create temp A CREATE TEMP TABLE list_media (object_title CHAR(60), cust_name CHAR(30), out_date DATE) \ 24 Select S select t0."object_title", t2."cust_name" from "object_media" t0,"signed_out" t1,"customer_accounts" t2 where (t0."media_id"=t1."media_id") and (t1."cust_id"=t2."cust_id") object_media 10 10 t0 signed_out 155.0 44.0 t1 customer_accounts 272.0 120.0 t2 t0 media_id t1 media_id t1 cust_id t2 cust_id object_title t0 unsorted {} Yes cust_name t2 unsorted {} Yes \ \ \ \ \ 27 Insert into A INSERT INTO list_media SELECT m.object_title, c.cust_name, s.out_date FROM object_media m, customer_accounts c, signed_out s WHERE m.media_id = s.media_id AND c.cust_id = s.cust_id; \ 28 Insert into A INSERT INTO list_media (object_title) SELECT m.object_title FROM object_media m WHERE m.media_id NOT IN (SELECT media_id FROM signed_out) \ 29 Select S SELECT * FROM list_media \ 30 Create temp A CREATE TEMP TABLE my_vars (search_name char (30)) \ 31 Insert A INSERT INTO my_vars VALUES ('Delphi') \ 32 Select S SELECT * FROM object_media WHERE category IN (SELECT search_name FROM my_vars) \ \. 19.dat100600 04000 02000 3 7460333351 6263 0ustar \. 20.dat100600 04000 02000 3 7460333351 6253 0ustar \. 21.dat100600 04000 02000 3 7460333351 6254 0ustar \. 22.dat100600 04000 02000 3 7460333351 6255 0ustar \. 23.dat100600 04000 02000 1313 7460333351 6343 0ustar 1 Web dev Book Programming JavaScript for Netscape 2.0 Tim Ritchey New Riders ISBN 1-56205-585-2 $47.95 2 Web dev Book Mastering Perl 5 Eric C. Herrmann SYBEX ISBN 0-7821-2200-0 $39.99 3 Delphi Book Delphi Power Tookkit for Windows Harold Davis Ventana ISBN 1-56604-292-5 $49.95 4 Linux CD-ROM Mandrake 7.2 Linux Mandrake Linux Mandrake $29.95 \. 24.dat100600 04000 02000 2456 7460333351 6355 0ustar object_media 8 media_id category media_type object_title author_name publish_company isbn_number purchase_price 150 150 150 150 150 150 150 150 customer_accounts 5 cust_id cust_name cust_phone cust_address cust_email 150 150 150 150 150 12 Select 8 media_id category media_type object_title author_name publish_company isbn_number purchase_price 150 150 150 150 150 150 150 150 13 Select 8 media_id category media_type object_title author_name publish_company isbn_number purchase_price 150 150 150 150 150 150 150 150 14 Select 8 media_id category media_type object_title author_name publish_company isbn_number purchase_price 150 150 150 150 150 150 150 150 15 Select 8 media_id category media_type object_title author_name publish_company isbn_number purchase_price 150 150 150 150 150 150 150 150 copy_object_media 8 media_id category media_type object_title author_name publish_company isbn_number purchase_price 150 150 150 150 150 150 150 150 23 Select 2 object_title cust_name 150 150 24 Select 2 object_title cust_name 150 150 24 vd 2 object_title cust_name 150 150 25 Left outer join 2 object_title out_date 150 150 29 Select 3 object_title cust_name out_date 150 150 150 32 Select 8 media_id category media_type object_title author_name publish_company isbn_number purchase_price 150 150 150 150 150 150 150 150 \. 25.dat100600 04000 02000 643 7460333351 6332 0ustar 1 Joe Brown 555-1234 123 Hill Top Road jbrown@coldmail.com 2 Bill Smith 555-6789 44 Bottem Drive bsmith@bol.com \. 26.dat100600 04000 02000 41 7460333351 6303 0ustar 2 1 2001-04-14 3 2 2001-04-15 \. 27.dat100600 04000 02000 35 7460333351 6307 0ustar 3 1 2001-04-04 2001-04-12 \. restore.sql100600 04000 02000 14307 7460333351 7660 0ustar create temporary table pgdump_restore_path(p text); -- -- NOTE: -- -- File paths need to be edited. Search for $$PATH$$ and -- replace it with the path to the directory containing -- the extracted data files. -- -- Edit the following to match the path where the -- tar archive has been extracted. -- insert into pgdump_restore_path values('/tmp'); -- -- Selected TOC Entries: -- \connect - librarian DROP TABLE "returned_back"; DROP TABLE "signed_out"; DROP TABLE "customer_accounts"; DROP TABLE "pga_layout"; DROP TABLE "object_media"; DROP TABLE "pga_schema"; DROP TABLE "pga_reports"; DROP TABLE "pga_scripts"; DROP TABLE "pga_forms"; DROP TABLE "pga_queries"; -- -- TOC Entry ID 2 (OID 16557) -- -- Name: pga_queries Type: TABLE Owner: librarian -- CREATE TABLE "pga_queries" ( "queryname" character varying(64), "querytype" character(1), "querycommand" text, "querytables" text, "querylinks" text, "queryresults" text, "querycomments" text ); -- -- TOC Entry ID 3 (OID 16557) -- -- Name: pga_queries Type: ACL Owner: -- REVOKE ALL on "pga_queries" from PUBLIC; GRANT ALL on "pga_queries" to PUBLIC; GRANT ALL on "pga_queries" to "librarian"; -- -- TOC Entry ID 4 (OID 16562) -- -- Name: pga_forms Type: TABLE Owner: librarian -- CREATE TABLE "pga_forms" ( "formname" character varying(64), "formsource" text ); -- -- TOC Entry ID 5 (OID 16562) -- -- Name: pga_forms Type: ACL Owner: -- REVOKE ALL on "pga_forms" from PUBLIC; GRANT ALL on "pga_forms" to PUBLIC; GRANT ALL on "pga_forms" to "librarian"; -- -- TOC Entry ID 6 (OID 16567) -- -- Name: pga_scripts Type: TABLE Owner: librarian -- CREATE TABLE "pga_scripts" ( "scriptname" character varying(64), "scriptsource" text ); -- -- TOC Entry ID 7 (OID 16567) -- -- Name: pga_scripts Type: ACL Owner: -- REVOKE ALL on "pga_scripts" from PUBLIC; GRANT ALL on "pga_scripts" to PUBLIC; GRANT ALL on "pga_scripts" to "librarian"; -- -- TOC Entry ID 8 (OID 16572) -- -- Name: pga_reports Type: TABLE Owner: librarian -- CREATE TABLE "pga_reports" ( "reportname" character varying(64), "reportsource" text, "reportbody" text, "reportprocs" text, "reportoptions" text ); -- -- TOC Entry ID 9 (OID 16572) -- -- Name: pga_reports Type: ACL Owner: -- REVOKE ALL on "pga_reports" from PUBLIC; GRANT ALL on "pga_reports" to PUBLIC; GRANT ALL on "pga_reports" to "librarian"; -- -- TOC Entry ID 10 (OID 16577) -- -- Name: pga_schema Type: TABLE Owner: librarian -- CREATE TABLE "pga_schema" ( "schemaname" character varying(64), "schematables" text, "schemalinks" text ); -- -- TOC Entry ID 11 (OID 16577) -- -- Name: pga_schema Type: ACL Owner: -- REVOKE ALL on "pga_schema" from PUBLIC; GRANT ALL on "pga_schema" to PUBLIC; GRANT ALL on "pga_schema" to "librarian"; -- -- TOC Entry ID 12 (OID 16583) -- -- Name: object_media Type: TABLE Owner: librarian -- CREATE TABLE "object_media" ( "media_id" integer NOT NULL, "category" character(8), "media_type" character(10), "object_title" character(60), "author_name" character(30), "publish_company" character(30), "isbn_number" character(25), "purchase_price" money, Constraint "object_media_pkey" Primary Key ("media_id") ); -- -- TOC Entry ID 13 (OID 16586) -- -- Name: pga_layout Type: TABLE Owner: librarian -- CREATE TABLE "pga_layout" ( "tablename" character varying(64), "nrcols" smallint, "colnames" text, "colwidth" text ); -- -- TOC Entry ID 14 (OID 16586) -- -- Name: pga_layout Type: ACL Owner: -- REVOKE ALL on "pga_layout" from PUBLIC; GRANT ALL on "pga_layout" to PUBLIC; GRANT ALL on "pga_layout" to "librarian"; -- -- TOC Entry ID 15 (OID 16593) -- -- Name: customer_accounts Type: TABLE Owner: librarian -- CREATE TABLE "customer_accounts" ( "cust_id" integer NOT NULL, "cust_name" character(30), "cust_phone" character(12), "cust_address" character(100), "cust_email" character(60), Constraint "customer_accounts_pkey" Primary Key ("cust_id") ); -- -- TOC Entry ID 16 (OID 16597) -- -- Name: signed_out Type: TABLE Owner: librarian -- CREATE TABLE "signed_out" ( "media_id" integer, "cust_id" integer, "out_date" date ); -- -- TOC Entry ID 17 (OID 16600) -- -- Name: returned_back Type: TABLE Owner: librarian -- CREATE TABLE "returned_back" ( "media_id" integer, "cust_id" integer, "out_date" date, "returned_date" date ); -- -- Data for TOC Entry ID 18 (OID 16557) -- -- Name: pga_queries Type: TABLE DATA Owner: librarian -- File: 18.dat -- COPY "pga_queries" FROM stdin; \. copy "pga_queries" from '$$PATH$$/18.dat' ; -- -- Data for TOC Entry ID 19 (OID 16562) -- -- Name: pga_forms Type: TABLE DATA Owner: librarian -- File: 19.dat -- COPY "pga_forms" FROM stdin; \. copy "pga_forms" from '$$PATH$$/19.dat' ; -- -- Data for TOC Entry ID 20 (OID 16567) -- -- Name: pga_scripts Type: TABLE DATA Owner: librarian -- File: 20.dat -- COPY "pga_scripts" FROM stdin; \. copy "pga_scripts" from '$$PATH$$/20.dat' ; -- -- Data for TOC Entry ID 21 (OID 16572) -- -- Name: pga_reports Type: TABLE DATA Owner: librarian -- File: 21.dat -- COPY "pga_reports" FROM stdin; \. copy "pga_reports" from '$$PATH$$/21.dat' ; -- -- Data for TOC Entry ID 22 (OID 16577) -- -- Name: pga_schema Type: TABLE DATA Owner: librarian -- File: 22.dat -- COPY "pga_schema" FROM stdin; \. copy "pga_schema" from '$$PATH$$/22.dat' ; -- -- Data for TOC Entry ID 23 (OID 16583) -- -- Name: object_media Type: TABLE DATA Owner: librarian -- File: 23.dat -- COPY "object_media" FROM stdin; \. copy "object_media" from '$$PATH$$/23.dat' ; -- -- Data for TOC Entry ID 24 (OID 16586) -- -- Name: pga_layout Type: TABLE DATA Owner: librarian -- File: 24.dat -- COPY "pga_layout" FROM stdin; \. copy "pga_layout" from '$$PATH$$/24.dat' ; -- -- Data for TOC Entry ID 25 (OID 16593) -- -- Name: customer_accounts Type: TABLE DATA Owner: librarian -- File: 25.dat -- COPY "customer_accounts" FROM stdin; \. copy "customer_accounts" from '$$PATH$$/25.dat' ; -- -- Data for TOC Entry ID 26 (OID 16597) -- -- Name: signed_out Type: TABLE DATA Owner: librarian -- File: 26.dat -- COPY "signed_out" FROM stdin; \. copy "signed_out" from '$$PATH$$/26.dat' ; -- -- Data for TOC Entry ID 27 (OID 16600) -- -- Name: returned_back Type: TABLE DATA Owner: librarian -- File: 27.dat -- COPY "returned_back" FROM stdin; \. copy "returned_back" from '$$PATH$$/27.dat' ;