Use postgresql to complete this

Assignment steps

1. Run the script from Lab 3. to create/reset all tables, constraints and data.
2. Write one or more commands for the given requirements listed below.

Requirements

1. Create a query that uses a CASE statement and gets the following columns:

1. • order_id
   • num_items (number of items in the order)
   • num_desks (number of desks in the order)

2. Create a query that uses a Common Table Expression (CTE) and a Window Function to get the following columns:

• state_province
• last_customer_name (name of customer that placed an order most recently)

3. Create the query from question #2 using a Temporary Table instead of a CTE. Please include a DROP IF EXISTS statement prior to your statement that creates the Temporary Table.

4. Create the query from question #2 using a View instead of a CTE. Please include a DROP IF EXISTS statement prior to your statement that creates the View.

5. Create a role named product_administrator with permissions to SELECT and INSERT records into the product table. Create a user named bob finance who is a member of that role.

Data

DROP TABLE IF EXISTS order_line ;

DROP TABLE IF EXISTS product ;

DROP TABLE IF EXISTS order_header ;

DROP TABLE IF EXISTS customer ;

CREATE TABLE customer ( customer_id INTEGER NOT NULL, customer_name VARCHAR (25) NOT NULL, address_line_1 VARCHAR (200), city VARCHAR (100), state_province CHAR (25), postal_code VARCHAR (50), PRIMARY KEY (customer_id) ) ;

CREATE TABLE order_header ( order_id INTEGER NOT NULL, order_date DATE, customer_id INTEGER, PRIMARY KEY (order_id), CONSTRAINT order_fkey_customer_id FOREIGN KEY (customer_id) REFERENCES customer (customer_id)) ;

CREATE TABLE product ( product_id INTEGER NOT NULL, product_name VARCHAR (200), product_price MONEY, PRIMARY KEY (product_id) ) ;

CREATE TABLE order_line ( order_id INTEGER NOT NULL, product_id INTEGER NOT NULL, quantity INTEGER, PRIMARY KEY (order_id, product_id), CONSTRAINT order_line_fkey_order_id FOREIGN KEY (order_id) REFERENCES order_header (order_id), CONSTRAINT order_line_fkey_product_id FOREIGN KEY (product_id) REFERENCES product (product_id)) ;