CIS22751 Lab Week 6


Show a unique list of supplier states (i.e. no duplication!).’ + CHAR(10)

Write the query to project from invoices the average total price, the minimum total price, and the maximum total price.  Money should be formatted to two decimal places (use

Write a query to list each Type in the STOVE table along with the total number of stoves for that value (use GROUP BY with the aggregate function COUNT).  Now write another query to list each unique combination of Type and Version, along with the total number of stoves for that combination.’ + CHAR(10)

Display the total Extended Price values from the INV_LINE_ITEM table broken doen by invoice number. Sort the results in descending order by the extended price total.’ + CHAR(10)

Display the invoice number and the total number of parts for every invoice (hint: add up the Quantity value). Do NOT include stoves in your part totals.  Order the list by invoice number, and format output using CAST, Show the invoice number and total extended price (use SUM) for every invoice in the INV_INE_ITEM table; but omit invoices whose total extended price is less than $100 (use the HAVING clause).’ + CHAR(10)

Show the part number and total quantity for all parts in the PO_LINE_ITEM table whose total quantity is one gross (144) or less.  Display results in ascending order by total quantity.’ + CHAR(10)

For each repair, show the repair number and total extended price (from the REPAIR_LINE_ITEM table). i.e. show one line per repair number, with the SUM of Extended Price values for that repair. Display query results as [T]ext, and avoid this warning message:

Warning: Null value is eliminated by an aggregate or other SET operation.’ + CHAR(10)

Show the name, address, and city/state/ZIP code (these last three concatenated into a single line) for all customers who do not live in an apartment.  Sort by customer number.’ + CHAR(10)

Use the CREATE TABLE statement to make a table in the database; include at least three columns of different data types.
You have access to 275Sandbox where you can actually run your SQL Statement to see if it works; change from the default FiredUp database by using the pull-down database list on the taskbar, or by selecting Query -> Connection -> Change Connection.’ + CHAR(10)

Based upon your work in last week’’s lab, identify two entities which are involved in a one-to-many relationship.
Write the SQL statement to create the table on the “one” side of the relationship.
Make sure that you define a primary key for your table.’ + CHAR(10)

Write two separate SQL statements to insert data into the table that you just created.  Explicitly specify the columns, into which the data should be inserted in corresponding order.’ + CHAR(10)

Select all rows from the new table you’ve just created (there should only be the two rows you just inserted!). Order the output by your table”s primary key; format all columns using CAST, CONVERT and/or STR.’ + CHAR(10)

Write the SQL statement to add a column called “LastModified” to your table; this should be of the data type DATETIME.
Ensure that the column may not contain NULLs, and provide a DEFAULT value for rows that may have this value missing.’ + CHAR(10)

Write the SQL statement to create the other table from the 1:N relationship above; link the two tables using the appropriate database CONSTRAINT.  What effect will this have on data inserted into the new table?  (answer with a PRINT statement or in comments)’ + CHAR(10)