Purchase Solution

How to Use SQL on Premiere Products

Not what you're looking for?

Ask Custom Question

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

Purchase this Solution

Solution Preview

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

Solution provided by:
  • BSc (Hons), University of Colombo - Sri Lanka
  • MEngSc, University of Melbourne
  • Certificate IV in Training & Assessment , Australian Business Council
Recent Feedback
  • "Thank you!"
  • "Thank you."
  • "Thanks a lot for you help and support"
  • "Sorry about the late payment but the assignment was sufficient and appreciated. Good work"
  • "Great description! Thank you for your quick response."
Purchase this Solution

Free BrainMass Quizzes
Excel Introductory Quiz

This quiz tests your knowledge of basics of MS-Excel.

Inserting and deleting in a linked list

This quiz tests your understanding of how to insert and delete elements in a linked list. Understanding of the use of linked lists, and the related performance aspects, is an important fundamental skill of computer science data structures.

C# variables and classes

This quiz contains questions about C# classes and variables.

Basic Computer Terms

We use many basic terms like bit, pixel in our usual conversations about computers. Are we aware of what these mean? This little quiz is an attempt towards discovering that.

Basic UNIX commands

Use this quiz to check your knowledge of a few common UNIX commands. The quiz covers some of the most essential UNIX commands and their basic usage. If you can pass this quiz then you are clearly on your way to becoming an effective UNIX command line user.