Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

I am trying to find the right SELECT statements for these 5 requests. 1.) List the totals of the balances for sales representatives with at

I am trying to find the right SELECT statements for these 5 requests.

1.) List the totals of the balances for sales representatives with at least two customers.

2.) List the order totals and order numbers, for all the orders with totals that are higher than $1000. Order your results by the numbers of the orders.

3.) List the numbers, names, balances and sales representative numbers of those customers with a balance that is smaller than the average balance of at least one customer of Ann Hull.

4.) List the numbers, names, balances and sales representative numbers of those customers whose balance is smaller than the average balance of all customers of Ann Hull.

5.) List the numbers and names of all customers who are represented by Ann Hull but who do not currently have orders on file. Here is some info for the schema of the database.

Here is the schema information

Company x is a distributor of appliances, houseware and sporting goods, which they supply to stores. They maintain a database in order to manage their customers, ordering information, sales representatives and inventory data.

The Rep table, which contains the name, the address and city where they are based, total commission and commission rate of all sales representatives. 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.) The information about Orders, i.e. the order number, data placed and the customer who placed the order. For each OrderLine, the order number, the part number, the number of parts ordered and the quoted price. Finally, we store information about each Part, such as the description, price, warehouse and class.

and HERE is the SQL Code for the tables:

CREATE TABLE REP ( REP_NUM CHAR(2) PRIMARY KEY, LAST_NAME CHAR(15), FIRST_NAME CHAR(15), STREET CHAR(15), CITY CHAR(15), PROVINCE CHAR(3) CHECK (PROVINCE IN ('ONT', 'QC')), ZIP CHAR(5), COMMISSION DECIMAL(7,2) CHECK (COMMISSION IS NOT NULL AND commission <= 0.15), RATE DECIMAL(3,2) ); CREATE TABLE CUSTOMER ( CUSTOMER_NUM CHAR(3) PRIMARY KEY, CUSTOMER_NAME CHAR(35) NOT NULL, STREET CHAR(15), CITY CHAR(15) DEFAULT 'Ottawa', PROVINCE CHAR(3), ZIP CHAR(5), BALANCE DECIMAL(8,2) CHECK (BALANCE <= CREDIT_LIMIT), CREDIT_LIMIT DECIMAL(8,2), REP_NUM CHAR(2) ); CREATE TABLE ORDERS ( ORDER_NUM CHAR(5) PRIMARY KEY, ORDER_DATE DATE, CUSTOMER_NUM CHAR(3) ); CREATE TABLE PART ( PART_NUM CHAR(4) PRIMARY KEY, DESCRIPTION CHAR(15), ON_HAND DECIMAL(4,0), CLASS CHAR(2), WAREHOUSE CHAR(1), PRICE DECIMAL(6,2) ); CREATE TABLE ORDER_LINE ( ORDER_NUM CHAR(5), PART_NUM CHAR(4), NUM_ORDERED DECIMAL(3,0), QUOTED_PRICE DECIMAL(6,2), PRIMARY KEY (ORDER_NUM, PART_NUM) ); 

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Spomenik Monument Database

Authors: Donald Niebyl, FUEL, Damon Murray, Stephen Sorrell

1st Edition

0995745536, 978-0995745537

More Books

Students also viewed these Databases questions

Question

122. If X is distributed as N(0, 1), find the pdf of .

Answered: 1 week ago

Question

Analyze the impact of mergers and acquisitions on employees.

Answered: 1 week ago

Question

Evaluate the importance of the employee handbook.

Answered: 1 week ago