Write SQL queries (do not use QBE) for the following questions and execute the queries using 'TAL_Distributors" database on MS Access.

a. How many customers have a credit limit of $15000.

b. How many items are there in the Item table with the Price greater than $40.

c. Find the total of the balances for all customers represented by rep 30.

d. List all columns and all rows in the Item table. Sort the results by Item description.

e. List all columns and all rows in the Item table. Sort the results by Item number within category.

f. For every order, list the order number, order date, customer number, and customer name.

Order the records by order number.

g. List the numbers, names, Credit Limit and complete address of every customer with a credit limit of $5000 or $7500. Hint: use IN operator.

h. List ItemNum, Description, OnHand, and Price for all rows on which the category is TOY and price is greater than $120.

i. List the number and name of each customer whose credit limit is $10000 together with the number, last name and first name of the rep who represents the customer.

(Note: you need to join 2 tables Customer and Rep )

j. Suppose you have the ERD shown in Figure Q3.18. How would you convert this model into an ERM that displays only 1:M relationships? (Make sure you create the revised ERD.)

https://drive.google.com/file/d/1pgRD0kFVV7Sj2enKAk3VQg9gmgvLeuEI/view?usp=sharing

 

 

FIGURE Q3.18 THE CROW'S FOOT ERD FOR QUESTION 14 DRIVER TRUCK drives During some time interval, a DRIVER can drive many TRUCKS and any TRUCK can be driven by many DRIVERS