SELECT CURRENT_USER;


SELECT
1 + 3
;


SELECT
2 * 10 + 1
\p

\g


CREATE TABLE friend (
             firstname CHAR(15),
             lastname  CHAR(20),
             city      CHAR(15),
             state     CHAR(2),
             age       INTEGER
);

\d friend

INSERT INTO friend VALUES (
                           'Mike', 
                           'Nichols', 
                           'Tampa',
                           'FL',
                           19
);

INSERT INTO friend VALUES (
                           'Cindy', 
                           'Anderson', 
                           'Denver',
                           'CO',
                           23
);

INSERT INTO friend VALUES (
                           'Sam', 
                           'Jackson', 
                           'Allentown',
                           'PA',
                           22
);

SELECT * FROM friend;

SELECT * FROM friend WHERE age = 23;

SELECT * FROM friend WHERE age <= 22;

SELECT lastname FROM friend WHERE age = 22;

SELECT city, state FROM friend WHERE age >= 21;

SELECT * FROM friend WHERE firstname = 'Sam';

SELECT * FROM friend;

INSERT INTO friend VALUES ('Jim', 'Barnes', 'Ocean City','NJ', 25);

SELECT * FROM friend;

DELETE FROM friend WHERE lastname = 'Barnes';

SELECT * FROM friend;

UPDATE friend SET age = 20 WHERE firstname = 'Mike';

SELECT * FROM friend;

SELECT * FROM friend ORDER BY state;

SELECT * FROM friend ORDER BY age DESC;

SELECT * FROM friend WHERE age >= 21 ORDER BY firstname;

CREATE TABLE alltypes (
                 state CHAR(2), 
                 name CHAR(30), 
                 children INTEGER, 
                 distance FLOAT, 
                 budget NUMERIC(16,2), 
                 born DATE, 
                 checkin TIME, 
                 started TIMESTAMP  
);

INSERT INTO alltypes
VALUES ( 
        'PA',
        'Hilda Blairwood',
         3,
         10.7,
         4308.20,
         '9/8/1974',
         '9:00',
         '07/03/1996 10:30:00');

SELECT state, name, children, distance, budget FROM alltypes;

SELECT born, checkin, started FROM alltypes;



SELECT * FROM alltypes;

INSERT INTO friend (firstname, lastname, city, state)  
VALUES ('Mark', 'Middleton', 'Indianapolis', 'IN');

SELECT * FROM friend ORDER BY age DESC;

SELECT * FROM friend WHERE age > 0 ORDER BY age DESC;

SELECT * FROM friend WHERE age <> 99 ORDER BY age DESC;

SELECT * FROM friend WHERE age IS NULL ORDER BY age DESC;

INSERT INTO friend
VALUES ('Jack', 'Burger', NULL, NULL, 27);

SELECT * FROM friend WHERE city = state;

CREATE TABLE nulltest (name CHAR(20), spouse CHAR(20));

INSERT INTO nulltest VALUES ('Andy', '');

INSERT INTO nulltest VALUES ('Tom', NULL);

SELECT * FROM nulltest ORDER BY name;

SELECT * FROM nulltest WHERE spouse = '';

SELECT * FROM nulltest WHERE spouse IS NULL;

CREATE TABLE account (
        name     CHAR(20), 
        balance  NUMERIC(16,2) DEFAULT 0,
        active   CHAR(1) DEFAULT 'Y',
        created  TIMESTAMP DEFAULT CURRENT_TIMESTAMPindex{timestamp!current}
);

INSERT INTO account (name) 
VALUES ('Federated Builders');

SELECT * FROM account;

SELECT firstname AS buddy FROM friend ORDER BY buddy;

SELECT 1 + 3 AS total;

-- a single line comment
/* a multiline
   comment */

DELETE FROM friend;

INSERT INTO friend
VALUES ('Dean', 'Yeager', 'Plymouth', 'MA', 24);

INSERT INTO friend
VALUES ('Dick', 'Gleason', 'Ocean City', 'NJ', 19);

INSERT INTO friend
VALUES ('Ned', 'Millstone', 'Cedar Creek', 'MD', 27);

INSERT INTO friend
VALUES ('Sandy', 'Gleason', 'Ocean City', 'NJ', 25);

INSERT INTO friend
VALUES ('Sandy', 'Weber', 'Boston', 'MA', 33);

INSERT INTO friend
VALUES ('Victor', 'Tabor', 'Williamsport', 'PA', 22);

SELECT * FROM friend ORDER BY firstname;

SELECT * FROM friend
WHERE firstname = 'Sandy' AND lastname = 'Gleason';

SELECT * FROM friend
WHERE state = 'NJ' OR state = 'PA' 
ORDER BY firstname;

SELECT * FROM friend
WHERE firstname = 'Victor' AND state = 'PA' OR state = 'NJ'
ORDER BY firstname;

SELECT * FROM friend
WHERE firstname = 'Victor' AND (state = 'PA' OR state = 'NJ')
ORDER BY firstname;

SELECT *
FROM friend
WHERE age >= 22 AND age <= 25
ORDER BY firstname;

SELECT *
FROM friend
WHERE age BETWEEN 22 AND 25
ORDER BY firstname;

SELECT * FROM friend 
WHERE firstname LIKE 'D%'
ORDER BY firstname;

SELECT * FROM friend 
ORDER BY firstname;

-- firstname begins with 'S'
SELECT * FROM friend 
WHERE firstname textasciitilde 'textasciicircumS' 
ORDER BY firstname;

-- firstname has an e in the second position
SELECT * FROM friend 
WHERE firstname textasciitilde 'textasciicircum.e' 
ORDER BY firstname;

-- firstname contains b, B, c, or C
SELECT * FROM friend 
WHERE firstname textasciitilde* '[bc]' 
ORDER BY firstname;

-- firstname does not contain s or S
SELECT * FROM friend 
WHERE firstname !textasciitilde* 's' 
ORDER BY firstname;

-- firstname ends with n
SELECT * FROM friend 
WHERE firstname textasciitilde 'n *$' 
ORDER BY firstname;

-- firstname contains a non-S character
SELECT * FROM friend 
WHERE firstname textasciitilde '[textasciicircumS]' 
ORDER BY firstname;

SELECT firstname,
       age,
       CASE
           WHEN age >= 21 THEN 'adult'
           ELSE 'minor'
       END
FROM friend
ORDER BY firstname;

SELECT  firstname, 
        state, 
        CASE
                WHEN state = 'PA' THEN 'close'
                WHEN state = 'NJ' OR state = 'MD' THEN 'far'
                ELSE 'very far'
        END AS distance
FROM friend
ORDER BY firstname;

SELECT state FROM friend ORDER BY state;

SELECT DISTINCT state FROM friend ORDER BY state;

SELECT DISTINCT city, state FROM friend ORDER BY state, city;

\df

\df int

\df upper

\dd upper

SELECT upper('jacket');

SELECT sqrt(2.0);  -- square root

\do

\do /

\do textasciicircum

\dd textasciicircum

SELECT 2 + 3 textasciicircum 4;

SHOW DATESTYLE;

SET DATESTYLE TO 'SQL, EUROPEAN';

SHOW DATESTYLE;

RESET DATESTYLE;

SHOW DATESTYLE;

SELECT * FROM friend ORDER BY firstname;

SELECT COUNT(*) FROM friend;

SELECT SUM(age) FROM friend;

SELECT MAX(age) FROM friend;

SELECT MIN(age) FROM friend;

SELECT AVG(age) FROM friend;

CREATE TABLE aggtest (col INTEGER);

INSERT INTO aggtest VALUES (NULL);

SELECT SUM(col) FROM aggtest;

SELECT MAX(col) FROM aggtest;

SELECT COUNT(*) FROM aggtest;

SELECT COUNT(col) FROM aggtest;

INSERT INTO aggtest VALUES (3);

SELECT AVG(col) FROM aggtest;

SELECT COUNT(*) FROM aggtest;

SELECT COUNT(col) FROM aggtest;

SELECT state, COUNT(*) 
FROM friend
GROUP BY state;

SELECT state, MIN(age), MAX(age), AVG(age)  
FROM friend
GROUP BY state
ORDER BY 4 DESC;

SELECT city, state, COUNT(*)
FROM friend
GROUP BY state, city
ORDER BY 1, 2;

SELECT state, COUNT(*)
FROM friend
GROUP BY state
HAVING COUNT(*) > 1
ORDER BY state;

SELECT firstname FROM friend WHERE state = 'PA';

SELECT friend.firstname FROM friend WHERE friend.state = 'PA';

SELECT f.firstname FROM friend f WHERE f.state = 'PA';

CREATE TABLE customer (                              
                       customer_id INTEGER,
                       name        CHAR(30),
                       telephone   CHAR(20),
                       street      CHAR(40),
                       city        CHAR(25),
                       state       CHAR(2),
                       zipcode     CHAR(10),
                       country     CHAR(20)
);

CREATE TABLE employee (
                       employee_id INTEGER,
                       name        CHAR(30),
                       hire_date   DATE
);

CREATE TABLE part (
                   part_id     INTEGER,
                   name        CHAR(30),
                   cost        NUMERIC(8,2),
                   weight      FLOAT
);

CREATE TABLE salesorder (
                order_id      INTEGER,
                customer_id   INTEGER,  -- joins to customer.customer_id
                employee_id   INTEGER,  -- joins to employee.employee_id
                part_id       INTEGER,  -- joins to part.part_id
                order_date    DATE,
                ship_date     DATE, 
                payment       NUMERIC(8,2)
);

INSERT INTO customer VALUES (
                             648,
                             'Fleer Gearworks, Inc.',
                             '1-610-555-7829',
                             '830 Winding Way',
                             'Millersville',
                             'AL',
                             '35041',
                             'USA'
);

INSERT INTO employee VALUES (
                             24,
                             'Lee Meyers',
                             '10/16/1989'
);

INSERT INTO part VALUES ( 
                          153,
                          'Garage Door Spring',
                          6.20
);

INSERT INTO salesorder VALUES( 
                              14673,
                              648,
                              24, 
                              153,
                              '7/19/1994',
                              '7/28/1994',
                              18.39
);

SELECT customer_id FROM salesorder WHERE order_id = 14673;

SELECT name FROM customer WHERE customer_id = 648;  

SELECT customer.name                    -- query result
FROM   customer, salesorder             -- query tables
                                        -- table join
WHERE  customer.customer_id = salesorder.customer_id AND
       salesorder.order_id = 14673;     -- query restriction

SELECT salesorder.order_id
FROM   salesorder, customer
WHERE  customer.name = 'Fleer Gearworks, Inc.' AND 
       salesorder.customer_id = customer.customer_id;

SELECT customer.name, employee.name
FROM   salesorder, customer, employee
WHERE  salesorder.customer_id = customer.customer_id AND
       salesorder.employee_id = employee.employee_id AND
       salesorder.order_id = 14673;

SELECT customer.name AS customer_name, 
       employee.name AS employee_name, 
       part.name AS part_name
FROM   salesorder, customer, employee, part
WHERE  salesorder.customer_id = customer.customer_id AND
       salesorder.employee_id = employee.employee_id AND
       salesorder.part_id = part.part_id AND
       salesorder.order_id = 14673;

SELECT DISTINCT customer.name, employee.name
FROM   customer, employee, salesorder
WHERE  customer.customer_id = salesorder.customer_id and
       salesorder.employee_id = employee.employee_id
ORDER BY customer.name, employee.name;

SELECT DISTINCT customer.name, employee.name, COUNT(*)
FROM   customer, employee, salesorder
WHERE  customer.customer_id = salesorder.customer_id and
       salesorder.employee_id = employee.employee_id
GROUP BY customer.name, employee.name 
ORDER BY customer.name, employee.name;

CREATE TABLE statename (code CHAR(2),
                         name  CHAR(30) 
);

INSERT INTO statename VALUES ('AL', 'Alabama');

SELECT statename.name AS customer_statename
FROM   customer, statename
WHERE  customer.customer_id = 648 AND
       customer.state = statename.code;

SELECT * FROM animal;

SELECT * FROM vegetable;

SELECT *
FROM animal, vegetable
WHERE animal.animal_id = vegetable.animal_id;

SELECT *
FROM animal, vegetable;

CREATE TABLE oidtest(age INTEGER);

INSERT INTO oidtest VALUES (7);

SELECT oid, age FROM oidtest;

CREATE TABLE salesorder (
            order_id      INTEGER,
            customer_oid   OID,  -- joins to customer.oid
            employee_oid   OID,  -- joins to employee.oid
            part_oid       OID,  -- joins to part.oid

CREATE SEQUENCE functest_seq;

SELECT nextval('functest_seq');

SELECT nextval('functest_seq');

SELECT currval('functest_seq');

SELECT setval('functest_seq', 100);

SELECT nextval('functest_seq');

CREATE SEQUENCE customer_seq;

CREATE TABLE customer (
             customer_id INTEGER DEFAULT nextval('customer_seq'),
             name CHAR(30)
);

INSERT INTO customer VALUES (nextval('customer_seq'), 'Bread Makers');

INSERT INTO customer (name) VALUES ('Wax Carvers');

INSERT INTO customer (name) VALUES ('Pipe Fitters');

SELECT * FROM customer;

CREATE TABLE customer (
          customer_id SERIAL,
          name CHAR(30)
);

\d customer

INSERT INTO customer (name) VALUES ('Car Wash');      

SELECT * FROM customer;

SELECT firstname 
FROM friend
UNION
SELECT lastname
FROM friend
ORDER BY 1;

INSERT INTO terrestrial_animal (name) VALUES ('tiger');

INSERT INTO aquatic_animal (name) VALUES ('swordfish');

SELECT name      
FROM   aquatic_animal 
UNION
SELECT name
FROM   terrestrial_animal;

INSERT INTO aquatic_animal (name) VALUES ('penguin');

INSERT INTO terrestrial_animal (name) VALUES ('penguin');

SELECT name
FROM   aquatic_animal
UNION 
SELECT name
FROM   terrestrial_animal;

SELECT name
FROM   aquatic_animal
UNION ALL
SELECT name
FROM   terrestrial_animal;

SELECT name
FROM   aquatic_animal
EXCEPT
SELECT name
FROM   terrestrial_animal;

SELECT name
FROM   aquatic_animal
INTERSECT
SELECT name
FROM   terrestrial_animal;

SELECT * FROM friend ORDER BY firstname;

SELECT f1.firstname, f1.lastname, f1.state
FROM   friend f1, friend f2
WHERE  f1.state <> f2.state AND
       f2.firstname = 'Dick' AND
       f2.lastname = 'Gleason'
ORDER BY firstname, lastname;

SELECT f1.firstname, f1.lastname, f1.state
FROM   friend f1
WHERE  f1.state <> (
                    SELECT f2.state
                    FROM   friend f2
                    WHERE  f2.firstname = 'Dick' AND
                           f2.lastname = 'Gleason'
                   )
ORDER BY firstname, lastname;

SELECT name
FROM   customer, salesorder
WHERE  customer.customer_id = salesorder.customer_id AND
       salesorder.order_id = 14673;

SELECT name
FROM   customer            
WHERE  customer.customer_id = (                         
                               SELECT salesorder.customer_id
                               FROM salesorder
                               WHERE order_id = 14673
                              ); 

SELECT f1.firstname, f1.lastname, f1.age
FROM   friend f1, friend f2
WHERE  f1.state = f2.state
GROUP BY f2.state, f1.firstname, f1.lastname, f1.age
HAVING f1.age = max(f2.age)
ORDER BY firstname, lastname;

SELECT f1.firstname, f1.lastname, f1.age
FROM   friend f1
WHERE  age = (
              SELECT MAX(f2.age)
              FROM friend f2
              WHERE f1.state = f2.state
             )
ORDER BY firstname, lastname;

SELECT DISTINCT employee.name
FROM   employee, salesorder
WHERE  employee.employee_id = salesorder.employee_id AND
       salesorder.order_date = '7/19/1994';

SELECT name
FROM   employee
WHERE  employee_id IN (
                       SELECT employee_id
                       FROM   salesorder
                       WHERE  order_date = '7/19/1994'
                      );

SELECT name
FROM   customer
WHERE  customer_id NOT IN (
                           SELECT customer_id
                           FROM salesorder
                          );

DELETE FROM customer   
WHERE customer_id NOT IN (
                          SELECT customer_id
                          FROM salesorder
                         );

UPDATE salesorder
SET    ship_date = '11/16/96'
WHERE  customer_id = (
                      SELECT customer_id 
                      FROM   customer
                      WHERE  name = 'Fleer Gearworks, Inc.'
                     );

INSERT INTO customer (name, city, state, country)
SELECT trim(firstname) || ' ' || lastname, city, state, 'USA'
FROM friend;

SELECT firstname, lastname, city, state 
INTO   newfriend
FROM   friend;

\d newfriend

SELECT * FROM newfriend ORDER BY firstname;

SELECT * FROM functest;

SELECT upper(name) FROM functest;

SELECT date_part('year', '5/8/1971');

SELECT date_part('year', CAST('5/8/1971' AS DATE));

SELECT CAST('1/1/1992' AS DATE) + CAST('1/1/1993' AS DATE);

SELECT CAST('1/1/1992' AS DATE) + CAST('1 year' AS INTERVAL);

SELECT CAST('1/1/1992' AS TIMESTAMP) + '1 year';

CREATE TABLE array_test (
                         col1  INTEGER[5],
                         col2  INTEGER[][],
                         col3  INTEGER[2][2][]
);

INSERT INTO array_test VALUES (
                               '{1,2,3,4,5}',
                               '{{1,2},{3,4}}',
                               '{{{1,2},{3,4}},{{5,6}, {7,8}}}' 
);

SELECT * FROM array_test;

SELECT col1[4] FROM array_test;

SELECT col2[2][1] FROM array_test;

SELECT col3[1][2][2] FROM array_test;

CREATE TABLE fruit (name CHAR(30), image OID);

INSERT INTO fruit
VALUES ('peach', lo_import('/usr/images/peach.jpg'));

SELECT lo_export(fruit.image, '/tmp/outimage.jpg')
FROM   fruit 
WHERE  name = 'peach';

SELECT lo_unlink(fruit.image) FROM fruit;

INSERT INTO trans_test VALUES (1);

BEGIN WORK;

INSERT INTO trans_test VALUES (1);

COMMIT WORK;

BEGIN WORK;

INSERT INTO trans_test VALUES (1);

INSERT INTO trans_test VALUES (2);

COMMIT WORK;

BEGIN WORK;

UPDATE bankacct SET balance = balance - 100 WHERE acctno = '82021';

UPDATE bankacct SET balance = balance + 100 WHERE acctno = '96814';

COMMIT WORK;

INSERT INTO rollback_test VALUES (1);

BEGIN WORK;

DELETE FROM rollback_test;

ROLLBACK WORK;

SELECT * FROM rollback_test;

BEGIN WORK;

SELECT COUNT(*) FROM trans_test;

--
-- someone commits INSERT INTO trans_test
--
SELECT COUNT(*) FROM trans_test;

COMMIT WORK;

BEGIN WORK;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SELECT COUNT(*) FROM trans_test;

--
-- someone commits INSERT INTO trans_test
--
SELECT COUNT(*) FROM trans_test;

COMMIT WORK;

BEGIN WORK;

SELECT *
FROM lock_test
WHERE name = 'James';

--
-- the SELECTed row is not locked
--
UPDATE lock_test
SET name = 'Jim'
WHERE name = 'James';

COMMIT WORK;

BEGIN WORK;

SELECT *
FROM lock_test
WHERE name = 'James'
FOR UPDATE;

--
-- the SELECTed row is locked
--
UPDATE lock_test
SET name = 'Jim'
WHERE name = 'James';

COMMIT WORK;

CREATE INDEX customer_custid_idx ON customer (customer_id);

CREATE TABLE duptest (channel INTEGER);

CREATE UNIQUE INDEX duptest_channel_idx ON duptest (channel);

INSERT INTO duptest VALUES (1);

INSERT INTO duptest VALUES (1);

EXPLAIN SELECT customer_id FROM customer;

EXPLAIN SELECT customer_id FROM customer WHERE customer_id = 55;

VACUUM ANALYZE customer;

EXPLAIN SELECT customer_id FROM customer WHERE customer_id = 55;

CREATE UNIQUE INDEX customer_custid_idx ON customer (customer_id);

EXPLAIN SELECT customer_id FROM customer WHERE customer_id = 55;

EXPLAIN SELECT customer_id FROM customer;

EXPLAIN SELECT * FROM customer ORDER BY customer_id;

EXPLAIN SELECT * FROM tab1, tab2 WHERE col1 = col2;

SELECT customer_id FROM customer ORDER BY customer_id LIMIT 3;

SELECT customer_id FROM customer ORDER BY customer_id LIMIT 3 OFFSET 997;

BEGIN WORK;

DECLARE customer_cursor CURSOR FOR
SELECT customer_id FROM customer;

FETCH 1 FROM customer_cursor;

FETCH 1 FROM customer_cursor;

FETCH 2 FROM customer_cursor;

FETCH -1 FROM customer_cursor;

FETCH -1 FROM customer_cursor;

MOVE 10 FROM customer_cursor;

FETCH 1 FROM customer_cursor;

CLOSE customer_cursor;

COMMIT WORK;

CREATE TEMPORARY TABLE temptest(col INTEGER);

SELECT * FROM temptest;



SELECT * FROM temptest;

SELECT *
INTO TEMPORARY customer_pennsylvania
FROM customer
WHERE state = 'PA';

CREATE index customer_penna_custid_idx ON customer_pennsylvania (customer_id);

CREATE TABLE altertest (col1 INTEGER);

ALTER TABLE altertest RENAME TO alterdemo;

ALTER TABLE alterdemo RENAME COLUMN col1 TO democol;

ALTER TABLE alterdemo ADD COLUMN col2 INTEGER;

-- show renamed table, renamed column, and new column
\d alterdemo

ALTER TABLE alterdemo ALTER COLUMN col2 SET DEFAULT 0;

-- show new default value
\d alterdemo 

ALTER TABLE alterdemo ALTER COLUMN col2 DROP DEFAULT;  

CREATE TABLE permtest (col INTEGER);

-- now only the owner can use permtest
GRANT SELECT ON permtest TO meyers;

-- now user 'meyers' can do SELECTs on permtest
GRANT ALL ON permtest TO PUBLIC;  

-- now all users can perform all operations on permtest

CREATE TABLE parent_test (col1 INTEGER);

CREATE TABLE child_test (col2 INTEGER) INHERITS (parent_test);

\d parent_test

\d child_test

INSERT INTO parent_test VALUES (1);

INSERT INTO child_test VALUES (2,3);

SELECT * FROM parent_test;

SELECT * FROM child_test;

SELECT * FROM parent_test*;

CREATE TABLE grandchild_test (col3 INTEGER) INHERITS (child_test);

INSERT INTO grandchild_test VALUES (4, 5, 6);

SELECT * FROM parent_test*;

SELECT * FROM child_test*;

CREATE VIEW customer_ohio AS                
SELECT *
FROM customer
WHERE state = 'OH';

-- let sanders see only Ohio customers
GRANT SELECT ON customer_ohio TO sanders;

-- create view to show only certain columns
CREATE VIEW customer_address AS                
SELECT customer_id, name, street, city, state, zipcode, country
FROM customer;

-- create view that combines fields from two tables
CREATE VIEW customer_finance AS                
SELECT customer.customer_id, customer.name, finance.credit_limit
FROM customer, finance
WHERE customer.customer_id = finance.customer_id;

CREATE TABLE ruletest (col INTEGER);

CREATE RULE ruletest_insert AS     -- rule name
ON INSERT TO ruletest              -- INSERT rule
DO INSTEAD                         -- DO INSTEAD-type rule
    NOTHING;                       -- ACTION is NOTHING

INSERT INTO ruletest VALUES (1);
SELECT * FROM ruletest;

CREATE TABLE service_request (
                   customer_id INTEGER,
                   description text,
                   cre_user text DEFAULT CURRENT_USER,
                   cre_timestamp timestamp DEFAULT CURRENT_TIMESTAMPindex{timestamp!current});

CREATE TABLE service_request_log (
                   customer_id INTEGER,
                   description text,
                   mod_type char(1),
                   mod_user text DEFAULT CURRENT_USER,
                   mod_timestamp timestamp DEFAULT CURRENT_TIMESTAMP);

CREATE RULE service_request_update AS     -- UPDATE rule
ON UPDATE TO service_request 
DO 
    INSERT INTO service_request_log (customer_id, description, mod_type)
    VALUES (old.customer_id, old.description, 'U');

CREATE RULE service_request_delete AS     -- DELETE rule
ON DELETE TO service_request 
DO
    INSERT INTO service_request_log (customer_id, description, mod_type)
    VALUES (old.customer_id, old.description, 'D');

INSERT INTO service_request (customer_id, description)
VALUES (72321, 'Fix printing press');

UPDATE service_request 
SET description = 'Fix large printing press'
WHERE customer_id = 72321;

DELETE FROM service_request 
WHERE customer_id = 72321;

SELECT * 
FROM service_request_log
WHERE customer_id = 72321;

CREATE TABLE realtable (col INTEGER);

CREATE VIEW view_realtable AS SELECT * FROM realtable;

INSERT INTO realtable VALUES (1);

INSERT INTO view_realtable VALUES (2);

SELECT * FROM realtable;

SELECT * FROM view_realtable;

CREATE RULE view_realtable_insert AS     -- INSERT rule
ON INSERT TO view_realtable 
DO INSTEAD 
    INSERT INTO realtable 
    VALUES (new.col);

CREATE RULE view_realtable_update AS     -- UPDATE rule
ON UPDATE TO view_realtable 
DO INSTEAD 
    UPDATE realtable 
    SET col = new.col 
    WHERE col = old.col;

CREATE RULE view_realtable_delete AS     -- DELETE rule
ON DELETE TO view_realtable 
DO INSTEAD 
    DELETE FROM realtable 
    WHERE col = old.col;

INSERT INTO view_realtable VALUES (3);

SELECT * FROM view_realtable;

UPDATE view_realtable 
SET col = 4;

SELECT * FROM view_realtable;

DELETE FROM view_realtable;

SELECT * FROM view_realtable; 

CREATE TABLE not_null_test (
                            col1 INTEGER, 
                            col2 INTEGER NOT NULL
                           );

INSERT INTO not_null_test
VALUES (1, NULL);

INSERT INTO not_null_test (col1)
VALUES (1);

INSERT INTO not_null_test VALUES (1, 1);

UPDATE  not_null_test SET col2 = NULL;

CREATE TABLE not_null_with_default_test (
                                         col1 INTEGER,
                                         col2 INTEGER NOT NULL DEFAULT 5
                                        );

INSERT INTO not_null_with_default_test (col1) 
VALUES (1);

SELECT * 
FROM not_null_with_default_test;

CREATE TABLE uniquetest (col1 INTEGER UNIQUE);

\d uniquetest

INSERT INTO uniquetest VALUES (1);

INSERT INTO uniquetest VALUES (1);

INSERT INTO uniquetest VALUES (NULL);

INSERT INTO uniquetest VALUES (NULL);

CREATE TABLE uniquetest2 (
                          col1 INTEGER, 
                          col2 INTEGER, 
                          UNIQUE (col1, col2)
                         );

CREATE TABLE primarytest (col INTEGER PRIMARY KEY);

\d primarytest 

CREATE TABLE primarytest2 (
                           col1 INTEGER, 
                           col2 INTEGER, 
                           PRIMARY KEY(col1, col2)
                          );

 CREATE TABLE statename (code CHAR(2) PRIMARY KEY,
                         name  CHAR(30) 
);

INSERT INTO statename VALUES ('AL', 'Alabama');

CREATE TABLE customer (                              
                       customer_id INTEGER,
                       name        CHAR(30),
                       telephone   CHAR(20),
                       street      CHAR(40),
                       city        CHAR(25),
                       state       CHAR(2) REFERENCES statename,
                       zipcode     CHAR(10),
                       country     CHAR(20)
);

INSERT INTO customer (state) 
VALUES ('AL');

INSERT INTO customer (state)
VALUES ('XX');

CREATE TABLE customer (                              
                       customer_id INTEGER PRIMARY KEY,
                       name        CHAR(30),
                       telephone   CHAR(20),
                       street      CHAR(40),
                       city        CHAR(25),
                       state       CHAR(2),
                       zipcode     CHAR(10),
                       country     CHAR(20)
);

CREATE TABLE employee (
                       employee_id INTEGER PRIMARY KEY,
                       name        CHAR(30),
                       hire_date   DATE
);

CREATE TABLE part (
                   part_id     INTEGER PRIMARY KEY,
                   name        CHAR(30),
                   cost        NUMERIC(8,2),
                   weight      FLOAT
);

CREATE TABLE salesorder (
                         order_id      INTEGER,
                         customer_id   INTEGER REFERENCES customer,
                         employee_id   INTEGER REFERENCES employee,
                         part_id       INTEGER REFERENCES part,
                         order_date    DATE,
                         ship_date     DATE, 
                         payment       NUMERIC(8,2)
);

CREATE TABLE customer (                              
                       customer_id INTEGER,
                       name        CHAR(30),
                       telephone   CHAR(20),
                       street      CHAR(40),
                       city        CHAR(25),
                       state       CHAR(2) REFERENCES statename 
                                           ON UPDATE CASCADE 
                                           ON DELETE SET NULL,
                       zipcode     CHAR(10),
                       country     CHAR(20)
);

CREATE TABLE primarytest (col INTEGER PRIMARY KEY);

CREATE TABLE foreigntest (
                          col2 INTEGER REFERENCES primarytest
                          ON UPDATE CASCADE 
                          ON DELETE NO ACTION
                         );

INSERT INTO primarytest values (1);

INSERT INTO foreigntest values (1);

-- CASCADE UPDATE is performed
UPDATE primarytest SET col = 2;

SELECT * FROM foreigntest;


-- NO ACTION prevents deletion
DELETE FROM primarytest;

-- By deleting the foreign key first, the DELETE succeeds
DELETE FROM foreigntest;

DELETE FROM primarytest;

CREATE TABLE primarytest2 (
                           col1 INTEGER, 
                           col2 INTEGER, 
                           PRIMARY KEY(col1, col2)
                          );

CREATE TABLE foreigntest2 (col3 INTEGER, 
                           col4 INTEGER,
                           FOREIGN KEY (col3, col4) REFERENCES primarytest2
                         );

INSERT INTO primarytest2 
VALUES (1,2);

INSERT INTO foreigntest2
VALUES (1,2);

UPDATE foreigntest2
SET col4 = NULL;

CREATE TABLE matchtest (
                   col3 INTEGER,
                   col4 INTEGER,
                   FOREIGN KEY (col3, col4) REFERENCES primarytest2 
                                            MATCH FULL
                       );

UPDATE matchtest 
SET col3 = NULL, col4 = NULL;

UPDATE matchtest
SET col4 = NULL;

CREATE TABLE defertest( 
                       col2 INTEGER REFERENCES primarytest 
                                    DEFERRABLE
);

BEGIN;

-- INSERT is attempted in non-DEFERRABLE mode
INSERT INTO defertest VALUES (5);

COMMIT;

BEGIN;

-- all foreign key constraints are set to DEFERRED
SET CONSTRAINTS ALL DEFERRED;

INSERT INTO defertest VALUES (5);

INSERT INTO primarytest VALUES (5);

COMMIT;

CREATE TABLE friend2 (
             firstname CHAR(15),
             lastname  CHAR(20),
             city      CHAR(15),
             state     CHAR(2)      CHECK (length(trim(state)) = 2),
             age       INTEGER      CHECK (age >= 0),
             gender    CHAR(1)      CHECK (gender IN ('M','F')),
             last_met  DATE         CHECK (last_met BETWEEN '1950-01-01'
                                           AND CURRENT_DATE),
             CHECK (upper(trim(firstname)) != 'ED' OR
                    upper(trim(lastname)) != 'RIVERS')
);

INSERT INTO friend2 
VALUES ('Ed', 'Rivers', 'Wibbleville', 'J', -35, 'S', '1931-09-23');

CREATE TABLE copytest (
                        intcol  INTEGER,
                        numcol  NUMERIC(16,2),
                        textcol TEXT,
                        boolcol BOOLEAN
);

INSERT INTO copytest
VALUES (1, 23.99, 'fresh spring water', 't');

INSERT INTO copytest
VALUES (2, 55.23, 'bottled soda', 't');

SELECT * FROM copytest;

COPY copytest TO '/tmp/copytest.out';

DELETE FROM copytest;

COPY copytest FROM '/tmp/copytest.out';

SELECT * FROM copytest;



COPY copytest TO '/tmp/copytest.out' USING DELIMITERS '|';



DELETE FROM copytest;

COPY copytest FROM '/tmp/copytest.out';

COPY copytest FROM '/tmp/copytest.out' USING DELIMITERS '|';

textbf{COPY copytest FROM stdin;}

textbf{COPY copytest TO stdout;} 



DELETE FROM copytest;

INSERT INTO copytest
VALUES (4, 837.20, 'abc|def', NULL);

COPY copytest TO stdout USING DELIMITERS '|';

SELECT NULL;

\pset tuples_only

SELECT NULL; 

\pset null '(null)'

SELECT NULL;

\set num_var 4
SELECT :num_var;

\set operation SELECT
:operation :num_var;

\set str_var '\'My long string\''
\echo :str_var

SELECT :str_var;

\set date_var `date`
\echo :date_var

\set date_var2 '\''`date`'\''
\echo :date_var2

SELECT :date_var2;

 CREATE TABLE statename (code CHAR(2) PRIMARY KEY,
                         name  CHAR(30) 
);

INSERT INTO statename VALUES ('AL', 'Alabama');

INSERT INTO statename VALUES ('AK', 'Alaska');

CREATE FUNCTION ftoc(float)
RETURNS float
AS 'SELECT ($1 - 32.0) * 5.0 / 9.0;'
LANGUAGE 'sql';

SELECT ftoc(68);

CREATE FUNCTION tax(numeric)
RETURNS numeric
AS 'SELECT ($1 * 0.06::numeric(8,2))::numeric(8,2);'
LANGUAGE 'sql';

SELECT tax(100);

CREATE TABLE part (
                   part_id     INTEGER,
                   name        CHAR(30),
                   cost        NUMERIC(8,2),
                   weight      FLOAT
);

INSERT INTO part VALUES (637, 'cable', 14.29, 5);

INSERT INTO part VALUES (638, 'sticker', 0.84, 1);

INSERT INTO part VALUES (639, 'bulb', 3.68, 3);

SELECT part_id,
       name, 
       cost, 
       tax(cost), 
       cost + tax(cost) AS total 
FROM part
ORDER BY part_id;

CREATE FUNCTION shipping(numeric)
RETURNS numeric
AS 'SELECT CASE
                WHEN $1 < 2             THEN CAST(3.00 AS numeric(8,2))
                WHEN $1 >= 2 AND $1 < 4 THEN CAST(5.00 AS numeric(8,2))
                WHEN $1 >= 4            THEN CAST(6.00 AS numeric(8,2))
           END;'
LANGUAGE 'sql';

SELECT part_id,
       trim(name) AS name,
       cost,
       tax(cost),
       cost + tax(cost) AS subtotal,
       shipping(weight),
       cost + tax(cost) + shipping(weight) AS total
FROM part
ORDER BY part_id;

CREATE FUNCTION getstatename(text)
RETURNS text
AS 'SELECT CAST(name AS TEXT) 
    FROM  statename 
    WHERE code = $1;'
LANGUAGE 'sql';

SELECT getstatename('AL');

SELECT customer.name, statename.name
FROM   customer, statename
WHERE  customer.state = statename.code
ORDER BY customer.name;

SELECT customer.name, getstatename(customer.state)
FROM   customer
ORDER BY customer.name;

CREATE FUNCTION getstatename2(text)
RETURNS text
AS 'DECLARE ret TEXT;
    BEGIN
        SELECT INTO ret CAST(name AS TEXT)
        FROM  statename
        WHERE code = $1;
        RETURN ret;
    END;'
LANGUAGE 'plpgsql';

CREATE FUNCTION spread(text)
RETURNS text
AS 'DECLARE
        str text;
        ret text;
        i   integer;
        len integer;
    BEGIN
        str := upper($1);
        ret := '''';           -- start with zero length
        i   := 1;
        len := length(str);
        WHILE i <= len LOOP  
            ret := ret || substr(str, i, 1) || '' '';
            i := i + 1;
        END LOOP;  
        RETURN ret;
    END;'
LANGUAGE 'plpgsql';

SELECT spread('Major Financial Report');

CREATE FUNCTION getstatecode(text)
RETURNS text
AS 'DECLARE
        state_str  statename.name%TYPE;
        statename_rec record;
        i          integer;
        len        integer;
        matches    record;
        search_str text;

    BEGIN
        state_str := initcap($1);               -- capitalization match column
        len := length(trim($1));
        i   := 2;

        SELECT INTO statename_rec *             -- first try for an exact match
        FROM   statename
        WHERE  name = state_str;
        IF FOUND
        THEN    RETURN statename_rec.code;
        END IF;

        WHILE i <= len LOOP                     -- test 2,4,6,... chars for match
            search_str = trim(substr(state_str, 1, i)) || ''%'';
            SELECT INTO matches COUNT(*)
            FROM   statename
            WHERE  name LIKE search_str;

            IF matches.count = 0                -- no matches, failure
            THEN   RETURN NULL;
            END IF;
            IF matches.count = 1                -- exactly one match, return it
            THEN   
                 SELECT INTO statename_rec *
                 FROM   statename
                 WHERE  name LIKE search_str;
                 IF FOUND
                 THEN    RETURN statename_rec.code;
                 END IF;
            END IF;
            i := i + 2;                         -- >1 match, try 2 more chars
        END LOOP;
        RETURN '''' ;
   END;'
LANGUAGE 'plpgsql';

SELECT getstatecode('Alabama');

SELECT getstatecode('ALAB');

SELECT getstatecode('Al');

SELECT getstatecode('Ail');

CREATE FUNCTION change_statename(char(2), char(30))
RETURNS boolean
AS 'DECLARE
    state_code ALIAS FOR $1;
    state_name ALIAS FOR $2;
    statename_rec RECORD;
 
    BEGIN
        IF length(state_code) = 0                   -- no state code, failure
        THEN    RETURN ''f'';
        ELSE
            IF length(state_name) != 0              -- is INSERT or UPDATE?
            THEN
                SELECT INTO statename_rec *
                FROM   statename
                WHERE  code = state_code;
                IF NOT FOUND                        -- is state not in table?
                THEN    INSERT INTO statename 
                        VALUES (state_code, state_name);
                ELSE    UPDATE statename
                        SET    name = state_name
                        WHERE  code = state_code;
                END IF;
                RETURN ''t'';
            ELSE                                    -- is DELETE
                SELECT INTO statename_rec *
                FROM   statename
                WHERE  code = state_code;
                IF FOUND
                THEN    DELETE FROM statename
                        WHERE code = state_code;
                        RETURN ''t'';
                ELSE    RETURN ''f'';
                END IF;
            END IF;
        END IF;
   END;'
LANGUAGE 'plpgsql';

DELETE FROM statename;

SELECT change_statename('AL','Alabama');

SELECT * FROM statename;

SELECT change_statename('AL','Bermuda');

SELECT * FROM statename;

SELECT change_statename('AL','');

SELECT change_statename('AL','');    -- row was already deleted

CREATE FUNCTION trigger_insert_update_statename()
RETURNS opaque
AS 'BEGIN
        IF new.code !textasciitilde ''textasciicircum[A-Za-z][A-Za-z]$''
        THEN    RAISE EXCEPTION ''State code must be two alphabetic characters.'';
        END IF;
        IF new.name !textasciitilde ''textasciicircum[A-Za-z ]*$''
        THEN    RAISE EXCEPTION ''State name must be only alphabetic characters.'';
        END IF;
        IF length(trim(new.name)) < 3
        THEN    RAISE EXCEPTION ''State name must longer than two characters.'';
        END IF;
        new.code = upper(new.code);               -- uppercase statename.code
        new.name = initcap(new.name);             -- capitalize statename.name
        RETURN new;
    END;'
LANGUAGE 'plpgsql';

CREATE TRIGGER trigger_statename
BEFORE INSERT OR UPDATE
ON statename
FOR EACH ROW
EXECUTE PROCEDURE trigger_insert_update_statename();

DELETE FROM statename;

INSERT INTO statename VALUES ('a', 'alabama');

INSERT INTO statename VALUES ('al', 'alabama2');

INSERT INTO statename VALUES ('al', 'al');

INSERT INTO statename VALUES ('al', 'alabama');

SELECT * FROM statename;

CREATE FUNCTION ctof(float)
RETURNS float
AS '/users/pgman/sample/ctof.so'
LANGUAGE 'C';

SELECT ctof(20);

CREATE USER demouser2;

ALTER USER demouser2 CREATEDB;

CREATE GROUP demogroup WITH USER demouser1, demouser2;

CREATE TABLE grouptest (col INTEGER);

GRANT ALL on grouptest TO GROUP demogroup;

\connect test demouser2 



CREATE DATABASE demodb2;

DROP DATABASE demodb1;

\connect demodb2

 textbf{\z}