Description
b) This is an individual assignment.
c) You are required to use PostgreSQL to complete this assignment.
d) Use the Schema.sql script provided to create the initial tables and insert the data into the database; as well as to
drop the tables if need be.
CanProducts is a distributor of appliances, house ware and sporting goods, to stores throughout Ontario. They maintain
a database in order to manage their customers, ordering information, sales representatives and inventory data.
The database contains the following tables (as contained in the SQLschema.sql script file):
i. The Rep table, which contains the name, address, total commission and commission rate of all sales
representatives.
ii. The Customer table, containing customers’ names and addresses, their outstanding balance, credit limit and the
sales representative they order from. (Note that these customers are actually stores.)
iii. The information about Orders, i.e. the order number, data placed and the customer who placed the order.
iv. For each OrderLine, the order number, the part number, the number of parts ordered and the quoted price.
v. Finally, we store information about each Part, such as the description, price, warehouse and class.
Complete all the following questions.
1. Modify the SQL code to enforce the following integrity constraints:
a) The balance of a customer may not be less than 0 or exceed his credit limit; the default value is 100. (2)
b) The class in the part table has to be one of the following: AP, HW, SG. (2)
c) The order number and part number in the order line table may not be empty. (2)
2. List the number and name of all the customers not represented by Kaiser Valerie (Rep num 20). (4)
3. List the number and name of all sales representatives, along with the number of customers they represent. (4)
4. List the number, name, balance and sales representative number of those customers whose balance is larger than
the balance of at least one customer of Juan Perez (Rep num 65). (4)
5. List the order numbers and dates of all orders that contain a Gas Range and were sold by Juan Perez. (4)
6. List the number, names and balances of all customers whose credit limit is equal to the largest credit limit awarded
to a customer of Richard Hull (Rep num 35). (4)
7. List the sales representative numbers and names, along with the total amount sold, of all the parts within the AP
class. (4)
8. List each credit limit held by less than two customers, together with the number of customers of Richard Hull (rep
num 35) who have this limit. (4)
9. Find the descriptions of all parts included in order 21610. (4)
10. List the customer numbers, customer names and balances, together with their sales representative number, of those
customers who have a remaining credit that is less than the 15% of their credit limit. (4)
11. List the customer numbers, customer names and balances of all the customers of Kaiser Valerie who never placed
any orders. (4)
12. Remove all the information about customer number 408 from the database. (4)
