Short Answer { 10 pts each, Total: 40 pts } For all of these questions, you are

Question

Short Answer { 10 pts each, Total: 40 pts } For all of these questions, you are writing the SQL command, not showing the results Use these tables for all of the Short Answer questions: Database name: Ch08 SaleCo Table name: CUSTOMER Table name: INVOICE INV NUM | CUST NUM INV DATE INV AMOUNT 235 89 312 82 528.10 194.78 619.44 CUST CUST CUST Jeanne Juan CUST 000 23-Mar-10 001 23-Mar-10 1001 30-Mar-10 1000 12-Apr-10 1000 23 Apr 10 1000 Smth 1001 Ortega 1050.11 8101 8102 8003 8104 840.92 Table name: CUSTOMER 2 CUST CUST CUST Anne Juan 2000 McPherson 2001 Ortega 2002 Kowalski 2003 Chen George #1. Write a query to show the invoice number, the invoice date. customer number and customer last S1,000 #2. Write the query that will show the invoice number and the name for all customers who have a customer balance of more than difference between the average invoice amount and the actual invoice amount #3. Modify the CUSTOMER2 table to include one new attribute: CUST DOB ( customer date of birth ) #4. Assume that you modified the CUSTOMER2 table as stated in the last question AND assume that values were populated for that new attribute for all of the rows Assume you added another attribute: CUST AGE. Show the code to populate the values for the attribute CUST AGE for all the rows by using the values stored in the attribute CUST DOB. HINT: use ROUND and SYSDATE?

Explanation / Answer

Question 1

Answer:

select i.inv_num, i.inv_date, c.cust_num,c.cust_lname from customer c, invoice i where c.cust_num = i.cust_num and c.cust_balance > 1000;

Question 2

Answer:

select inv_num, (avg(inv_amount) - inv_amount) from invoice;

Question 3

Answer:

ALTER TABLE invoice

ADD cust_dob date;

Question 4

Answer:

update invoice set cust_age=round(TO_DATE(sysdate, 'YYYYMMDD HH:MI:SS AM') - TO_DATE(cust_dob, 'YYYYMMDD HH:MI:SS AM'));

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

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