# SQL

Thehonest
(Not rated)
(Not rated)
Chat

PRINT 'CIS2275, Lab Week 6, Question 1

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

PRINT CHAR(10) + 'CIS2275, Lab Week 6, Question 2

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 CAST or CONVERT).' + CHAR(10)

PRINT CHAR(10) + 'CIS2275, Lab Week 6, Question 3

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)

PRINT CHAR(10) + 'CIS2275, Lab Week 6, Question 4

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)

PRINT CHAR(10) + 'CIS2275, Lab Week 6, Question 5

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,

CONVERT, and/or STR.' + CHAR(10)

PRINT CHAR(10) + 'CIS2275, Lab Week 6, Question 6

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)

PRINT CHAR(10) + 'CIS2275, Lab Week 6, Question 7

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)

PRINT CHAR(10) + 'CIS2275, Lab 6, Question 8

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 ExtendedPrice 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)

PRINT CHAR(10) + 'CIS2275, Lab 6, Question 9

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)

PRINT CHAR(10) + 'CIS2275, Lab Week 6, Question 10

Use the CREATE TABLE statememnt 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)

PRINT CHAR(10) + 'CIS2275, Lab Week 6, Question 11

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)

PRINT CHAR(10) + 'CIS2275, Lab Week 6, Question 12

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)

PRINT CHAR(10) + 'CIS2275, Lab Week 6, Question 13

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)

PRINT CHAR(10) + 'CIS2275, Lab Week 6, Question 14

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)

PRINT CHAR(10) + 'CIS2275, Lab Week 6, Question 15

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)

• 6 years ago