Creating a view with a natural join statement : Create View « View « PostgreSQL






Creating a view with a natural join statement


postgres=#
postgres=# CREATE TABLE "editions" (
postgres(#      "isbn" text NOT NULL,
postgres(#      "book_id" integer,
postgres(#      "edition" integer,
postgres(#      "publisher_id" integer,
postgres(#      "publication" date,
postgres(#      "type" character(1),
postgres(#      CONSTRAINT "integrity" CHECK (((book_id NOTNULL) AND (edition NOTNULL))),
postgres(#      Constraint "pkey" Primary Key ("isbn")
postgres(# );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "pkey" for table "editions"
CREATE TABLE
postgres=#
postgres=# insert into editions values('039480001X', 1608,  1,  59,  '1957-03-01', 'h');
INSERT 0 1
postgres=# insert into editions values('0451160916', 7808,  1,  75,  '1981-08-01', 'p');
INSERT 0 1
postgres=# insert into editions values('0394800753', 1590,  1,  59,  '1949-03-01', 'p');
INSERT 0 1
postgres=# insert into editions values('0590445065', 25908, 1,  150, '1987-03-01', 'p');
INSERT 0 1
postgres=# insert into editions values('0694003611', 1501,  1,  65,  '1947-03-04', 'p');
INSERT 0 1
postgres=# insert into editions values('0679803335', 1234,  1,  102, '1922-01-01', 'p');
INSERT 0 1
postgres=# insert into editions values('0760720002', 190,   1,  91,  '1868-01-01', 'p');
INSERT 0 1
postgres=# insert into editions values('0394900014', 1608,  1,  59,  '1957-01-01', 'p');
INSERT 0 1
postgres=# insert into editions values('0385121679', 7808,  2,  75,  '1993-10-01', 'h');
INSERT 0 1
postgres=# insert into editions values('1885418035', 156,   1,  163, '1995-03-28', 'p');
INSERT 0 1
postgres=# insert into editions values('0929605942', 156,   2,  171, '1998-12-01', 'p');
INSERT 0 1
postgres=# insert into editions values('0441172717', 4513,  2,  99,  '1998-09-01', 'p');
INSERT 0 1
postgres=# insert into editions values('044100590X', 4513,  3,  99,  '1999-10-01', 'h');
INSERT 0 1
postgres=# insert into editions values('0451457994', 4267,  3,  101, '2000-09-12', 'p');
INSERT 0 1
postgres=# insert into editions values('0451198492', 4267,  3,  101, '1999-10-01', 'h');
INSERT 0 1
postgres=# insert into editions values('0823015505', 2038,  1,  62,  '1958-01-01', 'p');
INSERT 0 1
postgres=# insert into editions values('0596000855', 41473, 2,  113, '2001-03-01', 'p');
INSERT 0 1
postgres=#
postgres=# select * from editions;
    isbn    | book_id | edition | publisher_id | publication | type
------------+---------+---------+--------------+-------------+------
 039480001X |    1608 |       1 |           59 | 1957-03-01  | h
 0451160916 |    7808 |       1 |           75 | 1981-08-01  | p
 0394800753 |    1590 |       1 |           59 | 1949-03-01  | p
 0590445065 |   25908 |       1 |          150 | 1987-03-01  | p
 0694003611 |    1501 |       1 |           65 | 1947-03-04  | p
 0679803335 |    1234 |       1 |          102 | 1922-01-01  | p
 0760720002 |     190 |       1 |           91 | 1868-01-01  | p
 0394900014 |    1608 |       1 |           59 | 1957-01-01  | p
 0385121679 |    7808 |       2 |           75 | 1993-10-01  | h
 1885418035 |     156 |       1 |          163 | 1995-03-28  | p
 0929605942 |     156 |       2 |          171 | 1998-12-01  | p
 0441172717 |    4513 |       2 |           99 | 1998-09-01  | p
 044100590X |    4513 |       3 |           99 | 1999-10-01  | h
 0451457994 |    4267 |       3 |          101 | 2000-09-12  | p
 0451198492 |    4267 |       3 |          101 | 1999-10-01  | h
 0823015505 |    2038 |       1 |           62 | 1958-01-01  | p
 0596000855 |   41473 |       2 |          113 | 2001-03-01  | p
(17 rows)

postgres=#
postgres=# CREATE TABLE "shipments" (
postgres(#      "id" integer DEFAULT nextval('"shipments_ship_id_seq"'::text) NOT NULL,
postgres(#      "customer_id" integer,
postgres(#      "isbn" text,
postgres(#      "ship_date" timestamp with time zone
postgres(# );
CREATE TABLE
postgres=#
postgres=# insert into shipments values (375,  142,  '039480001X','2004-01-06 09:19:21-01');
INSERT 0 1
postgres=# insert into shipments values (323,  671,  '0451160916','2004-02-14 10:26:41-02');
INSERT 0 1
postgres=# insert into shipments values (998,  1045, '0590445065','2004-03-12 12:39:47-03');
INSERT 0 1
postgres=# insert into shipments values (749,  172,  '0694003611','2003-04-11 10:42:34-04');
INSERT 0 1
postgres=# insert into shipments values (662,  655,  '0679803335','2003-05-09 07:50:07-05');
INSERT 0 1
postgres=# insert into shipments values (806,  1125, '0760720002','2003-06-05 09:14:04-06');
INSERT 0 1
postgres=# insert into shipments values (102,  146,  '0394900014','2002-07-11 13:24:08-07');
INSERT 0 1
postgres=# insert into shipments values (813,  112,  '0385121679','2002-08-08 09:33:46-08');
INSERT 0 1
postgres=# insert into shipments values (652,  724,  '1885418035','2002-09-14 13:41:39-09');
INSERT 0 1
postgres=# insert into shipments values (599,  430,  '0929605942','2001-01-10 08:59:42-01');
INSERT 0 1
postgres=# insert into shipments values (969,  488,  '0441172717','2006-02-14 08:12:58-02');
INSERT 0 1
postgres=# insert into shipments values (433,  898,  '044100590X','2006-03-12 08:26:35-03');
INSERT 0 1
postgres=# insert into shipments values (660,  409,  '0451457994','2006-04-07 11:36:42-04');
INSERT 0 1
postgres=# insert into shipments values (310,  738,  '0451198492','2005-04-15 14:42:01-05');
INSERT 0 1
postgres=# insert into shipments values (510,  860,  '0823015505','2005-05-14 07:53:47-06');
INSERT 0 1
postgres=# insert into shipments values (997,  185,  '039480001X','2005-05-10 13:47:52-07');
INSERT 0 1
postgres=# insert into shipments values (999,  221,  '0451160916','2005-06-14 13:45:51-08');
INSERT 0 1
postgres=# insert into shipments values (56,   880,  '0590445065','2005-05-14 13:39:00-09');
INSERT 0 1
postgres=# insert into shipments values (72,   574,  '0694003611','2004-04-06 07:29:44-00');
INSERT 0 1
postgres=# insert into shipments values (146,  270,  '039480001X','2004-03-13 09:12:10-01');
INSERT 0 1
postgres=# insert into shipments values (981,  652,  '0451160916','2003-02-08 08:46:44-02');
INSERT 0 1
postgres=# insert into shipments values (95,   480,  '0590445065','2003-01-10 07:29:52-03');
INSERT 0 1
postgres=# insert into shipments values (593,  476,  '0694003611','2002-03-15 11:17:40-04');
INSERT 0 1
postgres=# insert into shipments values (977,  853,  '0679803335','2002-05-09 09:20:46-05');
INSERT 0 1
postgres=# insert into shipments values (117,  185,  '0760720002','2005-05-07 13:30:48-06');
INSERT 0 1
postgres=# insert into shipments values (406,  1123, '0394900014','2004-04-13 09:41:04-07');
INSERT 0 1
postgres=# insert into shipments values (340,  1149, '0385121679','2003-02-12 13:21:22-08');
INSERT 0 1
postgres=# insert into shipments values (871,  388,  '1885418035','2003-03-07 11:41:57-09');
INSERT 0 1
postgres=# insert into shipments values (1000, 221,  '039480001X','2002-05-14 16:46:32-01');
INSERT 0 1
postgres=# insert into shipments values (1001, 107,  '039480001X','2001-02-14 17:42:22-02');
INSERT 0 1
postgres=# insert into shipments values (754,  107,  '0394800753','2006-05-11 09:55:05-03');
INSERT 0 1
postgres=# insert into shipments values (458,  107,  '0394800753','2005-06-07 10:58:36-04');
INSERT 0 1
postgres=# insert into shipments values (189,  107,  '0394800753','2004-03-06 11:46:36-04');
INSERT 0 1
postgres=# insert into shipments values (720,  107,  '0394800753','2003-02-08 10:36:13-05');
INSERT 0 1
postgres=# insert into shipments values (1002, 107,  '0394800753','2002-09-22 11:23:28-06');
INSERT 0 1
postgres=# insert into shipments values (2,    107,  '0394800753','2001-09-22 20:18:56-07');
INSERT 0 1
postgres=#
postgres=# select * from shipments;
  id  | customer_id |    isbn    |       ship_date
------+-------------+------------+------------------------
  375 |         142 | 039480001X | 2004-01-06 02:19:21-08
  323 |         671 | 0451160916 | 2004-02-14 04:26:41-08
  998 |        1045 | 0590445065 | 2004-03-12 07:39:47-08
  749 |         172 | 0694003611 | 2003-04-11 07:42:34-07
  662 |         655 | 0679803335 | 2003-05-09 05:50:07-07
  806 |        1125 | 0760720002 | 2003-06-05 08:14:04-07
  102 |         146 | 0394900014 | 2002-07-11 13:24:08-07
  813 |         112 | 0385121679 | 2002-08-08 10:33:46-07
  652 |         724 | 1885418035 | 2002-09-14 15:41:39-07
  599 |         430 | 0929605942 | 2001-01-10 01:59:42-08
  969 |         488 | 0441172717 | 2006-02-14 02:12:58-08
  433 |         898 | 044100590X | 2006-03-12 03:26:35-08
  660 |         409 | 0451457994 | 2006-04-07 08:36:42-07
  310 |         738 | 0451198492 | 2005-04-15 12:42:01-07
  510 |         860 | 0823015505 | 2005-05-14 06:53:47-07
  997 |         185 | 039480001X | 2005-05-10 13:47:52-07
  999 |         221 | 0451160916 | 2005-06-14 14:45:51-07
   56 |         880 | 0590445065 | 2005-05-14 15:39:00-07
   72 |         574 | 0694003611 | 2004-04-06 00:29:44-07
  146 |         270 | 039480001X | 2004-03-13 02:12:10-08
  981 |         652 | 0451160916 | 2003-02-08 02:46:44-08
   95 |         480 | 0590445065 | 2003-01-10 02:29:52-08
  593 |         476 | 0694003611 | 2002-03-15 07:17:40-08
  977 |         853 | 0679803335 | 2002-05-09 07:20:46-07
  117 |         185 | 0760720002 | 2005-05-07 12:30:48-07
  406 |        1123 | 0394900014 | 2004-04-13 09:41:04-07
  340 |        1149 | 0385121679 | 2003-02-12 13:21:22-08
  871 |         388 | 1885418035 | 2003-03-07 12:41:57-08
 1000 |         221 | 039480001X | 2002-05-14 10:46:32-07
 1001 |         107 | 039480001X | 2001-02-14 11:42:22-08
  754 |         107 | 0394800753 | 2006-05-11 05:55:05-07
  458 |         107 | 0394800753 | 2005-06-07 07:58:36-07
  189 |         107 | 0394800753 | 2004-03-06 07:46:36-08
  720 |         107 | 0394800753 | 2003-02-08 07:36:13-08
 1002 |         107 | 0394800753 | 2002-09-22 10:23:28-07
    2 |         107 | 0394800753 | 2001-09-22 20:18:56-07
(36 rows)

postgres=#
postgres=# CREATE TABLE "books" (
postgres(#      "id"           integer NOT NULL,
postgres(#      "title"        text NOT NULL,
postgres(#      "author_id"    integer,
postgres(#      "subject_id"   integer,
postgres(#      Constraint "books_id_pkey" Primary Key ("id")
postgres(# );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "books_id_pkey" for table "books"
CREATE TABLE
postgres=#
postgres=#
postgres=# insert into books values(7808,  'Java',                  4156, 9);
INSERT 0 1
postgres=# insert into books values(4513,  'Javascript',            1866, 15);
INSERT 0 1
postgres=# insert into books values(4267,  'C#',                    2001, 15);
INSERT 0 1
postgres=# insert into books values(1608,  'Oracle',                1809, 2);
INSERT 0 1
postgres=# insert into books values(1590,  'Sql Server',            1809, 2);
INSERT 0 1
postgres=# insert into books values(25908, 'Postgre SQL',          15990, 2);
INSERT 0 1
postgres=# insert into books values(1501,  'Python',                2031, 2);
INSERT 0 1
postgres=# insert into books values(190,   'Java by API',             16, 6);
INSERT 0 1
postgres=# insert into books values(1234,  '2D',                   25041, 3);
INSERT 0 1
postgres=# insert into books values(2038,  'C',                     1644, 0);
INSERT 0 1
postgres=# insert into books values(156,   'C++',                    115, 9);
INSERT 0 1
postgres=# insert into books values(41473, 'Programming Python',    7805, 4);
INSERT 0 1
postgres=# insert into books values(41477, 'Learning Python',       7805, 4);
INSERT 0 1
postgres=# insert into books values(41478, 'Perl Cookbook',         7806, 4);
INSERT 0 1
postgres=# insert into books values(41472, 'Practical PostgreSQL',  1212, 4);
INSERT 0 1
postgres=#
postgres=# select * from books;
  id   |        title         | author_id | subject_id
-------+----------------------+-----------+------------
  7808 | Java                 |      4156 |          9
  4513 | Javascript           |      1866 |         15
  4267 | C#                   |      2001 |         15
  1608 | Oracle               |      1809 |          2
  1590 | Sql Server           |      1809 |          2
 25908 | Postgre SQL          |     15990 |          2
  1501 | Python               |      2031 |          2
   190 | Java by API          |        16 |          6
  1234 | 2D                   |     25041 |          3
  2038 | C                    |      1644 |          0
   156 | C++                  |       115 |          9
 41473 | Programming Python   |      7805 |          4
 41477 | Learning Python      |      7805 |          4
 41478 | Perl Cookbook        |      7806 |          4
 41472 | Practical PostgreSQL |      1212 |          4
(15 rows)

postgres=#
postgres=#
postgres=#
postgres=#
postgres=# -- Creating a view
postgres=#
postgres=# CREATE VIEW recent_shipments
postgres-#         AS SELECT count(*) AS num_shipped, max(ship_date), title
postgres-#            FROM shipments
postgres-#            JOIN editions USING (isbn)
postgres-#            NATURAL JOIN books AS b (book_id)
postgres-#            GROUP BY b.title
postgres-#            ORDER BY num_shipped DESC;
CREATE VIEW
postgres=#
postgres=# select * from recent_shipments;
 num_shipped |          max           |    title
-------------+------------------------+-------------
           7 | 2005-05-10 13:47:52-07 | Oracle
           6 | 2006-05-11 05:55:05-07 | Sql Server
           5 | 2005-06-14 14:45:51-07 | Java
           3 | 2005-05-14 15:39:00-07 | Postgre SQL
           3 | 2004-04-06 00:29:44-07 | Python
           3 | 2003-03-07 12:41:57-08 | C++
           2 | 2003-05-09 05:50:07-07 | 2D
           2 | 2005-05-07 12:30:48-07 | Java by API
           2 | 2006-03-12 03:26:35-08 | Javascript
           2 | 2006-04-07 08:36:42-07 | C#
           1 | 2005-05-14 06:53:47-07 | C
(11 rows)

postgres=#
postgres=# SELECT * FROM recent_shipments
postgres-#         ORDER BY max DESC
postgres-#         LIMIT 3;
 num_shipped |          max           |   title
-------------+------------------------+------------
           6 | 2006-05-11 05:55:05-07 | Sql Server
           2 | 2006-04-07 08:36:42-07 | C#
           2 | 2006-03-12 03:26:35-08 | Javascript
(3 rows)

postgres=#
postgres=#
postgres=#
postgres=#
postgres=# drop view recent_shipments;
DROP VIEW
postgres=#
postgres=# drop table books;
DROP TABLE
postgres=# drop table editions;
DROP TABLE
postgres=# drop table shipments;
DROP TABLE
postgres=#
postgres=#
           
       








Related examples in the same category

1.Create view from the user defined function
2.Creates a view of all publishers whose names begin with H