These are SQL questions. Here is the table reference to them: CREATE TABLE custo

Question

These are SQL questions. Here is the table reference to them:

CREATE TABLE customer
(cust_id       CHAR(4) PRIMARY KEY,
cust_name       VARCHAR2(30),
cust_street       VARCHAR2(20),
cust_city       VARCHAR2(20),
cust_state       CHAR(2),
cust_zip       CHAR(5),
cust_balance       DECIMAL(8,2),
cust_limit       DECIMAL(8,2),
rep_id           CHAR(3) CONSTRAINT rep_id_fk REFERENCES rep(rep_id) );

1. Display the sales representative’s id and the sum of the balances of all customers represented by each of these sales representatives. Group and order the display by the sales representative ids.

2. Display the sales representative’s id and the sum of the balances of all customers represented by each of these sales representatives, but limit the result to those sales representatives whose sum is more than $12,000.

Explanation / Answer

1. Display the sales representative’s id and the sum of the balances of all customers represented by each of these sales representatives. Group and order the display by the sales representative ids.

SELECT rep_id, sum(cust_balance) FROM customer GROUP BY rep_id ORDER BY rep_id;

2. Display the sales representative’s id and the sum of the balances of all customers represented by each of these sales representatives, but limit the result to those sales representatives whose sum is more than $12,000.

SELECT rep_id, sum(cust_balance) FROM customer GROUP BY rep_id HAVING sum(cust_balance) ORDER BY rep_id;

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.