The assignment:

1. Download and install Postgres.

2. 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

3. 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.

1. 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.

a. Execute your scripts to test them

b. 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.

3. 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 prodf

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

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

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

5. The fields are:

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

b. cust_id : a string that uniquely identifies the customer

c. 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)

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

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

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

g. unit_price :the price for one unit of the product

h. trans_date: date on which the transaction was completed

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

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

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

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

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

b. 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

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

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

7. 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) :

a. List distinct suppliers used by company

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

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

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

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

c.1 Orders should take place on more than one date

c.2 The cost is just the product of quantity and price

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

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

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

d.1 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

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