{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "## Overview of Views\n", "Here are the details related to views." ] }, { "cell_type": "code", "execution_count": 1, "metadata": { "tags": [ "remove-cell" ] }, "outputs": [ { "data": { "text/html": [ "\n" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "%%HTML\n", "" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "* View is nothing but a named query. We typically create views for most commonly used queries.\n", "* Unlike tables, views does not physically store the data and when ever we write a query against view it will fetch the data from underlying tables defined as part of the views.\n", "* We can perform DML operations over the tables via views with restrictions (for example, we cannot perform DML operations on views with joins, group by etc).\n", "* Views that can be used to perform DML operations on underlying tables are called as **updatable views**\n", "* Views can be used to provide restricted permissions on tables for DML Operations. However, it is not used these days." ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [], "source": [ "%load_ext sql" ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db\n" ] } ], "source": [ "%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db" ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Done.\n" ] }, { "data": { "text/plain": [ "[]" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "CREATE OR REPLACE VIEW orders_v\n", "AS\n", "SELECT * FROM orders" ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "(psycopg2.errors.DuplicateTable) relation \"orders_v\" already exists\n", "\n", "[SQL: CREATE VIEW orders_v AS\n", "SELECT * FROM orders]\n", "(Background on this error at: http://sqlalche.me/e/13/f405)\n" ] } ], "source": [ "%%sql\n", "\n", "CREATE VIEW orders_v\n", "AS\n", "SELECT * FROM orders" ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "2 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
table_catalogtable_schematable_nametable_typeself_referencing_column_namereference_generationuser_defined_type_cataloguser_defined_type_schemauser_defined_type_nameis_insertable_intois_typedcommit_action
itversity_retail_dbpublicordersBASE TABLENoneNoneNoneNoneNoneYESNONone
itversity_retail_dbpublicorders_vVIEWNoneNoneNoneNoneNoneYESNONone
" ], "text/plain": [ "[('itversity_retail_db', 'public', 'orders', 'BASE TABLE', None, None, None, None, None, 'YES', 'NO', None),\n", " ('itversity_retail_db', 'public', 'orders_v', 'VIEW', None, None, None, None, None, 'YES', 'NO', None)]" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT * FROM information_schema.tables\n", "WHERE table_name ~ 'orders'" ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "68883 rows affected.\n" ] }, { "data": { "text/plain": [ "[]" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "UPDATE orders_v\n", "SET order_status = lower(order_status)" ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "10 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
order_idorder_dateorder_customer_idorder_status
1222013-07-26 00:00:002071processing
1232013-07-26 00:00:003695pending_payment
1242013-07-26 00:00:002374complete
1252013-07-26 00:00:004611pending_payment
1262013-07-26 00:00:00610complete
1272013-07-26 00:00:005261pending_payment
1282013-07-26 00:00:002772pending_payment
1292013-07-26 00:00:009937closed
1302013-07-26 00:00:007509pending_payment
1312013-07-26 00:00:0010072processing
" ], "text/plain": [ "[(122, datetime.datetime(2013, 7, 26, 0, 0), 2071, 'processing'),\n", " (123, datetime.datetime(2013, 7, 26, 0, 0), 3695, 'pending_payment'),\n", " (124, datetime.datetime(2013, 7, 26, 0, 0), 2374, 'complete'),\n", " (125, datetime.datetime(2013, 7, 26, 0, 0), 4611, 'pending_payment'),\n", " (126, datetime.datetime(2013, 7, 26, 0, 0), 610, 'complete'),\n", " (127, datetime.datetime(2013, 7, 26, 0, 0), 5261, 'pending_payment'),\n", " (128, datetime.datetime(2013, 7, 26, 0, 0), 2772, 'pending_payment'),\n", " (129, datetime.datetime(2013, 7, 26, 0, 0), 9937, 'closed'),\n", " (130, datetime.datetime(2013, 7, 26, 0, 0), 7509, 'pending_payment'),\n", " (131, datetime.datetime(2013, 7, 26, 0, 0), 10072, 'processing')]" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT * FROM orders LIMIT 10" ] }, { "cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "68883 rows affected.\n" ] }, { "data": { "text/plain": [ "[]" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "UPDATE orders_v\n", "SET order_status = upper(order_status)" ] }, { "cell_type": "code", "execution_count": 10, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "Done.\n" ] }, { "data": { "text/plain": [ "[]" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "CREATE OR REPLACE VIEW order_details_v\n", "AS\n", "SELECT * FROM orders o\n", " JOIN order_items oi\n", " ON o.order_id = oi.order_item_order_id" ] }, { "cell_type": "code", "execution_count": 11, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "10 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
order_idorder_dateorder_customer_idorder_statusorder_item_idorder_item_order_idorder_item_product_idorder_item_quantityorder_item_subtotalorder_item_product_price
12013-07-25 00:00:0011599CLOSED119571299.98299.98
22013-07-25 00:00:00256PENDING_PAYMENT2210731199.99199.99
22013-07-25 00:00:00256PENDING_PAYMENT325025250.050.0
22013-07-25 00:00:00256PENDING_PAYMENT424031129.99129.99
42013-07-25 00:00:008827CLOSED54897249.9824.99
42013-07-25 00:00:008827CLOSED643655299.9559.99
42013-07-25 00:00:008827CLOSED745023150.050.0
42013-07-25 00:00:008827CLOSED8410144199.9249.98
52013-07-25 00:00:0011318COMPLETE959571299.98299.98
52013-07-25 00:00:0011318COMPLETE1053655299.9559.99
" ], "text/plain": [ "[(1, datetime.datetime(2013, 7, 25, 0, 0), 11599, 'CLOSED', 1, 1, 957, 1, 299.98, 299.98),\n", " (2, datetime.datetime(2013, 7, 25, 0, 0), 256, 'PENDING_PAYMENT', 2, 2, 1073, 1, 199.99, 199.99),\n", " (2, datetime.datetime(2013, 7, 25, 0, 0), 256, 'PENDING_PAYMENT', 3, 2, 502, 5, 250.0, 50.0),\n", " (2, datetime.datetime(2013, 7, 25, 0, 0), 256, 'PENDING_PAYMENT', 4, 2, 403, 1, 129.99, 129.99),\n", " (4, datetime.datetime(2013, 7, 25, 0, 0), 8827, 'CLOSED', 5, 4, 897, 2, 49.98, 24.99),\n", " (4, datetime.datetime(2013, 7, 25, 0, 0), 8827, 'CLOSED', 6, 4, 365, 5, 299.95, 59.99),\n", " (4, datetime.datetime(2013, 7, 25, 0, 0), 8827, 'CLOSED', 7, 4, 502, 3, 150.0, 50.0),\n", " (4, datetime.datetime(2013, 7, 25, 0, 0), 8827, 'CLOSED', 8, 4, 1014, 4, 199.92, 49.98),\n", " (5, datetime.datetime(2013, 7, 25, 0, 0), 11318, 'COMPLETE', 9, 5, 957, 1, 299.98, 299.98),\n", " (5, datetime.datetime(2013, 7, 25, 0, 0), 11318, 'COMPLETE', 10, 5, 365, 5, 299.95, 59.99)]" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT * FROM order_details_v LIMIT 10" ] }, { "cell_type": "code", "execution_count": 12, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "1 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
count
172198
" ], "text/plain": [ "[(172198,)]" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT count(1) FROM order_details_v" ] }, { "cell_type": "code", "execution_count": 13, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "10 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
order_dateorder_item_product_idrevenue
2013-07-25 00:00:00100410799.46
2013-07-25 00:00:009579599.36
2013-07-25 00:00:001918499.15
2013-07-25 00:00:003657558.74
2013-07-25 00:00:0010736999.65
2013-07-25 00:00:0010146397.44
2013-07-25 00:00:004035589.57
2013-07-25 00:00:005025100.00
2013-07-25 00:00:006272879.28
2013-07-25 00:00:00226599.99
" ], "text/plain": [ "[(datetime.datetime(2013, 7, 25, 0, 0), 1004, Decimal('10799.46')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 957, Decimal('9599.36')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 191, Decimal('8499.15')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 365, Decimal('7558.74')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 1073, Decimal('6999.65')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 1014, Decimal('6397.44')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 403, Decimal('5589.57')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 502, Decimal('5100.00')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 627, Decimal('2879.28')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 226, Decimal('599.99'))]" ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT order_date,\n", " order_item_product_id,\n", " round(sum(order_item_subtotal)::numeric, 2) AS revenue\n", "FROM order_details_v \n", "GROUP BY order_date,\n", " order_item_product_id\n", "ORDER BY order_date,\n", " revenue DESC\n", "LIMIT 10" ] }, { "cell_type": "code", "execution_count": 14, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "3 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
order_idorder_dateorder_customer_idorder_statusorder_item_idorder_item_order_idorder_item_product_idorder_item_quantityorder_item_subtotalorder_item_product_price
22013-07-25 00:00:00256PENDING_PAYMENT2210731199.99199.99
22013-07-25 00:00:00256PENDING_PAYMENT325025250.050.0
22013-07-25 00:00:00256PENDING_PAYMENT424031129.99129.99
" ], "text/plain": [ "[(2, datetime.datetime(2013, 7, 25, 0, 0), 256, 'PENDING_PAYMENT', 2, 2, 1073, 1, 199.99, 199.99),\n", " (2, datetime.datetime(2013, 7, 25, 0, 0), 256, 'PENDING_PAYMENT', 3, 2, 502, 5, 250.0, 50.0),\n", " (2, datetime.datetime(2013, 7, 25, 0, 0), 256, 'PENDING_PAYMENT', 4, 2, 403, 1, 129.99, 129.99)]" ] }, "execution_count": 14, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT * FROM order_details_v\n", "WHERE order_id = 2" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "```{note}\n", "We cannot directly update data in tables via views when the view is defined with joins. Even operations such as `GROUP BY` or `ORDER BY` etc will make views not updatable by default.\n", "```" ] }, { "cell_type": "code", "execution_count": 15, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "(psycopg2.errors.ObjectNotInPrerequisiteState) cannot update view \"order_details_v\"\n", "DETAIL: Views that do not select from a single table or view are not automatically updatable.\n", "HINT: To enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON UPDATE DO INSTEAD rule.\n", "\n", "[SQL: UPDATE order_details_v SET order_status = 'pending_payment'\n", "WHERE order_id = 2]\n", "(Background on this error at: http://sqlalche.me/e/13/e3q8)\n" ] } ], "source": [ "%%sql\n", "\n", "UPDATE order_details_v\n", "SET\n", " order_status = 'pending_payment'\n", "WHERE order_id = 2" ] } ], "metadata": { "kernelspec": { "display_name": "", "name": "" }, "language_info": { "name": "" } }, "nbformat": 4, "nbformat_minor": 4 }