---------------------------------------------------------------------------
--
-- advanced.sql-
--    Tutorial on advanced PostgreSQL features
--
--
-- Copyright (c) 1994, Regents of the University of California
--
-- src/tutorial/advanced.source
--
---------------------------------------------------------------------------

-----------------------------
-- Window Functions
-----------------------------

-- a sample table
CREATE TABLE empsalary (
    depname text,
    empno bigint,
    salary int,
    enroll_date date
);

INSERT INTO empsalary VALUES
('develop', 10, 5200, '2007-08-01'),
('sales', 1, 5000, '2006-10-01'),
('personnel', 5, 3500, '2007-12-10'),
('sales', 4, 4800, '2007-08-08'),
('personnel', 2, 3900, '2006-12-23'),
('develop', 7, 4200, '2008-01-01'),
('develop', 9, 4500, '2008-01-01'),
('sales', 3, 4800, '2007-08-01'),
('develop', 8, 6000, '2006-10-01'),
('develop', 11, 5200, '2007-08-15');

SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname)
  FROM empsalary;

SELECT depname, empno, salary,
       row_number() OVER (PARTITION BY depname ORDER BY salary DESC)
FROM empsalary;

SELECT salary, sum(salary) OVER () FROM empsalary;

SELECT salary, sum(salary) OVER (ORDER BY salary) FROM empsalary;

SELECT depname, empno, salary, enroll_date
FROM
  (SELECT depname, empno, salary, enroll_date,
     row_number() OVER (PARTITION BY depname ORDER BY salary DESC, empno) AS pos
     FROM empsalary
  ) AS ss
WHERE pos < 3;

SELECT sum(salary) OVER w, avg(salary) OVER w
  FROM empsalary
  WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);

DROP TABLE empsalary;

-----------------------------
-- Inheritance:
-- A table can inherit from zero or more tables.  A query can reference
-- either all rows of a table or all rows of a table plus all of its
-- descendants.
-----------------------------

-- For example, the capitals table inherits from cities table. (It inherits
-- all data fields from cities.)

CREATE TABLE cities (
 name  text,
 population float8,
 elevation int  -- (in ft)
);

CREATE TABLE capitals (
 state  char(2)
) INHERITS (cities);

-- Now, let's populate the tables.
INSERT INTO cities VALUES ('San Francisco', 7.24E+5, 63);
INSERT INTO cities VALUES ('Las Vegas', 2.583E+5, 2174);
INSERT INTO cities VALUES ('Mariposa', 1200, 1953);

INSERT INTO capitals VALUES ('Sacramento', 3.694E+5, 30, 'CA');
INSERT INTO capitals VALUES ('Madison', 1.913E+5, 845, 'WI');

SELECT * FROM cities;
SELECT * FROM capitals;

-- You can find all cities, including capitals, that
-- are located at an elevation of 500 ft or higher by:

SELECT c.name, c.elevation
FROM cities c
WHERE c.elevation > 500;

-- To scan rows of the parent table only, use ONLY:

SELECT name, elevation
FROM ONLY cities
WHERE elevation > 500;


-- clean up (you must remove the children first)
DROP TABLE capitals;
DROP TABLE cities;