-- Refreshers
-- #1
-- List schema names starting with "meta"
\dn meta*

-- #2
-- List table names starting with "meta"
\dt meta*.*

-- #3
-- List the record #, indicators, and value for rows in metabib.full_rec
-- with a tag of '245', a subfield of 'a', and a value containing 'mozart'
SELECT record, ind1, ind2, value
FROM metabib.full_rec
WHERE tag = '245'
  AND subfield = 'a'
  AND value ~ 'mozart'
;

-- #4
-- List the date of creation for the records returned in #3
SELECT record, bre.create_date
FROM biblio.record_entry bre
  INNER JOIN metabib.full_rec mfr
    ON mfr.record = bre.id
WHERE tag = '245'
  AND subfield = 'a'
  AND value ~ 'mozart'
;

-- Subquery practice
-- #1
SELECT shortname
FROM actor.org_unit
WHERE id IN (
  SELECT home_ou
  FROM actor.usr
  WHERE family_name LIKE 'S%'
) GROUP BY shortname;

-- #2
SELECT barcode
FROM asset.copy
WHERE id IN (
  SELECT target_copy
  FROM action.circulation
  WHERE circ_lib = (
    SELECT id
    FROM actor.org_unit
    WHERE shortname = 'BR3'
  )
  AND duration = '1 hour'::interval
);

-- CTE practice
-- #1
WITH libs AS (
  SELECT home_ou
  FROM actor.usr
  WHERE family_name LIKE 'S%'
)
SELECT shortname
FROM actor.org_unit aou
  INNER JOIN libs
    ON libs.home_ou = aou.id
GROUP BY shortname;

-- #2
WITH libs AS (
  SELECT id
  FROM actor.org_unit
  WHERE shortname = 'BR3'
), copies AS (
  SELECT target_copy
  FROM action.circulation
    INNER JOIN libs ON circ_lib = libs.id
  AND duration = '1 hour'::interval
)
SELECT barcode
FROM asset.copy ac
  INNER JOIN copies
    ON copies.target_copy = ac.id
;

-- View practice
-- #1
CREATE VIEW barcodes_and_branches AS
SELECT ac.barcode, circ.duration, aou.shortname 
FROM asset.copy ac
  INNER JOIN action.circulation circ
    ON circ.target_copy = ac.id
  INNER JOIN actor.org_unit aou
    ON aou.id = circ.circ_lib
;

-- #2
SELECT COUNT(*)
FROM barcodes_and_branches
WHERE shortname = 'BR4'
  AND duration = '1 hour'::interval
;

-- INSERT exercises
-- #1
INSERT INTO asset.copy_location (name, owning_lib)
VALUES ('Reserves', 4), ('Storage', 4);

-- #2
INSERT INTO actor.usr_note (usr, creator, pub, title, value)
SELECT id, 1, TRUE, 'S name', family_name
FROM actor.usr
WHERE family_name LIKE 'S%';

-- DELETE exercises
-- #1

-- UPDATE exercises
-- #1
BEGIN;
UPDATE actor.usr
  SET second_given_name = NULL
  WHERE second_given_name = '';
ROLLBACK;

-- #2
BEGIN;
UPDATE actor.usr
  SET email = usrname || '@example.org';
ROLLBACK;

-- #3
BEGIN;
UPDATE actor.usr
  SET usrname = card.barcode
  FROM actor.card card
  WHERE actor.usr.id = card.usr;
ROLLBACK;