Explore BrainMass
Share

How to Use SQL on Premiere Products

This content was STOLEN from BrainMass.com - View the original, and get the already-completed solution here!

Exercise 1

In the following exercises, you will use the data in the Premiere Products database (shown in the attachment). Explain how you would use SQL to obtain the desired results:

1.Craete the TopLevelCust view described in the following:
Using data from the Premiere Products database, define a view named TopLevelCust. It consists of the number, name, address, balance, and credit limit for all customers with credit limits that are greater than or equal to $100,000.
a.Using SQL, write the view definition for SmallCust.
b.Write an SQL query to retrieve the number and name of all customers in the SmallCust view with balances that exceed their credit limits.
c.Convert the query you wrote in Question 1b to the query that the DBMS will actually execute.
Display the data in the view.

2.Create thePartOrder view described in the following:
Define a view named PartOrder. It consists of the part number, description, price, order number, order date, number ordered, and quoted price for all order lines currently on file.
a.Using SQL, write the view definition for PartOrder.
b.Write an SQL query to retrieve the part number, description, order number, and quoted price for all orders in the PartOrder view for parts with quoted prices that exceed $100.
c.Convert the query you wrote in Question 2b to the query that the DBMS will actually execute.
Display the data in the view.

3.Create a view named OrdTot. It consists of the order number and order total for each order currently on file. (The order total is the sum of the number ordered multiplied by the quoted price on each order line for each order.) Display the data in the view.

4.Create the following indexes. If it necessary to name the index in your DBMS, use the indicated name.

a.Create an index named PartIndex1 on the PartNum field in the OrderLine table.
b.Create an index named PartIndex2 on the Warehouse field in the Part table.
c.Create an index named PartIndex3 on the Warehouse and Class fields in the Part table.
d.Create an index named PartIndex4 on the Warehouse and OnHand fields in the Part table and list units on hand in descending order.

5.Drop the PartIndex3 index.

Note: You will not be able to execute the code for view on the MySQL database, but you can test the SELECT statement that creates it. Questions 1, 2, and 3 require that you show the CREATE VIEW SQL as well as the SQL to select from the view itself.

---------------- End Exercise 1-----------------

Exercise 2

The following exercises are based on the Premiere Products database:

1.Using your knowledge of Premiere Products, determine the functional dependencies that exist in the following table. After determining the functional dependencies, convert this table to an equivalent collection of tables that are in third normal form.

Part (Part Num, Description, OnHand, Class, Warehouse, Price, (OrderNum, OrderDate, CustomerNum, CustomerName, RepNum, LastName, FirstName, NumOrdered, QuotedPrice) )

2.List the functional dependencies in the following table that concerns invoicing (an application Premiere Products in considering adding to its database), subject to the specified conditions. For a given invoice (identified by the InvoiceNum), there will be a single customer. The customer's number, name, and complete address appear on the invoice, as does the data. Also, there may be several different parts appearing on the invoice. For each part that appears, display the part number, description, price, and number shipped. Each customer that orders a particular part pays the same price. Convert this table to an equivalent collection of tables that are in third normal form.

Invoice (InvoiceNum, CustomerNum, LastName, FirstName, Street, City, State, Zip, Date, (PartNum, Description, Price, NumShipped) )

---------------- End Exercise 2-----------------

© BrainMass Inc. brainmass.com October 24, 2018, 6:36 pm ad1c9bdddf
https://brainmass.com/computer-science/databases/sql-premiere-products-44355

Attachments

Solution Preview

Dear Student,
I have attached a solution in a word document and corresponding Database in Access. With ...

$2.19
See Also This Related BrainMass Solution

SQL Commands

See attach file for data and details.

Use SQL to make the following changes to the Premiere Products database. After each change, execute an appropriate query to show that the change was made correctly.

1. Use the following information to create a new table named NON_SP_GOOD.*see attachment*

2. Insert into the NON_SP_GOOD table the part number, part description, number of units on hand, item class, and unit price from the PART table for each part that is not in item class SG.

3. In the NON_SP_GOOD table, change the description of part number AT94 to "Deluxe Iron."

4. In the NON_SP_GOOD table, increase the price of each item in item class HW by 2%. (Hint: Multiply each price by 1.02.)

5. Add the following part to the NON_SP_GOOD table: part number: LJ28; description: Electric Razor; number of units on hand: 21; class: AP; and price: 39.95.

6. Delete every part in the NON_SP_GOOD table for which the class is HW.

7. In the NON_SP_GOOD table, change the class for part KL62 to null.

8. Add a column named ON_HAND_VALUE to the NON_SP_GOOD table. The allocation is a seven-digit number with two decimal places, representing the product of the number of units on hand and the price. Then set all values of ON_HAND to ON_HAND * PRICE.

9. In the NON_SP_GOOD table, increase the length of the PART_DESCRIPTION column to 30 characters.

10. Delete the NON_SP_GOOD table from the Premiere Products database.

View Full Posting Details