Scribd Logo
Upload

Welcome to Scribd!

  • 284 views

    Lesson4 Premiere Assignment

    Uploaded by

    Carlos Vallejos
    The document contains the answers to 16 questions about queries run on the Premiere Products database. The questions involve creating queries to retrieve data from tables and joining tables, and modifying a table by filtering records. Key details provided in the responses include: 1) Sample queries run against the Customer, Part, Rep, and Orders tables to return requested data. 2) A query showing the 3 customers with a credit limit of $10,000. 3) The total outstanding balance for all customers represented by Rep 35. 4) Modification of the Part table to create a new SportingGoods table containing only records for class SG.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PDF, TXT or read online from Scribd

    Lesson4 Premiere Assignment

    Uploaded by

    Carlos Vallejos
    284 views7 pages
    The document contains the answers to 16 questions about queries run on the Premiere Products database. The questions involve creating queries to retrieve data from tables and joining tables, and modifying a table by filtering records. Key details provided in the responses include: 1) Sample queries run against the Customer, Part, Rep, and Orders tables to return requested data. 2) A query showing the 3 customers with a credit limit of $10,000. 3) The total outstanding balance for all customers represented by Rep 35. 4) Modification of the Part table to create a new SportingGoods table containing only records for class SG.

    Copyright

    © Attribution Non-Commercial (BY-NC)

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document contains the answers to 16 questions about queries run on the Premiere Products database. The questions involve creating queries to retrieve data from tables and joining tables, and modifying a table by filtering records. Key details provided in the responses include: 1) Sample queries run against the Customer, Part, Rep, and Orders tables to return requested data. 2) A query showing the 3 customers with a credit limit of $10,000. 3) The total outstanding balance for all customers represented by Rep 35. 4) Modification of the Part table to create a new SportingGoods table containing only records for class SG.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    284 views7 pages

    Lesson4 Premiere Assignment

    Uploaded by

    Carlos Vallejos
    The document contains the answers to 16 questions about queries run on the Premiere Products database. The questions involve creating queries to retrieve data from tables and joining tables, and modifying a table by filtering records. Key details provided in the responses include: 1) Sample queries run against the Customer, Part, Rep, and Orders tables to return requested data. 2) A query showing the 3 customers with a credit limit of $10,000. 3) The total outstanding balance for all customers represented by Rep 35. 4) Modification of the Part table to create a new SportingGoods table containing only records for class SG.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    of 7

    1

    Answer the 16 questions in the case study, Premiere Products Exercises: QBE, Page 68. Answer 1: We create a query (Query 1) by selecting the Customer table, and the columns CustmerNum and CustomerName. After saving the query we can run it, and the result is shown in the figure below:

    Query1
    CustomerNum CustomerName 842 All Season 148 Al's Appliance and Sport 462 Bargains Galore 282 Brookings Direct 725 Deerfield's Four Seasons 356 Ferguson's 608 Johnson's Department Store 524 Kline's 687 Lee's Sport and Appliance 408 The Everything Shop

    Answer2: Here we create a new query (Query 2) and we pick the Part table. We can now select all the columns from the Part table without specifying any criteria, so in this way, we will be able to see in the query all the data contained in the table Part as requested in this question. The result is shown in the figure below:

    Query2
    PartNum Description OnHand Class Warehouse AT94 Iron 50 HW 3 BV06 Home Gym 45 SG 2 CD52 Microwave Oven 32 AP 1 Price $24.95 $794.95 $165.00

    Query2
    PartNum Description DL71 Cordless Drill DR93 Gas Range DW11 Washer FD21 Stand Mixer KL62 Dryer KT03 KV29 Dishwasher Treadmill OnHand Class Warehouse 21 HW 3 8 AP 2 12 AP 3 22 HW 3 12 AP 1 8 AP 9 SG 3 2 Price $129.95 $495.00 $399.99 $159.95 $349.95 $595.00 $1,390.00

    Answer 3: Here we create a new query (Query 3) and we select the Rep and Customer tables. From the Rep table we select the RepNum column and from the Customer table we select the CustomerNum and CustomerName columns. After running the query the result is shown in the figure below: Query3
    RepNum CustomerNum CustomerName 35 282 Brookings Direct 35 408 The Everything Shop 35 687 Lee's Sport and Appliance 35 725 Deerfield's Four Seasons

    Answer 4: Here we create a new query (Query 4) and we select the Rep and Customer tables. In the criteria we type 35 under RepNum column and 10000 under the CreditLimit column. The result is shown below:

    Query3
    RepNum CustomerNum CustomerName CreditLimit 35 282 Brookings $10,000.00 Direct

    Answer 5: The result of this query is shown in the figure below: Query5
    RepNum CustomerNum 35 35 35 35 65 65 282 408 687 725 462 608 CustomerName Brookings Direct The Everything Shop Lee's Sport and Appliance Deerfield's Four Seasons Bargains Galore CreditLimit $10,000.00 $5,000.00 $5,000.00 $7,500.00 $10,000.00

    Johnson's Department Store $10,000.00

    Answers 6: For this query we select the Orders table and the Customer table. From these two tables we select all the columns from the Orders table and the CustomerName column from the Customer table. After saving and running the query, the report is shown in the figure below:

    Query 6
    OrderNum OrderDate CustomerNum CustomerName 21608 10/20/2013 148 Al's Appliance and Sport 21619 10/23/2013 148 Al's Appliance and Sport 21614 10/21/2013 282 Brookings Direct 21610 10/20/2013 356 Ferguson's 21613 10/21/2013 408 The Everything Shop 21617 10/23/2013 608 Johnson's Department Store 21623 10/23/2013 608 Johnson's Department Store

    Answers 7: For this query we select the Rep table and the Customer table. From the Rep table we select FirstName and LastName columns, and from the Customer table we select the CustmerNum and CustomerName columns. After saving and running the query, the report is shown in the figure below: Query 7
    FirstName LastName CustomerNum CustomerName Juan Perez 356 Ferguson's Juan Perez 462 Bargains Galore Juan Perez 608 Johnson's Department Store

    Answers 8: There are three customers with a credit limit of $10,000. Query 8
    CustomerNum CustomerName CreditLimit 282 Brookings Direct $10,000.00 462 Bargains Galore $10,000.00 608 Johnson's Department Store $10,000.00

    Answers 9: The total outstanding balance for all customers represented by Rep 35 is $8,815.75 as shown in the figure below:
    RepNum CustomerName 35 Brookings Direct 35 The Everything Shop Lee's Sport and 35 Appliance 35 Deerfield's Four Seasons Total Balances: Balance $431.50 $5,285.25 $2,851.00 $248.00 $8,815.75

    Answers 10: The result of this query is shown in the figure below: Query 10
    PartNum Description OnHand Class Price AT94 Iron 50 HW $24.95 DL71 Cordless Drill 21 HW $129.95 FD21 Stand Mixer 22 HW $159.95

    Answers 11: The result of this query is shown in the figure below: Query 11
    PartNum Description OnHand Class Warehouse Price DL71 Cordless Drill 21 HW 3 $129.95 KT03 Dishwasher 8 AP 3 $595.00 KL62 Dryer 12 AP 1 $349.95 DR93 Gas Range 8 AP 2 $495.00 BV06 Home Gym 45 SG 2 $794.95 AT94 Iron 50 HW 3 $24.95 CD52 Microwave Oven 32 AP 1 $165.00 FD21 Stand Mixer 22 HW 3 $159.95 KV29 Treadmill 9 SG 2 $1,390.00 DW11 Washer 12 AP 3 $399.99

    Answers 12: The result of this query is shown in the figure below: Query 12
    PartNum Description OnHand Class Warehouse Price AT94 Iron 50 HW 3 $24.95 BV06 Home Gym 45 SG 2 $794.95 CD52 Microwave Oven 32 AP 1 $165.00 DL71 Cordless Drill 21 HW 3 $129.95 DR93 Gas Range 8 AP 2 $495.00 DW11 Washer 12 AP 3 $399.99 FD21 Stand Mixer 22 HW 3 $159.95 KL62 Dryer 12 AP 1 $349.95 KT03 Dishwasher 8 AP 3 $595.00 KV29 Treadmill 9 SG 2 $1,390.00

    Answers 13: The result of this query is shown in the figure below:
    Class AP AP AP AP AP HW HW HW SG SG OnHand 8 12 12 8 32 22 21 50 9 45 Total = 219

    Answers 14: To create this new table, we can create a copy of the Part table, and delete the all records except the ones for class SG. The result of this task is shown in the figure below:

    SportinGoods
    PartNum Description OnHand Class Warehouse Price KV29 Treadmill 9 SG 2 $1,390.00 BV06 Home Gym 45 SG 2 $794.95

    Answers 15: The result of this task is shown in the figure below: SportinGoods
    PartNum Description OnHand Class Warehouse Price KV29 Treadmill 9 SG 2 $1,390.00 BV06 Fitness Gym 45 SG 2 $794.95

    Answers 16: The result of this task is shown in the figure below: SportinGoods
    PartNum Description OnHand Class Warehouse Price BV06 Fitness Gym 45 SG 2 $794.95

    You might also like