Working With Data in PostgreSQL

sudo su - postgres

createdb acweb

wget https://github.com/linuxacademy/content-postgresql-deepdive/raw/master/acweb/acweb.tar

pg_restore --dbname=acweb --verbose /var/lib/pgsql/acweb.tar

psql

• List the databases with \l.
• Connect to acweb:

  \c acweb
  

• Get a count for the payment table:

  SELECT COUNT(*) FROM sales.payment;
  

CREATE TABLE payment_audit (
   payment_id INTEGER,
   customer_id INTEGER,
   amount numeric(6,2),
   payment_date timestamp without time zone NOT NULL,
   username VARCHAR(20),
   delete_date TIMESTAMP
);

CREATE FUNCTION audit_payment_deletes() RETURNS trigger AS $$
   BEGIN
      INSERT INTO payment_audit VALUES((OLD).*, current_user, current_timestamp);
      RETURN OLD;
   END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER audit_payment_deletes
BEFORE DELETE ON sales.payment 
FOR EACH ROW
EXECUTE PROCEDURE audit_payment_deletes();

CREATE VIEW sales.customer_addresses AS
   SELECT c.first_name, c.last_name , a.address, ct.name as city, s.name as state, a.postal_code
   FROM sales.customer c
   LEFT JOIN sales.address a on c.addr_id = a.addr_id
   LEFT JOIN sales.city ct on a.city_id = ct.city_id
   LEFT JOIN sales.state s on ct.state_id = s.state_id;

SELECT * FROM sales.customer
WHERE first_name = 'Mick'
AND last_name = 'Fowler';

INSERT INTO sales.payment (customer_id, amount, payment_date)
VALUES (303, 10, current_timestamp);

INSERT INTO sales.payment (customer_id, amount, payment_date)
VALUES (303, 20, current_timestamp);

INSERT INTO sales.payment (customer_id, amount, payment_date)
VALUES (303, 30, current_timestamp);

INSERT INTO sales.payment (customer_id, amount, payment_date)
VALUES (303, 40, current_timestamp);

INSERT INTO sales.payment (customer_id, amount, payment_date)
VALUES (303, 50, current_timestamp);

UPDATE sales.payment
SET amount = 15
WHERE customer_id = 303
AND amount = 30;

DELETE FROM sales.payment
WHERE customer_id = 303
AND amount > 20;

\x

SELECT * FROM sales.customer_addresses;

SELECT * FROM payment_audit;