Introduction:

An assignment to make sure you get Postgres installed and can execute basic statements. The subject of the table you will work with is supply-chain management, though we are focusing on the data being managed rather than how to solve the associated problems. We will start with a single table and see some of its limitations and then redesign things in future assignments. Our initial design is based on the Transaction entity of a diagram in Supply Management System Database Design with ERD - 2020 (itsourcecode.com) Links to an external site.. Terminology-wise, I will use "order" and "transaction" interchangeably.

The assignment:

Download and install Postgres.

In pgAdmin 4, on the left, explode the Servers to choose one, after which you should see the Databases item. Right-click on that item to create a database or use the default database called postgres

If you right-click on the database, the next-to-last menu option is Query Tool. Choose that and then you can save your work in a script called create.sql to define the table sc_order.Remember that SQL is code, even if the syntax is new for you. Do not use Word or another word processor to edit it. The Query Tool feature is not the greatest, but it has syntax highlighting.

Execute your scripts to test them.

Make sure to save your files as .sql files. For some time, pgAdmin 4 would not add a filetype (perhaps the developers run it on Linux), but the current version seems to take care of that now.

To distinguish assignments for grading purposes, create a schema named using your last name (up to 6 letters), appending your first initial, so my schema would be yangd

You can find examples at: PostgreSQL: Documentation: 14: CREATE SCHEMALinks to an external site.

This general section of the Postgres documentation has all the SQL syntax, so its a decent bookmark to have for this semester

Define the table within the schema for me, this would be sc_order

The fields are:

trans_id : a SERIAL (this is the "order id" mentioned below)

cust_id : a string that uniquely identifies the customer

employee: a string for the name of the employee who is managing this transaction (sorry I managed to miss this field in the initial writeup)

prod_id : a string that uniquely identifies the product sold to the customer

supplier_id : a string that identifies where we get the product from

quantity : an integer for how much of the product was sold

unit_price :the price for one unit of the product

trans_date: date on which the transaction was completed

Note that it is certainly possible to add fields to make it easier to write the queries described below. Do not do that because:

The point of this assignment is to give you practice using different parts of the SQL syntax

Adding fields will probably partly duplicate other fields and make the data harder to maintain.

Create a script insert_ok.sql to insert acceptable data. Use INSERT statements to insert records into the sc_order table

Insert enough records so that any queries beyond the first one will return at least one, but not all records

You may find PostgreSQL: Documentation: 15: 9.8. Data Type Formatting Functions Links to an external site.a useful resource for the trans_date attribute it lets you choose your preferred date format

SQL itself does not have the kind of variables like Python or C++ does, so do not try to separate this into multiple statements

[Databases like Postgres do support stored procedures for more complicated operations, but that is a separate language]

Create a script query.sql that includes semicolon-separated queries to do the following (terminate each query with a semicolon so the grader can run the whole script at once) :List distinct suppliers used by company

Make sure there is more than one record for at least one supplier

List order ID, customers helped by Tom and the dates on which the orders he helped with were completed.

There should be at least two orders for Tom to help with

List suppliers, product ID, quantity, price, transaction date, with a generated total cost column, ordered most recent to oldest

Orders should take place on more than one date

The cost is just the product of quantity and price

Give this column an alias (nickname) and use the alias in the ORDER BY clause

The ORDER keyword is why we cannot just call the table Order

List order id, customer, employee, supplier, trans_date that completed in December 2022

Here, the EXTRACT function described in PostgreSQL: Documentation: 15: 9.9. Date/Time Functions and Operators Links to an external site.should help you get the answer

Try EXTRACT in the SELECT clause first to see how it behaves

Submit all files on Canvas -- click on the Hw1 Spr 2023 item under Modules and you get to submit.