First Teradata Assignment

Using database db_pvfc12_big, based on the diagram in Figure 2-22, write SQL queries to answer the following questions and execute them (finding the correct answers!!) Remember, for each question you must submit the question, your SQL query and the results of executing it, together. Be sure to number your answers.

In order to write the queries, you're going to have to know the table names and the names of the attributes in the tables. See the list of tables and attributes, below. It is up to you to figure out which tables you will need for each question in this assignment but note that each query in this assignment requires only one table. (If you know something about SQL, joins will not come into play until the next assignment.) These are very small tables for learning purposes only.

Tables and Fields in Database db_pvfc12_big

(Note: The use of fields in Teradata SQL is not case sensitive.)

CUSTOMER_T

Customerid

Customername

Customeraddress

Customercity

Customerstate

Customerpostalcode

PRODUCT_T

Productid

Productdescription

Productfinish

Productstandardprice

Productonhand

Productlineid

RAWMATERIAL_T

Materialid

Materialname

Thickness

Width

Size

Material

Materialstandardprice

Unitofmeasure

Materialtype

ORDER_T

Orderid

Orderdate

Customerid

Fulfillmentdate

Salespersonid

Shipadrsid

ORDERLINE_T

Orderlineid

Orderid

Productid

Orderedquantity

USES_T

Productid

Materialid

Quantityrequired

SUPPLIES_T

Vendorid

Materialid

Supplyunitprice

7. How many customers are from each state? Only include states that have at least two such customers. List the results in alphabetic order by state.

8. How many customers with customer numbers between 3 and 12 (inclusive) are from each state? Only include states that have at least two such customers. List the results in alphabetic order by state.

9. List the states in alphabetic order in which PVFC has customers.

10. List the products from highest to lowest product id, that are finished in Birch, Pine, or Walnut, and that cost more than $300, and that are from product lines 1 or 2.